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

MySQL 索引全解析：類型、建立、最左前綴、覆蓋索引與避坑指南

編輯：MySQL 綜合教學

MySQL 索引全解析：類型、建立、最左前綴、覆蓋索引與避坑指南

索引是查詢效能的倍增器，也是寫入效能的成本中心。本文系統梳理 MySQL 索引類型、建立語法、複合索引最左前綴原理、覆蓋索引優化、外鍵索引關聯、以及常見失效場景與避坑清單。

索引類型對照

類型	關鍵字	約束	適用場景
主鍵	PRIMARY KEY	唯一 + NOT NULL，單表僅一個	業務主鍵、聚簇索引基礎
唯一	UNIQUE KEY	唯一，允許多個 NULL	業務唯一鍵 (手機、身分證、Email)
全文	FULLTEXT	MyISAM/InnoDB 5.6+，僅 CHAR/VARCHAR/TEXT	中文需配合 ngram parser
普通	INDEX / KEY	無約束	WHERE/JOIN/ORDER BY 加速
空間	SPATIAL	GIS 資料類型	地理位置查詢

建立語法速查

-- 建表時
CREATE TABLE t (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100),
  status TINYINT DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uk_email (email),
  KEY idx_status_name (status, name)   -- 複合索引
) ENGINE=InnoDB;

-- 事後新增
ALTER TABLE t ADD INDEX idx_name (name(20));        -- 前綴索引
ALTER TABLE t ADD UNIQUE KEY uk_phone (phone);
ALTER TABLE t ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram;

-- 獨立建立 (不支援 PRIMARY KEY)
CREATE INDEX idx_email ON t(email);
CREATE UNIQUE INDEX uk_phone ON t(phone);
CREATE FULLTEXT INDEX ft_title ON t(title) WITH PARSER ngram;

複合索引：最左前綴原理 (B+Tree 結構決定)

索引 KEY idx_a_b_c (a, b, c) 實際排序順序：a → b → c。

查詢條件	能否用到索引	用到的列
WHERE a=1	✅	a
WHERE a=1 AND b=2	✅	a, b
WHERE a=1 AND b=2 AND c=3	✅	a, b, c
WHERE b=2	❌ (全表掃描)	-
WHERE a=1 AND c=3	⚠️ 僅用 a	a
WHERE a=1 ORDER BY b	✅ (避免 filesort)	a, b
WHERE a=1 ORDER BY c	❌ (b 缺失導致 filesort)	a

覆蓋索引：查詢只走索引不回表

-- 索引
KEY idx_status_name (status, name)

-- 查詢
SELECT name FROM t WHERE status = 1;
-- EXPLAIN: type=ref, key=idx_status_name, Extra=Using index  ✅ 覆蓋索引

-- 若 SELECT * 或 SELECT id, name, email → 需回表查 email，失去覆蓋優勢

設計原則：高頻查詢的 SELECT 列盡量包含在複合索引尾部，實現「索引下推」+「覆蓋索引」雙重紅利。

前綴索引：長字串的空間折中

VARCHAR(500) 全長索引佔空間大、比較慢。
KEY idx_name (name(10)) 僅索引前 10 字符。
選擇長度：SELECT COUNT(DISTINCT LEFT(name, 10))/COUNT(*) FROM t → 選擇性 > 0.9 即可。
BLOB/TEXT 必須指定前綴長度，否則報錯。

引擎差異：InnoDB vs MyISAM vs Memory

引擎	索引結構	主鍵特性	支援索引類型
InnoDB	B+Tree (聚簇)	資料即主鍵索引葉節點	B+Tree, FULLTEXT, SPATIAL
MyISAM	B+Tree (非聚簇)	葉節點存行指標	B+Tree, FULLTEXT, SPATIAL
Memory	Hash (預設) / B+Tree	Hash 只支援 = / IN	Hash, B+Tree

常見失效場景 (避坑清單)

隱性轉換：WHERE phone = 13800000000 (phone 是 VARCHAR) → 索引失效。
函數/運算在索引列：WHERE DATE(created_at) = '2024-06-15' → 改 WHERE created_at >= '2024-06-15' AND created_at < '2024-06-16'。
LIKE 以 % 開頭：LIKE '%keyword' → 全掃；LIKE 'keyword%' 可用索引。
OR 條件未全覆蓋索引：WHERE a=1 OR b=2 → 除非有 KEY (a,b) 且 a,b 都在索引中，否則全掃。
字符集/校對規則不一致：JOIN 關聯欄位 charset/collate 不同 → 隱性轉換失效。
統計資訊過期：ANALYZE TABLE t 更新統計，優化器才能正確選擇索引。

監控與維護

-- 查看索引使用情況
SELECT * FROM sys.schema_unused_indexes;  -- 從未用到的索引 (可考慮刪除)
SELECT * FROM sys.schema_redundant_indexes; -- 重複/多餘索引

-- 碎片整理 (InnoDB)
OPTIMIZE TABLE t;  -- 實際執行 ALTER TABLE ... ENGINE=InnoDB 重建

-- 線上變更結構 (不鎖表)
pt-online-schema-change --alter "ADD INDEX idx_new (col)" D=db,t=t --execute

結語：索引設計是「查詢模式驅動」而非「表結構驅動」。先收集 EXPLAIN、slow log、sys.schema_* 實際負載，再針對性建立複合/覆蓋/前綴索引；定期清理未用索引、更新統計、重建碎片，讓索引持續產出 ROI。