【MySQL】获取执行计划

执行计划（execution plan，也叫查询计划或者解释计划）是 MySQL 服务器执行 SQL 语句的具体步骤。例如，通过索引还是全表扫描访问表中的数据，连接查询的实现方式和连接的顺序，分组和排序操作的实现方式等。

负责生成执行计划的组件就是优化器，优化器利用表结构、字段、索引、查询条件、数据库的统计信息和配置参数决定 SQL 语句的最佳执行方式。如果想要解决慢查询的性能问题，首先应该查看它的执行计划。

获取执行计划

MySQL 提供了 EXPLAIN 语句，用于获取 SQL 语句的执行计划。该语句的基本形式如下：

{
  EXPLAIN | DESCRIBE | DESC}
{
    SELECT statement
  | TABLE statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}
123456789

EXPLAIN和DESCRIBE是同义词，可以通用。实际应用中，DESCRIBE主要用于查看表的结构，EXPLAIN主要用于获取执行计划。MySQL 可以获取 SELECT、INSERT、DELETE、UPDATE、REPLACE 等语句的执行计划。从 MySQL 8.0.19 开始，支持 TABLE 语句的执行计划。

举例来说：

explain
select *
from employee;
id|select_type|table   |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
--|-----------|--------|----------|----|-------------|---|-------|---|----|--------|-----|
 1|SIMPLE     |employee|          |ALL |             |   |       |   |  25|   100.0|     |
123456

MySQL 中的执行计划包含了 12 列信息，这些字段的含义我们在下文中进行解读。

除了使用 EXPLAIN 语句之外，很多管理和开发工具都提供了查看图形化执行计划的功能，例如 MySQL Workbench 中显示以上查询的执行计划如下：

当然，这种方式最终也是执行了 EXPLAIN 语句。

解读执行计划

理解执行计划中每个字段的含义可以帮助我们知悉 MySQL 内部的操作过程，找到性能问题的所在并有针对性地进行优化。在执行计划的输出信息中，最重要的字段就是 type。

type 字段

type 被称为连接类型（join type）或者访问类型（access type），它显示了 MySQL 如何访问表中的数据。

访问类型会直接影响到查询语句的性能，性能从好到差依次为：

• system，表中只有一行数据（系统表），这是 const 类型的特殊情况；
• const，最多返回一条匹配的数据，在查询的最开始读取；
• eq_ref，对于前面的每一行，从该表中读取一行数据；
• ref，对于前面的每一行，从该表中读取匹配索引值的所有数据行；
• fulltext，通过 FULLTEXT 索引查找数据；
• ref_or_null，与 ref 类似，额外加上 NULL 值查找；
• index_merge，使用索引合并优化技术，此时 key 列显示使用的所有索引；
• unique_subquery，替代以下情况时的 eq_ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)；
• index_subquery，与 unique_subquery 类似，用于子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)；
• range，使用索引查找范围值；
• index，与 ALL 类型相同，只不过扫描的是索引；
• ALL，全表扫描，通常表示存在性能问题。

const 和 eq_ref 都意味着着通过 PRIMARY KEY 或者 UNIQUE 索引查找唯一值；它们的区别在于 const 对于整个查询只返回一条数据，eq_ref 对于前面的结果集中的每条记录只返回一条数据。例如以下查询通过主键（key = PRIMARY）进行等值查找：

explain
select * 
from employee
where emp_id = 1;
id|select_type|table   |partitions|type |possible_keys|key    |key_len|ref  |rows|filtered|Extra|
--|-----------|--------|----------|-----|-------------|-------|-------|-----|----|--------|-----|
 1|SIMPLE     |employee|          |const|PRIMARY      |PRIMARY|4      |const|   1|   100.0|     |
1234567

const 只返回一条数据，是一种非常快速的访问方式，所以相当于一个常量（constant）。

以下语句通过主键等值连接两个表：

