MySQL 索引建立、刪除與優化實戰詳解

1. 索引為何能加速查詢

無索引時，三表連結 WHERE c1=c2 AND c1=c3 需掃描 10億種組合 (1000³)。有索引後，MySQL 只需全掃駕駛表 (t1)，其餘表透過索引直接定位，速度提升百萬倍。

2. 建立索引三種語法

ALTER TABLE 可單語句建多個索引；CREATE INDEX 索引名不可省略；無法用 CREATE INDEX 建 PRIMARY KEY。

3. 索引類型對照

• PRIMARY KEY：唯一且非空，每表僅一個，本質是名為 PRIMARY 的 UNIQUE 索引
• UNIQUE：值唯一，允許 NULL (多個 NULL 不衝突)
• FULLTEXT：全文檢索，僅 MyISAM，MySQL 3.23.23+
• SPATIAL：空間資料索引

4. 刪除索引

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

刪除組成複合索引的某欄位，該欄位自動從索引移除；全刪則整索引消失。

5. 查看索引資訊

SHOW INDEX FROM tbl_name;
SHOW KEYS FROM tbl_name;

關鍵欄位：Non_unique (0=唯一)、Cardinality (基數估計值，越大越優)、Index_type (BTREE/FULLTEXT/HASH/RTREE)。

6. 索引使用黃金法則

1. 小表別建索引，全表掃描更快。
2. 高基數欄位優先，重複值多、NULL 多的欄位索引效果差。
3. 回傳資料 < 25% 時索引最有效；大量回傳反而拖慢。
4. 寫入頻繁的表慎建索引，每次 DML 需維護索引。批次更新可先刪索引、事後重建。
5. 索引佔空間，磁碟容量規劃要納入。
6. 複合索引最左前綴原則：INDEX (a,b,c) 僅當 WHERE 條件包含 a、或 a+b、或 a+b+c 時生效。
7. JOIN 欄位務必建索引，且型別、長度一致。
8. 別對頻繁 UPDATE 的欄位建索引。
9. 索引與資料分離存放 (不同磁碟) 可減少 I/O 爭用。

7. 索引設計三大口訣

1. 選基數最大的欄位建索引
2. 複合索引把最常查詢的欄位放最左
3. 針對唯一屬性欄位或 JOIN 鍵建索引