MySQL 查詢優化核心系列（一）：索引原理、選擇與避坑指南

索引為何能加速？

無索引表 = 無序堆，查詢需全表掃描。索引 = 有序數據結構（B+Tree），提供：

1. 範圍定位：二分查找/樹遍歷定位首匹配行，O(log N)
2. 有序性：滿足 ORDER BY/GROUP BY 避免排序
3. 覆蓋掃描：索引包含所有查詢列，避免回表
4. JOIN 加速：驅動表掃描 + 被驅動表索引查找，將笛卡兒積優化為 Nested Loop Join

索引類型與適用場景

建立索引的黃金法則

1. 只為 WHERE/JOIN/ORDER BY/GROUP BY 列建索引，SELECT 列表列不建索引
2. 高選擇性優先：選擇性 = COUNT(DISTINCT col) / COUNT(*)，越接近 1 越好（唯一鍵=1）
3. 複合索引遵循「最左前綴」：INDEX (a,b,c) 支援 a/a,b/a,b,c，不支援 b/c/b,c
4. 等值條件前，範圍條件後：WHERE a=1 AND b>2 AND c=3 → INDEX (a,c,b) 或 (a,b)（範圍列後的列失效）
5. 覆蓋索引：SELECT a,b FROM t WHERE c=1 → INDEX (c,a,b)，Using index 避免回表
6. 避免冗餘索引：PRIMARY KEY (id) + INDEX (id) 重複；INDEX (a,b) 已覆蓋 INDEX (a)
7. 外鍵列必須建索引：InnoDB 要求被引用列與引用列都有索引

索引失效常見模式

索引維護成本

• 寫入放大：每次 INSERT/UPDATE/DELETE 需同步更新所有索引，索引越多寫入越慢
• 空間佔用：MyISAM 獨立 .MYI，InnoDB 共享表空間，索引體積常超過數據
• 統計資訊失真：大量變更後需 ANALYZE TABLE 更新統計，否則優化器誤判

實戰檢查清單

-- 1. 查看表索引
SHOW INDEX FROM tbl;

-- 2. 分析查詢計畫
EXPLAIN SELECT ...;
-- 關注：type（ALL/index/range/ref/eq_ref/const）、key、rows、Extra（Using index/Using where/Using temporary/Using filesort）

-- 3. 發現缺失索引
-- 慢查詢日誌 + pt-query-digest → 高頻慢 SQL → EXPLAIN 驗證 → 加索引

-- 4. 發現冗餘/未用索引
-- sys.schema_unused_indexes / sys.schema_redundant_indexes (5.7+)
-- 或長期監控 INNODB_INDEX_USAGE / USER_INDEX_USAGE

總結

索引是有序數據結構換取查詢速度的空間/寫入成本。核心功課：**理解 B+Tree 原理、掌握最左前綴、識別失效模式、定期審計索引使用情況**。沒有銀彈，只有基於負載特徵的持續調優。