線上慢查詢發現：100 萬行表，聯合索引全命中，查詢耗時 600ms+。將索引列從 VARCHAR(50) 改為 BIGINT 後，耗時降至 20ms（**30 倍提升**）。本文深度剖析字符串索引效能劣化根因，提供前綴索引與哈希索引兩大優化方案，並揭示 LIKE 前綴匹配的隱藏陷阱。

根因：字符串比較的本質開銷

• B+ 樹索引查找過程本質是鍵值比較
• 整數比較：單 CPU 指令（CMP），納秒級
• 字符串比較：逐字節/字符比較，涉及 Collation 規則、多字節解碼、區域設定，開銷是整數的 10-50 倍
• 索引樹高度相同時，字符串索引每層比較成本遠高於整數

方案一：前綴索引——以空間換時間

-- 僅索引前 8 個字符（建議 ≤10）
ALTER TABLE region ADD INDEX idx_city_prefix (city(8));

適用場景：字符串前綴區分度高（如 URL、郵箱、編碼前綴）

限制：

• 無法用於 ORDER BY：前綴索引不保存完整值，無法支援排序
• 無法用於索引覆蓋：需回表讀取完整列
• 區分度驗證：SELECT COUNT(DISTINCT LEFT(city,8))/COUNT(*) FROM region，比率 < 0.9 慎用

方案二：哈希索引——字符串轉整數

應用層或資料庫層計算哈希值，建立整數索引：

-- 表結構擴展
ALTER TABLE region ADD city_hash BIGINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE region ADD INDEX idx_city_hash (city_hash);

-- 寫入時計算（應用層或觸發器）
UPDATE region SET city_hash = CRC32(city) WHERE city_hash = 0;
-- 或 MySQL 8.0: CAST(SHA2(city, 256) AS UNSIGNED) 取前 64 位

-- 查詢改寫
SELECT * FROM region 
WHERE city_hash = CRC32('北京市') 
  AND city = '北京市';  -- 二次過濾防哈希碰撞

優勢：整數索引極快、支援覆蓋索引、排序無影響

代價：寫入額外計算、存儲額外 8 字節、需處理哈希碰撞（極低概率）

隱藏陷阱：LIKE 前綴匹配的字符級別「誤命中」

觀察到現象：

SELECT * FROM region WHERE city LIKE '任丘%' LIMIT 10;
SELECT * FROM region WHERE city LIKE '商丘%' LIMIT 10;
-- 返回結果相同！

SELECT * FROM region WHERE city LIKE '臨安市%' LIMIT 10;
SELECT * FROM region WHERE city LIKE '六安市%' LIMIT 10;
-- 返回結果相同！

原因：MySQL 某些版本/字符集下，LIKE '任丘%' 的模式匹配在索引查找階段可能因字符編碼相似性（如 GBK 雙字節邊界、UTF-8 多字節前綴重疊）導致索引範圍掃描錯誤擴大，掃入不應匹配的行。

驗證與規避

1. EXPLAIN 觀察 rows：預估掃描行數是否異常大
2. 強制字符集比較：WHERE city COLLATE utf8mb4_bin LIKE '任丘%'（二進制比較，精確前綴）
3. 改用前綴索引 + 應用層過濾：WHERE city_hash = CRC32('任丘') AND city LIKE '任丘%'
4. 升級 MySQL 8.0：優化器對多字節字符集 LIKE 範圍計算有顯著改進

字符串索引最佳實踐清單

1. 業務鍵優先用整數（自增 ID、雪花 ID、枚舉映射）
2. 必須用字符串時：短優於長、定長優於變長、二進制優於字符集
3. VARCHAR(20) 以內可直接建全列索引；超長用前綴索引或哈希索引
4. 避免在長字符串列上做 ORDER BY、GROUP BY、JOIN
5. 定期 ANALYZE TABLE 更新索引統計資訊，防止優化器誤判

總結

字符串索引是「隱形效能殺手」。100 萬行表 600ms → 20ms 的 30 倍提升，證明了將比較操作從字符串降維為整數的巨大價值。前綴索引適合高區分度前綴場景；哈希索引是通用高效能方案。選型原則：能用整數絕不用字符串，必須用字符串則轉整數索引。