一文讀懂 MySQL Explain 執(zhí)行計(jì)劃

一、前言

上周老周的一個(gè)好朋友讓我出一篇教你讀懂 SQL 執(zhí)行計(jì)劃，和我另一位讀者反饋的面試題如何排查慢 SQL 的強(qiáng)相關(guān)，索性先出一篇一文讀懂 MySQL Explain 執(zhí)行計(jì)劃。Explain 執(zhí)行計(jì)劃你一定得會(huì)看，不然你簡(jiǎn)歷上就別去寫(xiě)什么你會(huì) SQL 調(diào)優(yōu)，不然面試官會(huì)覺(jué)得，Explain 執(zhí)行計(jì)劃你都不會(huì)看，那你還 SQL 調(diào)啥優(yōu)?。縎QL 調(diào)你吧？？？開(kāi)個(gè)小玩笑，玩笑歸玩笑，重要是真的重要?。?！

二、Explain 執(zhí)行計(jì)劃是什么？

什么是執(zhí)行計(jì)劃？簡(jiǎn)而言之，就是 SQL 在數(shù)據(jù)庫(kù)中執(zhí)行時(shí)的表現(xiàn)情況，通常用于 SQL 性能分析、優(yōu)化和加鎖分析等場(chǎng)景，執(zhí)行過(guò)程會(huì)在 MySQL 查詢(xún)過(guò)程中由解析器，預(yù)處理器和查詢(xún)優(yōu)化器共同生成。在 MySQL 中使用 explain 關(guān)鍵字來(lái)查看。

2.1 執(zhí)行計(jì)劃有什么用？

它可以用來(lái)分析 SQL 語(yǔ)句和表結(jié)構(gòu)的性能瓶頸

• 關(guān)聯(lián)查詢(xún)的執(zhí)行順序

• 查詢(xún)操作的操作類(lèi)型

• 哪些索引可以被命中

• 哪些索引實(shí)際被命中

• 每張表有多少記錄參與查詢(xún)

• …

2.2 MySQL 執(zhí)行過(guò)程

如上圖所示，MySQL 數(shù)據(jù)庫(kù)由 Server 層和 Engine 層組成：

• Server 層有 SQL 分析器、SQL優(yōu)化器、SQL 執(zhí)行器，用于負(fù)責(zé) SQL 語(yǔ)句的具體執(zhí)行過(guò)程；

• Engine 層負(fù)責(zé)存儲(chǔ)具體的數(shù)據(jù)，如最常使用的 MyISAM、InnoDB 存儲(chǔ)引擎，還有用于在內(nèi)存中存儲(chǔ)臨時(shí)結(jié)果集的 TempTable 引擎。

SQL 優(yōu)化器會(huì)分析所有可能的執(zhí)行計(jì)劃，選擇成本最低的執(zhí)行，這種優(yōu)化器稱(chēng)之為：CBO（Cost-based Optimizer，基于成本的優(yōu)化器）。

而在 MySQL 中，一條 SQL 的計(jì)算成本計(jì)算如下所示：

Cost ?= Server Cost + Engine Cost
= CPU Cost + IO Cost

其中，CPU Cost 表示計(jì)算的開(kāi)銷(xiāo)，比如索引鍵值的比較、記錄值的比較、結(jié)果集的排序等這些操作都在 Server 層完成；

IO Cost 表示引擎層 IO 的開(kāi)銷(xiāo)，MySQL 8.0 可以通過(guò)區(qū)分一張表的數(shù)據(jù)是否在內(nèi)存中，分別計(jì)算讀取內(nèi)存 IO 開(kāi)銷(xiāo)以及讀取磁盤(pán) IO 的開(kāi)銷(xiāo)。

數(shù)據(jù)庫(kù) mysql 下的表 server_cost、engine_cost 則記錄了對(duì)于各種成本的計(jì)算，如：

表 server_cost 記錄了 Server 層優(yōu)化器各種操作的成本，這里面包括了所有 CPU Cost，其具體含義如下：

• disk_temptable_create_cost：創(chuàng)建磁盤(pán)臨時(shí)表的成本，默認(rèn)為 20。

• disk_temptable_row_cost：磁盤(pán)臨時(shí)表中每條記錄的成本，默認(rèn)為 0.5。

• key_compare_cost：索引鍵值比較的成本，默認(rèn)為 0.05，成本最小。

• memory_temptable_create_cost：創(chuàng)建內(nèi)存臨時(shí)表的成本：默認(rèn)為 1。

