MySQL EXPLAIN 备忘

EXPLAIN 语句提供了有关 MySQL 如何执行语句的信息。

EXPLAIN 为 SELECT 语句中使用的每个表返回一行信息，它按照 MySQL 在处理语句时读取它们的顺序列出。 MySQL 使用嵌套循环的方式来解决所有的 join 操作，这意味着 MySQL 从第一个表中读取一行，然后在第二个表、第三个表中找到匹配的行，依此类推。处理完所有表后，MySQL 会输出选定的列并通过 table 列表回溯，直到找到匹配行较多的表。从此表中读取下一行，并且该过程继续应用到下一个表。

EXPLAIN 的每个输出行都提供有关一个表的信息。每行包含下表中汇总的值，并在表后进行了更详细的描述。

列	含义
id	查询标识
select_type	查询使用的类型
table	输出行用到的表
partitions	匹配的分区
type	join 的类型
possible_keys	可能会选择的索引
key	实际选择的索引
key_len	选择的索引所用的长度（字节）
ref	与索引比较的列
rows	估计要检测的行数
filtered	按表条件过滤的行百分比
Extra	附加信息

数据准备

文中使用的数据表结构如下

CREATE TABLE `app_metric` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `rel_id` bigint(20) unsigned NOT NULL COMMENT '外部ID',
  `m_key` char(2) DEFAULT NULL COMMENT 'key',
  `val` float DEFAULT NULL COMMENT '值',
  PRIMARY KEY (`id`),
  KEY `idx_rel_id_m_key_gmt_create` (`rel_id`,`m_key`,`gmt_create`)
) ENGINE=InnoDB AUTO_INCREMENT=60000001 DEFAULT CHARSET=utf8mb4 COMMENT='指标表';

CREATE TABLE `sys_dictionary` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `const_key` varchar(50) NOT NULL DEFAULT '常量key',
  `val` varchar(50) NOT NULL DEFAULT '值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='系统字典表';

app_metric 表中写入了 6 千万数据，sys_dictionary 只写入了少量数据，数据内容如下。

 select * from app_metric limit 10;
+----+---------------------+--------+-------+-------+
| id | gmt_create          | rel_id | m_key | val   |
+----+---------------------+--------+-------+-------+
|  1 | 2022-06-17 02:44:23 |      7 | aa    | 10.14 |
|  2 | 2022-06-17 02:44:23 |      7 | ab    | 10.14 |
|  3 | 2022-06-17 02:44:23 |      7 | ac    | 10.14 |
|  4 | 2022-06-17 02:44:23 |      7 | ad    | 10.14 |
|  5 | 2022-06-17 02:44:23 |      7 | ae    | 10.14 |
|  6 | 2022-06-17 02:44:23 |      7 | af    | 10.14 |
|  7 | 2022-06-17 02:44:23 |      7 | ag    | 10.14 |
|  8 | 2022-06-17 02:44:23 |      7 | ah    | 10.14 |
|  9 | 2022-06-17 02:44:23 |      7 | ai    | 10.14 |
| 10 | 2022-06-17 02:44:23 |      7 | aj    | 10.14 |
+----+---------------------+--------+-------+-------+

select * from sys_dictionary;
+----+-----------+-----------------+
| id | const_key | val             |
+----+-----------+-----------------+
|  1 | aa        | xavier_cpu_temp |
|  2 | ab        | mt8666_cpu_temp |
+----+-----------+-----------------+

id

SELECT 标识。这是查询中 SELECT 的序号。如果该行引用其他行的 union 结果，则该值可以为 NULL。在这种情况下，表格列显示一个类似 <union M,N> 的值，以指示该行引用 id 值为 M 和 N 的行的并集。

mysql> explain
    -> select  m.*,d.const_key from app_metric m, sys_dictionary d where m.m_key=d.const_key and m.id = 1
    -> union
    -> select  m.*,d.const_key from app_metric m, sys_dictionary d where m.m_key=d.const_key and m.id = 20;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | m          | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL            |
