MySQL 索引類型全解析：從單列到組合索引，讓查詢飛起來

索引核心價值：從全表掃描到精準定位

以 1 萬筆測試資料為例，查詢 username='admin'：

• 無索引：全表掃描 10,000 行
• 有索引：B+ 樹直接定位，毫秒級完成

五大索引類型詳解

1. 普通索引

最基礎，無任何限制。建立方式三選一：

-- 獨立建立
CREATE INDEX idx_name ON mytable(username(10));

-- ALTER TABLE
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))
);

刪除：DROP INDEX idx_name ON mytable;

2. 唯一索引

值必須唯一，允許 NULL。組合唯一索引要求列組合唯一。語法同普通索引，加 UNIQUE 關鍵字。

3. 主鍵索引

特殊的唯一索引：不允許 NULL、每表僅一個。通常建表時指定：PRIMARY KEY (id)。

4. 組合索引（複合索引）

將多欄位建在同一索引：ALTER TABLE mytable ADD INDEX name_city_age (name(10), city, age);

最左前綴原則

上述索引等效於：(name,city,age)、(name,city)、(name) 三個索引。查詢必須從最左列開始才能用到組合索引。

5. 何時建立索引

• 出現在 WHERE、JOIN、ORDER BY、GROUP BY 的欄位
• 支援索引的運算子：<、<=、=、>、>=、BETWEEN、IN、LIKE 'prefix%'
• 不走索引：LIKE '%suffix'、LIKE '%mid%'、欄位上做運算（YEAR(date)<2007）、NOT IN、<>

索引的代價與避坑指南

三大副作用

1. 寫入變慢：INSERT/UPDATE/DELETE 需同步維護索引檔
2. 佔磁碟：大表多組合索引易導致索引檔膨脹
3. 優化器誤判：統計資訊過期可能選錯索引

七條實戰鐵律

1. 拒絕 NULL：含 NULL 欄位不入索引，組合索引任一列 NULL 導致該列失效 → 設計時設 NOT NULL DEFAULT ''
2. 短索引：VARCHAR(255) 前 10-20 字元唯一性高 → INDEX(name(10)) 省空間又快
3. 排序單索引：WHERE 用了索引，ORDER BY 另一列不走索引 → 建組合索引 (where_col, order_col)
4. LIKE 只查前綴：LIKE 'abc%' 走索引，LIKE '%abc' 全掃
5. 欄位不做運算：WHERE YEAR(dt)<2007 → 改 WHERE dt<'2007-01-01'
6. 慎用 NOT IN / <>：優化器常放棄索引改全掃
7. 定期 ANALYZE TABLE：更新統計資訊，助優化器做正確決策

總結

索引是效能倍增器，也是雙面刃。掌握類型特性、最左前綴、SARGable 查詢寫法、維護成本權衡，才能在大數據量下駕馭 MySQL。