• memory_temptable_row_cost：內(nèi)存臨時(shí)表中每條記錄的成本，默認(rèn)為 0.1。

• row_evaluate_cost：記錄間的比較成本，默認(rèn)為 0.1。

可以看到， MySQL 優(yōu)化器認(rèn)為如果一條 SQL 需要?jiǎng)?chuàng)建基于磁盤(pán)的臨時(shí)表，則這時(shí)的成本是最大的，其成本是基于內(nèi)存臨時(shí)表的 20 倍。而索引鍵值的比較、記錄之間的比較，其實(shí)開(kāi)銷(xiāo)是非常低的，但如果要比較的記錄數(shù)非常多，則成本會(huì)變得非常大。

而表 engine_cost 記錄了存儲(chǔ)引擎層各種操作的成本，這里包含了所有的 IO Cost，具體含義如下：

• io_block_read_cost：從磁盤(pán)讀取一個(gè)頁(yè)的成本，默認(rèn)值為 1。

• memory_block_read_cost：從內(nèi)存讀取一個(gè)頁(yè)的成本，默認(rèn)值為 0.25。

也就是說(shuō)， MySQL 優(yōu)化器認(rèn)為從磁盤(pán)讀取的開(kāi)銷(xiāo)是內(nèi)存開(kāi)銷(xiāo)的 4 倍。

三、Explain 執(zhí)行計(jì)劃詳解

我們先來(lái)準(zhǔn)備以下 SQL 腳本：

CREATE?TABLE?`user`?(
????`id`?INT?(11)?NOT?NULL?AUTO_INCREMENT,
????`name`?VARCHAR?(20)?DEFAULT?NULL?COMMENT?"用戶(hù)名",
????PRIMARY?KEY?(`id`)
)?ENGINE?=?INNODB?DEFAULT?CHARSET?=?utf8mb4?COMMENT?=?"用戶(hù)表";

CREATE?TABLE?`user_robot_relate`?(
????`id`?INT?(11)?NOT?NULL?AUTO_INCREMENT,
????`user_id`?INT?(11)?NOT?NULL?COMMENT?"用戶(hù)id",
????`robot_id`?INT?(11)?NOT?NULL?COMMENT?"機(jī)器人id",
????PRIMARY?KEY?(`id`),?
????KEY?`idx_user_id`?(`user_id`),?
????KEY?`idx_robot_id`?(`robot_id`)
)?ENGINE?=?INNODB?DEFAULT?CHARSET?=?utf8mb4?COMMENT?=?"用戶(hù)與機(jī)器人表";

CREATE?TABLE?`robot`?(
????`id`?INT?(11)?NOT?NULL?AUTO_INCREMENT,
????`name`?VARCHAR?(?20?)?DEFAULT?NULL?COMMENT?"機(jī)器人名",
????PRIMARY?KEY?(`id`)
)?ENGINE?=?INNODB?DEFAULT?CHARSET?=?utf8mb4?COMMENT?=?"機(jī)器人表";

INSERT?INTO?user?VALUES?(1,?'riemann');
INSERT?INTO?user?VALUES?(2,?'andy');

INSERT?INTO?user_robot_relate?VALUES?(1,?1,?1);
INSERT?INTO?user_robot_relate?VALUES?(2,?1,?2);
INSERT?INTO?user_robot_relate?VALUES?(3,?2,?3);

INSERT?INTO?robot?VALUES?(1,?'小白鯨');
INSERT?INTO?robot?VALUES?(2,?'掃地機(jī)');
INSERT?INTO?robot?VALUES?(3,?'掃拖一體機(jī)');

我們創(chuàng)建三張表 user、user_robot_relate、robot，表之間的關(guān)系 user.id = user_robot_relate.user_id AND user_robot_relate.robot_id = robot.id。

先來(lái)看下我的 MySQL 版本，是 5.7.37 的。

接著我們看一下執(zhí)行計(jì)劃有哪些字段，先看個(gè)整體的，讓大家有個(gè)大概的認(rèn)識(shí)后，我們?cè)僦鹨蝗ピ斀夥治觥?br />
explain 執(zhí)行后輸出的結(jié)果集包含 12 列，分別是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered 和 Extra，下面對(duì)這些字段進(jìn)行解釋。

• id：Query Optimizer 所選定的執(zhí)行計(jì)劃中查詢(xún)的序列號(hào)

