前提:本文所有的案例均在MySQL5.7版本下实践
使用explain命令查看SQL语句的执行情况,可以帮助开发人员更快地定位到SQL执行效率欠佳的原因,本文对explain命令Extra字段的常见取值进行详解
SQL执行
SQL的编写过程是这样的:
select dinstinct from join on where group by having order by limit
而SQL的解析过程的顺序是这样的:
from on join where group by having select dinstinct order by limit
创建一张test02表,给表中的字段加上单值索引
create table test02
(
a1 char(3),
a2 char(3),
a3 char(3),
index idx_a1(a1),
index idx_a2(a2),
index idx_a3(a3)
);
1、using filesort
如果在Extra字段中出现了using filesort,说明SQL性能消耗大,它表示需要额外的一次排序或查询,常见于order by语句中
执行SQL,根据a1查询,然后根据a1排序,发现并没有出现using filesort
explain select * from test02 where a1 ='' order by a1 ;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test02 | NULL | ref | idx_a1 | idx_a1 | 10 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
更改SQL,根据a1查询,然后根据a2排序,出现了using filesort,这是为什么呢?
原因:查看SQL的解析顺序,先执行where子句,当查询完后,再执行order by子句,发现需要通过a2字段来进行排序,所以需要额外的一次查找或者称之为额外的一次排序,所以会出现using filesort
explain select * from test02 where a1 ='' order by a2 ;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | test02 | NULL