【MySQL01】【 Explain 命令详解】

已于 2024-05-21 16:57:47 修改
阅读量850 收藏 28
点赞数 25
分类专栏： # 《MySQL是怎样运行的》 文章标签： mysql
于 2024-05-14 17:12:04 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36882793/article/details/138847882
版权

文章目录

一、前言

最近在读《MySQL 是怎样运行的》、《MySQL技术内幕 InnoDB存储引擎 》，后续会随机将书中部分内容记录下来作为学习笔记，部分内容经过个人删改，因此可能存在错误，如想详细了解相关内容强烈推荐阅读相关书籍

本文 Mysql 版本为 5.7.16，

涉及表结构来源两处：

  1. 数据库 基于 Mysql 官方提供的 sakila 内容基于 下载地址：https://dev.mysql.com/doc/index-other.html
    在这里插入图片描述

  2. 如下：

    CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、Explain 概览

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 通过explain我们可以获得以下信息：

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

使用方法：explain + sql语句。 如下

在这里插入图片描述
可以看到下面我们来分析每一列的内容

列名描述
id选择标识符。数字越大，越先执行；数字相同，从上往下依次执行。需要注意在连接查询（join ）中由于每个表都会对应一条记录，因此会存在多条 ID 相同的记录，此时出现在前面的表示驱动表，出现在后面的表示被驱动表。
select_type表示查询的类型
table输出结果集的表。EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，table 即为该表的表名
partitions匹配的分区
type表示表的连接类型
possible_keys表示查询时，可能使用的索引
key表示实际使用的索引
key_len索引字段的长度
ref列与索引的比较
rows扫描出的行数(估算的行数)
filtered按表条件过滤的行百分比
Extra执行情况的描述和说明

三、Explain 详解

1. id

选择标识符。数字越大，越先执行；数字相同，从上往下依次执行。需要注意在连接查询（join ）中由于每个表都会对应一条记录，因此会存在多条 ID 相同的记录，此时出现在前面的表示驱动表，出现在后面的表示被驱动表。

  1. id 不同 : ID不同，数字越大，越先执行。 如下, 先执行SUBQUERY，再执行 PRIMARY 。

    mysql> explain select * from film where film_id = (select max(film_id) from film);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | PRIMARY     | film  | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL                         |
|  2 | SUBQUERY    | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
2 rows in set (0.08 sec)

  2. id 相同 :按照从上到下的顺序依次执行

    mysql> explain select f.* from film f left join film_actor fa on fa.film_id = f.film_id;
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | f     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL             | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | fa    | NULL       | ref  | idx_fk_film_id | idx_fk_film_id | 2       | sakila.f.film_id |    5 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
2 rows in set (0.06 sec)

  3. id为null : 表示一个结果集，并不需要使用它来进行查询。

    UNION 会将多个查询的结果集合合并起来并对结果集中的记录去重，去重的实现是通过 MySQL 的 内部临时表，如下即使将 id 为 1 和 2 的查询结果集合并起来去重，在内部创建了一个名为 <union1,2> 的零时表，id 为 null 说明这个临时表是为了合并两个查询的结果集而创建的 。额外的，由于 union all 不需要对结果集去重，他只是单纯的将结果集合并后返回给用户也就不需要临时表，因此就没有 id = NULL 的记录。

    -- 如下 UNION RESULT 作为一个合并结果集的操作的执行id为null。
mysql> explain select film_id from film union select film_id from film_actor;
+------+--------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra           |
+------+--------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------+
|    1 | PRIMARY      | film       | NULL       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index     |
|    2 | UNION        | film_actor | NULL       | index | NULL          | idx_fk_film_id     | 2       | NULL | 5462 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL               | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------+
3 rows in set (0.03 sec)

-- 额外的，由于 union all 不需要对结果集去重，他只是单纯的将结果集合并后返回给用户也就不需要临时表，因此就没有 id = NULL 的记录。
mysql> explain select * from t1 union all select * from t2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL  |
|  2 | UNION       | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set (0.04 sec)

需要注意的是：

  1. 在连接查询（join ）中由于每个表都会对应一条记录，因此会存在多条 ID 相同的记录，此时出现在前面的表示驱动表，出现在后面的表示被驱动表。

    -- 如下：t1在t2 之前，所以 t1 表将作为驱动表，t2 表将作为被驱动表
mysql> explain select * from t1 inner join t2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL                                  |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set (0.02 sec)

  2. 查询优化器可能对涉及子查询的查询语句重写，从而转换为连接查询，如果想知道查询优化器是否对某个包含子查询的语句进行了重写，可以直接看执行计划。如下：

    -- 这一句由于包含 or id = 1 的部分，因此无法转换为 连接查询，所以存在 DEPENDENT SUBQUERY, id 列的值不同，说明存在执行优先级
