系列文章：MySQL系列专栏

查询优化器

在 MySQL系列（1）— 基础架构这篇文章中，介绍了MySQL的体系架构，在MySQL架构中，有一个很重要的组件就是查询优化器。

在 MySQL Server 层，解析器会将SQL语句解析成一颗解析树，然后由查询优化器将其转化为执行计划。一条查询语句可以有很多种执行方式，最后都返回同样的结果，优化器的作用就是找到这其中最好的执行计划。最后查询执行引擎则根据这个执行计划来完成整个查询。

查询成本

无论是执行单表查询，还是多表关联查询，都有多种执行计划可以选择，比如有的表可以全表扫描，也可以用索引A，也可以用索引B，那么到底是选择哪个执行计划呢?

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

一条SQL查询语句的成本主要有两块：

1、I/O成本

首先数据都是存储在磁盘文件中的，需要先把数据从磁盘读到内存中才能操作。这个从磁盘读数据到内存所损耗的时间就是I/O成本。对于InnoDB来说，页是磁盘和内存之间交互的最小单位，MySQL 约定读一页的IO成本是1.0。

2、CPU成本

拿到数据之后，接着就会对数据做一些运算，比如验证是否满足条件，做一些分组排序的事情，这些都是耗费CPU资源，属于CPU成本。MySQL 约定读取和检测一条数据是否符合条件的CPU成本是0.2。

这个所谓 1.0 和 0.2 就是MySQL自定义的一个成本值，也称为成本常数，代表的意思就是一个数据页IO成本就是 1.0，一条数据检测的CPU成本就是 0.2。

单表查询成本

针对单表查询，查询优化器会找出所有可能的执行计划，然后计算对比各个执行计划的成本，找出成本最低的一个。

例如有一条单表查询语句：

select * from iam_user where phone = '10001' and email = '888@qq.com';

iam_user 表上分别为 phone 和 email 建了普通索引：

KEY `iam_user_n1` (`email`) USING BTREE
KEY `iam_user_n2` (`phone`) USING BTREE

1、找出可能使用的索引

MySQL 首先会根据搜索条件，找出这个SQL可以用到哪几个索引，此时就会发现 iam_user_n1、iam_user_n2 这两个索引都能用到，这也就是执行计划中的 possible_keys。

2、计算全表扫描的成本

接着MySQL会计算全表扫描的成本。全表扫描需要将聚簇索引的叶子节点的数据页都读到内存中，有多少数据页就会耗费多少I/O成本，接着对内存中的每条数据判断是否符合条件，每条数据还会耗费 0.2 的CPU成本。

我们可以通过 show table status like xxx查看表的统计信息：

mysql> show table status like 'iam_user';
+----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+--------------------+---------------------+---------------------+------------+-------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment     | Create_time         | Update_time         | Check_time | Collation   | Checksum | Create_options | Comment |
+----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+--------------------+---------------------+---------------------+------------+-------------+----------+----------------+---------+
| iam_user | InnoDB |      10 | Dynamic    | 28113 |            317 |     8929280 |               0 |      5816320 |   5242880 | 159332009760137304 | 2021-08-23 21:45:38 | 2021-08-23 21:44:30 | NULL       | utf8mb4_bin | NULL     |                |         |
+----------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+--------------------+---------------------+---------------------+------------+-------------+----------+----------------+---------+
1 row in set (0.02 sec)

主要需要关注两个信息：

Rows：就是表中的记录数，对于InnoDB来说，这是一个估计值。这里可以看到 Rows=28113。
Data_length：表示表的聚簇索引的字节数大小，可以用这个值计算出数据页的数量。因为每个页的大小默认是16KB，所以 数据页数量 = Data_length / 1024 / 16，这里可以计算出数据页数量 = 545。

所以全表扫描的I/O成本是：545 * 1.0 = 545，CPU成本是：28113 * 0.2 = 5622.6。总成本就是：545 + 5622.6 = 6167.6。

