您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 索引優化深度解析：B+樹原理、覆蓋索引、最左前綴與 key_len 估算實戰

編輯：MySQL 資料庫相關知識

前言

索引是 MySQL 效能的靈魂。InnoDB 採用 B+樹作為索引結構，理解其頁面分裂、聚簇索引與二級索引雙層查找機制，能指導我們設計高效索引、避免隱式轉換、解決排序與分組性能瓶頸。本文從底層原理出發，結合 EXPLAIN 實戰案例，系統講解索引優化核心知識點。

一、InnoDB B+樹索引結構

1.1 聚簇索引

主鍵即聚簇索引，葉子節點**直接儲存完整行資料**。
表資料按主鍵順序物理儲存，**一張表只能有一個聚簇索引**。
若無主鍵，InnoDB 選擇唯一非空索引；若無，內建 6 字節 row_id 隱藏主鍵。

1.2 二級索引

葉子節點儲存 **索引列值 + 主鍵值**，不儲存完整行。
查詢走二級索引時：**先查二級索引找主鍵 → 回表查聚簇索引取完整行**（回表）。
優勢：頁面分裂時主鍵值不變，二級索引無需更新行指標。

1.3 頁面與分裂

預設頁面大小 16KB，分裂閾值 15/16 (≈93.75%)。
**自增主鍵**：新行追加到最右頁，無分裂、無碎片、寫入極快。
**UUID/非單調鍵值**：隨機插入導致頻繁分裂、碎片率高、寫入放大。

二、複合索引與最左前綴原則

2.1 定義

ALTER TABLE staffs ADD INDEX idx_nap (name, age, pos);

索引鍵值順序：(name, age, pos) 依序比較，類似字典序。

2.2 可用的查詢模式

SQL 範例	使用索引列	說明
`WHERE name='A' AND age=25 AND pos='dev'`	name, age, pos	全值匹配
`WHERE name='A' AND age=25`	name, age	最左前綴
`WHERE name='A'`	name	最左單列
`WHERE name LIKE 'A%'`	name	前綴模糊匹配
`WHERE name='A' AND age>25`	name, age	精確匹配 + 範圍（範圍列之後不可用）
`SELECT name,age FROM ... WHERE name='A'`	name (覆蓋索引)	只讀索引列，無需回表

2.3 不可用的反例

WHERE age=25 — 跳過最左列
WHERE name='A' AND pos='dev' — 跳過中間列
WHERE name>'A' AND age=25 — 範圍列在前，後列失效
WHERE SUBSTR(name,1,1)='A' — 索引列加函數/運算，失效
WHERE name LIKE '%A' — 非前綴模糊，全掃

三、覆蓋索引

定義：查詢只需掃描索引即可獲得所有所需列，無需回表。
識別：EXPLAIN 的 Extra 欄顯示 Using index。
設計技巧：將 SELECT 列順序納入複合索引尾部，例如 INDEX (status, created_at, id, title, content) 覆蓋 SELECT id,title,content FROM t WHERE status=1 ORDER BY created_at。

四、key_len 估算實戰

key_len 表示本次查詢實際使用的索引位元組長度，可反推用到哪些列。

資料型別	基礎長度	NULL 可否	實際 key_len
INT / MEDIUMINT	4 / 3	+1	5 / 4
BIGINT	8	+1	9
CHAR(N) UTF8MB4	3N	+1	3N+1
VARCHAR(N) UTF8MB4	3N+2	+1	3N+3
DATE / DATETIME	3 / 5	+1	4 / 6

實例：INDEX idx_nap (name VARCHAR(20), age INT, pos CHAR(10))，UTF8MB4。
WHERE name='A' AND age=25 → key_len = (3*20+3) + 5 = 68，確認用了前兩列。

五、索引下推

MySQL 5.6+ 引入 ICP：儲存引擎層在讀取索引時，直接評估 WHERE 中可用的索引列條件，過濾後再回表，減少回表次數。
EXPLAIN 顯示 Using index condition。

六、排序與分組優化

ORDER BY / GROUP BY 遵循最左前綴，且列順序、方向（ASC/DESC）需一致。
INDEX (a,b) 支援 ORDER BY a,b、ORDER BY a DESC, b DESC，**不支援** ORDER BY a ASC, b DESC（MySQL 8.0 支援降序索引）。
若 WHERE 固定前綴列，ORDER BY 可用後續列：WHERE a=1 ORDER BY b。

七、前綴索引選擇性計算

-- 計算前 N 字元選擇性
SELECT
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
  COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel_15,
  COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel_20
FROM users;
-- 選擇性接近 1.0 時，該長度足夠代表全列
ALTER TABLE users ADD INDEX idx_email_prefix (email(15));

八、常見反模式與修正

反模式	影響	修正
過度索引（單表 >5 個）	寫入放大、優化器選擇困難、空間浪費	定期 `pt-index-usage` 分析，刪除未用索引
隱式轉換 `WHERE varchar_col = 123`	索引失效、全表掃描	應用層傳字串、或加 `CAST`
大字段建索引 `INDEX (long_text)`	索引極大、維護成本高	改用前綴索引或全文索引
外鍵索引缺失	子表刪除/更新需鎖定父表全表掃描	明確建立外鍵列索引