Optimize 正常都會出現 status ok (MyISAM), 但是 InnoDB 的都會出現此訊息: Table does not support optimize, doing recreate + analyze instead.
mysqlcheck -o --all-databases -uroot -p -hlocalhost
訊息如下
wordpress.wp_commentmeta Table is already up to date wordpress.wp_comments Table is already up to date wordpress.wp_croer_meta Table is already up to date wordpress.wp_croer_posts Table is already up to date wordpress.wp_filemeta Table is already up to date wordpress.wp_links Table is already up to date wordpress.wp_options Table is already up to date wordpress.wp_postmeta Table is already up to date wordpress.wp_posts Table is already up to date wordpress.wp_statpress OK wordpress.wp_term_relationships Table is already up to date wordpress.wp_term_taxonomy Table is already up to date wordpress.wp_terms Table is already up to date wordpress.wp_usermeta Table is already up to date wordpress.wp_users Table is already up to date wordpress.wp_wpb2d_excluded_files note : Table does not support optimize, doing recreate + analyze instead status : OK wordpress.wp_wpb2d_options note : Table does not support optimize, doing recreate + analyze instead status : OK wordpress.wp_wpb2d_processed_dbtables note : Table does not support optimize, doing recreate + analyze instead status : OK wordpress.wp_wpb2d_processed_files note : Table does not support optimize, doing recreate + analyze instead status : OK
優化InnoDB table,請先備份你的資料庫再執行
ALTER TABLE table.name ENGINE='InnoDB';
網路有找到OPTIMIZE TABLE shell script的程式來優化mysql
原文在：http://www.justin.my/2010/09/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帳密就可以執行
參考資料：
http://blog.longwin.com.tw/2012/03/mysql-myisam-innodb-optimize-2012/
http://www.justin.my/2010/09/optimize-only-fragmented-tables-in-mysql/