3、计算不同索引查询的成本

除了全表扫描之外，还可能同时使用多个索引，当然同时一般只能用一个索引，所以不同索引的使用成本都得计算一下。

使用索引访问数据，除了直接根据主键查询可以使用聚簇索引，否则使用二级索引，一般都是先从二级索引查询索引数据和主键，再根据主键去聚簇索引回表查询。

这种查询方式的成本主要来源于两方面：

范围区间数量

首先，在二级索引里根据条件查数据的IO成本，一般是看查询条件涉及到几个范围，比如说某个字段使用 IN 查询，范围在 20 ~ 50，200 ~ 300 两个区间，那么就是两个范围，使用等值查询就只有一个范围区间。所以 phone='10001' 或者 emial = '888@qq.com' 都是一个范围区间。

一般一个范围区间就粗暴的认为等同于一个数据页，所以一般根据二级索引查询的时候，IO成本都比较小，可能就是 1 * 1.0 = 1，或者是 n * 1.0 = n，基本就是个位数级别。

回表的记录数

二级索引数据页读到内存里以后，还得根据搜索条件去筛选数据，此处还需估算从二级索引里读取符合条件的数据的CPU成本。这个过程会根据一定的算法估算从二级索引查出多少条数据。比如估计根据条件能查出10条数据，此时从二级索引查询数据的CPU成本就是 10 * 0.2 = 2。

拿到这 10 条数据后，还要回表到聚簇索引里查询完整数据，这时就需要先估算回表的IO成本。MySQL比较粗暴的认为1条数据就得回表查询聚簇索引的一个数据页，所以10条数据的IO成本就是 10 * 1.0 = 10。

拿到这10条完整的数据后，如果还需根据条件继续判断，则还有CPU成本 10 * 0.2 = 2。

所以二级索引+回表查询总的成本就是：1 + 2 + 10 + 2 = 15。

（从这里也可以看出，如果覆盖索引能将需要的数据都查出来时，就应该尽量避免回表查询，回表查询也是有成本的。）

4、找到成本最低的执行计划

在计算出全表扫描、使用各个索引查询的成本之后，就会对比各个执行计划的成本，然后找出成本最低的一个执行计划。经过计算，全表扫描的成本显示是最大的，使用索引的成本最低。

多表查询成本

MySQL连接查询采用的是嵌套循环连接算法，即先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。

对于两表连接查询来说，它的查询成本主要包含两部分：

单次查询驱动表的成本
多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

多表查询执行计划的选择思路跟单表查询是类似的，先计算驱动表的成本，再计算被驱动表的成本。不过多表连接可能会有多种表顺序连接的方式，不同的连接方式可能成本不同，所以最终MySQL执行的SQL经过优化后，可能跟我们原始的SQL是不一样的。

基于规则的优化

MySQL 的优化器可能会认为我们写的SQL效率不高，就会基于一些规则去改写这个SQL，这个过程也称为查询重写，查询重写的本质就是在优化SQL的语义，使其更清晰，方便后续在索引和数据页里查找。

下面来看一下有哪些查询重写规则。

1、条件简化

移除不必要的括号：表达式里有许多无用的括号时，优化器就会把无关紧要的括号删掉。
常量传递：类似于 a = 5 AND b > a 这样的SQL，就会改写为 a = 5 AND b > 5。
等值传递：类似于 a = b AND b = 10，就会改写为 a = 10 AND b = 10。
移除不必要的条件：类似于 a = a AND b = b，1=1，这种条件就没什么意义，就会被删除。
转化常数表达式：类似于 a > 10 + 5，表达式中只包含常量时，它的值就会先计算出来，变成 a > 15。

2、连接查询优化

重新定义关联表的顺序

数据表的关联并不总是按照在查询中指定的顺序进行，通常多表关联的时候，可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

将外连接转化成内连接

外连接和内连接的本质区别在于，对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃。

内连接的驱动表和被驱动表的位置可以相互转换，而外连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。

