MySQL 索引優化系統性實戰指南（深度重構版）
一、MySQL 三層邏輯架構
- 連接層：連接池、認證、執行緒
- 服務層：解析、優化、快取、視圖、函數
- 儲存引擎層：InnoDB、MyISAM 等，索引實際存放處
二、索引類型全景圖
| 分類 | 類型 | 關鍵特性 |
|---|
| 資料結構 | B+ 樹 | 範圍、排序、最左前綴，通用首選 |
| Hash | 精確匹配 O(1)，無範圍/排序，Memory 專用 |
| Fulltext | 全文搜尋，5.6+ InnoDB 支援 |
| R-Tree | GIS 空間資料，SPATIAL 索引 |
| 物理存儲 | 聚簇索引 | 主鍵即資料，InnoDB 預設 |
| 非聚簇索引 | 次級索引，葉子存主鍵，需回表 |
| 邏輯功能 | 主鍵索引 | 唯一、非空、單表一個 |
| 普通/單列 | 基礎索引 |
| 複合/多列 | 遵循最左前綴 |
| 唯一索引 | 值唯一，允許多 NULL |
| 空間索引 | GIS 類型，MyISAM 專用 |
三、建立索引語法
CREATE TABLE t(
a INT, b VARCHAR(50),
INDEX idx_a (a),
UNIQUE uk_b (b),
INDEX idx_a_b (a, b) -- 複合索引順序關鍵
);
ALTER TABLE t ADD INDEX idx_a (a);
CREATE INDEX idx_a ON t(a);
四、建立索引鐵律
- 低基數（性別/狀態）不建
- 高頻更新欄位不建
- 無 Bitmap 索引
- Text/Blob 全文 → Elasticsearch/Sphinx
- 高頻 WHERE/ORDER BY/GROUP BY/DISTINCT 必建
- 索引不過多：每索引 = 一棵 B+ 樹維護成本
- 優先複合，少單列
- 字串建前綴：
INDEX idx_name (name(18))
- 複合索引序：過濾性最高/最左前綴優先
五、最左前綴匹配原則（必背）
從左向右匹配，**遇到範圍查詢 (>、<、>=、<=、LIKE %...、BETWEEN) 即停止**。
-- 索引 (a,b,c,d)
a=1 AND b=2 AND c>3 AND d=4 -- 用到 a,b,c；d 失效
-- 優化：索引 (a,b,d,c) → 全用到
-- = 和 IN 可亂序，優化器自動重排
六、可用索引的運算符
<、<=、=、>、>=、BETWEENLIKE 'prefix%'（非萬用字元開頭）col IS NULL（已建索引）- 多條件：Index Merge (union/intersection/sort-union)
七、7 大索引失效場景
- 掃描行數 > 30% → 全表掃描
- 複合索引首列用範圍查詢
- Memory 表 Hash 索引做範圍/排序
WHERE key1=? ORDER BY key2 → 僅用一索引- 關聯欄位型別/長度不一致 → 隱式轉換
- 索引列用函數/運算：
func(col)、col+1 ❌
- 字元集/排序規則不一致導致隱式轉換
八、索引三大成本（副作用）
- 維護成本：增刷改同步 B+ 樹
- 訪問成本：遍歷索引樹
- 空間成本：磁碟+記憶體佔用
九、工具選型建議
| 工具 | 適用場景 | 注意事項 |
|---|
| pt-index-usage | 低峰期分析特定表 slow log | 需 long_query_time=0，大檔案慢 |
| EXPLAIN + SHOW INDEX | 單條 SQL 即時分析 | 結合業務知識人工判斷 |
| sys.schema_unused_indexes | MySQL 5.7+ 系統視圖 | 基於 performance_schema，零侵入 |
| information_schema.INDEX_STATISTICS | 啟用 userstat 時可用 | 風險較高，生產慎開 |
十、避坑速查清單
- ✅ 複合索引順序 = WHERE 條件順序 = 最左前綴
- ✅ 範圍列放複合索引最後
- ✅ 關聯查詢雙方欄位型別、長度、字元集**完全一致**
- ✅ 避免
WHERE col = DATE_FORMAT(...) 等函數包裹索引列
- ✅ 定期
ANALYZE TABLE 更新統計資訊
- ✅ 監控
Handler_read_rnd_next（全表掃描計數）
- ❌ 不要在每個欄位都建單列索引
- ❌ 不要用
SELECT * 導致回表
- ❌ 不要用
OFFSET 1000000 LIMIT 10 深分頁（改用游標/延遲關聯）