|  1 | PRIMARY      | d          | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    2 |   100.00 | Using where     |
|  2 | UNION        | m          | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL            |
|  2 | UNION        | d          | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    2 |   100.00 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

如果 SELECT id 相同，执行顺序从上至下。
如果 SELECT id 不同，id 的值越大优先级越高，越先被执行。

select_type

SELECT 的类型，可以是下表中显示的任何一种。

select_type Value	Meaning
SIMPLE	简单查询不包含子查询或 union 操作
PRIMARY	查询中若包含复杂的子部分（如子查询、union），最外层查询则被标记为主查询
UNION	UNION 查询中的第二个或之后 SELECT，被标记为UNION
DEPENDENT UNION	UNION 作为子查询时候，查询中的第二个或之后的 SELECT
DEPENDENT SUBQUERY	UNION 作为子查询时候，查询中的第一个 SELECT
UNION RESULT	UNION 查询的结果
SUBQUERY	子查询的第一个 SELECT
DERIVED	MySQL 5.7 使用了 Merge Derived table 优化，查询类型变为SIMPLE
MATERIALIZED	物化子查询？
UNCACHEABLE SUBQUERY	结果不能被缓存的子查询，必须为外部查询的每一行重新计算
UNCACHEABLE UNION	UNION 作为不能缓存的子查询时，查询中的第二个或之后的 SELECT

UNION 示例

mysql> explain
    -> select id from app_metric where id =10
    -> union
    -> select id from app_metric where id =11
    -> union
    -> select id from app_metric where id =13;
+----+--------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | app_metric   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index     |
|  2 | UNION        | app_metric   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index     |
|  3 | UNION        | app_metric   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2,3> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

DEPENDENT UNION | DEPENDENT SUBQUERY 示例

mysql> explain
    -> select * from app_metric where id in(
    -> select id from app_metric where id =10
    -> union
    -> select id from app_metric where id =11
    -> union
    -> select id from app_metric where id =13
    -> );
+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+----------+----------+-----------------+
| id | select_type        | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows     | filtered | Extra           |
+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+----------+----------+-----------------+
|  1 | PRIMARY            | app_metric   | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 59812819 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | app_metric   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |        1 |   100.00 | Using index     |
|  3 | DEPENDENT UNION    | app_metric   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |        1 |   100.00 | Using index     |
|  4 | DEPENDENT UNION    | app_metric   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |        1 |   100.00 | Using index     |
| NULL | UNION RESULT       | <union2,3,4> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |     NULL |     NULL | Using temporary |
+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+----------+----------+-----------------+

SUBQUERY 示例

mysql> explain select * from app_metric where id = (select m2.id from app_metric m2 , sys_dictionary s where m_key = const_key and s.const_key='aa' and m2.id<10 );
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | app_metric | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL                                               |
|  2 | SUBQUERY    | s          | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    2 |    50.00 | Using where                                        |
|  2 | SUBQUERY    | m2         | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL  |    9 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+----------------------------------------------------+

table

输出行所引用的表的名称。这也可以是以下值之一：

<unionM,N>: 行引用的是 id 值为 M 和 N 的行的并集。
<derivedN>: 行引用的是 id 值为 N 的行的派生表结果，派生表可能来自例如 FROM 子句中的子查询。
<subqueryN>: 行引用的是 id 值为 N 的行的具体化子查询的结果。

partitions

查询将匹配记录的分区。对于非分区表，该值为“NULL”。

type

描述了表是如何连接的。以下列表描述了连接类型，按从最佳到最差的顺序排列。

system

该表只有一行，这是 const 类型的一个特例。

const

该表最多有一个匹配行。因为只有一行，所以该行中列的值可以被优化器的其余部分视为常量。 const 表非常快，因为它们只被读取一次。

