MySQL 表碎片產生原因與清理優化：MyISAM 與 InnoDB 差異化策略 MySQL 表碎片產生原因與清理優化：MyISAM 與 InnoDB 差異化策略

大量刪除、更新操作會在數據文件中產生不連續的空洞（碎片），影響查詢性能與磁盤利用率。不同存儲引擎的碎片整理方式截然不同。

碎片產生機制

• DELETE：標記刪除，空間不立即回收，形成空洞
• UPDATE：行長度變化導致頁分裂或留空
• INSERT：隨機插入導致頁分裂，產生碎片

MyISAM 碎片整理

檢測碎片

SHOW TABLE STATUS LIKE 'table_name'\G
-- 關鍵指標：Data_free > 0 表示存在碎片

優化命令

-- 方法 1：OPTIMIZE TABLE（推薦）
OPTIMIZE TABLE table_name;

-- 方法 2：mysqlcheck 命令行工具
mysqlcheck -u root -p --optimize database_name table_name

-- 方法 3：重建表（等效）
ALTER TABLE table_name ENGINE=MyISAM;

優化前後對比

-- 優化前
Data_free: 40
Avg_row_length: 45

-- 優化後
Data_free: 0
Avg_row_length: 32
Data_length: 96

InnoDB 碎片整理

❌ 無效方法

OPTIMIZE TABLE 和 mysqlcheck -o 對 InnoDB **無效**（實際執行 ANALYZE TABLE，僅更新統計信息）。

✅ 有效方法

大表在線重建最佳實踐

-- 使用 Percona Toolkit（推薦）
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  D=db_name,t=table_name \
  --execute

-- 或手動分步驟
CREATE TABLE new_table LIKE old_table;
ALTER TABLE new_table ENGINE=InnoDB;  -- 可調整參數
INSERT INTO new_table SELECT * FROM old_table;
RENAME TABLE old_table TO old_table_bak, new_table TO old_table;
DROP TABLE old_table_bak;

碎片監控指標

-- 查看表空間碎片率
SELECT 
    TABLE_NAME,
    DATA_FREE,
    DATA_LENGTH,
    ROUND(DATA_FREE / DATA_LENGTH * 100, 2) AS fragment_ratio
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_name'
  AND DATA_FREE > 0
ORDER BY fragment_ratio DESC;

預防措施

• 避免大批量 DELETE，改用 TRUNCATE 或分批刪除
• 合理設計主鍵（自增 ID 減少頁分裂）
• 定期 ANALYZE TABLE 更新統計信息
• 監控 Data_free 指標，設定告警閾值

工具推薦：innodb_ruby、mysqlfragfinder (Python) 可深度分析 InnoDB 頁面碎片分佈。