MySQL 字符串索引性能陷阱：VARCHAR 索引慢 30 倍、前綴索引與 Hash 索引優化方案

線上慢查詢發現：100 萬行表、聯合索引全走索引，VARCHAR(50) 列查詢耗時 600ms+；改為 BIGINT 後提速 30 倍。根因：B+ 樹索引查找需大量字符串逐字節比較，遠慢於整數比較。本文分析字符串索引性能劣勢，提供「前綴索引」與「Hash 索引」兩種優化方案，並展示 LIKE 前綴匹配的隱式轉換陷阱。

一、現象與根因

• 現象：WHERE address LIKE '任丘%' 與 '商丘%' 執行計劃相同但結果相同（隱式轉換導致）；VARCHAR(50) 索引查詢 600ms → 改 BIGINT 20ms。
• 根因：InnoDB B+ 樹索引頁面存儲完整字符串，查找時需逐字節比較 memcmp，CPU 開銷大；整數比較單指令完成。

二、優化方案 1：前綴索引

ALTER TABLE t ADD INDEX idx_address_prefix (address(8));

• 僅索引前 8 字節，大幅縮小索引體積、減少比較次數。
• 限制：不能用於 ORDER BY、GROUP BY、覆蓋索引掃描；選擇性需足夠高（前綴區分度接近全列）。
• 建議前綴長度 <= 10，通過 SELECT COUNT(DISTINCT LEFT(col,8))/COUNT(*) FROM t 評估選擇性。

三、優化方案 2：Hash 索引（應用層計算）

1. 添加整數列：address_key INT UNSIGNED NOT NULL DEFAULT 0
2. 寫入時計算：address_key = CRC32(address) 或 MurmurHash32(address)（極低碰撞）。
3. 建立索引：INDEX idx_address_key (address_key)
4. 查詢改寫：

WHERE address_key = CRC32('北京市朝陽區') AND address = '北京市朝陽區';

• 索引定位 address_key（整數極快）→ 回表驗證 address 防碰撞。
• 適用於精確匹配、Join 條件、分佈式鍵。

四、LIKE 前綴匹配的隱式轉換陷阱

SELECT * FROM region WHERE city LIKE '任丘%';  -- 結果包含 '商丘%'
SELECT * FROM region WHERE city LIKE '臨安市%'; -- 結果包含 '六安市%'

原因：字符集校對規則 utf8_general_ci 將部分漢字視為等價（如「任/商」在某些校對權重相同），導致索引範圍掃描範圍擴大、結果錯誤。

修復：WHERE city LIKE '任丘%' COLLATE utf8_bin 或表級改 utf8_bin。

五、總結建議

1. 業務鍵優先用整數（自增 ID、雪花 ID、Hash 值），避免 VARCHAR 做主鍵/外鍵/高頻查詢列。
2. 必須用字符串時：前綴索引（≤10 字節）+ 覆蓋索引或應用層 Hash 列+索引。
3. 字符集校對統一 utf8mb4_bin，避免 _ci 隱式等價導致錯誤結果與索引失效。
4. 定期 ANALYZE TABLE 更新索引統計，防止優化器誤判選擇性。