• select_type：顯示本行是簡(jiǎn)單或復(fù)雜 select。如果查詢(xún)有任何復(fù)雜的子查詢(xún)，則最外層標(biāo)記為PRIMARY、DERIVED.、UNION、UNION RESUIT 等。

• table：顯示這一步所訪(fǎng)問(wèn)的數(shù)據(jù)庫(kù)中的表的名稱(chēng)

• partitions：查詢(xún)時(shí)匹配到的分區(qū)信息，對(duì)于非分區(qū)表值為 NULL，當(dāng)查詢(xún)的是分區(qū)表時(shí)，partitions 顯示分區(qū)表命中的分區(qū)情況。

• type：數(shù)據(jù)訪(fǎng)問(wèn)、讀取操作類(lèi)型（ALL、index、range、ref、eq_ref、const、system）等

• possible_keys：該查詢(xún)可以利用的索引，如果沒(méi)有任何索引可以使用，就會(huì)顯示成 null，這一
  項(xiàng)內(nèi)容對(duì)于優(yōu)化時(shí)候索引的調(diào)整非常重要。

• key：MySQL Query Optimizer 從 possible_keys 中所選擇使用的索引

• key_len：被選中使用索引的索引鍵長(zhǎng)度

• ref：列出是通過(guò)常量（const），還是某個(gè)表的某個(gè)字段（如果是 join）來(lái)過(guò)濾（通過(guò) key）
  的。

• rows：MySQL Query Optimizer 通過(guò)系統(tǒng)收集到的統(tǒng)計(jì)信息估算出來(lái)的結(jié)果集記錄條數(shù)

• filtered：表示存儲(chǔ)引擎返回的數(shù)據(jù)在經(jīng)過(guò)過(guò)濾后，剩下滿(mǎn)足條件的記錄數(shù)量的比例。

• Extra：查詢(xún)中每一步實(shí)現(xiàn)的額外細(xì)節(jié)信息，如 Using filesort、index 等。

3.1 id

看到三條記錄的 id 都相同，可以理解成這三個(gè)表為一組，具有同樣的優(yōu)先級(jí)，執(zhí)行順序由上而下，具體順序由優(yōu)化器決定。

3.1.1 id 相同

mysql>?EXPLAIN?SELECT?*?FROM?user?u?WHERE?u.id?=?(SELECT?ur.user_id?FROM?user_robot_relate?ur?WHERE?ur.robot_id?=?(SELECT?r.id?FROM?robot?r?WHERE?r.name?=?'掃地機(jī)'));
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
|?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys????????????|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra??????????????????????????????????????????????|
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
|??1?|?SIMPLE??????|?ur????|?NULL???????|?ALL??|?idx_user_id,idx_robot_id?|?NULL?|?NULL????|?NULL?|????3?|???100.00?|?NULL???????????????????????????????????????????????|
|??1?|?SIMPLE??????|?u?????|?NULL???????|?ALL??|?PRIMARY??????????????????|?NULL?|?NULL????|?NULL?|????2?|????50.00?|?Using?where;?Using?join?buffer?(Block?Nested?Loop)?|
|??1?|?SIMPLE??????|?r?????|?NULL???????|?ALL??|?PRIMARY??????????????????|?NULL?|?NULL????|?NULL?|????3?|????50.00?|?Using?where;?Using?join?buffer?(Block?Nested?Loop)?|
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+

3.1.2 id 不同

如果我們的 SQL 中存在子查詢(xún)，那么 id 的序號(hào)會(huì)遞增，id 值越大優(yōu)先級(jí)越高，越先被執(zhí)行。當(dāng)三個(gè)表依次嵌套，發(fā)現(xiàn)最里層的子查詢(xún) id 最大，最先執(zhí)行。

mysql>?EXPLAIN?SELECT?*?FROM?user?u?WHERE?u.id?=?(SELECT?ur.user_id?FROM?user_robot_relate?ur?WHERE?ur.robot_id?=?(SELECT?r.id?FROM?robot?r?WHERE?r.name?=?'掃地機(jī)'));
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|?id?|?select_type?|?table?|?partitions?|?type??|?possible_keys?|?key??????????|?key_len?|?ref???|?rows?|?filtered?|?Extra???????|
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|??1?|?PRIMARY?????|?u?????|?NULL???????|?const?|?PRIMARY???????|?PRIMARY??????|?4???????|?const?|????1?|???100.00?|?NULL????????|
|??2?|?SUBQUERY????|?ur????|?NULL???????|?ref???|?idx_robot_id??|?idx_robot_id?|?4???????|?const?|????1?|???100.00?|?Using?where?|
|??3?|?SUBQUERY????|?r?????|?NULL???????|?ALL???|?NULL??????????|?NULL?????????|?NULL????|?NULL??|????3?|????33.33?|?Using?where?|
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+

