共計 5412 個字符，預計需要花費 14 分鐘才能閱讀完成。

本篇內容介紹了“MySQL 之 Explain 怎么使用”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

Explain 工具介紹

使用 Explain 關鍵字可以模擬優化器執行 SQL 語句，分析你的查詢語句或是結構的性能瓶頸。在 select 語句之前增加 explain 關鍵字，Mysql 會在查詢上設置一個標記，執行查詢會返回執行計劃的信息，而不是執行這條 SQL
注意：如果 from 中包含子查詢，仍會執行該子查詢，將結構放入臨時表中

示例代碼

DROP TABLE IF EXISTS `actor`; 
CREATE TABLE `actor` ( `id` int(11) NOT NULL,
 `name` varchar(45) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1, a , 2017-12-22 15:27:18), (2, b , 2017-12-22 15:27:18), (3, c , 2017-12-22 15:27:18 
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film` (`id`, `name`) VALUES (3, film0),(1, film1),(2, film2 
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` ( `id` int(11) NOT NULL,
 `film_id` int(11) NOT NULL,
 `actor_id` int(11) NOT NULL,
 `remark` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

explain select * from actor;

在查詢中的每個表都會輸出一行，如果有兩個表通過 join 連接查詢，那么會輸出兩行

explain 兩個變種 explain extended

會在 explain 的基礎上額外提供一些查詢優化的信息（5.7 自動加上了，不需要這個命令了）緊隨其后通過 shouw warnings 命令可以得到優化后的查詢語句，從而看出優化器優化了什么。額外還有 filtered 列，是一個半分比的值。rows * filtered/100 可以估算出將要和 explain 中前一個表進行連接的行數。

explain partitions

相比 explain 多了個 partitions 字段(5.7 以后，explain 默認有了)，如果查詢時基于分區表的話，會顯示查詢將訪問的分區

explain 中的列

接下來我們將展示 explain 中每個列的信息。

id 列

id 列的編號是 select 的序列號，有幾個 select 就有幾個 id，并且 id 的順序是按 select 出現的順序增長的。
id 列值越大優先級越高，id 相同則是從上往下執行，id 為 NULL 最后執行

select_type 列

select_type 表示對應行是簡單還是復雜查詢

1.simple：簡單查詢，不包含子查詢和 union

2.primary：復雜查詢中最外層的 select

3.subquery：包含在 select 中的子查詢（不在 from 子句中）

4.derived：包含在 from 自居中的子查詢，MySQL 會將結果存放在一個臨時表中，也稱為派生表

mysql set session optimizer_switch= lsquo;derived_merge=off rsquo;; #關閉 mysql5.7 新特性對衍生表的合并優化

mysql explain select (select 1 from actor where id = 1) from (select * from film where

id = 1) der;

5.union：在 union 中的第二個和隨后的 select

table 列

這一列表示 explain 的一行正在訪問哪個表
當 from 子句中有子查詢時，table 列時格式，表示當前查詢依賴 id= N 的查詢，于是先執行 id= N 的查詢
當有 union 時，union result 的 table 列的值為 union,1,2，1 和 1 表示參與 union 的 select 行 id。

type 列

這一列表示關聯類型或訪問類型，即 MySQL 決定如何查找表中的行，查找數據行記錄的大概范圍。
依次從最優到最差分別為：system const eq_ref ref range index ALL

一般來說得保證查詢達到 range 級別，最大達到 ref

NULL：mysql 能夠在優化階段分解查詢語句，在執行階段用不著訪問表或索引。例如：在索引列中取最小值，可以單獨查找索引來完成，不需要在執行時訪問表

const,system：mysql 能對查詢的某部分進行優化并將其轉換成一個常量(可以看 show warnings 的結果)。用于 primary key 或 unique key 的所有列與常數比較時，所以表最多有一個匹配行，讀取一次，速度比較快。system 時 const 的特例，表里只有一條元組匹配時為 system。（意思就是 system 只有在數據找到一條的情況下出現）

 explain extended select * from (select * from film where id =1) tmp;

eq_ref：primary key 或 unique key 索引所在部分被連接使用，最多只會返回一條符合條件的記錄。這可能時在 const 之外最好的聯接類型了，簡單的 select 查詢不會出現這種 type

explain select * from film_actor left join film on film_actor.film_id = film.id;

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行(hang)。

1.  簡單  select  查詢，name 是普通索引（非唯一索引）mysql  explain select * from film where name =  film1 
關聯表查詢，idx_film_actor_id 是 film_id 和 actor_id 的聯合索引，這里使用到了 film_actor 的左邊前綴 film_id 部分。mysql  explain select film_id from film left join film_actor on film.id = film_actor.film_id;

range：范圍掃描通常出現在 in(),between, , , = 等操作中，使用一個索引來檢索給定范圍的行。

mysql  explain select * from actor where id   1;

index：掃描全索引就能拿到結果，一般是掃描某個二級索引，這種掃描不會從索引樹根節點開始快速查找，而是直接對二級索引的葉子節點遍歷和掃描，速度還是比較慢的，這種查詢一般為使用覆蓋索引，二級索引一般比較小，所以這種通常比 ALL 快一些。

explain select * from film;

為什么這里是走的 idx_name 這個索引？沒有走主鍵索引？
因為 MySQL 底層有一套判斷使用哪個索引的機制，這里是因為這張表一共就 2 個字段，而這 inx_name 索引樹就已經包含了 id 和 name，這時候選擇用主鍵和二級索引，它會優先選擇二級索引因為它的索引樹大小會小很多。
如果有很多其他字段，可能會選擇主鍵索引，因為你選擇了二級索引還得做回表操作

ALL：即全表掃描，掃描你的聚簇索引的所有葉子節點。通常情況下這需要增加索引來進行優化了

 explain select * from actor;

possible_keys 列

這一列顯示查詢可能使用哪些索引來查找。
explain 時可能出現 possible_keys 有列，而 key 顯示 NULL 的情況，這種情況是因為表中數據不多，mysql 認為索引對此查詢幫助不大，所以選擇全表掃描。
如果該列是 NULL，則沒有相關的索引。在這種情況下可以通過檢查 where 子句看是否可以創建一個適當的索引來提高查詢性能，然后用 explain 查看效果。

key 列

這一列顯示 MySQL 使用采用那個索引來優化對該表的訪問。
如果沒有使用索引，則該列是 NULL。如果想強制 MySQL 使用或忽視 possibe_keys 列中的索引，在查詢中使用 force index、ignore index。

key_len 列

這一列顯示了 MySQL 在索引里使用的字節數，通過這個值可以算出具體使用了索引中的哪些列。
舉例來說，film_actor 的聯合索引 idx_film_actor_id 由 film_id 和 actor_id 兩個 int 列組成，并且每個 int 是 4 字節。通過結果中的 key_len= 4 可推斷出查詢使用了第一個列：film_id 列來執行索引查找。

explain select * from film_actor where film_id = 2;

ref 列

這一列顯示了在 key 列記錄的索引中，表查找值所用到的列或常量，常見的有：const（常量），字段名(例：film.id)

rows 列

這一列是 MySQL 估計要讀取并檢測的行數，注意這個不是結果集里的行數

Extra 列

這一列展示的是額外信息，常見的重要值如下：

1.Using index：使用覆蓋索引（不會回表）

覆蓋索引定義：MySQL 執行計劃 explain 結果里的 key 有使用索引，如果 select 后面查詢的字段都可以從這個索引的樹中獲取，這種情況一般可以說用到了覆蓋索引，extra 里一般都有 using index；覆蓋索引一般針對的是輔助索引，整個查詢結構只通過輔助索引就能拿到結構，不需要通過輔助所以樹找到主鍵，再通過主鍵去主鍵索引樹里獲取其他字段值

explain select film_id from film_actor where film_id = 1;

2.Using where：使用 where 語句來處理結果，并且查詢的列未被索引覆蓋

explain select * from actor where name =  a

3.Using index condition：查詢的列不完全被索引覆蓋，where 條件中是一個前導列的范圍

explain select * from film_actor where film_id   1;

1.Using temporary：MySQL 需要創建一張臨時表來處理查詢。出現這種情況一般是要進行優化的，首先是想導用索引來優化。

4.1 actor.name 沒有索引，此時創建一張臨時表來 distinct

explain select distinct name from actor;

4.2 film.name 創建了 idx_name 索引，此時查詢時 extra 時 using index，沒有用臨時表

explain select distinct name from film;

5.Using filesort：將用外部排序而不是索引排序，數據較小時從內存排序，否則需要再磁盤完成排序。這種情況下一般也是要考慮使用索引來優化的

5.1 actor.name 未創建索引，會瀏覽 actor 整個表，保存排序關鍵字 name 和對應的 id，然后排序 name 并檢索行記錄

explain select * from actor order by name;

5.2 film.name 建立了 idx_name 索引, 此時查詢時 extra 是 using index

explain select * from film order by name;

6.Select tables optimized away：使用某些聚合函數（比如 max、min）來訪問存在索引的某個字段

explain select min(id) from film;

“MySQL 之 Explain 怎么使用”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！