一文學會MySQL的explain工具

開篇說明

(1) 本文將細緻介紹MySQL的explain工具，是下一篇《一文讀懂MySQL的索引機制及查詢優化》的準備篇。

(2) 本文主要基於MySQL 5.7 版本( https://dev.mysql.com/doc/refman/5.7/en/ )，MySQL 8.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
);

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
);

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最後執行。

爲了驗證上面的結論，臨時關閉mysql 5.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)