使用explain执行计划对SQL进行性能分析

一、什么是执行计划

执行计划是用于计算数据库优化器处理SQL的资源成本，在MySQL中使用执行计划的命令是explain（也可以写为desc、describe），通过该命令可以查看SQL在真正执行时所需要经历的一系列过程，通过执行结果可以看出一条复杂的SQL查找顺序、是否有通过索引查询等信息。执行计划命令所带的SQL并不会真正执行，所以不会担心占用系统资源，用于对SQL进行分析优化非常有用。如下示例：

mysql > EXPLAIN SELECT id,name FROM table;  #explain后面接上SQL即可

二、explain命令解析

explain执行计划分为了多列，以下是各列的说明

· id：id值相同的为同组SQL，会从上往下执行；如果存在子查询，id编号会递增，ID大的SQL会优先执行。

· select_type：查询类型，SIMPLE为不包含UNION或子查询的简单查询；PRIMARY为关联查询或包含子查询的语句中最外层的那条语句；UNION为联合查询中第二个以及后面的查询；SUBQUERY为从句中包含的子查询。

· table：SQL所涉及到的表，一条SQL可能会涉及多张表，可以看这个分析具体是查哪张表有问题

· type：查询类型。按照执行效率从高到低为const（使用主键或唯一索引进行查询，可以直接返回需要查询的某条记录）、eq_ref（多表查询时使用主键或唯一索引进行查询）、ref（基于普通索引等值查询，比如包含了=的语句）、range（基于索引进行范围查询，比如包含了>、<、like、in、between、and、or的语句）、index（对某一个有索引的列进行遍历）、all（对整张表全表扫描）。通常需要对ALL或者INDEX类型做优化，至少达到range级别。

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

· key_len：索引长度，主要是对联合索引进行分析，越短越好，通常大于100需要优化

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

· rows：SQL查询需要回表的次数

· filtered：扫描出来的行数与实际满足查询条件行数的比例，100%为最好情况。比如扫描了100行数据，有50行满足查询要求，那么显示为50

· extra：额外信息，如果出现Using temporary表示查询使用了临时表来存储结果，通常发生在对不同的列进行ORDER BY；如果出现Using filesort表示查询使用了order by、group by语句排序，但是没有可用的索引，此类查询比较消耗CPU，建议建立联合索引；如果出现Using index表示查询使用了覆盖索引，该SQL无需回表，性能最好；如果出现Using join buffer (Block Nested Loop) 代表多表 JOIN 连接没有走索引

三、索引成本导致优化器用错索引问题

 

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;