Explain详解

explain语句用于查看某个查询语句具体使用了什么执行计划

执行输出各列详解

table

每条记录对应一个表的查询，如果是两表连接查询，就会有两条记录，table对应查询表名

id

查询语句可能是单表查询，也可能是多表查询，查询语句中每出现一个select，就会为其分配一个唯一的id值；

• 连接查询：每个表对应一条记录，前一条记录是驱动表的记录，后一条是被驱动表的记录，都有唯一的id，如驱动表查询id为1，被驱动表查询id为1

• 子查询：子查询也会独立分配一个唯一id，如外部select查询id为1，内部子查询id就会为2（TODO 这里的唯一不是对表来说的吗？）

• 子查询被查询优化器重写为连接查询：子查询的id本来是应该是2，现在为1

• union查询：n个表查询中每个select查询对应一个唯一id，但因为union查询的特性（将所有查询结果集合并起来对结果集中的记录集进行去重，而这里去重是使用的临时表，所以会出现第n+1条记录，而它的id为null，表明这个临时表是为了合并两个查询的结果集而创建的，table表名为<union1,2...n>

• unionAll查询：没有去重的需要，和普通多表查询一样

select_type

表示当前记录的select扮演了什么角色

• simple：单表或连表查询，没有union/union all/子查询的

• primary：union/union all/子查询的大查询中最左边表的查询，一般是第一条执行输出的记录的类型

• union：union/union all查询除最左边表的查询

• union result：union查询中为去重而产生的临时表

• subquery：如果包含子查询的查询语句不能够转为对应的半连接semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化（物理化生成在内存或磁盘的临时表）的方案来执行该子查询（TODO：半连接和物化表），该子查询的第一个select关键字代表的查询类型就是subquery；如 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = ‘a‘;这个子查询只会被执行一次，因为是物化表

• dependent subqery：如果包含子查询的查询语句不能够转为对应的半连接semi-join的形式，并且该子查询是相关子查询，该子查询的第一个select关键字代表的查询类型就是dependent subquery；如 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = ‘a‘;这个子查询会被执行多次

• dependent union：在union/union all大查询中，如果每个小查询都与外部查询有关，如 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = ‘a‘ UNION SELECT key1 FROM s1 WHERE key1 = ‘b‘);第一个子查询SELECT key1 FROM s2 WHERE key1 = ‘a‘是dependent subquery， 第二个子查询SELECT key1 FROM s1 WHERE key1 = ‘b‘是dependent union，它们都与外部表的某个列有关

• derived：对于采用物化的方式执行的b包含派生表（from后查询表的结果集作为表）的查询，该派生表对于的子查询的select type；如 SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1; 其外层查询的select_type为primary（TODO ）

• MATERIALIZED：子查询执行后的物化表与外层查询进行连接查询时，如EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);

type

查询记录的访问方法，以InnoDB存储引擎为例，其单表查询的访问方法有system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL每种方法的代表的查询成本不同

• system：表中只有一条记录，且当前数据库使用的存储引擎的统计数据是精确的，如MyISAM/Memory

• const：主键或唯一二级 索引和常数等值匹配

• eq_ref：连接查询中，被驱动表的主键或唯一二级索引和常数等值匹配，如果是唯一二级联合索引，要所有索引列都匹配

• ref：普通二级索引与常数值等值匹配

• fulltext：全文索引

• ref_or_null：对普通二级索引等值匹配或null值匹配，唯一二级索引的null值匹配也是

• index_merge：单表访问时，某些场景中，可能使用Interaction/union/sort-union这三种索引合并的方式执行查询

• unique_subquery：针对一些包含in子查询的查询语句中，当查询优化器决定将in子查询转换为exits子查询，而且子查询使用到主键进行等值匹配；如SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = ‘a‘;

• index_subqery：与unique_subquery不同的是，子查询使用的是普通索引进行等值匹配

• range：使用索引获取某些范围区间的记录，如in查询或者大小比较区间

• index：索引覆盖，但需要扫描全部的索引记录，如查询条件和搜索条件都是索引列，不包含最左边那个列，只能扫描整个联合索引

• all：全表扫描