mysql> explain select * from t1 where id in (select id from t2) or id = 1;
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type            | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL             | PRIMARY       | NULL    | NULL    | NULL |  100 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 |   100.00 | Using index |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set (0.03 sec)
-- 这一句可以直接转换为连接查询，所以id 列相同。
mysql> explain select * from t1 where id in (select id from t2);
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       |  100 |   100.00 | NULL        |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
2 rows in set (0.04 sec)

2. select_type

select_type 代表查询的类型, 各种取值如下：

  1. SIMPLE: 即简单查询，查询语句中不包含 UNION 或者子查询的查询都算做 SIMPLE 类型。

    mysql> explain select * from film;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.05 sec)

mysql> explain select f.* from film f left JOIN film_actor fa on fa.film_id = f.film_id;
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | f     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL             | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | fa    | NULL       | ref  | idx_fk_film_id | idx_fk_film_id | 2       | sakila.f.film_id |    5 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
2 rows in set (0.03 sec)

  2. PRIMARY: 对于包含 UNION、UNION ALL 或者子查询的大查询来说，他是由几个小查询组成的。

    mysql> explain select film_id from film union all select film_id from film_actor;
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | film       | NULL       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index |
|  2 | UNION       | film_actor | NULL       | index | NULL          | idx_fk_film_id     | 2       | NULL | 5462 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
2 rows in set (0.03 sec)

mysql> explain select film_id from film union all select film_id from film_actor where film_id > (select min(film_id) from film_actor);
+----+-------------+------------+------------+-------+----------------+--------------------+---------+------+------+----------+------------------------------+
| id | select_type | table      | partitions | type  | possible_keys  | key                | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+------------+------------+-------+----------------+--------------------+---------+------+------+----------+------------------------------+
|  1 | PRIMARY     | film       | NULL       | index | NULL           | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index                  |
|  2 | UNION       | film_actor | NULL       | range | idx_fk_film_id | idx_fk_film_id     | 2       | NULL | 5452 |   100.00 | Using where; Using index     |
|  3 | SUBQUERY    | NULL       | NULL       | NULL  | NULL           | NULL               | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+------------+------------+-------+----------------+--------------------+---------+------+------+----------+------------------------------+
3 rows in set (0.02 sec)

  3. UNION：对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的；其中除了最左边的那个小查询外，其余小查询的 select_type 的值就是 UNUION。

    mysql> explain select film_id from film union all select film_id from film_actor UNION all select film_id from film_text;
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | film       | NULL       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index |
|  2 | UNION       | film_actor | NULL       | index | NULL          | idx_fk_film_id     | 2       | NULL | 5462 |   100.00 | Using index |
|  3 | UNION       | film_text  | NULL       | index | NULL          | PRIMARY            | 2       | NULL | 1000 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
3 rows in set (0.04 sec)

  4. UNION RESULT : MySQL 选择用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT

    mysql> explain select * from t1 union select * from t2;
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|    1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL            |
|    2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set (0.06 sec)

  5. SUBQUERY : 如果包含子查询的查询语句不能转换为对应的半连接形式，并且该子查询是不相关子查询，而且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查询就是 SUBQUERY。(需要注意： 查询条件是 SUBQUERY 的子查询会被物化，所以该子查询只需要执行一遍)

    mysql> explain SELECT * FROM film_category WHERE film_id = (SELECT max(film_id) FROM film_actor);
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table         | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | PRIMARY     | film_category | NULL       | ref  | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | Using where                  |
|  2 | SUBQUERY    | NULL          | NULL       | NULL | NULL          | NULL    | NULL    | NULL  | NULL | NULL     | Select tables optimized away |
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------+
2 rows in set (0.03 sec)

