MySQL 複合索引欄位順序實戰：等值優先，範圍在後

核心結論先行

建立複合索引時，將「等值比較（=、IN）的欄位放在最左邊，範圍比較（>、<、>=、<=、LIKE '%...'）的欄位放在右邊」，才能讓查詢完整使用索引（Index Full Scan / Ref），否則只能用到索引前綴（Range），效能大打折扣。

實驗場景

表 t1，欄位 a, b，已有索引 idx_b_a (b, a)。
資料 7 筆，含 NULL 值。

四種 WHERE 條件對比 EXPLAIN 結果

關鍵觀察

1. key_len=5 → 僅用到索引第一欄 b（int 4 bytes + NULL flag 1 byte）。
2. key_len=10 → 完整使用 (b, a) 兩欄。
3. type=ref（等值匹配）優於 type=range（範圍掃描）。
4. 當最左欄為等值時，第二欄無論等值或範圍，索引皆可完整使用；最左欄為範圍時，第二欄再好也無法使用。

原理：最左前綴原則

B+ 樹複合索引按 (b, a) 排序。查詢時：

• b = 2 可直接定位到單一葉子頁面區間，再在該區間內按 a 進一步篩選 → 全索引生效。
• b > 2 需掃描多個葉子頁面，a 在此時已無序，無法利用索引順序 → 只能用 b 做 Range Scan。

實務建議清單

1. 先數等值欄位數： WHERE 條件中有幾個等值欄位，複合索引前幾欄就放這些（順序不影響）。
2. 再放範圍欄位： 最多「一個」範圍欄位能用到索引，其後欄位全失效。
3. 避免 SELECT *： 只查索引欄位可觸發 Using index（覆蓋索引），避免回表。
4. 注意 NULL： 等值條件 col = NULL 永遠不成立，需用 IS NULL，同樣遵循最左原則。
5. 驗證手段： 永遠跑 EXPLAIN 看 key_len 與 type，而非憑直覺。

延伸：索引下推 (ICP, MySQL 5.6+)

若 WHERE 條件包含索引無法完全覆蓋的部分（如 b = 2 AND a > 3 AND c = 'x'），MySQL 5.6+ 會在儲存引擎層用索引欄位先過濾，再回表檢查 c，減少回表次數。但前提仍是索引最左欄能被使用。

總結

複合索引欄位順序不是任意的：等值在左，範圍在右。本文以實測 EXPLAIN 證明：(b, a) 索引下，b = const 讓 a 完整生效；b > const 導致 a 失效。下次建索引前，先統計 WHERE 條件的等值/範圍分布，再決定欄位順序，事半功倍。