索引是資料庫查詢效能的關鍵。本文系統講解四大索引類型、DDL 操作、組合索引最左前綴原則、8 大常見失效場景、EXPLAIN 執行計劃欄位解讀，並附分頁優化實戰技巧。
| 類型 | 關鍵字 | 特點 | 適用場景 |
|---|---|---|---|
| 普通索引 | INDEX / KEY | 僅加速查詢，允許重複值、NULL | 一般查詢加速 |
| 唯一索引 | UNIQUE INDEX | 值唯一，允許一個 NULL | 業務鍵（郵箱、手機、身分證） |
| 主鍵索引 | PRIMARY KEY | 唯一、非 NULL、聚簇索引（InnoDB） | 每張表必須有，建議自增整數 |
| 組合索引 | INDEX (a,b,c) | 多欄位組合，遵循最左前綴 | 多條件聯合查詢 |
-- 建表時建立 CREATE TABLE t1 ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32), email VARCHAR(64), extra TEXT, INDEX idx_name (name), UNIQUE INDEX uk_email (email), INDEX idx_name_email (name, email) ); -- 事後新增 CREATE INDEX idx_extra ON t1(extra(20)); -- TEXT/BLOB 必須指定前綴長度 CREATE UNIQUE INDEX uk_phone ON t1(phone); ALTER TABLE t1 ADD PRIMARY KEY (id); -- 若無主鍵 ALTER TABLE t1 ADD INDEX idx_multi (a,b,c); -- 刪除 DROP INDEX idx_name ON t1; DROP PRIMARY KEY ON t1; -- 先移除 AUTO_INCREMENT -- 查看 SHOW INDEX FROM t1; -- 或 SELECT * FROM information_schema.STATISTICS WHERE TABLE_NAME='t1';
索引 INDEX (name, email, age)：
| 查詢條件 | 是否走索引 | 說明 |
|---|---|---|
WHERE name='A' AND email='B' AND age=20 | ✅ | 完整匹配 |
WHERE name='A' AND email='B' | ✅ | 最左前綴 |
WHERE name='A' | ✅ | 最左單列 |
WHERE email='B' | ❌ | 非最左列 |
WHERE email='B' AND age=20 | ❌ | 缺少最左列 |
WHERE name='A' AND age=20 | ⚠️ 部分 | 僅用到 name，age 無法用索引 |
LIKE '%abc'、LIKE '%abc%' → 全表掃描。LIKE 'abc%' 可用索引。WHERE YEAR(dt) = 2023、WHERE id + 1 = 100 → 索引失效。WHERE name = 123（未加引號） → 索引失效。OR 條件兩側索引不全：WHERE a=1 OR b=2，若 b 無索引 → 全表掃描。解決：UNION ALL 拆分。!= / <> / NOT IN / IS NULL：通常全表掃描（主鍵例外）。INDEX (a,b,c)，WHERE a=1 AND b>2 AND c=3 → c 用不到索引。ORDER BY 非索引列 / 混合 ASC/DESC：SELECT * FROM t ORDER BY name DESC 若 name 無索引 → Using filesort。| 欄位 | 關鍵值 | 含義 |
|---|---|---|
id | 數字 | 查詢順序，越大越先執行；相同則從上往下 |
select_type | SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION | 查詢類型 |
table | 表名/衍生表 | 訪問的表 |
type | system/const > eq_ref > ref > range > index > ALL | 訪問類型，由好到壞。目標至少 ref，避免 ALL |
possible_keys | 索引名列表 | 理論可用索引 |
key | 索引名 | 實際使用索引 |
key_len | 位元組 | 使用的索引長度，推斷組合索引用了幾列 |
ref | 常數/欄位 | 與索引比較的值 |
rows | 數字 | 預估掃描行數（越小越好） |
Extra | Using index/Using where/Using filesort/Using temporary | 關鍵提示：Using index=覆蓋索引；Using filesort=需優化排序 |
場景：SELECT * FROM t ORDER BY id LIMIT 100000, 20 掃描 10 萬行再丟棄，極慢。
方案：延遲關聯
-- 1. 覆蓋索引快速定位主鍵 SELECT id FROM t ORDER BY id LIMIT 100000, 20; -- 2. 回表查完整欄位 SELECT t.* FROM t INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 100000, 20) AS tmp USING (id);
或遊標分頁（需應用層記住上頁最大 ID）：
SELECT * FROM t WHERE id > 100000 ORDER BY id LIMIT 20;
總結：索引是空間換時間的藝術。掌握「類型選對、最左前綴、避開失效、讀懂 EXPLAIN、分頁用遊標」五項核心技能，即可應對 80% 以上索引相關效能問題。
