MySQL 执行计划

通过 Explain 查看执行计划

一条查询语句在经过 MySQL 查询优化器的基于成本和规则的优化会后会生成一个执行计划，这个执行计划展示了 MySQL 会使用何种方式来执行查询，通过 MySQL 提供的 EXPLAIN 语法可以查看执行计划。

执行计划的生成是 MySQL server 层中的功能，并不会针对某个具体的存储引擎。

Explain 的普通格式执行计划

我们在想要查看执行计划的 SQL 前面加上 EXPLAIN 关键字来查看执行计划，如：

mysql> EXPLAIN SELECT * FROM employee WHERE name IN ('Tom', '张伟', '李娜') AND employee_id < 100;
+----+-------------+----------+------------+-------+--------------------------+-----------------+---------+------+------+----------+------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys            | key             | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+----------+------------+-------+--------------------------+-----------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | employee | NULL       | range | idx_employee_id,idx_name | idx_employee_id | 5       | NULL |   99 |    22.05 | Using index condition; Using where |
+----+-------------+----------+------------+-------+--------------------------+-----------------+---------+------+------+----------+------------------------------------+

EXPLAIN 不仅可以用于 SELECT 语句，也可以用于 DELETE、INSERT、REPLACE 和 UPDATE这些语句。

我们看一下 EXPLAIN 输出结果中各个列的含义：

列名	说明
id	查询操作的标识符
select_type	查询操作的类型
table	表名
partitions	查询将访问的分区信息
type	单表访问方法
possible_keys	可能使用的索引
key	实际使用的索引
key_len	实际使用的索引的最大长度
ref	索引列等值查询时，与索引列进行等值匹配的信息
rows	估计需要读取的记录数
filtered	估计经查询条件过滤后剩余的记录数百分比
Extra	一些额外信息

table

MySQL Explain 结果的每一条记录都对应某个单表的访问方法，而 table 列就是这张表的名称。

id

在 MySQL 的 Explain 结果中，id 列是用来表示查询中每个操作的唯一标识符。对于一个查询，它可能只有一个 id 值，也可能有多个 id 值：

id 值越大执行优先级越高，id 相同时前面的先执行，id 为 NULL 的最后执行。

(1) 单个 id 值

单个 id 值一般出现在简单的查询中，如：

mysql> EXPLAIN SELECT * FROM employee WHERE id = 666;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

(2) 多个相同 id 值

如果 Explain 结果中出现了多个相同的 id 值，表示这些操作属于同一个查询部分，一般出现在连接查询中，如：

mysql> EXPLAIN SELECT * FROM employee e1 LEFT JOIN employee2 e2 ON e1.employee_id = e2.employee_id WHERE e1.id < 10;
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | e1    | NULL       | range | PRIMARY         | PRIMARY         | 4       | NULL                |    9 |   100.00 | Using where |
|  1 | SIMPLE      | e2    | NULL       | ref   | idx_employee_id | idx_employee_id | 5       | test.e1.employee_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+---------------------+------+----------+-------------+

在连接查询的执行计划中，每个表都会对应一条记录，这些记录的 id 值是相同的，前面的表是驱动表，后面的表是被驱动表。

(3) 多个不同 id 值

表示这些操作属于不同的查询部分，一般出现在子查询或者 Union 查询中，如：

子查询：

mysql> EXPLAIN SELECT * FROM employee WHERE employee_id IN (SELECT employee_id FROM employee2) OR id < 10;
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | employee  | NULL       | ALL   | PRIMARY         | NULL            | NULL    | NULL | 9414 |   100.00 | Using where |
|  2 | SUBQUERY    | employee2 | NULL       | index | idx_employee_id | idx_employee_id | 5       | NULL | 9920 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+

从执行计划中我们可以看到， employee 表在外层查询中，对应第一条记录，其 id 值为 1；employee2 表在子查询中，对应第二条记录，其 id 值为 2。

有一点需要注意，符合条件的子查询可能会被查询优化器转换为连接查询，如：

mysql> EXPLAIN SELECT * FROM employee WHERE employee_id IN (SELECT employee_id FROM employee2);
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+---------------------------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys   | key             | key_len | ref                       | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+---------------------------+------+----------+-------------+
|  1 | SIMPLE      | employee  | NULL       | ALL  | idx_employee_id | NULL            | NULL    | NULL                      | 9414 |   100.00 | Using where |
|  1 | SIMPLE      | employee2 | NULL       | ref  | idx_employee_id | idx_employee_id | 5       | test.employee.employee_id |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+---------------------------+------+----------+-------------+

可以看到，虽然上面这个 SQL 是一个子查询，但是执行计划中 employee 和 employee2 表对应记录的 id 值部是 1 ，表示查询优化器将子查询转换为了连接查询。

Union 查询：

mysql> EXPLAIN SELECT * FROM employee UNION SELECT * FROM employee2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | employee   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9414 |   100.00 | NULL            |
|  2 | UNION        | employee2  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9920 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