possible_keys / key

possible_keys指查询中可能使用到的索引有哪些，key表示实际使用到的索引；possible_keys中索引值越多，代表需要计算查询成本需要花费时间越多

key_len

当优化器决定使用哪个索引列，整个索引列的最大长度

• 固定长度的索引列，其实际占用的存储空间的最大长度就是g该固定值
• 指定字符集的变长类型列，该列实际占用最大存储空间就是字符集每字符字节数*变长变量声明大小
• 如果该列可以存储null值，它的key_len比bu不可以存储null值时多一个字节
• 变长字段，有2个字节的空间来存储该变长列的实际长度

如InnoDB行格式，字符集为utf8下，可为null的变长字段a类型为varchar(100)其key_len为100*3 + 2 + 1 = 303（TODO InnoDB行格式下，变长字段实际长度可能是1或2，但执行计划是在mysql sever中产生的，并不针对某个存储引擎）

ref

展示与索引列等值匹配的内容，如常数或某个列或函数func；但这只有当使用索引列等值匹配的条件去查询时，即访问方法为const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery

rows

查询优化器决定全表扫描或索引查询时，对应的预计全表扫描的行数或预计扫描的索引记录行数

filtered

单表查询中没有什么意义，主要在连表查询中驱动表对应的执行计划记录的filtered值，根据整个值可以计算出被驱动表预计执行次数，即驱动表的扇出数 = 驱动表查询的rows * filtered / 100

Extra

MySQL提供的额外信息，表明是如何执行给定的查询语句

• no table used：没有from语句时
• impossible where：查询语句的where永远为false时
• no matching min/max row：查询列表有min/max聚集函数，但where搜索条件中没有符合的记录
• using index：索引覆盖时，不需要回表
• using index condition：搜索条件出现索引但不能使用索引，即查询语句执行过程中将要使用索引条件下推这个特性的，如搜索条件与查询条件均用到索引，但部分不符合索引使用规范，只能回表筛选的，为了降低成本，MySQL会将步骤调整，先在索引中检测这些不符合索引规范的是否满足，再根据这个结果回表SELECT * FROM s1 WHERE key1 > ‘z‘ AND key1 LIKE ‘%b‘;
• using where：全表扫描时，where语句中有搜索条件
• using join buffer(Block Nested Loop)：当使用基于块的嵌套循环算法时，会分配一个内存块名为join buffer来加快查询速度
• not exits：当使用外连接时，where搜索条件中被驱动表的某个不允许为null的列值要求为null时
• Using intersect(...)、Using union(...)和Using sort_union(...)：使用intersect联合索引/使用union索引合并/使用sort-union索引合并
• zero limit：limit子句参数为0，查不出记录
• using filesort：无法使用索引排序，只能在内存或磁盘中进行排序，而在这种内存或磁盘中的排序被称为文件排序filesort，尽量使用索引排序替代文件排序；group by子句会在查询中默认添加order by子句，可以通过order by null消除，减少文件排序的成本
• using temporary：在distinct/group by/union等子句查询中，不能用到索引来去重或排序，只能创建内部临时表来执行查询
• Start temporary, End temporary：（TODO in查询转换为semi-join。。。）
• losescan：在将In子查询转为semi-join时，如果采用的是LooseScan执行策略 TODO
• FirstMatch(tbl_name)：TODO

Json格式的执行计划

可以通过explain format=json select...........来查看查询语句的执行成本

cost_info

"cost_info": {
    "read_cost": "1840.84", 	//read_cost=IO成本+检测rows*（1-filter）的CPU成本
    "eval_cost": "193.76",		//检测rows*filter的成本
    "prefix_cost": "2034.60",	//查询成本=read_cost+eval_cost（连表查询下被驱动表成本是整个连接								   //查询预计的成本）
    "data_read_per_join": "1M"	//此次查询要读的数据量
}

extend explain

在explain语句查询结果后，紧跟着show warnings；可以得到code，level，message信息，当Code值为1003时，Message字段展示的信息类似于查询优化器将我们的查询语句重写后的语句

Explain详解

原文：https://www.cnblogs.com/hangzhi/p/11279549.html