您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 索引底層原理深度解析（下）：最左前綴、EXPLAIN 詳解、前綴索引與 InnoDB 主鍵設計鐵律

編輯：MySQL 綜合教學

前言：以 employees 庫為實戰沙箱

本文承接上篇，使用 MySQL 官方 employees 示例庫（44 萬+ 行），通過實際 EXPLAIN 執行計劃，深度講解 **聯合索引最左前綴原理**、**EXPLAIN 關鍵列解讀**、**索引選擇性與前綴索引**、**InnoDB 主鍵選擇對插入性能的決定性影響**。

一、最左前綴原理：聯合索引的「生存法則」

實驗表 employees.titles，主鍵 PRIMARY KEY (emp_no, title, from_date)。刪除輔助索引後，僅保留主鍵進行純淨分析。

七大匹配場景實測

場景	SQL 片段	Key_len	Type	索引使用	核心結論
1. 全列精確匹配	`WHERE emp_no=... AND title=... AND from_date=...`	59	const	✅ 全用	順序無關，優化器自動調整
2. 最左前綴匹配	`WHERE emp_no=...`	4	ref	✅ 僅第一列	key_len=4 對應 emp_no(int)
3. 中間列缺失	`WHERE emp_no=... AND from_date=...`	4	ref	⚠️ 僅第一列	title 缺失斷開前綴，from_date 退化為 WHERE 過濾
4. 無第一列	`WHERE from_date=...`	NULL	ALL	❌ 全表掃描	非最左前綴，索引失效
5. LIKE 前綴匹配	`WHERE emp_no=... AND title LIKE 'Senior%'`	56	range	✅ 兩列	% 不在開頭可用索引
6. 範圍查詢	`WHERE emp_no < ... AND title=...`	4	range	⚠️ 僅第一列	範圍列後的列無法用索引
7. 函數/表達式	`WHERE left(title,6)='Senior'`	4	ref	❌ 僅第一列	函數導致索引失效，需改用 LIKE

「隔離列」優化技巧：用 IN 填坑

場景 3 中 title 只有 7 個枚舉值，可用 IN 湊齊最左前綴：

WHERE emp_no=... 
  AND title IN ('Senior Engineer','Staff',...)  -- 填滿 title
  AND from_date=...

實測 key_len=59，**三列全用**，性能提升顯著。但枚舉值多時不適用，需建輔助索引 (emp_no, from_date)。

二、EXPLAIN 深度解讀：看懂執行計劃的「CT報告」

列	含義	重點關注
`id`	SELECT 序列號	同 id 從上往下；衍生表 `<derivedN>` 指向 id=N
`select_type`	查詢類型	SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION/UNION RESULT
`type`	訪問類型（核心）	system > const > eq_ref > ref > range > index > ALL，至少達 `range`，最好 `ref`
`possible_keys`	可能用到的索引	為 NULL 表示無索引可用
`key`	實際用到的索引	為 NULL 表示全表掃描
`key_len`	索引長度（字節）	推斷聯合索引用了幾列（int=4, date=3, varchar(N)=3N+2）
`ref`	與 key 配合的列/常量	`const` 表示常量
`rows`	預估掃描行數	越小越好，InnoDB 為估算值
`Extra`	額外信息	`Using index`（覆蓋索引）、`Using where`（後過濾）、`Using filesort`、`Using temporary`

filesort 兩種算法

雙路排序：先取排序字段+行指針，排序後回表。IO 多，佔用 sort_buffer_size 少。
單路排序：一次取所有字段，內存排序。IO 少，佔用 sort_buffer_size 多。
MySQL 根據 max_length_for_sort_data（默認 1024）與 SELECT 列總長度決定算法。

三、索引選擇性與前綴索引：空間與速度的平衡術

選擇性 = Cardinality / 總行數，越接近 1 越好。

title 選擇性 < 0.0001 → **不建索引**。
employees.employees 按名字搜索：first_name 選擇性 0.0042（太低），(first_name, last_name) 0.9313（高但長 30 字節）。

前綴索引實戰：`first_name + last_name 前 4 字符`

SELECT COUNT(DISTINCT(CONCAT(first_name, LEFT(last_name, 4)))) / COUNT(*) 
FROM employees.employees;  -- 選擇性 0.9007，長度 18 字節

ALTER TABLE employees.employees 
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

**效果**：查詢耗時從 0.119s 降至 0.0009s，提速 120+ 倍。

⚠️ **前綴索引限制**：不可用於 ORDER BY、GROUP BY、Covering Index。

四、InnoDB 主鍵選擇鐵律：自增主鍵不是建議，是生存法則

為什麼非自增主鍵會拖垮性能？

InnoDB **聚簇索引**：數據行物理順序 = 主鍵順序，葉子節點存全行數據。

自增主鍵：新行順序追加到當前頁末尾，頁滿開新頁 → **緊湊、無碎片、無數據移動**，插入極快。
業務主鍵（身份證、UUID、雪花 ID）：值隨機，新行插入現有頁中間 → **頁分裂、數據移動、碎片化**、甚至觸發磁盤刷盤/讀回，**寫入性能崩塌**，後續需 OPTIMIZE TABLE 整理。

最佳實踐

CREATE TABLE t (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- 與業務無關
  biz_id VARCHAR(32) NOT NULL,  -- 業務唯一鍵，加 UNIQUE INDEX
  ...
) ENGINE=InnoDB;