一文学会MySQL的explain工具

开篇说明

(1) 本文将细致介绍MySQL的explain工具，是下一篇《一文读懂MySQL的索引机制及查询优化》的准备篇。

(2) 本文主要基于MySQL5.7版本(https://dev.mysql.com/doc/refman/5.7/en/)，MySQL8.x版本可另行翻阅对应版本文档(https://dev.mysql.com/doc/refman/8.0/en/)。

(3) 演示过程中的建库、建表、建索引等语句仅为了测试explain工具的使用，并未考虑实际应用场景的合理性。

explain工具介绍

相关文档：
https://dev.mysql.com/doc/refman/5.7/en/explain.html
https://dev.mysql.com/doc/refman/5.7/en/using-explain.html

EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).

简单翻译一下，就是explain用于获取查询执行计划（即MySQL是如何执行一个查询的）。

工作中，我们会遇到慢查询，这个时候我们就可以在select语句之前增加explain关键字，模拟MySQL优化器执行SQL语句，从而分析该SQL语句有没有用上索引、是否全表扫描、能否进一步优化等。

还是来个快速入门的案例比较直观，依次在mysql的命令行执行下面几条语句(建库、建表sql脚本见下面的数据准备部分)：

mysql> use `explain_test`;
mysql> select * from tb_hero where hero_name = '李寻欢' and book_id = 1;
mysql> explain select * from tb_hero where hero_name = '李寻欢' and book_id = 1;
mysql> show warnings \G

得到下面的输出：

mysql> use `explain_test`;
Database changed
mysql> select * from tb_hero where hero_name = '李寻欢' and book_id = 1;
+---------+-----------+--------------+---------+
| hero_id | hero_name | skill        | book_id |
+---------+-----------+--------------+---------+
|       1 | 李寻欢    | 小李飞刀     |       1 |
+---------+-----------+--------------+---------+
1 row in set (0.00 sec)

mysql> explain select * from tb_hero where hero_name = '李寻欢' and book_id = 1;
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_hero | NULL       | ref  | idx_book_id_hero_name | idx_book_id_hero_name | 136     | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `explain_test`.`tb_hero`.`hero_id` AS `hero_id`,`explain_test`.`tb_hero`.`hero_name` AS `hero_name`,`explain_test`.`tb_hero`.`skill` AS `skill`,`explain_test`.`tb_hero`.`book_id` AS `book_id` from `explain_test`.`tb_hero` where ((`explain_test`.`tb_hero`.`book_id` = 1) and (`explain_test`.`tb_hero`.`hero_name` = '李寻欢'))
1 row in set (0.00 sec)

先别急explain语句输出结果每一列表示什么意思(后面会具体描述)，用show warnings命令可以得到优化后的查询语句大致长什么样子。

补充：

有关show warnings更详细的使用见https://dev.mysql.com/doc/refman/5.7/en/show-warnings.html
有关获取explain额外的输出信息见https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html

原SQL语句：

select * from tb_hero where hero_name = '李寻欢' and book_id = 1;

优化后的SQL语句：

select `explain_test`.`tb_hero`.`hero_id`   AS `hero_id`,
       `explain_test`.`tb_hero`.`hero_name` AS `hero_name`,
       `explain_test`.`tb_hero`.`skill`     AS `skill`,
       `explain_test`.`tb_hero`.`book_id`   AS `book_id`
from `explain_test`.`tb_hero`
where ((`explain_test`.`tb_hero`.`book_id` = 1) and (`explain_test`.`tb_hero`.`hero_name` = '李寻欢'))

可以看出，MySQL优化器把*优化成具体的列名，另外把我where中的两个过滤条件hero_name、book_id先后顺序调换了一下，这种顺序调换是概率性事件还是另有文章？
(哈哈哈，(●´ω｀●)留个悬念，本篇仅介绍explain工具，读了下篇《一文读懂MySQL的索引机制及查询优化》后自然豁然开朗)

数据准备

为了方便演示explain工具的使用以及输出结果的含义，准备了一些测试数据，初始化sql脚本如下:

