MySQL 數據碎片成因、檢測與 MyISAM/InnoDB 整理實戰

MySQL 數據碎片成因、檢測與 MyISAM/InnoDB 整理實戰

大量刪除/更新操作會導致數據頁面分裂、空洞，產生「數據碎片」，嚴重影響掃描性能、佔用額外磁盤空間。本文深度解析碎片成因、檢測指標、MyISAM 與 InnoDB 的整理差異。

碎片成因機制

1. DELETE：標記刪除，頁面留下空洞，空間不釋放
2. UPDATE 變長列：行長度增大導致頁面分裂（Page Split），原頁留空洞，新頁填充度低
3. INSERT 隨機主鍵：InnoDB 聚簇索引頻繁分裂，產生碎片

碎片分兩類：單行碎片（行內空洞）與 多行碎片（頁面間邏輯順序與物理順序不一致）。索引同樣產生碎片。

檢測碎片：關鍵指標 DATA_FREE

-- 查看所有表碎片空間（字節）
SELECT TABLE_SCHEMA, TABLE_NAME, DATA_FREE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND DATA_FREE > 0
ORDER BY DATA_FREE DESC;

-- 單表詳情
SHOW TABLE STATUS FROM db_name LIKE 'table_name'\G
-- 關注：Data_free、Avg_row_length、Row_format

• DATA_FREE > 0 表示存在可重用空間（碎片）
• InnoDB 表空間共享時，DATA_FREE 反映整個表空間碎片，非單表

MyISAM 整理：OPTIMIZE TABLE

OPTIMIZE TABLE `table_name`;

原理：重建表（臨時表拷貝數據 → 替換原表），物理順序重組，索引重建，釋放碎片空間。

特點：

• ✅ 徹底整理數據+索引碎片
• ✅ 更新索引統計信息
• ⚠️ 全程鎖表（MyISAM 表鎖），大表不可用時間長
• ⚠️ 需額外磁盤空間（臨時表）

建議：低峰期執行，配合 myisamchk -r 離線更快。

InnoDB 整理：線上重建

InnoDB 不支持 傳統 OPTIMIZE TABLE（實際執行 ALTER TABLE ... ENGINE=InnoDB）。

方案 1：ALTER TABLE tbl ENGINE=InnoDB（5.6+ Online DDL）

ALTER TABLE `large_table` ENGINE=InnoDB;

• ✅ 5.6+ 支持 Online DDL，僅結束階段短暫元數據鎖，允許並發 DML
• ✅ 重建聚簇索引+所有二級索引，徹底消除碎片
• ✅ 更新統計信息
• ⚠️ 需 1.5-2 倍表大小臨時空間（tmpdir）
• ⚠️ 大表耗時久（GB 級數小時），低峰期執行

方案 2：OPTIMIZE TABLE（實際同方案 1）

InnoDB 表執行 OPTIMIZE TABLE 等價於 ALTER TABLE ... ENGINE=InnoDB + ANALYZE TABLE。

方案 3：Percona Toolkit pt-online-schema-change（零停機、可控）

pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  D=db_name,t=table_name \
  --execute \
  --max-load Threads_running=50 \
  --critical-load Threads_running=100

• ✅ 觸發器同步增量，全程無鎖
• ✅ 可限流、暫停、恢復、回滾
• ✅ 生產環境大表整理首選

實戰演示：MyISAM 碎片產生與整理

-- 1. 創建表並插入 4 行
CREATE DATABASE xinxing;
USE xinxing;
CREATE TABLE xin (c CHAR(40)) ENGINE=MyISAM;
INSERT INTO xin VALUES ('xiaohei'),('xiaoqian'),('xiaolin'),('xiaonan');

-- 2. 查看：Data_free = 0
SHOW TABLE STATUS FROM xinxing LIKE 'xin'\G

-- 3. 刪除 1 行
DELETE FROM xin WHERE c = 'xiaolin';

-- 4. 再查：Data_free = 121（產生碎片）
SHOW TABLE STATUS FROM xinxing LIKE 'xin'\G

-- 5. 整理
OPTIMIZE TABLE xin;
-- Data_free 歸 0

索引碎片同理

OPTIMIZE TABLE / ALTER TABLE ENGINE=InnoDB 會重建所有索引，消除索引碎片，恢復 B+ 樹填充度。

最佳實踐總結

建立 DATA_FREE 監控告警（如 > 表大小 20%），定期低峰整理，保持存儲緊湊、掃描高效。