MySQL 表優化、分析、檢查與修復實務指南

核心維護任務

資料庫例行維護包含四大項：優化、分析、檢查、修復，主要針對 MyISAM 與 BDB 表。

一、表優化 — 消除碎片、回收空間

頻繁 INSERT/UPDATE/DELETE 導致變長欄位 (BLOB, VARCHAR) 產生碎片，降低 I/O 效能。

三種執行方式

-- 1. SQL 語句 (線上，會鎖表)
OPTIMIZE TABLE tbl_name;

-- 2. mysqlcheck (線上，支援多表/整庫)
mysqlcheck -o db_name tbl_name -u root -p
mysqlcheck -o db_name -u root -p          # 整庫

-- 3. myisamchk (離線，需停服務或確保無存取)
myisamchk --quick --check-only-changed --sort-index --analyze tbl_name
myisamchk -r tbl_name                     # 修復並回收空間
myisamchk -r /path/to/data/db/tbl_name    # 指定路徑

注意：優化期間表會被鎖定，避開尖峰時段；需預留足夠磁碟空間 (臨時檔案)。

二、表分析 — 更新索引統計資訊

長期運作後索引統計過期，導致優化器產生劣質執行計畫。大量寫入後應執行分析。

三種執行方式

-- 1. SQL
ANALYZE TABLE tbl_name;

-- 2. mysqlcheck (線上，僅 MyISAM)
mysqlcheck -a db_name tbl_name -u root -p

-- 3. myisamchk (離線)
myisamchk -a /path/to/data/db/tbl_name

InnoDB 不支援 ANALYZE TABLE，會報錯。

三、自動化腳本範例

以下 Bash 腳本可放入 crontab 定期對所有資料庫所有表執行 ANALYZE TABLE：

#!/bin/bash
MYSQL_DIR=/usr/local/mysql
USER=root
PASS=root
SOCK="/tmp/mysql.sock"

DBS=$($MYSQL_DIR/bin/mysql -u$USER -p$PASS -S$SOCK -BsNe "SHOW DATABASES")
for DB in $DBS; do
    echo "Analyzing $DB ..."
    TABLES=$($MYSQL_DIR/bin/mysql -u$USER -p$PASS -S$SOCK -BsNe "SHOW TABLES" $DB)
    for TBL in $TABLES; do
        $MYSQL_DIR/bin/mysql -u$USER -p$PASS -S$SOCK -BsNe "ANALYZE TABLE $TBL" $DB
    done
done

四、表檢查與修復

• CHECK TABLE / mysqlcheck -c / myisamchk：偵測錯誤、更新索引統計
• REPAIR TABLE / mysqlcheck -r / myisamchk -r：修復損壞的 MyISAM 表

修復前請備份資料檔 (.MYD, .MYI, .frm)。