mysql> explain SELECT (select 1 from film_category) from film;
+----+-------------+---------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | film          | NULL       | index | NULL          | idx_fk_language_id        | 1       | NULL | 1000 |   100.00 | Using index |
|  2 | SUBQUERY    | film_category | NULL       | index | NULL          | fk_film_category_category | 1       | NULL | 1000 |   100.00 | Using index |
+----+-------------+---------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+
2 rows in set (0.05 sec)

  6. DEPENDENT SUBQUERY : 如果包含子查询的查询语句不能够转为对应的半连接形式，并且该子查询被查询优化器转换为相关子查询的形式，则该子查询第一个 SELECT 关键字代表的那个查询就是 DEPENDENT SUBQUERY。需要注意 DEPENDENT SUBQUERY 的子查询可能会被执行多次。

    如下SQL，大查询中包含一个子查询，子查询中又包含由 UNION 连接起来的两个小查询。从执行计划可以看到，select id from t2 where a = '1' 是子查询的第一个查询，所以他的类型是 DEPENDENT SUBQUERY，而 select id from t1 where a = '1' 这个查询就是 DEPENDENT UNION 类型。

    mysql> explain select * from t1 where id in (select id from t2 where a = '1' union select id from t1 where a = '1');
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
| id   | select_type        | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
|    1 | PRIMARY            | t1         | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |  100 |   100.00 | Using where     |
|    2 | DEPENDENT SUBQUERY | t2         | NULL       | eq_ref | PRIMARY,a     | PRIMARY | 4       | func |    1 |     5.00 | Using where     |
|    3 | DEPENDENT UNION    | t1         | NULL       | eq_ref | PRIMARY,a     | PRIMARY | 4       | func |    1 |     5.00 | Using where     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
4 rows in set (0.07 sec)

  7. DEPENDENT UNION : 在包含 UNION 或者 UNION ALL的大查询中，如果各个子查询都依赖于外层查询，除了最左边的那个小查询之外，其余小查询就是 DEPENDENT UNION 。

    如下SQL，大查询中包含一个子查询，子查询中又包含由 UNION 连接起来的两个小查询。从执行计划可以看到，select id from t2 where a = '1' 是子查询的第一个查询，所以他的类型是 DEPENDENT SUBQUERY，而 select id from t1 where a = '1' 这个查询就是 DEPENDENT UNION 类型。

    mysql> explain select * from t1 where id in (select id from t2 where a = '1' union select id from t1 where a = '1');
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
| id   | select_type        | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
|    1 | PRIMARY            | t1         | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |  100 |   100.00 | Using where     |
|    2 | DEPENDENT SUBQUERY | t2         | NULL       | eq_ref | PRIMARY,a     | PRIMARY | 4       | func |    1 |     5.00 | Using where     |
|    3 | DEPENDENT UNION    | t1         | NULL       | eq_ref | PRIMARY,a     | PRIMARY | 4       | func |    1 |     5.00 | Using where     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
4 rows in set (0.07 sec)

  8. DERIVED : 在包含派生表的查询中，如果是以物化派生表的方式执行查询，则派生表对应的子查询是 DERIVED。

    mysql> explain SELECT ff.* FROM ( SELECT film_id FROM film UNION ALL SELECT film_id FROM film_actor ) ff;
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL               | NULL    | NULL | 6462 |   100.00 | NULL        |
|  2 | DERIVED     | film       | NULL       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index |
|  3 | UNION       | film_actor | NULL       | index | NULL          | idx_fk_film_id     | 2       | NULL | 5462 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
3 rows in set (0.02 sec)

  9. MATERIALIZED : 当查询优化器在执行包含子查询的语句时，选择将子查询物化后与外层查询进行连接查询，该子查询对应的类型即为 MATERIALIZED 。

    mysql> explain select * from t1 where b in (select b from t2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref       | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE       | t1          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL      |  100 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 5       | demo.t1.b |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | t2          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL      | 1000 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+
3 rows in set (0.06 sec)

3. table

显式的查询显式查询的表名，如果查询使用了别名，这里显示的则是别名。如果不涉及对数据库的操作，那么这里显示的就是null 。如果显示为 <derived N > 则表明这个是临时表，N为执行计划id，表示这个结果来源于这个查询自身，如果显示为 <union M, N>，其也是一个临时表，表示这个结果来自于 union 查询的 id为 M，N的结果集。

不涉及表操作 ：

mysql> explain select sleep(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 (0.06 sec)

4. type

type列很重要，是用来说明表与表之间是如何进行关联操作的，有没有使用索引。MySQL中“关联”一词比一般意义上的要宽泛，MySQL认为任何一次查询都是一次“关联”，并不仅仅是一个查询需要两张表才叫关联，所以也可以理解MySQL是如何访问表的。主要有下面几种类别。

type 依次性能从好到差 ： system，const，eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range,index,all。除了all之外，其他type都能用到索引，除了 index_merge 之外，其他的type只可以用到一个索引，一般来说，好的 sql查询至少达到 range 级别，最好能达到 ref

  1. system : 当表中只有一条记录并且该表使用的存储引擎（如 MyISAM、MEMORY）的统计数据是精确的，那么该表的访问方法就是 system。如果是 Innodb 引擎表，type 在这个情况下通常都是 all 或者 index。

  2. const : 当根据主键或者唯一二级索引列与常数进行等值匹配的时候，对单表的访问方法就是 const。即查询是通过主键或唯一二级索引来与常量做比较，最终结果只能有一条记录。

    mysql> explain select * from film where film_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.01 sec)

  3. eq_ref : 执行连接查询时，如果被驱动表是通过主键或者不允许存储 NULL 值的唯一二级索引列等值匹配的方式进行访问的（如果该主键或者不允许存储 NULL 值的唯一二级索引是联合索引，则所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref。即查询是通过主键或唯一二级索引来来作为查询条件，最终结果只能有一条记录。

    如下，t1 将作为驱动表，t2 作为被驱动表。在访问 t2 表时，可以通过主键等值匹配来访问：

    mysql> explain select * from t1 inner join t2 on t1.id = t2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       |  100 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
2 rows in set (0.07 sec)

  4. ref : 当通过普通索引列与常量进行等值匹配的方式来查询某个表时，对该表的访问方法就可能是 ref。另外，如果是执行连接查询，被驱动表中的某个普通的二级索引列与驱动表中的某个列进行等值匹配，那么对被驱动表也可能使用 ref。

    相较于 eq_ref。 ref 不要求记录唯一性，也就是不要求索引是主键或者唯一索引。ref在连接查询时，只要求是索引即可，返回记录也可出现多条。总之，返回数据可能不唯一的等值查找就可能出现。

    mysql> explain select * from film f left join film_actor fa on fa.film_id = f.film_id;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | fa    | NULL       | ALL  | NULL           | NULL           | NULL    | NULL              | 1000 |   100.00 | NULL  |
|  1 | SIMPLE      | f     | NULL       | ref  | idx_fk_film_id | idx_fk_film_id | 2       | sakila.fa.film_id |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
2 rows in set (0.06 sec)

mysql> explain select * from film where title = 'film1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | ref  | idx_title     | idx_title | 514     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set (0.07 sec)

  5. fulltext : 全文索引检索，全文检索的优先级很高，若全文索引和普通索引同时存在，mysql不管代价，优先选择全文索引。

  6. ref_or_null : 当对普通二级索引列进行等值匹配且该索引列的值也可以是 NULL 值时，对该表的访问方法就可能是 ref_or_null。简单来说类似ref，但是可以搜索值为NULL的行。

    mysql> explain select * from t1 where a = 'a' or a is null;
+----+-------------+-------+------------+-------------+---------------+-----+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+-----+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref_or_null | a             | a   | 5       | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+-----+---------+-------+------+----------+-----------------------+
1 row in set (0.06 sec)

  7. index_merge : 一般清理下只会为单个索引生成扫描区间，但是 InnoDB 存在Intersection (交集)索引合并、Union （并集）索引合并、Sort-Union 索引合并 三种索引合并的方式来执行查询。(关于这三种索引合并方式，本文末有补充)。

    如下，type 为 index_mege， 即MySQL 打算使用索引合并的方式来执行对 t1 表的查询。

    mysql> explain select * from t1 where a = 'a' or b = 'b';
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key         | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index_merge | idx_a,idx_b   | idx_a,idx_b | 5,5     | NULL |    2 |   100.00 | Using union(idx_a,idx_b); Using where |
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
1 row in set (0.06 sec)

  8. unique_subquery : 类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 针对的事一些包含 in 子查询的查询语句。如果查询优化器决定将 in 子查询转换为 exists 子查询，而且子查询在转换之后可以使用主键或者不允许存储 NULL 值的唯一二级索引进行等值匹配，那么该子查询执行计划的 type 列的值就是 unique_subquery 。

    mysql> explain select * from t1 where a in (select id from t2 where t1.id = t2.id) or b = 'a';
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+
|  1 | PRIMARY            | t1    | NULL       | ALL    | idx_b         | NULL    | NULL    | NULL       |  100 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.id |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+
2 rows in set (0.06 sec)

  9. index_subquery : 与 unique_subquery 类似，只不过在访问子查询中表时使用的是普通索引。

  10. range : 如果使用索引获取某些单点扫描区间的记录，那么就可能使用到 range 访问方法，即索引范围扫描，常见于使用 >， <, is null, between, in, like 等范围运算符查询中。

    mysql> explain select * from t1 where id > 0;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |  100 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.06 sec)

  11. index : 当可以使用索引覆盖，但需要扫描全部的索引时，该表的访问方法就是 index。 额外的，当我们需要执行全表扫描，并且需求对主键进行排序时，此时的 type 也是 index。

    mysql> explain select id from t1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_a | 5       | NULL |  100 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)

  12. all : 全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。效率最差

    mysql> explain select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.04 sec)