诸多因素，例如 wHERE 条件、库表结构都可能会让外连接等价于一个内连接。MySQL 能够识别这点并重写查询，让其可以调整关联顺序。

例如在外连接查询时，在WHERE条件中给被驱动表加上不为空的条件，其就等价于内连接：

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m3 IS NOT NULL;

3、子查询优化

MySQL在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。

不相关子查询

如果子查询可以单独运行出结果，而不依赖于外层查询的值，这个子查询就是不相关子查询。

例如：

SELECT * FROM t1 WHERE x1 = (SELECT x2 FROM t2 WHERE t2.id = xx)

这种不相关子查询会先执行子查询，得到子查询的值，再将子查询得到的结果当做外层查询的参数去执行外层查询。

相关子查询

如果子查询的执行需要依赖于外层查询的值，这个子查询就是相关子查询。

例如：

SELECT * FROM t1 WHERE x1 = (SELECT x2 FROM t2 WHERE t2.m2 = t1.m2)

这种相关子查询WHERE条件依赖于外层子查询，这种查询的效率就会比较低下。它需要遍历外层 t1 表里的每一条数据，取出字段 m2 的值，放到子查询里去查询，然后找出子查询中 t2 表 x2 字段的值，再放到外层去判断 t1 表中这条记录是否满足条件。

IN 子查询优化

例如有下面一个 IN 子查询：

SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2 WHERE m2 = xx)

也许我们会想当然地认为这个 IN 子查询跟不相关子查询一样，会先执行子查询，然后将子查询的结果集放到外层查询中去执行。

其实优化器会将其优化为：先执行子查询，然后将子查询的结果写入一个临时表里，也叫物化表，就是把这个中间结果集进行物化。并且写入临时表的记录会去重，这样可以让临时表变得更小。

一般情况下子查询结果集不会太大，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树聚簇索引。

重要的是，无论如何，这个物化表都会建立索引，子查询的结果集写入物化表是有索引的。

这个时候就可以将 IN 子查询转化为物化表连接查询了，因为物化表中的数据量一般都比外层驱动表中的数据量要小得多，此时就可以反过来，全表扫描物化表，对每个数据值都到原来的驱动表 t1 中去查询数据。

需要注意的是，IN 子查询并不总是按照物化表的方式执行，在某些情况下，也会被优化为连接查询的形式。

子查询转换为 semi-join

semi-join 是半连接的意思，SQL层面并没有 semi-join 的语法，这是MySQL内核里面使用的一种方式。就是在某些场景下，优化器可能将子查询优化为 semi-join 半连接的形式，使用连接查询就可以避免使用物化表了。

例如下面的SQL语句在底层可能就会转化为一个半连接：

SELECT * FROM t1 WHERE x1 = (SELECT t2.x1 FROM t2 WHERE t2.x2=xx);

转化为半连接：

SELECT t1.* FROM t1 SEMI JOIN t2 ON t1.x1=t2.x1 AND t2.x2=xxx;

转化为半连接后，驱动表中的记录在被驱动表判断到只要有一条记录满足，驱动表的记录就可以返回了，跟 JOIN 是不一样的，JOIN 是会和每条满足的记录连接并返回。

不过有时候 semi-join 优化后可能效率更低了，这时可以先通过 EXPLAIN [SQL] SHOW WARNINGS; 查看优化后的SQL，看是否被优化为了 semi-join 半连接查询。然后通过 SET optimizer_switch='semijoin=off' 来关闭 semi-join 优化，对比两次的查询效果。不过一般不建议关闭系统默认配置，此时可以通过调整SQL语句来消除 semi-join 优化。

选择错误的执行计划

这章节最重要的是要了解到MySQL的优化器会对SQL语句进行优化，并且计算多个执行计划的成本，然后选择它认为最优的一个执行计划来执行。

然而MySQL最终选择的执行计划并不一定是最优的，有很多种原因会导致MySQL优化器选择错误的执行计划。

