explain

记录提炼一些explain知识点

id

查询语句中每出现一个 SELECT 关键字，设计 MySQL 的大叔就会为它分配一个唯一的 id 值。这个 id 值就是 EXPLAIN 语句的第一个列。

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

一个 SELECT 关键字后边的 FROM 子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的。

例如select * from t1,t2。会有2条记录但他们id都为1

在连接查询的执行计划中，每个表都会对应一条记录（包括物化的表），这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表

type

type 列就表明了这个表的访问方法。

type值如下时，其对应的访问方法

system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是 system
const：就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const
eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref ，比方说：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref
ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可能是 ref_or_null

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
index_merge：一般情况下对于某个表的查询只能使用到一个索引，但我们唠叨单表访问方法时特意强调了在某些场景下可以使用 Intersection 、 Union 、 Sort-Union 这三种索引合并的方式来执行查询，忘掉的可以看看第10章，我们看一下执行计划中是怎么体现 MySQL 使用索引合并的方式来对某个表执行查询的：

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra

| 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key 1,idx_key3 | 303,303 | NULL | 14 | 100.00 | Using union(idx_key1,idx_key3); Using where |
unique_subquery：类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery ，比如下边的这个查询语句：

EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2. key1) OR key3 = 'a';
index_subquery：index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引。
range：如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法。
index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

联合索引 key_part1,key_part2,key_part3。此语句扫描索引后不需回表，所以是index
ALL：最熟悉的全表扫描

一般来说，这些访问方法按照我们介绍它们的顺序性能依次变差。其中除了 All 这个访问方法外，其余的访问方法都能用到索引，除了 index_merge 访问方法外，其余的访问方法都最多只能用到一个索引。

possible_keys和key

在 EXPLAIN 语句输出的执行计划中， possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些， key 列表示实际用到的索引有哪些。

经过查询优化器计算使用不同索引的成本后，最后决定使用 key中的索引来执行查询比较划算。

另外需要注意的一点是，possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引

key_len

key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：

对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占用的最大存储空间就是 100 × 3 = 300 个字节。
如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节
对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

这里需要强调的一点是，执行计划的生成是在 MySQL server 层中的功能，并不是针对具体某个存储引擎的功能（实际更具存储层），设计 MySQL 的大叔在执行计划中输出 key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列，而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间到底是占用1个字节还是2个字节。

ref

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

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 | NUL L | 9688 | 100.00 | NULL |

| 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xia ohaizi.s1.id | 1 | 100.00 | NULL |

可以看到对被驱动表 s2 的访问方法是 eq_ref ，而对应的 ref 列的值是 xiaohaizi.s1.id ，这说明在对被驱动表进行访问时会用到 PRIMARY 索引，也就是聚簇索引与一个列进行等值匹配的条件，于 s2 表的 id 作等值匹配的对象就是 xiaohaizi.s1.id 列（注意这里把数据库名也写出来了）。

有的时候与索引列进行等值匹配的对象是一个函数，比方说下边这个查询：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

rows

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

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

| 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 | NUL L | 266 | 100.00 | Using index condition |

我们看到执行计划的 rows 列的值是 266 ，这意味着查询优化器在经过分析使用 idx_key1 进行查询的成本之后，觉得满足 key1 > 'z' 这个条件的记录只有 266 条。

filter

之前在分析连接查询的成本时提出过一个 condition filtering 的概念，就是 MySQL 在计算驱动表扇出时采用的一个策略：

如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。
如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

例如：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' 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 | NUL L | 266 | 10.00 | Using index condition; Using where |

从执行计划的 key 列中可以看出来，该查询使用 idx_key1 索引来执行查询，从 rows 列可以看出满足 key1 > 'z' 的记录有 266 条。

执行计划的 filtered 列就代表查询优化器预测在这 266 条记录中，有多少条记录满足其余的搜索条件，也就是 common_field = 'a' 这个条件的百分比。此处 filtered 列的值是 10.00 ，说明查询优化器预测在 266 条记录中有 10.00% 的记录满足 common_field = 'a' 这个条件。对于单表查询来说，这个 filtered 列的值没什么意义，我们更关注在连接查询中驱动

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 | 9688 | 10.00 | Using where

| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | xiao haizi.s1.key1 | 1 | 100.00 | NULL |

从执行计划中可以看出来，查询优化器打算把 s1 当作驱动表， s2 当作被驱动表。我们可以看到驱动表 s1 表的执行计划的 rows 列为 9688 ， filtered 列为 10.00 ，这意味着驱动表 s1 的扇出值就是 9688 × 10.00% = 968.8 ，这说明还要对被驱动表执行大约 968 次查询

Extra

Using index：当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在 Extra 列将会提示该额外信息
Using index condition：如果在查询语句的执行过程中将要使用索引条件下推这个特性，在 Extra 列中将会显示 Using index condition ，比如这个例子就是索引下推：
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b'; key1为索引

