一、优化思路
- 开启慢查询日志，找出问题SQL
- 查看SQL的执行计划
- 优化SQL
- 使用show profile[s] 查看问题SQL的性能使用情况
- 调整操作系统参数
- 升级服务器硬件
二、慢查询日志
mysql> show variables like 'slow%'; +---------------------+--------------------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------------------+ | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | C:Program FilesMySQLdataB-PF1H2APD-slow.log | +---------------------+--------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)
1. 临时开启慢查询
#打开慢查询 set global slow_query_log = ON; #设置阈值时间 set global long_query_time = 1;
2. 修改配置文件
[mysqlId] slow_query_log=ON long_query_time=1
3. 分析慢查询日志的工具
MySQL自带的mysqldumpslow。请参考：https://blog.csdn.net/sunyuhua_keyboard/article/details/81204020
三、查看执行计划
explain + sql语句。前排提示：explain预估row会忽视limit，分析带limit的SQL语句，请使用 SHOW SESSION STATUS LIKE "Handler%";
mysql> explain select * from t where a = 1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.07 sec)
3.1. id：编号
用于区分多个查询。
1. 有查询id相同：包含关联查询，执行顺序由上到下
2. 有查询id不同：包含子查询，id号自增，id越大，优先级越高越先执行
3.2. select_type：查询类型
1. simple：简单select查询。有关联查询时，外层查询为simple
2. primary：包含子查询或者合并查询时，最外层查询为primary
3. union：包含合并查询，内部查询为union
4. union result：包含合并查询，并且使用的是union，需要对结果进行去重，排序，最后返回查询结果，这个查询为union result
5. dependent union：union/union all之后的select为dependent union，并且该查询依赖外部查询。
mysql> explain select a from t as t3 where a in(select a from t as t1 union select a from t as t2); +----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+ | 1 | PRIMARY | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 49410 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 49410 | 10.00 | Using where | | 3 | DEPENDENT UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 49410 | 10.00 | Using where | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+ 4 rows in set, 1 warning (0.00 sec)
上述查询似乎没有显式依赖，实际是MySQL会将in优化成exists
mysql> explain select a from t t3 where exists(select 1 from t t1 where t1.a = t3.a union select 1 from t t2 where t2.a = t3.a); +----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+ | 1 | PRIMARY | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 49410 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 49410 | 10.00 | Using where | | 3 | DEPENDENT UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 49410 | 10.00 | Using where | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+ 4 rows in set, 3 warnings (0.00 sec)
6. subquery：在select或where中包含子查询，且结果不依赖于外部查询
mysql> explain select * from t where a = (select a from t where a = 1); +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | PRIMARY | t | NULL | ALL | NULL | NULL | NULL | NULL | 49410 | 10.00 | Using where | | 2 | SUBQUERY | t | NULL | ALL | NULL | NULL | NULL | NULL | 49410 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
7. dependent subquery：子查询中的第一个select，取决于外部的查询。如下子查询的筛选需要先查出主查询。这种查询类型说明SQL性能差。但是在MySQL5.7.26版本下测试好像被优化了。
mysql> explain select * from t where a in (select a from test where test.b = t.b); +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+------------------------------+ | 1 | SIMPLE | test | NULL | ALL | idx_a | NULL | NULL | NULL | 1024 | 100.00 | Using where; Start temporary | | 1 | SIMPLE | t | NULL | ref | b | b | 5 | test.test.b | 1 | 10.00 | Using where; End temporary | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+------------------------------+ 2 rows in set, 2 warnings (0.00 sec)
换一个查询试试，很奇怪哦
mysql> explain select(select a from test where test.b = t.b) from t; +----+--------------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+ | 1 | PRIMARY | t | NULL | index | NULL | b | 5 | NULL | 49410 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | test | NULL | ALL | NULL | NULL | NULL | NULL | 1024 | 10.00 | Using where | +----+--------------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec)
8. derived：from语句中出现的子查询。已被优化为 select * from t;
mysql> explain select * from (select * from t)t; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+ | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 49410 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
3.3 table：查询使用的表
1. 如果使用了别名，显示别名
2. 如果不涉及对表的操作，显示null
3. 如果使用的是临时表，表名为<derived N>，N代表的是id，即该表是由这条查询语句N生成的
4. 如果是union查询结果产生的临时表，表名<union M,N>，表示由查询语句M和查询语句N合并查询产生的
3.4 type：查询的连接类型或者访问类型
system>const>eq_ref>ref>range>index_merge>index>ALL
1. const：使用唯一索引或者主键查询返回一行记录的等值查询
2. eq_ref：使用唯一索引或主键进行等值关联查询，InnoDB在数据量小时，会退化成ALL
mysql> explain select * from t t1 left join t t2 on t1.a = t2.a; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.a | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)
3. ref：跟eq_ref的区别是，使用非唯一索引
mysql> explain select * from t t1 left join t t2 on t1.b = t2.b; +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ref | b | b | 5 | test.t1.b | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)
4. range：索引范围查找，常见于>,<,is null,between,in,like等运算查询中
5. index_merge：MySQL5.7版本的优化，在查询中可以使用多个单独索引。看到这个说明需要优化索引了
6. index：在查询结果中使用了索引值，即索引覆盖
7. all：全表扫描
3.5 possible_keys：可能使用的索引
3.6 key：实际使用的索引
3.7 key_len：使用到的索引的长度
组合索引不一定用到所有的索引，根据长度可以判断
varchr(10)变长字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)
bigint长度是8bytes，int长度是4，typeint长度是1，smallint长度2，middleint长度是3。
注意int(10)设置的是显示宽度，假如不足10位则补零，int类型存储的范围固定是-2的31次方到2的31次方。
3.8 ref
如果是常数等值查询，显示const。如果是关联查询，被驱动表会显示驱动表的关联字段。
3.9 row：结果行数，是一个估计值
3.10 extra
1. Using filesort：产生了无法使用索引的排序（请参考：https://www.jianshu.com/p/069428a6594e）
2. Using temporary：使用临时表保存中间结果。常见于排序和分组
3. Using index：覆盖索引，筛选条件从索引第一列开始
4. Using where：没有覆盖索引，筛选条件不是索引第一列开始或第一列的范围查找
5. Using where，Using index：覆盖索引，但筛选条件是索引，但不是索引第一列或是第一列的范围查找。即代表在索引树上进行了扫描
6. Using index condition：索引下推。没有覆盖索引，筛选条件从索引第一列开始
7. NULL：没有索引覆盖，筛选条件从索引第一列开始
参考：https://www.cnblogs.com/ivy-zheng/p/11121360.html
四、SQL语句优化
1. 索引优化，请见索引篇
2. LIMIT优化
1. 对于已知只有一行结果的查询，使用limit 1，使查询尽快中止
2. 分页查询。
mysql> explain select * from page a inner join (select id from page limit 1000,100) b on a.id = b.id; +----+-------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 1100 | 100.00 | NULL | | 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | b.id | 1 | 100.00 | NULL | | 2 | DERIVED | page | NULL | index | NULL | PRIMARY | 4 | NULL | 11747 | 100.00 | Using index | +----+-------------+------------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+
还有一种方法是在程序缓存上一次查询的最大id，下次分页查询时，用id做查询条件，防止id不连续的情况，一般查询条件 id + page_size + n。确保查询结果足量包含一页的大小。
3. 小表驱动大表(in(小表查询)，exists(大表查询))
4. 关联表查询关联的字段需建立索引，并且注意字段的类型需要一致避免做类型转换
五、服务器层优化
1. 缓冲区优化，设置缓冲池大小，一般设置为总内存的3/4到4/5。
2. 降低磁盘写入次数，关闭通用查询日志、慢查询日志、错误日志。