MySQL依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大，所以可能导致统计信息不准确。
执行计划中的成本估算不等同于实际执行的成本，即使统计信息精准，优化器给出的执行计划也可能不是最优的。
MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。
MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。

我们只需要知道MySQL可能会选择并不是最优的执行计划来执行就可以了，这一般就需要我们对SQL进行调优分析，做一些改写来使得其按照我们的意图去执行查询。

如果对某个查询执行 EXPLAIN EXTENDED [SQL;] SHOW WARNINGS; 就可以看到优化器重构后的查询语句。

在某些情况下，如果MySQL选择了错误的执行计划，没有使用我们指定的索引，此时可以使用 force index 语法来强制SQL使用我们指定的索引。

EXPLAIN 查询执行计划

在进行SQL调优时，我们经常会使用 EXPLAIN 来查看SQL的执行计划。在进行SQL调优的时候，核心就是分析执行计划里哪些地方出现了全表扫描，或者扫描数据过大，尽可能通过合理优化索引或者调整SQL语句来保证执行计划每个步骤都可以基于索引执行，避免扫描过多的数据。

先看一个简单的单表SQL查询，通过 EXPLAIN 命令可以看到这个SQL的执行计划。

mysql> EXPLAIN SELECT * FROM country WHERE country = 'China';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | country | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  109 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.02 sec)

EXPLAIN 的各列信息如下：

id：每个 SELECT 关键字都对应一个唯一的id
select_type：查询的类型
table：查询的表（别名）
partitions：分区信息
type：访问方法
possible_keys：可能用到的索引
key：实际上使用的索引
key_len：实际使用到的索引长度
ref：使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows：预估需要读取的记录条数
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比
Extra：一些额外的信息

id

查询语句中每出现一个 SELECT 关键字，MySQL 就会为它分配一个唯一的id值。

简单查询

如果是简单的单表查询，只有一个 SELECT 关键字，则只会有一条 id=1 的记录：

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

连接查询

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

例如下面的SQL查询关联了三张表，只有一个SELECT关键字，所以 id 都等于 1。t1 表是驱动表，而 t2、t3 就是被驱动表。

mysql> EXPLAIN SELECT t1.*, t2.city, t3.country
        FROM address t1 
        LEFT JOIN city t2 ON t1.city_id = t2.city_id 
        LEFT JOIN country t3 ON t2.country_id = t3.country_id 
       WHERE t1.address_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t3    | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

子查询

如果是包含子查询的SQL语句，就可能有多个SELECT关键字，那么每个SELECT关键字都会对应一个唯一的id值。

例如下面的SQL，外层 t1 表的执行计划对应 id=1 这条记录，子查询的执行计划对应 id=2 这条记录。

mysql> EXPLAIN SELECT t1.* 
        FROM city t1 
        WHERE country_id = (SELECT country_id FROM country WHERE country = 'China');
+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t1      | NULL       | ref  | idx_fk_country_id | idx_fk_country_id | 2       | const |   53 |   100.00 | Using where |
|  2 | SUBQUERY    | country | NULL       | ALL  | NULL              | NULL              | NULL    | NULL  |  109 |    10.00 | Using where |
+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+

UNION 查询

如果是 UNION 查询，每个 SELECT 也会对应一条记录，但是还会多一条 id=NULL 的记录。因为 UNION 查询时会创建一个内部的临时表，然后把多个查询的结果放入这个临时表中，然后对结果集中的记录去重。

例如下面的SQL查询计划，UNION 为了把id=1的查询和id=2的查询的结果集合并起来并去重，在内部创建了一个名为 <union1, 2> 的临时表，id=NULL 就表明这个临时表是为了合并两个查询的结果集而创建的。

mysql> EXPLAIN SELECT * FROM address WHERE city_id = 10 
            UNION SELECT * FROM address WHERE city_id = 20;