explain
select * 
from employee e
join department d
on (e.dept_id = d.dept_id )
where e.emp_id in(1, 2);
id|select_type|table|partitions|type  |possible_keys       |key    |key_len|ref           |rows|filtered|Extra      |
--|-----------|-----|----------|------|--------------------|-------|-------|--------------|----|--------|-----------|
 1|SIMPLE     |e    |          |range |PRIMARY,idx_emp_dept|PRIMARY|4      |              |   2|   100.0|Using where|
 1|SIMPLE     |d    |          |eq_ref|PRIMARY             |PRIMARY|4      |hrdb.e.dept_id|   1|   100.0|           |
12345678910

对于 employee 中返回的每一行（table = e），department 表通过主键（key = PRIMARY）返回且仅返回一条数据（type = eq_ref）。Extra 字段中的 Using where 表示将经过条件过滤后的数据传递给下个表或者客户端。

ref、ref_or_null 以及 range 表示通过范围查找所有匹配的索引项，然后根据需要再访问表中的数据。通常意味着使用了非唯一索引或者唯一索引的前面部分字段进行数据访问，例如：

explain
select * 
from employee e
where e.dept_id = 1;
id|select_type|table|partitions|type|possible_keys|key         |key_len|ref  |rows|filtered|Extra|
--|-----------|-----|----------|----|-------------|------------|-------|-----|----|--------|-----|
 1|SIMPLE     |e    |          |ref |idx_emp_dept |idx_emp_dept|4      |const|   3|   100.0|     |

explain
select * 
from employee e
join department d
on (e.dept_id = d.dept_id )
where d.dept_id = 1;
id|select_type|table|partitions|type |possible_keys|key         |key_len|ref  |rows|filtered|Extra|
--|-----------|-----|----------|-----|-------------|------------|-------|-----|----|--------|-----|
 1|SIMPLE     |d    |          |const|PRIMARY      |PRIMARY     |4      |const|   1|   100.0|     |
 1|SIMPLE     |e    |          |ref  |idx_emp_dept |idx_emp_dept|4      |const|   3|   100.0|     |
123456789101112131415161718

以上两个查询语句都是通过索引 idx_emp_dept 返回 employee 表中的数据。

ref_or_null 和 ref 的区别在于查询中包含了 IS NULL 条件。例如：

alter table employee modify column dept_id int null;

explain
select * 
from employee e
where e.dept_id = 1 or dept_id is null;
id|select_type|table|partitions|type       |possible_keys|key         |key_len|ref  |rows|filtered|Extra                |
--|-----------|-----|----------|-----------|-------------|------------|-------|-----|----|--------|---------------------|
 1|SIMPLE     |e    |          |ref_or_null|idx_emp_dept |idx_emp_dept|5      |const|   4|   100.0|Using index condition|
123456789

其中，Extra 字段显示为 Using index condition，意味着通过索引访问表中的数据之前，直接通过 WHERE 语句中出现的索引字段条件过滤数据。这是 MySQL 5.6 之后引入了一种优化，叫做索引条件下推（Index Condition Pushdown）。

为了显示 ref_or_null，我们需要将字段 dept_id 设置为可空，测试之后记得重新修改为 NOT NULL：

alter table employee modify column dept_id int not null;
1

range 通常出现在使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或者 IN() 运算符和索引字段进行比较时，例如：

explain
select * 
from employee e
where e.email like 'zhang%';
id|select_type|table|partitions|type |possible_keys|key         |key_len|ref|rows|filtered|Extra                |
--|-----------|-----|----------|-----|-------------|------------|-------|---|----|--------|---------------------|
 1|SIMPLE     |e    |          |range|uk_emp_email |uk_emp_email|302    |   |   2|   100.0|Using index condition|
1234567

index_merge 表示索引合并，当查询通过多个索引 range 访问方式返回数据时，MySQL 可以先对这些索引扫描结果合并成一个，然后通过这个索引获取表中的数据。例如：

explain
select * 
from employee e
where dept_id = 1 or job_id = 1;
id|select_type|table|partitions|type