Day 5 - MySQL 如何檢查查詢效能？ (Optimizer & Explain)

17th鐵人賽

vicxu

2025-09-05 09:16:15

144 瀏覽

分享至

隨著業務變得複雜，一張 Table 的 Index 也會變多，此時 MySQL 會用哪個 Index 搜尋資料就成一個謎，例如 orders Table 有 (user_id, status) & (user_id, created_time) 兩個 Index，執行 SELECT * FROM orders WHERE user_id = 1 AND status BETWEEN 1 AND 3 AND created_time > NOW() 時究竟會用哪個 Index 呢？

MySQL 是怎麼決定要用哪個 Index 查資料？

執行 SQL 時，MySQL 會將語法解析成 Abstract Syntax Tree 後交由 Optimizer 制定 Query Plan 並決定要用哪個 Index，而 Optimizer 決策因素有：

Index 的篩選能力
總 I/O 次數
CPU 消耗

首先 Index 篩選能力：

MySQL 會統計每個 Index 的 cardinality (基數)，也就是該欄位中有多少種 Value，例如 orders 表中有 (status) & (user_id) Index，status 只有處理中，失敗，完成三種狀態，cardinality 就是 3，而 user_id 則可能有 1~10w 種，在相同查詢條件下，cardinality 越高篩選能力越好，所以說
SELECT * FROM orders WHERE user_id = 1 AND status = 完成 的情況下，選用 (user_id) Index 效果更佳。

但只用 cardinality 會有誤判，因為真實資料會傾斜，例如處理中只是中間狀態，資料通常很少，因此 MySQL 會加上 histogram 統計出不同 Value 的佔比，假設在 user_id = 1 是一個大戶的情況下執行 SELECT * FROM orders WHERE user_id = 1 AND status = 處理中 ，選用 (status) Index 篩選能力反而更好。

總 I/O 次數：

越少的 I/O 查詢效能一定越快，而 I/O 次數會受到 Index Tree 高度和是否要回 Clustered Index 影響，Index Tree 越高，Traversal 時就要載入越多節點 (Page) 到記憶體。