+------+--------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra           |
+------+--------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------+
|    1 | PRIMARY      | address    | NULL       | ref  | idx_fk_city_id | idx_fk_city_id | 2       | const |    1 |   100.00 | NULL            |
|    2 | UNION        | address    | NULL       | ref  | idx_fk_city_id | idx_fk_city_id | 2       | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL           | NULL           | NULL    | NULL  | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------+

因为 UNION 需要去重，所以会创建内部临时表，如果是 UNION ALL 则不需要去重，所以就不会有 id=NULL 这条记录了。

mysql> EXPLAIN SELECT * FROM address WHERE city_id = 10 
            UNION ALL SELECT * FROM address WHERE city_id = 20;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | address | NULL       | ref  | idx_fk_city_id | idx_fk_city_id | 2       | const |    1 |   100.00 | NULL  |
|  2 | UNION       | address | NULL       | ref  | idx_fk_city_id | idx_fk_city_id | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

select_type

这一列显示了对应的行是简单查询还是复杂查询，MySQL官方文档中显示有如下类型。

SIMPLE

SIMPLE 就是非常简单的查询，意味着不包含 UNION 或子查询，连接查询也算是 SIMPLE 类型。

PRIMARY

如果查询有任何复杂的子部分，如 UNION、UNION ALL、子查询，则最外层部分标记为 PRIMARY。

UNION

在 UNION 或 UNION ALL 子句中的第二个之后的 SELECT 都会被标记为 UNION。

UNION RESULT

UNION 查询需要使用内存临时表来去重，针对该临时表的查询的 select_type 就是 UNION RESULT，上面介绍 UNION 时可以看到这个类型。

SUBQUERY

包含在 SELECT 列表中的子查询中的 SELECT 会标记为 SUBQUERY，不包含 FROM 子查询。

DERIVED

DERIVED 用来表示包含在 FROM 子句的子查询中的 SELECT，MySQL 会将子查询的结果物化，放到一个临时表中，也称为 派生表，因为该临时表是从子查询中派生来的。

例如下面的SQL执行计划，id=2 的记录就代表子查询的执行方式，它的 select_type=DERIVED，表示该子查询是以物化的方式执行的。第二行记录的 table=<derived2>，表示该查询是针对将派生表物化之后的表进行查询的，第一行记录则是驱动表。

mysql> EXPLAIN SELECT * FROM country t1 
        LEFT JOIN (SELECT country_id, count(*) FROM city group by country_id) der 
            ON t1.country_id = der.country_id;
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys     | key               | key_len | ref                    | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------------------------+------+----------+-------------+
|  1 | PRIMARY     | t1         | NULL       | ALL   | NULL              | NULL              | NULL    | NULL                   |  109 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>       | <auto_key0>       | 2       | sakila_t.t1.country_id |   10 |   100.00 | NULL        |
|  2 | DERIVED     | city       | NULL       | index | idx_fk_country_id | idx_fk_country_id | 2       | NULL                   |  600 |   100.00 | Using index |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------------------------+------+----------+-------------+

DEPENDENT UNION、DEPENDENT SUBQUERY、DEPENDENT DERIVED

DEPENDENT 意味着 SELECT 依赖于外层查询中发现的数据。

例如下面的SQL，子查询依赖于外层 t1 表中的数据，所以其 select_type 带有 DEPENDENT。

mysql> EXPLAIN SELECT * FROM address t1 WHERE EXISTS (SELECT 1 FROM city WHERE city_id = t1.city_id);
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                |  603 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | city  | NULL       | eq_ref | PRIMARY       | PRIMARY | 2       | sakila_t.t1.city_id |    1 |   100.00 | Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+

MATERIALIZED

MATERIALIZED 意味着SQL中包含子查询（非FROM子查询），且子查询是物化的方式和外层查询进行连接查询的。

例如下面的执行计划，第三行记录表明 IN 子查询是以物化表的方式执行；接着看第二条记录，它是一个 SIMPLE 查询，从 Extra 可以看出，它是将 city 表所有数据查到内存中的；最后看第一条记录，也是 SIMPLE 查询，但它的 table=<subquery2>，这里其实就是用 IN 子查询物化表中的数据去前面内存中的 city 数据做关联查询。

