萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 以 CRC32 虛擬欄位為字串建索引,大幅提升查詢效能

核心痛點

在 MySQL 中,對 VARCHAR、CHAR、TEXT 等字串欄位直接建立索引,隨著資料量增長,索引體積龐大、比較運算緩慢,導致查詢效能下降。特別是長度較長的業務流水號(如 sys_trans_id),全字串索引極其低效。

解決思路:CRC32 虛擬索引欄位

  1. 新增一個 INT UNSIGNED 欄位(如 sys_trans_id_crc32)。
  2. 寫入資料時,同步計算原字串的 CRC32 值並存入該欄位。
  3. 對該整數欄位建立普通 B-Tree 索引。
  4. 查詢時,WHERE 條件同時帶上 crc32 = CRC32('目標字串') 与原字串精確比對。

實戰 SQL 範例

-- 查詢帶上 CRC32 欄位走索引
EXPLAIN SELECT *
FROM `js_checking_third_detail`
WHERE (`biz_date` = '20160104')
  AND (`diff_type` IN ('2', '3'))
  AND (`sys_trans_id_crc32` = 509417929)   -- 整數索引,極快定位
  AND (`sys_trans_id` = '11451875264169885') -- 精確比對,消除碰撞
  AND (`trans_type` = '1')
  AND (`pay_type` = '1')
  AND (`account_id` = '1')
ORDER BY diff_type DESC, biz_date DESC, id DESC
LIMIT 0, 50;

效能對比(實測截圖)

帶 CRC32 索引: 掃描行數極少,type=ref,rows=1。

帶CRC32索引的EXPLAIN結果

不帶 CRC32 索引: 全表掃描,type=ALL,rows=幾十萬。

無CRC32索引的EXPLAIN結果

數據量越大,差距越明顯。百萬級資料表,查詢時間可從秒級降至毫秒級。

PHP 端 CRC32 負值修正函數

PHP crc32() 在 32 位元環境會回傳有號整數(負值),需轉為無號格式存入 INT UNSIGNED:

<?php
/**
 * 修正 PHP x86 環境下 crc32() 可能回傳負值的問題
 * @param string $str
 * @return string 無號整數字串,可直接寫入 INT UNSIGNED 欄位
 */
function jp_crc32($str) {
    return sprintf('%u', crc32($str));
}

// 使用範例
$transId = '11451875264169885';
$crc = jp_crc32($transId);  // 得到 '509417929'

// 寫入 SQL
$sql = "INSERT INTO `table` (`sys_trans_id`, `sys_trans_id_crc32`) VALUES ('$transId', $crc)";

// 查詢時同樣計算
$whereCrc = jp_crc32($inputId);
$sql = "SELECT * FROM `table` WHERE `sys_trans_id_crc32` = $whereCrc AND `sys_trans_id` = '$inputId'";

注意事項

  • 碰撞概率極低但非零: CRC32 為 32 位,約 42 億分之一碰撞機率,務必保留原字串精確比對 作為二次驗證。
  • 適用場景: 高基數、長字串、頻繁精確查詢的業務主鍵/流水號(訂單號、交易流水、設備 ID 等)。
  • 不適用: 低基數欄位(性別、狀態)、模糊查詢(LIKE '%abc%')、需排序的字串欄位。

總結

「CRC32 虛擬索引欄位」是一個低成本、高收益的 MySQL 優化技巧:用 4 字節整數索引替代長字串索引,配合原字串精確比對消除碰撞,能在千萬級資料量下實現毫秒級精確查詢。只需在寫入/查詢邏輯中增加一行 CRC32 計算,即可獲得數十倍效能提升。

copyright © 萬盛學電腦網 all rights reserved