MySQL 索引優化全攻略：索引類型、建立原則、失效場景、副作用與工具選型

MySQL 索引優化完整指南：從原理到實戰的系統性方法論

一、MySQL 邏輯架構三層

1. 連接層：連接池、認證、執行緒管理
2. 服務層：解析器、優化器、快取、視圖、內建函數
3. 儲存引擎層：InnoDB、MyISAM 等，負責資料儲存與索引

二、索引分類全景

三、建立索引語法

CREATE TABLE t(
  col1 INT,
  col2 VARCHAR(50),
  INDEX idx_col1 (col1),
  UNIQUE uk_col2 (col2),
  INDEX idx_col1_col2 (col1, col2)  -- 複合索引
);

-- 或
ALTER TABLE t ADD INDEX idx_col1 (col1);
CREATE INDEX idx_col1 ON t(col1);

四、建立索引核心原則

1. 低基數欄位（如性別、狀態）不建索引
2. 高頻更新欄位不建索引
3. MySQL 無 Bitmap 索引
4. Text/Blob 全文搜尋用 Sphinx/Elasticsearch
5. WHERE/ORDER BY/GROUP BY/DISTINCT 高頻欄位建索引
6. 索引過多傷寫入：每增一索引，INSERT/UPDATE/DELETE 多維護一棵 B+ 樹
7. 優先複合索引，少建單列索引
8. 字串欄位建**前綴索引**：INDEX idx_name (name(18))（80% 資料 < 18 字元）
9. 複合索引欄位序：**過濾性最高/最左前綴優先**

五、最左前綴匹配原則（核心）

MySQL 從左向右匹配，**遇到範圍查詢 (>、<、>=、<=、LIKE %...、BETWEEN) 停止匹配**。

-- 索引 (a,b,c,d)
WHERE a=1 AND b=2 AND c>3 AND d=4  -- 僅用到 a,b,c；d 無法用到

-- 索引 (a,b,d,c) → a,b,d,c 全用到
-- = 和 IN 可亂序，優化器自動重排

六、MySQL 使用索引的運算符

• <、<=、=、>、>=、BETWEEN
• LIKE 'prefix%'（不以萬用字元開頭）
• col IS NULL（已建索引）
• 多條件：Index Merge 優化（union/intersection/sort-union）

七、索引失效常見場景

1. 掃描行數 > 30% 全表 → 優化器選全表掃描
2. 複合索引首列用範圍查詢
3. Memory 表用 Hash 索引做範圍/排序
4. ORDER BY key2 配合 WHERE key1=? → 僅能用一個索引
5. 關聯欄位型別/長度不一致 → 隱式轉換失效
6. 索引列用函數/運算：WHERE func(col)=?、WHERE col+1=? ❌；WHERE col=?+? ✅

八、索引三大副作用（成本）

1. 維護成本：增刪改需同步修改 B+ 樹
2. 訪問成本：查詢需遍歷索引樹
3. 空間成本：每個索引佔用磁碟與記憶體

九、索引分析工具選型

建議：生產環境優先用 pt-index-usage 低峰期分析特定表，或人工結合 EXPLAIN、SHOW INDEX、業務知識判斷。