5. possible_keys

表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，这里可能出现多个的。如下：对于 t1 表 字段a 和 b 都是普通索引，如下语句在执行前就可能会使用 idx_a 或 idx_b 为索引。最终通过查询优化器计算后认为执行全表扫描成本更低（因为表里就一条记录）

mysql> explain select * from t1 where a > 'a' and b > 'b';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_a,idx_b   | NULL | NULL    | NULL |  100 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)

6. key

表示在某个查询语句中，对某个表执行单表查询时真正用到的索引有哪些。之所以出现 possible_keys 与 key 的不同是因为MySQL 经过查询优化器计算不同所以的使用成本后，会选择成本较少的索引使用。需要注意的是 ：当select_type 为 index_merge 时，这里可能出现两个以上的索引，其他的 select_type 这里只会出现一个

7. key_len

用于处理查询的索引长度，如果是单列索引，那就是整个索引长度，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少列的索引，这里就会计算出去，没有使用到的列不会计算进去。留意下整个列的值，算一下你的多列索引使用长度就知道有没有使用到所有的列。另外 key_len 只计算where 条件用到的索引长度，而 排序和分组就算用到了索引，也不会计算到 key_len 中。

具体的长度计算逻辑在《MySQL是怎样运行的》中有具体示例，如有需要可以看书。

