MySQL 索引優化全攻略：原理、選擇策略、最左前綴、避坑指南

為什麼需要索引

無索引時多表聯結需全表掃描，組合數呈指數級增長；索引讓查詢從「全掃描」變為「索引定位 + 回表」，理論提速百萬倍。

索引的代價

• 寫入/更新/刪除變慢：每條記錄需同步維護所有索引結構。
• 佔用磁盤空間：MyISAM 索引文件單獨增長，InnoDB 共享表空間或獨立表空間同樣佔用。

如何選擇索引列

1. 出現在 WHERE、JOIN、ORDER BY、GROUP BY 的列，而非 SELECT 輸出列。
2. 聯結條件 col1 = col2 的列優先。
3. 高基數（Cardinality）列：COUNT(DISTINCT col)/COUNT(*) > 0.1 適合，性別類低基數列不宜。
4. 短數據類型：MEDIUMINT 優於 BIGINT，VARCHAR(20) 優於 CHAR(100)。
5. 字符串列用前綴索引：INDEX idx_name (name(10))，兼顧區分度與空間。

多列索引與最左前綴原則

建立 INDEX idx_a_b_c (a,b,c) 等價於擁有 (a)、(a,b)、(a,b,c) 三個索引。查詢必須從最左列開始匹配，遇到範圍查詢（>、<、LIKE '%...'）停止匹配後續列。

五大建索引鐵律

索引類型選擇

• B+Tree：支援 =、>、<、BETWEEN、LIKE 'prefix%'，InnoDB/MyISAM 默認。
• Hash：MEMORY 引擎默認，僅支援精確匹配 =、<=>，不支援範圍；可用 USING BTREE 強制 B+Tree。

慢查詢日誌驅動優化

開啟 slow_query_log、long_query_time，用 mysqldumpslow 分析高頻慢 SQL，針對性加索引或改寫。

總結

索引是「查詢加速器」，但也是「寫入剎車、空間佔用者」。遵循「高區分、短類型、最左前綴、不計算、不濫建」五大原則，配合 EXPLAIN 與慢日誌持續迭代，才能實現穩健優化。