MySQL 對 MyISAM、InnoDB 使用 Optimize Table

[yehlu]

http://blog.longwin.com.tw/2012/03/mysq ... gle+Reader

系統用久了, 自然就會有不連續的碎片(fragmented)產生, 以前 Dos 使用 defrag, Windows 使用磁碟重組, 而 MySQL 則是使用 Optimize table.

以往都是使用: 使用 PHP 對所有 MySQL Database 做 Optimize / Repair 的動作 - 這裡面的那隻程式來跑.

這次突然想到對特定的 Table 手動執行一下, 意外看到下述的訊息:

mysql> OPTIMIZE TABLE foo;

+----------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status | OK |
+----------+----------+----------+-------------------------------------------------------------------+

雖然結果一樣, 但是還是查查看差別為何~


Table does not support optimize, doing recreate + analyze instead 原因 與 處理

於 MySQL 5.1 Reference Manual :: 12.7.2.5 OPTIMIZE TABLE Syntax 此文件有寫

For InnoDB tables, *OPTIMIZE TABLE is mapped to ALTER TABLE*, which rebuilds the table to update index statistics and free unused space in the clustered index.

問題

OPTIMIZE TABLE 與 ALTER TABLE 如何對應?
到底是怎麼對應的? 那如果用 ALTER Table 要如何使用?
此篇: Table does not support optimize, doing recreate + analyze instead 有講對應的作法:

Optimize 正常都會出現 status ok (MyISAM), 但是 InnoDB 的都會出現此訊息: Table does not support optimize, doing recreate + analyze instead.

所以對 InnoDB 的話, 就使用下述語法: (請自行將 table.name 改成自己的 Table 名稱)

ALTER TABLE table.name ENGINE='InnoDB';
這語法的作用會是: 建立一個新的 Table, 由舊的 Table 將資料拷貝進來, 然後再把舊的 Table 砍掉, 但是, 作者建議先備份後再來執行此動作比較好.

結論

目前還是使用 Optimize Table 就好, MySQL 現在都還是會對應處理好.
以後如果有問題, 確定硬碟空間夠大, 再來考慮使用 ALTER TABLE 處理.
OPTIMIZE TABLE shell script

此作者有另外一篇文章, 也是 Optimize 的 Script, 看起來也不錯用: Optimize only fragmented tables in MySQL
vim mysql_optimize.sh
#!/bin/sh
echo -n "MySQL username: " ; read username
echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo

mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
if [ "$datafree" -gt 0 ] ; then
fragmentation=$(($datafree * 100 / $datalength))
echo "$database.$name is $fragmentation% fragmented."
mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"
fi
done
done

chmod +x ./mysql_optimize.sh
./mysql_optimize.sh # 輸入 MySQL 帳號、密碼 就會自動執行囉~