当使用 PRIMARY KEY 或 UNIQUE 索引列进行等值比较时候，将使用 const。

eq_ref

当连接使用索引的所有部分并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时为 eq_ref。

mysql> explain select * from app_metric m1,(select id from app_metric m2) t where m1.id = t.id and t.id in(2,3);
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+
|  1 | SIMPLE      | m2    | NULL       | range  | PRIMARY       | PRIMARY | 8       | NULL      |    2 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | m1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | db0.m2.id |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------+

ref

与 const，eq_ref 类似，当做等值比较，使用的索引是 PRIMARY KEY 或 UNIQUE 的一部分，或者非 PRIMARY KEY 及 UNIQUE 的索引时候为 ref。

mysql> explain select  id from app_metric where rel_id=7;
+----+-------------+------------+------------+------+-----------------------------+-----------------------------+---------+-------+----------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys               | key                         | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+------------+------------+------+-----------------------------+-----------------------------+---------+-------+----------+----------+-------------+
|  1 | SIMPLE      | app_metric | NULL       | ref  | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 8       | const | 10947356 |   100.00 | Using index |
+----+-------------+------------+------------+------+-----------------------------+-----------------------------+---------+-------+----------+----------+-------------+

fulltext

使用 FULLTEXT 索引执行连接。

ref_or_null

类似于 ref，但是 MySQL 会额外搜索包含 NULL 值的行。这种连接类型优化最常用于解析子查询。

mysql> explain select  id from app_metric where rel_id=7 and(m_key='aa' or m_key is null);
+----+-------------+------------+------------+-------------+-----------------------------+-----------------------------+---------+-------------+--------+----------+--------------------------+
| id | select_type | table      | partitions | type        | possible_keys               | key                         | key_len | ref         | rows   | filtered | Extra                    |
+----+-------------+------------+------------+-------------+-----------------------------+-----------------------------+---------+-------------+--------+----------+--------------------------+
|  1 | SIMPLE      | app_metric | NULL       | ref_or_null | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 17      | const,const | 174577 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------------+

index_merge

表明使用了索引合并优化。在这种情况下，输出行中的键列包含使用的索引列表，而 key_len 包含使用的索引的最长键部分的列表。

mysql> explain select  id from app_metric where id=4 or (rel_id=7 and m_key='aa');
+----+-------------+------------+------------+-------------+-------------------------------------+-------------------------------------+---------+------+--------+----------+--------------------------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys                       | key                                 | key_len | ref  | rows   | filtered | Extra                                                              |
+----+-------------+------------+------------+-------------+-------------------------------------+-------------------------------------+---------+------+--------+----------+--------------------------------------------------------------------+
|  1 | SIMPLE      | app_metric | NULL       | index_merge | PRIMARY,idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create,PRIMARY | 17,8    | NULL | 174577 |   100.00 | Using sort_union(idx_rel_id_m_key_gmt_create,PRIMARY); Using where |
+----+-------------+------------+------------+-------------+-------------------------------------+-------------------------------------+---------+------+--------+----------+--------------------------------------------------------------------+

unique_subquery

对于以下形式的某些 IN 子查询，此类型替换 eq_ref。

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery 只是一个索引查找函数，它完全替换了子查询以提高效率。

index_subquery

这个跟 unique_subquery 非常相似，唯一的差别就是子查询查的不是 PRIMARY 或 UNIQUE 索引而是非唯一索引。

range

仅检索给定范围内的行，使用一个索引来选择行。此类型的 ref 列为 NULL。

mysql> explain select  * from app_metric where id < 5;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | app_metric | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    4 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

index

索引连接类型与 ALL 类似，都是扫描全量数据，区别是 index 只扫描索引树。仅索引扫描通常比 ALL 快，因为索引的大小通常小于表数据。这个一般有两种方式：

如果索引是查询的覆盖索引并且可以用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra 列显示 Using index
使用从索引中读取的内容执行完整表扫描，以索引顺序查找数据行。Extra 列不显示 Using index。

