查詢最佳化

找出慢的查詢，再看它怎麼執行

上一課從設計著手：建立索引、選擇資料型別。但索引建了，查詢不一定用得上。查詢最佳化的流程是：

1. 找出慢的查詢：開啟慢查詢日誌，記錄執行超過某個秒數的查詢
2. 看它怎麼執行：在查詢前面加上 EXPLAIN，MySQL 會說明它打算怎麼找資料（執行計畫），但不真的執行
3. 調整：建立或修改索引、改寫查詢，再用 EXPLAIN 確認

EXPLAIN 可以放在 SELECT、INSERT、UPDATE、DELETE 前面：

EXPLAIN SELECT * FROM employee WHERE name = 'Ben Smith';

關於播放器裡的 EXPLAIN：本課的直譯器以簡化的規則模擬 MySQL 優化器。type、possible_keys、key 的判斷接近 MySQL 8.0， 但 rows（估計要讀幾列）與 filtered（估計條件過濾後剩下的百分比）是估計值，本來就依伺服器的統計資訊而定，播放器的數字只是示意。 真實的優化器還會考慮資料量與成本，表格很小時可能選擇和大表格不同的方式。 學完之後，請在自己的 MySQL 8 上建立同樣的表格執行，比較結果。本課有幾處直接列出在真的 MySQL 8.0.44 上執行的結果，會特別標明。

EXPLAIN 的欄位

EXPLAIN 的結果每一列代表查詢裡的一張表格（JOIN 兩張表格就有兩列），重要的欄位：

type 由好到差：

看到 ALL，而且 rows 很大，通常就是要改善的地方。Extra 常見的值：

原始課程的例子：ename_did

原始課程用這個連接查詢示範 EXPLAIN：

EXPLAIN
SELECT e.name, d.name
FROM employee e, department d
WHERE e.departmentID = d.departmentID;

結果有兩列：e 是 ALL（每位員工都要讀），d 是 eq_ref（對每位員工，用部門的主鍵找到剛好一個部門）。 這其實已經是合理的計畫：員工本來就要全部列出，部門則每次只找一列。下一個範例會在播放器裡看到這兩列。

課程接著建立複合索引，再 EXPLAIN 一次：

CREATE INDEX ename_did ON employee (name, departmentID);

在真的 MySQL 8.0.44 上，建立索引前後的結果是（只列出重要的欄位）：

建立後 e 改成 index，用的是 ename_did。注意這不是「用索引快速找到某位員工」，查詢沒有任何篩選員工的條件， 仍然要讀完所有員工；變快的原因是 e 需要的兩個欄位 name、departmentID 都在 ename_did 裡， MySQL 改讀比較小的索引（覆蓋索引，Using index），不必讀整張表格。

驗證最左前綴：看 key_len

上一課的 student 表格有複合索引 idx_dept_grade (dept, grade)，dept 是 CHAR(4) NOT NULL、grade 是 TINYINT NOT NULL。 在真的 MySQL 8.0.44 上 EXPLAIN 的結果（12 位學生，只列出重要的欄位）：

• key_len 16 是 dept 的長度（CHAR(4)，utf8mb4 每字最多 4 位元組），17 是再加上 grade（TINYINT 1 位元組）， 所以從 key_len 可以看出複合索引用到了幾個欄位
• 只給 grade 時 key 是 NULL，印證了最左前綴規則
• 最後一列很有意思：只需要 dept、grade 兩個欄位時，雖然不能在索引裡「查找」，MySQL 還是選擇讀完整個索引（index）， 因為索引比表格小

讓索引失效的寫法

索引存的是「欄位原本的值，排好序」。條件的寫法如果讓 MySQL 無法利用這個順序，索引就用不上：

1. 對欄位套用函式或運算

WHERE UPPER(name) = 'BEN SMITH'：索引裡存的是 name，不是 UPPER(name)，MySQL 只能逐列算出 UPPER 再比較。 同理 WHERE YEAR(workdate) = 2003、WHERE employeeID + 1 = 7514 都會讓索引失效。改寫的方法是讓欄位保持原樣，把運算移到另一邊：

-- 失效：YEAR(workdate) = 2003
WHERE workdate >= '2003-01-01' AND workdate < '2004-01-01'   -- workdate 有索引時就用得上

MySQL 預設的定序不分大小寫，所以 WHERE name = 'BEN SMITH' 本來就找得到 Ben Smith，根本不需要 UPPER。

2. 開頭是 % 的 LIKE

