萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 索引核心原理與實戰優化:B+Tree、最左前綴、覆蓋索引全解析

索引底層結構:B+Tree 為什麼是首選?

  • B-Tree:所有節點存 key+value,葉子節點同層無鏈接。
  • B+Tree(InnoDB 預設):
    1. 非葉子節點只存 key,一頁能裝更多 key → 樹高更矮(通常 3 層存千萬行)。
    2. 葉子節點存完整行數據(聚簇索引)或主鍵值(二級索引),並雙向鏈表連接 → 範圍查詢、全表掃描、ORDER BY 極快。
    3. 所有查詢最多走 樹高 h 次磁碟 I/O,穩定可預期。

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

聯合索引 INDEX (a, b, c) 等價於建立三個索引:(a)、(a,b)、(a,b,c)。
查詢必須從最左列開始連續匹配,遇到範圍查詢(>, <, >=, <=, LIKE '%...', BETWEEN)即停止匹配後續列。

WHERE 條件能否用到索引 (a,b,c)用到哪幾列
a=1 AND b=2 AND c=3✅a,b,c
a=1 AND b=2✅a,b
a=1 AND c=3✅a(b 缺失中斷)
b=2 AND c=3❌無(缺最左列 a)
a=1 AND b>2 AND c=3✅a,b(c 被範圍中斷)
a=1 AND b LIKE 'abc%'✅a,b(前綴 LIKE 視為等值/範圍邊界)
a=1 AND b LIKE '%abc'✅a(通配符開頭導致範圍掃描,中斷 c)

可使用 B+Tree 索引的查詢類型

  1. 全值匹配: WHERE a=1 AND b=2
  2. 最左前綴匹配: WHERE a=1
  3. 列前綴匹配: WHERE name LIKE 'Zhang%'(不可 %Zhang)
  4. 範圍查詢: WHERE a > 10 AND a < 100
  5. 精確匹配一列 + 範圍匹配另一列: WHERE a=1 AND b>10
  6. 覆蓋索引掃描: SELECT a,b FROM t WHERE a=1 索引包含所有所需列,無需回表。
  7. ORDER BY / GROUP BY 優化: 索引列順序與排序/分組順序一致時,可避免 filesort。

索引建立黃金法則

  1. 最左前綴: 聯合索引列順序按「區分度高 → 低」或「查詢頻率高 → 低」排列,範圍列放最後。
  2. = 和 IN 可亂序: 優化器會自動重排 a=1 AND b=2 AND c=3 以匹配索引順序。
  3. 高區分度優先: count(DISTINCT col)/count(*) 越接近 1 越好,掃描行數越少。
  4. 索引列不參與運算: ❌ WHERE date(created_at)='...' → ✅ WHERE created_at >= '...' AND created_at < '...'。
  5. 覆蓋索引: SELECT id,status FROM t WHERE id=2 + INDEX(id,status) 直接從索引返回,避免回表。
  6. DML 同樣需索引: DELETE/UPDATE 的 WHERE 條件列必須有索引,否則全表鎖/掃描。
  7. 小表驅動大表: JOIN 時用小結果集驅動大表,確保驅動表關聯列有索引。
  8. 少用子查詢: 改寫為 JOIN 或 EXISTS。

實戰反模式(導致索引失效)


-- 1. 索引列參與運算
SELECT id FROM t WHERE id+1 = 5;              -- ❌
SELECT id FROM t WHERE id = 5-1;              -- ✅

-- 2. 索引列用函數
SELECT * FROM t WHERE to_days(now()) - to_days(gmt_created) <= 10; -- ❌
SELECT * FROM t WHERE gmt_created >= DATE_SUB(now(), INTERVAL 10 DAY); -- ✅

-- 3. 隱式類型轉換
SELECT * FROM t WHERE phone = 13800138000;    -- ❌ phone 是 varchar,數字轉字串導致索引失效
SELECT * FROM t WHERE phone = '13800138000';  -- ✅

索引維護成本認知

  • 每個索引約增加 5-15% 寫入開銷(INSERT/UPDATE/DELETE 需維護 B+Tree)。
  • 單表索引建議 ≤ 5 個,核心表 ≤ 3 個。
  • 定期 ANALYZE TABLE 更新統計資訊,避免優化器誤判。
  • 監控 Handler_read_rnd_next(全表掃描計數),飆升即缺索引。

總結

MySQL 索引本質是 B+Tree 有序結構,掌握「最左前綴、區分度、覆蓋索引、不參與運算」四大核心原則,配合 EXPLAIN 驗證、慢查詢日誌分析、定期統計更新,即可解決 90% 以上的查詢效能問題。索引不是越多越好,精準、最小、覆蓋 才是王道。

copyright © 萬盛學電腦網 all rights reserved