MySQL EXPLAIN 详解

说明：以下语法基于 MariaDB-10.3.29

什么是 EXPLAIN

MySQL 在执行SQL语句之前，会通过查询优化器基于各种成本和规则的优化后生成一条所谓的执行计划。这个执行计划展示了具体执行的查询方式，比如多表连接的顺序，以及对于每个表采用什么访问方式来具体执行查询等等。

MySQL 在设计的时候提供了 EXPLAIN 语句来查看某个查询语句的具体执行计划。

标准语法

MariaDB [test]> explain select * from test;
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.000 sec)

其实除了select 外，像 delete，insert，replace和update都可以加上explain，用来查看这些语句的执行计划，只不过大家select用的多些。

### explain insert
MariaDB [test]> explain insert into test (`test`) values ('11111');
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|    1 | INSERT      | test  | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL  |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.000 sec)

### explain update
MariaDB [test]> explain update test set test = 111 where id = 2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.000 sec)

...

看看explain 各个字段的含义

id

在一个大的查询语句中每个select关键字对应一个唯一的id

只有一条记录 id = 1 的情况，例如：简单查询，连接查询（join）
有多条记录的情况，例如：子查询，但是这里需要注意下，查询优化器可能会将子查询重写转换成连接查询

有多条记录切还有id = null 的情况，例如：union查询，但是union all 是没有null

MariaDB [test]> explain select id  from t1 union select t1_id as id from t2;
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
|    1 | PRIMARY      | t1         | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
|    2 | UNION        | t2         | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |       |
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
3 rows in set (0.000 sec)

null 是因为 union 为了吧1和2的结果集合并起来并去重，所以会生成临时表，但是 union all就不需要为结果去重，所以不需要使用临时表

select_type

select关键字对应的那个查询类型

simple：不包含union或者子查询的查询都算simple类型，连接查询也算simple类型

primary：包含union、union all 或者子查询的大查询，它是由多个小查询组成的，其中就包含select_type值就是primary，例如：

MariaDB [test]> explain select id  from t1 union select t1_id as id from t2;
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
|    1 | PRIMARY      | t1         | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
|    2 | UNION        | t2         | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |       |
+------+--------------+------------+------+---------------+------+---------+------+------+-------+
3 rows in set (0.000 sec)

union：包含union或者union all 的大查询，它是有多个小查询组成，对比上面的例子
union result： MySQL选择使用临时表来完成union查询的去重工作
subquery：不能转化成连接查询的子查询d select_type就是subquery，由于select_type为subquery的子查询会被物化，所以只需要执行一遍。

dependent subquery：如果包含子查询的查询语句不能转换为对应的join的形式，并且该子查询是相关子查询，则该子查询的第一个select关键字代表的那个查询的select_type就是 dependent subquery，例如：

MariaDB [test]> explain select * from t1 where id in (select id from t2 where t1.id = t2.id) or name = 11;
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
|    2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.000 sec)

dependent union 包含 union 或者 union all 的大查询，如果各个小查询都依赖于外层查询的话，那么除了最左边的那个小查询，其余的小查询的select_type 的值都是 dependent union，例如：

MariaDB [test]> explain select * from t1 where id in (select id from t2 union select id from t1 where name = 11 );
+------+--------------------+------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type        | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+--------------------+------------+------+---------------+------+---------+------+------+-------------+
|    1 | PRIMARY            | t1         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
|    2 | DEPENDENT SUBQUERY | t2         | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
|    3 | DEPENDENT UNION    | t1         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
| NULL | UNION RESULT       | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |             |
+------+--------------------+------------+------+---------------+------+---------+------+------+-------------+
4 rows in set (0.001 sec)

derived

对于采用物化的方式执行包含派生的查询，该派生查询表对应的子查询的select_type 就是 derived，例如：

MariaDB [test]> explain select * from (select id,count(*) as c from t1 group by id) as derived_t1 where c > 1;
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                     |
|    2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary; Using filesort |
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set (0.000 sec)

materialized

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

MariaDB [test]> explain select * from t1 where id in (select id from t2);
+------+--------------+-------------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id   | select_type  | table       | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+------+--------------+-------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|    1 | PRIMARY      | <subquery2> | ALL  | distinct_key  | NULL | NULL    | NULL |    1 |                                                 |
|    1 | PRIMARY      | t1          | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using join buffer (flat, BNL join) |
|    2 | MATERIALIZED | t2          | ALL  | NULL          | NULL | NULL    | NULL |    1 |                                                 |
+------+--------------+-------------+------+---------------+------+---------+------+------+-------------------------------------------------+
3 rows in set (0.000 sec)

