mysql之explain执行计划 ​

引言 ：sql慢查询主要解决方法有：

1. 监控sql执行情况，然后发邮件或者短信通知，便于识别慢查询sql
2. 打开数据库慢查询日志，可以看到哪些sql执行速度效率低
3. 简化业务逻辑
4. 代码重构、优化
5. 异步处理
6. sql优化
7. 索引优化

其中第七种是通过explain关键字，查看执行计划，通过执行计划我们可以看到sql对应的表和索引情况，索引有没有执行，索引执行顺序和索引的类型等，以便于我们针对性优化

索引优化步骤 ​

1. 使用explain查看sql执行计划
2. 判断哪些索引使用不当
3. 优化sql、sql优化需要多次才能达到索引使用的最优值

explain介绍 ​

• explain可以使用于select、delete、insert、update、replace
• explain于可解释语句一起使用时，将显示来自于优化器的有关语句执行计划的信息，包括有关连接表以及何种顺序连接表的信息
• 当explain于非可解释语句一起使用时，它将显示在命令连接中执行语句的执行计划
• 对于select语句，explain可以显示其他执行计划的告警信息

explain语句执行计划时，有如下12个字段：

Explain执行计划字段含义 ​

重点理解及关注：type、key、key_len、Extra

id ​

该列的值是可解释语句的序号，比如：1、2、3、4等，它决定了表的执行顺序

某条sql的执行计划中一般会出现三种情况：

1. id相同：从上到下执行
2. id不同：先执行序号大的id，再执行序号小的id
3. id有相同的也有不相同的出现：先执行序号大的，遇到序号相同的，再从上到下执行

select_type ​

select_type一般会有一下几个值出现，其中绿色部分是经常出现的

SIMPLE ​

只在简单的查询语句出现，不包含子查询和UNION

PRIMARY和SUBQUERY ​

explain select * from test1 t1 where t1.id = (select id from test2 t2 where t2.id = 2)

• sql嵌套查询，最外层的表是PRIMARY【t1】，而最里面的子查询是SUBQUERY【t2】类型
• PRIMARY：查询语句中不包含UNION或者子查询的查询都算是PRIMARY类型，比如常见的表单查询和连接查询
• SUBQUERY：对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY

DERIVED ​

explain
select t1.* from test1 t1 inner join (select max(id) mid from test1 t3 group by id) t2 
on t1.id = t2.mid

• 对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED

UNION和UNION RESULT ​

explain
select * from test1
union 
select * from test2

• test2表是union关键字之后的查询，所有被标记为UINON
• test1是最主要的表，所以被标记为PRIMARY
• 而<union1,2>表示id=1和id=2的表UNION，其结果被标记为UNION RESULT
• UNION和UNION RESULT一般会成对出现
• id字段可以为null

table ​

• 表示输出行所引用的表的名称
• 特殊值：<union M,N>据有和id字段的行的M并集N；<derived N>用于与该行的派生表结果id的值N。派生表可能来自于FROM子句中的子查询；<subquery N>子查询的结果，其id为N

partitions ​

• 当前查询匹配记录的分区，对于未分区的表，返回null

type ​

• 执行结果最好到最坏的顺序是从上到下
• 重点掌握：system > const > eq_ref > ref >range > index > ALL

system ​

• 这种类型要求数据库表中只有一条数据，是const类型中的一个特例，一般情况下是不会出现的

const ​

• 通过一次索引就能找到数据，一般用于主键或者唯一索引作为条件的查询sql中

eq_ref ​

explain select * from test1 t1 inner join test1 t2 on t1.id = t2.id

• 常用于主键或唯一索引扫描
• 注意：const只索引一次，而eq_ref主键和主键匹配，由于表中有多条数据，一般情况下要索引多次，才能全部匹配上

ref ​

• 常用于非主键和非唯一索引扫描

range ​

explain select * from test2 where id between 1 and 3;

• 常用于范围查询，比如：between...and或in等操作

index ​

explain select name from test2;

