MySQL 索引核心原理與性能優化實戰指南

為什麼索引是查詢加速的「銀彈」？

關係數據庫邏輯上是集合運算，但物理執行受限於 I/O、CPU、內存。全表掃描隨數據量線性增長，而索引通過有序數據結構 (B+Tree) 將查找複雜度降為 O(log N)。MySQL 郵件列表中 80% 以上的「查詢慢」問題，加上合適索引即可立竿見影解決。

索引如何改變查找過程

無索引：全表掃描

表是無序堆，查找 company_num=13 需逐行比對，100 萬行需 100 萬次 I/O。

有索引：有序查找 + 範圍截斷

索引文件按 company_num 排序，包含 (鍵值, 行指針) 對。查找 13：

1. 二分查找定位到第一個 13；
2. 順序讀取後續 13，直到遇到 14 停止；
3. 通過行指針回表獲取完整行 (或覆蓋索引直接返回)。

優勢：跳過 99% 無關行；支持 ORDER BY、MIN/MAX、覆蓋索引掃描等額外優化。

多表 JOIN 時的指數級收益

三表 t1/t2/t3 各 1000 行，無索引 JOIN 需 10 億次比較；有索引時：掃描 t1 (1000) → 索引查找 t2/t3 (各 1 次 I/O) → 總成本 ~3000，提速百萬倍。

索引的副作用與權衡

如何為表選擇索引：實戰方法論

1. 識別高頻查詢：開啟慢查詢日志 (long_query_time=1)，收集 Top 20 SQL；
2. 分析 WHERE/JOIN/ORDER BY/GROUP BY 列：這些列是索引候選；
3. 評估選擇性：COUNT(DISTINCT col)/COUNT(*) 越接近 1 越適合建索引；
4. 設計聯合索引遵循「最左前綴」：INDEX (a,b,c) 支持 a、a,b、a,b,c 查詢；
5. 覆蓋索引優化：SELECT a,b FROM t WHERE c=1 → INDEX (c,a,b) 避免回表；
6. 驗證執行計劃：EXPLAIN 觀察 type、key、rows、Extra (Using index/Using where/Using filesort)；
7. 持續迭代：業務變更、數據分布漂移需定期審視索引集合。

常見索引失效場景 (反模式)

• WHERE col + 1 = 10 / WHERE UPPER(name) = 'ABC' → 函數/運算作用於索引列；
• WHERE name LIKE '%abc' → 導模糊匹配無法利用 B+Tree 有序性；
• WHERE a=1 OR b=2 (無聯合索引) → 優化器傾向全表掃描；
• 隱式類型轉換：VARCHAR 列比較數字 WHERE phone=13800000000；
• 聯合索引跳列使用：INDEX(a,b,c) 查 WHERE b=1 或 WHERE a=1 AND c=2。

進階：查看索引使用統計

-- 查看索引基數 (選擇性)
SHOW INDEX FROM tbl_name;

-- 查看實際使用情況 (5.6+ performance_schema)
SELECT * FROM sys.schema_unused_indexes;  -- 未用索引
SELECT * FROM sys.schema_redundant_indexes; -- 冗余索引

總結

索引是數據庫性能的杠桿，但非無成本。「測量 → 分析 → 設計 → 驗證 → 迭代」閉環，結合 EXPLAIN、慢查詢日志、performance_schema 系統表，才能在讀寫平衡點上構建最優索引集合。先加索引，再調 SQL，最後調架構，是性能優化的鐵律。