索引基礎知識

• 所有欄位型別皆可建索引，提升 SELECT 效能。
• MyISAM/InnoDB 預設使用 B-Tree 索引。
• MySQL 目前不支援函數索引，支援前綴索引（字串前 N 字元）。
• 每張表最多 16 個索引，總長度 ≤ 256 位元組（舊版限制，新版已放寬）。

建立索引語法

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [USING index_type] ON tbl_name (col_name[(length)] [ASC | DESC], ...);

實戰範例：城市名稱前綴索引

CREATE INDEX idx_city_prefix ON city (city(2));
-- 查詢驗證
EXPLAIN SELECT * FROM city WHERE city = 'www.111cn.net'\G
-- 輸出 key: idx_city_prefix 表示索引生效

修改索引

MySQL 不支援 ALTER INDEX，需 DROP 後 CREATE，或使用 ALTER TABLE ... DROP INDEX ..., ADD INDEX ... 原子操作。

刪除索引

DROP INDEX index_name ON tbl_name;
-- 範例
DROP INDEX idx_city_prefix ON city;

索引設計黃金法則

1. 最左前綴原則：複合索引 (a,b,c) 可支援 a / a,b / a,b,c 查詢。
2. 高選擇性優先：基數越大（越唯一）越適合做索引鍵值。
3. 覆蓋索引：查詢欄位全在索引中，避免回表（Using index）。
4. 避免過度索引：寫入密集表索引 ≤ 5 個，讀多寫少可放寬。
5. 外鍵欄位必須建索引：InnoDB 會自動建立，MyISAM 需手動。

常見誤區

• 為每個欄位建單獨索引 → 優化器可能選錯、寫入極慢。
• 對大字段（TEXT/BLOB）建全長索引 → 必須指定前綴長度。
• 索引包含 NULL 值 → 仍可用，但統計資訊準確度稍降。