MySQL 索引設計實戰技巧：類型選擇、複合索引與避坑指南

MySQL 索引設計實戰技巧：類型選擇、複合索引與避坑指南

索引是查詢效能的關鍵。本文系統講解 MySQL 索引分類、建立語法、複合索引最左前綴原則、建立時機判斷、索引缺陷與實戰注意事項，助您設計高效索引策略。

一、索引分類與建立語法

1. 普通索引

-- 建立
CREATE INDEX idx_name ON mytable(username(10));
-- 修改表
ALTER TABLE mytable ADD INDEX idx_name (username(10));
-- 建表時指定
CREATE TABLE mytable (
    id INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    INDEX idx_name (username(10))
);

CHAR/VARCHAR 可指定前綴長度；BLOB/TEXT **必須**指定長度。

2. 唯一索引

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

值必須唯一，允許 NULL (多個 NULL 不衝突)。

3. 主鍵索引

CREATE TABLE mytable (
    id INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    PRIMARY KEY (id)
);
-- 或
ALTER TABLE mytable ADD PRIMARY KEY (id);

特殊的唯一索引：不允許 NULL、一張表僅一個、通常配合 AUTO_INCREMENT。

4. 複合索引 (多欄索引)

ALTER TABLE mytable ADD INDEX idx_name_city_age (name(10), city, age);

等價於建立三組索引：(name, city, age)、(name, city)、(name) —— **最左前綴原則**。

5. 全文索引

CREATE FULLTEXT INDEX ft_content ON articles(content);
-- 僅 MyISAM (5.6 前) / InnoDB (5.6+) 支援

二、複合索引最左前綴原則實戰

INDEX idx_a_b_c (a, b, c)

-- ✅ 使用到索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3  -- 僅用 a

-- ❌ 無法使用索引
WHERE b = 2
WHERE b = 2 AND c = 3
WHERE c = 3
WHERE a = 1 OR b = 2  -- OR 導致全表掃描 (除非兩側都有索引)

三、何時建立索引

1. WHERE 條件欄位
2. JOIN 關聯欄位 (ON 子句)
3. ORDER BY / GROUP BY 欄位
4. 高選擇性欄位 (唯一值多，如身份證、手機號)
5. 外鍵欄位

但：MySQL 僅對 =, >, >=, <, <=, BETWEEN, IN, LIKE 'prefix%' 使用索引。

四、索引不會生效的常見場景 (必背)

1. LIKE '%keyword' 或 LIKE '%keyword%' — 萬用字元開頭
2. OR 條件兩側索引不完整
3. 複合索引未遵循最左前綴
4. 字符欄位查詢未加引號 → 隱含轉換 (WHERE phone = 13800000000)
5. MySQL 評估全表掃描更快 (選擇性低、表較小)
6. 對索引列進行運算/函數：WHERE YEAR(create_time) = 2024 → 改為 WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
7. NOT IN、<>、!= 通常不走索引
8. IS NULL 在某些版本/引擎可能不走索引 (InnoDB 5.6+ 支援)

五、索引的代價 (缺點)

• 寫入效能下降：INSERT/UPDATE/DELETE 需同步維護索引結構
• 空間佔用：每個索引佔用磁碟空間，複合索引膨脹更快
• 優化器負擔：索引過多增加優化器決策時間

六、實戰注意事項與技巧

七、索引維護命令

-- 查看索引
SHOW INDEX FROM tbl_name;
SHOW KEYS FROM tbl_name;
DESC tbl_name;

-- 刪除索引
DROP INDEX idx_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX idx_name;
-- 刪除主鍵
ALTER TABLE tbl_name DROP PRIMARY KEY;

八、索引使用情況監控

SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key: 越高越好 (索引命中)
-- Handler_read_rnd_next: 越高越差 (全表掃描)

總結

索引設計遵循「查詢驅動、適度原則」：只為核心查詢建索引，複合索引遵循最左前綴，拒絕 NULL、用短索引、追求覆蓋索引，定期清理無效索引。結合 EXPLAIN 與 Handler_read 監控，持續優化索引策略，才能在讀寫效能與空間成本間找到平衡點。