type

针对表的访问方法

效率对比

system > const > eq_ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

possible_keys

可能用到的索引，并不是越多越好，可能用的索引越多，查询优化器计算成本时耗时更长，尽量删除不必要的索引

key

经过优化器计算后实际上使用的索引

key_len

实际使用索引的长度，由一下三部分组成

固定长度类型，实际占用存储空间的最大值就是固定值，例如：int key_ken = 4字节；指定字符集的变长类型，实际占用最大存储空间通过计算后得出，例如：utf-8 字符集，变长 vachar(100) key_len = 100 * 3 字节
如果索引列可以存储null值，则key_len比不可以存储null值时多1个字节
变长字段都会有2个字节的空间来存储该变长列的实际长度

例如：

不允许为null值的 int 类型的列，key_len = 4
允许为null值的int 类型的列，key_len = 5
不允许为null值的 vachar(100)，key_len = 100 * 3 + 2 = 302

ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows

预估需要读取的记录条数

extra

额外的信息

no tables used：查询语句没有form子句将会提示这个额外信息

MariaDB [test]> explain select version();
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.000 sec)

impossible where：查询语句where子句永远为false的时候

MariaDB [test]> explain select * from t1 where 1 !=1;
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.000 sec)

no matching min/max row：当查询有min或者max聚集函数，但是并没有符合where子句中搜索条件的记录时。

注意：在测试过程中发现where搜索条件必须是索引列才会出现次场景
using index：索引覆盖
using index condition：索引下推
using where：全表扫描

using join buffer (block nested loop)：连接查询过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会分配一块名叫join buffer的内存快来加速查询速度，就是常说的基于块的嵌套循环算法，例如：

MariaDB [test]> explain select * from t1 inner join t2 on t1.id = t2.id;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
|    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 |                                                 |
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
2 rows in set (0.001 sec)

not exists：当使用左（外）连接时，如果where子句中包含要求被驱动表的某个列等于null值的搜索条件，而且那个列又是不允许存储null值的，那么在该表的执行计划的extra列就会提示not exists额外信息，例如：

MariaDB [test]> explain select * from t1 left join t2 on t1.id = t2.id where t2.name is null;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |                                                             |
|    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where; Not exists; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------+
2 rows in set (0.000 sec)

using intersect(...)、using union(...) 和 using sort_union(...)：
- intersect 说明准备使用intersect索引合并的方式查询，括号内表示需要进行索引合并的索引名称
- union 说明准备使用union索引合并的方式查询
- sort_union 说明准备使用sort-union索引合并的方式执行查询
zero limit：当limit 参数为0，没有打算从数据库读取记录
using filesort：对结果集排序时使用了文件排序方式

对结果集排序有以下两种情况
- 使用了索引排序
- 内存或磁盘排序，这种情况是filesort，如果需要排序的记录数比较多，那么这个过程耗时也会很长
using temporary：查询过程中借助了临时表来完成一些功能，比如去重、排序等。在执行包含distinct、group by、union等子句的查询过程中，如果不能有效利用索引来完成，MySQL 很有可能通过建立内部的临时表来执行查询。建立临时表的成本比较大，最好能通过索引来替代临时表

注意：group by 会默认带上order by ，可以通过 order by null 显式关闭
start temporary，end temporary：当semi-join执行 duplicateWeedout策略时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的extra将显示start temporary，被驱动表extra 将显示end temporary
looseScan：当semi-join采用的策略looseScan执行策略，则驱动表执行计划的extra将显示looseScan
firstMatch（tbl_name)：当semi-join采用的策略是firstMatch时，被驱动表执行计划的extra 将显示firstMatch

执行计划json格式输出

语法：explain 后加 format=json

例如：
explain format=json select * from t1;

mysql> explain format = json select * from t1 where id in (select id from t2);
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "<subquery2>",
      "access_type": "ALL",
      "possible_keys": ["distinct_key"],
      "rows": 1,
      "filtered": 100,
      "materialized": {
        "unique": 1,
        "query_block": {
          "select_id": 2,
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows": 1,
            "filtered": 100
          }
        }
      }
    },
    "block-nl-join": {
      "table": {
        "table_name": "t1",
        "access_type": "ALL",
        "rows": 2,
        "filtered": 100
      },
      "buffer_type": "flat",
      "buffer_size": "256Kb",
      "join_type": "BNL",
      "attached_condition": "t1.`id` = t2.`id`"
    }
  }
} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

— 于 2022年01月11日，共写了14151个字；

— 标签：explain mysql

笠缪

Menu