您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 資料表壓縮實戰對比：InnoDB Plugin 透明壓縮、MyISAM 壓縮與歸檔壓縮技術選型指南

編輯：MySQL 資料庫相關知識

MySQL 壓縮技術分類與適用場景

資料表壓縮能大幅降低儲存成本、提升 IO 效能（讀取更少頁面）、減少備份時間。MySQL 提供多層次壓縮方案，適用不同業務場景：

技術	引擎	壓縮類型	寫入支援	適用場景	壓縮比
InnoDB 透明頁面壓縮	InnoDB (Plugin/5.7+)	線上透明壓縮 (Zlib/LZ4/Snappy)	完整支援 (OLTP)	熱/溫資料、生產業務表	40%~60%
MyISAM 靜態壓縮	MyISAM	`myisampack` 唯讀壓縮	僅讀 (壓縮後不可寫入)	歸檔表、日誌表、唯讀查詢	50%~70%
InnoDB 表空間壓縮	InnoDB	`ROW_FORMAT=COMPRESSED` + `KEY_BLOCK_SIZE`	完整支援	大字串欄位表、JSON/TEXT 重表	30%~50%
歸檔引擎	ARCHIVE	插入時流式壓縮 (Zlib)	僅 INSERT/SELECT，無 UPDATE/DELETE/索引	海量日誌、審計追蹤、Write-Once	70%~90%
列式引擎	ColumnStore / Infobright / ClickHouse	列級編碼 (RLE, Delta, Dictionary, Bit-packing)	批次載入為主	OLAP 分析、大寬表、聚合查詢	80%~95%

InnoDB 透明頁面壓縮 (Transparent Page Compression)

核心優勢：應用無感知、支援完整 OLTP、懸浮檔案系統 (punch hole) 釋放磁碟空間。

-- 建表啟用 (需檔案系統支援 punch hole: ext4, XFS, Btrfs)
CREATE TABLE t_compressed (
    id BIGINT PRIMARY KEY,
    data JSON
) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

-- 或修改現有表 (需重建表，鎖表時間長)
ALTER TABLE t ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

-- 關鍵參數
innodb_compression_algorithm = zlib  # 或 lz4, snappy (8.0.13+)
innodb_compression_level = 6         # 1-9, 平衡壓縮比與 CPU
innodb_compression_failure_threshold_pct = 5
innodb_compression_pad_pct_max = 50

實測對比數據 (來源：原文實測)

表類型	引擎/壓縮	大小	壓縮比	備註
url_comment_0	InnoDB (無壓縮)	632 MB	基準	原始業務表
share_16	InnoDB Plugin 壓縮	10.7 GB	~50%	`ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4`
share_17	InnoDB (無壓縮)	23.5 GB	基準	同結構對照組
url_comment_0	MyISAM (轉換後)	178 MB (MYD) + 98 MB (MYI) = 276 MB	~56%	`ALTER TABLE ... ENGINE=MyISAM`

結論：InnoDB Plugin 透明壓縮近 50% 空間節省；MyISAM 同樣顯著縮減體積。實際壓縮比取決於欄位類型：VARCHAR/TEXT/JSON 壓縮率高，INT/BIGINT 等定長數值壓縮率低。

MyISAM 靜態壓縮 `myisampack`

# 1. 停止服務或鎖表
mysql -e "LOCK TABLES tbl READ; FLUSH TABLES tbl;"

# 2. 執行壓縮 (生成 .TMD 檔案)
myisampack /var/lib/mysql/db/tbl.MYI

# 3. 重建索引
myisamchk -rq /var/lib/mysql/db/tbl.MYI

# 4. 解鎖
mysql -e "UNLOCK TABLES;"

# 壓縮後：唯讀、極高壓縮比、查詢快 (資料連續、無碎片)

歸檔壓縮技術選型決策樹

資料特徵                    → 推薦方案
----------------------------------------------------------
熱資料、頻繁讀寫、需索引     → InnoDB ROW_FORMAT=COMPRESSED
溫資料、以讀為主、大字串多   → InnoDB 透明壓縮 (Punch Hole)
冷資料、唯讀、海量日誌       → ARCHIVE 引擎 / myisampack
分析查詢、大寬表、聚合為主   → ColumnStore / ClickHouse
跨版本遷移、異構同步         → 邏輯備份 (mysqldump) + 目標端壓縮

效能影響與監控指標

CPU 開銷：壓縮/解壓消耗 CPU，innodb_compression_level 越高 CPU 越重。LZ4/Snappy 比 Zlib 快 3-5 倍，壓縮比略低
記憶體：壓縮頁在 Buffer Pool 中以壓縮形式駐留，實際容納更多資料，變相擴大緩衝池容量
IO：讀取物理頁面減少，但需解壓。隨機讀取受益大，順序掃描受益小

監控：

SHOW ENGINE INNODB STATUS\G
# 關注：
# Compression: 
#   Compressed pages: 12345
#   Compression ratio: 0.45
#   Compression failures: 12 (過多表示 KEY_BLOCK_SIZE 太小)

避坑指南

檔案系統支援：透明壓縮需 punch hole (FALLOC_FL_PUNCH_HOLE)。ext4 (3.0+)、XFS、Btrfs 支援。ZFS、NTFS、網路檔案系統可能不支援
KEY_BLOCK_SIZE 選擇：4K/8K/16K。太小 → 壓縮失敗率高；太大 → 壓縮比低。建議 8K 起步，觀察 Compression failures 調整
主鍵/索引包含長字串：壓縮效果打折，考慮前綴索引或拆表
線上 DDL 重建表鎖表：ALTER TABLE ... ROW_FORMAT=COMPRESSED 需重建表。5.6+ ALGORITHM=INPLACE 仍需排他鎖。大表用 pt-online-schema-change 或 gh-ost
備份/還原：mysqldump 還原時需目標表同樣啟用壓縮，否則體積暴增

MySQL 壓縮非銀彈，但對「大字串欄位多、讀多寫少、儲存成本敏感」的表極具價值。InnoDB 透明頁面壓縮 (Punch Hole) 是現代生產環境首選：應用零改造、完整 OLTP 支援、懸浮空間釋放。配合 LZ4 演算法平衡 CPU 與壓縮比，納入表設計標準化流程，可持續降低儲存 TCO。