MySQL 索引優化全維度指南：類型、創建、最左原理、失效場景、監控指標

MySQL 優化體系中的索引定位

索引是查詢加速的核心手段，但「有索引不一定快，無索引一定慢」。優化金字塔：Schema 設計 → 索引策略 → SQL 改寫 → 配置調優 → 架構擴展 → 硬件升級。

四大索引類型與創建語法

創建語法全集

-- 建表時
CREATE TABLE t (
  id INT PRIMARY KEY,
  name VARCHAR(32),
  email VARCHAR(64) UNIQUE,
  content TEXT,
  INDEX idx_name (name),
  FULLTEXT ft_content (content)
) ENGINE=InnoDB;

-- 事後添加
ALTER TABLE t ADD INDEX idx_name (name);
ALTER TABLE t ADD UNIQUE uk_email (email);
ALTER TABLE t ADD FULLTEXT ft_content (content);
CREATE INDEX idx_name ON t(name);
CREATE UNIQUE INDEX uk_email ON t(email);

-- 前綴索引 (大字段必用)
CREATE INDEX idx_email_prefix ON t(email(10));  -- 索引前 10 字符
-- BLOB/TEXT 必須指定前綴長度

索引類型選擇：BTREE vs HASH

BTREE：範圍查詢、排序、分組、最左前綴匹配全支持。**通用首選**。
HASH：僅支持 =、IN、<=> 精確查找，O(1) 極快，但不支持範圍/排序。適合 Memory 引擎的精確查找表。

最左前綴原理 (複合索引核心法則)

複合索引 INDEX idx_a_b_c (a, b, c) 等價於建立三棵 B+ 樹：(a), (a,b), (a,b,c)。

• 能用上：WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3、WHERE a=1 ORDER BY b、WHERE a=1 AND b=2 ORDER BY c
• 用不上：WHERE b=2 (缺最左列 a)、WHERE a=1 AND c=3 (中間斷層 b)、WHERE a=1 ORDER BY c (跳過 b 導致排序無序)
• 範圍列後面失效：WHERE a=1 AND b>2 AND c=3 → c 用不上索引 (b 是範圍查詢)

索引失效十大經典場景 (避坑指南)

1. LIKE 以 % 開頭：WHERE name LIKE '%abc' → 全表掃描。LIKE 'abc%' 可用索引
2. 隱式類型轉換：字符串列不加引號 WHERE phone = 13800000000 → 列轉數字，索引失效。**永遠加引號！**
3. OR 條件未全覆蓋索引：WHERE a=1 OR b=2，若 a、b 無聯合索引 → 全掃。**改用 UNION ALL**
4. 對索引列做運算/函數：WHERE DATE(create_time) = '2024-01-01' → 改為 WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'
5. 不等於 (!= / <> ) / IS NULL / IS NOT NULL：通常不走索引 (優化器判斷掃描行數過多)
6. 優化器認為全掃更快：小表、低選擇性 (如性別)、統計信息不準。ANALYZE TABLE 更新統計
7. 複合索引最左列未在 WHERE：如前所述
8. 字符集/校對規則不匹配：JOIN 關聯列字符集不同 → 隱式轉換，索引失效
9. 覆蓋索引未命中：SELECT 列超出索引列，導致回表。優化：構建覆蓋索引 INDEX idx_a_b_c (a, b, c) 滿足 SELECT a,b,c FROM t WHERE a=1
10. 統計信息偏差：InnoDB 隨機采樣統計，大表可能不準。SET innodb_stats_persistent=ON + 定期 ANALYZE TABLE

索引監控與效果驗證

-- 1. 查看索引使用情況
SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key: 索引讀取次數 (越高越好)
-- Handler_read_rnd_next: 全表掃描次數 (越低越好)

-- 2. EXPLAIN 實戰
EXPLAIN SELECT * FROM t WHERE a=1 AND b=2;
-- 關鍵列：type (ref/range/ALL), key (實際用索引), key_len, rows (預估掃描行), Extra (Using index/Using where/Using filesort)

-- 3. 索引統計信息
SHOW INDEX FROM t;
-- Cardinality: 基數 (去重行數)，越接近總行數選擇性越高

索引維護最佳實踐

• 定期重建：大量 UPDATE/DELETE 導致碎片，OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 重建 (在線 DDL 工具首選)
• 監控未使用索引：sys.schema_unused_indexes (5.7+ sys schema) 定期清理無用索引，減少寫入開銷
• 控制索引數量：單表建議 ≤ 5 個索引，每個索引列數 ≤ 3，權衡讀寫比
• 命名規範：idx_ 普通、uk_ 唯一、fk_ 外鍵、ft_ 全文，便於識別管理