LIKE 'B%' 是「B 開頭」，在排好序的索引裡是一段連續的範圍（range）；LIKE '%Smith' 是「Smith 結尾」， 可能散落在索引的任何位置，只能全部讀過。需要找文章中間的字詞，請用全文索引（第 09 課）。

3. 型別不一致

client 的 contactNumber 是 CHAR(12)。替它建立索引 idx_phone 後，在真的 MySQL 8.0.44 上比較兩種寫法：

字串欄位和數字比較時，MySQL 要把每一列的字串轉成數字再比（'095551234'、'95551234 ' 都等於 95551234）， 索引的排序就派不上用場。執行 SHOW WARNINGS 會看到： Cannot use ref access on index 'idx_phone' due to type or collation conversion on field 'contactNumber'。 反過來，整數欄位和字串常數比較（WHERE employeeID = '7513'）仍然可以用索引，因為只要把常數轉換一次。 原則是：常數的型別和欄位一致。

型別不一致的例子播放器會顯示成 ref，和真實 MySQL 不同，請以上表為準。

BENCHMARK、EXPLAIN FORMAT=TREE 與 EXPLAIN ANALYZE

BENCHMARK(次數, 運算式) 把一個運算式重複計算很多次，結果一律傳回 0，重點是 mysql 用戶端顯示的執行時間：

SELECT BENCHMARK(1000000, 6*9);      -- 6*9 算 100 萬次，看 (0.01 sec) 這類時間

它適合比較兩個運算式或函式的速度，例如兩種字串處理寫法。原始課程把整個 SELECT 寫成字串放進 BENCHMARK： BENCHMARK(10000000, 'select employee.name … ')，這只是重複計算一個字串常數，並不會執行那個查詢，量不到查詢的速度。 要比較查詢，直接執行它看時間、用下面的 EXPLAIN ANALYZE，或看慢查詢日誌。 播放器的直譯器不實際計時，BENCHMARK 傳回 0，時間一律顯示 0.00 sec。

EXPLAIN FORMAT=TREE（MySQL 8.0.16 起）用樹狀文字顯示執行計畫，每一步附上優化器估計的成本（cost）與列數。 EXPLAIN ANALYZE（MySQL 8.0.18 起）會真的執行查詢，在估計值旁邊加上實際花費的時間（毫秒）、實際列數與執行次數（loops）， 可以檢查估計準不準。因為會真的執行，慢的查詢也會一樣慢。

下面是在一台測試用的 MySQL 8.0.44 上，employee 有 idx_name 索引時的輸出（成本與時間每台機器都不同；敘述以 \G 結尾時，mysql 用戶端把結果改成一欄一行的直式顯示，長的文字比較好讀）：

mysql> EXPLAIN FORMAT=TREE SELECT * FROM employee WHERE name = 'Ben Smith'\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on employee using idx_name (name='Ben Smith')  (cost=0.35 rows=1)

mysql> EXPLAIN ANALYZE SELECT * FROM employee WHERE name = 'Ben Smith'\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on employee using idx_name (name='Ben Smith')  (cost=0.35 rows=1) (actual time=0.0196..0.0207 rows=1 loops=1)

mysql> EXPLAIN FORMAT=TREE SELECT e.name, d.name
    -> FROM employee e JOIN department d ON e.departmentID = d.departmentID\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=2.05 rows=4)
    -> Table scan on e  (cost=0.65 rows=4)
    -> Single-row index lookup on d using PRIMARY (departmentID=e.departmentID)  (cost=0.275 rows=1)

樹狀格式從最內層讀起：先 Table scan on e（全表掃描員工），對每一位員工 Single-row index lookup on d（用主鍵找部門，就是 eq_ref）， 再由 Nested loop inner join 把兩邊接起來。播放器可以執行這兩種寫法，但只輸出簡化的近似結果，請在真的伺服器上觀察。

慢查詢日誌

EXPLAIN 要先知道「哪個查詢慢」。網站上線後，查詢來自程式的各個角落，慢查詢日誌（slow query log） 會把執行時間超過 long_query_time 秒的查詢記到一個檔案裡，這些查詢就是最值得最佳化的對象。

原始課程使用的 log-slow-queries=檔名 選項是 MySQL 5.1 以前的寫法，已經移除，MySQL 8 改用上表的變數。 Windows 的 MySQL 安裝程式通常預設就開啟慢查詢日誌；Ubuntu 的 mysql-server 套件預設關閉。下面的導覽在 Ubuntu 上操作。

重點整理

流程：慢查詢日誌找出慢的查詢 → EXPLAIN 看計畫 → 建立索引或改寫 → 再 EXPLAIN 確認。 下一課介紹檢視表：把常用的查詢存成一個名稱，像表格一樣使用。