从执行计划中可以看到，UNION 前面的查询 id 为 1，后面的查询 id 为 2，最后还有一条 id 为 NULL 的记录。在 Explain 结果中，id 列为 NULL 通常表示该行涉及的操作不是实际执行的部分，可能是子查询、临时表、派生表或优化器生成的虚拟操作。

我们知道 UNION 用来合并多个查询的结果集并去重，而去重操作会使用临时表，上面 id 为 NULL 的记录中的 <union1,2> 表就是用来去重的临时表。

相比于 UNION，UNION ALL 合并的结果不需要去重，因此不需要用到临时表，也就不会有 id 为 NULL 的记录，如：

mysql> EXPLAIN SELECT * FROM employee UNION ALL SELECT * FROM employee2;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | employee  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9414 |   100.00 | NULL  |
|  2 | UNION       | employee2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9920 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+

select_type

select_type 表示查询操作的类型，有以下取值：

(1) SIMPLE

一个查询不包含子查询或者 UNION 查询，它的 select_type 值就是 SIMPLE，如：

mysql> EXPLAIN SELECT * FROM employee WHERE id = 666;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

连接查询的每个查询操作的 select_type 值也是 SIMPLE，如：

mysql> EXPLAIN SELECT * FROM employee e1 LEFT JOIN employee2 e2 ON e1.employee_id = e2.employee_id WHERE e1.id < 10;
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | e1    | NULL       | range | PRIMARY         | PRIMARY         | 4       | NULL                |    9 |   100.00 | Using where |
|  1 | SIMPLE      | e2    | NULL       | ref   | idx_employee_id | idx_employee_id | 5       | test.e1.employee_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+---------------------+------+----------+-------------+

(2) PRIMARY

对于包含子查询或者 UNION 查询的查询，它最左边的那个查询操作的 select_type 值就是 PRIMARY，如：

mysql> EXPLAIN SELECT * FROM employee WHERE employee_id IN (SELECT employee_id FROM employee2) OR id < 10;
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | employee  | NULL       | ALL   | PRIMARY         | NULL            | NULL    | NULL | 9414 |   100.00 | Using where |
|  2 | SUBQUERY    | employee2 | NULL       | index | idx_employee_id | idx_employee_id | 5       | NULL | 9920 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
mysql> EXPLAIN SELECT * FROM employee UNION SELECT * FROM employee2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | employee   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9414 |   100.00 | NULL            |
|  2 | UNION        | employee2  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9920 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

(3) UNION

对于 UNION 查询来说，除了最左边的那个查询操作以外，其他查询操作的 select_type 值都是 UNION，如：

mysql> EXPLAIN SELECT * FROM employee UNION SELECT * FROM employee2 UNION ALL SELECT * FROM employee3;
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | employee     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9414 |   100.00 | NULL            |
|  2 | UNION        | employee2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9920 |   100.00 | NULL            |
|  3 | UNION        | employee3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9917 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

(4) UNION RESULT

MySQL 使用临时表来执行 UNION 查询的去重操作，针对该临时表的查询操作的 select_type 值就是 UNION RESULT，具体可以参考上面的例子。

(5) SUBQUERY

如果包含的子查询无法被优化成 semi-join 形式，并且是一个不相关子查询，即可以被优化器物化来执行子查询时，该子查询的第一个查询操作的 select_type 值就是 SUBQUERY，如：

mysql> EXPLAIN SELECT * FROM employee WHERE employee_id IN (SELECT employee_id FROM employee2) OR id < 10;
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | employee  | NULL       | ALL   | PRIMARY         | NULL            | NULL    | NULL | 9414 |   100.00 | Using where |
|  2 | SUBQUERY    | employee2 | NULL       | index | idx_employee_id | idx_employee_id | 5       | NULL | 9920 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+

在这个例子中，外层查询存在 OR 连接，不满足 semi-join 的条件；子查询和外层查询不相关，可以被物化。

SUBQUERY 的子查询只需要执行一次。

(6) DEPENDENT SUBQUERY

如果包含的子查询无法被优化成 semi-join 形式，并且是一个相关子查询，该子查询的第一个查询操作的 select_type 值就是 DEPENDENT SUBQUERY，如：

mysql> EXPLAIN SELECT * FROM employee WHERE employee_id IN (SELECT employee_id FROM employee2 WHERE employee2.id = employee.id) OR id < 10;
+----+--------------------+-----------+------------+--------+-------------------------+---------+---------+------------------+------+----------+-------------+
| id | select_type        | table     | partitions | type   | possible_keys           | key     | key_len | ref              | rows | filtered | Extra       |
+----+--------------------+-----------+------------+--------+-------------------------+---------+---------+------------------+------+----------+-------------+
|  1 | PRIMARY            | employee  | NULL       | ALL    | PRIMARY                 | NULL    | NULL    | NULL             | 9414 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | employee2 | NULL       | eq_ref | PRIMARY,idx_employee_id | PRIMARY | 4       | test.employee.id |    1 |    10.00 | Using where |
+----+--------------------+-----------+------------+--------+-------------------------+---------+---------+------------------+------+----------+-------------+

DEPENDENT SUBQUERY 的子查询可能会执行多次。

(7) DEPENDENT UNION