8. ref

当访问方法时 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 中的其中一个时，ref 列展示的就是与索引列进行等值匹配的东西，比如只是一常数或者某一列

-- 如下查询：以 t1 表作为驱动表，查询条件是 const 级别， 以 t2 表作为被驱动表，对应的 ref列是 demo.t1.id
mysql> explain select * from t1 inner join t2 on t1.id = t2.id where t1.a = 'a';
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref    | PRIMARY,idx_a | idx_a   | 5       | const      |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
2 rows in set (0.05 sec)

需要注意的是有时与索引列做匹配的是一个函数，此时 ref 列展示的是 func，如下：

mysql> explain select * from t1 inner join t2 on t1.id = upper(t2.id);
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set (0.05 sec)

9. rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数。

10. filtered

5.7 版本之前，使用 explain extended 命令才会出现此字段，5.7 版本之后默认有此字段，这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，这里是百分比，并非具体记录数。在单表查询中这一列没有什么意义，更应该在连接查询中关注驱动表对应的执行计划的 filtered 值。

11. extra 列

  1. No table used : 不带 from字句的查询或者 from dual 查询, 如下：

    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 (0.05 sec)

  2. Impossible WHERE : 查询语句的 where 字句永远为 false，如下：

    mysql> explain select MAX(id) from t1 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 (0.05 sec)

  3. No matching min/max row : 当查询列表有 MIN 或者 MAX 函数，但是并没有记录符合 WHERE 字句中的搜索条件，如下：

    mysql> explain select MAX(id) from t1 where a = 'asda';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
| 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 (0.04 sec)

  4. Using index : 不需要回表，直接通过索引查询 (覆盖索引) ，如下：

    mysql> explain select a from t1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_a | 5       | NULL |  100 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set (0.05 sec)

  5. Using index condition : 有些搜索条件中虽然出现了索引列，但是不能充当边界条件来形成扫描区间，即不能用来减少需要扫描的记录数量，将会提示该额外信息; 出现了该信息提示，也可以说明该查询使用了索引下推 （Index Condition Pushdown ICP）。如下：

    mysql> explain select * from t1 where a > '1';
+----+-------------+----------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1		  | NULL       | range | a             | a   | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
1 row in set (0.05 sec)

  6. Using where : 当某个查询条件需要在 server 层进行判断时，会提示该信息。如下：这里虽然 a 列是索引，但是MySQL 判断全表扫描效率更高，因此没有使用索引（key 为 null）；全表扫描后将数据返回给 Server 层，在 Server 层过滤 a > ‘a’ 并且 c = ‘c’ 的条件.

    mysql> explain select * from t1 where a > 'a' and c = 'c';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_a         | NULL | NULL    | NULL |  100 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)

  7. Using join buffer(Block Nested Loop) : 在连接查询的执行过程中，当被驱动表不能有效利用索引加快访问速度时，MySQL一般会为其分配一块名为连接缓冲区（Join Buffer）的内存卡来加快查询速度；也就是使用基于块的嵌套循环算法来执行连接查询。如下：

    sql> explain select * from t1 inner join t2 on t1.c = t2.c;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set (0.06 sec)

    在 Extra 中显示了两个提示：

    • Using join buffer (Block Nested Loop) : 因为对于 t2 表的访问不能有效利用索引，所以只能退而求其次，使用 Join Buffer 来减少 t2 表的访问次数，提高性能。
    • Using where : 查询语句中的 t2 on t1.c = t2.c条件，因此 t1 表是驱动表，t2 表是被驱动表，所以在访问 t2 表的时候 t1.c 的值已经确定下来了，所以提示了 Using where 信息。

  8. Using intersect(…)、Using union(…)、Using sort_union(…) : 说明查询使用了 Intersect、Union 或 Sort-Union 索引合并。

  9. Zero limit ：当 Limit 参数为0时，表示不打算从表中读取出任何记录。

    mysql> explain select * from t1 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 (0.04 sec)

  10. Using filesort : 在有些情况下， 对结果集总的记录进行排序时，是可以使用到索引的。但是很多情况下排序操作是无法使用到索引的，只能在内存或磁盘中进行排序，而这种在内存或磁盘中进行排序的方式称为文件排序（filesort）。如果某个查询使用到了文件排序，则会显示该提示，如下：

    mysql> explain select * from t1 order by c;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.04 sec)

  11. Using temporary ：在许多查询的执行过程中，MySQL会借助临时表来完成一些去重、排序等功能。如果不能有效利用索引来完成查询，MySQL很有可能通过建立内部临时表执行查询。如果查询中使用了内部临时表则会展示该信息。

    mysql> explain select distinct(c) from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set (0.04 sec)

  12. Loosescan : 在将 IN 子查询转为半连接时，如果采用的是 LooseScan 执行策略，则驱动表的 Extra 列会显示该提示。

  13. Firstmatch ：在将 IN 子查询转为半连接时，如果采用的是 FirstMatch 执行策略，则被驱动表的 Extra 列会显示该提示。

  14. NULL : 查询的列未被索引覆盖，并且where 筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过 “回表” 来实现，不是纯粹地用到了索引，也不是完全没用到索引。（需要回表操作完成的查询）,如下：

    mysql> explain select * from t1 where a = 'a';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a         | idx_a | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

