再谈MySQL之执行计划Explain

前言

我们经常会使用 Explain 去查看执行计划，这个众所周知。但在面试时问面试者，你用 Explain 主要是看什么？对方的回答大多是查看是否有使用到索引，这显然不是最好的答案

Explain 各个字段及其含义

• id : 表示 SQL 执行的顺序的标识， SQL 从大到小的执行
• select_type：表示查询中每个 select 子句的类型
• table：显示这一行的数据是关于哪张表的，有时不是真实的表名字
• type：表示 MySql 在表中找到所需行的方式，又称访问类型。常用的类型有：ALL, index, range, ref,eq_ref, const, system, NULL（从左到右，性能从差到好）
• possible_keys：指出 MySql 能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
• Key：key 列显示 MySql 实际决定使用的键（索引），如果没有选择索引，键是 NULL
• key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的）
• ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• rows：表示 MySql 根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，理论上行数越少，查询性能越好
• Extra：该列包含 MySql 解决查询的详细信息

在 Explain 执行计划中，我们重点关注 Type 和 Extra 两个字段，它们是核心

Explain 中的 Type

Explain 中的 Type 在 MySql 的官网解释为：连接类型（the join type）。它描述了找到所需数据使用的扫描方式。最为常见的扫描方式有

• system：说明数据已经加载到内存里，不需要进行磁盘 IO，这类扫描是速度最快的
• const：命中主键索引或者唯一索引，被连接的部分是一个常量值
• eq_ref：主键索引或者非空唯一索引等值扫描
• ref：非主键索引或非唯一索引等值扫描
• range：范围扫描，它是索引上的范围查询，它会在索引上扫描特定范围内的值
• index：索引树扫描，需要扫描索引树上的全部数据
• ALL：全表扫描

上面各类扫描方式由快到慢：system > const > eq_ref > ref > range > index > ALL

Explain 中的 Extra

Explain 中的 Extra 的值有

• Using index：说明 SQL 所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录
• Using where：说明 SQL 使用了 where 条件过滤数据

explain select * from account_user_base where id > 4;

• Using index condition：说明确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录

explain select * from account_user_security t1, account_user_base t2 where t1.user_id = t2.id;

• Using filesort：说明得到所需结果集，需要对所有记录进行文件排序。典型的，在一个没有建立索引的列上进行了 order by，就会触发 filesort，常见的优化方案是，在 order by 的列上添加索引，避免每次查询都全量排序。详情可以参考：MySql 中 order by 的原理
• Using temporary：说明需要建立临时表来暂存中间结果。这类 SQL 语句性能较低，往往也需要进行优化。典型的 group by 和 order by 同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集