在包含 UNION 的查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其他的小查询的 select_type 的值都是 DEPENDENT UNION。但是出现 DEPENDENT UNION 并不总是意味着 UNION 子查询直接依赖外部查询，如：

mysql> EXPLAIN SELECT * FROM employee WHERE employee_id IN (SELECT employee_id FROM employee2 UNION SELECT employee_id FROM employee3) OR id < 10;
+----+--------------------+------------+------------+------+-----------------+-----------------+---------+------+------+----------+-----------------+
| id | select_type        | table      | partitions | type | possible_keys   | key             | key_len | ref  | rows | filtered | Extra           |
+----+--------------------+------------+------------+------+-----------------+-----------------+---------+------+------+----------+-----------------+
|  1 | PRIMARY            | employee   | NULL       | ALL  | PRIMARY         | NULL            | NULL    | NULL | 9414 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | employee2  | NULL       | ref  | idx_employee_id | idx_employee_id | 5       | func |    1 |   100.00 | Using index     |
|  3 | DEPENDENT UNION    | employee3  | NULL       | ref  | idx_employee_id | idx_employee_id | 5       | func |    1 |   100.00 | Using index     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL  | NULL            | NULL            | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+------+-----------------+-----------------+---------+------+------+----------+-----------------+

(8) DERIVED

一般在主查询的 FROM 子句中使用子查询时，MySQL 会将子查询视为一个派生表，该派生表对应的子查询的 select_type 值就是 DERIVED，如：

mysql> EXPLAIN SELECT * FROM (SELECT name, count(*) FROM employee2 GROUP BY name) AS ee;
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL     | NULL    | NULL | 9920 |   100.00 | NULL        |
|  2 | DERIVED     | employee2  | NULL       | index | idx_name      | idx_name | 403     | NULL | 9920 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+

派生表的结果集是在主查询执行之前计算出来的，并且结果集通常不会被缓存。

(9) MATERIALIZED

当查询优化器在执行包含子查询的语句时，选择将子查询物化后再与外层查询进行连接查询时，该子查询对应的 select_type 值就是 MATERIALIZED，如：

mysql> EXPLAIN SELECT * FROM employee WHERE name IN (SELECT name FROM employee2 WHERE extra_info = 'xxx');
+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref                | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE       | employee    | NULL       | ALL    | idx_name      | NULL       | NULL    | NULL               | 9414 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 403     | test.employee.name |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | employee2   | NULL       | ALL    | idx_name      | NULL       | NULL    | NULL               | 9920 |    10.00 | Using where |
+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------------+------+----------+-------------+

物化表的结果集会被存储在临时表中，并在查询的执行过程中被重复使用，避免多次计算相同的子查询。

(10) UNCACHEABLE SUBQUERY

不常见，不做介绍。

(11) UNCACHEABLE UNION

不常见，不做介绍。

partitions

partitions 列跟表分区有关，它会显示查询执行时将访问的分区信息。

type

我们知道执行计划中的每一条记录都对应一张表的查询操作，其中的 type 列就表示对应查询的访问方法。完整的访问方法有：system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL，我们前面已经介绍过的就不赘述了，下面介绍下 system、eq_ref、fulltext、index_merge、unique_subquery、index_subquery：

除了 ALL 这个访问方法外，其他的访问方法都能用到索引，除了 index_merge 访问方法外，其他的访问方法最多只能用到一个索引。

system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM 和 Memory，那么对该表的访问方法就是 system。

eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是 eq_ref。

fulltext

全文索引。

index_merge

索引合并。

unique_subquery

unique_subquery 出现在一些包含 IN 子查询的语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，并且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery，如：

mysql> EXPLAIN SELECT * FROM employee e1 WHERE extra_info IN (SELECT id FROM employee2 e2 WHERE e1.name = e2.name) OR english_name = 'Tom';
+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type            | possible_keys    | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | e1    | NULL       | ALL             | idx_english_name | NULL    | NULL    | NULL | 9414 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | e2    | NULL       | unique_subquery | PRIMARY,idx_name | PRIMARY | 4       | func |    1 |    10.00 | Using where |
+----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+

index_subquery

index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引，如：

mysql> EXPLAIN SELECT * FROM employee e1 WHERE extra_info IN (SELECT employee_id FROM employee2 e2 WHERE e1.name = e2.name) OR english_name = 'Tom';
+----+--------------------+-------+------------+----------------+--------------------------+-----------------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type           | possible_keys            | key             | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+----------------+--------------------------+-----------------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | e1    | NULL       | ALL            | idx_english_name         | NULL            | NULL    | NULL | 9414 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | e2    | NULL       | index_subquery | idx_employee_id,idx_name | idx_employee_id | 5       | func |    1 |    10.00 | Using where |
+----+--------------------+-------+------------+----------------+--------------------------+-----------------+---------+------+------+----------+-------------+

possible_keys 和 key

possible_keys 表示某个单表查询可能使用的索引，key 表示某个单表查询实际使用的索引。

key_len

