MySQL 建立索引語法與實務詳解

MySQL 建立索引語法與實務詳解

索引是資料庫效能調優的關鍵利器。本文系統梳理 MySQL 索引的建立語法、索引類型選擇、前綴索引應用以及刪除索引的操作，助您精準掌握索引管理核心技能。

一、索引基礎認知

• 全欄位可索引：MySQL 所有索引欄位類型皆可建立索引。
• 效能提升：合理使用索引能大幅提高 SELECT 查詢效能，尤其在大數據量場景下效果顯著。
• 引擎限制：各儲存引擎對單表索引數量與總長度有限制。InnoDB/MyISAM 至少支援 16 個索引，總索引長度上限 256 位元組（舊版本限制，新版已放寬）。
• 預設索引類型：MyISAM 與 InnoDB 預設皆使用 B-Tree 索引。
• 函數索引：MySQL 8.0 前不支援函數索引，8.0+ 開始支援；但早期版本可透過前綴索引針對欄位前 N 個字元建立索引來優化長字串查詢。

二、建立索引語法（CREATE INDEX）

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name, ...)
    [index_type]

參數詳細說明

索引類型選擇指南

• BTREE：最通用，支援 =、>、<、>=、<=、BETWEEN、LIKE 'prefix%' 等範圍查詢。
• HASH：僅支援精確查詾（=、<=>），記憶體表（MEMORY）預設使用，查詢極快但不支援範圍掃描。
• RTREE：專用於空間資料類型（GEOMETRY、POINT 等）。

三、前綴索引實戰

對於 VARCHAR、TEXT、BLOB 等長字串欄位，建立全欄位索引會佔用大量空間且降低寫入效能。前綴索引只對欄位前 N 個字元建立索引：

-- 建立 email 欄位前 10 字元的索引
CREATE INDEX idx_email_prefix ON users (email(10));

選擇長度原則：透過 SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) FROM users; 計算選擇度，當選擇度接近全欄位選擇度時，該長度即為最佳平衡點。

四、刪除索引語法（DROP INDEX）

DROP INDEX index_name ON tbl_name;

• 需指定索引名稱與資料表名稱。
• 若索引為主鍵，需使用 ALTER TABLE tbl_name DROP PRIMARY KEY;。
• 刪除索引會釋放磁碟空間，但大表操作可能鎖表較久，建議低峰期執行或使用線上 DDL 工具（如 pt-online-schema-change）。

五、修改索引

MySQL 無直接 ALTER INDEX 指令，修改索引通常採用「刪除舊索引 → 建立新索引」流程：

DROP INDEX idx_old ON tbl_name;
CREATE INDEX idx_new ON tbl_name (new_col_list);

大表修改索引時，建議使用 ALGORITHM=INPLACE 或線上工具避免長時間鎖表。

六、索引設計黃金法則

1. 最左前綴原則：複合索引 (a,b,c) 可支援 a、a,b、a,b,c 查詢，不可跳過欄位。
2. 高選擇度優先：區分度高的欄位放在複合索引前列。
3. 覆蓋索引：查詢只需掃描索引即可取得所有所需欄位，避免回表。
4. 避免過度索引：每個索引都會增加寫入開銷（INSERT/UPDATE/DELETE），單表索引建議不超過 5 個。
5. 定期維護：透過 ANALYZE TABLE 更新統計資訊，配合 SHOW INDEX 檢查基數。

索引是把雙面刃，用得好查詢飛快，用不好寫入拖慢。深入理解語法與原理，結合業務查詢模式精準設計，才能發揮最大效能紅利。