mysql> EXPLAIN SELECT * FROM city WHERE city IN (SELECT country FROM country);
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type  | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |   100.00 | NULL                                               |
|  1 | SIMPLE       | city        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  600 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | MATERIALIZED | country     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  109 |   100.00 | NULL                                               |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

UNCACHEABLE SUBQUERY、UNCACHEABLE UNION

UNCACHEABLE 意味着 SELECT 中的某些特性阻止结果被缓存。

table

这一列显示了对应行正在访问哪个表，一般情况下它就是访问的那个表，或是该表的别名。可以从这一列中从上往下观察SQL执行的关联顺序。

当有 FROM 子查询、UNION 查询时，这一列则不同，显示的是物化表，这点从前面的演示中可以得知。

partitions

这一列跟表分区相关，一般情况下查询语句的执行计划的 partitions 列的值都是 NULL。

type

这一列表示这行的访问类型或者访问方法，就是MySQL决定如何查找表中的行。这个属性是分析执行计划时需要重点关注的信息，它告诉我们SQL是如何查询这张表的数据，有没有用到索引等等。

下面是访问方法，依次从最优到最差。

NULL

这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。

system

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

const

当根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const。出现 const 表明查询是极为快速的。

eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是 eq_ref，被驱动表最多只会返回一条符合条件的记录。

ref

当通过普通二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref。这是一种索引访问方式，相比 eq_ref，ref 可能返回多条符合条件的记录。

ref_or_null

当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是 ref_or_null。

index_merge

一般情况下对于某个表的查询只会用到一个索引，但也可能同时使用多个索引，使用索引合并的方式来执行查询，这时访问方法就可能是 index_merge。

unique_subquery

在一些包含 IN 子查询的SQL语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询使用的方法就是 unique_subquery。

index_subquery

类似于 unique_subquery，只不过访问子查询中的表时如果使用的是普通索引，则访问方法是 index_subquery。

range

range 表示基于索引的范围扫描，返回索引某些范围区间的记录。这比 index 全索引扫描好一些，因为它不用遍历全部索引。

index

index 类似于 ALL 全表扫描，不过 index 是顺序扫描索引的全部记录。它的主要优点是避免了排序；但如果要回表的话，开销可能比 ALL 更大。

ALL

ALL 就是全表扫描，通常意味着MySQL必须扫描整张表，应尽量避免这种方法的出现。

key

possible_keys：显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。
key：实际使用到的索引，也可能不在 possible_keys 中。
key_len：表示使用的索引的最大长度（字节数）。

ref

当使用索引列等值匹配的条件去执行查询时，ref 列显示与索引列作等值匹配的对象，比如是常量、某个字段，或者是一个函数等。

rows & filtered

rows：表示使用指定的查询方式（type）会读出来的行数，注意它并不是最终要从表里读取出来的行数，只是一个预估值。

filtered：表示针对读出来的行数（rows），再用上其它的查询条件（WHERE子句或连接条件），又可以过滤出百分之几的数据来。

例如下面的SQL执行计划，针对 actor 表的查询方式是 range，也就是基于主键索引的范围查询。用 id < 100 这个条件通过 PRIMARY 主键索引查询出来的数据大概是 rows=99 条；接着对这 99 条数据再使用 WHERE 里的其它的条件，就是 first_name='CUBA'，估计可以过滤出 filtered=10% 的数据来；也就是最终查出来的数据大概是 99 * 10% = 10 条数据。

mysql> EXPLAIN SELECT * FROM actor WHERE actor_id < 100 AND first_name = 'CUBA';
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | range | PRIMARY       | PRIMARY | 2       | NULL |   99 |    10.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+

其实 filtered 在单表查询中意义不是很大，一般在多表连接中，则可用于预测被驱动表会被查询多少次。

Extra

