MySQL总结（五）——Explain的坑以及如何分析SQL

本节主要回答以下4个小问题。

一、问题

1.explain真的准确吗？limit 100为什么会扫全表？ 

2.怎么确定走没走索引？怎么确定哪里耗时高？

3.走索引就一定耗时低吗？

4.order by desc 比正序慢吗？

 

二、SQL举例

举一个最近碰到的栗子，线上耗时偶尔飘高，于是乎分析了下

select source_id from table where a = 123 and b < 2000 order by desc limit 1000;

使用explain 查看执行计划: 全表一共7kw数据，limit 1000 扫了3.2kw数据，好吓人。。 

select count(*) from table where a = 123;实际是2.9kw行

limit XXX是不是不准？3.2kw行是怎么出来的？ 由此问题整理下explain遇到的坑。

三、EXPLAIN的困惑

3.1 limit在explain时无效

（直接节选官网的回答） 有问题时还是要先提出质疑，再去相信。

explain忽略limit，然后对select xxx order by desc 进行查询分析。

3.2 explain预估影响行数的机制

3.2kw行应该是这么算出来的。MySQL是按照Page存储的，每个Page都有自己的元数据，会记录行数。（存储结构见MySQL总结（二））explain不可能真正扫一遍表，它根据索引找到第一页和后续八页以及最后一页，然后算平均值。 上述案例中a字段行数应该超过八页，所以差距比较大。

预估Rows = ((Records_PLeft + Records_P1 + Records_P2 + ... + Records_P8 + Records_PRight)/10)*Page_Num

3.3 使用索引依然全表扫描

使用explain一定要结合多个字段分析。

key：表示使用的索引 rows:表示影响行数。使用索引不代表不会进行全表扫描，举一个可能会踩的坑。

CREATE TABLE `t1` (

 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',

 `a` varchar(255) NOT NULL COMMENT '字符',

 `b` varchar(255) NOT NULL COMMENT '字符',

 `c` varchar(255) NOT NULL COMMENT '字符',

 PRIMARY KEY (`id`),

 KEY `idx_a_b_c` (`a`,`b`,`c`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 

explain select * from t2 where a = 1;

explain告诉我们使用key了，但行数为什么是全部！因为1是整型 在mysql server会做一次强制类型转化，但！不会走索引查找，会进行全表扫描。

explain select * from t2 where a = "1"; //所以双引号很重要。。 

改成字符串 扫描索引只查4行。

四、分析性能的正确姿势

如果觉得explain不太准，怎么办？ 可以使用 show session status like "Handler%"； show profile 这两个命令对SQL进行更准确的分析。

4.1 SHOW SESSION STATUS LIKE "Handler%"

关注这几个参数 （官方文档: https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html）

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数,越低代表索引不经常使用。

Handler_read_next ：按照键顺序读下一行的请求数。利用索引进行范围查找，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。没有使用索引查询，这个值较高，意味着运行效率低。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。

rnd、rnd_next过大需要优化； read_key、read_next、read_prev 太大也不行，这说明扫描了很多索引值才找到记录，也需要优化。

4.2 SHOW PROFILE 性能分析

官方文档:https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html 

重点说一个参数 Sending data: 这个不是数据包发送到client的耗时，而是server获取数据处理数据的耗时，查索引做merge等等操作的时间，一般慢查询都是这个指标偏高。

4.3 举栗分析

1.执行 show session status like "Handler%";

2.explain select * from t1 where a = 1 ;

 

扫描了全表，真的吗？ 

3.执行 select * from t1 where a = 1 ;

4.执行 show session status like "Handler%";

还真是46行！因为用的整型而不是字符串！

5.explain select * from t1 where a = 1 limit 1;

发现还是46行，limit不管用吗？

6.执行select * from t1 where a = 1 limit 1; 并执行SHOW SESSION STATUS LIKE "Handler%”

limit实际只查一行哦～

7.执行SQL语句分析性能

show profiles

set profiling = on

show profile 

 

五、倒序和正序效果分析

这里顺带提一下order by desc。 MySQL在8.0前只支持正序排序存储，链表是双向的，其实order by desc a 不会有什么性能损耗。但如果是order by a asc b desc 这种蛋疼的逻辑（线上业务谁会用呢。。），联合索引就不生效了，只有8.0后支持倒序存储才能解决。详见这篇wiki http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

六、小结

1.explain真的准确吗？limit 100为什么会扫全表？ 

explain只能作为参考；limit不支持

2.怎么能确定走没走索引？怎么确定哪里耗时高？

使用 show session status like "Handler%" 确认是否走索引；show profile 进行各阶段耗时分析

3.走索引就一定耗时低吗？

不一定；索引扫描过多 需要优化；全表扫描需要优化。

4.order by desc 比正序慢吗？

只要用了索引，正常情况不会慢放心用

 

参考文献:

1.http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

2.https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

3.https://imysql.com/node/89

4.https://blog.csdn.net/yunhua_lee/article/details/8573621