MySQL 索引類型全解析：從普通到全文，選型與避坑指南

MySQL 索引類型全解析與選型指南

索引是查詢性能的核心。MySQL (InnoDB) 支持多種索引類型，各有適用場景與限制。本文系統梳理六大類索引特性、創建語法、選型決策樹、常見誤區。

InnoDB 索引底層結構：B+ 樹

• 聚簇索引 (Clustered Index) = 主鍵索引，葉子節點存儲完整行數據。
• 二級索引 (Secondary Index) 葉子節點存儲主鍵值，回表查詢需兩次 B+ 樹查找。
• 覆蓋索引：查詢欄位全部包含在二級索引中，避免回表，性能提升數量級。

六大索引類型詳解

1. 普通索引 (Normal Index / KEY / INDEX)

CREATE INDEX idx_name ON `table` (`col`);
ALTER TABLE `table` ADD INDEX idx_name (`col`);

• 唯一任務：加速 WHERE/JOIN/ORDER BY/GROUP BY。
• 允許重複值、允許 NULL。
• 選擇性高 (基數大) 的欄位效果最好 (如用戶 ID、郵箱、手機號)。

2. 唯一索引 (Unique Index / UNIQUE KEY)

CREATE UNIQUE INDEX uk_email ON `users` (`email`);
ALTER TABLE `users` ADD UNIQUE KEY uk_email (`email`);

• 功能 = 普通索引 + 業務約束 (防止重複數據)。
• InnoDB 內部同樣為 B+ 樹，查詢性能與普通索引無異。
• 業務唯一鍵必建唯一索引：用戶名、郵箱、手機、身份證、訂單號等。

3. 主鍵索引 (Primary Key / PRIMARY KEY)

PRIMARY KEY (`id`)
ALTER TABLE `table` ADD PRIMARY KEY (`id`);

• 表只能有一個主鍵，隱含 NOT NULL + UNIQUE。
• InnoDB 必須有主鍵 (無顯式則選首個非空唯一鍵，否則生成 6 字節隱藏 row_id)。
• 推薦：BIGINT UNSIGNED NOT NULL AUTO_INCREMENT (單調遞增，頁分裂少，二級索引小)。

4. 復合索引 / 聯合索引 (Composite Index)

CREATE INDEX idx_a_b_c ON `table` (`a`, `b`, `c`);

• 最左前綴原則：索引 (a,b,c) 可支持 a | a,b | a,b,c 三種查詢；不支持 b、c、b,c、a,c (跳過 a)。
• 欄位順序核心原則：區分度最高 (基數大) 的放最左，其次考慮範圍查詢欄位放最後 (範圍後面的欄位無法用到索引)。
• 覆蓋索引優化：SELECT a,b FROM table WHERE a=1 AND b=2 直接走索引掃描，無回表。

5. 全文索引 (Full-Text Index)

CREATE FULLTEXT INDEX ft_content ON `articles` (`title`, `body`);
ALTER TABLE `articles` ADD FULLTEXT INDEX ft_content (`title`, `body`);

• 專為自然語言文本搜索設計，倒排索引結構。
• 語法：SELECT * FROM articles WHERE MATCH(title,body) AGAINST('關鍵詞' IN NATURAL LANGUAGE MODE);。
• 限制：5.6+ InnoDB 支持；中文需分詞器 (ngram parser) 或外部引擎 ; 最小詞長 ft_min_word_len (默認 4) 影響短詞搜索。

6. 空間索引 (Spatial Index / SPATIAL INDEX)

ALTER TABLE `geo` ADD SPATIAL INDEX idx_location (`location`);

• 針對 GEOMETRY 類型 (Point, Polygon 等)，R 樹結構。
• 支持 MBRContains, ST_Distance 等空間關係查詢。

索引長度優化 (前綴索引)

-- 長字串欄位只索引前綴，節省空間
CREATE INDEX idx_email_prefix ON `users` (`email`(10));
-- 選擇長度使區分度接近完整欄位
SELECT COUNT(DISTINCT LEFT(email,10))/COUNT(*) FROM users;  -- 目標 > 0.9

• 適用：VARCHAR/CHAR/TEXT/BLOB 長欄位。
• 不可用於覆蓋索引、ORDER BY、外鍵引用。

索引選型決策樹

1. 是否為主鍵？→ PRIMARY KEY (自增 BIGINT)。
2. 業務上是否唯一？→ UNIQUE KEY (兼具查詢加速)。
3. 多欄位聯合查詢？→ 復合索引 (遵循最左前綴、區分度排序)。
4. 全文搜索需求？→ FULLTEXT + ngram parser (中文) 或 Elasticsearch (專業場景)。
5. 地理空間查詢？→ SPATIAL INDEX。
6. 單欄普通查詢？→ 普通 INDEX。

常見誤區與避坑

索引維護工具箱

-- 查看表索引
SHOW INDEX FROM `table`;

-- 分析未使用索引 (sys schema, 5.7+)
SELECT * FROM sys.schema_unused_indexes;

-- 分析索引基數
ANALYZE TABLE `table`;

-- 強制/忽略索引 (調試用)
SELECT * FROM `table` FORCE INDEX (idx_name) WHERE ...;
SELECT * FROM `table` IGNORE INDEX (idx_name) WHERE ...;