key_len 表示 MySQL 查询优化器在执行查询时，使用的索引的最大长度（单位为字节）。key_len 列主要是为了让我们区分某个使用联合索引的查询用到了几个索引列，而不是为了准确展示索引实际占用的空间，因此并未考虑存储引擎的差异。

key_len 有两个重要的计算规则：

如果索引列可以存储 NULL 值，则 key_len 要多加 1 个字节。
如果索引列是变长类型，则 key_len 要多加 2个字节的空间来存储该变长列的实际长度。

举两个例子说明：

固定长度类型：如索引列类型是 CHAR(100)，不能为 NULL，字符集是 utf8，则索引最大长度为 100 * 3 = 300 字节
变长类型：如索引列类型是 VARCHAR(100)，可以为 NULL，字符集是 utf8，则索引最大长度为 100 × 3 + 1 + 2 = 303 个字节

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 其中之一时，ref 列展示与索引列等值匹配的是什么，如：

下面的 ref 是一个常数：

mysql> EXPLAIN SELECT * FROM employee WHERE english_name = 'Tom';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_english_name | idx_english_name | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+

可以是一个列：

mysql> EXPLAIN SELECT * FROM employee e1 LEFT JOIN employee2 e2 ON e1.name = e2.english_name;
+----+-------------+-------+------------+------+------------------+------------------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref          | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+--------------+------+----------+-------+
|  1 | SIMPLE      | e1    | NULL       | ALL  | NULL             | NULL             | NULL    | NULL         | 9414 |   100.00 | NULL  |
|  1 | SIMPLE      | e2    | NULL       | ref  | idx_english_name | idx_english_name | 403     | test.e1.name |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+--------------+------+----------+-------+

也可以是一个函数：

mysql> EXPLAIN SELECT * FROM employee e1 LEFT JOIN employee2 e2 ON UPPER(e1.name) = e2.english_name;
+----+-------------+-------+------------+------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+------------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | e1    | NULL       | ALL  | NULL             | NULL             | NULL    | NULL | 9414 |   100.00 | NULL        |
|  1 | SIMPLE      | e2    | NULL       | ref  | idx_english_name | idx_english_name | 403     | func |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+------------------+------------------+---------+------+------+----------+-------------+

rows

如果查询优化器决定使用全表扫描的方式执行查询时，rows 列就表示预计需要扫描的记录数；如果使用索引来执行查询时，rows 列则表示预计扫描的索引记录数。

filtered

filtered 列表示查询优化器估算在 rows 这些记录中，满足其他查询条件的记录百分比，如：

mysql> EXPLAIN SELECT * FROM employee WHERE employee_id <= 100 AND english_name = 'Zhang_Wei';
+----+-------------+----------+------------+-------+----------------------------------+-----------------+---------+------+------+----------+------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys                    | key             | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+----------+------------+-------+----------------------------------+-----------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | employee | NULL       | range | idx_employee_id,idx_english_name | idx_employee_id | 5       | NULL |  100 |    10.25 | Using index condition; Using where |
+----+-------------+----------+------------+-------+----------------------------------+-----------------+---------+------+------+----------+------------------------------------+

上面这个查询使用的索引为 idx_employee_id，rows 为 100，表示 MySQL 查询优化器估算扫描 idx_employee_id 索引后满足 employee_id <= 100 的记录大约有 100 条；filtered 为 10.25，表示 MySQL 查询优化器估算 rows 这些记录中满足 english_name = 'Zhang_Wei' 条件的大约有 100 * 10.25% = 11 条记录。

Extra

Extra 用来说明一些额外信息，由于 MySQL 提供的额外信息有好几十种，我们只介绍一些比较常见或者比较重要的：

#### No tables used
当查询语句没有 FROM 子句时，会提示 No tables used，如：

mysql> EXPLAIN SELECT 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

Impossible WHERE

当 WHERE 查询条件永远为 False 时，会提示 Impossible WHERE，如：

mysql> EXPLAIN SELECT * FROM employee WHERE 1 > 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

No matching min/max row

当查询列表使用了 MIN 或者 MAX 函数并且没有符合 WHERE 查询条件的记录时，会提示 No matching min/max row，如：

mysql> EXPLAIN SELECT MIN(id) FROM employee WHERE id < 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                   |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No matching min/max row |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+

Using index

当查询列表和查询条件中的列都属于某个索引的列，会提示 Using index，表示使用了覆盖索引，如：

mysql> EXPLAIN SELECT id, name FROM employee WHERE name != 'Tom';
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employee | NULL       | range | idx_name      | idx_name | 403     | NULL | 4708 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

注意：由于二级索引包含主键列，因此查询列表中包含主键列也可以使用索引覆盖。

Using index condition

当部分查询条件可以使用索引，而部分查询条件不能使用该索引但是这个查询列属于该索引，会提示 Using index condition，表示使用了索引条件下推（Index Condition Pushdown, ICP），如：

mysql> EXPLAIN SELECT * FROM employee WHERE country = '中国' AND city IN ('南京', '武汉', '上海');
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_country_province_city | idx_country_province_city | 403     | const | 1059 |    30.00 | Using index condition |
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+

ICP 的大致执行过程如下：