3.1.3 以上兩種同時(shí)存在

將上邊的 SQL 稍微修改一下，增加一個(gè)子查詢(xún)，發(fā)現(xiàn) id 的以上兩種同時(shí)存在。相同 id 劃分為一組，這樣就有三個(gè)組，同組的從上往下順序執(zhí)行，不同組 id 值越大，優(yōu)先級(jí)越高，越先執(zhí)行。

mysql>?EXPLAIN?SELECT?*?FROM?user?u?WHERE?u.id?=?(SELECT?ur.user_id?FROM?user_robot_relate?ur?WHERE?ur.robot_id?=?(SELECT?r.id?FROM?robot?r?WHERE?r.name?=?'掃地機(jī)'))?AND?u.id?IN?(SELECT?u.id?FROM?user?u?WHERE?u.name?=?'riemann');
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|?id?|?select_type?|?table?|?partitions?|?type??|?possible_keys?|?key??????????|?key_len?|?ref???|?rows?|?filtered?|?Extra???????|
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|??1?|?PRIMARY?????|?u?????|?NULL???????|?const?|?PRIMARY???????|?PRIMARY??????|?4???????|?const?|????1?|???100.00?|?NULL????????|
|??1?|?PRIMARY?????|?u?????|?NULL???????|?const?|?PRIMARY???????|?PRIMARY??????|?4???????|?const?|????1?|???100.00?|?NULL????????|
|??2?|?SUBQUERY????|?ur????|?NULL???????|?ref???|?idx_robot_id??|?idx_robot_id?|?4???????|?const?|????1?|???100.00?|?Using?where?|
|??3?|?SUBQUERY????|?r?????|?NULL???????|?ALL???|?NULL??????????|?NULL?????????|?NULL????|?NULL??|????3?|????33.33?|?Using?where?|
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+

3.2 select_type

select_type：表示 select 查詢(xún)的類(lèi)型，主要是用于區(qū)分各種復(fù)雜的查詢(xún)，例如：普通查詢(xún)、聯(lián)合查詢(xún)、子查詢(xún)等。

• SIMPLE：表示最簡(jiǎn)單的 select 查詢(xún)語(yǔ)句，也就是在查詢(xún)中不包含子查詢(xún)或者 union 交并差集等操作。

• PRIMARY：當(dāng)查詢(xún)語(yǔ)句中包含任何復(fù)雜的子部分，最外層查詢(xún)則被標(biāo)記為 PRIMARY。

• SUBQUERY：當(dāng) select 或 where 列表中包含了子查詢(xún)，該子查詢(xún)被標(biāo)記為 SUBQUERY。

• DERIVED：表示包含在 from 子句中的子查詢(xún)的 select，在我們的 from 列表中包含的子查詢(xún)會(huì)被標(biāo)記為 derived。

• UNION：如果 union 后邊又出現(xiàn)的 select 語(yǔ)句，則會(huì)被標(biāo)記為 union；若 union 包含在 from 子句的子查詢(xún)中，外層 select 將被標(biāo)記為 derived。

• UNION RESULT：代表從 union 的臨時(shí)表中讀取數(shù)據(jù)，而 table 列的表示用第一個(gè)和第四個(gè) select 的結(jié)果進(jìn)行 union 操作。,4>

  mysql>?EXPLAIN?SELECT?t.user_id,?(SELECT?u.id?FROM?user?u)?o?FROM?(SELECT?ur.user_id,?ur.robot_id?FROM?user_robot_relate?ur?WHERE?ur.id?=?2)?t?UNION?(SELECT?r.id,?r.name?FROM?robot?r);
  +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
  |?id?|?select_type??|?table??????|?partitions?|?type??|?possible_keys?|?key?????|?key_len?|?ref???|?rows?|?filtered?|?Extra???????????|
  +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
  |??1?|?PRIMARY??????|?ur?????????|?NULL???????|?const?|?PRIMARY???????|?PRIMARY?|?4???????|?const?|????1?|???100.00?|?NULL????????????|
  |??2?|?SUBQUERY?????|?u??????????|?NULL???????|?index?|?NULL??????????|?PRIMARY?|?4???????|?NULL??|????2?|???100.00?|?Using?index?????|
  |??4?|?UNION????????|?r??????????|?NULL???????|?ALL???|?NULL??????????|?NULL????|?NULL????|?NULL??|????3?|???100.00?|?NULL????????????|
  |?NULL?|?UNION?RESULT?|??|?NULL???????|?ALL???|?NULL??????????|?NULL????|?NULL????|?NULL??|?NULL?|?????NULL?|?Using?temporary?|
  +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
  

