MySQL阅读笔记——11.Explain详解

　　MySQL经过 基于成本 和 基于规则 的优化后生成 执行计划 ，通过在语句前面加上Explain关键字可以查看执行计划：

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

 

id

• 查询语句每出现一个select语句，MySQL就会为它分配一个唯一id这个id作为explain第一列

• 连接查询时每个表都会对应explain中id相同的一条记录

• 子查询中每个select语句都会对应一个explain中的 唯一id

• 如果查询优化器对子查询语句进行重写转换为 连接查询 ，则在explain结果中的记录id全部相同

• union会对结果集去重，explain结果集会在原select语句基础上多出id为null的 临时表 记录

Explain每一条记录是一中查询，id相同的多条记录是连接查询（或者是能转换成连接查询的子查询），id不同的多条记录是子查询嵌套

select_type

• SIMPLE：不包含union、union all和 子查询 的查询都算SIMPLE，连接查询也算SIMPLE

• PRIMARY：包含union \ union all或者子查询的大查询，最左边的查询就是PRIMARY

• UNION：包含union \ union all或者子查询的大查询，除去最左边的查询其余小查询是UNION

• UNION RESULT：使用临时表完成union去重工作

• SUBQUERY：子查询不是相关子查询并且不能转换为semi-join（半连接）并且查询优化器使用 物化 方案执行该子查询，则该子查询的第一个select语句就是SUBQUERY,（由于该类型子查询会被物化，所以只会执行一遍）

• DEPENDENT SUBQUERY：子查询是相关子查询并且不能转换为semi-join（半连接），则该子查询的第一个select语句就是SUBQUERY SUBQUERY

• DEPENDENT UNION：子查询是union或union all并且都依赖外层查询，则该子查询中除了最左边的第一个查询，其余小查询都是DEPENDENT UNION

• DERIVED：物化 后派生表（from语句后的子查询）对应的 直接查询（派生表不一定 物化，还有可能与外层查询合并）

• MATERIALIZED：将 子查询物化 后与外层查询进行 连接查询 ，该子查询类型就是MATERIALIZED

• UNCACHEABLE SUBQUERY 和 UNCACHEABLE UNION：不常用

type

• system：当表只有一条记录并且表的存储引擎统计数是精确的，如MyISAM、Memory（InnoDB是ALL）

• const：根据 主键 或者 唯一二级索引 与常数列等值匹配

• eq_ref：连接查询 被驱动表 通过 主键 或 唯一二级索引 列等值匹配访问（如果被驱动表的主键或唯一二级索引是 联合索引 ，则 联合索引的所有列 必须等值比较）

• ref：通过 普通二级索引 与常量进行等值匹配来查询某个表

• ref_or_null：通过 普通二级索引 与常量进行等值匹配并且查询条件指定该列值可以是 NULL 来查询某个表

• index_merge：以Intersection、Union、Sort-Union三种索引合并的方式执行查询（就是将查询条件中用到每个条件进行查询，将结果集（主要是主键）进行 交集 、并集、并集-排序 操作，之后再进行 回表 或者直接返回结果（查询结果覆盖索引））

• unique_subquery：查询优化器将 IN子查询 转换为 EXISTS查询 并且子查询用到 主键 或者 唯一二级索引 等值匹配（IN相关子查询），执行该计划的子查询type就是unique_subquery

   EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';
   +----+--------------------+-------+-----------------+------------------+
  | id | select_type        | table | type            | possible_keys    |
  +----+--------------------+-------+-----------------+------------------+
  |  1 | PRIMARY            | s1    | ALL             | idx_key3         |
  |  2 | DEPENDENT SUBQUERY | s2    | unique_subquery | PRIMARY,idx_key1 |
  +----+--------------------+-------+-----------------+------------------+

• index_subquery：查询优化器将 IN子查询 转换为 EXISTS查询 并且子查询用到是 普通索引

• range：在索引上使用范围查询（IN查询可以看作是单点区间）

