MySQL 查詢索引正確使用指南：從原理到選擇策略與優化器交互

MySQL 查詢索引正確使用指南：從原理到選擇策略與優化器交互

索引是提升查詢速度最關鍵工具。本文深度解析索引工作原理（B+Tree）、優缺點權衡、選擇策略（基數、前綴、最左前綴）、索引類型匹配（Hash vs B-Tree）、慢查詢日誌驅動優化，並對比 MyISAM/InnoDB/BDB 存儲引擎索引實現差異。

一、索引工作原理：從無序到有序

無索引表： 堆表，全表掃描 O(N)

有索引表： B+Tree 結構，索引項按鍵值有序排列，查找 O(log N)

-- 無索引：掃描所有行
SELECT * FROM ad WHERE company_num = 13;

-- 有索引：B+Tree 定位
CREATE INDEX idx_company ON ad(company_num);
-- 1. 二分查找/ B+Tree 遍歷定位 company_num=13 首條
-- 2. 連續讀取後續滿足條件的葉子節點
-- 3. 遇到 company_num=14 停止 (有序特性)

二、存儲引擎索引實現差異

三、多表 JOIN 索引威力

-- 三表關聯，各 1000 行
SELECT t1.i1, t2.i2, t3.i3
FROM t1, t2, t3
WHERE t1.i1 = t2.i2 AND t2.i2 = t3.i3;

-- 無索引：1000^3 = 10 億次組合嘗試
-- 有索引：掃描 t1 (1000) + t2 索引查找 (log N) + t3 索引查找 (log N)
-- 理論提速百萬倍

四、MySQL 使用索引的四大場景

1. WHERE/JOIN 匹配： 加速行定位
2. MIN/MAX： 直接讀索引首/尾
3. ORDER BY/GROUP BY： 避免 filesort
4. 覆蓋索引： SELECT indexed_col FROM t 僅讀索引文件

五、索引代價：寫入開銷與空間佔用

原則： 不需要的索引堅決不建，定期 pt-index-usage 清理零使用索引。

六、索引選擇六大黃金法則

1. 用於 WHERE/JOIN/ORDER BY/GROUP BY 的列，而非 SELECT 輸出列
2. 高區分度優先： Cardinality / Rows > 0.3，主鍵 > 唯一 > 普通，枚舉/性別低區分度不建
3. 短索引： VARCHAR(255) 索引前 10-20 字符，INT < BIGINT
4. 最左前綴： 組合索引 (a,b,c) 實際支援 a/a,b/a,b,c，不可跳列
5. 不重複建立： 已有 (a,b,c) 勿再建 (a)/(a,b)
6. 類型匹配： 精確匹配 (=, <=>, IN) 可用 Hash 索引 (MEMORY)；範圍 (<, >, >=, <=, BETWEEN, LIKE 'prefix%') 必須 B-Tree

七、索引類型選擇：Hash vs B-Tree

-- MEMORY 表指定 B-Tree
CREATE TABLE lookup (
  id INT NOT NULL,
  name CHAR(20),
  PRIMARY KEY USING BTREE (id)
) ENGINE=MEMORY;

八、慢查詢日誌驅動優化閉環

# 1. 開啟慢查詢日誌
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  -- 秒
log_queries_not_using_indexes = 1

# 2. 分析工具
pt-query-digest /var/log/mysql/slow.log
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 3. 關注指標
-- Query_time: 總耗時
-- Lock_time: 鎖等待
-- Rows_sent/Rows_examined: 返回/掃描行數比 (越低越好)
-- Query: 具體 SQL

# 4. 優化動作
-- 加索引、改 SQL、調參數、分表、引入緩存

九、實戰避坑檢查清單

• [ ] 所有外鍵列建立索引
• [ ] 組合索引遵循最左前綴，無跳列查詢
• [ ] 避免在索引列上使用函數/運算 (YEAR(date)=2023 → date >= '2023-01-01')
• [ ] LIKE '%keyword%'` 改全文索引或 ES
• [ ] 定期 ANALYZE TABLE 更新統計信息
• [ ] 監控 Handler_read_rnd_next (全表掃描計數) 異常增長
• [ ] 單表索引數控制：核心表 ≤ 5，非核心 ≤ 3