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

MySQL 索引原理與應用實戰完全指南

編輯：MySQL 資料庫相關知識

索引核心概念

索引是資料庫提升查詢效能的關鍵結構，類比書籍目錄：無需掃描全書即可定位內容。在 MySQL 中，索引屬於儲存引擎層實作，不同引擎（InnoDB、MyISAM）的索引結構略有差異。

索引類型與特性

分類	類型	建立語法	特點
按功能	主鍵索引	`PRIMARY KEY (id)`	唯一、非空、叢集索引（InnoDB）
按功能	唯一索引	`UNIQUE INDEX uk_email (email)`	值唯一、允許一個 NULL
按結構	單欄索引	`INDEX idx_name (name)`	單一欄位、最常用
	複合索引	`INDEX idx_name_age (name, age)`	多欄位、遵循最左前綴原則
	全文索引	`FULLTEXT INDEX ft_content (content)`	支援自然語言/布林模式搜尋
按儲存	叢集索引	InnoDB 主鍵自動建立	資料與索引共存、葉節點即資料頁
按儲存	非叢集索引	其餘所有索引	葉節點存主鍵值、需回表查資料

InnoDB 索引深度解析

叢集索引（Clustered Index）

表資料按主鍵順序儲存（B+ 樹葉節點即資料行）
每表僅有一個叢集索引（主鍵或首個非空唯一索引）
主鍵應盡量使用自增整數，避免 UUID 導致頁分裂

二級索引

葉節點存儲主鍵值而非資料指標
查詢流程：二級索引定位主鍵 → 回表查詢叢集索引取資料
覆蓋索引：查詢欄位全包含於索引中，避免回表，效能大增

複合索引與最左前綴原則

INDEX idx_a_b_c (a, b, c)

等價於建立：(a)、(a,b)、(a,b,c) 三個索引。

查詢條件	是否用到索引	說明
`WHERE a=1`	✅ 是	最左列匹配
`WHERE a=1 AND b=2`	✅ 是	最左兩列匹配
`WHERE b=2`	❌ 否	非最左列，無法使用
`WHERE a=1 AND c=3`	部分	僅用到 `a`，`c` 無法用到
`WHERE a=1 ORDER BY b`	✅ 是	排序也能用到索引順序

索引失效常見場景

對索引列運算：WHERE YEAR(create_time)=2023 → 改用 WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'
隱含型別轉換：WHERE varchar_col = 123 → 字串比數字會觸發全表掃描
LIKE 以萬用字元開頭：WHERE name LIKE '%abc' → 無法使用索引
OR 條件未全覆蓋索引：WHERE a=1 OR b=2 → 若 b 無索引則全表掃描
IS NULL/IS NOT NULL：某版本下可能失效，建議用預設值+索引

索引設計最佳實踐

高選擇性優先：唯一值比例高的欄位（如身分證、手機號）優先建索引
複合索引替代單索引：INDEX(a,b) 優於 INDEX(a) + INDEX(b)
覆蓋索引優化：常用查詢欄位納入索引，避免回表
控制索引數量：單表建議 ≤ 5 個索引，過多拖慢寫入
定期維護：ANALYZE TABLE 更新統計資訊，OPTIMIZE TABLE 整理碎片

實戰案例：優化訂單查詢

-- 原查詢：慢
SELECT * FROM orders WHERE user_id=1001 AND status=1 ORDER BY create_time DESC LIMIT 10;

-- 優化：建立複合索引
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

-- 覆蓋索引進階（若只查特定欄位）
ALTER TABLE orders ADD INDEX idx_cover (user_id, status, create_time, order_no, amount);

掌握索引原理、善用 EXPLAIN 分析、避開失效陷阱，是 MySQL 效能調優的核心功力。