• index：结果集覆盖索引，但要扫描全部索引记录（查询条件中不符合联合索引 最左匹配原则 没有使用到索引，但是结果列是索引列不用 回表）

• ALL：没用到任何索引，全表扫描

除了index_merge之外，所有访问方法最多只用到一个索引

possible_keys和key

• possible_keys：表示可能查询用到的索引

• key：表示查询实际用到的索引

possible_keys越多表示查询优化器计算成本耗时越多，因此尽量删除不需要的索引

使用index访问，possible_keys是NULL，而key是查询涉及的索引（联合索引）

key_len

• 索引列如果是固定长度类型就是固定值，如果是变长字符集（如：utf8）就是最大存储空间

• 如果该列可以为NULL，则比不可以存储NULL值多1字节

• 变长索引字段会使用2字节存储实际长度（之前说过使用1~2字节，此处统一为2字节）

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref展示的是与索引列等值匹配的值，可能是const代表一个常数，可能是连接查询的另一个表主键，可能是函数（ref列为func）

rows

如果全表扫描就代表 预估 的扫描行数，如果使用索引执行计划，就代表 预估 执行计划扫描的 索引记录行数

filtered

• 如果使用索引执行扫描，则filtered列代表代表除去使用到索引条件外，满足其他条件的记录占比是多少（扇出数=filtered%*rows）

• 如果是全表扫描，则计算扇出时满足条件的记录数

Extra

• No tables used：查询语句没有from语句

• Impossible WHERE：where子句永远为false

• No matching min/max row：使用MIN或者MAX函数覆盖二级索引，但没有符合where条件的记录

• Using index：当 查询条件 和 搜索条件 只包含某个索引列，即覆盖索引（不需要 回表）

• Using index condition：索引条件下推（MySQL5.6新增）无法利用到索引的where条件（但是在索引中存储了该值）会在 回表 之前进行过滤，如：where中使用了like '%xxx'匹配联合索引的另外字段

• Using where：使用全表扫描，并且where中有搜索条件（未使用到索引）

• Using join buffer：连接查询，对 被驱动表 的访问不能有效利用索引，则会分配一块名叫join buffer的内存块加快查询速度

• Not exists：连接查询，where要求 被驱动表 某列等于NULL，而那个列又不允许为NULL

• Using intersect(...)、Using union(...)和Using sort_union(...)：该查询在括号内的列上进行了索引合并

• Zero limit：LIMIT子句参数为0

• Using filesort：order by无法使用到索引，只能在内存或者磁盘进行排序

• Using temporary：如果不能有效利用索引，会借助临时表（MySQL会在group by查询后默认添加order by，如果不想在group by后执行排序，要手动设置order by null）

• Start temporary, End temporary：查询优化器将IN查询转化为semi-join通过DuplicateWeedout策略建立临时表对外层查询记录去重，则驱动表为Start temporary,被驱动表为End temporary

• LooseScan：将IN查询转化为semi-join通过LooseScan策略，驱动表的执行计划

• FirstMatch(tbl_name)：将IN查询转化为semi-join通过FirstMatch策略，被驱动表的执行计划

通过在查询语句前面加上explain format=json可以看到语句的执行成本

在使用explain语句后紧接着通过show warnings可以查看查询计划的扩展信息，当Code为1003时，Message展示的是不标准的 查询优化器重写 后的语句

optimizer trace在MySQL5.6之后新增的用来查看 查询优化器 执行的整个过程，通过设置系统变量set optimizer_trace="enabled=on";来开启，通过SHOW VARIABLES LIKE 'optimizer_trace';来查看是否开启了该功能（默认关闭），开启后执行查询语句后可以通过SELECT * FROM information_schema.OPTIMIZER_TRACE查看优化器的具体执行过程，主要分成 prepare 、optimize 、execute 三个阶段，基于成本的优化主要集中在 optimize 阶段，单表查询在 optimize 阶段的 rows_estimation过程，多表连接查询在considered_execution_plans过程

---