四、补充

1. EXPLAIN 扩展

1.1 Extend EXPLAIN

在使用 Explain 语句查看 SQL 的执行计划后，紧接着还可以使用 show warnings 语句来查看与这个查询计划有关的扩展信息, 如下：

mysql> explain select * from t1 where a = 'a' and c = 'c' ;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a         | idx_a | 5       | const |    1 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set (0.04 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                          |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `demo`.`t1`.`id` AS `id`,`demo`.`t1`.`a` AS `a`,`demo`.`t1`.`b` AS `b`,`demo`.`t1`.`c` AS `c` from `demo`.`t1` where ((`demo`.`t1`.`a` = 'a') and (`demo`.`t1`.`c` = 'c')) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

show warnings 展示了三个字段：Level、Code、 Message。其中当 Code =1003 时，Message 字段展示的信息类似于查询优化器将查询语句重写后的语句。

1.2 JSON 格式的执行计划

可以在 Explain 命令后追加 format=json 命令以得到一个 JSON 格式的执行计划，里面除了常规的信息外海包含该计划花费的成本。

mysql> explain format=json select * from t1 where a = 'a' and c = 'c';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1, # 整个查询语句只有一个 Select 关键字，该关键字对应的id号为 1
    "cost_info": {
      "query_cost": "1.20" # 整个查询语句预计执行成本
    },
    "table": {
      "table_name": "t1",
      "access_type": "ref",
      "possible_keys": [
        "idx_a"
      ],
      "key": "idx_a",
      "used_key_parts": [
        "a"
      ],
      "key_length": "5",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 0,
      "filtered": "10.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.02",
        "prefix_cost": "1.20",
        "data_read_per_join": "78"
      },
      # 执行查询中设计的列
      "used_columns": [  
        "id",
        "a",
        "b",
        "c"
      ],
      # 针对单表的查询条件
      "attached_condition": "(`demo`.`t1`.`c` = 'c')"
    }
  }
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

2. Intersection、Union、Sort-Union 索引合并

一般情况下 MySQL 只会为单个索引生成扫描空间，但是特殊情况下MySQL 也会为多个索引生成扫描区间。

2.1 Intersection

select  from  t1 where k1='a' and k2 = 'b'

其中 k1，k2 都是索引，此处存在三种方案：

  1. 扫描 k1 辅助索引，回表查询 k2 = ‘b’ 的记录
  2. 扫描 k2 辅助索引，回表查询 k1 = 'a’的记录
  3. 同时扫描 k1 和 k2 的辅助索引，然后从二者的操作结果中找出 id 列值相同的记录。此方案，即为 Intersection (交集)索引合并

如果要使用 Intersection (交集)索引合并 ，并且每个使用到的索引都是二级索引，那么则要求从每个索引中获取到的二级索引记录都是按照主键值排序的。比如上面的sql， 查询 k1 = ‘a’ 的辅助索引记录，因为其辅助索引列都是 a，又因为在索引列相同时按照主键排序，所以这里查询出来的二级索引记录是按照主键排序的，k2 = ‘b’ 同理，因此 上述SQL 可以使用 Intersection索引合并。

为什么需要主键有序？

  1. 从两个有序集合中取交集更为容易
  2. 如果获取到的id 值是有序的，则回表时就不是随机 IO了（相当于实现了MMR），从而提高效率。

2.2 Union

基本与 Intersection (交集)索引合并 类似，只不过这里取的是并集。

如：select from t1 where k1='a' or k2 = 'b'

2.3 Sort-Union

Union 需要保证扫描出来的二级索引的主键值是有序的，而 Sort-Union 则会将从各个索引中扫描到的记录的主键值进行排序，在按照 Union 索引合并的方式执行查询。相较于 Union， Sort-Union 多了一步将查询出来的记录按照主键排序的过程。

需要注意的是并不存在 Sort-Intersection 索引合并，在《MySQL是怎样运行的》一书中说是 Sort-Intersection 索引合并 查询出的记录太多，导致回表代价太大。

3. 基于块的嵌套循环连接

