一、准备数据
为了更好地看看剖析执行计划，需要一个数据量不算小的数据库作为示例。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE s1 ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), KEY idx_key1 (key1), UNIQUE KEY idx_key2 (key2), KEY idx_key3 (key3), KEY idx_key_part(key_part1, key_part2, key_part3) ) Engine=InnoDB CHARSET=utf8; |
还有一张与 s1 表结构相同的 s2 表，而且这两个表里边儿有10000条记录，除id列外其余的列都插入随机值。插入数据的存储过程如下。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DELIMITER $ drop procedure if exists general $ CREATE PROCEDURE `general`(IN n INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE d INT DEFAULT 1; DECLARE c INT DEFAULT 1; WHILE i <= n DO SELECT i%26 into d; SELECT i%10 into c; INSERT INTO s1(`key1`,`key2`,`key3`,`key_part1`,`key_part2`,`key_part3`,`common_field`) \ values(substring('abcdefghijklmnoplrstuvwxyz',d,1),i,substring('fncdaebgij',c,1), \ substring('cdaebgfhij',c,1),left(uuid(),2),left(uuid(),3),substring('fncdaebgij',c,1)); SET i = i + 1; END WHILE; END $ DELIMITER ; |
调用很简单，直接 call general(100000) 即可。
二、EXPLAIN OUTPUT FORMAT
首先得知道在 MySQL Server 层有一个称为查询优化器的模块，一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。My SQL 为我们提供了 EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划，本章的内容就是为了帮助大家看懂 EXPLAIN 语句的各个输出项都是干嘛使的，从而可以有针对性的提升我们查询语句的性能。
执行计划在 MySQL 中作用非常大，在执行计划的帮助下，你就知道什么时候该给表添加索引，以及使用什么样的索引来查找记录从而让查询运行更快。如果由于不恰当使用索引而引起一些问题的话，可以运行 ANALYZE TABLE 来更新该表的统计信息，如索引键的基数（Cardinality），它能帮您在优化方面做出更好的选择。
如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN，就像这样：
|
1 2 3 4 5 6 7 |
mysql> explain select * from s1 limit 1; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+ 1 row in set, 1 warning (0.00 sec) |
其实除了以 SELECT 开头的查询语句，其余的 DELETE、INSERT、REPLACE 以及 UPDATE 语句前边都可以加上 EXPLAIN 关键字，用来查看这些语句的执行计划。EXPLAIN 结果的每行记录显示了每个表的相关信息，每行记录都包含以下几个字段，下面一一介绍这些字段的意义。
- id
我们写的查询语句一般都以 SELECT 关键字开头，比较简单的查询语句里只有一个 SELECT 关键字，稍微复杂一点的联接查询、子查询或 UNION 查询中出现多个 SELECT 关键字。查询语句中每出现一个 SELECT 关键字，MySQL 就会为它分配一个唯一的 id 值。
对于联接查询来说，一个 SELECT 关键字后边的 FROM 子句中可以跟随多个表，所以在联接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的，比如如下查询：
|
1 2 3 4 5 6 7 8 |
mysql> explain select * from s1 join s2 on s1.id=s2.id; +----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100165 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) |
在联接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表。所以从上边的 EXPLAIN 输出中我们可以看出，查询优化器准备让 a 表作为驱动表，让 b 表作为被驱动表来执行查询。
对于包含子查询的查询语句来说，就可能涉及多个 SELECT 关键字，所以在包含子查询的查询语句的执行计划中，每个 SELECT 关键字都会对应一个唯一的id值。
但是这里大家需要特别注意，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为联接查询，此时id值就会相同了。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，就看id值是否相同，相同就表明了查询优化器将子查询转换为了联接查询。
对于包含 UNION 子句的查询语句来说，每个 SELECT 关键字对应一个id值也是没错的，不过还是有点儿特别的东西，比方说下边这个查询：
|
1 2 3 4 5 6 7 8 9 |
mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ 3 rows in set, 1 warning (0.01 sec) |
我们知道 UNION 子句会把多个查询的结果集合并起来并对结果集中的记录进行去重，怎么去重呢？MySQL 使用的是内部的临时表。正如上边的查询计划中所示，UNION 子句是为了把 id 为1的查询和 id 为2的查询的结果集合并起来并去重，所以在内部创建了一个名为 <union1, 2> 的临时表（就是执行计划第三条记录的 table 列的名称），id 为 NULL 表明这个临时表是为了合并两个查询的结果集而创建的。
跟 UNION 对比起来，UNION ALL 就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含 UNION ALL 子句的查询的执行计划中，就没有那个 id 为 NULL 的记录，如下所示：
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) |
不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的。所以根据 id 值，SQL 执行的规则如下：
* id 相同时，执行顺序由上至下。
* 如果是子查询，id 的序号会递增，id值越大优先级越高，越先被执行。
* 如果id 相同，则认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行。
- select_type
通过上边的内容我们知道，一条大的查询语句里边可以包含若干个 SELECT 关键字，每个 SELECT 关键字代表着一个小的查询语句，而每个 SELECT 关键字的 FROM 子句中都可以包含若干张表（这些表用来做联接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个 SELECT 关键字中的表来说，它们的 id 值是相同的。
MySQL 为每一个 SELECT 关键字代表的小查询都定义了一个称之为 select_type 的属性，意思是我们只要知道了某个小查询的 select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。select_type 列提供了各种表示 table 列引用的使用方式的类型。可能会有以下几种：
SIMPLE
对于不包含子查询或UNION查询的简单查询，都算SIMPLE类型，包括联接查询，这是一个常见的类型。
PRIMARY
这是为更复杂的查询而创建的首要表（也就是最外层的表），这个类型通常可以在 DERIVED、UNION 及 UNION ALL 使用时见到。
|
1 2 3 4 5 6 7 8 9 |
mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec) |
从结果中可以看到，最左边的小查询 SELECT * FROM s1 对应的是执行计划中的第一条记录，它的 select_type 值就是 PRIMARY。
UNION
表示内层表，对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最外层的那个小查询以外，其余的小查询的 select_type 值就是 UNION。也就是说外层表是 PRIMARY，内层表就是 UNION 了。
DEPENDENT UNION
在包含 UNION 或者 UNION ALL 的查询中，如果各个小查询都依赖于外层查询的话，那除了最左边（外层）的那个小查询之外，其余的小查询的 select_type 的值就是 DEPENDENT UNION。比如这个查询：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b'); +----+--------------------+------------+------------+------+---------------+----------+---------+-------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+--------+----------+--------------------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 10000 | 100.00 | Using where; Using index | | 3 | DEPENDENT UNION | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 10000 | 100.00 | Using where; Using index | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+--------+----------+--------------------------+ 4 rows in set, 1 warning (0.01 sec) |
这个查询比较复杂啊，大查询里包含了一个子查询，子查询里又是由 UNION 连起来的两个小查询。从执行计划中可以看出来，SELECT key1 FROM s2 WHERE key1 = ‘a’ 这个小查询由于是子查询中第一个查询，所以它的 select_type 是 DEPENDENT SUBQUERY，而 SELECT key1 FROM s1 WHERE key1 = ‘b’ 这个查询的 select_type 就是 DEPENDENT UNION。
UNION RESULT
MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT，例子上边有，就不赘述了。
SUBQUERY
如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 SUBQUERY。
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; +----+--------------------+-------+------------+----------------+---------------+----------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+----------------+---------------+----------+---------+------+--------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 100165 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | index_subquery | idx_key1 | idx_key1 | 303 | func | 1 | 100.00 | Using index | +----+--------------------+-------+------------+----------------+---------------+----------+---------+------+--------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) |
简单说就是此子查询为独立子查询，不依赖外部查询。独立子查询会被物化，只需执行一次，关于 SUBQUERY，海翔老师有详细阐述。
DEPENDENT SUBQUERY
如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是相关子查询，相关子查询表示子查询中的第一个子查询依赖于外部的子查询，速度是子查询中最慢的，需要执行多次。该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY。
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a'; +----+--------------------+-------+------------+------+-------------------+----------+---------+--------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+------+-------------------+----------+---------+--------------+--------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 100165 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key2,idx_key1 | idx_key2 | 5 | test.s1.key2 | 1 | 10.00 | Using where | +----+--------------------+-------+------------+------+-------------------+----------+---------+--------------+--------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec) |
DERIVED
当一个表不是物理表时，那么就被叫做 derived，称为派生表（from 子句中的子查询，是从子查询中派生出来的虚拟表中产生的）。形式：FROM (subquery expression) AS derived_table_alias。对于采用物化的方式执行的包含派生表的查询（派生表被物化），该派生表对应的子查询的 select_type 就是 DERIVED。 比方说下边这个查询：
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1; +----+-------------+------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 33.33 | Using where | | 2 | DERIVED | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 100165 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) |
从执行计划中可以看出，id 为2的记录就代表子查询的执行方式，它的 select_type 是 DERIVED，说明该子查询是以物化的方式执行的。id 为1的记录代表外层查询，大家注意看它的 table 列显示的是 <derived2>，表示该查询是针对将派生表物化之后的表进行查询的。
MATERIALIZED
当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的 select_type 属性就是 MATERIALIZED，表示物化子查询，MySQL 引入了 Materialization（物化） 这一关键特性用于子查询（比如在 IN/NOT IN 子查询以及 FROM 子查询）优化。比如下边这个查询：
|
1 2 3 4 5 6 7 8 9 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a'); +----+--------------+-------------+------------+--------+---------------+------------+---------+--------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------+------------+---------+--------------+--------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 100080 | 100.00 | Using where | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 303 | test.s1.key1 | 1 | 100.00 | NULL | | 2 | MATERIALIZED | s2 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 100165 | 10.00 | Using where | +----+--------------+-------------+------------+--------+---------------+------------+---------+--------------+--------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec) |
执行计划的第三条记录的 id 值为2，说明该条记录对应的是一个单表查询，从它的 select_type 值为 MATERIALIZED 可以看出，查询优化器是要把子查询先转换成物化表。然后看执行计划的前两条记录的 id 值都为1，说明这两条记录对应的表进行联接查询，需要注意的是第二条记录的 table 列的值是 <subquery2>，说明该表其实就是 id 为2对应的子查询执行之后产生的物化表，然后将 s1 和该物化表进行联接查询。
具体实现方式是：在 SQL 执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表，后续对子查询结果集的访问将直接通过临时表获得。与此同时，优化器还具有延迟物化子查询的能力，先通过其它条件判断子查询是否真的需要执行。物化子查询优化 SQL 执行的关键点在于对子查询只需要执行一次，与之相对的执行方式是对外表的每一行都对子查询进行调用，其执行计划中的查询类型为 “DEPENDENT SUBQUERY”。
UNCACHEABLE SUBQUERY
子查询的结果不能被缓存，必须重新评估每行的外部查询。用的不多。
UNCACHEABLE UNION
UNION 中的第二个或后面的 SELECT 属于 UNCACHEABLE SUBQUERY。用的不多。
- table
不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所以 MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的 table 列代表着该表的表名。当然，表名也可以是一个别名或者一个为查询产生临时表的标识符，如派生表、子查询和集合。输出引用行的表的名字，有以下几种情况：
<unionM,N>
指的是 UNION 查询中 id 值在 M 和 N 之间的行。比如 <union1, 2> 表示 UNION 子句是为了把 id 为1的查询和id为2的查询的结果集合并起来并去重。
<derivedN>
指派生表结果集在这行中的 id 值为 N。比如 <derived2> 表示 id 为1的外层查询是针对将派生表物化之后的表进行查询的。
<subqueryN>
指的是物化结果集在这行的 id 值为 N。比如 <subquery2> 表示 id 为1的外层查询是针对 id 为2对应的子查询执行之后产生的物化表进行查询的。
- partitions
查询匹配的记录分区，只有在分区关键词被使用时，这列才会显示。一般的查询该列都为 NULL。
- type
执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法，其中的 type 列就表明了这个访问方法是什么？也称之为表的访问路径方式，以下列出了各种不同类型的 MySQL 访问路径方式，依次是从最好的到最差的（理论上）：
system
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory ，那么对该表的访问方法就是 system。比方说我们新建一个 MyISAM 表，并为其插入一条记录：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> CREATE TABLE t(i int) Engine=MyISAM; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t VALUES(1); Query OK, 1 row affected (0.01 sec) mysql> EXPLAIN SELECT * FROM t; +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) |
可以看到 type 列的值就是 system 了。
const
这个值表示，表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。由于只有一行记录，在余下的优化程序里该行记录的字段值可以被当作是一个恒定值。const 表查询起来非常快，因为只要读取一次，其成本几乎忽略不计！
就是当我们根据主键或者唯一二级索引的所有列与常数进行等值匹配时，对单表的访问方法才是 const。也就是说，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个 const 访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。
对于唯一二级索引来说，查询该列为 NULL 值的情况比较特殊，比如这样：
|
1 |
SELECT * FROM tbl_name WHERE key1 IS NULL; |
因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说上边这个语句不可以使用 const 访问方法来执行。
eq_ref
这个值出现在联接查询计划中，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是 eq_ref。如果唯一索引或主键是多列时，只有所有的列都用作比较时才会出现 eq_ref，如果只是组合索引的左前缀匹配则会变成ref。除了 system 和 const 类型之外，这是最好的联接类型。
下面这个例子中，MySQL 使用了 eq_ref 联接来处理 ref_table：
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; +----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100165 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) |
从执行计划的结果中可以看出，MySQL 打算将 s1 作为驱动表，s2 作为被驱动表，重点关注 s2 的访问方法是 eq_ref，表明在访问 s2 表的时候可以通过主键的等值匹配来进行访问。
ref
对于一个普通的单表查询，有时候我们对某个普通的二级索引列与常数进行等值比较，比如这样：
|
1 |
SELECT * FROM ref_table WHERE key_column=expr; |
对于这个查询，我们当然可以选择全表扫描来逐一对比搜索条件是否满足要求，我们也可以先使用二级索引找到对应记录的主键值，然后再回表到聚簇索引中查找完整的用户记录。由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的，所以 MySQL 可能选择使用索引而不是全表扫描的方式来执行查询。这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。
对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种 ref 访问方法比 const 差了那么一丢丢，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的（如果匹配的二级索引记录太多那么回表的成本就太大了）。
Tips：对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用 ref 的访问方法。但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为 ref 了。
另外，多表联接时，被驱动表中所有符合检索值的记录都会被取出来和驱动表中取出来的记录做联接。如果检索被驱动表只使用主键或唯一索引的最左前缀匹配，或检索被驱动表使用二级索引，则访问路径为 ref。如果使用的二级索引检索被驱动表时仅仅匹配少量行，该联接类型还是不错的。
以下这个例子中，MySQL 将使用 ref 联接来处理 ref_table：
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1; +----+-------------+-------+------------+------+---------------+----------+---------+--------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+--------------+--------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 100165 | 100.00 | Using where | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | test.s1.key1 | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+--------------+--------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) |
fulltext
使用全文索引执行查询。
ref_or_null
该联接类型类似 ref，不同的是 MySQL 会在检索的时候额外的搜索包含 null 值的记录。这种联接类型的优化是从 MySQL 4.1.1 开始的，它经常用于子查询优化。在以下的例子中，MySQL 使用 ref_or_null 联接来处理 ref_table：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL; +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+-------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+-------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | ref_or_null | idx_key1 | idx_key1 | 303 | const | 10001 | 100.00 | Using index condition | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+-------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) |
上边的查询相当于先分别从 key_column 索引对应的 B+树 中找出 key_column IS NULL 和 key_column = expr 的两个连续的记录范围，然后根据这些二级索引记录中的主键值再回表查找完整的用户记录。
index_merge
在 MySQL 5.0 之前对于单个表的查询只能使用到一个索引，但是从 MySQL 5.1 开始，引入了 Index Merge 优化技术，对单个表可以使用多个索引分别进行条件扫描。常见的 Index Merge 方法有 Intersection、Union、Sort-Union 这三种。官方排序这个在 ref_or_null 之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如 range。具体可以看index_merge。
unique_subquery
类似于两表联接中被驱动表的 eq_ref 访问方法，unique_subquery 是针对在一些包含IN子查询的查询语句中，形式如：value in (select primary_key from single_table where some_expr)。如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery，比如下边的这个查询语句：
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a'; +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+--------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 100165 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4 | func | 1 | 10.00 | Using where | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+--------+----------+-------------+ 2 rows in set, 2 warnings (0.01 sec) |
可以看到执行计划的第二条记录的 type 值就是 index_subquery，说明在执行子查询时会使用到id列的索引。
index_subquery
该联接类型类似于 unique_subquery，可以替换 IN 子查询，但只适合下列形式的子查询中的非唯一索引：value in (select key_column from single_table where some_expr)。也就是访问子查询中的表时使用的是普通的索引。
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a'; +----+--------------------+-------+------------+------+-------------------+----------+---------+--------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+------+-------------------+----------+---------+--------------+--------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 100165 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key1,idx_key3 | idx_key1 | 303 | test.s1.key1 | 1 | 10.00 | Using where | +----+--------------------+-------+------------+------+-------------------+----------+---------+--------------+--------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec) |
range
如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法，比如下边的这个查询：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c'); +----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 11541 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) |
执行计划中 key 列显示使用了哪个索引，key_len 包含所使用索引的最长关键元素。在该类型中 ref 列为 NULL。
其实对于 B+树 索引来说，只要索引列和常数使用 =、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者 LIKE 操作符连接起来，就可以产生一个所谓的区间，是可以使用 range。
Tips： LIKE 操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引。 IN 操作符的效果和若干个等值匹配操作符`=`之间用 OR 连接起来是一样的，也就是说会产生多个单点区间。
下面示例查询都会使用 range：
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30); |
一般来说，等值查找比范围查找需要扫描的行数更少（也就是 ref 的访问方法一般比 range 好，但这也不总是一定的，也可能采用 ref 访问方法的那个索引列的值为特定值的行数特别多）。
index
此类型表示扫描整棵索引树，但不适用索引B+树结构，只使用索引叶子节点链表结构进行扫描。我们知道B+树各个叶子节点使用双向链表结构，并且是排好序的。其联结类型和 ALL 的方式差不多，访问效率并不高，其主要的应用场景为用于避免 order by 使用 using filesort，也就是避免排序。
虽然其联接类型跟all（全表扫描）差不都，但分两种情况：
- 如果索引是覆盖索引，并且可用于满足查询中所需的所有数据，则仅扫描索引树。在这种情况下，Extra 列显示 Using index。仅扫描索引通常比ALL快，因为索引的大小通常小于表数据。因为 InnoDB 二级索引的记录只包含索引列和主键列的值，而聚簇索引中包含用户定义的全部列以及一些隐藏列，所以扫描二级索引的代价比直接全表扫描，也就是扫描聚簇索引的代价更低一些。
- 执行全表扫描，以按索引顺序查找数据行。Using index 不会出现在 Extra 列中。
当查询只使用作为单索引一部分的列时，MySQL 可以使用该联接类型。
all
此类型表示将对该表做全表扫描，对于 InnoDB 表来说也就是直接扫描聚簇索引。这时候如果第一个表没有被标识为 const 的话就不大好了（对于多表联接），在其他情况下通常是非常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免all。一般就算有利用到索引时，也可能会出现全表扫描的情况，当需要范围的数据占总表的百分之 25 左右时，优化器会选择全表扫描，这是因为全表扫描是顺序读数据，是顺序 IO。当然，具体还是要看优化器得出的 cost 值。
Tips：type 类型出现的值依次排序：system -> const -> eq_ref -> ref -> ref_or_null -> unique_subquery -> index_subquery -> range -> index_merge -> index -> all，越往后理论上可以理解为其开销越大。
- possible_keys
此列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，也称之为可选择的索引，最终可能会从 possible_keys 中列出的索引选择一个或多个进行条件检索，当然也可能并不会使用索引。如果该列是 NULL，则代表没有相关的索引。在这种情况下，可以通过检查 WHERE 子句看它是否引用了某些列或适合索引的列来提高查询性能。如果是这样，那么就需要创造一个适当的索引，并再次用 EXPLAIN 检查。
不过有一点比较特别，就是在使用 index 访问方法来查询某个表时，possible_keys 列是空的，而 key 列展示的是实际使用到的索引，比如下面查询：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | s1 | NULL | index | NULL | idx_key_part | 909 | NULL | 100165 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.01 sec) |
另外需要注意的一点是，possible_keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。
- key
显示 MySQL 实际决定使用的键（索引），如果 MySQL 使用 possible_keys 列出的索引列表中的一个索引来进行条件检索，则该索引就会被显示在 key 列上，此索引是经过查询优化器计算使用不同索引的成本后得出的最优路径（当然这个索引根据统计数据的误差也可能并不是最优的）。如果没有选择索引，则 key 是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX 关键字即可。
- key_len
key_len 列表示当优化器决定使用某个索引执行查询时，使用了所选择的索引的长度有多少字节，比如对于 int 类型的单列主键索引，那么 key_len 就是4个字节。通常我们可借 key_len 判断组合索引有多少索引列被使用了，对于确认组合索引的有效性很重要，这也是 key_len 被设计的意义所在。另外，索引的最大长度为128位。
key_len 等于被使用索引列类型字节总长度，比如组合索引三个字段，但只使用了2个字段，那么 ken_len 就是这2个字段类型的总长度。而 key_len 大小的计算规则大概如下：
- int 类型为4 bytes，bigint 为8 bytes；
- 于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，同时还需要考虑字符集因素，例如：CHAR(30) UTF8则 key_len 至少是90 bytes；
- 若该列类型定义时允许NULL，其key_len还需要再加1 bytes；
- 若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引，也被视为动态列类型），其 key_len 还需要再加2 bytes，这两个2个字节的空间用来存储变长列的实际长度。
Tips：如果知道 InnoDB 存储变长字段的实际长度时可能占用1个字节或者2个字节？为什么在 key_len 的计算上不管三七二十一都用了2个字节？这里需要强调的一点是，执行计划的生成是在 MySQL server 层中的功能，并不是针对具体某个存储引擎的功能，MySQL 在执行计划中输出 key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列，而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间到底是占用1个字节还是2个字节。
综上，看下面几个例子：
| 列类型 | KEY_LEN | 备注 |
|---|---|---|
| id int | key_len = 4+1 = 5 | 允许NULL，加1 byte |
| id int not null | key_len = 4 | 不允许NULL |
| user char(30) utf8 | key_len = 30*3+1 | 允许NULL |
| user varchar(30) not null utf8 | key_len = 30*3+2 | 动态列类型，加2 bytes |
| user varchar(30) utf8 | key_len = 30*3+2+1 | 动态列类型，加2 bytes；允许NULL，再加1 byte |
| detail text(10) utf8 | key_len = 30*3+2+1 | TEXT列截取部分，被视为动态列类型，加2 bytes；且允许NULL |
备注，key_len 只指示了 WHERE 中用于条件过滤时被选中的索引列，是不包含 ORDER BY/GROUP BY 这部分被选中的索引列。
比方说下边这个使用到联合索引 idx_key_part 的查询，索引 idx_key_part(key_part1 varchar(100) not null, key_part2 varchar(100) not null, key_part3 varchar(100) not null)：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a'; +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 303 | const | 17344 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+ 1 row in set, 1 warning (0.00 sec) |
我们可以从执行计划的 key_len 列中看到值是303，这意味着 MySQL 在执行上述查询中只能用到 idx_key_part 索引的一个索引列，而下边这个查询：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'; +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 606 | const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) |
这个查询的执行计划的 ken_len 列的值是606，说明执行这个查询的时候可以用到联合索引 idx_key_part 的两个索引列。
- ref
当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 其中之一时，ref 列展示的就是与索引列作等值匹配的东东是什么，比如只是一个常数或者是某个列。大家看下边这个查询：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+-------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+-------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 10000 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+-------+----------+-------+ 1 row in set, 1 warning (0.00 sec) |
可以看到 ref 列的值是 const，表明在使用 idx_key1 索引执行查询时，与 key1 列作等值匹配的对象是一个常数，当然有时候更复杂一点：
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; +----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100165 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) |
可以看到对被驱动表 s2 的访问方法是 eq_ref，而对应的 ref 列的值是 test.s1.id，这说明在对被驱动表进行访问时会用到 PRIMARY 索引，也就是聚簇索引与一个列进行等值匹配的条件，于 s2 表的 id 作等值匹配的对象就是 test.s1.id 列（注意这里把数据库名也写出来了）。 如果为 NULL 表示没有。
有的时候与索引列进行等值匹配的对象是一个函数，比方说下边这个查询：
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1); +----+-------------+-------+------------+------+---------------+----------+---------+------+--------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+------+--------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | func | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+------+---------------+----------+---------+------+--------+----------+-----------------------+ 2 rows in set, 1 warning (0.00 sec) |
我们看执行计划的第二条记录，可以看到对 s2 表采用 ref 访问方法执行查询，然后在查询计划的 ref 列里输出的是 func，说明与 s2 表的 key1 列进行等值匹配的对象是一个函数。
- rows
这个 rows 就是 MySQL 认为必须要逐行去检查和判断的记录的条数，不要误解为实际返回的行数了。在使用到索引的情况下，这个值就代表预计扫描的索引记录行数，但这个值在 MySQL 内部是通过一定的算法实际访问对应的索引估算得到的，并不能很精确。最好的估值是1，一般来说这种情况发生在当寻找的行在表中可以通过主键或者唯一键找到的时候。
举个例子来说，假如有一个语句 select * from t where column_a = 1 and column_b = 2; 全表假设有 100 条记录，column_a 字段有独立索引，column_b 没有索引。那么 column_a = 1 的记录有 20 条， column_a = 1 and column_b = 2 的记录有 5 条。那么最终查询结果应该是显示 5 条记录，但 rows 应该是 20，因为这 20 条记录 MySQL 引擎必须逐行检查是否满足 where 条件。当然，前面说了 rows 的值是通过实际访问 column_a 索引并通过一定的算法估算得到的值，所以不一定会这么精确。
有些查询显示的 rows 比较特殊，比如 in 查询，在 MySQL 内部就是 or 操作，把每个值都进行一次定位搜索操作，所以这里的 rows 就是每个值需要扫描的索引行数的累加。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> desc select * from t1 where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> desc select * from t1 where id in (1,2); +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) |
还有一种比较特殊的情况，rows 值是通过一系列计算方式得到的成本。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> show table status like 't1'\G *************************** 1. row *************************** Name: t1 Rows: 32 mysql> desc select * from t1 where a>10; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t1 | NULL | range | iabc | iabc | 5 | NULL | 16 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc select * from t1 where a>10 and c>=3 order by id limit 2; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | index | iabc,ic | PRIMARY | 4 | NULL | 4 | 26.56 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) |
可以看到通过统计信息得到的 t1 表有 32 行数据。我们使用 a>10 的条件可以看到走了 iabc 组合索引（a,b,c），rows 为 16。当我们使用 a>10 and c>=3 order by id limit 2 查询时走了主键索引扫描，因为优化器觉得走主键扫描的成本比走 iabc 索引要低，因为避免了排序并且只需要返回 2 行数据，所以这里 rows 只有 4。至于成本怎么计算的不是这里的重点，只想说 rows 等于 4 是怎么计算出来的，优化器知道 t1 表数据是 32 行，并通过 a>10 估算出 rows 为 16。所以，优化器拿 32/16 得到查询一行的成本是 2，然后拿 limit 2 乘以查询每一行的成本 2，得到了 4 这个值。
- filtered
这个字段的结果指的是预估实际返回结果集的行数（MySQL 层 where 过滤后的数据集）占需要扫描到的行数（rows 列的值）的百分比。简单说就是查询优化器预测 rows 列的值会有多少条记录满足其余的搜索条件，也就是实际返回用户需要的最终结果集。一般来说 filtered 的值越高越好，越低证明查询代价花的越高。
比如下面这个执行计划：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 < 'b' AND common_field = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 7693 | 10.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ 1 row in set, 1 warning (0.00 sec) |
从执行计划的 key 列中可以看出来，该查询使用 idx_key1 索引来执行查询，从 rows 列可以看出满足 key1 < ‘b’ 的记录有 7693 条。执行计划的 filtered 列就代表查询优化器预测在这 7693 条记录中，有多少条记录满足其余的搜索条件，也就是common_field = ‘a’这个条件的百分比。此处filtered列的值是10.00，说明查询优化器预测在 7693 条记录中有 10.00% 的记录满足 common_field = ‘a’ 这个条件，套用公式 7693 * 10 / 100 的值 769 大概就是实际需要返回的记录。看一下结果：
|
1 2 3 4 5 6 7 |
mysql> SELECT count(*) FROM s1 WHERE key1 < 'b' AND common_field = 'a'; +----------+ | count(*) | +----------+ | 769 | +----------+ 1 row in set (0.05 sec) |
对于单表查询来说，这个 filtered 列的值意义可能不大。有意义的在于联接查询中驱动表对应的执行计划记录的 filtered 值，驱动表的结果集大小直接影响了被驱动表循环的次数（rows * filtered / 100 可以作为驱动表的返回行的参考值）。
比方说下边这个查询：
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+--------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+--------------+--------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 100080 | 10.00 | Using where | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | test.s1.key1 | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+--------------+--------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) |
从执行计划中可以看出来，查询优化器打算把 s1 当作驱动表，s2 当作被驱动表。我们可以看到驱动表 s1 表的执行计划的 rows 列为 100080， filtered列为 10.00，这意味着驱动表 s1 的扇出值就是 100080 × 10.00% = 10008，这说明还要对被驱动表执行大约 10008 次查询。
在 MySQL 5.7.3 以前，如果你使用 EXPLAIN EXTENDED，filtered 这列会显示。到了 MySQL 5.7.3，扩展输出默认开启，扩展关键词也不是必须的（废除了）。
- extra
Extra 列提供了有关不同种类的 MySQL 优化器路径的一系列额外信息。我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句。MySQL 提供的额外信息有好几十个，并且这些值还在随着 MySQL 新版本的发布而进一步增加。这里只挑一些平时常见的或者比较重要的额外信息介绍给大家。
not exists
当我们使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的，那么在该表的执行计划的 Extra 列就会提示 Not exists 额外信息，比如下面这样：
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL; +----+-------------+-------+------------+------+---------------+----------+---------+--------------+--------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+--------------+--------+----------+-------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100080 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | test.s1.key1 | 1 | 10.00 | Using where; Not exists | +----+-------------+-------+------------+------+---------------+----------+---------+--------------+--------+----------+-------------------------+ 2 rows in set, 1 warning (0.00 sec) |
上述查询中 s1 表是驱动表，s2 表是被驱动表，s2.id 列是不允许存储 NULL 值的，而 WHERE 子句中又包含 s2.id IS NULL 的搜索条件，这意味着必定是驱动表的记录在被驱动表中找不到匹配 ON 子句条件的记录才会把该驱动表的记录加入到最终的结果集，所以对于某条驱动表中的记录来说，如果能在被驱动表中找到1条符合 ON 子句条件的记录，那么该驱动表的记录就不会被加入到最终的结果集，也就是说我们没有必要到被驱动表中找到全部符合 ON 子句条件的记录，这样可以稍微节省一点性能。
using filesort
当我们使用索引来为查询结果排序时，由于索引本身就是排序的，所以速度很快。MySQL 可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序。
但是很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL 把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的 Extra 列中显示 Using filesort 提示。
如果查询中需要使用 filesort 的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用 filesort 的执行方式改为使用索引进行排序。另外，在使用 filesort 时，MySQL 也这对这种情况做了多种不同的排序算法根据不同的场景，具体可以看 ORDER BY。
using where
using where 表示查询条件需要在 MySQL Server 进行过滤，而没有在引擎层过滤掉数据，这就需要看看索引是否合理了。InnoDB 引擎层需要通过全表扫描或扫描索引回表一行一行的扫描数据发送给 MySQL Server 层，这个过程通过 show processlist 命令可以看到进程状态显示为 sending data。
当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。比如下边这个查询：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = 'a'; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100080 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) |
当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时，在 Extra 列中也会提示上述额外信息。比如下边这个查询虽然使用 idx_key1 索引执行查询，但是搜索条件中除了包含 key1 的搜索条件 key1 = ‘a’，还有包含 common_field 的搜索条件，所以 Extra 列会显示 Using where 的提示：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 3847 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) |
索引一般是游标首次定位 index read key 需要做索引查找，然后逐步读取 index read next 。MySQL Server 层拿到数据后如果还需要过滤，其执行计划显示为 using where。
另外，需要注意的一点，当查询条件使用了索引，但表达式为非 ‘=’ 时，我们还是会看到 Using where。这是因为就算存储引擎层根据条件返回数据到 MySQL Server 层之后，MySQL Server 层还是会再判断一次，所以就会出现 Using where。
搞明白 Using where 之前，必须先搞明白根据何登成大神总结出一套放置于所有 SQL 语句而皆准的 where 查询条件的提取规则。所有 SQL 的 where 条件均可归纳为3大类：Index Key (First Key & Last Key)，Index Filter，Table Filter。详情请看：SQL语句中，where条件在数据库中提取与应用浅析。
using index
当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在 Extra 列将会提示该额外信息。比方说下边这个查询中只需要用到 idx_key1 而不需要回表操作：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 3847 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) |
using where; using index
表示使用到了覆盖索引，同时又使用到了 index filter 进行数据过滤。
|
1 2 3 4 5 6 7 |
mysql> explain select key_part1 from s1 where key_part2='a'; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | s1 | NULL | index | NULL | idx_key_part | 909 | NULL | 100080 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) |
这里 key_part2=’a’ 本身是无法直接使用索引的，因为它并不是 idx_key_part 组合索引的最做前缀，但是由于我们需要投影的列 key_part1 也属于 idx_key_part 组合索引，所以这里就不需要扫描表了，而是直接对索引进行扫描过滤即可得到结果集。如果需要投影的列有非索引列，那么就需要进行全表扫描了，也就不存在 using where; using index 额外信息。
using index condition
首先 MySQL Server 和 Storage Engine 是两个组件，MySQL Server 负责 SQL 的解析，优化，生成执行计划；而 Storage Engine 去真正的做数据/索引的读取/写入。Index Condition Pushdown (ICP，索引条件下推) 是MySQL 5.6版本中的新特性，是一种在存储引擎层根据二级索引过滤 where 条件，从而达到减少回表次数的一种优化方式。不难看出，索引条件下推的重点就是索引过滤了，简单说就是有些搜索条件中虽然出现在了索引列，但却不能使用到索引，而 ICP 就是把不能使用索引的搜索条件在存储引擎层数据回表前就过滤掉，进而减少回表次数。具体看：MySQL ICP（Index Condition Pushdown）特性
Using join buffer (Block Nested Loop)
在联接查询执行过程过，当被驱动表不能有效的利用索引加快访问速度，MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法（Block Nested Loop），比如下边这个查询语句：
|
1 2 3 4 5 6 7 8 |
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100080 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 100165 | 10.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) |
可以看到对 s2 表的执行计划的 Extra 列显示了两个提示：
Using join buffer (Block Nested Loop)：这是因为对表s2的访问不能有效利用索引，只好退而求其次，使用join buffer来减少对s2表的访问次数，从而提高性能。
Using where：可以看到查询语句中有一个 s1.common_field = s2.common_field 条件，因为 s1 是驱动表，s2 是被驱动表，所以在访问s2表时，s1.common_field 的值已经确定下来了，所以实际上查询 s2 表的条件就是 s2.common_field = 一个常数，所以提示了 Using where 额外信息。
using temporary
在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含 DISTINCT、GROUP BY、UNION 等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的 Extra 列将会显示 Using temporary 提示，比方说这样：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100080 | 100.00 | Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec) |
上述执行计划的 Extra 列不仅仅包含 Using temporary 提示，还包含 Using filesort 提示，可是我们的查询语句中明明没有写 ORDER BY 子句呀？这是因为 MySQL 会在包含 GROUP BY 子句的查询中默认添加上 ORDER BY 子句，也就是说上述查询其实和下边这个查询等价：
|
1 |
EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY common_field; |
如果我们并不想为包含 GROUP BY 子句的查询进行排序，需要我们显式的写上 ORDER BY NULL，就像这样：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field ORDER BY NULL; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 100080 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec) |
这回执行计划中就没有 Using filesort 的提示了，也就意味着执行查询时可以省去对记录进行文件排序的成本了。
另外，执行计划中出现 Using temporary 并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表。
Start temporary, End temporary
在进行子查询的时候，查询优化器会优先尝试将IN子查询转换成 semi-join，而 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) 提示。
Zero limit
当我们的 LIMIT 子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息，比如这样：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 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 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ 1 row in set, 1 warning (0.00 sec) |
Using intersect(...)、Using union(...)和Using sort_union(...)
如果执行计划的 Extra 列出现了 Using intersect(…) 提示，说明准备使用 Intersect 索引合并的方式执行查询，括号中的…表示需要进行索引合并的索引名称；如果出现了 Using union(…) 提示，说明准备使用 Union 索引合并的方式执行查询；出现了 Using sort_union(…) 提示，说明准备使用 Sort-Union 索引合并的方式执行查询。具体每种索引合并技术的细节看索引合并章节。
No matching min/max row
当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该额外信息，比方说：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | 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 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ 1 row in set, 1 warning (0.01 sec) |
Impossible WHERE
查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息，比方说：
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 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 | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.01 sec) |
No tables used
当查询语句的没有 FROM 子句时将会提示该额外信息，比如：
|
1 2 3 4 5 6 7 |
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 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) |
Using MRR
使用 MRR（Multi Range Read，多范围读取） 的优化策略来读取表中的数据。
三、EXPLAIN OUTPUT JSON FORMAT
原始 EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本。在 MySQL 5.7 中 EXPLAIN 支持了 JSON 格式输出，里边儿包含该执行计划花费的成本，比如下面这样：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
mysql> EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, # 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1 "cost_info": { "query_cost": "3197.16" # 整个查询的执行成本预计为3197.16 }, "nested_loop": [ # 几个表之间采用嵌套循环连接算法执行 # 以下是参与嵌套循环连接算法的各个表的信息 { "table": { "table_name": "s1", # s1表是驱动表 "access_type": "ALL", # 访问方法为ALL，意味着使用全表扫描访问 "possible_keys": [ # 可能使用的索引 "idx_key1" ], "rows_examined_per_scan": 9688, # 查询一次s1表大致需要扫描9688条记录 "rows_produced_per_join": 968, # 驱动表s1的扇出是968 "filtered": "10.00", # condition filtering代表的百分比 "cost_info": { "read_cost": "1840.84", # 稍后解释 "eval_cost": "193.76", # 稍后解释 "prefix_cost": "2034.60", # 单次查询s1表总共的成本 "data_read_per_join": "1M" # 读取的数据量 }, "used_columns": [ # 执行查询中涉及到的列 "id", "key1", "key2", "key3", "key_part1", "key_part2", "key_part3", "common_field" ], # 对s1表访问时针对单表查询的条件 "attached_condition": "((`xiaohaizi`.`s1`.`common_field` = 'a') and (`xiaohaizi`.`s1`.`key1` is not null))" } }, { "table": { "table_name": "s2", # s2表是被驱动表 "access_type": "ref", # 访问方法为ref，意味着使用索引等值匹配的方式访问 "possible_keys": [ # 可能使用的索引 "idx_key2" ], "key": "idx_key2", # 实际使用的索引 "used_key_parts": [ # 使用到的索引列 "key2" ], "key_length": "5", # key_len "ref": [ # 与key2列进行等值匹配的对象 "xiaohaizi.s1.key1" ], "rows_examined_per_scan": 1, # 查询一次s2表大致需要扫描1条记录 "rows_produced_per_join": 968, # 被驱动表s2的扇出是968（由于后边没有多余的表进行连接，所以这个值也没啥用） "filtered": "100.00", # condition filtering代表的百分比 # s2表使用索引进行查询的搜索条件 "index_condition": "(`xiaohaizi`.`s1`.`key1` = `xiaohaizi`.`s2`.`key2`)", "cost_info": { "read_cost": "968.80", # 稍后解释 "eval_cost": "193.76", # 稍后解释 "prefix_cost": "3197.16", # 单次查询s1、多次查询s2表总共的成本 "data_read_per_join": "1M" # 读取的数据量 }, "used_columns": [ # 执行查询中涉及到的列 "id", "key1", "key2", "key3", "key_part1", "key_part2", "key_part3", "common_field" ] } } ] } } 1 row in set, 2 warnings (0.00 sec) |
来看 “cost_info” 里边的成本是怎么计算出来的？先看 s1 表的 “cost_info” 部分：
|
1 2 3 4 5 6 |
"cost_info": { "read_cost": "1840.84", "eval_cost": "193.76", "prefix_cost": "2034.60", "data_read_per_join": "1M" } |
read_cost 是由下边这两部分组成的：
- IO成本。
- 检测 rows × (1 – filtered) 条记录的 CPU 成本。rows 和 filtered 都是我们前边介绍执行计划的输出列，在 JSON 格式的执行计划中，rows 相当于 rows_examined_per_scan，filtered 名称不变。
eval_cost 是这样计算的：
- 检测 rows × filtered 条记录的成本。
prefix_cost 就是单独查询s1表的成本，也就是：
- read_cost + eval_cost
data_read_per_join 表示在此次查询中需要读取的数据量。
Tips： 大家其实没必要关注 MySQL 为啥使用这么古怪的方式计算出 read_cost 和 eval_cost，关注 prefix_cost 是查询 s1 表的成本就好了。
对于s2表的 “cost_info” 部分是这样的：
|
1 2 3 4 5 6 |
"cost_info": { "read_cost": "968.80", "eval_cost": "193.76", "prefix_cost": "3197.16", "data_read_per_join": "1M" } |
由于 s2 表是被驱动表，所以可能被读取多次，这里的 read_cost 和 eval_cost 是访问多次 s2 表后累加起来的值，主要关注里边的 prefix_cost 的值代表的是整个联接查询预计的成本，也就是单次查询 s1 表和多次查询 s2 表后的成本的和，也就是：968.80 + 193.76 + 2034.60 = 3197.16。