这一列包含的是不适合在其他列显示的额外信息，这些额外信息可用来更准确的理解MySQL将如何执行给定的查询语句。常见的一些额外信息如下。

Impossible WHERE

查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息。

例如下面的SQL执行计划：

mysql> EXPLAIN SELECT * FROM city WHERE 1 > 2;
+----+-------------+-------+------------+------+------------------+
| id | select_type | table | partitions | type | Extra            |
+----+-------------+-------+------------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+------------------+

Using Index

表示使用了 覆盖索引，没有 回表 查询，就是仅仅涉及到一个二级索引。

注意和 type=index 的区别，覆盖索引是说查询只用到了二级索引，而没有回表查询。index 是全索引扫描。

例如下面的查询计划，因为只查询了 last_name，且在 idx_actor_last_name 这个索引中，因此只用到 idx_actor_last_name 这个索引覆盖查询就可以了，不用再回表查询。所以 Extra 显示了 Using Index。

mysql> EXPLAIN SELECT last_name FROM actor WHERE last_name = 'CHASE';
+----+-------------+-------+------+---------------------+---------------------+-------------+
| id | select_type | table | type | possible_keys       | key                 | Extra       |
+----+-------------+-------+------+---------------------+---------------------+-------------+
|  1 | SIMPLE      | actor | ref  | idx_actor_last_name | idx_actor_last_name | Using index |
+----+-------------+-------+------+---------------------+---------------------+-------------+

Using where

一般在没有使用到索引，执行全表扫描时，并且 WHERE 子句中有针对该表的搜索条件，就会提示 Using where。或者用到了索引，但还有其它 WHERE 条件，也可能会提示 Using where。

例如下面的SQL：

mysql> EXPLAIN SELECT * FROM actor WHERE last_name LIKE '%CH';
+----+-------------+-------+------------+------+---------------+-------------+
| id | select_type | table | partitions | type | possible_keys | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | Using where |
+----+-------------+-------+------------+------+---------------+-------------+

Using temporary

出现 Using temporary 意味着MySQL内部使用了临时表来完成一些功能，比如去重、排序之类的，前面演示 UNION 时，因为 UNION 要对结果集去重，所以就使用了临时表。

例如下面的第三条记录的 Extra 就提示使用了临时表。

mysql> EXPLAIN SELECT * FROM address WHERE city_id = 10 
            UNION SELECT * FROM address WHERE city_id = 20;
+------+--------------+------------+------+----------------+----------------+-----------------+
| id   | select_type  | table      | type | possible_keys  | key            | Extra           |
+------+--------------+------------+------+----------------+----------------+-----------------+
|    1 | PRIMARY      | address    | ref  | idx_fk_city_id | idx_fk_city_id | NULL            |
|    2 | UNION        | address    | ref  | idx_fk_city_id | idx_fk_city_id | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL           | NULL           | Using temporary |
+------+--------------+------------+------+----------------+----------------+-----------------+

Using filesort

有些排序可以直接使用索引的排序，但很多时候可能无法使用索引排序，这时就需要将数据查到内存中，或者在数据了较大时放到磁盘中，再进行排序。这种在内存中或者磁盘上进行排序的方式统称为文件排序（filesort），在 Extra 列就会显示 Using filesort。

例如下面的SQL：

mysql> EXPLAIN SELECT * FROM country ORDER BY country;
+----+-------------+---------+------------+------+---------------+------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | Extra          |
+----+-------------+---------+------------+------+---------------+------+----------------+
|  1 | SIMPLE      | country | NULL       | ALL  | NULL          | NULL | Using filesort |
+----+-------------+---------+------------+------+---------------+------+----------------+

要注意文件排序这个过程是很耗费性能的，当出现 Using filesort 时，最好能优化SQL或索引，将文件排序的执行方式改为使用索引进行排序。

Using join buffer (Block Nested Loop)

