深度解析 MySQL 索引與優化：從 B+ 樹到覆蓋索引、排序、鎖機制

索引價值量化

假設 100 萬行、4KB 頁存 100 行 → 1 萬頁。無索引全掃最壞 1 萬次隨機 I/O × 10ms = 100 秒。B+ 樹索引 log100(106) = 3 次頁讀取 → 30ms。索引是性能調優的起點。

選擇索引列數據類型三原則

1. 越小越好：INT (4B) 優於 BIGINT (8B) 優於 CHAR(32)
2. 越簡單越好：整數比較快於字符串 (字符集/校對規則開銷)
3. 盡量 NOT NULL：NULL 值增加優化器複雜度、索引統計偏差

標識列首選 INT UNSIGNED AUTO_INCREMENT；IP 存 INT UNSIGNED (INET_ATON/INET_NTOA)；時間用原生 DATETIME/TIMESTAMP。

索引類型核心差異

聚簇索引 vs 非聚簇 (InnoDB vs MyISAM)

• InnoDB：主鍵即聚簇索引，葉子存完整行；二級索引葉子存主鍵值 → 「回表」查完整行
• MyISAM：主鍵/二級索引無區別，葉子存行號 (物理指針)，數據文件獨立

最佳實踐：InnoDB 使用 自增整數主鍵，保證順序寫入、避免頁分裂、縮小二級索引體積。

覆蓋索引：避免回表的殺手鐧

查詢列全部包含在索引中 → Extra: Using index。InnoDB 二級索引葉子含主鍵，故含主鍵查詢易覆蓋。

-- 索引 idx_store_id_film_id (store_id, film_id)
SELECT store_id, film_id FROM inventory; -- 覆蓋
SELECT * FROM inventory WHERE store_id=1; -- 非覆蓋，需回表

利用索引排序：避免 filesort

• ORDER BY 列順序與索引順序一致、方向一致 → 直接輸出有序
• 混合 ASC/DESC、跨索引列、非最左前綴 → filesort (內存快排/外排)
• EXPLAIN 關鍵：type=index + Extra 無 Using filesort

索引與鎖：隱形鎖範圍

BEGIN;
SELECT * FROM actor WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE;
-- 實際鎖住 1,2,3 三行！因優化器僅用索引範圍掃描，無法下推 <> 過濾

對應 EXPLAIN 顯示 Using where; Using index。InnoDB 鎖住索引掃描到的所有行，非索引條件不縮小鎖範圍。

調優清單

1. 慢查詢日誌 + pt-query-digest 定位 Top SQL
2. EXPLAIN 分析訪問類型、可能索引、實際索引、rows、Extra
3. 補充最左前綴索引、覆蓋索引、冗余索引清理
4. 大表考慮分區、分庫分表、緩存層

原文：理解MySQL——索引與優化