MySQL 索引基礎與效能優化完整指南

索引是關聯式資料庫效能的核心。本文涵蓋索引建立/刪除/查看語法、索引類型（普通、唯一、全文、組合）、最左前綴原理、聚簇與非聚簇索引差異、以及 10 大優化避坑原則，助您從根本上解決慢查詢問題。

一、索引基礎操作語法

建立索引三種方式

-- 1. ALTER TABLE（支援主鍵、唯一、普通）
ALTER TABLE tbl ADD INDEX idx_name (col1, col2);
ALTER TABLE tbl ADD UNIQUE uk_name (col);
ALTER TABLE tbl ADD PRIMARY KEY (col);

-- 2. CREATE INDEX（不支援主鍵）
CREATE INDEX idx_name ON tbl (col(length));
CREATE UNIQUE INDEX uk_name ON tbl (col);

-- 3. 建表時同步建立
CREATE TABLE tbl (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    INDEX idx_title (title(50)),
    UNIQUE uk_email (email)
);

刪除索引

DROP INDEX idx_name ON tbl;
ALTER TABLE tbl DROP INDEX idx_name;
ALTER TABLE tbl DROP PRIMARY KEY;  -- 僅刪主鍵

查看索引

SHOW INDEX FROM tbl;
SHOW KEYS FROM tbl;

重點欄位：Cardinality（基數，唯一值估計）、Non_unique、Index_type（BTREE/FULLTEXT/HASH/RTREE）。

二、索引類型詳解

三、組合索引與最左前綴原理

建立 INDEX idx_a_b_c (a, b, c) 實際等同於擁有：

• (a, b, c)
• (a, b)
• (a)

可用到索引的查詢：

WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 ORDER BY b

用不到索引的查詢：

WHERE b = 2           -- 缺少最左列 a
WHERE c = 3           -- 缺少最左列 a
WHERE a = 1 AND c = 3 -- 中間斷層，只用到 (a)

四、聚簇 vs 非聚簇索引（InnoDB 核心機制）

最佳實踐：InnoDB 必須有主鍵，建議用 BIGINT AUTO_INCREMENT 單調遞增，避免 UUID/字串主鍵導致頁分裂。

五、10 大索引優化避坑原則

1. NULL 值不入索引：單欄索引忽略 NULL，組合索引任一列為 NULL 導致該行不入組合索引。設計時設 NOT NULL DEFAULT ''。
2. 使用短索引：VARCHAR(255) 前綴長度 10-20 即可區分，節省空間與 I/O。INDEX idx_title (title(20))
3. 索引列不參與運算/函數：WHERE YEAR(create_time) = 2023 ❌ → WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01' ✅
4. LIKE 只支援右側萬用字元：LIKE 'abc%' ✅ 走索引；LIKE '%abc%' ❌ 全表掃描。
5. WHERE 用索引則 ORDER BY 不再用索引：單查詢僅用一個索引。需排序時建立 INDEX (where_col, order_col)。
6. 避免 SELECT *：只查需要欄位，利用覆蓋索引（Using index）避免回表。
7. 單表索引數量適度：理論上限 16 個，實務建議 ≤ 5 個。每新增索引，寫入（INSERT/UPDATE/DELETE）成本增加。
8. 字元集一致：JOIN/關聯欄位字元集、排序規則必須一致，否則索引失效。
9. 外鍵/關聯欄位必建索引：JOIN、WHERE、ORDER BY、GROUP BY 涉及欄位優先建索引。
10. 定期 ANALYZE TABLE：更新 Cardinality 統計資訊，讓優化器做正確決策。

六、何時選擇聚簇/非聚簇索引（決策表）

索引優化是持續迭代的過程：觀察慢查詢 → EXPLAIN 分析 → 調整索引/語句 → 驗證效果。沒有萬能索引，只有適合當前業務查詢模式的索引。