全网最全面、最细致的EXPLAIN解读
explain作为mysql的性能分析神器，读懂其结果是很有必要的，然而我在各种搜索引擎上竟然找不到特别完整的解读。都是只有重点，没有细节（例如type的取值不全、extra缺乏完整的介绍等）。
所以，我肝了将近一个星期，整理了一下。这应该是全网最全面、最细致的explain解读文章了，下面是全文。
文章比较长，建议收藏。
tips
本文基于mysql 8.0编写，理论支持mysql 5.0及更高版本。
explain使用
explain可用来分析sql的执行计划。格式如下：
{explain | describe | desc} tbl_name [col_name | wild] {explain | describe | desc} [explain_type] {explainable_stmt | for connection connection_id} {explain | describe | desc} analyze select_statement explain_type: { format = format_name} format_name: { traditional | json | tree} explainable_stmt: { select statement | table statement | delete statement | insert statement | replace statement | update statement}
示例：
explain format = traditional json select tt.ticketnumber, tt.timein, tt.projectreference, tt.estimatedshipdate, tt.actualshipdate, tt.clientid, tt.servicecodes, tt.repetitiveid, tt.currentprocess, tt.currentdpperson, tt.recordvolume, tt.dpprinted, et.country, et_1.country, do.custname from tt, et, et as et_1, do where tt.submittime is null and tt.actualpc = et.employid and tt.assignedpc = et_1.employid and tt.clientid = do.custnmbr;
结果输出展示：
字段 format=json时的名称 含义
id select_id 该语句的唯一标识
select_type 无 查询类型
table table_name 表名
partitions partitions 匹配的分区
type access_type 联接类型
possible_keys possible_keys 可能的索引选择
key key 实际选择的索引
key_len key_length 索引的长度
ref ref 索引的哪一列被引用了
rows rows 估计要扫描的行
filtered filtered 表示符合查询条件的数据百分比
extra 没有 附加信息
结果解读
id
该语句的唯一标识。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。
select_type
查询类型，有如下几种取值：
查询类型 作用
simple 简单查询（未使用union或子查询）
primary 最外层的查询
union 在union中的第二个和随后的select被标记为union。如果union被from子句中的子查询包含，那么它的第一个select会被标记为derived。
dependent union union中的第二个或后面的查询，依赖了外面的查询
union result union的结果
subquery 子查询中的第一个 select
dependent subquery 子查询中的第一个 select，依赖了外面的查询
derived 用来表示包含在from子句的子查询中的select，mysql会递归执行并将结果放到一个临时表中。mysql内部将其称为是derived table（派生表），因为该临时表是从子查询派生出来的
dependent derived 派生表，依赖了其他的表
materialized 物化子查询
uncacheable subquery 子查询，结果无法缓存，必须针对外部查询的每一行重新评估
uncacheable union union属于uncacheable subquery的第二个或后面的查询
table
表示当前这一行正在访问哪张表，如果sql定义了别名，则展示表的别名
partitions
当前查询匹配记录的分区。对于未分区的表，返回null
type
连接类型，有如下几种取值，性能从好到坏排序如下：
1 system：该表只有一行（相当于系统表），system是const类型的特例
2 const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
3 eq_ref：当使用了索引的全部组成部分，并且索引是primary key或unique not null 才会使用该类型，性能仅次于system及const。
-- 多表关联查询，单行匹配select * from ref_table,other_table where ref_table.key_column=other_table.column; -- 多表关联查询，联合索引，多行匹配select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;
4 ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
-- 根据索引（非主键，非唯一索引），匹配到多行select * from ref_table where key_column=expr; -- 多表关联查询，单个索引，多行匹配select * from ref_table,other_table where ref_table.key_column=other_table.column; -- 多表关联查询，联合索引，多行匹配select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;
tips
最左前缀原则，指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3)，那么，如果查询条件是：
•where column1 = 1、where column1= 1 and column2 = 2、where column1= 1 and column2 = 2 and column3 = 3 都可以使用该索引；•where column2 = 2、where column2 = 1 and column3 = 3就无法匹配该索引。
5 fulltext：全文索引
6 ref_or_null：该类型类似于ref，但是mysql会额外搜索哪些行包含了null。这种类型常见于解析子查询
select * from ref_table where key_column=expr or key_column is null;
7 index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引
8 unique_subquery：该类型和eq_ref类似，但是使用了in查询，且子查询是主键或者唯一索引。例如：
value in (select primary_key from single_table where some_expr)
9 index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引
value in (select key_column from single_table where some_expr)
10 range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有between子句或where子句里有>、>=、<、 5
38 using where with pushed condition
仅用于ndb
39 zero limit
该查询有一个limit 0子句，不能选择任何行
explain select name from resource_template limit 0
扩展的explain
explain可产生额外的扩展信息，可通过在explain语句后紧跟一条show warning语句查看扩展信息。
tips
•在mysql 8.0.12及更高版本，扩展信息可用于select、delete、insert、replace、update语句；在mysql 8.0.12之前，扩展信息仅适用于select语句；•在mysql 5.6及更低版本，需使用explain extended xxx语句；而从mysql 5.7开始，无需添加extended关键词。
使用示例：
mysql> explain select t1.a, t1.a in (select t2.a from t2) from t1g*************************** 1. row *************************** id: 1 select_type: primary table: t1 type: indexpossible_keys: null key: primary key_len: 4 ref: null rows: 4 filtered: 100.00 extra: using index*************************** 2. row *************************** id: 2 select_type: subquery table: t2 type: indexpossible_keys: a key: a key_len: 5 ref: null rows: 3 filtered: 100.00 extra: using index2 rows in set, 1 warning (0.00 sec) mysql> show warningsg*************************** 1. row *************************** level: note code: 1003message: /* select#1 */ select `test`.`t1`.`a` as `a`, (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), (`test`.`t1`.`a` in on where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) as `t1.a in (select t2.a from t2)` from `test`.`t1`1 row in set (0.00 sec)
由于show warning的结果并不一定是一个有效sql，也不一定能够执行（因为里面包含了很多特殊标记）。特殊标记取值如下：
1
自动生成的临时表key
2(expr)
表达式（例如标量子查询）执行了一次，并且将值保存在了内存中以备以后使用。对于包括多个值的结果，可能会创建临时表，你将会看到的字样
3(query fragment)
子查询被转换为exists
4(query fragment)
这是一个内部优化器对象，对用户没有任何意义
5(query fragment)
使用索引查找来处理查询片段，从而找到合格的行
6(condition, expr1, expr2)
如果条件是true，则取expr1，否则取expr2
7(expr)
验证表达式不为null的测试
8(query fragment)
使用子查询实现
9materialized-subquery.col_name
在内部物化临时表中对col_name的引用，以保存子查询的结果
10(query fragment)
使用主键来处理查询片段，从而找到合格的行
11(expr)
这是一个内部优化器对象，对用户没有任何意义
12/* select#n */ select_stmt
select与非扩展的explain输出中id=n的那行关联
13outer_tables semi join (inner_tables)
半连接操作。inner_tables展示未拉出的表。详见“optimizing subqueries, derived tables, and view references with semijoin transformations”[17]
14
表示创建了内部临时表而缓存中间结果
当某些表是const或system类型时，这些表中的列所涉及的表达式将由优化器尽早评估，并且不属于所显示语句的一部分。但是，当使用format=json时，某些const表的访问将显示为ref。
估计查询性能
多数情况下，你可以通过计算磁盘的搜索次数来估算查询性能。对于比较小的表，通常可以在一次磁盘搜索中找到行（因为索引可能已经被缓存了），而对于更大的表，你可以使用b-tree索引进行估算：你需要进行多少次查找才能找到行：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1
在mysql中，index_block_length通常是1024字节，数据指针一般是4字节。比方说，有一个500,000的表，key是3字节，那么根据计算公式log(500,000)/log(1024/3*2/(3+4)) + 1 = 4次搜索。
该索引将需要500,000 * 7 * 3/2 = 5.2mb的存储空间（假设典型的索引缓存的填充率是2/3），因此你可以在内存中存放更多索引，可能只要一到两个调用就可以找到想要的行了。
但是，对于写操作，你需要四个搜索请求来查找在何处放置新的索引值，然后通常需要2次搜索来更新索引并写入行。
前面的讨论并不意味着你的应用性能会因为log n而缓慢下降。只要内容被os或mysql服务器缓存，随着表的变大，只会稍微变慢。在数据量变得太大而无法缓存后，将会变慢很多，直到你的应用程序受到磁盘搜索约束（按照log n增长）。为了避免这种情况，可以根据数据的增长而增加key的。对于myisam表，key的缓存大小由名为key_buffer_size的系统变量控制，详见section 5.1.1, “configuring the server”[18]
参考文档
•explain output format[19]•explain statement[20]•extended explain output format[21]•estimating query performance[22]•mysql中explain执行计划中额外信息字段(extra)详解[23]•explain参数详解[24]•what does eq_ref and ref types mean in mysql explain[25]•面试官：不会看 explain执行计划，简历敢写 sql 优化？
原文标题：全网最全 | mysql explain 完全解读
文章出处：【微信公众号：数据分析与开发】欢迎添加关注！文章转载请注明出处。
探维王雨晴：激光雷达助力低速无人驾驶产业规模化发展
Red Eye Camera开发日记之API移植I2C和关键接口函数
技术 | 运放放大器应该注意的问题,你知道如何避免吗?
我国千兆固定宽带接入用户数快速提升，用户总数达4.76亿户
ZMC408SCAN光纤激光器如何实现激光能量的控制
全网最全面、最细致的EXPLAIN解读
如何使用ArduinoUNO和SD卡模块播放音乐
电源可靠性评估
DP610数字防爆对讲机的整体介绍
Microchip推出业界唯一可透过手套和液体工作的电容式触
nokia全球第一部智能手机！
315/433M 无线发射芯片XL4456的功能介绍
选取一款合适的虹科Dimetix激光测距传感器
卫星连接弥合了与服务不足的物联网市场的差距
STM32L151CBT6A ST ARM微控制器的详细参数
什么是三防漆_三防漆有哪些好处_哪些应用可从三防漆受益？
减速机渗漏也能快速治理，效果令人刮目相看
基于nRF24L01和FT245BM的无线文件传输系统设计
华为P9系列出货量超1千万 2017年三强对决大屏手机
恒讯科技分析：CDN为什么对直播服务器传输很重要？
所以，我肝了将近一个星期，整理了一下。这应该是全网最全面、最细致的explain解读文章了，下面是全文。
文章比较长，建议收藏。
tips
本文基于mysql 8.0编写，理论支持mysql 5.0及更高版本。
explain使用
explain可用来分析sql的执行计划。格式如下：
{explain | describe | desc} tbl_name [col_name | wild] {explain | describe | desc} [explain_type] {explainable_stmt | for connection connection_id} {explain | describe | desc} analyze select_statement explain_type: { format = format_name} format_name: { traditional | json | tree} explainable_stmt: { select statement | table statement | delete statement | insert statement | replace statement | update statement}
示例：
explain format = traditional json select tt.ticketnumber, tt.timein, tt.projectreference, tt.estimatedshipdate, tt.actualshipdate, tt.clientid, tt.servicecodes, tt.repetitiveid, tt.currentprocess, tt.currentdpperson, tt.recordvolume, tt.dpprinted, et.country, et_1.country, do.custname from tt, et, et as et_1, do where tt.submittime is null and tt.actualpc = et.employid and tt.assignedpc = et_1.employid and tt.clientid = do.custnmbr;
结果输出展示：
字段 format=json时的名称 含义
id select_id 该语句的唯一标识
select_type 无 查询类型
table table_name 表名
partitions partitions 匹配的分区
type access_type 联接类型
possible_keys possible_keys 可能的索引选择
key key 实际选择的索引
key_len key_length 索引的长度
ref ref 索引的哪一列被引用了
rows rows 估计要扫描的行
filtered filtered 表示符合查询条件的数据百分比
extra 没有 附加信息
结果解读
id
该语句的唯一标识。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。
select_type
查询类型，有如下几种取值：
查询类型 作用
simple 简单查询（未使用union或子查询）
primary 最外层的查询
union 在union中的第二个和随后的select被标记为union。如果union被from子句中的子查询包含，那么它的第一个select会被标记为derived。
dependent union union中的第二个或后面的查询，依赖了外面的查询
union result union的结果
subquery 子查询中的第一个 select
dependent subquery 子查询中的第一个 select，依赖了外面的查询
derived 用来表示包含在from子句的子查询中的select，mysql会递归执行并将结果放到一个临时表中。mysql内部将其称为是derived table（派生表），因为该临时表是从子查询派生出来的
dependent derived 派生表，依赖了其他的表
materialized 物化子查询
uncacheable subquery 子查询，结果无法缓存，必须针对外部查询的每一行重新评估
uncacheable union union属于uncacheable subquery的第二个或后面的查询
table
表示当前这一行正在访问哪张表，如果sql定义了别名，则展示表的别名
partitions
当前查询匹配记录的分区。对于未分区的表，返回null
type
连接类型，有如下几种取值，性能从好到坏排序如下：
1 system：该表只有一行（相当于系统表），system是const类型的特例
2 const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
3 eq_ref：当使用了索引的全部组成部分，并且索引是primary key或unique not null 才会使用该类型，性能仅次于system及const。
-- 多表关联查询，单行匹配select * from ref_table,other_table where ref_table.key_column=other_table.column; -- 多表关联查询，联合索引，多行匹配select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;
4 ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
-- 根据索引（非主键，非唯一索引），匹配到多行select * from ref_table where key_column=expr; -- 多表关联查询，单个索引，多行匹配select * from ref_table,other_table where ref_table.key_column=other_table.column; -- 多表关联查询，联合索引，多行匹配select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;
tips
最左前缀原则，指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3)，那么，如果查询条件是：
•where column1 = 1、where column1= 1 and column2 = 2、where column1= 1 and column2 = 2 and column3 = 3 都可以使用该索引；•where column2 = 2、where column2 = 1 and column3 = 3就无法匹配该索引。
5 fulltext：全文索引
6 ref_or_null：该类型类似于ref，但是mysql会额外搜索哪些行包含了null。这种类型常见于解析子查询
select * from ref_table where key_column=expr or key_column is null;
7 index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引
8 unique_subquery：该类型和eq_ref类似，但是使用了in查询，且子查询是主键或者唯一索引。例如：
value in (select primary_key from single_table where some_expr)
9 index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引
value in (select key_column from single_table where some_expr)
10 range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有between子句或where子句里有>、>=、<、 5
38 using where with pushed condition
仅用于ndb
39 zero limit
该查询有一个limit 0子句，不能选择任何行
explain select name from resource_template limit 0
扩展的explain
explain可产生额外的扩展信息，可通过在explain语句后紧跟一条show warning语句查看扩展信息。
tips
•在mysql 8.0.12及更高版本，扩展信息可用于select、delete、insert、replace、update语句；在mysql 8.0.12之前，扩展信息仅适用于select语句；•在mysql 5.6及更低版本，需使用explain extended xxx语句；而从mysql 5.7开始，无需添加extended关键词。
使用示例：
mysql> explain select t1.a, t1.a in (select t2.a from t2) from t1g*************************** 1. row *************************** id: 1 select_type: primary table: t1 type: indexpossible_keys: null key: primary key_len: 4 ref: null rows: 4 filtered: 100.00 extra: using index*************************** 2. row *************************** id: 2 select_type: subquery table: t2 type: indexpossible_keys: a key: a key_len: 5 ref: null rows: 3 filtered: 100.00 extra: using index2 rows in set, 1 warning (0.00 sec) mysql> show warningsg*************************** 1. row *************************** level: note code: 1003message: /* select#1 */ select `test`.`t1`.`a` as `a`, (`test`.`t1`.`a`,`test`.`t1`.`a` in ( (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` where 1 having 1 ), (`test`.`t1`.`a` in on where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) as `t1.a in (select t2.a from t2)` from `test`.`t1`1 row in set (0.00 sec)
由于show warning的结果并不一定是一个有效sql，也不一定能够执行（因为里面包含了很多特殊标记）。特殊标记取值如下：
1
自动生成的临时表key
2(expr)
表达式（例如标量子查询）执行了一次，并且将值保存在了内存中以备以后使用。对于包括多个值的结果，可能会创建临时表，你将会看到的字样
3(query fragment)
子查询被转换为exists
4(query fragment)
这是一个内部优化器对象，对用户没有任何意义
5(query fragment)
使用索引查找来处理查询片段，从而找到合格的行
6(condition, expr1, expr2)
如果条件是true，则取expr1，否则取expr2
7(expr)
验证表达式不为null的测试
8(query fragment)
使用子查询实现
9materialized-subquery.col_name
在内部物化临时表中对col_name的引用，以保存子查询的结果
10(query fragment)
使用主键来处理查询片段，从而找到合格的行
11(expr)
这是一个内部优化器对象，对用户没有任何意义
12/* select#n */ select_stmt
select与非扩展的explain输出中id=n的那行关联
13outer_tables semi join (inner_tables)
半连接操作。inner_tables展示未拉出的表。详见“optimizing subqueries, derived tables, and view references with semijoin transformations”[17]
14
表示创建了内部临时表而缓存中间结果
当某些表是const或system类型时，这些表中的列所涉及的表达式将由优化器尽早评估，并且不属于所显示语句的一部分。但是，当使用format=json时，某些const表的访问将显示为ref。
估计查询性能
多数情况下，你可以通过计算磁盘的搜索次数来估算查询性能。对于比较小的表，通常可以在一次磁盘搜索中找到行（因为索引可能已经被缓存了），而对于更大的表，你可以使用b-tree索引进行估算：你需要进行多少次查找才能找到行：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1
在mysql中，index_block_length通常是1024字节，数据指针一般是4字节。比方说，有一个500,000的表，key是3字节，那么根据计算公式log(500,000)/log(1024/3*2/(3+4)) + 1 = 4次搜索。
该索引将需要500,000 * 7 * 3/2 = 5.2mb的存储空间（假设典型的索引缓存的填充率是2/3），因此你可以在内存中存放更多索引，可能只要一到两个调用就可以找到想要的行了。
但是，对于写操作，你需要四个搜索请求来查找在何处放置新的索引值，然后通常需要2次搜索来更新索引并写入行。
前面的讨论并不意味着你的应用性能会因为log n而缓慢下降。只要内容被os或mysql服务器缓存，随着表的变大，只会稍微变慢。在数据量变得太大而无法缓存后，将会变慢很多，直到你的应用程序受到磁盘搜索约束（按照log n增长）。为了避免这种情况，可以根据数据的增长而增加key的。对于myisam表，key的缓存大小由名为key_buffer_size的系统变量控制，详见section 5.1.1, “configuring the server”[18]
参考文档
•explain output format[19]•explain statement[20]•extended explain output format[21]•estimating query performance[22]•mysql中explain执行计划中额外信息字段(extra)详解[23]•explain参数详解[24]•what does eq_ref and ref types mean in mysql explain[25]•面试官：不会看 explain执行计划，简历敢写 sql 优化？
原文标题：全网最全 | mysql explain 完全解读
文章出处：【微信公众号：数据分析与开发】欢迎添加关注！文章转载请注明出处。
探维王雨晴：激光雷达助力低速无人驾驶产业规模化发展
Red Eye Camera开发日记之API移植I2C和关键接口函数
技术 | 运放放大器应该注意的问题,你知道如何避免吗?
我国千兆固定宽带接入用户数快速提升，用户总数达4.76亿户
ZMC408SCAN光纤激光器如何实现激光能量的控制
全网最全面、最细致的EXPLAIN解读
如何使用ArduinoUNO和SD卡模块播放音乐
电源可靠性评估
DP610数字防爆对讲机的整体介绍
Microchip推出业界唯一可透过手套和液体工作的电容式触
nokia全球第一部智能手机！
315/433M 无线发射芯片XL4456的功能介绍
选取一款合适的虹科Dimetix激光测距传感器
卫星连接弥合了与服务不足的物联网市场的差距
STM32L151CBT6A ST ARM微控制器的详细参数
什么是三防漆_三防漆有哪些好处_哪些应用可从三防漆受益？
减速机渗漏也能快速治理，效果令人刮目相看
基于nRF24L01和FT245BM的无线文件传输系统设计
华为P9系列出货量超1千万 2017年三强对决大屏手机
恒讯科技分析：CDN为什么对直播服务器传输很重要？