本节主要回答以下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