mysql> explain select  id from app_metric;
+----+-------------+------------+------------+-------+---------------+-----------------------------+---------+------+----------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key                         | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-----------------------------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | app_metric | NULL       | index | NULL          | idx_rel_id_m_key_gmt_create | 22      | NULL | 59812819 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-----------------------------+---------+------+----------+----------+-------------+

ALL

进行全表扫描，最慢的联接类型，尽可能的避免。

mysql> explain select  id from app_metric where val < 5;
+----+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | app_metric | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59812819 |    33.33 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+-------------+

possible_keys

possible_keys 列指示 MySQL 查找该表时候可能选择的索引。请注意，此列完全独立于 EXPLAIN 输出中显示的表格顺序。这意味着 possible_keys 中的某些键在实际中可能无法与生成的表顺序一起使用。

如果此列为 NULL，则没有相关索引。在这种情况下，您可以通过检查 WHERE 子句来检查它是否引用了适合索引的某个或多个列，从而提高查询的性能。

key

key 列表示 MySQL 实际决定使用的索引。如果 MySQL 决定使用 possible_keys 索引之一来查找行，该索引作为键值列出。

key 可以命名一个不在 possible_keys 值中的索引。如果没有 possible_keys 索引适合查找行，但查询选择的所有列都是其他索引的列，则可能会发生这种情况。也就是说，命名索引覆盖了选定的列，因此虽然它不用于确定要检索哪些行，但索引扫描比数据行扫描更有效。

对于 InnoDB，即使查询还选择了主键，二级索引也可能覆盖选定的列，因为 InnoDB 将主键值与每个二级索引一起存储。如果 key 为 NULL，那么 MySQL 没有找到用于更有效地执行查询的索引。

key_len

key_len 列表示 MySQL 决定使用的 key 的长度。 key_len 的值能够帮助确定 MySQL 实际使用了复合索引的多少。如果 key 列显示为 NULL，则 key_len 列也显示为 NULL。

由于 key 存储格式的原因，可以为 NULL 的列的 key_len 比 NOT NULL 列的 key_len 更大一些。

ref

ref 列显示哪些列或常量与 key 列中指定的索引进行比较，用于从表中选择行。

如果值为 func，则使用的值是某个函数的结果。要查看哪个函数，请使用 EXPLAIN 后的 SHOW WARNINGS 来查看扩展的 EXPLAIN 输出。

rows

rows 列表示 MySQL 认为它为了执行查询而必须要检测行数。

对于 InnoDB 表，这个数字是一个估计值，可能并不总是准确的。

filtered

filtered 列表示存储引擎返回的数据在经过 server 过滤后，剩下满足条件的记录数量所占比例。最大值为 100，表示未被过滤，这个值越大越好。

Extra

此列包含 MySQL 如何解析查询的附加信息。

Distinct

MySQL 正在寻找不同的值，它在找到第一个匹配行后停止寻找当前行后面的匹配行。

mysql> explain select distinct d.val from app_metric m, sys_dictionary d where m.m_key=d.const_key and rel_id=7;
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys               | key                         | key_len | ref        | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+------------+------+----------+------------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL                        | NULL                        | NULL    | NULL       |    2 |   100.00 | Using temporary                    |
|  1 | SIMPLE      | m     | NULL       | ref  | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 17      | const,func |  525 |   100.00 | Using where; Using index; Distinct |
+----+-------------+-------+------------+------+-----------------------------+-----------------------------+---------+------------+------+----------+------------------------------------+

Impossible WHERE

WHERE 子句始终为 false，不能查询任何行。

No tables used

查询没有 FROM 子句，或是 FROM DUAL 子句。

Not exists

MySQL 能够对 LEFT JOIN 查询进行优化，在找到与 LEFT JOIN 条件匹配的行后，不会检查该表中的更多的行。

