MySQL 索引優化深度實戰：從 B-Tree 到覆蓋索引、鎖優化

MySQL 索引優化深度實戰：從 B-Tree 到覆蓋索引、鎖優化

索引是查詢效能的核心。本文深度解析 B-Tree/Hash/空間/全文索引原理、最左前綴原則、覆蓋索引、排序優化、InnoDB 鎖與索引關係，並提供大量 EXPLAIN 實戰案例。

一、索引資料類型選擇原則

1. 越小越好：佔用記憶體/CPU 快取少，處理快。INT (4B) < BIGINT (8B) < CHAR(10) (10B)
2. 越簡單越好：整數比較快於字串比較。時間用 DATETIME/TIMESTAMP，IP 用 INT UNSIGNED（INET_ATON()）
3. 盡量 NOT NULL：NULL 使索引、統計、比較更複雜。用 0、空串、特殊值替代

二、標識符選擇

• 整數最佳：處理快、支援 AUTO_INCREMENT
• 避免字串：佔空間大、處理慢、隨機分布導致頁面分裂、聚簇索引碎片
• 業務主鍵 vs 代理主鍵：業務主鍵（身分證、訂單號）常為字串/非遞增，插入隨機。建議用 BIGINT AUTO_INCREMENT 代理主鍵，業務欄位加 UNIQUE 索引

三、索引類型深度解析

1. B-Tree 索引（最常用）

支援：全鍵值匹配、最左前綴匹配、列前綴匹配、範圍查詢、精確+範圍混合、覆蓋索引、ORDER BY 優化

限制：必須從最左列開始；不能跳過列；範圍條件右側列無法使用索引

2. Hash 索引（Memory 引擎預設）

• 極快等值查詾（=, IN, <=>）
• 不支援排序、範圍、部分匹配、覆蓋索引
• 哈希衝突用鏈表解決

3. 空間索引

MyISAM 支援，GEOMETRY 類型，地理空間查詢

4. 全文索引

MyISAM/InnoDB 5.6+，自然語言搜尋，MATCH AGAINST

四、高效能索引策略

1. 聚簇索引

• 資料按主鍵順序物理存放，葉節點含完整行
• InnoDB 強制聚簇索引：有主鍵用主鍵，無則用唯一非空索引，無則隱藏 6 字節 RowID
• 二級索引葉節點存主鍵值而非行指標，移動資料/分頁無需更新二級索引
• MyISAM 非聚簇：葉節點存行號，主鍵與二級索引無本質差異

2. 覆蓋索引

• 查詢欄位全包含在索引中，Extra: Using index
• 優勢：索引項小、順序存儲少 I/O、引擎更好緩存索引
• InnoDB 特有：二級索引含主鍵值，可覆蓋含主鍵的查詢
• 僅 B-Tree 支援，Memory/Falcon 不支援

3. 利用索引排序

• 索引順序 = ORDER BY 順序，且同方向（全 ASC 或全 DESC）
• 多表聯結時，ORDER BY 列必須全屬第一張表
• 否則使用 filesort：雙遍歷（舊）或單遍歷（新，max_length_for_sort_data 控制）

4. 索引與鎖優化

• InnoDB 只鎖訪問到的行，索引能過濾不需訪問的行
• 若索引無法過濾（如範圍查詢後無法用索引再過濾），InnoDB 鎖住掃描範圍內所有行，Server 層再過濾 → 鎖爭用劇增
• 案例：WHERE id < 4 AND id <> 1 FOR UPDATE → 只用 id < 4 索引範圍掃描，鎖住 id=1,2,3 即使 id=1 不滿足條件
• 無索引全表掃描 → 鎖全表所有行

五、EXPLAIN 實戰案例庫

案例 1：主鍵排序

CREATE TABLE `actor` (
  `actor_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(16) NOT NULL DEFAULT '',
  `password` VARCHAR(16) NOT NULL DEFAULT '',
  PRIMARY KEY (`actor_id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB;

EXPLAIN SELECT actor_id FROM actor ORDER BY actor_id;
-- type=index, key=PRIMARY, Extra=Using index  ✅ 覆蓋索引+索引排序

案例 2：無索引排序

EXPLAIN SELECT actor_id FROM actor ORDER BY password;
-- type=ALL, key=NULL, Extra=Using filesort  ❌ 全表掃描+filesort

案例 3：索引列排序

EXPLAIN SELECT actor_id FROM actor ORDER BY name;
-- type=index, key=idx_name, Extra=Using index  ✅

案例 4：覆蓋索引（InnoDB 二級索引含主鍵）

-- sakila.actor 有 idx_last_name (last_name)
EXPLAIN SELECT actor_id, last_name FROM actor WHERE last_name = 'HOPPER';
-- type=ref, key=idx_last_name, Extra=Using where; Using index  ✅

案例 5：範圍條件阻礙後續列

-- 索引 (last_name, first_name, dob)
EXPLAIN SELECT * FROM people WHERE last_name='Smith' AND first_name LIKE 'J%' AND dob='1976-12-23';
-- 僅用前兩列索引，LIKE 是範圍查詢

六、最佳實踐檢查清單

• [ ] 核心查詢是否有 EXPLAIN 驗證？
• [ ] 復合索引是否遵循最左前綴？
• [ ] 高頻查詢是否達成覆蓋索引？
• [ ] ORDER BY 是否利用索引避免 filesort？
• [ ] 字串索引是否用前綴長度？
• [ ] 是否避免 SELECT *？
• [ ] InnoDB 表是否用代理主鍵（AUTO_INCREMENT BIGINT）？
• [ ] 範圍查詢後是否避免在索引中再過濾？
• [ ] 定期 ANALYZE TABLE 更新統計？
• [ ] 大表是否考慮分區/分表？