根据 country = '中国' 这个条件，定位到二级索引 idx_country_province_city 中相关的记录
不着急回表，先在定位到的记录中检查并筛选出满足 city IN ('南京', '武汉', '上海') 条件的索引记录
拿着满足 city IN ('南京', '武汉', '上海') 条件的索引记录去执行回表操作

Using where

当使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的查询条件时，会提示 Using where，如：

mysql> EXPLAIN SELECT * FROM employee WHERE extra_info = 'Zhang_Wei';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9414 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他查询条件时，会提示 Using where，如：

mysql> EXPLAIN SELECT * FROM employee WHERE employee_id <= 100 AND english_name = 'Zhang_Wei';
+----+-------------+----------+------------+-------+----------------------------------+-----------------+---------+------+------+----------+------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys                    | key             | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+----------+------------+-------+----------------------------------+-----------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | employee | NULL       | range | idx_employee_id,idx_english_name | idx_employee_id | 5       | NULL |  100 |    10.25 | Using index condition; Using where |
+----+-------------+----------+------------+-------+----------------------------------+-----------------+---------+------+------+----------+------------------------------------+

该查询使用 idx_employee_id 索引，相关查询条件是 employee_id <= 100，但是还有一个 english_name = 'Zhang_Wei' 的查询条件，因此会提示 Using where。

Using join buffer (Block Nested Loop)

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度时， MySQL 一般会为驱动表分配一块名叫 join buffer 的内存，使得一条被驱动表记录一次可以和多条驱动表记录做匹配，从而加快查询速度，这就是基于块的嵌套循环算法，如：

mysql> EXPLAIN SELECT * FROM employee e1 LEFT JOIN employee2 e2 ON e1.name = e2.extra_info;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | e1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9414 |   100.00 | NULL                                               |
|  1 | SIMPLE      | e2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9920 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

在这个查询中，e2 被驱动表的 extra_info 列无法利用索引，因此使用了 join buffer 一次匹配多条 e1 驱动表中的记录来加快查询速度。

Not exists

当使用外连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的，就会提示 Not exists，如：

mysql> EXPLAIN SELECT * FROM employee e1 LEFT JOIN employee2 e2 ON e1.name = e2.name WHERE e2.id IS NULL;
+----+-------------+-------+------------+------+---------------+----------+---------+--------------+------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref          | rows | filtered | Extra                   |
+----+-------------+-------+------------+------+---------------+----------+---------+--------------+------+----------+-------------------------+
|  1 | SIMPLE      | e1    | NULL       | ALL  | NULL          | NULL     | NULL    | NULL         | 9414 |   100.00 | NULL                    |
|  1 | SIMPLE      | e2    | NULL       | ref  | idx_name      | idx_name | 403     | test.e1.name |    1 |    10.00 | Using where; Not exists |
+----+-------------+-------+------------+------+---------------+----------+---------+--------------+------+----------+-------------------------+

Using intersect(...) 或 Using union(...) 或 Using sort_union(...)

表示查询使用了索引合并，不再赘述。

Zero limit

当一个查询的 LIMIT 子句参数为 0 时，如：

mysql> EXPLAIN SELECT * FROM employee LIMIT 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Zero limit |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+

Using filesort

当一个查询的排序操作无法使用到索引，只能在内存中(记录较少的时候)或者磁盘中(记录较多的时候)进行排序，这种排序方式称为文件排序，会提示 Using filesort，如：

mysql> EXPLAIN SELECT * FROM employee ORDER BY extra_info;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9414 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+

执行计划中出现的 Using filesort 要尽量去除掉。

Using temporary

在 DISTINCT 、 GROUP BY 、 UNION 等子句的查询执行过程中，MySQL 可能会借助临时表来完成一些功能，比如去重、排序等，如果查询使用到了临时表，在执行计划的 Extra 列将会提示 Using temporary，如:

mysql> EXPLAIN SELECT extra_info, count(*) FROM employee GROUP BY extra_info;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9414 |   100.00 | Using temporary; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

MySQL 会在包含 GROUP BY 子句的查询中默认添加上 ORDER BY 子句，我们可以使用 ORDER BY NULL 去掉排序操作：

mysql> EXPLAIN SELECT extra_info, count(*) FROM employee GROUP BY extra_info ORDER BY NULL;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9414 |   100.00 | Using temporary |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+

执行计划中出现的 Using temporary 要尽量去除掉。

Start temporary 和 End temporary

在将 In 子查询转换为 semi-join 形式时，如果采用的是 DuplicateWeedout 执行策略，则会在驱动表执行计划的 Extra 列提示 Start temporary，在被驱动表执行计划的 Extra 列提示 End temporary。

LooseScan

在将 In 子查询转换为 semi-join 形式时，如果采用的是 LooseScan 执行策略，则会在驱动表执行计划的 Extra 列提示 LooseScan。

FirstMatch(tbl_name)

在将 In 子查询转换为 semi-join 形式时，如果采用的是 FirstMatch 执行策略，则会在被驱动表执行计划的 Extra 列提示 FirstMatch(tbl_name)，如：

