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

MySQL 批量 OPTIMIZE TABLE 自動化 Shell 腳本與日誌分析

編輯：MySQL 資料庫相關知識

碎片整理 OPTIMIZE TABLE 能回收磁碟空間、整理索引頁、更新統計資訊。對擁有數百資料庫、數千資料表的實例，手動執行不可行。本文提供生產可用的 Shell 自動化腳本、日誌解讀與排程建議。

自動化腳本 `optimize_all.sh`

#!/bin/bash
# 用途：遍歷所有用戶資料庫執行 OPTIMIZE TABLE，記錄結果至 /tmp/optimize.log
# 需求：MySQL 客戶端在 PATH、具備 root 或相應權限帳號

read -s -p "Enter MySQL root password: " MYSQL_PWD; echo
read -p "Enter MySQL data directory (e.g. /var/lib/mysql): " DATADIR

[[ -z "$DATADIR" || ! -d "$DATADIR" ]] && { echo "Invalid data dir"; exit 1; }

cd "$DATADIR"
LOG="/tmp/optimize.log"
echo "=== $(date '+%F %T') OPTIMIZE START ===" >> "$LOG"

for db in *; do
    [[ -d "$db" && "$db" != "mysql" && "$db" != "test" && "$db" != "performance_schema" && "$db" != "sys" ]] || continue
    echo "Processing database: $db" >> "$LOG"
    cd "$db"
    for frm in *.frm; do
        [[ -f "$frm" ]] || continue
        tbl="${frm%.frm}"
        echo "$(date '+%F %T') OPTIMIZE $db.$tbl" >> "$LOG"
        mysql -uroot -p"$MYSQL_PWD" -e "OPTIMIZE TABLE \`$db\`.\`$tbl\`" 2>&1 | tee -a "$LOG"
    done
    cd ..
done

echo "=== $(date '+%F %T') OPTIMIZE END ===" >> "$LOG"
echo "Done. Log: $LOG"

執行與授權

chmod +x optimize_all.sh
./optimize_all.sh
# 或排程 (每週日 03:00)
0 3 * * 0 /root/scripts/optimize_all.sh >/dev/null 2>&1

日誌結果解讀

輸出訊息	含義	後續動作
`status OK`	優化成功，空間回收/索引重建完成	無
`Table is already up to date`	儲存引擎判定無碎片，無需操作 (InnoDB 通常顯示此訊息)	正常，略過
`Table does not support optimize`	引擎不支援 (如 Archive, CSV, 黑洞)	忽略或轉換引擎
`Error 1031: Table storage engine doesn't have this option`	同不支援	忽略
`Lock wait timeout exceeded`	長事務阻塞 DDL	檢查長事務、調整 `innodb_lock_wait_timeout`、擇時重試

進階優化策略

僅優化有碎片的表：先查 INFORMATION_SCHEMA.TABLES 的 DATA_FREE > 0 或 DATA_FREE / (DATA_LENGTH+INDEX_LENGTH) > 0.1 再執行。
線上 DDL 並行：5.6+ OPTIMIZE TABLE 採 ALGORITHM=INPLACE，可並發執行多表 (注意 innodb_online_alter_log_max_size)。
分區表：OPTIMIZE TABLE t PARTITION(p0, p1) 僅整理指定分區。
避開備份窗口：與 mysqldump --single-transaction、FLUSH LOGS 錯開，防鎖衝突。

InnoDB 特殊說明

InnoDB OPTIMIZE TABLE 實際執行 ALTER TABLE ... ENGINE=InnoDB (重建表) + ANALYZE TABLE (更新統計)。5.7+ 線上重建不鎖讀寫，但需額外臨時空間 ≈ 表大小。**大表 (>50GB) 建議用 pt-online-schema-change 或原地重建工具分批處理**。