MySQL 索引從入門到精通：原理、優化、避坑與實戰技巧

MySQL 索引從入門到精通：原理、優化、避坑與實戰技巧

索引本質

索引是對特定列通過特定算法 (默認 B+Tree) 排序後的數據結構，將掃描行數從 O(N) 降至 O(log N)。哈希索引適用於等值查詢，B+Tree 支持範圍與排序。

EXPLAIN 核心欄位解讀

索引類型

• PRIMARY KEY：唯一、非空、一表僅一個，聚簇索引。
• UNIQUE：唯一、允許 NULL。
• INDEX (NORMAL)：允許重複、允許 NULL。
• FULLTEXT：全文檢索，僅 MyISAM/InnoDB 5.6+ 支持，中文需分詞器 (Coreseek/xunsearch)。

建索引五大原則

1. 最左前綴匹配：組合索引 (a,b,c)，查詢 a=1 AND b=2 AND c>3 用到 a,b；c>3 為範圍查詢，匹配停止，d 無法用到索引。
2. = 和 IN 可亂序：優化器自動調整順序匹配索引。
3. 高區分度優先：count(DISTINCT col)/count(*) > 0.1 (JOIN 字段建議 > 0.1)。性別/狀態等低區分度列不宜建索引。
4. 索引列不參與計算：from_unixtime(create_time) = '...' 失效，改為 create_time = unix_timestamp('...')。
5. 擴展索引不新建：已有 (a)，需 (a,b) 直接 ALTER TABLE ... ADD INDEX (a,b) 覆蓋舊索引。

組合索引與前綴索引

組合索引 = 多個索引

INDEX (name, city, age) 等價於擁有 (name,city,age), (name,city), (name) 三個索引。最左前綴原則：缺左列則不可用，如 WHERE city='x' AND age=20 不走索引。

前綴索引

長字符串列 (如 VARCHAR(255)) 可只索引前 N 字符：INDEX (title(10))。選擇 N 使區分度 > 0.31 (黃金值)。

SELECT COUNT(DISTINCT LEFT(title,10))/COUNT(*) FROM t; -- >0.31 可建
ALTER TABLE t ADD INDEX idx_title_10 (title(10));

不走索引的常見 SQL 反模式

WHERE col + 10 = 30;           -- 索引列參與運算
WHERE LEFT(date,4) < 1990;     -- 索引列用函數
WHERE name LIKE '%abc%';       -- 前導通配符
WHERE name REGEXP '...';       -- 正則不走索引
WHERE char_col = 123;          -- 字符串列比較數字 (隱式轉換)
WHERE a=1 OR b=2;              -- OR 導致全表掃描 (除非兩列都有索引且優化器選擇 index_merge)

多表 JOIN 索引策略

• 驅動表 (第一張表) 必須全掃描或範圍掃描，被驅動表需有 JOIN 列索引。
• 用 LEFT JOIN 以小表驅動大表，減少驅動表掃描行數。
• 避免過多表 JOIN (超過 3-5 表) 產生笛卡爾積爆炸。

索引的代價與權衡

• 寫入 (INSERT/UPDATE/DELETE) 需維護索引，速度下降。
• 佔用磁盤空間。
• 互聯網讀多寫少 (80-90% 讀)，通常利大於弊。
• 大數據導入技巧：先刪索引 → 批量導入 → 重建索引。