索引雖能大幅提升查詢效能，但並非銀彈。本文系統剖析索引的三大固有缺點、六大使用陷阱與避坑指南，助你在享受索引紅利的同時，避開效能反而下降的陷阱。
| 缺點 | 影響 | 緩解策略 |
|---|---|---|
| 降低寫入效能 | INSERT/UPDATE/DELETE 需同步維護索引樹，索引越多寫入越慢 | 讀寫比低的表慎加索引；大量匯入時先刪索引、匯入後重建 |
| 佔用磁碟空間 | 每個索引對應一棵 B+ 樹，複合索引、長字串索引膨脹快 | 用前綴索引、避免過度索引、定期 OPTIMIZE TABLE 整理碎片 |
| 增加優化器負擔 | 優化器需評估更多索引選擇，統計資訊不準時可能選錯索引 | 定期 ANALYZE TABLE 更新統計；必要時用 FORCE INDEX 指定 |
現象： 含 NULL 的行不會出現在索引中；複合索引任一欄為 NULL，整行不入該索引。
影響： WHERE col IS NULL 無法用索引；COUNT(col) 不統計 NULL。
解決：
NOT NULL DEFAULT '' 或 0WHERE col IS NULL 改用 WHERE IFNULL(col, '') = '' (仍可能不走索引)，或建立輔助欄位 is_col_null TINYINT 索引原則： 長字串欄位 (VARCHAR/CHAR/TEXT/BLOB) 指定前綴長度建索引。
範例： CHAR(255) 欄位，前 10-20 字元區分度已高 → INDEX idx_prefix (col(10))
效益： 索引體積 ↓ 50-90%、I/O ↓、緩存命中率 ↑、插入更新速度 ↑
鐵律： MySQL 單表查詢只能使用一個索引。
場景： WHERE a=1 ORDER BY b，若用 INDEX(a) 則 ORDER BY b 需 filesort；若用 INDEX(b) 則 WHERE a=1 全掃。
解決：
INDEX(a, b)，同時滿足過濾與排序ORDER BY a, b，必要時建 INDEX(a, b)| 模式 | 索引 | 替代方案 |
|---|---|---|
LIKE 'abc%' | ✅ 生效 | — |
LIKE '%abc%' | ❌ 失效 | 全文索引 FULLTEXT / Elasticsearch |
LIKE '%abc' | ❌ 失效 | 反轉欄位存儲 + 前綴匹配 / 搜尋引擎 |
錯誤範例：
SELECT * FROM users WHERE YEAR(create_time) = 2024;
SELECT * FROM inventory WHERE amount / 7 < 24;
正確重寫：
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
SELECT * FROM inventory WHERE amount < 24 * 7;
核心原則： 將運算移至等號右側常數，保持欄位「乾淨」。
NOT IN 含 NULL 會導致整體判斷為 UNKNOWN (視為 FALSE)，且常觸發全掃<>、!= 範圍過大，優化器傾向全掃NOT EXISTS、LEFT JOIN ... WHERE t2.id IS NULL、NOT BETWEEN (有範圍時)
-- 錯誤：VARCHAR 欄位與數字比較
WHERE phone = 13800138000 -- 索引失效，全表掃描
-- 正確：字串比字串
WHERE phone = '13800138000'
-- 查詢：WHERE a=1 AND b>2 ORDER BY c
-- 錯誤索引：INDEX(b, a, c) -- 範圍欄位 b 在前，a、c 用不到
-- 正確索引：INDEX(a, b, c) -- 等值 a 在前，範圍 b 次之，排序 c 最後
-- 索引：INDEX(a, b)
-- 查詢：SELECT a, b, c FROM t WHERE a=1 -- 需回表取 c
-- 優化：INDEX(a, b, c) -- 覆蓋索引，Extra: Using index
long_query_time=1，配合 pt-query-digest 分析pt-duplicate-key-checker 或人工審視ALGORITHM=INPLACE 避免鎖表索引是「換空間換時間、換寫入換讀取」的權衡藝術。掌握「不存 NULL、用短索引、避免函數運算、拒絕 %開頭 LIKE、謹慎 NOT IN、警惕隱含轉換」六大鐵律，配合定期統計更新與慢查詢分析，才能讓索引持續發揮正效益，而非成為效能負債。
