提升 MySQL InnoDB BLOB/TEXT 列存儲效率：Off-page 機制解析與五大優化策略

InnoDB 存儲長列（BLOB/TEXT/VARCHAR>255）會觸發 Off-page 機制：溢出資料獨佔獨立頁面，聚集索引僅存 20 字節指標，導致空間浪費與 I/O 惡化。本文深度解析存儲格式並給出五大優化策略。

InnoDB 存儲格式關鍵點

1. 頁面大小預設 16KB（5.6+ 可調 innodb_page_size，不建議改）。
2. 新頁面預留 1/16 空間供後續寫入。
3. 每頁至少存 2 行 → 理論單行上限 8KB（實際更小）。
4. 順序寫入填充率 15/16；隨機寫入 1/2~15/16 → 自增主鍵關鍵。
5. 填充率 < 1/2 觸發收縮釋放空間。
6. 行格式：COMPACT（預設，省 20%）、REDUNDANT、DYNAMIC、COMPRESSED（壓縮 50% 但 TPS 掉 90%，強烈不推薦，改用 TokuDB）。
7. Off-page 觸發：DYNAMIC/COMPRESSED 格式下長列完全 Off-page；COMPACT/REDUNDANT 格式下前 768 字節在頁內，超出部分 Off-page（哪怕 1 字節）。
8. Off-page 頁面獨佔，不可共享 → 一表多長列 = 多倍 Off-page 開銷。

五大優化策略

1. 序列化壓縮合併到單列

將所有長內容 JSON 序列化 + 壓縮（gzip/zstd）存入單一 BLOB 列，僅觸發 1 次 Off-page。

2. 短列轉 VARCHAR/CHAR

實際長度 ≤ 255 的列改為 VARCHAR(n) 或 CHAR(n)（定長用 CHAR），避免 Off-page。

3. 垂直拆分子表

按長度分組拆分多個子表，使每表總行長 < 8KB，減少 Off-page 頻率。示例：64KB 資料拆 5 子表，總空間最接近原始大小。

4. VARCHAR(255) 定義不增加實際空間

實際存 100 字節，定義 VARCHAR(255) 與 VARCHAR(100) 佔用空間相同，但 255 利於建前綴索引。

5. 遊戲/日誌領域重點關注

大量 BLOB 寫入場景（遊戲存檔、審計日誌）必須套用上述策略。

實測驗證：64KB 資料 5000 行，6 種存儲方式空間對比

• 單 BLOB 列：基準。
• 9 個 BLOB 列：空間暴增（9 個 Off-page）。
• 20 個離散 BLOB 列：更嚴重。
• 混合 CHAR/VARCHAR/BLOB：略優於純 BLOB。
• 全 VARCHAR(255) 定義：與混合相近（定義長度不影響實際）。
• 垂直拆分 5 子表：總空間 507,904 字節，最接近理論最優。

DDL 範例：垂直拆分 5 子表

-- 子表 1：行長 7533 字節
CREATE TABLE t_longcol_51 (id INT AUTO_INCREMENT PRIMARY KEY,
  longcol1 VARCHAR(255), ..., longcol15 VARCHAR(3082)) ENGINE=InnoDB;

-- 子表 2：行長 7168 字節
CREATE TABLE t_longcol_52 (id INT AUTO_INCREMENT PRIMARY KEY,
  longcol13 VARCHAR(1024), longcol14 VARCHAR(2048), longcol16 VARCHAR(4096)) ENGINE=InnoDB;
...（略）

掌握 Off-page 機制，長列存儲不再是黑盒。