【MySQL】透過閱讀執行計劃來確認索引效果

https://www.flickr.com/photos/8058098@N07/2718571627/

雖然建立了索引，但它真的有生效嗎？如果沒有生效，那麼平常單看查詢指令就隨心所欲地為欄位建立索引，恐怕反而會拖慢寫入資料的效率。

本文將介紹執行計劃，確認 MySQL 查詢時，是否有用到了我們建立的索引。

一、範例資料介紹

以下是本文的範例資料。

科系表，有編號與名稱，共 2 個欄位。

學生表，有編號、名字與科系編號，共 3 個欄位。

針對學生表，筆者還針對科系編號這個欄位建立索引。

CREATE INDEX `idx_student_dept` ON `student`(`dept_id`);

二、執行計劃

MySQL 在執行查詢前，會預測各種查詢方式的成本，最後選擇它認為適合的做法。

本節讓我們看看執行計劃的內容。想取得 MySQL 的執行計劃，只要在寫好的查詢指令前加上 EXPLAIN 關鍵字即可。

（一）案例一：主鍵查詢

以下是在科系表透過編號（主鍵）來查詢資料。

EXPLAIN
SELECT *
FROM `dept`
WHERE `id` = 3;

此時會出現一個表格，可看到當中有許多欄位，節錄部份內容如下。

type	possible_keys	key	filtered
const	PRIMARY	PRIMARY	100.00

（二）執行計劃概觀

在執行計劃中，能看到表格有許多欄位。

「Type」的意思是查詢類型，用來給執行效率區分「等級」（就像演算法有分時間複雜度那樣）。查詢類型由效率快到慢排序如下。

1. NULL：不必查詢表。如「SELECT (1 + 2);」。
2. system：查詢系統表。
3. const：用了主鍵或唯一索引來查詢。有「常數」（constant）時間的意味在。
4. ref：用了一般索引來查詢。
5. range：用了欄位值的範圍來查詢。
6. index：有使用索引，但仍需掃描底層樹狀結構中的所有內容。
7. ALL：全表掃描。

「Possible_keys」的意思，是 MySQL 覺得可能會用到的索引，而「key」是實際使用的索引。

「Filtered」的意思是查詢結果佔 MySQL 讀取資料量的比例（單位為百分比），越高越好。不過這是個估計值。

我們來印證一下查詢計劃的內容。由於 id 屬於主鍵欄位，正好也具有唯一性，因此 Type 為「const」。而 key 為「PRIMARY」，這是主鍵索引的名稱。

在主鍵索引中，id 值只能對應到 1 筆資料，查詢結果當然也只有 1 筆。因此 Filtered 為 1 / 1 = 100（%）。

接下來的小節，將繼續閱讀不同情況的執行計劃。

三、案例二：全表掃描

以下是在科系表透過名稱來查詢資料。

EXPLAIN
SELECT *
FROM `dept`
WHERE `name` = "資管";

節錄部份結果如下。

table	type	possible_keys	key	filtered
dept	ALL	NULL	NULL	33.33

由於我們並未針對 name 欄位建立索引，MySQL 便以全表掃描的方式進行，故 type 為「ALL」。

整個資料表共 3 筆資料，而回傳結果為 1 筆，故 Filtered 為 1 / 3 = 33.33（%）。

四、案例三：聯表查詢

以下是用科系名稱查詢學生。

EXPLAIN
SELECT *
FROM `student`
JOIN `dept` ON `student`.`dept_id` = `dept`.`id`
WHERE `dept`.`name` IN ("資管", "企管");

節錄部份結果如下。

id	table	type	key
1	dept	ALL	NULL
1	student	ref	idx_student_dept

執行計劃中還有一個「id」欄位，它的意思是 MySQL 執行這組查詢的步驟。在執行計劃中，id 的值越大，代表該步驟越先執行。若值相同，則由上往下執行。

從這裡可以解讀出，MySQL 會先在科系表進行全表掃描，找出名稱為「資管」和「企管」的資料。接著拿取它們的 id，到學生表查詢符合的資料，且這一步用到科系編號的索引。

上一篇：【MySQL】配置慢查詢日誌來紀錄耗時的查詢

下一篇：