碎片產生原因

1. 刪除操作：刪除行留下空洞，空間標記為可復用但不釋放。
2. 插入無法完美填充：新數據大小與空洞不匹配，空洞長期殘留。
3. 掃描代價增大：全表掃描按「高水位線」讀取，碎片越多掃描越多無效頁，查詢變慢。

例：表 1萬行 × 10字節 = 100KB，刪除剩 1 行 (10B)，MySQL 掃描仍按 100KB 處理。

檢測碎片大小

單表查看

mysql> SHOW TABLE STATUS LIKE 'table_name'\G
-- 關鍵列：Data_free（碎片字節數）

全庫掃描有碎片表

mysql> SELECT table_schema AS db, table_name, data_free, engine
    -> FROM information_schema.tables
    -> WHERE table_schema NOT IN ('information_schema','mysql')
    ->   AND data_free > 0
    -> ORDER BY data_free DESC;

清理碎片命令（按引擎區分）

1. MyISAM 表

-- 方法一：OPTIMIZE TABLE（鎖表，重建數據+索引文件，釋放空間）
mysql> OPTIMIZE TABLE testusers;
+----------------+----------+----------+----------+
| Table          | Op       | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| test.testusers | optimize | status   | OK       |
+----------------+----------+----------+----------+

-- 方法二：mysqlcheck 命令行（同樣效果）
shell> mysqlcheck -u root -p -o database table_name

執行後 Data_free 歸零，Data_length 縮減。

2. InnoDB 表

注意： OPTIMIZE TABLE 對 InnoDB 實際執行 ALTER TABLE ... ENGINE=InnoDB（重建表），同樣會鎖表。

-- 小表可直接用
mysql> ALTER TABLE tbl_name ENGINE=InnoDB;

-- 大表禁用 ALTER TABLE（長時間鎖表），採用「新建表→拷貝數據→重命名」方案
CREATE TABLE tbl_new LIKE tbl_old;
INSERT INTO tbl_new SELECT * FROM tbl_old;
RENAME TABLE tbl_old TO tbl_bak, tbl_new TO tbl_old;
DROP TABLE tbl_bak;

-- 或使用 pt-online-schema-change (Percona Toolkit) 在線重建，業務無感知。

引擎差異說明

• MyISAM：數據(.MYD)與索引(.MYI)分離，OPTIMIZE 會整理數據文件並重排索引。
• InnoDB：數據索引共存於表空間，OPTIMIZE 等價於 ALTER TABLE ... ENGINE=InnoDB 重建聚簇索引。

自動化維護建議

1. 編寫 Shell/Python 腳本，定時（如每周三凌晨低峰）執行：

#!/bin/bash
# 碎片超過 100MB 或碎片率 > 20% 才執行 OPTIMIZE
mysql -e "SELECT table_schema, table_name, data_free FROM information_schema.tables WHERE data_free > 100*1024*1024 AND table_schema NOT IN ('information_schema','mysql');" | while read db tbl free; do
  mysql -e "OPTIMIZE TABLE \`$db\`.\`$tbl\`;"
done

2. 監控 Data_free 趨勢，納入巡檢報表。
3. 大表優先使用 pt-online-schema-change 或計劃停機窗口重建。

核心原則：碎片整理會鎖表，必須在業務低峰期執行，大表嚴禁直接 ALTER/OPTIMIZE，務必採用在線工具或影子表遷移。