MySQL 索引進階：哈希索引優化長字串查詢（URL、MD5、CRC32 實戰與碰撞處理）

MySQL 索引進階：哈希索引優化長字串查詢

B+Tree 索引存儲長字串（URL、JSON、Base64）體積大、比較慢。利用哈希值（CRC32、MD5 截斷、FNV64）建立整數索引，可大幅縮小索引體積、提升查詢速度。本文詳細說明實作步驟、碰撞處理與函數選型建議。

問題場景

CREATE TABLE url (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  url VARCHAR(2048) NOT NULL,
  INDEX idx_url (url)  -- 索引巨大，比較慢
);

-- 查詢
SELECT id FROM url WHERE url = 'https://www.example.com/very/long/path...';

哈希索引方案

1. 新增 url_crc INT UNSIGNED 欄位，存 CRC32(url)
2. 建立索引 INDEX idx_url_crc (url_crc)
3. 查詢改寫：

   SELECT id FROM url 
   WHERE url_crc = CRC32('https://www.example.com/...') 
     AND url = 'https://www.example.com/...';
   

哈希函數選型對比

MD5 截取實作（無需 UDF）

-- 取 MD5 後 16 字元轉 10 進位 64-bit 整數
SELECT CONV(RIGHT(MD5('https://example.com'), 16), 16, 10) AS hash64;
-- 結果：12345678901234567890

碰撞處理：雙重驗證

-- 正確：哈希 + 原值雙重驗證
SELECT id FROM url 
WHERE url_crc = CRC32('target_url') 
  AND url = 'target_url';

-- 錯誤：僅用哈希，碰撞時返回錯誤資料
SELECT id FROM url WHERE url_crc = CRC32('target_url');

生日悖論：CRC32 32-bit 空間，9.3 萬行碰撞機率達 1%。必須雙重驗證。

維護哈希值三種方式

-- Generated Column 範例 (MySQL 5.7+)
ALTER TABLE url 
ADD COLUMN url_crc INT UNSIGNED 
GENERATED ALWAYS AS (CRC32(url)) VIRTUAL,
ADD INDEX idx_url_crc (url_crc);

統計查詢可容忍碰撞

若只需近似計數（如 `COUNT(*)`），可僅用哈希值查詢，速度極快：

SELECT COUNT(*) FROM url WHERE url_crc = CRC32('target_url');
-- 允許少量誤差，不回表驗證原值

總結

• 長字串查詢 → 哈希索引 + 雙重驗證
• CRC32 適合中小表，大表用 FNV64 或 MD5 截取 64-bit
• MySQL 5.7+ 首選 Generated Column 自動維護
• 統計類查詢可犧牲精確度換極致速度

1. 上一篇文章： MySQL Slave 同步錯誤 Duplicate Entry 解決實戰：Binlog 位點手動校正與避坑指南
2. 下一篇文章： MySQL 日期時間函數完整速查手冊（第二版）：格式化、運算、時間戳、週期計算

MySQL 綜合教學

MySQL 修改 root 密碼四大方法（含忘記密碼強制重置）

MySQL 修改 root 密碼四大方法（含忘記密碼強制重置

Apache + PHP + MySQL 伺服器環境建置完整教學

前言 想要在本機端快速架設一個功能完整的 Web 開發環境嗎

MySQL 全文檢索完整指南：原理、模式、限制與最佳實踐

MySQL 全文檢索完整指南 MySQL 全文檢索 針對文本

MySQL 5.7.11 綠色版（免安裝）Windows 詳細安裝配置圖文教程

MySQL 5.7 系列取消了 MSI 安裝包，僅提供 ZI