索引基礎：加速查詢的「目錄」

索引是特殊文件 (InnoDB 為表空間一部分)，包含數據指針。如書目錄，避免全表掃描。200 萬行無索引查詢 50-100s，加索引後毫秒級。

索引類型與創建語法

1. 普通索引

CREATE INDEX idx_name ON t(col(length));
ALTER TABLE t ADD INDEX idx_name (col(length));
CREATE TABLE t (..., INDEX idx_name (col(length)));

2. 唯一索引

CREATE UNIQUE INDEX uk_name ON t(col(length));
ALTER TABLE t ADD UNIQUE uk_name (col(length));

值唯一，允許 NULL（多 NULL 允許）。

3. 主鍵索引

ALTER TABLE t ADD PRIMARY KEY (col);
CREATE TABLE t (id INT PRIMARY KEY, ...);

唯一 + 非 NULL，聚簇索引 (InnoDB)。

4. 全文索引

ALTER TABLE t ADD FULLTEXT ft_content (content);
CREATE FULLTEXT INDEX ft_content ON t(content);

僅 MyISAM/MySQL 5.6+ InnoDB 支持，大文本搜索用，構建極慢佔空間大。

5. 組合索引 (最左前綴原則)

ALTER TABLE t ADD INDEX idx_a_b_c (a, b, c);

等價於建立：(a)、(a,b)、(a,b,c) 三個索引。

生效條件：WHERE/ORDER BY/GROUP BY 必須從最左列開始連續匹配。

SQL	是否用到 idx(a,b,c)
WHERE a=1 AND b=2 AND c=3	是 (全匹配)
WHERE a=1 AND b=2	是 (最左前綴)
WHERE a=1	是 (最左前綴)
WHERE b=2	否 (斷層)
WHERE a=1 AND c=3	部分 (僅用 a)
ORDER BY a, b	是
ORDER BY b, a	否

查看與刪除索引

SHOW INDEX FROM t;
SHOW KEYS FROM t;

DROP INDEX idx_name ON t;
ALTER TABLE t DROP INDEX idx_name;
ALTER TABLE t DROP PRIMARY KEY;

性能優化黃金法則

1. 選擇性高的列建索引

Cardinality / 行數 > 0.1 才值得。性別/狀態/類型等低選擇性列不要單獨建索引。

2. 短索引 (前綴索引)

ALTER TABLE t ADD INDEX idx_title (title(20));

長字符串列取前綴，節省空間、提速 I/O。

3. 避免索引失效的寫法

失效寫法	優化寫法	原因
WHERE YEAR(dt) < 2007	WHERE dt < '2007-01-01'	函數/運算導致全掃
WHERE col LIKE '%abc%'	WHERE col LIKE 'abc%'	前導通配符無法用索引
WHERE col + 1 = 10	WHERE col = 9	列運算失效
WHERE col IS NULL	--	NULL 不進普通索引

4. 索引列排序

• WHERE 用到索引時，ORDER BY 同方向可避免 filesort
• 多列排序建組合索引 (a,b) 對應 ORDER BY a,b

5. 覆蓋索引 (Index Only Scan)

SELECT 列全部在索引中 → 無需回表。
例：INDEX (vtype, id) 覆蓋 SELECT id FROM t WHERE vtype=1 ORDER BY id。

聚簇 vs 非聚簇選擇指南

場景	聚簇	非聚簇
範圍查詢	✓	✗
單行點查	✓	✓ (更快)
頻繁更新索引列	✗	✓
主鍵/外鍵	✓	✓

InnoDB 強制主鍵為聚簇索引，二級索引為非聚簇 (存主鍵值)。

限制與代價

• 單表最多 64 個索引 (InnoDB)，建議 ≤ 5-6
• 寫入開銷：每 INSERT/UPDATE/DELETE 需維護所有索引
• 空間開銷：索引文件可超數據文件

總結：優化檢查清單

1. EXPLAIN 分析 type、key、rows、Extra
2. 慢查詢優先加組合索引滿足 WHERE+ORDER BY+GROUP BY
3. 長字符串用前綴索引
4. 移除未用/低選擇性索引
5. 大表 COUNT(*) 改用計數表/緩存