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

MySQL 索引深度解析：類型、建立時機、失效場景與最佳實踐

編輯：MySQL 綜合教學

前言

索引是資料庫效能的靈魂。MySQL 支援多種索引類型，理解其底層結構 (B+ Tree)、建立時機、失效條件，才能做出正確的索引設計決策。本文深度解析 MySQL 索引機制與實戰應用。

一、索引底層結構：B+ Tree

InnoDB：聚簇索引 (主鍵即資料)、非聚簇索引葉節點存主鍵值、雙向鏈表支援範圍掃描
MyISAM：非聚簇索引、葉節點存資料指標、主鍵與二級索引結構相同
優勢：對數級查找、範圍查詢高效、順序讀取友善

二、索引類型完整分類

1. 普通索引 (INDEX / KEY)

CREATE INDEX idx_name ON users(name);
ALTER TABLE users ADD INDEX idx_name (name);
CREATE TABLE users (id INT, name VARCHAR(50), INDEX idx_name (name));

允許重複、允許 NULL、最基礎索引。

2. 唯一索引 (UNIQUE INDEX)

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

值唯一、允許多個 NULL (MySQL 特性)、可多個。

3. 主鍵索引 (PRIMARY KEY)

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
ALTER TABLE users ADD PRIMARY KEY (id);

特殊的唯一索引：不允許 NULL、每表僅一個
InnoDB：主鍵即聚簇索引，資料按主鍵順序儲存
無主鍵時：InnoDB 選第一個非空唯一索引，否則產生隱藏 row_id (6 bytes)

4. 組合索引 (Composite Index)

ALTER TABLE users ADD INDEX idx_name_city_age (name(10), city, age);

遵循最左前綴原則：(a,b,c) 支援 a / a,b / a,b,c
順序決定性：高選擇性、常用於等值查詢的欄位放前面
等同於建立：(a)、(a,b)、(a,b,c) 三個索引

5. 全文索引 (FULLTEXT)

ALTER TABLE articles ADD FULLTEXT ft_content (content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('關鍵字');

MyISAM 早期支援，InnoDB 5.6+ 支援
中文需配置 ngram parser (5.7.6+)

6. 空間索引 (SPATIAL)

ALTER TABLE geom ADD SPATIAL INDEX sp_idx (location);

GIS 空間資料類型 (POINT, POLYGON 等)
InnoDB 5.7+ 支援

三、建立索引的最佳時機

✅ 應建立索引

WHERE 條件欄位 (=, >, <, >=, <=, BETWEEN, IN, LIKE 'prefix%')
JOIN 關聯欄位 (ON t1.id = t2.id)
ORDER BY / GROUP BY 欄位
高選擇性欄位 (基數/總行數 > 0.1 經驗值)
外鍵欄位 (InnoDB 自動建立，MyISAM 需手動)

❌ 不宜建立索引

低選擇性欄位 (性別、狀態、類型、布林值)
極少查詢的欄位
頻繁大量 UPDATE/INSERT 的欄位 (維護成本 > 查詢收益)
長文本欄位 (BLOB/TEXT 必須前綴，效果有限)
小表 (全表掃描更快，通常 < 1000 行)

四、索引失效常見場景 (反模式)

場景	SQL 範例	原因
函數/運算在索引列	WHERE YEAR(dt) = 2024	索引存原始值，函數破壞有序性
隱式類型轉換	WHERE varchar_col = 123	MySQL 轉換欄位值，等同函數操作
LIKE 以 % 開頭	WHERE name LIKE '%abc'	B+ Tree 只支援前綴匹配
OR 條件欄位無索引	WHERE a=1 OR b=2 (b 無索引)	MySQL 5.6+ Index Merge 有條件支援，但通常全掃
組合索引違反最左前綴	WHERE b=2 (索引 a,b)	缺少最左列 a
IS NULL / IS NOT NULL	WHERE col IS NULL	5.6+ 可用索引，但選擇性通常極低
NOT IN / NOT EXISTS	WHERE id NOT IN (subquery)	難以利用索引，改寫 LEFT JOIN IS NULL
字元集/校對規則不匹配	JOIN 兩表欄位 charset 不同	隱式轉換導致索引失效

五、索引維護與監控

1. 查看索引使用情況

SHOW INDEX FROM users;
-- 或
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'users';

2. 索引基數統計

ANALYZE TABLE users;  -- 更新統計資訊
SHOW INDEX FROM users;  -- Cardinality 欄位查看基數

3. 找出未使用索引 (MySQL 5.6+)

SELECT * FROM sys.schema_unused_indexes;

4. 找出重複/冗餘索引

SELECT * FROM sys.schema_redundant_indexes;

六、大表索引策略

1. 線上建立索引 (Online DDL)

-- MySQL 5.6+ 預設線上
ALTER TABLE big_table ADD INDEX idx_col (col), ALGORITHM=INPLACE, LOCK=NONE;

-- 強制複製表 (舊版/特定操作)
ALTER TABLE big_table ADD INDEX idx_col (col), ALGORITHM=COPY;