3.3 table

查詢(xún)的表名，并不一定是真實(shí)存在的表，有別名顯示別名，也可能為臨時(shí)表，例如上邊的 DERIVED、等。,4>

3.4 partitions

查詢(xún)時(shí)匹配到的分區(qū)信息，對(duì)于非分區(qū)表值為 NULL，當(dāng)查詢(xún)的是分區(qū)表時(shí)，partitions 顯示分區(qū)表命中的分區(qū)情況。

3.5 type

type：查詢(xún)使用了何種類(lèi)型，它在 SQL優(yōu)化中是一個(gè)非常重要的指標(biāo)，以下性能從好到壞依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system：當(dāng)表僅有一行記錄時(shí)（系統(tǒng)表），數(shù)據(jù)量很少，往往不需要進(jìn)行磁盤(pán) IO，速度非?？臁?/p>

• const：表示查詢(xún)時(shí)命中 primary key 主鍵或者 unique 唯一索引，或者被連接的部分是一個(gè)常量（const）值。這類(lèi)掃描效率極高，返回?cái)?shù)據(jù)量少，速度非常快。


  mysql>?EXPLAIN?SELECT?*?FROM?robot?WHERE?id?=?1;
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  |?id?|?select_type?|?table?|?partitions?|?type??|?possible_keys?|?key?????|?key_len?|?ref???|?rows?|?filtered?|?Extra?|
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  |??1?|?SIMPLE??????|?robot?|?NULL???????|?const?|?PRIMARY???????|?PRIMARY?|?4???????|?const?|????1?|???100.00?|?NULL??|
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  

• eq_ref：查詢(xún)時(shí)命中主鍵 primary key 或者 unique key 索引， type 就是 eq_ref。

  mysql>?EXPLAIN?SELECT?u.name?FROM?user?u,?user_robot_relate?ur?WHERE?u.id?=?ur.id;?
  +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
  |?id?|?select_type?|?table?|?partitions?|?type???|?possible_keys?|?key?????|?key_len?|?ref???????|?rows?|?filtered?|?Extra???????|
  +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
  |??1?|?SIMPLE??????|?u?????|?NULL???????|?ALL????|?PRIMARY???????|?NULL????|?NULL????|?NULL??????|????2?|???100.00?|?NULL????????|
  |??1?|?SIMPLE??????|?ur????|?NULL???????|?eq_ref?|?PRIMARY???????|?PRIMARY?|?4???????|?test.u.id?|????1?|???100.00?|?Using?index?|
  +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
  

• ref：區(qū)別于 eq_ref，ref 表示使用非唯一性索引，會(huì)找到很多個(gè)符合條件的行。


  mysql>?EXPLAIN?SELECT?id?FROM?user_robot_relate?WHERE?user_id?=?2;?
  +----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
  |?id?|?select_type?|?table?????????????|?partitions?|?type?|?possible_keys?|?key?????????|?key_len?|?ref???|?rows?|?filtered?|?Extra???????|
  +----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
  |??1?|?SIMPLE??????|?user_robot_relate?|?NULL???????|?ref??|?idx_user_id???|?idx_user_id?|?4???????|?const?|????1?|???100.00?|?Using?index?|
  +----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
  