在连接查询过程中，分为驱动表和被驱动表，驱动表只需要访问一次，被驱动表可能需要访问多次。两表连接查询的过程并不是将所有满足条件的驱动表记录先查询出来放到一个地方然后再去被驱动表中查询（因为可能驱动表记录太大，无法存储），而是每获取到一条驱动表记录，就立即到被驱动表中寻找匹配的记录，如果得到了匹配的记录，就把组合后的记录发送给客户端，然后再到驱动表中获取下一条记录，这个过程将重复进行。

但上述这种方案需要频繁访问 被驱动表，因此InnoDB 存在一个 Join Buffer (连接缓冲区) 的概念，Join Buffer 在执行连接查询前申请的一块大小固定的内存。先把若干条驱动表结果集中的记录记录在 Join Buffer 中，然后开始扫描被驱动表，每一条被动表的记录一次性与 Join Buffer 中的多条驱动表记录进行匹配。由于匹配的过程是在内存中完成的，因此可以显著减少被驱动表的 IO 代价。此为基于块的嵌套循环连接（Block Nested-Loop Join）算法。

需要注意的是 : Join Buffer 中并不会存放驱动表记录的所有列，只有查询列表中的列和过滤条件中的列才会被放到 Join Buffer 中。

Join Buffer 示意图如下：
在这里插入图片描述

五、参考内容

书籍：《MySQL是怎样运行的——从根儿上理解MySQL》、《MySQL技术内幕 InnoDB存储引擎 》
全技术栈企业级性能调优万花筒
一张图彻底搞懂MySQL的 explain
MySQL优化—工欲善其事，必先利其器之EXPLAIN*
https://coding.imooc.com/class/442.html
https://segmentfault.com/a/1190000021458117?utm_source=tag-newest
https://www.cnblogs.com/tufujie/p/9413852.html
https://blog.csdn.net/qq_27399407/article/details/93741225
https://smartan123.github.io/book/?file=home-%E9%A6%96%E9%A1%B5
https://www.cnblogs.com/zhanjindong/archive/2013/11/23/3439042.html
如有侵扰，联系删除。 内容仅用于自我记录学习使用。如有错误，欢迎指正

