MySQL 對 MyISAM、InnoDB 使用 Optimize Table

系統用久了, 自然就會有不連續的碎片(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

1. 此作者有另外一篇文章, 也是 Optimize 的 Script, 看起來也不錯用: Optimize only fragmented tables in MySQL
2. 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

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