• ref_or_null：這種連接類(lèi)型類(lèi)似于 ref，區(qū)別在于 MySQL 會(huì)額外搜索包含 NULL 值的行。

  --?為了模擬這個(gè)場(chǎng)景，我又新增了一個(gè) user_test 表。
  mysql>?CREATE?TABLE?`user_test`?(
  ????->?`id`?INT?(11)?NOT?NULL?AUTO_INCREMENT,
  ????->?`name`?VARCHAR?(20)?DEFAULT?NULL?COMMENT?"用戶(hù)名",
  ????->?PRIMARY?KEY?(`id`),?
  ????->?KEY?`idx_name`?(`name`)
  ????->?)?ENGINE?=?INNODB?DEFAULT?CHARSET?=?utf8mb4?COMMENT?=?"用戶(hù)測(cè)試表";
  Query?OK,?0?rows?affected?(0.01?sec)
  
  mysql>?EXPLAIN?SELECT?id?FROM?user_test?WHERE?name?=?'riemann'?OR?name?IS?NULL;?
  +----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
  |?id?|?select_type?|?table?????|?partitions?|?type????????|?possible_keys?|?key??????|?key_len?|?ref???|?rows?|?filtered?|?Extra????????????????????|
  +----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
  |??1?|?SIMPLE??????|?user_test?|?NULL???????|?ref_or_null?|?idx_name??????|?idx_name?|?83??????|?const?|????2?|???100.00?|?Using?where;?Using?index?|
  +----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+

• index_merge：使用了索引合并優(yōu)化方法，查詢(xún)使用了兩個(gè)以上的索引。


  --?下邊示例中同時(shí)使用到主鍵 id 和字段 user_id 的索引。
  mysql>?EXPLAIN?SELECT?*?FROM?user_robot_relate?WHERE?id?>?1?AND?user_id?=?2;?
  +----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
  |?id?|?select_type?|?table?????????????|?partitions?|?type????????|?possible_keys???????|?key?????????????????|?key_len?|?ref??|?rows?|?filtered?|?Extra?????????????????????????????????????????????|
  +----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
  |??1?|?SIMPLE??????|?user_robot_relate?|?NULL???????|?index_merge?|?PRIMARY,idx_user_id?|?idx_user_id,PRIMARY?|?8,4?????|?NULL?|????1?|???100.00?|?Using?intersect(idx_user_id,PRIMARY);?Using?where?|
  +----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
  

• unique_subquery：替換下面的 IN 子查詢(xún)，子查詢(xún)返回不重復(fù)的集合。


  value?IN?(SELECT?primary_key?FROM?single_table?WHERE?some_expr)
  

• index_subquery：區(qū)別于 unique_subquery，用于非唯一索引，可以返回重復(fù)值。


  value?IN?(SELECT?key_column?FROM?single_table?WHERE?some_expr)
  

• range：使用索引選擇行，僅檢索給定范圍內(nèi)的行。簡(jiǎn)單點(diǎn)說(shuō)就是針對(duì)一個(gè)有索引的字段，給定范圍檢索數(shù)據(jù)。在 where 語(yǔ)句中使用 bettween…and、<、>、<=、in 等條件查詢(xún) type 都是 range。

  -- user_robot_relate 表中 id 為唯一主鍵，name 普通字段未建索引。
  mysql>?EXPLAIN?SELECT?*?FROM?user_robot_relate?WHERE?id?BETWEEN?2?AND?3;
  +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  |?id?|?select_type?|?table?????????????|?partitions?|?type??|?possible_keys?|?key?????|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
  +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  |??1?|?SIMPLE??????|?user_robot_relate?|?NULL???????|?range?|?PRIMARY???????|?PRIMARY?|?4???????|?NULL?|????2?|???100.00?|?Using?where?|
  +----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  

  從結(jié)果中看到只有對(duì)設(shè)置了索引的字段，做范圍檢索 type 才是 range。

  mysql>?EXPLAIN?SELECT?*?FROM?user?WHERE?name?BETWEEN?2?AND?3;
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  |?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  |??1?|?SIMPLE??????|?user??|?NULL???????|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????2?|????50.00?|?Using?where?|
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  

• index：Index 與 ALL 其實(shí)都是讀全表，區(qū)別在于 index 是遍歷索引樹(shù)讀取，而 ALL 是從硬盤(pán)中讀取。


  -- id 為主鍵，不帶 where 條件全表查詢(xún)，type 結(jié)果為 index。
  mysql>?EXPLAIN?SELECT?id?FROM?robot;
  +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  |?id?|?select_type?|?table?|?partitions?|?type??|?possible_keys?|?key?????|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
  +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  |??1?|?SIMPLE??????|?robot?|?NULL???????|?index?|?NULL??????????|?PRIMARY?|?4???????|?NULL?|????3?|???100.00?|?Using?index?|
  +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  

• ALL：將遍歷全表以找到匹配的行，性能最差。


  mysql>?EXPLAIN?SELECT?*?FROM?robot;
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
  |?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra?|
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
  |??1?|?SIMPLE??????|?robot?|?NULL???????|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????3?|???100.00?|?NULL??|
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
  

