MySQL入门教程（21）使用EXPLAIN执行计划进行SQL性能分析

一、MySQL EXPLAIN执行计划介绍

MySQL的执行计划可以用于计算出数据库自身优化器在处理SQL时的资源成本，对于SQL优化起到关键作用。在MySQL中使用执行计划的命令是EXPLAIN（也可以写为desc、describe），通过该命令可以查看SQL在真正执行时所需要经历的一系列过程，通过执行计划的结果可以分析出一条SQL在进行查询时的查找顺序、是否有使用索引等有用信息。从5.6.3版本之后，执行计划不光可以对SELECT语句生效，对于UPDATE、DELETE语句也是可以生效的。在使用执行计划对SQL进行分析时其SQL并不会真正执行，所以不用担心占用系统资源或者修改了数据

执行计划分为了传统格式、TREE格式和JSON格式，虽然最常用的是传统格式，但是相比内容最齐全的JSON格式来说，缺少了重要的成本指标。各格式的使用语法：

#使用默认的传统格式查看执行计划，缺少SQL成本信息
mysql > EXPLAIN SELECT id,name FROM table;

#以JSON格式进行输出，信息比默认的传统模式更详细，增加了query_cost查看SQL成本信息 
mysql > EXPLAIN FORMAT=JSON SELECT id,name FROM table;

#8.0.16开始支持的tree模式，层次结构稍有变化
mysql > EXPLAIN FORMAT=TREE SELECT id,name FROM table;

MySQL 8.0新增 explain analyze 执行计划，区别在于会真实进行SQL的执行，只是不返回查询结果，而是一个执行计划，这样可以得到更真实的执行流程

二、MySQL EXPLAIN执行计划说明

下图是一个执行计划语句执行后的返回结果，可以看到包含了多列信息，每一列信息所代表的含义各不相同

1、id列

· 每个ID表示一个独立的查询，通常有多少个SELECT关键字就会有多少个ID（部分情况下优化器会自动进行改写，存在ID数比SELECT关键词的数量少的情况），所以ID数越少越好。

· ID大的SQL语句会优先执行，ID值相同的为同组SQL按照从上往下的顺序执行。

· 如果有使用临时表（比如UNION操作）等情况会出现ID为空的记录，这些语句会最后执行。排在前面的为驱动表，后面的是被驱动表。

2、select_type列

在id列中每个id都代表了一个SELECT的出现，而在select_type列中则说明了每一个SELECT语句的查询类型

· SIMPLE：不包含UNION或子查询的简单查询

· PRIMARY：包含子查询或者UNION语句时，最外层的查询语句，也是主查询语句

· UNION、UNION RESULT：UNION 是指UNION语句中的后表，比如A UNION B，那么A为PRIMARY，B为UNION；UNION RESULT则是UNION去重操作产生的临时表，使用UNION ALL时没有UNION RESULT

· SUBQUERY：子查询不依赖外部查询

· DEPENDENT SUBQUERY：子查询的结果依赖外部查询，即子查询有多表关联查询

· MATERIALIZED：需要临时表来存储结果集，以便下一步操作可以使用。通常是子查询语句包含了聚合函数（如SUM()和COUNT()）或GROUP BY子句，然后优化器会将子查询物化之后再与外层查询进行连接

3、table列

SQL所涉及到的表，可以看这个分析具体是查哪张表有问题。如果一个SELECT关键词涉及多张表，那么在该关键词所属的ID下就会出现多个表的情况。如果table列显示为derived表示该表是来自于一个查询的结果

4、type列

执行计划最关键的列，记录了SQL运行时对某个表数据的查找方式，比如是否有全表扫描。不同的查找方式性能不同，按照性能从高到底排序为system-->const-->eq_ref-->ref-->range-->index-->all，通常需要对ALL或者INDEX类型做优化，至少达到range级别

· system：被查询的表中只有一条数据，并且该表使用的是精确的存储引擎进行数据统计，比如MyISAM、Memory，那么查询方法就为system

· const：根据主键或者唯一索引与数据进行等值匹配时，如id为主键，查询语句使用where id = 1，这种查询方法为const
· eq_ref：通过唯一索引进行多表查询，被驱动表的查询方式为eq_ref
· ref：基于普通索引进行等值查询，比如age字段有二级索引，使用查询语句为where age = 18
· range：基于索引进行范围查询，比如包含了>、<、like、in、between、and、or的语句。如果在联合索引中使用了范围查询，那么范围查询之后的字段即便有索引也会失效

· index：对索引列进行全表扫描，需要优化
· all：对整张表全表扫描，最糟糕的情况

5、possible_keys与key列

分别显示了可能会用到的索引和实际用到的索引。如果possible_keys非空而key为空，则要检查语句是否存在索引失效的情况

6、key_len列

计算索引长度，对单列索引没有意义，主要用于计算联合索引命中情况。比如一个包含4个字段的联合索引，可以通过key_len计算具体哪些列的索引有被使用。

