大家都知道看懂sql执行计划对于优化的意义，本文将为您详解MySQL的执行计划。
一、MySQL查看执行计划的方法
1.1 执行计划查看方法
查看和读懂执行计划是进行数据库sql语句调优时的一个重要依据，MySQL的执行计划查看相对oracle简便很多，功能也相对简单。
MySQL是通过EXPLAIN命令查看执行计划，使用explain查看有两种方式：
（1）直接在查询语句之前直接加上EXPLAIN即可
EXPLAIN支持 SELECT,DELETE,INSERT,REPLACE和UPDATE语句。执行EXPLAIN时并没有真正的执行该后面的语句，因此可以安全的查看执行计划。
MySQL 8.0.18后，有一个新的特性explain analyze ，explain analyze是实际执行查询，并在执行查询时收集有关执行的统计信息。
（2）查看当前连接的执行计划
当用户发现有一个查询执行了很长时间，用户需要分析执行缓慢的原因，这时可以通过EXPLAIN FOR CONNECTION语句查看当前执行语句的查询计划。
用户可以通过sys.session视图里面的conn_id来获得当前查询的连接id。
1.2 执行计划输出格式
EXPLAIN可以输出四种格式，传统格式，JSON格式，tree格式以及可视化输出。用户可以根据需要选择适用于自己的格式。
（1）传统表格式：explain format=TRADITIONAL
format=TRADITIONAL 可以省略，使用不带format选项，返回的就是传统表格式的结果。
| explain format=traditional select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal; |
（2）JSON格式：explain format=json
JSON格式是四种格式里面输出信息最详尽的格式，里面还会包含执行成本信息。
| mysql> explain format=json select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal; |
（3）tree格式：explain format=tree
TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。
| explain format=tree select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal; |
（4）可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划。
二、执行计划输出详解
EXPLAIN 语句输出列的格式：
| 列 | JSON | 含义 |
| id | select_id | SELECT 标识符 |
| select_type | / | SELECT 类型 |
| 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 | / | 附加信息 |
2.1 id
标识符SELECT。SELECT这是查询中的序列号 。NULL如果该行引用其他行的并集结果，则该值可以是。在本例中，该 table列显示一个值 ，表示该行引用值为 和 的行的并集。idMN
2.2 select_type
SELECT 的类型，可以是下表中显示的任何类型。JSON 格式将类型EXPLAIN公开 SELECT为 a 的属性 query_block，除非它是 SIMPLE或PRIMARY。
| select_type 的值 | 含义 |
| SIMPLE | 简单的 SELECT 查询，没有使用 UNION 或者 子查询 |
| PRIMARY | 最外层的 SELECT |
| UNION | UNION 查询中的第二次或者更晚的 SELECT 语句 |
| DEPENDENT UNION | UNION 查询中的第二次或者更晚的 SELECT 语句, 取决于外层查询 |
| UNION RESULT | UNION 查询的结果 |
| SUBQUERY | 子查询中的第一个 SELECT |
| DEPENDENT SUBQUERY | 子查询中的第一个 SELECT，依赖于外部查询 |
| DERIVED | 派生表 |
| DEPENDENT DERIVED | 被派生表依赖的另一个表 |
| MATERIALIZED | 物化的子查询 |
| UNCACHEABLE SUBQUERY | 无法缓存结果且必须针对外部查询的每一行重新计算结果的子查询 |
| UNCACHEABLE UNION | UNION 中属于不可缓存子查询的第二个或后续查询 |
2.3 type
EXPLAIN 输出的类型列描述了表的连接方式。下面的列表描述了连接类型，从最好的类型到最差的类型排序：
不同类型性能从强到差：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all。
| 连接类型 | 说明 |
| system | 表只有一行 |
| const | 表最多只有一行匹配，通用用于主键或者唯一索引比较时 |
| eq_ref | 每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种， 特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引 |
| ref | 如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键 |
| ref_or_null | 与ref类似，但包括NULL |
| index_merge | 表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。 这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话） |
| unique_subquery | 在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换。 PS：所以不一定in子句中使用子查询就是低效的！ |
| index_subquery | 同上，但把形如”select non_unique_key_column“的子查询替换range常数值的范围 |
| range | 常数值的范围 |
| index | a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）； b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）； c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思； d.如单独出现，则是用读索引来代替读行，但不用于查找 |
| all | 全表扫描 |
·system
该表只有一行（通常是系统表），这是 const 连接类型的特例。。
·const
该表最多有一个匹配行，该行在查询开始时读取。由于只有一行，因此该行中的列的值可以被优化器的其余部分视为常量。const 表非常快，因为它们只被读取一次。
当我们使用 PRIMARY KEY 或 UNIQUE 索引与常量值进行比较时，MySQL 会使用 const。
| create table t1(id int,ename varchar(20) unique); insert into t1 values(1,'robin'),(2,'jack'),(3,'henry'); explain select * from (select * from t1 where ename='robin')x; |
·eq_ref
对于前一个表中的行的每个组合，都会从此表中读取一行。除了 system 和 const 类型之外，这是最好的连接类型。
当连接使用索引的所有部分并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，会使用 eq_ref。
eq_ref 可用于使用 = 运算符进行比较的索引列。比较值可以是常量或使用在此表之前读取的表中的列的表达式。
| explain select * from film a join film_text b on a.film_id=b.film_id; |
·ref
对于前面表中的行的每个组合，都会从此表中读取具有匹配索引值的所有行。如果联接仅使用索引的最左边前缀或者索引不是 PRIMARY KEY 或 UNIQUE 索引，换句话说，如果联接无法根据索引的值选择单个行，就会使用 ref。如果使用的索引仅匹配几行，那么这是一个很好的连接类型。
ref 可用于使用 = 或 <=> 运算符进行比较的索引列。在以下示例中，MySQL 会使用 ref 连接来处理 ref_table：
| create index idx_fisrt_last_name on customer(first_name,last_name); explain select first_name from customer where first_name='JESSIE'; |
·fulltext
连接是使用 FULLTEXT 索引执行的。
·ref_or_null
这种连接类型类似于 ref，但 MySQL 对包含 NULL 值的行进行了额外的搜索。这种连接类型优化最常用于解析子查询。
| explain select * from emp e where e.mgr is null or e.mgr=7369; |
·index_merge
该连接类型表明使用了索引合并优化。在这种情况下，输出行中的 key 列会包含所使用的索引的列表，并且 key_len 包含所使用的索引的最长键部分的列表。
| select * from t3 where key1 =3 or key2 =4 |
索引合并其实也很好理解，当查询条件可以命中多个索引时，MySQL会尝试在两个索引树查找匹配的条件，然后将结果其合并起来。
·unique_subquery
unique_subquery 只是一个索引查找功能，完全替代了子查询，以获得更好的效率。
| SELECT * FROM t2 WHERE t2.key2 IN ( SELECT id FROM t3 WHERE t2.key2 = t3.key2 ) OR t2.key2 = 1 |
·index_subquery
此连接类型类似于 unique_subquery. 它取代了 IN 子查询，但它适用于以下形式的子查询中的非唯一索引。
| SELECT * FROM t2 WHERE t2.key2 IN ( SELECT key1 FROM t3 WHERE t2.key2 = t3.key2 ) OR t2.key2 = 1 |
index_subquery和unique_subquery的区别在于子查询中的列是唯一索引还是普通的二级索引。
·range
使用索引来选择行，并且仅检索给定范围内的行。输出行中的 key 列会提示使用了哪个索引，key_len 列包含了所使用的最长的 key 部分。对于这种类型，ref 列为 NULL。
当索引列与常量进行比较时，如果使用了 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 这些运算符，则 MySQL 会使用 range。
| explain select * from payment where customer_id>300 and customer_id<305; |
·index
index 连接类型与 ALL 相同，只是扫描索引树。这种情况有两种发生方式：
o如果索引是查询的覆盖索引，并且可以用来满足表中所需的所有数据，则仅扫描索引树。
这种情况下，Extra 列会显示 Using index，只进行索引扫描通常比 ALL 更快，因为索引的大小通常小于表数据。
o全表扫描是通过读取索引来按索引顺序查找数据行来执行的。Uses index 不会出现在 Extra 列中。
当查询仅使用属于单个索引的列时，MySQL 会使用 index 连接类型。
| explain select title from film |
·ALL
对先前表中的每个行组合进行全表扫描。如果该表是第一个未标记为 const 的表，这通常不好，并且在所有其他情况下通常非常糟糕。
通常，我们可以通过添加索引来避免 ALL，这些索引允许根据早期表中的常量值或列值从表中检索行。
| explain select count(description) from film |
2.4 possible_keys
显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
如果此列是NULL（或在 JSON 格式的输出中未定义），则没有相关索引。在这种情况下，您可以通过检查子句来WHERE 检查它是否引用某些适合建立索引的列，从而提高查询的性能。如果是这样，请创建适当的索引并再次检查查询 EXPLAIN。
| explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10; |
2.5 key
实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
该key列表示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用其中一个possible_keys 索引来查找行，则该索引将被列为键值。
可能key会指定值中不存在的索引 possible_keys。如果没有possible_keys索引适合查找行，但查询选择的所有列都是其他索引的列，则可能会发生这种情况。也就是说，命名索引覆盖了选定的列，因此虽然它不用于确定要检索哪些行，但索引扫描比数据行扫描更有效。
对于InnoDB，即使查询还选择主键，辅助索引也可能覆盖所选列，因为InnoDB每个辅助索引都存储主键值。如果 key是NULL，MySQL 找不到可用于更有效地执行查询的索引。
要强制 MySQL 使用或忽略列中列出的索引 possible_keys，请 在查询中使用FORCE INDEX、USE INDEX或。
| explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10; |
2.6 key_len
表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好，可用于判断复合索引是否被完全使用。
| explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10; |
2.7 ref
显示索引的哪一列被使用了，如果可能的话，是一个常数
| explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10; |
该ref列显示将哪些列或常量与列中指定的索引进行比较 key以从表中选择行。如果值为func，则使用的值是某个函数的结果。
2.8 rows
根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反映sql找了多少数据，在完成目的的情况下越少越好。
这里是执行计划中估算的扫描行数，不是精确值。
2.9 extra
包含额外的信息，这个列可以显示的信息非常多，有几十种，常用的有
这个列可以显示的信息非常多，有几十种，常用的有
| extra列信息 | 说明 |
| distinct | 在select部分使用了distinc关键字 |
| no tables used | 不带from字句的查询或者From dual查询 |
| using filesort | 排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中 |
| using index | 使用覆盖索引的时候就会出现。查询时不需要回表查询，直接通过索引就可以获取查询的数据。 |
| using join buffer（block nested loop），using join buffer（batched key accss） | 5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。 |
| using sort_union，using_union，using intersect，using sort_intersection | using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集 using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集 using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。 |
| using temporary | 表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。 |
| using where | 在查找使用索引的情况下，需要回表去查询所需的数据。 表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition |
| using index condition | 在查找使用索引的情况下，需要回表去查询所需的数据 |
| using index & using where | 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 |
| firstmatch(tb_name) | 5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个 |
| loosescan(m..n) | 5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个 |
注：回表的含义是，先根据索引查询数据，然后在根据确定的数据id和查询条件再去查询具体的数据的过程
除了这些之外，还有很多查询数据字典库，执行计划过程中就发现不可能存在结果的一些提示信息。
三、执行顺序解读
如何确定执行计划的执行顺序？执行顺序跟MySQL执行计划中的id和select-type有关系，需要结合id值和被执行对象之间的关系，才能确定id的执行次序。
网上的一些说法是不准确的，例如"id一样，按照顺序执行；id越大，执行的优先级就越高（如子查询）......"。
执行顺序分为以下几种情况：
Ø并列关系：如两个表之间做UNION操作，这两个对象是并列的关系，则id值小的先执行。id值相同，从上到下，依次执行。
Ø嵌套的关系：如存在子查询，首先启动的是id值小的，即对父对象先进行操作，在id值小的对象执行过程中，接着又启动了id值为大的子对象。
所以，最先执行完毕的是id值大者；最先执行的是id值小者。
Ø如果嵌套关系中，有多个子查询，且子查询间的关系是并列的，则执行顺序按照id序号进行，id值小的先执行。
Ø如果子查询在优化阶段被执行，则执行阶段不再执行，这中情况与id值表示的执行顺序没有关系。
下面看一个执行计划：
如上面的这个执行计划，id 2最先执行，id 1有两个按从上到下执行。
在看下面这一个执行计划
| mysql> EXPLAIN -> SELECT -> * -> FROM emp -> WHERE -> empno IN -> ( SELECT a.empno FROM emp a WHERE a.ename like 'M%' -> UNION -> SELECT a.empno FROM emp a WHERE a.deptno = 20 ); |
注释：按照网上的说法执行顺序是 4->3->2->1，这里其实是错误的。
首先id=2和id=3是UNION的上下部分是并列关系，而id=4 是合并表其实就是查询的临时表也就是结果集 t，所以正确的执行顺序应该是2->3->4->1。
如果是mysql 8.0以上版本，可以查看explan analyze的执行计划：
| EXPLAIN ANALYZE SELECT * FROM emp WHERE empno IN ( SELECT a.empno FROM emp a WHERE a.ename like 'M%' UNION SELECT a.empno FROM emp a WHERE a.deptno = 20 ); |
这个基本很接近oracle的执行计划了，遵守最右最上原则看即可。
关注我，学习更多的数据库知识
2297
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
