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

MySQL 索引最佳實踐：單列/組合索引、建立時機、避坑指南

編輯：MySQL 綜合教學

索引基礎：為什麼需要索引？

表 mytable(id, username) 1 萬筆資料，查 username='admin'：

有索引：直接定位，毫秒級
無索引：全表掃描 1 萬行，明顯變慢

索引類型與建立語法

類型	特點	建立語法範例
普通索引	無限制，允許 NULL	`CREATE INDEX idx_name ON t(name(10));` `ALTER TABLE t ADD INDEX idx_name (name(10));`
唯一索引	值唯一，允許 NULL	`CREATE UNIQUE INDEX uk_email ON t(email);`
主鍵索引	唯一+非空，一表僅一個	`CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT);`
組合索引	多欄位組合，遵循最左前綴	`ALTER TABLE t ADD INDEX idx_name_city_age (name(10), city, age);`

組合索引核心原則：最左前綴

索引 (name, city, age) 等價於擁有：

(name, city, age)
(name, city)
(name)

不會用到： (city, age)、(age)、(city) — 因為未包含最左列 name。

何時建立索引？

出現在 WHERE、JOIN ON、ORDER BY、GROUP BY 的欄位
運算子：=, >, >=, <, <=, BETWEEN, IN, LIKE 'prefix%'
不會用到索引： LIKE '%suffix', LIKE '%mid%', NOT IN, <>, 函數運算 (如 YEAR(date))、隱含型別轉換

索引的代價（缺點）

寫入效能下降：INSERT/UPDATE/DELETE 需同步維護索引樹
佔用磁碟空間：每個索引一棵 B+ 樹
優化器負擔：索引過多導致執行計畫選擇變慢、甚至選錯

實戰技巧與避坑

技巧	說明	正確範例	錯誤範例
短索引	字串只索引前綴長度	`INDEX idx_name (name(10))`	`INDEX idx_name (name(255))`
避免 NULL	含 NULL 列不進索引，組合索引失效	`NOT NULL DEFAULT ''`	預設 NULL
不在索引列運算	函數/運算導致索引失效	`WHERE create_time > '2023-01-01'`	`WHERE YEAR(create_time) > 2023`
用 NOT EXISTS 代替 NOT IN	`NOT IN` 不走索引	`WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id=a.id)`	`WHERE id NOT IN (SELECT id FROM b)`
`<>` 改寫	不走索引	`WHERE id > 3 OR id < 3`	`WHERE id <> 3`
ORDER BY 用索引順序	避免 filesort	索引 `(a,b)` → `ORDER BY a,b`	`ORDER BY b,a`

分析索引效率：EXPLAIN

EXPLAIN SELECT * FROM t WHERE name='admin' AND city='Taipei';
關鍵欄位：
- type: ref/range/ALL (ref 最理想)
- possible_keys: 可能用到的索引
- key: 實際使用的索引
- key_len: 使用的索引長度
- rows: 預估掃描行數
- Extra: Using index / Using where / Using filesort 等建議

索引只是優化因素之一，大數據量還需配合 SQL 改寫、分區表、讀寫分離、快取等綜合手段。