假設 orders Table 有 (user_id) & (user_id, status) 並執行下面查詢
`SELECT * FROM orders WHERE user_id = 123 AND status = 'PENDING'
依照篩選力 (user_id, status) 能篩選掉更多資料，但因為 (user_id, status) 一筆 Index Key 資料較多，等於一個 Page 能放的 Index Key 筆數變少，樹就會變高，因此在 I/O 次數上可能 (user_id) 更好。

不過更常見的情況是 orders Table (user_id) & (created_time, user_id) 並執行下面查詢
SELECT user_id, created_time FROM orders WHERE user_id = 123 AND created_time > NOW()
雖然 (user_id) 能篩選掉更多資料，但因為 SELECT 需要 created_time 的欄位，使用 (user_id) Index 還需要回 Clustered Index 查到完整資料，但 (created_time, user_id) Index Tree 中就有 user_id & created_time 的欄位資料，不用回 Clustered Index，在 I/O 次數上 (created_time, user_id) 會更好。

最後是 CPU 消耗：

從 Index Tree 過濾完資料後，若資料還不精準，需要額外在記憶體中過濾，也就是說篩選力越好，對 CPU 的消耗就越小，但除了過濾資料 Order By 也可能消耗不少的 CPU。

例如 orders Table 有 (created_time) Index 並執行
SELECT * FROM orders WHERE created_time > ? ORDER BY id
如果不用 created_time Index 先過濾資料，就必須把所有資料放進記憶體中一筆筆過濾，CPU 消耗肯定不小，但如果 orders 表不大且 created_time > ? 過濾不多資料時，由於 ORDER BY id 用 created_time index 過濾後，還要在記憶體中用 id 額外排序，此時 MySQL 會覺得用 primary key Index Tree CPU 消耗可能比較小，因為用 primary key Index Tree 把資料放到記憶體時，資料已是依照 id 排序，一筆筆比對只要 O(N)，額外排序還要 O(nLogN)，因此可能選用 primary key。

Optimizer 會透過 Index 統計數據依照上面三個因素計算出一個 cost 分數，最後選用 cost 最低的 Index Tree。

但我看不到統計數據，也不知道怎麼計算 cost，要怎知 Optimizer 實際用哪個 Index Tree？

MySQL 有 EXPLAIN 功能，可以在不實際執行 Query 的情況下，查詢 Optimizer 制定的 Query Plan 細節，Explain 出來的內容有：

prossible_keys ＆ key ：Index 內容

prossible_keys ：query 中可以用的 inedx
key：Optimizer 選中的 index

ref： 與 index key 比較的資料類別

const：使用常數比較 (e.g id = 5)
column name：使用其他表的 column 比較 (e.g o.customer_id = c.customer_id)
func：比較的 index key 有經過 function 處理 (e.g UPPER(name) = 'JOHN')
NULL：沒有等於比較 (e.g id > 5 或全表掃描)

type ：Index 的查詢方式

system：Table 裡只有一筆 row 或是空的
const：只查詢一筆
eq_ref：查詢條件來自其他表的唯一值，例如 JOIN
ref：= 查詢條件非唯一值
range：範圍查詢 (e.g BETWEEN, >, <, IN …)
index : FULL Index Scan，掃描整個 Secondary Index Tree
ALL : FULL Table Scan，掃描整個 Clustered Index Tree

效能排序：system > const > eq_ref > ref > range > index > ALL

extra : 除了 Index Tree 搜尋外的其他操作

using index：只用到 Secondary Index Tree，不需回 Clustered Index Tree 拿完整資料
using filesort：無法使用 index 排序，需要花 CPU 在記憶體中排序
using where : 表示 index tree 無法過濾完資料，還要在記憶體中過濾
using temporary：是否有用臨時表，例如 Union 或 Sub Query，會額外消耗記憶體或硬碟空間
using MMR : 使用 Multi-Range Read (MMR) 優化，通常用在 Range 查詢。
using intersect/union/sort_union：使用 index merge 優化，通常用在不同欄位的 OR 條件
using index condition : 使用 composited index 的 index condition pushdown (ICP) 優化

MMR 優化：
用 secondary index range 查詢且需要回 clustered index 找完整資料時，由於 secondary index 排序跟 primary key 排序不一樣，若依照 secondary index 排序回 clustered index 找資料會造成隨機 I/O，而 MMR 優化是將 secondary index 找到的資料 primary key 暫存到記憶體，排序後批次去 clustered index 查詢，將隨機 I/O 變成順序 I/O。

Index Merge 優化：
使用 OR 時，例如 SELECT * FROM t WHERe a = 1 OR b = 2，會分別查詢 Index Tree 並把結果 Merge 起來並移除重複 primary key 資料，再用 Merge 的結果去 Clustered Index 找完整資料，降低去 Clustered Index 查找數量。

ICP 優化：
使用 index (a, b, c) 執行 SELECT * FROM t WHERE a = ? AND b > ? AND c = ? 時，由於 b 是範圍查詢 c 欄位無法用 binary search，此時因為 SELECT * 要回 clustered index 找完整資料，就會觸發 ICP，ICP 會在 index (a, b, c) tree 中一筆筆比對 c = ? 的資料，藉此過濾掉更多資料，減少要回 clustered index 查找的數量，降低 I/O 次數。

rows & filtered : Optimizer 根據 Index Tree 統計資料估數的查詢數量

rows：預估會 scan 多少筆數
filtered：預估 scan 出來的 row 有多少比例完全匹配查詢條件，filtered 率越高越好

key_len ：預估使用到的 Index Key 長度，可用來判斷 Composite Index 是否所有欄位都用到

我有辦法看到 query 實際執行的 CPU 和時間嗎？

EXPLAIN 主要是看 Optimizer 透過統計資料制定的 Query Plan，如果你想看到實際 Query 的執行時間和 cost 分數，可以執行 EXPLAIN ANALYZE。

EXPLAIN ANALYZE 會實際執行 Query 並輸出：

EXPLAIN ANALYZE SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.country = 'US';

-> Nested loop inner join  (cost=1000.25 rows=5000) 
   (actual time=0.020..10.532 rows=100000 loops=1)
    -> Filter: (u.country = 'US')  
       (cost=500.12 rows=5000) 
       (actual time=0.010..2.354 rows=100000 loops=1)
        -> Table scan on u  
           (cost=500.12 rows=100000) 
           (actual time=0.009..1.500 rows=100000 loops=1)
    -> Index lookup on o (user_id=u.id)  
       (cost=0.20 rows=1) 
       (actual time=0.000..0.050 rows=1 loops=100000)

執行計劃節點，也就是 Query 的步驟，以上面為例可以看到有四個步驟：
- users u Table 的 Full Table Scan
- Filter (u.country = 'US') 資料
- orders 表使用 Index 查詢 user_id=u.id
- 將兩批資料使用 Nested loop inner join 關聯起來
Optimizer 預估資訊 (cost=1000.25 rows=5000)，cost 是成本分數，cost 越高可能會消耗越多 CPU ，rows 是預計 scan 筆數
實際執行資訊 (actual time=0.020..10.532 rows=100000 loops=1)
- actual time 時間單位為 ms，讀到第一筆花 0.02ms，讀完最後一筆花 10ms
- rows 實際總共 scan 的筆數
- loops 這個節點被執行了幾次

如果 EXPLAIN ANALYZE 結果發現，實際執行跟 Optimizer 預估落差很大怎麼辦？

由於 Optimizer 使用的統計數據是抽樣調查，難免會有誤差，此時可透過
ANALYZE TABLE your_table; 重新抽樣，若發現光靠 cardinality 會不準，可透過
`ANALYZE TABLE your_table UPDATE HISTOGRAM ON column_name WITH 100 BUCKETS;
建立 histogram 統計數據。

如果還是不準，最後可試 ALTER TABLE your_table STATS_SAMPLE_PAGES=100; 調整抽樣數量，不過要注意 MySQL 執行抽樣統計是會消耗 CPU 的，尤其建立 Histogram 時是需要逐筆計算的，可能造成 Full Table Scan。

About Me
歡迎大家追蹤我的 Thread，平常會在上面分享技術文章
https://www.threads.com/@chill.vic.22