mysql> EXPLAIN SELECT * FROM employee WHERE extra_info IN (SELECT name FROM employee2 WHERE employee.english_name = employee2.english_name);
+----+-------------+-----------+------------+------+---------------------------+----------+---------+--------------------------+------+----------+-----------------------------------+
| id | select_type | table     | partitions | type | possible_keys             | key      | key_len | ref                      | rows | filtered | Extra                             |
+----+-------------+-----------+------------+------+---------------------------+----------+---------+--------------------------+------+----------+-----------------------------------+
|  1 | SIMPLE      | employee  | NULL       | ALL  | idx_english_name          | NULL     | NULL    | NULL                     | 9414 |   100.00 | Using where                       |
|  1 | SIMPLE      | employee2 | NULL       | ref  | idx_name,idx_english_name | idx_name | 403     | test.employee.extra_info |    1 |     5.00 | Using where; FirstMatch(employee) |
+----+-------------+-----------+------------+------+---------------------------+----------+---------+--------------------------+------+----------+-----------------------------------+

Explain 的 Json 格式执行计划

在 EXPLAIN 关键字和查询语句间加上 FORMAT=JSON 可以获得带成本的执行计划，如：

mysql> EXPLAIN FORMAT=JSON SELECT * FROM employee e1 LEFT JOIN employee2 e2 ON e1.name = e2.english_name WHERE e1.extra_info like '额外信息_827%'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,  # 查询操作的 id
    "cost_info": {
      "query_cost": "3234.87"  # 该查询的总成本
    },
    "nested_loop": [  # 使用嵌套循环连接算法执行表连接
      {
        "table": {
          "table_name": "e1",  # e1 是驱动表
          "access_type": "ALL",  # 访问方法为 ALL，使用全表扫描的方式访问
          "rows_examined_per_scan": 9414,  # 查询一次驱动表 e1 表大约要扫描的记录数 
          "rows_produced_per_join": 1045,  # 驱动表 e1 的扇出
          "filtered": "11.11", # 其他查询条件过滤后的记录的百分比（不包括使用索引的查询条件）
          "cost_info": {
            "read_cost": "1770.62",  # read_cost = IO 成本 + 检查 rows × (1 - filtered) 条记录的 CPU 成本 
            "eval_cost": "209.18",  # 检查 rows × filtered 条记录的成本
            "prefix_cost": "1979.80",  # 单次查询 e1 表的成本，这里 prefix_cost = read_cost + eval_cost
            "data_read_per_join": "2M"  # 读取的数据量
          },
          "used_columns": [  # 查询执行中涉及到的列
            "id",
            "name",
            "employee_id",
            "english_name",
            "country",
            "province",
            "city",
            "extra_info"
          ],
          "attached_condition": "(`test`.`e1`.`extra_info` like '额外信息_827%')"  # e1 表的附加过滤条件
        }
      },
      {
        "table": {
          "table_name": "e2",  # e1 是被驱动表
          "access_type": "ref",  # 访问访问为 ref，使用索引等值匹配的方式访问
          "possible_keys": [  # 可能使用的索引
            "idx_english_name"
          ],
          "key": "idx_english_name",  # 实际使用的索引
          "used_key_parts": [  # 使用了索引的哪些列
            "english_name"
          ],
          "key_length": "403",  # 实际使用的索引的最大长度
          "ref": [
            "test.e1.name"  # 与 english_name 列进行等值匹配的对象
          ],
          "rows_examined_per_scan": 1,  # 查询一次被驱动表 e2 表大约要扫描的记录数
          "rows_produced_per_join": 1045,  # 被驱动表 e2 的扇出（后面没有表要进行连接，所以没用）
          "filtered": "100.00",  # 其他查询条件过滤后的记录的百分比（不包括使用索引的查询条件）
          "cost_info": {
            "read_cost": "1045.90",  # read_cost = IO 成本 + 检查 rows × (1 - filtered) 条记录的 CPU 成本 
            "eval_cost": "209.18",  # 检查 rows × filtered 条记录的成本
            "prefix_cost": "3234.87",  # 单次查询 e1 表+多次查询 e2 表的成本，这里 prefix_cost = e1.prefix_cost + e2.read_cost + e2.eval_cost
            "data_read_per_join": "2M"  # 读取的数据量
          },
          "used_columns": [  # 查询执行中涉及到的列
            "id",
            "name",
            "employee_id",
            "english_name",
            "country",
            "province",
            "city",
            "extra_info"
          ]
        }
      }
    ]
  }
}

Extented EXPLAIN 扩展信息

在使用 EXPLAIN 语句查看了某个查询的执行计划后，紧接着还可以使用 SHOW WARNINGS 语句查看与这个查询的执行计划有关的一些扩展信息，如：