-- ----------------------------
--  create database
-- ----------------------------
DROP database IF EXISTS `explain_test`;
create database `explain_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- switch database
use `explain_test`;

-- ----------------------------
--  table structure for `tb_book`
-- ----------------------------
DROP TABLE IF EXISTS `tb_book`;
CREATE TABLE `tb_book` (
  `book_id` int(11) NOT NULL,
  `book_name` varchar(64) DEFAULT NULL,
  `author` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`book_id`),
  UNIQUE KEY `uk_book_name` (`book_name`) USING BTREE,
  INDEX `idx_author` (`author`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

BEGIN;
INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (1, '多情剑客无情剑', '古龙');
INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (2, '笑傲江湖', '金庸');
INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (3, '倚天屠龙记', '金庸');
INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (4, '射雕英雄传', '金庸');
INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (5, '绝代双骄', '古龙');
COMMIT;

-- ----------------------------
--  table structure for `tb_hero`
-- ----------------------------
DROP TABLE IF EXISTS `tb_hero`;
CREATE TABLE `tb_hero` (
  `hero_id` int(11) NOT NULL,
  `hero_name` varchar(32) DEFAULT NULL,
  `skill` varchar(64) DEFAULT NULL,
  `book_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`hero_id`),
  INDEX `idx_book_id_hero_name`(`book_id`, `hero_name`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

BEGIN;
INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (1, '李寻欢', '小李飞刀', 1);
INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (2, '令狐冲', '独孤九剑', 2);
INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (3, '张无忌', '九阳神功', 3);
INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (4, '郭靖', '降龙十八掌', 4);
INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (5, '花无缺', '移花接玉', 5);
INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (6, '任我行', '吸星大法', 2);
COMMIT;

-- ----------------------------
--  Table structure for `tb_book_hero`
-- ----------------------------
DROP TABLE IF EXISTS `tb_book_hero`;
CREATE TABLE `tb_book_hero` (
  `book_id` int(11) NOT NULL,
  `hero_id` int(11) NOT NULL,
  `user_comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`book_id`, `hero_id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

BEGIN;
INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (1, 1, '小李飞刀，例无虚发，夺魂索命，弹指之间');
INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (2, 2, '令狐少侠留步!');
INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (3, 3, '尝遍世间善恶，归来仍是少年');
INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (4, 4, '我只要我的靖哥哥!');
INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (5, 5, '风采儒雅亦坦荡，武艺精深兼明智。');
INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (2, 6, '有人就有恩怨，有恩怨就有江湖，人心即是江湖，你如何退出！');
COMMIT;

explain的输出结果

相关文档：
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

看一下官方文档显示的关于explain输出结果列(explain output columns)的含义：

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

其中JSON Name指的是当设定FORMAT=JSON时，列名在json中显示的name，见下面的演示就明白了

mysql> explain select * from tb_book \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_book
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain FORMAT=JSON select * from tb_book \G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "table": {
      "table_name": "tb_book",
      "access_type": "ALL",
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "1.00",
        "prefix_cost": "2.00",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "book_id",
        "book_name",
        "author"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

下面重点看一下比较重要的几个字段。

id列

id是select的唯一标识，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的，id值越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

为了验证上面的结论，临时关闭mysql5.7对子查询(sub queries)产生的衍生表(derived tables)的合并优化

set session optimizer_switch='derived_merge=off';

详情见：
https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html

https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html

mysql> set session optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> select (select count(1) from tb_book) as book_count, (select count(1) from tb_hero) as hero_count from (select * from tb_book_hero) as book_hero;
+------------+------------+
| book_count | hero_count |
+------------+------------+
|          5 |          6 |
|          5 |          6 |
|          5 |          6 |
|          5 |          6 |
|          5 |          6 |
|          5 |          6 |
+------------+------------+
6 rows in set (0.00 sec)

mysql> explain select (select count(1) from tb_book) as book_count, (select count(1) from tb_hero) as hero_count from (select * from tb_book_hero) as book_hero;
+----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived4>   | NULL       | ALL   | NULL          | NULL                  | NULL    | NULL |    6 |   100.00 | NULL        |
|  4 | DERIVED     | tb_book_hero | NULL       | ALL   | NULL          | NULL                  | NULL    | NULL |    6 |   100.00 | NULL        |
|  3 | SUBQUERY    | tb_hero      | NULL       | index | NULL          | idx_book_id_hero_name | 136     | NULL |    6 |   100.00 | Using index |
|  2 | SUBQUERY    | tb_book      | NULL       | index | NULL          | uk_book_name          | 259     | NULL |    5 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
4 rows in set, 1 warning (0.00 sec)

mysql> set session optimizer_switch='derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

可见，查询语句中有4个select，先执行的是select * from tb_book_hero，然后执行select count(1) from tb_hero，再执行select count(1) from tb_book，最后执行select book_count, hero_count from book_hero

select_type列

select_type表示的是查询类型，常见的包括SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION

(1) SIMPLE：简单查询(不包含子查询和UNION查询)

mysql> explain select * from tb_book where book_id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_book | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

(2) PRIMARY：复杂查询中最外层的查询
(3) SUBQUERY：包含在select中的子查询(不在from子句中)
(4) DERIVED：包含在from子句中的子查询，MySQL会将结果存放在一个临时表中，也称为派生表(derived tables)

这3种select_type见下面的例子

mysql> set session optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select (select count(1) from tb_book) as book_count from (select * from tb_book_hero) as book_hero;
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived3>   | NULL       | ALL   | NULL          | NULL         | NULL    | NULL |    6 |   100.00 | NULL        |
|  3 | DERIVED     | tb_book_hero | NULL       | ALL   | NULL          | NULL         | NULL    | NULL |    6 |   100.00 | NULL        |
|  2 | SUBQUERY    | tb_book      | NULL       | index | NULL          | uk_book_name | 259     | NULL |    5 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql> set session optimizer_switch='derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

(5) UNION：在UNION中的第二个和随后的select

mysql> select * from tb_book where book_id = 1 union all select * from tb_book where book_name = '笑傲江湖';
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情剑客无情剑        | 古龙   |
|       2 | 笑傲江湖              | 金庸   |
+---------+-----------------------+--------+
2 rows in set (0.00 sec)

mysql> explain select * from tb_book where book_id = 1 union all select * from tb_book where book_name = '笑傲江湖';
+----+-------------+---------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | tb_book | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL  |
|  2 | UNION       | tb_book | NULL       | const | uk_book_name  | uk_book_name | 259     | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

table列

table表示查询涉及的表或衍生表。

常见table列是<derivenN>格式，表示当前查询依赖id=N的查询，需先执行id=N的查询。上面含select_type为DERIVED的查询就是这种情况，这里不再重复举例。

type列

相关文档：
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

type列是判断查询是否高效的重要依据，我们可以通过type字段的值，判断此次查询是全表扫描还是索引扫描等，进而进一步优化查询。

一般来说表示查询性能最优到最差依次为：NULL > system > const > eq_ref > ref > range > index > ALL

前面的几种类型都是利用到了索引来查询数据, 因此可以过滤部分或大部分数据, 查询效率自然就比较高了。
而后面的index类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比ALL类型稍快。
所以，应当尽可能地保证查询达到range级别，最好达到ref。

(0) NULL: 不用访问表或者索引，直接就能得到结果，如：在索引列中选取最大值，执行时不需要再访问表

mysql> explain select max(book_id) from tb_book;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

(1) system：The table has only one row. This is a special case of the const join type.

当查询的表只有一行的情况下，system是const类型的特例，

(2) const：It is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values.

针对主键或唯一索引的等值查询扫描, 最多只返回一行数据。const查询速度非常快, 因为它仅仅读取一次即可。

关于type列为system、const的情况，见下面的示例：

mysql> set session optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from (select * from tb_book where book_id = 5) as book;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | tb_book    | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> set session optimizer_switch='derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

(3) eq_ref：It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

此类型通常出现在多表的join查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是=，查询效率较高。

mysql> select tb_hero.*, tb_book_hero.user_comment from tb_book_hero, tb_hero where tb_book_hero.book_id = 2 and tb_book_hero.hero_id = tb_hero.hero_id;
+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+
| hero_id | hero_name | skill        | book_id | user_comment                                                                         |
+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+
|       2 | 令狐冲    | 独孤九剑     |       2 | 令狐少侠留步!                                                                        |
|       6 | 任我行    | 吸星大法     |       2 | 有人就有恩怨，有恩怨就有江湖，人心即是江湖，你如何退出！                             |
+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain select tb_hero.*, tb_book_hero.user_comment from tb_book_hero, tb_hero where tb_book_hero.book_id = 2 and tb_book_hero.hero_id = tb_hero.hero_id;
+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+
| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref                               | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+
|  1 | SIMPLE      | tb_book_hero | NULL       | ref    | PRIMARY       | PRIMARY | 4       | const                             |    2 |   100.00 | NULL  |
|  1 | SIMPLE      | tb_hero      | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | explain_test.tb_book_hero.hero_id |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> select tb_hero.*, tb_book_hero.user_comment from tb_book_hero join tb_hero on tb_book_hero.book_id = 2 and tb_book_hero.hero_id = tb_hero.hero_id;
+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+
| hero_id | hero_name | skill        | book_id | user_comment                                                                         |
+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+
|       2 | 令狐冲    | 独孤九剑     |       2 | 令狐少侠留步!                                                                        |
|       6 | 任我行    | 吸星大法     |       2 | 有人就有恩怨，有恩怨就有江湖，人心即是江湖，你如何退出！                             |
+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain select tb_hero.*, tb_book_hero.user_comment from tb_book_hero join tb_hero on tb_book_hero.book_id = 2 and tb_book_hero.hero_id = tb_hero.hero_id;
+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+
| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref                               | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+
|  1 | SIMPLE      | tb_book_hero | NULL       | ref    | PRIMARY       | PRIMARY | 4       | const                             |    2 |   100.00 | NULL  |
|  1 | SIMPLE      | tb_hero      | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | explain_test.tb_book_hero.hero_id |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

(4) ref: It is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value).

相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的最左前缀，可能会找到多个符合条件的行。

简单的select查询，author列上建有普通索引（非唯一索引）

mysql> explain select * from tb_book where author = '古龙';
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_book | NULL       | ref  | idx_author    | idx_author | 131     | const |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

关联表查询，tb_book_hero表使用了联合主键PRIMARY KEY (book_id, hero_id)，这里使用到了左边前缀book_id进行过滤。

mysql> explain select * from tb_book_hero where book_id = 3;
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_book_hero | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

(5) range: It can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators
扫描部分索引(范围扫描)，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>、in等查询

mysql> explain select * from tb_book where book_id > 3;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_book | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

(6) index：the index tree is scanned, MySQL can use this type when the query uses only columns that are part of a single index.
表示全索引扫描(full index scan), 和ALL类型类似, 只不过ALL类型是全表扫描, 而index类型则仅仅扫描所有的索引, 而不扫描数据.

mysql> explain select book_name from tb_book;
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_book | NULL       | index | NULL          | uk_book_name | 259     | NULL |    5 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

上面的例子中, 我们查询的book_name字段上恰好有索引, 因此我们直接从索引中获取数据就可以满足查询的需求了, 而不需要查询表中的数据。因此这样的情况下, type的值是index, 并且Extra的值大多是Using index。

(7) ALL: A full table scan is done
表示全表扫描, 这个类型的查询是性能最差的查询之一。通常来说, 我们的查询不应该出现ALL类型的查询, 因为这样的查询在数据量大的情况下, 严重降低数据库的性能。如果一个查询是ALL类型查询, 那么大多可以对相应的字段添加索引来避免。

mysql> explain select * from tb_hero where hero_name = '令狐冲';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_hero | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

possible_keys列

表示MySQL在查询时, 能够使用到的索引。注意, 即使有些索引在possible_keys中出现, 但是并不表示此索引会真正地被MySQL使用到。MySQL在查询时具体使用了哪些索引, 由key字段决定。

key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是NULL。

key_len列

表示查询优化器使用了索引的字节数，这个字段可以评估联合索引是否完全被使用, 或只有最左部分字段被使用到。
举例来说，tb_hero表的联合索引idx_book_id_hero_name由book_id和hero_name两个列组成，int类型占4字节，另外如果字段允许为NULL，需要1字节记录是否为NULL，通过结果中的key_len=5(tb_hero.book_id允许为NULL)可推断出查询使用了第一个列book_id列来执行索引查找；再拿tb_book_hero表联合主键PRIMARY KEY (book_id, hero_id)举例，通过key_len=4(tb_book_hero.book_id不允许为NULL)可推断出查询使用了第一个列book_id列来执行索引查找

mysql> explain select * from tb_hero where book_id = 2;
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_hero | NULL       | ref  | idx_book_id_hero_name | idx_book_id_hero_name | 5       | const |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

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

key_len的计算规则如下:

字符串:
- char(n): n字节长度
- varchar(n): 如果是utf8编码, 则是3n + 2字节; 如果是utf8mb4编码, 则是4n + 2字节.
数值类型:
- TINYINT: 1字节
- SMALLINT: 2字节
- MEDIUMINT: 3字节
- INT: 4字节
- BIGINT: 8字节
时间类型
- DATE: 3字节
- TIMESTAMP: 4字节
- DATETIME: 8字节
字段属性:
- NULL属性占用一个字节
- 如果一个字段是NOT NULL的, 则没有此属性

再看下面的计算:
4 [book_id是int类型] + 1 [book_id允许为NULL] + (4 * 32 + 2) [hero_name是varchar32,且用的是utf8mb4编码] + 1 [hero_name允许为NULL] = 136

mysql> explain select * from tb_hero where book_id = 2 and hero_name = '令狐冲';
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_hero | NULL       | ref  | idx_book_id_hero_name | idx_book_id_hero_name | 136     | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

ref列

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.
显示的是哪个字段或常数与key一起被使用

rows列

MySQL查询优化器根据统计信息, 估算SQL要查找到结果集需要扫描读取的数据行数，注意这个不是结果集里的行数。这个值非常直观显示SQL的效率好坏, 原则上rows越少越好。

Extra列

这一列展示的是额外信息。常见的重要值如下:

(1) Using index

表示查询在索引树中就可查到所需数据, 不用扫描表数据文件

mysql> explain select hero_id from tb_book_hero where book_id = 2;
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_book_hero | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    2 |   100.00 | Using index |
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select book_id  from tb_book where author = '金庸';
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_book | NULL       | ref  | idx_author    | idx_author | 131     | const |    3 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

(2) Using where
查询的列没有全部被索引覆盖

mysql> explain select book_id, book_name from tb_book where author = '金庸';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_book | NULL       | ALL  | idx_author    | NULL | NULL    | NULL |    5 |    60.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

(3) Using temporary

查询有使用临时表，一般出现于排序、分组、多表join、distinct查询等等。

举例子如下：tb_book表对book_name字段建立了唯一性索引，这时候distinct查询Extra列为Using index; tb_hero表的skill字段上没有任何索引，这时候distinct查询Extra列为Using temporary

mysql> select distinct book_name from tb_book;
+-----------------------+
| book_name             |
+-----------------------+
| 倚天屠龙记            |
| 多情剑客无情剑        |
| 射雕英雄传            |
| 笑傲江湖              |
| 绝代双骄              |
+-----------------------+
5 rows in set (0.00 sec)

mysql> explain select distinct book_name from tb_book;
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_book | NULL       | index | uk_book_name  | uk_book_name | 259     | NULL |    5 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select distinct skill from tb_hero;
+-----------------+
| skill           |
+-----------------+
| 小李飞刀        |
| 独孤九剑        |
| 九阳神功        |
| 降龙十八掌      |
| 移花接玉        |
| 吸星大法        |
+-----------------+
6 rows in set (0.00 sec)

mysql> explain select distinct skill from tb_hero;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | tb_hero | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

(4) Using filesort

表示MySQL不能通过索引顺序达到排序效果，需额外的排序操作，数据较小时在内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

mysql> explain select book_id, hero_name from tb_hero order by hero_name;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tb_hero | NULL       | index | NULL          | idx_book_id_hero_name | 136     | NULL |    6 |   100.00 | Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select book_id, hero_name from tb_hero order by book_id, hero_name;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_hero | NULL       | index | NULL          | idx_book_id_hero_name | 136     | NULL |    6 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

tb_hero表上有联合索引INDEX idx_book_id_hero_name(book_id, hero_name) USING BTREE
但是order by hero_name, 不能使用索引进行优化(下一篇博客会介绍联合索引的结构), 进而会产生Using filesort
如果将排序依据改为order by book_id, hero_name, 就不会出现Using filesort了。

(5) Select tables optimized away
比如下面的例子：

mysql> explain select min(book_id), max(book_id) from tb_book;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

posted @ 2020-08-03 08:05 行无际阅读(3084) 评论(0) 编辑收藏举报

刷新页面返回顶部

代码改变世界

行无际

行无际的博客