索引是 MySQL 性能調優的核心槓桿。本文整理自資深專家的實戰問答，提煉出 15 條關鍵原則，涵蓋類型選擇、組合策略、排序優化、外鍵設計、主鍵權衡、索引維護等高頻場景，助你避開誤區，精準優化。

一、 類型選擇：數值優於字符串

場景：用 BIGINT 替代 VARCHAR(25-30) 存儲 CRC64 哈希值。
結論：通常是優化利器。8 字節定長優於 25+ 字節變長，索引體積縮小，比較更快。
注意：需防哈希碰撞，查詢加雙重校驗 WHERE hash=crc64('str') AND col='str'；MySQL 字符串比較預設不區分大小寫，哈希值亦同；此技巧對超 100 字節字符串收益最大。

二、 組合索引與排序：最左前綴與「範圍列陷阱」

場景：WHERE A=xxx AND B BETWEEN 100 AND 200 ORDER BY B。
最佳索引：(A, B)。
陷阱：若需按第三列 C 排序，(A, B, C) 因 B 是範圍查詢，導致 C 無序，排序無法用索引。
對策：將排序轉化為小範圍聯合查詢，或接受 filesort。

三、 關聯表雙向索引：主鍵+輔助索引雙重保障

場景：關聯表 LINK(id1, id2) 需雙向查詢。
最佳實踐：

CREATE TABLE LINK (
    id1 INT UNSIGNED NOT NULL,
    id2 INT UNSIGNED NOT NULL,
    PRIMARY KEY (id1, id2),
    KEY K (id2, id1)
) ENGINE=INNODB;

主鍵加速 id1 為先導查詢，輔助索引 K 加速 id2 為先導查詢，雙向覆蓋無死角。

四、 IN vs BETWEEN：語法陷阱與優化器智慧

• IN (2-4) 被解析為 IN (-2) (數學運算)，非預期的 IN (2,3,4)。
• 現代優化器已將單值 IN (5) 自動轉為 =5，性能無差異。

五、 主鍵設計：自增 vs 業務鍵

• 自增主鍵：默認最佳選擇，順序寫入，頁分裂少，二級索引短小。
• 業務鍵 (如 A,B,C,ID)：僅當該列極大幅度優化分組/範圍查詢且插入壓力可接受時考慮。缺點：主鍵膨脹導致二級索引巨大、插入隨機 IO 激增、頁分裂頻繁、MySQL 追加主鍵到二級索引有諸多限制。

六、 外鍵列索引：視連接順序而定

若驅動表通過其他索引訪問，再回主表按主鍵查找，被驅動表的外鍵列**無需**額外索引。只有作為驅動表或需反向連接時才需索引。

七、 索引擴展 vs 新建：長度代價與維護成本

將 (vendor_id) 擴展為 (vendor_id, order_id) 且僅查 vendor_id：
優點：避免維護兩個索引，寫入開銷降低。
缺點：索引鍵值變長 (如 +4 字節 INT)，讀取略慢。
決策：增長極小 (INT/BIGINT) 可接受；增長大 (VARCHAR/TEXT) 必須新建單列索引。

八、 索引與內存：工作集原則

400GB 索引無需全入內存。只需熱點數據 (工作集) 常駐內存，佔總內存 5%-100%。工作集大小與性能呈指數關係，差 10 倍以上。關鍵在於識別並保護熱點數據。

九、 索引數量：質量重於數量

MySQL 8.0 單表上限 64 個，但**絕不應接近上限**。每個索引皆有寫入代價 (維護 B+ 樹、Undo Log、Redo Log)。只添加經驗證能提升讀性能且收益大於寫損耗的索引。

十、 UUID 主鍵：性能殺手的緩解方案

VARCHAR(36) UUID 破壞插入順序，導致嚴重頁分裂、索引膨脹。
優化：轉為 VARBINARY(16) 存儲二進制 UUID (移除連字符)，或使用有序 UUID (如 UUID v1/v2 時間戳前綴、COMB UUID)。仍不如自增主鍵高效，非分布式場景慎用。

十一、 GROUP BY 索引優化：避免臨時表與 Filesort

為 GROUP BY 列加索引，MySQL 可通過索引掃描直接完成分組，省去臨時表創建與文件排序，性能提升顯著。

十二、 日期範圍查詢：BETWEEN 轉 IN 技巧

日期比較本質同數值。特定條件下 (如離散值較少)，將 BETWEEN 改寫為 IN (date1, date2, ...) 可更好利用索引區間掃描。

十三、 InnoDB B+ 樹葉子節點：雙向鏈表

葉子節點包含前驅/後繼指針，支持雙向遍歷。ORDER BY DESC 可利用此特性逆序掃描，無需單向鏈表反向遍歷的高昂代價。

十四、 ORDER BY 索引使用條件：嚴格匹配

索引 (A, B) 優化 WHERE A=5 ORDER BY B 需滿足：
1. 單表查詢；
2. 無其他索引干擾；
3. WHERE 條件為等值比較 (A=5)；
4. ORDER BY 列順序與索引一致。
複雜條件需用「Unionizing Order By」等進階技巧。

十五、 CMS 索引管理：不可依賴自動化

MySQL **不會**自動創建索引。CMS 預置索引往往不足，DBA 必須根據慢查詢日誌、EXPLAIN 分析，手動添加、調整索引。

總結：索引優化無銀彈，核心在於「理解業務訪問模式、量化讀寫代價、持續監控驗證」。將這 15 條原則內化為直覺，方能游刃有餘。