其中的 key1 > 'z' 可以使用到索引，但是 key1 LIKE '%a' 却无法使用到索引，在以前版本的 MySQL 中，是按照下边步骤来执行这个查询的：
- 先根据 key1 > 'z' 这个条件，定位到二级索引 idx_key1 中对应的二级索引记录。
- 对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足 key1 LIKE '%a' 这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表(减少随机io)。
- 对于满足 key1 LIKE '%a' 这个条件的二级索引记录执行回表操作。
Using where：当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。比如下边这个查询：

EXPLAIN SELECT * FROM s1 WHERE common_field = 'a'; common_field不是索引

当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时，在 Extra 列中也会提示上述额外信息。
Using join buffer (Block Nested Loop)：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法，比如下边这个查询语句：
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 | re f | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+--- ---+------+----------+---------------- | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NU LL | 9688 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NU LL | 9954 | 10.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+--- ---+----

可以在对 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 额外信息。
Not exists：当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的(即被驱动表不存在这样的记录)，那么在该表的执行计划的 Extra 列就会提示 Not exists 额外信息，比如这样：

EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

由于id是索引，所以被驱动表中不存在这样的记录，也就没必要到被驱动表中找到全部符合ON子句条件的记录，这样可以稍微节省一点性能
Using intersect(...) 、 Using union(...) 和 Using sort_union(...)：如果执行计划的 Extra 列出现了 Using intersect(...) 提示，说明准备使用 Intersect 索引合并的方式执行查询，括号中的 ... 表示需要进行索引合并的索引名称；如果出现了 Using union(...) 提示，说明准备使用 Union 索引合并的方式执行查询；出现了 Using sort_union(...) 提示，说明准备使用 Sort-Union 索引合并的方式执行查询。
Zero limit：当我们的 LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息，比如这样：

EXPLAIN SELECT * FROM s1 LIMIT 0;
Using filesort：有一些情况下对结果集中的记录进行排序是可以使用到索引的就直接通过索引，但是很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，设计 MySQL 的大叔把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名： filesort ）。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的 Extra 列中显示 Using filesort 提示，比如这样：

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10; common_field不是索引

需要注意的是，如果查询中需要使用 filesort 的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用 文件排序 的执行方式改为使用索引进行排序。
Using temporary：在许多查询的执行过程中， MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含 DISTINCT 、 GROUP BY 、 UNION 等子句的查询过程中，如果不能有效利用索引来完成查询， MySQL 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的 Extra 列将会显示 Using temporary 提示，比方说这样：
- DISTINCT和GROUP BY去重效率，由于MySQL（5.7以前）会在包含 GROUP BY 子句的查询中默认添加上 ORDER BY 子句，所以可能会用到Using temporary; Using filesort ，效率会低，可以手动加上ORDER BY NULL 来去掉他的默认排序
EXPLAIN SELECT DISTINCT common_field FROM s1;

extra中为： Using temporary

如果5.7版本以前的 EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_fiel d;

extra中为： Using temporary; Using filesort。因为有order by需要借助内存或磁盘进行排序

执行计划中出现 Using temporary 并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表

EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;key1为索引

使用了key1索引，extra中为Using index，他扫描索引就能搞定了，不需要借助临时表。

再来一个案例， f_user_id为普通索引，t_appoint_order数据为60w

EXPLAIN SELECT DISTINCT f_user_id FROM t_appoint_order limit 10000;

extra中为Using index; Using temporary，按理来说总觉得他using index就ok了，但是他还借助了临时表。查询时间 14ms左右

SELECT DISTINCT f_user_id FROM t_appoint_order order by f_user_id limit 10000;

extra中为Using index; 没有借助临时表10ms左右

SELECT f_user_id FROM t_appoint_order group by f_user_id limit 10000;

extra：Using index，耗时10ms左右

mysql 5.7后，个人在去重方面更推荐group by

Start temporary, End temporary：我们前边唠叨子查询的时候说过，查询优化器会优先尝试将 IN 子查询转换成 semi-join ，而 semi-join 又有好多种执行策略，当执行策略为 DuplicateWeedout 时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的 Extra 列将显示 Start temporary 提示，被驱动表查询执行计划的 Extra 列将显示 End temporary 提示，就是这样：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_fiel d = 'a');

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+--------- +-------------------+------+----------

| 1 | SIMPLE | s2 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9954 | 10.00 | Using where; Start temporary | | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | xiaohaizi.s2.key3 | 1 | 100.00 | End temporary |

Json格式的执行计划

我们上边介绍的 EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本。不过设计 MySQL 的大叔贴心的为我们提供了一种查看某个执行计划花费的成本的方式：

在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON 。

这样我们就可以得到一个 json 格式的执行计划，里边儿包含该计划花费的成本，比如这样：

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.co
mmon_field = 'a'\G

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.98" # 整个查询的执行成本预计,并不是耗时
    },
    "table": {
      "table_name": "t_appoint_order",
      "access_type": "ref",
      "possible_keys": [
        "IDX_USERID"
      ],
      "key": "IDX_USERID",
      "used_key_parts": [
        "f_user_id"
      ],
      "key_length": "123",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.89",
        "eval_cost": "0.10",
        "prefix_cost": "0.99",
        "data_read_per_join": "23K"
      },
      "used_columns": [
        "f_appoint_number",
        "f_platform_id",
        "f_hospital_id"
      ]
    }
  }
}

haogui

id