為什麼需要 OPTIMIZE TABLE？
- 大量刪除資料後，留下「空洞」（刪除記錄維持在鏈表中）
- 可變長度行（VARCHAR/BLOB/TEXT）頻繁更新導致碎片
OPTIMIZE TABLE 可重組資料檔案，回收未使用空間，整理碎片
自動化腳本：optimize.sh
#!/bin/sh
# 用法: sh optimize.sh db_name1 [db_name2 ...]
time_log=/opt/optimize_time
sum=$#
if [ "$sum" -eq 0 ]; then
echo "Error: no parameter chosen"
exit 1
fi
for db in "$@"; do
echo "optimize database $db starting ..."
# 獲取資料庫所有表名
tables=$(/usr/bin/mysql $db -udevuser -pdevuser -e "SHOW TABLES" | grep -v "Tables" > /opt/$db)
tablelist=$(cat /opt/$db)
echo "optimize database $db starting ................" >> $time_log
echo "$db start at $(date +[%Y/%m/%d/%H:%M:%S])" >> $time_log
for table in $tablelist; do
echo "Optimizing $table ..."
/usr/bin/mysql $db -utaobao -padmin -e "OPTIMIZE TABLE $table"
done
echo "$db end at $(date +[%Y/%m/%d/%H:%M:%S])" >> $time_log
echo "" >> $time_log
done
腳本關鍵點解析
| 部分 | 說明 |
|---|
| 參數處理 | $@ 遍歷所有傳入的資料庫名稱 |
| 獲取表名 | SHOW TABLES 配合 grep -v "Tables" 過濾標題行 |
| 日誌記錄 | 記錄開始/結束時間，便於審計與效能分析 |
| 認憑資訊 | 建議改用 --defaults-extra-file 指定密碼檔，避免明文密碼 |
生產環境優化建議
- 加入
--skip-lock-tables 或使用 pt-online-schema-change 避免長時間鎖表
- 僅對碎片率超過閾值的表執行（可先用
ANALYZE TABLE 檢查）
- 排程設定：
crontab -e → 0 3 * * 0 /opt/optimize.sh db1 db2（每週日凌晨 3 點）
- 監控磁碟 I/O，避開業務高峰期
- InnoDB 表
OPTIMIZE TABLE 相當於 ALTER TABLE ... ENGINE=InnoDB，會重建表，需預留足夠磁碟空間
手動單表優化
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
LOCAL：不寫入二進位日誌（不同步到從庫）NO_WRITE_TO_BINLOG：同 LOCAL