3.6 possible_keys

possible_keys：表示在 MySQL 中通過(guò)哪些索引，能讓我們?cè)诒碇姓业较胍挠涗?，一旦查?xún)涉及到的某個(gè)字段上存在索引，則索引將被列出，但這個(gè)索引并不一定會(huì)是最終查詢(xún)數(shù)據(jù)時(shí)所被用到的索引。具體請(qǐng)參考上邊的例子。

3.7 key

key：區(qū)別于 possible_keys，key 是查詢(xún)中實(shí)際使用到的索引，若沒(méi)有使用索引，顯示為 NULL。具體請(qǐng)參考上邊的例子。

當(dāng) type 為 index_merge 時(shí)，可能會(huì)顯示多個(gè)索引。

3.8 key_len

key_len：表示查詢(xún)用到的索引長(zhǎng)度（字節(jié)數(shù)），原則上長(zhǎng)度越短越好 。

• 單列索引，那么需要將整個(gè)索引長(zhǎng)度算進(jìn)去；

• 多列索引，不是所有列都能用到，需要計(jì)算查詢(xún)中實(shí)際用到的列。

注：key_len 只計(jì)算 where 條件中用到的索引長(zhǎng)度，而排序和分組即便是用到了索引，也不會(huì)計(jì)算到 key_len 中。

3.9 ref

列出是通過(guò)常量（const），還是某個(gè)表的某個(gè)字段（如果是 join）來(lái)過(guò)濾（通過(guò) key）的。

3.10 rows

rows：以表的統(tǒng)計(jì)信息和索引使用情況，估算要找到我們所需的記錄，需要讀取的行數(shù)。

這是評(píng)估 SQL 性能的一個(gè)比較重要的數(shù)據(jù)，MySQL 需要掃描的行數(shù)，很直觀的顯示 SQL 性能的好壞，一般情況下 rows 值越小越好。

3.11 filtered

filtered 這個(gè)是一個(gè)百分比的值，表里符合條件的記錄數(shù)的百分比。簡(jiǎn)單點(diǎn)說(shuō)，這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在經(jīng)過(guò)過(guò)濾后，剩下滿(mǎn)足條件的記錄數(shù)量的比例。

在 MySQL.5.7 版本以前想要顯示 filtered 需要使用 explain extended 命令。MySQL.5.7 后，默認(rèn) explain 直接顯示 partitions 和 filtered 的信息。

3.12 Extra

Extra ：不適合在其他列中顯示的信息，Explain 中的很多額外的信息會(huì)在 Extra 字段顯示。

3.12.1 Using index

Using index：我們?cè)谙鄳?yīng)的 select 操作中使用了覆蓋索引，通俗一點(diǎn)講就是查詢(xún)的列被索引覆蓋，使用到覆蓋索引查詢(xún)速度會(huì)非常快，SQL 優(yōu)化中理想的狀態(tài)。

什么又是覆蓋索引?

一條 SQL 只需要通過(guò)索引就可以返回，我們所需要查詢(xún)的數(shù)據(jù)（一個(gè)或幾個(gè)字段），而不必通過(guò)二級(jí)索引，查到主鍵之后再通過(guò)主鍵查詢(xún)整行數(shù)據(jù)（SELECT * ）。

id 為 user 表的主鍵

mysql>?EXPLAIN?SELECT?id?FROM?user;?
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table?|?partitions?|?type??|?possible_keys?|?key?????|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?user??|?NULL???????|?index?|?NULL??????????|?PRIMARY?|?4???????|?NULL?|????2?|???100.00?|?Using?index?|
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

注意：想要使用到覆蓋索引，我們?cè)?select 時(shí)只取出需要的字段，不可 SELECT *，而且該字段建了索引。

mysql>?EXPLAIN?SELECT?*?FROM?user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra?|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|??1?|?SIMPLE??????|?user??|?NULL???????|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????2?|???100.00?|?NULL??|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3.12.2 Using where

Using where：查詢(xún)時(shí)未找到可用的索引，進(jìn)而通過(guò) where 條件過(guò)濾獲取所需數(shù)據(jù)，但要注意的是并不是所有帶 where 語(yǔ)句的查詢(xún)都會(huì)顯示 Using where。

下邊示例 name 并未用到索引，type 為 ALL，即 MySQL 通過(guò)全表掃描后再按 where 條件篩選數(shù)據(jù)。

