在使用MySQL的时候会遇到一些性能问题，因此对使用 MySQL 进行优化尤为重要。优化数据库可以从4个方面入手：从设计上优化、从查询上优化、从索引上优化、从存储上优化。下面针对不同场景的优化技巧进行说明。

查看SQL的执行频率

连接 MySQL，可以通过 show [session|global] status 命令查看服务器状态信息。

通过查看状态信息可以查看对当前数据库的主要操作类型。

show session status like 'Com_______';  	#查看当前会话统计结果(7个下划线)
show global  status  like 'Com_______'; 	#查看自数据库上次启动至今统计结果 
show status like 'Innodb_rows_%’;       #查看针对Innodb引擎的统计结果

定位低效率执行的SQL语句

• 慢查询日志 : 通过慢查询日志定位执行效率较低的 SQL 语句。
• show processlist：该命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

#查看慢日志配置信息 
show variables like '%slow_query_log%'; 
#开启慢日志查询 
set global slow_query_log=1; 
#查看慢日志记录SQL的最低阈值时间 
show variables like 'long_query_time%'; 
#修改慢日志记录SQL的最低阈值时间 
set global long_query_time=4;
#通过show processlist来查看当前客户端连接服务器的线程执行
show processlist;

trace分析优化器

MySQL5.6 开始提供对SQL的跟踪trace, 通过trace文件可以进一步了解为什么优化器选择A计划, 而不是选择B计划，帮助我们更好的了解优化器的操作。

首先要打开trace ，设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。再执行需要分析的 SQL，最后查看 trace 分析结果。

set optimizer_trace="enabled=on",end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;

select * from user where uid < 2;
# 使用 trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
#查看结果
select * from information_schema.optimizer_trace;
# 关闭 trace
set session optimizer_trace="enabled=off";

在trace结果中

• 准备阶段：对应文本中的 join_preparation
• 优化阶段：对应文本中的 join_optimization
• 执行阶段：对应文本中的 join_execution
  使用时，重点关注优化阶段和执行阶段。

索引优化

索引是数据库优化最常用也是最重要的手段之一。

创建索引

# 为name字段创建普通索引
create index idx_name on users(name);
# 为name字段创建唯一索引
create unique index idx_email on users(email);

创建索引组合（复合索引）

reate index 索引名字 on 数据库表(name,status,address)

使用 explain 分析查询

explain select * from 数据库表 where name='小米科技' and status='1' and address='北京市';

插入优化

大批量插入数据的优化，主要为优化insert语句，优化order by语句，优化子查询，limit优化。

优化 insert 语句

1、一个 insert 语句中插入多个值

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的 insert 语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个 insert 语句快。

# 原始方式为
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
​
# 优化后
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

2、在事务中进行数据插入

在插入大批量数据时，将多条插入操作放在一个事务中，可以减少事务开销。需要先关闭自动事务提交。本质上还是缩减客户端与数据库之间的连接、关闭等消耗。

START TRANSACTION;
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
INSERT INTO table_name (column1, column2) VALUES (value3, value4);
INSERT INTO table_name (column1, column2) VALUES (value5, value6);
COMMIT;

3、数据有序插入

# 优化前
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');
 
 
# 优化后
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

优化 order by 语句

第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

建立合适的索引可以优化。确保用于排序的列上有索引，避免全表扫描。

CREATE INDEX idx_column1 ON table_name (column1);
SELECT * FROM table_name ORDER BY column1;

减少排序数据量: 通过 WHERE 子句过滤数据，减少参与排序的数据量。

sql复制代码SELECT * FROM table_name WHERE condition ORDER BY column1;

排序前限制数据量

在排序前使用 LIMIT 子句，可以减少参与排序的数据量。

SELECT * FROM table_name ORDER BY column1 LIMIT 1000;

优化子查询

使用连接替代子查询

子查询的性能通常不如连接，可以用 JOIN 替代子查询。

-- 子查询
SELECT * FROM table_name1 WHERE column1 IN (SELECT column2 FROM table_name2);

-- JOIN 替代子查询
SELECT table_name1.* 
FROM table_name1 
JOIN table_name2 ON table_name1.column1 = table_name2.column2;

使用索引: 确保子查询涉及的列上有索引。

CREATE INDEX idx_column2 ON table_name2 (column2);

避免重复子查询: 将子查询的结果缓存到临时表中，减少重复计算。

CREATE TEMPORARY TABLE temp_table AS 
SELECT column2 FROM table_name2;

SELECT * FROM table_name1 WHERE column1 IN (SELECT column2 FROM temp_table);

多表查询的效率要高于子查询。

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

system > const > eq_ref > ref > range > index > ALL

连接 (Join) 查询之所以更有效率一些 ，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

一些其他的优化技巧

1、查询语句中不要使用select *
2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代
3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，
union all会更好)
5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表
扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有
null值，然后这样查询： select id from t where num=0