比如字段'user_name char(20) NOT NULL DEFAULT'，由于char(20)且非空，utf8字符集占用3个字节，那么最终长度就是20*3=60。如果允许NULL需要额外+1字节，VARCHAR变长额外+2字节。又假设字段类型为varchr(10)、允许NULL、字符集为UTF8，那么索引长度就是10 * 3 +1（NULL）+2（变长字段）；有字段为varchr(10)且不允许NULL，那么长度就是10 * 3 +2（变长字段）

CREATE TABLE `t1` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`name` char(20) NOT NULL DEFAULT '', 
`name1` char(20) DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY idx_key_name_name1 (`name`,`name1`) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8 

EXPLAIN select * from t1 where name='atlas' and name1='yayun'; # explain中key_len=121，计算方式为20 * 3 + (20 * 3 +1)=121

7、ref列

多表查询时表之间字段引用关系，显示索引哪一列被使用了

8、rows列

预估需要扫描的数据行数，也基本是回表的次数，值越小越好。如果表的.ibd文件存在大量碎片空洞，需要使用optimize回收空间后这个预估值才会更准确

9、filtered列

经过rows列扫描后满足查询条件的实际行数百分比，这个比例越高代表无效扫描的次数越少。如rows列显示扫描10000行，filterd列显示为50，代表有50%的行是有效的，即有5000行满足查询要求。该列对于多表连接的意义更大，比如t1表是驱动表、t2表是被驱动表，t1表中rows列为9688，filtered为10，代表着驱动表t1需要进行9688*0.1=968次的查询

10、extra列

额外信息列
· Using temporary：表示需要使用临时表来存储查询结果，通常是执行DISTINCT去重或者GROUP BY等操作但是缺少索引导致
· Using filesort：查询语句中使用到了排序语句，但是需要进行排序的字段上没有索引，建议where哪些字段就order by哪些字段。但是需要注意的是所有的排序操作都是在WHERE条件过滤后再执行的，所以如果WHERE条件能过滤大部分数据，那么剩下的少量数据出现了排序影响也不大

· Using index：使用了覆盖索引，无需回表，该查询语句的性能非常好
· Using join buffer：代表多表 JOIN 连接没有走索引
· Using where：在查询语句中使用到了where子句，但是该字段不存在索引，可考虑为该字段增加索引进行优化
· Using index condition：使用了索引下推特性，关于索引下推特性的介绍可以参考《MySQL入门教程（8）MySQL索引原理与索引使用原则》

# 创建一个复合索引 
alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ; 

# using filesort 
explain select *from test02 where a1='' order by a3 ; 
explain select *from test02 where a2='' order by a3 ; 

# 无using filesort 
explain select *from test02 where a1='' order by a2 ; 

# using temporary explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; 

# 无using temporary 
explain select a1 from test02 where a1 in ('1','2','3') group by a1 ; 

# where后面是a2和a4，按照SQL执行顺序先查出了a2和a4然后再对其进行分组，所以不需要temporary 
explain select * from test03 where a2=2 and a4=4 group by a2,a4; 

# where后面是a2和a4，但是最后分组是a3字段，此时确实相关数据，因此需要临时表，出现using temporary 
explain select * from test03 where a2=2 and a4=4 group by a3;

三、索引成本导致优化器用错索引问题
1、索引基数
在执行SQL前优化器会分析所有可能的执行计划，然后根据CBO（基于成本的优化器）方法选择一个它认为成本最低的方法去执行，如下语句可以查看表中索引的基数，其中cardinality列就是语句根据索引可能使用到的查询可能性，数值越大越接近表的行数，说明区分度越高

#查看表中索引的基数 
show index from table_name;

2、索引基数会影响区分度
如果explain预估的rows值跟实际情况差距比较大，或者原本应该选择索引的语句最终放弃了索引，一般是因为索引统计不准确导致的。还有一种情况就是如果全表扫描的成本比二级索引成本低，就会存在有索引但是不使用的问题（为什么全表扫描比二级索引查询快？ 因为二级索引需要回表，当回表的记录数非常大时，成本就会比直接扫描要慢，因此这取决于回表的记录数）

# 查看成本方法示例
EXPLAIN FORMAT=json SELECT o_custkey,SUM(o_totalprice) FROM orders GROUP BY o_custkey

3、更新索引统计

在有些时候会因为索引统计信息不同导致同样的语句出现了不同的执行计划，这个时候可以使用analyze table重新统计索引信息，该操作会重新计算索引基数，解决部分场景下索引预估出现了明显问题的情况

analyze table test_table  #重新统计索引信息

4、强制索引
大部分情况可以通过analyze命令解决，或者强制指定索引进行查询。甚至还可以通过删掉不必要的索引来规避优化器选错索引的问题

select * from tast_table force index(a) where a between 1 and 1000 and b between 50000 and 100000 order by b limit 1;