MySQL VARCHAR 前綴索引的隱形成本：UPDATE 效能暴跌 2 倍的實測分析

MySQL VARCHAR 前綴索引的隱形成本：UPDATE 效能暴跌 2 倍的實測分析

為 VARCHAR 欄位加前綴索引能大幅加速查詢，但寫入代價往往被忽視。本文通過億級表實測，量化索引維護對 UPDATE 的衝擊，並探討垂直拆表優化思路。

實測場景

• 表 tbl_name：億級行數
• 欄位 platforms VARCHAR(255)，加前綴索引 platforms(8)
• 操作：逐行更新該欄位為當前時間戳

實測結果

代碼片段：

-- 加索引
ALTER TABLE tbl_name ADD INDEX platforms_index(platforms(8));

-- 更新腳本
for idx in range(1, count+1):
    sql = "UPDATE tbl_name SET platforms='"+datetime.now().strftime('%Y%m%d%H%M%S%f')+"' WHERE id="+str(idx)+";"
    cursor.execute(sql)

根因分析

UPDATE 流程：

1. 定位行（聚簇索引）
2. 修改數據頁
3. 同步維護所有二級索引：刪除舊索引項 → 插入新索引項
4. 寫 Redo/Undo Log

字符串索引維護開銷大：
- 變長列需計算前綴哈希
- 頻繁頁分裂/合併
- 佔用更多緩衝池與磁盤 I/O

優化思路：垂直拆表

將高頻更新的長字符串拆到獨立表：

-- 主表
CREATE TABLE main_tbl (
    id BIGINT PRIMARY KEY,
    -- 其他高頻讀寫欄位
    platform_id INT
);

-- 字符串表（去重後極小）
CREATE TABLE platform_dim (
    platform_id INT PRIMARY KEY,
    platforms VARCHAR(255),
    UNIQUE KEY uk_platforms (platforms)
);

更新時僅修改 platform_dim 一行，或插入新值再更新映射。若重複率高，字符串表極小，索引維護成本近乎為零。

適用性評估

• ✅ 字符串重複率高（如狀態碼、分類標籤、URL 域名）
• ✅ 該欄位更新頻繁、查詢頻繁
• ❌ 字符串幾乎唯一（如 UUID、MD5），拆表無去重效果
• ❌ 極簡單查詢，JOIN 開銷大於索引收益

總結

索引是把雙刃劍。讀多寫少加索引，寫多讀少慎加索引，長字符串高頻更新優先考慮垂直拆表或外部存儲（如對象存儲+引用）。