mysql> explain select m1.* from app_metric m1 left join app_metric m2 on m1.id=m2.id where m2.id is null;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+----------+----------+--------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows     | filtered | Extra                                |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+----------+----------+--------------------------------------+
|  1 | SIMPLE      | m1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL      | 59812819 |   100.00 | NULL                                 |
|  1 | SIMPLE      | m2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | db0.m1.id |        1 |   100.00 | Using where; Not exists; Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+----------+----------+--------------------------------------+

对这个例子来说 app_metric 表定义 id 列为非空，MySQL 扫描 m1 并使用 m1.id 的值查找 m2 中的行。如果 MySQL 在 m2 中找到匹配行，它知道 m2.id 永远不会为 NULL，就不会扫描 m2 中具有相同 id 值的其他行。换句话说，对于 m1 中的每一行，MySQL 只需要在 m2 中进行一次查找，而不管 m2 中有多少行与之匹配。

Range checked for each record (index map: N)

MySQL 没有找到可以使用的索引，但发现在已知前面表中的列值之后，可能会使用某些索引。对于前面表格中的每一行，MySQL 检查是否可以使用 range 或 index_merge 的方式来检索行。这不会很快，但比完全没有索引要快。

mysql> explain select m2.* from app_metric m1,app_metric m2 where m1.id>m2.id and m1.rel_id=7 and m1.m_key='aa' and m1.gmt_create='2022-06-22';
+----+-------------+-------+------------+------+-------------------------------------+-----------------------------+---------+-------------------+----------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys                       | key                         | key_len | ref               | rows     | filtered | Extra                                          |
+----+-------------+-------+------------+------+-------------------------------------+-----------------------------+---------+-------------------+----------+----------+------------------------------------------------+
|  1 | SIMPLE      | m1    | NULL       | ref  | PRIMARY,idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 22      | const,const,const |        1 |   100.00 | Using index                                    |
|  1 | SIMPLE      | m2    | NULL       | ALL  | PRIMARY                             | NULL                        | NULL    | NULL              | 59812819 |    33.33 | Range checked for each record (index map: 0x1) |
+----+-------------+-------+------------+------+-------------------------------------+-----------------------------+---------+-------------------+----------+----------+------------------------------------------------+

索引从 1 开始编号，其顺序与表的 SHOW INDEX 所示的顺序相同。索引映射值N是指示哪些索引是候选的位掩码值。例如，值 0x19（二进制 11001）表示考虑索引 1、4 和 5。

Select tables optimized away

优化器确定 1) 最多应返回一行 2) 要生成此行，必须读取一个确定性的行。读取行的操作只发生在优化阶段（如通过读取索引行），而在查询执行时不需要读取任何表。

mysql> explain select min(id) from app_metric;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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 | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

Using filesort

MySQL 必须进行额外的传递，以了解如何按排序顺序检索行。MySQL 是通过根据 type 遍历所有的行并存储排序键和指向与 WHERE 子句匹配的行的指针来完成的。MySQL 中无法利用索引完成的排序操作称为“文件排序”。

mysql> explain select * from app_metric order by val;
+----+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+----------------+
|  1 | SIMPLE      | app_metric | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59812819 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+----------------+

Using index

仅使用索引树中的信息就可以满足查询需求，而无需执行额外的查找（回表）来读取实际行。也就是我们常说的使用了覆盖索引。

mysql> explain select id,rel_id,m_key,gmt_create from app_metric where rel_id=7 and m_key='ab';
+----+-------------+------------+------------+------+-----------------------------+-----------------------------+---------+-------------+--------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys               | key                         | key_len | ref         | rows   | filtered | Extra       |
+----+-------------+------------+------------+------+-----------------------------+-----------------------------+---------+-------------+--------+----------+-------------+
|  1 | SIMPLE      | app_metric | NULL       | ref  | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 17      | const,const | 174576 |   100.00 | Using index |
+----+-------------+------------+------------+------+-----------------------------+-----------------------------+---------+-------------+--------+----------+-------------+

