MySQL 索引深度解析與優化實戰指南

索引核心原理

索引以 B+Tree 結構儲存，包含排序後的鍵值與指向資料檔實際行的指標。無索引時全表掃描（O(n)），有索引可直接定位（O(log n)），百萬行資料效能差距超過 100 倍。

索引類型詳解

1. 普通索引

最基本，無唯一性限制。建立方式：

CREATE INDEX idx_name ON tbl (col_list);
ALTER TABLE tbl ADD INDEX idx_name (col_list);
CREATE TABLE tbl (..., INDEX idx_name (col_list));

2. 唯一索引

鍵值不可重複，允許 NULL（多個 NULL 不視為重複）。

CREATE UNIQUE INDEX idx_name ON tbl (col_list);
ALTER TABLE tbl ADD UNIQUE idx_name (col_list);

3. 主鍵

特殊唯一索引，隱含 NOT NULL，每表僅一個。通常配合 AUTO_INCREMENT。

CREATE TABLE tbl (..., PRIMARY KEY (col_list));
ALTER TABLE tbl ADD PRIMARY KEY (col_list);

4. 全文索引

MySQL 3.23.23+ 支援，針對 VARCHAR/TEXT，類型 FULLTEXT。大量資料建索引建議先導入資料再建索引。

單列 vs 多列（複合）索引

單列索引局限

查詢 WHERE firstname='Mike' AND lastname='Sullivan' AND age=17，單列索引只能用一個，仍需掃描大量中間結果。

複合索引優勢

ALTER TABLE people ADD INDEX fname_lname_age (firstname, lastname, age);

B+Tree 依序比較三欄，單次查找精確定位，無需掃描資料檔。

複合索引 ≠ 多個單列索引

MySQL 單查詢只能用一個索引，多單列索引無法發揮複合索引的多欄過濾威力。

最左前綴原則

複合索引 (a,b,c) 等價於擁有 (a,b,c)、(a,b)、(a) 三個索引。以下查詢可用：

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

以下無法使用：

WHERE b=2
WHERE c=3
WHERE b=2 AND c=3

索引欄位選擇策略

• 出現在 WHERE、JOIN ON 條件中的欄位
• SELECT 清單欄位不需建索引
• 比較運算子支援索引：=, >, <, >=, <=, BETWEEN, IN, LIKE 'prefix%'
• LIKE '%suffix'、NOT IN、OR、函數運算（YEAR(date)）不走索引

EXPLAIN 實戰分析

EXPLAIN SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;

關鍵欄位解讀：

索引的代價

• 空間：索引檔體積增長快於資料檔，大表可能觸及檔案系統大小限制
• 寫入效能：INSERT/UPDATE/DELETE 需同步維護索引，降低寫入速度

優化檢查清單

• ☐ 核心查詢是否有索引支援
• ☐ 複合索引欄序是否符合最左前綴
• ☐ 是否存在冗餘/未使用索引（SHOW INDEX + sys.schema_unused_indexes）
• ☐ 字串欄位是否可用前綴索引（INDEX(name(10))）
• ☐ 避免在索引欄上使用函數/運算
• ☐ 定期 ANALYZE TABLE 更新統計資訊