MySQL 索引建立、類型、優缺點與適用場景全解析

為什麼建索引

• 唯一性約束（主鍵/唯一索引）。
• 大幅提升檢索速度（核心優勢）。
• 加速表連結（外鍵列）。
• 優化 GROUP BY/ORDER BY（避免 filesort）。
• 優化器可選擇更優執行計劃。

索引的代價

• 維護開銷：增刪改需同步更新所有索引，寫入變慢。
• 空間佔用：MyISAM 索引文件獨立增長，InnoDB 共享/獨立表空間同樣佔用。

適合建索引的列

1. 經常 WHERE 搜索的列。
2. 主鍵列（強制唯一+聚簇）。
3. 經常 JOIN 的外鍵列。
4. 經常範圍查詢 BETWEEN、>、< 的列（索引有序）。
5. 經常 ORDER BY/GROUP BY 的列。
6. WHERE 條件高頻列。

不適合建索引的列

1. 極少查詢引用的列（維護成本 > 查詢收益）。
2. 低基數列（性別、狀態、布爾值），掃描比例超 30% 優化器傾向全表掃描。
3. TEXT/BLOB 大字段（長度大、區分度低）。
4. 寫多讀少場景（修改性能遠大於查詢性能時）。

建索引三種語法

-- 1. CREATE INDEX
CREATE INDEX idx_name ON table_name (col_list);
-- 2. ALTER TABLE
ALTER TABLE table_name ADD INDEX idx_name (col_list);
-- 3. 建表時指定
CREATE TABLE table_name (..., INDEX idx_name (col_list));

查看索引

SHOW INDEX FROM table_name;

五大索引類型與創建示例

修改主鍵示例

ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY (col_a, col_b);

總結

索引是「查詢加速器、寫入剎車、空間佔用者」。根據業務讀寫比、基數、字段類型、查詢模式科學選擇，定期 ANALYZE TABLE 更新統計信息，避免過度索引。