• 全索引扫描

ALL ​

explain select * from test2;

• 全表扫描

possible_keys ​

• 该列表是可能的索引选择
• 此列完全独立于表的顺序，意味着possible_keys某些键无法与生成的表的顺序一起使用
• 如果此列是null，则没有相关的索引。可以通过检查该语句的where子句来检查是否引用了某些合适索引的列，从而提交查询性能

key ​

• 表示实际用到的索引
• 可能回出现possible_keys列为null，但是key不为null的情况

key_len ​

• 该列表示使用索引的长度。key列可以看出有没有使用索引，key_len可以进一步看出索引使用是否充分

决定key_len值的三个因素

1. 字符集
2. 长度
3. 是否为空

mysql常用字段占用字节数：

常用的字符集：

例：定数据库使用字符集utf8mb4，其长度为4个字节，计算结果：(255*4+2+1) + (255*4+2+1) = 2046

• 255：字段定义的长度
• 4：数据库使用的字符集
• +2：varchar类型可变长度，需要加2
• +1：字段默认为null，所以需要加1

计算结果如果低于长度值：说明使用索引不充分

ref ​

• 当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是索引列等职匹配的具体信息，比如：某一个常数或者某列

• 表示索引命中的列或者常量
• t1命中的索引是const常量，而t2命中的索引是sys库的t1表的id字段

rows ​

• 如果使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计要扫描的行数；如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引的记录行数
• 表示mysql认为执行查询必须检查的行数
• 对于InnoDB，此数字是估计值，可能不总是准确
• 数据越小越好

filtered ​

• 表示按条件过滤的表行的估计百分比。最大值100，这表示未过滤行。值从100减少表示过滤量增加

• rows显示了检查的估计行数，rows * fitered显示了与下表连接的行数

• 我们需要关注里面查询中驱动表对应的执行计划记录的filtered值，因为这直接影响了驱动表的扇出值。在rows一样的情况下，filtered越大，扇出的值越小，效率可能越高

Extra ​

• 该列包含有关mysql如何解析查询的其他信息，以下是几种比较常见的

No tables used ​

• 当查询语句中没有from子句时会出现

Impossible WHERE ​

• 表示where后面的条件一直都是false

No matching min/max row ​

• 当查询列表有min()或者max()聚集函数，但是没有匹配到对应的记录时会出现

Using filesort ​

• 表示按文件顺序，一般是指定的排序和索引排序不一致的情况下出现的

Using index ​

• 表示是否用了覆盖索引，即是否所有获取的列都走了索引

Using index condition ​

• 如果查询的执行过程中使用了索引条件下推(Index Condition Pushdown)，就会出现

Using temporary ​

• 表示是否使用了临时表，一般多见于order by和group by、union、distinct语句

Using where ​

• 当使用全表扫描执行查询时，如果查询语句包含where条件，就会出现
• 当使用索引访问执行查询时，如果where字句包含非索引列字段，也会出现

Using join buffer ​

• 表示是否使用连接缓冲，来自较早连接的表被部分读取到连接缓冲区中，然后从缓冲去中使用她们的行来与当前表执行连接

Not exists ​

• 当我们使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息

Using intersect(...)、Using union(...)和Using sort_union(...) ​

• 如果使用了索引合并执行查询，则会出现

Zero limit ​

• 当limit子句参数为0时，就会出现

总结-索引优化过程 ​

1. 先用慢查询日志定位具体需要优化的sql
2. 使用explain执行计划查询索引使用情况
3. 重点关注key、key_len、type、Extra这4列就能找到索引问题
4. 根据上1步找出索引问题优化sql
5. 再回到第2步

TIPS ​

索引-最左侧元素 ​

索引按照最左侧有限的方式匹配索引

组合索引 idx_name_sex = (name, sex)。
-- 走idx_name_sex索引
select * from test1 where name = 'jiliang' and sex = '男';
-- 不走idx_name_sex索引
select * from test1 where sex = '男'  and name = 'jiliang';