如果同时出现 Using where，我理解应该是同时出现了对索引的过滤动作，类似于索引下推。

mysql> explain select id from app_metric where rel_id=7 and m_key<'ab' and gmt_create = '2022-06-17 02:54:23' ;
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys               | key                         | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | app_metric | NULL       | range | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 17      | NULL | 527512 |    10.00 | Using where; Using index |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+--------------------------+

Using index condition

Using index condition 这就是我们常说的索引下推。

索引下推 (ICP) 是 MySQL 对使用索引从表中获取数据的优化。如果没有 ICP，存储引擎将根据索引的最左前缀原则定位基表中的行就返回给 Server 层，Server 层再根据 WHERE 条件过滤。启用 ICP 后，如果 WHERE 子句中存在可以使用索引中的列来过滤的条件，Server 层会将这部分 WHERE 条件下推到存储引擎，存储引擎将只有在满足该条件时才从基表中读取行。ICP 可以减少存储引擎必须访问基表的次数及 Server 必须访问存储引擎的次数。

ICP 的适用性及限制条件如下：

ICP 适用于使用 range、ref、eq_ref 和 ref_or_null 来访问整行数据
对 InnoDB，ICP 仅能应用于二级索引，基于虚拟列创建的二级索引无法适用
子查询、存储函数、触发条件都无法下推

mysql> explain select val from app_metric where rel_id=7 and m_key<'ab';
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys               | key                         | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | app_metric | NULL       | range | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 17      | NULL | 174538 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+-----------------------+

Using index for group-by

与 Using index 类似， Using index for group-by 表示 MySQL 找到了一个索引，该索引可用于检索 GROUP BY 或 DISTINCT 查询的所有列，而无需对实际表进行任何额外的磁盘访问。此外，索引以最有效的方式使用，因此对于每个组，只读取几个索引条目。有关详细信息，请参阅第 8.2.1.15 节，“GROUP BY 优化”。

mysql> explain select distinct rel_id from app_metric;
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys               | key                         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | app_metric | NULL       | range | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 8       | NULL | 2962 |   100.00 | Using index for group-by |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+------+----------+--------------------------+

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

在执行联表查询的时候，如果表的连接条件没有用到索引，前面读取的内容将被缓存到 join buffer，后面的行将使用 buffer 中内容进行表连接。

mysql> explain select * from app_metric m, sys_dictionary d where m.m_key=d.const_key;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |        2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | m     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59812819 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+

Using MRR

使用多区间读取优化策略来读取表记录。核心思想是将从二级索引读取的主键先按照主键顺序排序再进行回表查询，尽量将随机 IO 变成顺序 IO。

想要利用 MRR 优化策略，首先要保证已经开启此开关，在 MySQL 5.7中是默认开启的。主要看“mrr=on,mrr_cost_based=on”参数设置。

SHOW VARIABLES LIKE '%optimizer_switch%';
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

由于 MRR 需要将主键排序，所以需要一定的内存支持。如果查询的数据较多而分配给 MRR 所用的内存不足时也不会启用 MRR 优化，这块内存的大小由参数 read_rnd_buffer_size 来控制。

select @@read_rnd_buffer_size;
+------------------------+
| @@read_rnd_buffer_size |
+------------------------+
|               33554432 |
+------------------------+

set read_rnd_buffer_size = 1024 * 1024 * 32;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select val from app_metric where rel_id=7 and m_key > 'ab' and m_key < 'ae' and gmt_create = '2022-06-17 02:54:23';
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table      | partitions | type  | possible_keys               | key                         | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | app_metric | NULL       | range | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 17      | NULL | 350232 |    10.00 | Using index condition; Using MRR |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+----------------------------------+

Using temporary

MySQL 需要创建一个临时表来保存结果。如果查询无法使用索引的 GROUP BY 、UNION 等，通常会发生这种情况。

