一文學會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
);

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

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