MySQL 索引實戰進階：組合索引最左前綴、建立時機與 LIKE 陷阱完整指南

延續基礎索引類型，本文聚焦實戰核心：組合索引「最左前綴」匹配機制深度解析、索引建立決策框架、LIKE 模糊查詢索引失效邊界、以及函數運算導致的索引失效陷阱與改寫技巧。

組合索引：最左前綴原理深度解析

組合索引 INDEX idx_a_b_c (a, b, c) 的 B+ 樹鍵值按 (a, b, c) 元組排序。查詢能否用到索引，取決於 WHERE 條件是否包含索引最左邊的連續列。

✅ 生效場景

WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b > 2          -- 範圍查詢停在 b，c 無法用到
WHERE a = 1 ORDER BY b, c      -- 排序也能用到

❌ 失效場景

WHERE b = 2                    -- 缺少最左列 a
WHERE b = 2 AND c = 3          -- 缺少最左列 a
WHERE a = 1 AND c = 3          -- 跳過 b，c 無法用到（中斷連續性）
WHERE a > 1 AND b = 2          -- a 範圍查詢，b 後續列失效

💡 設計原則

1. 等值查詢列優先：a, b 為等值，c 為範圍/排序 → INDEX (a, b, c)
2. 區分度高者優先：但需平衡查詢模式，若高區分度列從不單獨查詢，可後置
3. 避免冗餘單列索引：組合索引 (a,b) 已覆蓋單列 a，無需額外建 INDEX (a)

索引建立決策框架：何時建、何時不建

✅ 必須建立索引

• WHERE、JOIN ON 高頻出現的列
• ORDER BY、GROUP BY 高頻列（配合 WHERE 列組合索引）
• 外鍵關聯列（InnoDB 自動建立，MyISAM 需手動）

⚠️ 條件生效：操作符限制

MySQL 僅對以下操作符使用索引：

<, <=, =, >, >=, BETWEEN, IN, LIKE 'prefix%'

不使用索引：LIKE '%suffix'、LIKE '%mid%'、!=、<>、NOT IN、函數運算、隱式轉換。

❌ 不建議建立索引

• 區分度極低列（性別、狀態碼、布爾值）
• 極少查詢的列
• 頻繁大批量 UPDATE 的列
• 小表（優化器傾向全表掃描）

兩大實戰陷阱與改寫技巧

陷阱一：LIKE 通配符位置決定生死

解決方案：全文索引（FULLTEXT）、倒排索引、或應用層搜尋引擎。

陷阱二：欄位運算導致索引失效

-- ❌ 錯誤：每行計算 YEAR()，索引失效，全表掃描
SELECT * FROM users WHERE YEAR(create_time) < 2007;

-- ✅ 正確：常量預計算，索引範圍掃描
SELECT * FROM users WHERE create_time < '2007-01-01';

-- ❌ 錯誤：欄位參與運算
SELECT * FROM orders WHERE amount * 1.1 > 100;

-- ✅ 正確：常量移等號右側
SELECT * FROM orders WHERE amount > 100 / 1.1;

陷阱三：隱式類型轉換

-- 欄位 varchar，傳入數字 → 隱式轉換，索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- 修正：字串字面量
SELECT * FROM users WHERE phone = '13800138000';

進階避坑清單

• NOT IN / <> / !=：優化器通常放棄索引，改用 NOT EXISTS 或 LEFT JOIN ... IS NULL
• OR 條件：除非兩側都有獨立索引且使用 UNION 改寫，否則多數全表掃描
• IS NULL / IS NOT NULL：可用索引，但區分度極低時優化器可能拒用
• 字符集/校對規則不一致：JOIN 或比較時隱式轉換，索引失效

索引優化的本質是「讓優化器找到最短路徑」。持續收集 EXPLAIN、慢查詢日誌、Performance Schema 數據，建立「查詢模式-索引結構」映射知識庫，是 DBA 進階的核心功力。