MySQL 索引類型全解析：主鍵、唯一、普通、全文、組合索引創建與選型指南

五大索引類型語法速查

創建與刪除語法

-- 創建
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_name_city ON users(name(10), city); -- 指定前綴長度

-- 刪除
DROP INDEX idx_email ON users;
ALTER TABLE users DROP INDEX idx_email;

會員卡系統選型實例

單列 vs 組合索引深度對比

場景

SELECT * FROM myIndex 
WHERE vc_Name='erquan' AND vc_City='鄭州' AND i_Age=25;

方案 A：三個單列索引

• MySQL 僅能選擇**一個**最優單列索引（通常是區分度最高的）
• 回表過濾其餘條件，效率受限

方案 B：組合索引 (vc_Name, vc_City, i_Age)

• 單次索引查找定位到精確行，**無需回表**（若只查這三列則覆蓋索引）
• 等效於擁有三個組合索引：(vc_Name, vc_City, i_Age)、(vc_Name, vc_City)、(vc_Name)

最左前綴原則

組合索引 (a,b,c) 可支持：

WHERE a = ?                    -- ✓ 用到 a
WHERE a = ? AND b = ?          -- ✓ 用到 a,b
WHERE a = ? AND b = ? AND c = ? -- ✓ 用到 a,b,c
WHERE b = ?                    -- ✗ 無法用到 (非最左)
WHERE a = ? AND c = ?          -- ✓ 僅用到 a (b 缺失中斷)

前綴長度優化

-- vc_Name 定長 50，實際值多數 < 10 字符
ALTER TABLE myIndex ADD INDEX idx_name_city_age (vc_Name(10), vc_City, i_Age);
-- 索引體積縮減，插入更新更快，查詢不受影響

索引使用條件

• WHERE/JOIN/ORDER BY/GROUP BY 中出現的列優先建索引
• 操作符：=, >, <, >=, <=, BETWEEN, IN 可用索引
• LIKE 'prefix%' 可用索引，**LIKE '%suffix' 不能用索引**
• OR 兩側列**都有索引**才可能用索引，否則全表掃描

索引的代價

1. 寫入性能下降：INSERT/UPDATE/DELETE 需同步維護索引樹
2. 磁盤空間佔用：每個索引一棵 B+ 樹，大表多組合索引膨脹明顯
3. 優化器誤判風險：統計信息過期可能導致選錯索引

維護建議

• 定期 ANALYZE TABLE 更新統計信息
• pt-duplicate-key-checker 檢測冗余/重複索引
• pt-index-usage 分析慢查詢日誌識別未用索引
• 大表 DDL 使用 pt-online-schema-change 無鎖變更

總結

索引選型口訣：主鍵必建、唯一業務鍵建 UNIQUE、高頻查詢列建 INDEX、多條件組合查詢建組合索引遵循最左前綴、長文本慎用 FULLTEXT。定期審視索引使用情況，刪除未用索引，是持續優化的關鍵動作。