技术社群的这篇文章《EXPLAIN:解说一条简单语句的执行计划》给我们讲解了MySQL中如何通过EXPLAIN得到执行计划,以及通过这些执行计划我们能了解到SQL性能问题的实践案例,有助于我们更好展开SQL调优工作。
在MySQL里,当通过各种手段抓取到影响系统性能的慢SQL语句后,此刻想知道这条SQL语句为何会慢,大致有以下方式:
- 凭借个人已知的SQL优化经验进行SQL改写。
- 对照公司严格要求的开发规范进行SQL改写。
- 查看对应SQL语句的执行计划,从结果中分析SQL是否使用合适的索引、是否走了合适的表关联顺序、是否走了合适的表关联算法等。
通过以上三种方式审阅后,是否需要反馈给业务方来修改SQL语句的逻辑或者说是变更SQL语句涉及到的表结构。
虽然以上几种方式一般都需要互相结合来验证SQL的优化效果,仅凭一种方式来直接定位出SQL是否足够优化不太可能,但是每种方式都需要依赖第三种方式,通过直接和数据库交互来查看数据库的执行计划,能够比较直观的展示SQL执行的过程。在MySQL里查看SQL语句执行计划的方式是使用EXPLAIN语句。
EXPLAIN(对应同义词:DESC、DESCRIBE)语句可以用来查看一条SQL是否匹配到合适的索引、是否使用到中间临时表、是否需要额外的排序等等不利因素;并且EXPLAIN语句还有几种定制化的输出格式(表格、JSON、树状),每种格式输出内容的详细程度不一样;当一条SQL语句被带入EXPLAIN执行后,从执行结果就能大致判断这条SQL目前能否做进一步优化。
本篇使用EXPLAIN来验证一条简单的SQL语句,来帮助我们进行后续调优。以下是此语句的EXPLAIN 执行结果:
debian-ytt1:ytt>desc select * from t1 where r1 = 4\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: idx_r1,idx_u1
key: idx_u1
key_len: 5
ref: const
rows: 29324
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
debian-ytt1:ytt>show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `ytt`.`t1`.`id` AS `id`,`ytt`.`t1`.`r1` AS `r1`,`ytt`.`t1`.`r2` AS `r2`,`ytt`.`t1`.`r3` AS `r3`,`ytt`.`t1`.`log_date` AS `log_date` from `ytt`.`t1` where (`ytt`.`t1`.`r1` = 4)1 row in set (0.00 sec)
上面是对一条极其简单的SQL语句执行EXPLAIN的结果,有几个最直观的点需要关注:
- possible_keys: 表示这条SQL 可能会用到的索引, MySQL 会把所有这条SQL有可能会用到的索引罗列到这里,最终基于当前关系表的统计信息、当前索引表的统计信息选择一个最优的索引来执行。
- key: 表示这条SQL使用到的最终索引,如果不是NULL,就表示执行计划使用了指定的索引来执行这条SQL。比如这里用了索引idx_u1:这条索引是一个联合索引。
- rows:表是执行这条SQL 需要大致扫描多少行才能出来结果,这里扫描了2.9W行。
- Extra:额外的信息。这条SQL的EXTRA栏里是NULL,表示没有额外信息展示出来。
- warnings: warnings 结果表示最终这条语句在MySQL的SQL解析层最终被转换后的SQL形式。
读到这里,可能会启发几个问题:
- 虽然这条SQL可以正确用到索引,但是查询性能依然没有达到我的要求,有没有什么方法可以做进一步优化?
- 虽然从结果可以看到这条SQL 扫描的大致行数,但是此值依然很大。比如这里是2.9W行,有办法减少吗?
- 还有就是如果EXTRA栏里的内容我知道了,我该怎么做二次优化?
针对这条SQL语句,如果单纯从语句写法上来看,我觉得已经无法二次优化,因为它已经匹配到了索引。现在的问题是MySQL用到的这个索引是否足够好?从执行计划结果来猜测:这个索引本身的选择性太差,导致仅执行一行等值检索,就要耗费大量的资源去扫描接近3W行的数据记录。
接下来,我们给这条语句做些变化,让他执行计划输出的EXTRA栏里有点内容,而且还是看起来需要优化的内容。比如"using filesort":给这条SQL加条排序子句。
debian-ytt1:ytt>desc select * from t1 where r1 = 4 order by id desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: idx_r1,idx_u1
key: idx_u1
key_len: 5
ref: const
rows: 29324
filtered: 100.00
Extra: Using filesort1 row in set, 1 warning (0.00 sec)
接下来回答之前提到的几个问题:
第一个问题:选择了索引,这条SQL执行时间依然很长?
其实这个在我的专栏里索引设计那一块有讲过,并不是说SQL语句使用了索引,查询性能就一定会很好,使用索引和查询性能提升两者并没有什么必然联系。比如这条SQL语句,执行计划结果显示用了索引idx_u1,但是基于这个索引还得扫描2.9W行记录才行。那我们来进一步看看这条索引在表t1里的基数值:
debian-ytt1:ytt>select table_name,index_name,column_name,cardinality
-> from information_schema.statistics
-> where table_schema='ytt' and table_name='t1';
+------------+--------------+-------------+-------------+
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+------------+--------------+-------------+-------------+
| t1 | idx_log_date | log_date | 335 |
| t1 | idx_r1 | r1 | 5 |
| t1 | idx_r2 | r2 | 100 |
| t1 | idx_r3 | r3 | 100 |
| t1 | idx_u1 | r1 | 5 |
| t1 | idx_u1 | r2 | 599 |
| t1 | idx_u1 | r3 | 50118 |
| t1 | PRIMARY | id | 102046 |
+------------+--------------+-------------+-------------+8 rows in set (0.00 sec)
可以看到,索引idx_u1是一个组合索引,字段r1在此索引里的基数只有5,而主键的基数是10W。索引idx_u1或者索引idx_r1的基数都非常低,也就是说对每一条对应的r1字段匹配,都会扫描很多行记录出来,进而导致基于字段r的索引可选择性非常差。所以此时针对这条SQL的优化,单单从语句层面已经没有办法继续下去,这种情况就需要DBA和业务一起联动来进一步讨论能否选择其他字段来做过滤条件、减少这条SQL的运行频次等手段来进行非常规优化。
第二个问题:如果索引选择合适或者说过滤条件很优化,那扫描行数也会相应减少。这条SQL过滤条件非常简单,只有一个等值检索,对于关系表来讲,如果业务逻辑不改,还是用原来的索引来过滤,也算是优化到了尽头。
最后来到第三个问题:针对EXTRA栏里显示的必须要优化的信息进行二次优化。比如我们修改过的SQL,EXTRA栏显示"using filesort",避免排序只需要把被排序字段加现有索引上即可, 这样的好处是排序直接走索引表本身的顺序而不用二次排序。
debian-ytt1:ytt>alter table t1 add key idx_r1_id_desc (r1,id desc);
Query OK, 0 rows affected (1.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
我们再重新看下执行计划:
debian-ytt1:ytt>desc select * from t1 where r1 = 4 order by id desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: idx_r1,idx_u1,idx_r1_id_desc
key: idx_u1
key_len: 5
ref: const
rows: 29324
filtered: 100.00
Extra: Using filesort1 row in set, 1 warning (0.00 sec)
虽然我们加了新索引,不过MySQL依然选择索引idx_u1,"using filesort"并没有拿掉。
这里只能证明一点:基于表t1当前的统计信息,执行计划选择了走索引idx_u1,而不是索引idx_r1_id_desc。也就是说针对这条SQL来讲,优化器综合判断排序比不排序反而要高效。至于为什么排序更高效,我们继续来看。
那强制用这个新索引的执行计划又是什么样呢?
debian-ytt1:ytt>desc select * from t1 force index(idx_r1_id_desc) where r1 = 4 order by id desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: idx_r1_id_desc
key: idx_r1_id_desc
key_len: 5
ref: const
rows: 32176
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
从执行计划结果来看,虽然避免了排序,但是扫描行数比用索引idx_u1要多。这里就在于要消耗更多CPU来排序、还是消耗更多IO来扫更多行两者之间做个权衡。从EXPLAIN结果暂时还看不到这些信息,可以更进一步用EXPLAIN ANALYZE语句来看这条SQL选择不同索引的成本信息来做个对比:
debian-ytt1:ytt>desc analyze select * from t1 force index(idx_r1_id_desc) where r1 = 4 order by id desc\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on t1 using idx_r1_id_desc (r1=4) (cost=3434.35 rows=32176) (actual time=0.225..32.290 rows=17093 loops=1)
1 row in set (0.04 sec)
debian-ytt1:ytt>desc analyze select * from t1 where r1 = 4 order by id desc\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: t1.id DESC (cost=3149.15 rows=29324) (actual time=59.961..62.682 rows=17093 loops=1)
-> Index lookup on t1 using idx_u1 (r1=4) (actual time=0.046..42.779 rows=17093 loops=1)
1 row in set (0.07 sec)
很显然,避免排序的成本为3434.25,而排序的成本是3149.15,MySQL基于成本生成了最优匹配的执行计划,那我们新加的这个索引就没有意义,可以删掉了。
如果您认为这篇文章有些帮助,还请不吝点下文章末尾的"点赞"