MySQL 儲存十六進位識別碼的 5 大效能優化策略

背景

使用 MD5、SHA1 或 UUID 等 16 進位字串作為主鍵或唯一識別碼時，若儲存與索引策略不當，會導致索引膨脹、查詢變慢。以下針對 MySQL 提出五項優化建議，其他關聯式資料庫亦可參考。

策略一：避免使用 UTF-8 儲存 16 進位資料

CREATE TABLE `t` (
  `id` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

UTF-8 下 varchar(32) 主鍵索引長度達 98 bytes（32×3），造成排序、分組、臨時表操作時記憶體與磁碟消耗暴增 3 倍。建議改用 latin1 或 ascii 字元集，僅佔 32 bytes。

策略二：使用固定長度 CHAR 並禁止 NULL

所有 16 進位值長度固定（如 MD5 皆 32 字元），應用 CHAR(32) NOT NULL 取代 VARCHAR，避免變長欄位的額外儲存開銷與碎片化。

策略三：改用 BINARY(16) 二進位儲存

16 進位字串本質為數字，可直接轉為二進位儲存：

ALTER TABLE t MODIFY id BINARY(16) NOT NULL;
-- 寫入
INSERT INTO t (id) VALUES (UNHEX('0cc175b9c0f1b6a831c399e269772661'));
-- 查詢
SELECT * FROM t WHERE id = UNHEX('0cc175b9c0f1b6a831c399e269772661');
-- 或使用十六進位字面值
SELECT * FROM t WHERE id = x'0cc175b9c0f1b6a831c399e269772661';

索引長度從 98 bytes 降至 16 bytes，比較速度大幅提升。若使用 UUID()，寫入前先 REPLACE(UUID(), '-', '') 移除連字號。

策略四：建立前綴索引

若必須保留字串型態，可只索引前 8~10 字元：

ALTER TABLE t ADD KEY idx_id_prefix (id(8));

驗證選擇性：

SELECT COUNT(DISTINCT id), COUNT(DISTINCT LEFT(id,8)), COUNT(DISTINCT LEFT(id,9)) FROM t;

當前綴選擇性接近完整欄位時，即可大幅縮減索引體積。

策略五：建立 CRC32 雜湊索引

ALTER TABLE t ADD crc INT UNSIGNED NOT NULL, ADD KEY (crc);
UPDATE t SET crc = CRC32(id);

-- 查詢時帶上雜湊條件
SELECT * FROM t USE INDEX (crc)
WHERE id = '0cc175b9c0f1b6a831c399e269772661'
  AND crc = CRC32('0cc175b9c0f1b6a831c399e269772661');

索引長度僅 4 bytes，查詢型別為 ref，極速定位。CRC32 碰撞機率極低，適用於任意長字串的精確查詢加速。

總結對比