mysql> explain select val from app_metric group by val;
+----+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+---------------------------------+
|  1 | SIMPLE      | app_metric | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 59812819 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+----------+----------+---------------------------------+

Using where

需要使用 WHERE 子句在 Server 端过滤数据。除非打算从表中获取或检查所有行，否则如果 Extra 值不是 Using where 并且 type 列是 ALL 或 index，则查询可能有问题。

mysql> explain select * from app_metric where rel_id=7 and val = 10;
+----+-------------+------------+------------+------+-----------------------------+-----------------------------+---------+-------+----------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys               | key                         | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+------------+------------+------+-----------------------------+-----------------------------+---------+-------+----------+----------+-------------+
|  1 | SIMPLE      | app_metric | NULL       | ref  | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 8       | const | 10947356 |    10.00 | Using where |
+----+-------------+------------+------------+------+-----------------------------+-----------------------------+---------+-------+----------+----------+-------------+

其他说明

Using where; Using index

关于 Using where; Using index 这种情况网上也有比较多的讨论，如：MySQL - 'Using index condition' vs 'Using where; Using index'，MySQL 執行計劃中Extra等，我理解 Using where; Using index 这种情况应该是只需要在引擎层就可以完成数据的过滤跟返回，就类似于 Using index condition （ICP）。

但实际情况却有点打脸，首先看下面两个 SQL 的执行计划。

mysql> explain select id from app_metric where rel_id=7 and m_key<'ab' and gmt_create = '2022-06-17 02:54:23';
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys               | key                         | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | app_metric | NULL       | range | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 17      | NULL | 174538 |    10.00 | Using where; Using index |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+--------------------------+

mysql> explain select val from app_metric where rel_id=7 and m_key<'ab' and gmt_create = '2022-06-17 02:54:23';
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys               | key                         | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | app_metric | NULL       | range | idx_rel_id_m_key_gmt_create | idx_rel_id_m_key_gmt_create | 17      | NULL | 174538 |    10.00 | Using index condition |
+----+-------------+------------+------------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+-----------------------+

第一个 SQL 如果按照我们的假设，理论上应该执行的更快，第二个 SQL 虽然使用了 ICP 优化，但毕竟还需要回表，感觉要比第一个 SQL 慢。

然后实际执行下两个 SQL 看下数据返回及耗时情况。

mysql> select id from app_metric where rel_id=7 and m_key<'ab' and gmt_create = '2022-06-17 02:54:23';
+----------+
| id       |
+----------+
| 24050101 |
| ... ...  |
| 24089701 |
+----------+
95 rows in set (0.05 sec)

mysql> select val from app_metric where rel_id=7 and m_key<'ab' and gmt_create = '2022-06-17 02:54:23';
+-------+
| val   |
+-------+
| 10.14 |
| ..... |
| 10.14 |
+-------+
95 rows in set (0.03 sec)

多次执行下，也是差不多的性能差距，第二个 SQL 的耗时要比第一个 SQL 耗时少近一倍。这样的测试结果出现后，感觉前面的假设应该无法成立。更正确的过程应该是，引擎层返回了索引能提取的数据后，由 Server 层做了最终的数据过滤，不同于 ICP 在引擎层就完成了数据的所有过滤。也欢迎知道更多内幕的大佬留言告知。

EXPLAIN Output Format

最后编辑于：2023.02.20 20:22:10

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 214,029评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,238评论 3赞 388
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 159,576评论 0赞 349
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,214评论 1赞 287
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,324评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,392评论 1赞 292
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,416评论 3赞 412
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,196评论 0赞 269
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,631评论 1赞 306
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,919评论 2赞 328
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,090评论 1赞 342
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,767评论 4赞 337
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,410评论 3赞 322
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,090评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,328评论 1赞 267
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,952评论 2赞 365
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,979评论 2赞 351