mysql> EXPLAIN SELECT * FROM employee e1 LEFT JOIN employee2 e2 ON e1.name = e2.english_name WHERE e1.extra_info like '额外信息_827%';
-- ...
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`e1`.`id` AS `id`,`test`.`e1`.`name` AS `name`,`test`.`e1`.`employee_id` AS `employee_id`,`test`.`e1`.`english_name` AS `english_name`,`test`.`e1`.`country` AS `country`,`test`.`e1`.`province` AS `province`,`test`.`e1`.`city` AS `city`,`test`.`e1`.`extra_info` AS `extra_info`,`test`.`e2`.`id` AS `id`,`test`.`e2`.`name` AS `name`,`test`.`e2`.`employee_id` AS `employee_id`,`test`.`e2`.`english_name` AS `english_name`,`test`.`e2`.`country` AS `country`,`test`.`e2`.`province` AS `province`,`test`.`e2`.`city` AS `city`,`test`.`e2`.`extra_info` AS `extra_info` from `test`.`employee` `e1` left join `test`.`employee2` `e2` on((`test`.`e2`.`english_name` = `test`.`e1`.`name`)) where (`test`.`e1`.`extra_info` like '额外信息_827%')

可以看到 SHOW WARNINGS 展示出来的信息有三个字段，分别是 Level、Code 和 Message。当 Code 值为 1003 时，Message 字段展示的信息类似于查询优化器将查询语句重写后的语句，但是这个重写后的语句并不一定能在客户端正确运行。

通过 OPTIMIZER TRACE 查看执行计划的生成过程

通过 EXPLAIN 只能看到最终的执行计划，但是有时候我们还想知道这个执行计划是怎么生成的，因此 MySQL 提供了 OPTIMIZER TRACE 功能来让我们查看执行计划的生成过程。这个功能的开启由系统变量 optimizer_trace 来控制：

mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+

enabled=off 表示 OPTIMIZER TRACE 功能默认是关闭的，one_line=off 表示默认的输出格式是多行输出。

如果要打开这个功能，要把 enabled 设置为 on，即：

SET optimizer_trace = 'enabled=on';

一旦这个功能开启，每次查询语句执行完成，我们就可以到 information_schema 数据库的 OPTIMIZER_TRACE 表中查看生成执行计划的完整过程。OPTIMIZER_TRACE 表结构如下：

mysql> DESC information_schema.OPTIMIZER_TRACE;
+-----------------------------------+------------+------+-----+---------+-------+
| Field                             | Type       | Null | Key | Default | Extra |
+-----------------------------------+------------+------+-----+---------+-------+
| QUERY                             | longtext   | NO   |     | NULL    |       |
| TRACE                             | longtext   | NO   |     | NULL    |       |
| MISSING_BYTES_BEYOND_MAX_MEM_SIZE | int(20)    | NO   |     | 0       |       |
| INSUFFICIENT_PRIVILEGES           | tinyint(1) | NO   |     | 0       |       |
+-----------------------------------+------------+------+-----+---------+-------+

可以看到，OPTIMIZER_TRACE 表有 4 列：

QUERY：查询语句
TRACE：执行计划的生成过程
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：生成过程可能是非常长的一段文本，一旦超过某个长度限制，后面的文本将被忽略不显示，这里表示忽略的字节数
INSUFFICIENT_PRIVILEGES：表示是否有权限查看执行计划的生成过程

以 SELECT * FROM employee WHERE country = '中国' AND city IN ('南京', '武汉', '上海'); 为例，完整使用 OPTIMIZER TRACE 功能的步骤如下:

-- 1. 开启 OPTIMIZER TRACE 功能
SET optimizer_trace = 'enabled=on';
-- 2. 执行查询语句
SELECT * FROM employee WHERE name IN ('Tom', '张伟', '李娜') AND employee_id < 100;
-- 3. 从 OPTIMIZER_TRACE 表中查看上一个查询的执行计划的生成过程
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
-- 4. 关闭 OPTIMIZER TRACE 功能（OPTIMIZER TRACE 会影响 MySQL 性能，不使用了要关闭）
SET optimizer_trace="enabled=off";

这个 SQL 的执行计划如下：

mysql> EXPLAIN SELECT * FROM employee WHERE name IN ('Tom', '张伟', '李娜') AND employee_id < 100;
+----+-------------+----------+------------+-------+--------------------------+-----------------+---------+------+------+----------+------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys            | key             | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+----------+------------+-------+--------------------------+-----------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | employee | NULL       | range | idx_employee_id,idx_name | idx_employee_id | 5       | NULL |   99 |    22.05 | Using index condition; Using where |
+----+-------------+----------+------------+-------+--------------------------+-----------------+---------+------+------+----------+------------------------------------+