在连接查询执行过程中，当被驱动表没有索引或者不能有效的利用索引加快访问速度时，MySQL一般会为其分配一块名叫 join buffer 的内存块，然后将数据读到这块内存块中，再基于块中的数据做嵌套循环查询，以此来加快查询速度。

这个在前面也出现过，例如下面的查询，city 表的数据就被全表扫描到 join buffer 中，再做嵌套查询。

mysql> EXPLAIN SELECT * FROM city WHERE city IN (SELECT country FROM country);
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type  | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |   100.00 | NULL                                               |
|  1 | SIMPLE       | city        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  600 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | MATERIALIZED | country     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  109 |   100.00 | NULL                                               |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

上面执行计划的SQL语句是：SELECT * FROM city WHERE city IN (SELECT country FROM country)，IN 中是是一个子查询。

再对上面的执行计划做一个详细分析：

首先看 id 列，前两条 id = 1，说明这两条执行计划的表是平级关联的，第三条执行计划的 id=2，说明它可能是第二条执行计划的子查询。
看第三条执行计划，针对的表是 city，可以看到没有使用任何索引和过滤条件，所以 rows=109，filtered=100，说明数据会全部查出来，而且其 select_type=MATERIALIZED，说明会将结果集放到一个临时表中去。
接着看第二条执行计划，针对的是 city 表，同样也没有索引，所以 rows=600；但会发现它的 Extra 列里有 Using where;，说明用到了 where 条件来过滤数据，而且看 filtered=10，也就是可以过滤出 10% 的数据。那他这个 using where 是哪来的呢？
然后第二条执行计划 Extra 显示 Using join buffer (Block Nested Loop)，说明它使用了内存 buffer 来缓存数据，然后做 join 操作，我们的 SQL 中明明没有 join，他这个 join 又是从哪来的呢？
再看第一条执行计划，它的表是 <subquery2>，这其实就是第三条计划中产生的临时表，表中的数量可能就是 109 条。然后会发现它是在第一行的，说明 <subquery2> 是驱动表，city 表变成了被驱动表，city 表中的数据是 600 条的样子。这下就知道第二条计划中的 using where 其实就是在和这个临时表做关联查询，并且能够过滤出 10% 的数据。

这其实就是MySQL优化了 IN 子查询，他认为 IN 子查询的结果集更小，然后先执行子查询，并把结果集物化到临时表中。然后用这个临时小表去驱动大表来查询数据，这就是小表驱动大表。

我们再用 SHOW WARNINGS; 命令查看 MySQL 优化后的 SQL，可以看到 IN 子查询被优化为了一个 SEMI JOIN 查询。

SELECT
`sakila_t`.`city`.`city_id` AS `city_id`,
`sakila_t`.`city`.`city` AS `city`,
`sakila_t`.`city`.`country_id` AS `country_id`,
`sakila_t`.`city`.`last_update` AS `last_update` 
FROM
	`sakila_t`.`city` 
	SEMI JOIN ( `sakila_t`.`country` ) 
WHERE
	( `sakila_t`.`city`.`city` = `<subquery2>`.`country` )

树形格式输出

如果想更加精确地展示执行计划，可以在 EXPLAIN 后面加上 FORMAT=JSON 来将输出格式化为一颗树。在这棵树中，可以看到每步执行的成本。

mysql> EXPLAIN FORMAT=JSON SELECT * FROM country ORDER BY country;
+----------------------------------------------------------------------------+
| EXPLAIN     |
+----------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "131.80"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "109.00"
      },
      "table": {
        "table_name": "country",
        "access_type": "ALL",
        "rows_examined_per_scan": 109,
        "rows_produced_per_join": 109,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1.00",
          "eval_cost": "21.80",
          "prefix_cost": "22.80",
          "data_read_per_join": "17K"
        },
        "used_columns": [
          "country_id",
          "country",
          "last_update"
        ]
      }
    }
  }
} |
+----------------------------------------------------------------------------+
1 row in set (0.05 sec)

MySQL系列（12）— 查询执行计划