InnoDB 表碎片整理實戰：OPTIMIZE TABLE 將 5.6G 壓縮至 2.9G

生產環境 InnoDB 碎片整理完整操作記錄

高頻 DML（INSERT/UPDATE/DELETE）配合大量二級索引，極易導致 InnoDB 表空間碎片化，表現為 .ibd 文件遠大於實際數據量。本文記錄一次生產環境 transfe 表（354 萬行、6 個索引）的碎片整理全過程，文件從 5.6G 降至 2.9G，釋放 48% 空間。

一、現狀診斷

-- 表基本信息
mysql> show table status from emoprod like 'transfe'\G
Name: transfe
Engine: InnoDB
Rows: 3,543,033
Avg_row_length: 260
Data_length: 924,401,664  (~881 MB)
Index_length: 2,128,576,512 (~2.0 GB)  -- 索引比數據大 2.3 倍！
Data_free: 7,340,032      (~7 MB 可回收碎片)
Auto_increment: NULL
Create_time: 2016-09-25 17:43:44

-- 文件系統層面
[root@emo-mysql-02 emoprod]# ls -lh transfe.ibd
-rw-r----- 1 mysql mysql 5.6G Sep 28 16:03 transfe.ibd  -- 實際佔用 5.6 GB

核心現象：索引長度（2.1G）遠超數據長度（0.9G），.ibd 文件（5.6G）遠超 Data+Index（2.9G），說明存在大量頁碎片與未釋放空間。

二、整理操作

mysql> optimize table transfe;
+------------------------+----------+----------+-------------------------------------------------------------------+
| Table                  | Op       | Msg_type | Msg_text                                                          |
+------------------------+----------+----------+-------------------------------------------------------------------+
| emoprod.transfe        | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| emoprod.transfe        | optimize | status   | OK                                                                |
+------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (5 min 15.06 sec)

關鍵提示：InnoDB 不支援原生 OPTIMIZE，實際執行 重建表（recreate）+ 分析統計信息，相當於 ALTER TABLE ... ENGINE=InnoDB。

三、執行過程監控

-- 進程狀態
mysql> show processlist;
Id: 1180 | User: root | State: altering table | Info: optimize table transfe

-- 事務信息
mysql> select * from information_schema.INNODB_TRX\G
trx_id: 1918481819
trx_state: RUNNING
trx_query: optimize table transfe
trx_tables_locked: 1  -- 持有表鎖，會阻塞寫入！

四、整理效果對比

五、關鍵注意事項（生產環境必讀）

1. 鎖表風險：整理過程持有排他鎖，阻塞所有寫入，讀取可能正常（MVCC）。必須在業務低峰期執行。
2. MySQL 5.6+：支持 ALGORITHM=INPLACE 的 OPTIMIZE（部分場景無鎖），但重建表仍需拷貝數據，耗時與表大小成正比。
3. 備選方案：
   • ALTER TABLE transfe ENGINE=InnoDB（同等效果）
   • Percona pt-online-schema-change（在線無鎖重建，推薦大表）
   • MySQL 8.0 INSTANT DDL（部分操作秒級完成）
4. 統計信息更新：重建後自動執行 ANALYZE TABLE，優化器統計更準確，查詢計劃可能變更，需觀察慢查詢。
5. 磁盤空間：操作期間需額外 1 倍表大小的臨時空間（5.6G 表需 ~6G 空閒磁盤）。

六、碎片預防最佳實踐

• 主鍵設計：優先使用自增整型，避免 UUID/字符串導致頁分裂
• 索引策略：按需建立，定期 SHOW INDEX 檢查 Cardinality，刪除低選擇性索引
• 批量操作：大量 DELETE 改用 TRUNCATE 或分批 DELETE ... LIMIT 1000 + OPTIMIZE
• 定期維護：建立月度/季度整理計劃，配合 innodb_file_per_table=ON 獨立表空間

「碎片整理是治標，合理的表結構與索引設計才是治本。
每次 OPTIMIZE 都是對資源的昂貴消耗，請像對待手術一樣謹慎規劃。」

本文操作環境：MySQL 5.6 (Percona Server)，InnoDB 獨立表空間，生產從庫執行，主庫同步延遲 < 10s。完整日誌已歸檔備查。