这个 SQL 的执行计划的生成过程如下：

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
                            QUERY: SELECT * FROM employee WHERE name IN ('Tom', '张伟', '李娜') AND employee_id < 100  # 要生成执行计划的查询语句
                            TRACE: {  # 生成执行计划的具体过程
  "steps": [
    {
      "join_preparation": {  # 1. prepare 准备阶段
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
            "expanded_query": "/* select#1 */ select `employee`.`id` AS `id`,`employee`.`name` AS `name`,`employee`.`employee_id` AS `employee_id`,`employee`.`english_name` AS `english_name`,`employee`.`country` AS `country`,`employee`.`province` AS `province`,`employee`.`city` AS `city`,`employee`.`extra_info` AS `extra_info` from `employee` where ((`employee`.`name` in ('Tom','张伟','李娜')) and (`employee`.`employee_id` < 100))"
          }
        ]
      }
    },
    {
      "join_optimization": {  # 2. optimization 优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {  # 处理 WHERE 子句的查询条件
              "condition": "WHERE",
              "original_condition": "((`employee`.`name` in ('Tom','张伟','李娜')) and (`employee`.`employee_id` < 100))",  # 原始查询条件
              "steps": [
                {
                  "transformation": "equality_propagation",  # 等值传递转换
                  "resulting_condition": "((`employee`.`name` in ('Tom','张伟','李娜')) and (`employee`.`employee_id` < 100))"
                },
                {
                  "transformation": "constant_propagation",  # 常量传递转换
                  "resulting_condition": "((`employee`.`name` in ('Tom','张伟','李娜')) and (`employee`.`employee_id` < 100))"
                },
                {
                  "transformation": "trivial_condition_removal",  # 去除没用的条件
                  "resulting_condition": "((`employee`.`name` in ('Tom','张伟','李娜')) and (`employee`.`employee_id` < 100))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {  # 替换虚拟生成列
            }
          },
          {
            "table_dependencies": [  # 表的依赖信息
              {
                "table": "`employee`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [  # 分析使用不同访问方法的访问成本（单表）⭐️
              {
                "table": "`employee`",
                "range_analysis": {
                  "table_scan": {  # 预估全表扫描的行数和成本
                    "rows": 9414,
                    "cost": 1981.9
                  },
                  "potential_range_indexes": [  # 分析可能使用的索引
                    {
                      "index": "PRIMARY",  # 主键不可用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_employee_id",  # idx_employee_id 可能被使用
                      "usable": true,
                      "key_parts": [
                        "employee_id"
                      ]
                    },
                    {
                      "index": "idx_name",  # idx_name 可能被使用
                      "usable": true,
                      "key_parts": [
                        "name",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_english_name",  # idx_english_name 不可用
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_country_province_city",  # idx_country_province_city 不可用
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {  # 分析可能使用的索引的成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_employee_id",  # 使用 idx_employee_id 索引的成本分析
                        "ranges": [  # 使用 idx_employee_id 索引的范围区间
                          "NULL < employee_id < 100"
                        ],
                        "index_dives_for_eq_ranges": true,  # 是否使用 index dive
                        "rowid_ordered": false,  # 使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,  # 是否使用 MRR（Multi-Range Read，一种将多个不连续的范围查询合并成一个较大的范围查询的优化技术）
                        "index_only": false,  # 是否使用覆盖索引
                        "rows": 99,  # 使用该索引获取的记录数
                        "cost": 119.81,  # 使用该索引的成本
                        "chosen": true  # 是否选择该索引
                      },
                      {
                        "index": "idx_name",  # 使用 idx_name 索引的成本分析
                        "ranges": [
                          "Tom <= name <= Tom",
                          "张伟 <= name <= 张伟",
                          "李娜 <= name <= 李娜"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 2076,
                        "cost": 2494.2,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {  # 分析使用索引合并的成本
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {  # 优化器选择的具体访问方法
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_employee_id",
                      "rows": 99,
                      "ranges": [
                        "NULL < employee_id < 100"
                      ]
                    },
                    "rows_for_plan": 99,
                    "cost_for_plan": 119.81,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [  # 分析各种可能的执行计划（多表）⭐️
              {
                "plan_prefix": [
                ],
                "table": "`employee`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 99,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_employee_id"
                      },
                      "resulting_rows": 99,
                      "cost": 139.61,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 99,
                "cost_for_plan": 139.61,
                "chosen": true  # 表示这个执行计划被选为最终的执行计划
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {  # 为了优化查询而附加的条件
              "original_condition": "((`employee`.`name` in ('Tom','张伟','李娜')) and (`employee`.`employee_id` < 100))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`employee`",
                  "attached": "((`employee`.`name` in ('Tom','张伟','李娜')) and (`employee`.`employee_id` < 100))"
                }
              ]
            }
          },
          {
            "refine_plan": [  # 最后对执行计划的改进
              {
                "table": "`employee`",
                "pushed_index_condition": "(`employee`.`employee_id` < 100)",
                "table_condition_attached": "(`employee`.`name` in ('Tom','张伟','李娜'))"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {  # 3. execution 执行阶段
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0  # 超过长度限制后忽略的字节数
          INSUFFICIENT_PRIVILEGES: 0  # 是否有权限查看执行计划的生成过程

上面这段执行计划的生成过程大致分为 3 个阶段：

prepare 准备阶段
optimize 优化阶段
execute 执行阶段

我们所说的基于成本的优化主要集中在 optimize 阶段，对于单表查询来说，我们主要关注 optimize 阶段的 rows_estimation 这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；对于多表连接查询来说，我们需要关注 considered_execution_plans 这个过程，这个过程里会写明各种不同的连接方式所对应的成本。查询优化器最终会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用 EXPLAIN 语句查询到的那种方案。

MySQL

License: CC BY 4.0