猫吻鱼
关注
  • 25
    点赞
  • 踩
  • 28
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
mysql explain update,MySQL中explain命令详解
weixin_39736650的博客
03-17 819
MySQL中的explain命令显示了mysql如何使用索引来处理select语句以及连接表。explain显示的信息可以帮助选择更好的索引和写出更优化的查询语句。MySQL中explain命令的使用其实很简单，只要explain后边跟着SQL语句就行了。例如：explain select * from tb_student;好奇的同学可能要问了，MySQL中的explain命令有什么特别之处吗？...
MySQL中 EXPLAIN 命令详解
create
07-09 147
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法，在select语句前加上explain就可以了： 如： explain select id,mobile,gender from tbl_user where status = 1 and flag_check=1 当使用EXTENDED关键字时，E...
MySQL的explain命令详解
dongyan3595的博客
06-21 419
在查询中每个表在输出中占一行，如果查询是两个表的联接，那么输出中将有两行，因此如果一个表与自己联接，输出中也会有两行。explain命令是查看MySQL查询优化器如何执行查询的主要方法，可以很好的分析SQL语句的执行情况。每当遇到执行慢（在业务角度）的SQL，都可以使用explain检查SQL的执行情况，并根据explain的结果相应的去调优SQL等。记住，输出中的行以MySQL实际执行的查询的顺序出现，而这个顺序不总是与其在原始SQL中保持一致。
MySQL中explain命令详解
qf2019的博客
08-19 411
MySQL中的explain命令显示了mysql如何使用索引来处理select语句以及连接表。explain显示的信息可以帮助选择更好的索引和写出更优化的查询语句。MySQL中explain命令的使用其实很简单，只要explain后边跟着SQL语句就行了。例如：explain select * from tb_student; 好奇的同学可能要问了，MySQL中的explain命令有什么特别之处吗？值得单独拿出来讲，MySQL为什么需要explain命令呢？简单来说：通过explain命令我们可以学习到该条
MySQL EXPLAIN 命令详解
Future_LL的博客
04-03 187
在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策...
MySQL explain命令详解
IT_GJW的博客
02-13 6082
使用explain + sql语句可以得到该条sql语句的执行计划，具体信息如下 下面将依次介绍每个字段的含义 id id表示在多表查询时，表的执行顺序，它是一组数字序列号，表示查询中执行select子句或操作表的顺序,其取值分为以下三种情况 1.id相同，表示表的执行顺序由上至下 上例中，表示该sql语句执行时对三张表的查询顺序是先查询t1，再查询t3，最后查询t3 2....
MySQL调试--explain命令详解
IT利刃出鞘的博客
03-24 1147
本文介绍MySQL的explain命令的用法及其结果的含义。
mysql explain命令解析_MySQL Explain 命令详解
weixin_33417703的博客
01-19 71
Explain 作用Explain 提供了 MySQL 如何执行 SQL 语句的信息，通过这些信息，可以对 SQL 语句做相应的优化，提高执行效率。Explain 使用调用 Explain，只需要在 SQL 语句前添加 explain 关键字即可。一般情况下，添加 explain 关键字后，认为 MySQL 不会执行查询，但是如果在 from 子句中包含子查询，那么 MySQL 实际上会执行子查询...
MySQL Explain 字段详解
我的博客
03-26 1445
结合查询示例对 Explain 工具查询的列进行讲解
mysql+explain怎么看_MySQL中explain命令详解
weixin_31596631的博客
01-19 651
explain的作用我们使用explain命令来查看mysql语句的执行计划(execution plan),解释mysql是如何执行一条sql语句的；解释的内容主要包括表的连接方式和顺序，以及索引的使用情况。使用explain，可以分析出需要在哪里加上索引，以及调整表的连接，以达到优化查询的目的；explain命令之后不仅可以跟select语句，也可以跟delete,insert,update,...
MySQL中EXPLAIN命令详解
01-19
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法，在select语句前加上explain就可以了： 如： mysql> explain select * from kt_course ...
【MySQL】explain命令详解（一）
12-14
先来看看explain命令的表头 表头 1、id 查询的序列号，包含一组数字，表示了一个查询过程中select子句或操作表的顺序。 有三种情况： （1）id相同，表示顺序执行，自上而下。 原创文章 10获赞 13访问量 5284 ...
【MySQL】explain命令详解（二）
12-14
explain命令的表头 4、type 表示该查询的类型，有如下8种值： system：表示表中只有一行记录。 const：通过索引查找，且一次找到记录，针对主键或唯一键的索引，如用户ID。  例子中，主键作为where子句的条件，...
MySQL中执行计划explain命令示例详解
09-09
主要给大家介绍了关于MySQL中执行计划explain命令的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用explain命令具有一定的参考学习价值，需要的朋友们下面说来一起学习学习吧
MySQL 高级 - 第十一章 | 索引优化与查询优化
xhmico的博客
06-07 1302
上篇：第十章、性能分析工具的使用本文内容主要源于：bilibili-尚硅谷-MySQL高级篇都有哪些纬度可以进行数据库调优？简言之：关于数据库调优的知识点非常分散，不同 ，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。虽然 查询优化的技术很多，但是大体方向上完全可以分为 和 两大块。学员表插 万条， 班级表插 万条步骤1：建表 步骤2：设置参数命令开启：允许创建函数设置 步骤3：创建函数随机产生字符串，保证每条数据都不同 随机产生班级编号 步骤4：创建存储过程创建往 表中插入数据的存储
MySQL—多表查询—外连接
m0_74363339的博客
06-05 480
这篇博客主要讲到了关于MySQL中多表查询中的外连接的查询语法。其中关于左外连接和右外连接通过语法结构以及案例需求来进行学习和实操.....
python 比较 mysql 表结构差异
oyc619491800的博客
06-05 183
python 比较 mysql 表结构差异
Mysql的 存储过程（procedure）
最新发布
shark1978的专栏
06-09 698
存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名字和参数（如果有）来执行它。存储过程可以在很多数据库管理系统（DBMS）中找到，如SQL Server、Oracle、MySQL等。
【Mysql】mysql 拼接字符
姜太小白的博客
06-06 312
在 MySQL 中，你可以使用CONCAT()函数来拼接（连接）字符串。CONCAT()函数接受两个或多个字符串参数，并返回连接后的字符串。
mysql Explain type详解
05-19
MySQL 的 `EXPLAIN` 命令用于分析查询语句的执行过程。在使用 `EXPLAIN` 命令时，会返回一个结果集，其中包含关于查询语句的执行计划的信息。这些信息包括：查询使用的索引、数据表的连接顺序、以及查询执行时扫描的行数。 MySQL 中 Explain 的结果有如下几种类型 - `system` :单个表的查询，MySQL会将这张表一次性读取到内存中 - `const`: 确定结果集中最多有一行输出 - `eq_ref`: 使用索引查询主键或唯一键，每次查询最多返回一行 - `ref`: 使用索引查询非唯一键，可能会返回多行 - `range`: 使用索引查询一个范围内的数据 - `index`: 全表扫描 EXPLAIN 命令返回的结果集中一般有以下几列： - id: 查询语句中表的序号 - select_type: 查询类型 - table: 查询使用的数据表 - type: 查询执行计划类型 - possible_keys: 可能使用的索引 - key: 实际使用的索引 - key_len: 使用的索引长度 - ref: 使用的索引字段 - rows: 查询估计扫描的行数 - Extra: 其它信息，常见的有'Using index', 'Using temporary', 'Using filesort'等 通过分析这些信息可以

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

猫吻鱼

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值