mysql>?EXPLAIN?SELECT?name?FROM?user?WHERE?name?=?'riemann';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?user??|?NULL???????|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????2?|????50.00?|?Using?where?|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

3.12.3 Using temporary

Using temporary：表示查詢(xún)后結(jié)果需要使用臨時(shí)表來(lái)存儲(chǔ)，一般在排序或者分組查詢(xún)時(shí)用到。

mysql>?EXPLAIN?SELECT?name?FROM?user?WHERE?id?IN?(1,?2)?GROUP?BY?name;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
|?id?|?select_type?|?table?|?partitions?|?type??|?possible_keys?|?key?????|?key_len?|?ref??|?rows?|?filtered?|?Extra????????????????????????????????????????|
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
|??1?|?SIMPLE??????|?user??|?NULL???????|?range?|?PRIMARY???????|?PRIMARY?|?4???????|?NULL?|????2?|???100.00?|?Using?where;?Using?temporary;?Using?filesort?|
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+

3.12.4 Using filesort

Using filesort：表示無(wú)法利用索引完成的排序操作，也就是 ORDER BY 的字段沒(méi)有索引，通常這樣的 SQL 都是需要優(yōu)化的。

mysql>?EXPLAIN?SELECT?id?FROM?user?ORDER?BY?name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra??????????|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|??1?|?SIMPLE??????|?user??|?NULL???????|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|????2?|???100.00?|?Using?filesort?|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

如果 ORDER BY 字段有索引就會(huì)用到覆蓋索引，相比執(zhí)行速度快很多。

mysql>?EXPLAIN?SELECT?id?FROM?user?ORDER?BY?id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|?id?|?select_type?|?table?|?partitions?|?type??|?possible_keys?|?key?????|?key_len?|?ref??|?rows?|?filtered?|?Extra???????|
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|??1?|?SIMPLE??????|?user??|?NULL???????|?index?|?NULL??????????|?PRIMARY?|?4???????|?NULL?|????2?|???100.00?|?Using?index?|
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

3.12.5 Using join buffer

Using join buffer：在我們聯(lián)表查詢(xún)的時(shí)候，如果表的連接條件沒(méi)有用到索引，需要有一個(gè)連接緩沖區(qū)來(lái)存儲(chǔ)中間結(jié)果。

mysql>?EXPLAIN?SELECT?u.name?FROM?user?u,?user_test?t?WHERE?u.name?=?t.name;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
|?id?|?select_type?|?table?|?partitions?|?type??|?possible_keys?|?key??????|?key_len?|?ref??|?rows?|?filtered?|?Extra??????????????????????????????????????????????|
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
|??1?|?SIMPLE??????|?t?????|?NULL???????|?index?|?idx_name??????|?idx_name?|?83??????|?NULL?|????1?|???100.00?|?Using?index????????????????????????????????????????|
|??1?|?SIMPLE??????|?u?????|?NULL???????|?ALL???|?NULL??????????|?NULL?????|?NULL????|?NULL?|????2?|????50.00?|?Using?where;?Using?join?buffer?(Block?Nested?Loop)?|
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+

3.12.6 Impossible where

Impossible where：表示在我們用不太正確的 where 語(yǔ)句，導(dǎo)致沒(méi)有符合條件的行。

mysql>?EXPLAIN?SELECT?name?FROM?user?WHERE?1=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra????????????|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|??1?|?SIMPLE??????|?NULL??|?NULL???????|?NULL?|?NULL??????????|?NULL?|?NULL????|?NULL?|?NULL?|?????NULL?|?Impossible?WHERE?|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

3.12.7 No tables used

No tables used：我們的查詢(xún)語(yǔ)句中沒(méi)有 FROM 子句，或者有 FROM DUAL 子句。

mysql>?EXPLAIN?SELECT?now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|?id?|?select_type?|?table?|?partitions?|?type?|?possible_keys?|?key??|?key_len?|?ref??|?rows?|?filtered?|?Extra??????????|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|??1?|?SIMPLE??????|?NULL??|?NULL???????|?NULL?|?NULL??????????|?NULL?|?NULL????|?NULL?|?NULL?|?????NULL?|?No?tables?used?|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

---

歡迎大家關(guān)注我的公眾號(hào)【老周聊架構(gòu)】，Java后端主流技術(shù)棧的原理、源碼分析、架構(gòu)以及各種互聯(lián)網(wǎng)高并發(fā)、高性能、高可用的解決方案。