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

MySQL 索引設計與實戰避坑指南：從 B+ 樹原理到最左前綴、覆蓋索引與全文檢索

編輯：MySQL 資料庫相關知識

為什麼需要索引？

無索引 = 全表掃描（Full Table Scan）。資料量 10 萬行時，查詢可能掃描 10 萬行；有索引（B+ 樹），只需 O(log N) 次磁碟 I/O 定位到目標行。索引是關係型資料庫效能的基石。

索引類型與適用場景

類型	關鍵字	特點	適用場景
主鍵索引	PRIMARY KEY	唯一、非空、聚簇索引（InnoDB）	每張表必須有，業務無意義自增 ID 最佳
唯一索引	UNIQUE KEY	值唯一，允許一個 NULL	業務鍵：身分證、手機、Email
普通索引	KEY / INDEX	允許重複、允許 NULL	WHERE、JOIN、ORDER BY 欄位
全文索引	FULLTEXT	倒排索引，支援自然語言/布林模式	文章、日誌、評論內容搜尋（MySQL 5.6+ InnoDB 支援）
空間索引	SPATIAL	R 樹，GIS 資料	地理位置、幾何圖形查詢

索引設計六大黃金法則

1. 為 WHERE、JOIN、ORDER BY 欄位建索引——而非 SELECT 列

-- 慢：全表掃描
SELECT * FROM orders WHERE user_id = 100 AND status = 1;

-- 快：建立複合索引 (user_id, status)
ALTER TABLE orders ADD INDEX idx_uid_status (user_id, status);

出現在 SELECT 列表的欄位不一定要建索引；出現在 WHERE、ON、GROUP BY、ORDER BY 的欄位才是候選者。

2. 高選擇性優先——區分度越高效果越好

選擇性 = COUNT(DISTINCT col) / COUNT(*)。
gender（M/F）選擇性 ≈ 0.5 → 索引幾乎無效；
phone 選擇性 ≈ 1.0 → 索引極其高效。
原則：複合索引將高選擇性欄位放最左。

3. 使用短索引（前綴索引）——節省空間、提升快取命中

-- VARCHAR(200) 欄位，前 20 字元區分度已足夠
ALTER TABLE articles ADD INDEX idx_title_prefix (title(20));

減少索引檔案體積
InnoDB 頁面能容納更多索引鍵值 → 記憶體命中率↑
比較操作更快（字串比較長度變短）

4. 善用最左前綴原則——一個複合索引頂多個單列索引

INDEX idx_a_b_c (a, b, c)
-- 可服務以下查詢：
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 ORDER BY b
-- 無法服務：
WHERE b = 2           -- 缺少最左列 a
WHERE a = 1 AND c = 3 -- 中間斷層，c 用不上索引

5. 覆蓋索引——避免回表，查詢只掃索引樹

-- 索引包含所有查詢欄位
INDEX idx_uid_status_name (user_id, status, name)

-- 查詢只需掃描索引樹，不回聚簇索引
SELECT name FROM orders WHERE user_id = 100 AND status = 1;
-- EXPLAIN Extra: Using index

6. 避免索引失效的常見寫法

寫法	原因	修正
`WHERE col LIKE '%abc%'`	前導萬用字元，B+ 樹無法定位	改用全文索引或 `LIKE 'abc%'`
`WHERE col + 1 = 10` / `WHERE UPPER(col) = 'A'`	索引列參與運算/函數	改寫為 `col = 9`、儲存大寫或函數索引（8.0+）
`WHERE a = 1 OR b = 2`	OR 兩側索引不同，通常全掃	`UNION ALL` 拆分或建立複合索引
隱式轉型 `WHERE varchar_col = 123`	字串比數字，MySQL 轉型導致索引失效	`WHERE varchar_col = '123'`

全文檢索實戰

-- 建表時指定
CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;

-- 自然語言模式（預設，按相關性排序）
SELECT * FROM posts 
WHERE MATCH(title, body) AGAINST('MySQL 優化' IN NATURAL LANGUAGE MODE);

-- 布林模式（精確控制）
SELECT * FROM posts 
WHERE MATCH(title, body) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
-- + 必須包含，- 必須不包含，* 通配符

注意：MySQL 內建全文檢索對中文支援有限（需 ngram parser，5.7.6+），生產環境建議接入 Elasticsearch 或 Meilisearch。