MySQL 查詢優化核心：索引類型、創建策略與避坑指南

一、為什麼需要索引？

無索引時，WHERE name='test' 需全表掃描，複雜度 O(N)。建立索引後，MySQL 以 B+ 樹預排序，複雜度降為 O(log N) 或 O(1)（唯一值），百萬級數據查詢從秒級降至毫秒級。

二、索引生效條件

僅對以下操作符生效：<, <=, >, >=, =, BETWEEN, IN、LIKE 'prefix%'（不以通配符開頭）。
不生效：LIKE '%suffix'、LIKE '%mid%'、函數包裹列（UPPER(col)='A'）、隱式類型轉換（字符列比數字）。

三、四大索引類型詳解

1. 普通索引（INDEX/KEY）

最基本，無唯一性限制。

-- 三種創建方式
CREATE INDEX idx_name ON tbl(col(len));
ALTER TABLE tbl ADD INDEX idx_name (col(len));
CREATE TABLE tbl (..., INDEX idx_name (col(len)));

• CHAR/VARCHAR 可指定前綴長度 len（節省空間）。
• BLOB/TEXT 必須 指定前綴長度。

2. 唯一索引（UNIQUE INDEX）

值唯一，允許多個 NULL。

CREATE UNIQUE INDEX uk_email ON users(email);
ALTER TABLE users ADD UNIQUE uk_email (email);
CREATE TABLE users (..., UNIQUE uk_email (email));

3. 主鍵索引（PRIMARY KEY）

特殊的唯一索引：不允許 NULL、一張表只能有一個、InnoDB 為聚簇索引（數據與索引同存）。

CREATE TABLE t (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(16) NOT NULL,
    PRIMARY KEY (id)
);

常用 ALTER TABLE t ADD PRIMARY KEY (id) 補充。

4. 全文索引（FULLTEXT）

• MyISAM 專有（5.6+ InnoDB 支持）。
• 用於自然語言/布爾模式全文檢索：MATCH(col) AGAINST('word')。
• 大數據導入建議：先導數據，後建索引（避免逐行維護索引開銷）。
• 忽略規則：詞長 < 4 字符、停用詞、出現頻率 > 50% 的詞。

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
) ENGINE=MyISAM;

SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('database' IN NATURAL LANGUAGE MODE);

-- 布爾模式
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('+MySQL -YourSQL' IN BOOLEAN MODE);

四、單列索引 vs 組合索引

組合索引 = 在已索引結果集上再索引，遵循最左前綴原則。

ALTER TABLE test ADD INDEX idx_combo (col1, col2, col3);

• 查詢 WHERE col1=1 AND col2=2 → 用到索引。
• 查詢 WHERE col2=2 → 不用（未包含最左列）。
• 查詢 WHERE col1=1 AND col3=3 → 僅用到 col1。

五、索引設計黃金法則

1. 區分度高優先：COUNT(DISTINCT col)/COUNT(*) 越接近 1 越適合做索引首列。
2. 最左前綴：組合索引列順序 = 查詢 WHERE/ORDER BY 列順序。
3. 範圍列最後：組合索引中範圍條件（>, <, LIKE '%...'）後的列失效。
4. 覆蓋索引：SELECT 列全部在索引中，避免回表（Extra: Using index）。
5. 避免過度索引：每個索引增加寫入開銷，單表建議 ≤ 5 個索引。
6. 外鍵列必建索引：InnoDB 不自動創建，JOIN/級聯操作依賴索引。

六、常見失效場景（避坑清單）

七、總結

索引是查詢性能的杠桿，但也是寫入性能的負擔。掌握B+ 樹原理、最左前綴、覆蓋索引、範圍列陷阱四大核心，結合 EXPLAIN 實測驗證，才能做出精準的索引決策。