MySQL ですべてのテーブルを一括で OPTIMIZE する方法

今日も小ネタで、 MySQL で特定のデータベース内のすべてのテーブルを一括で OPTIMIZE する方法についてです。

確認時のバージョン

• MySQL 5.7 or 8.0

MySQL ですべてのテーブルを OPTIMIZE する方法

早速結論ですが、 mysqlcheck コマンドの --optimize オプションを使えば OK です:

mysqlcheck --optimize --user="$MYSQL_USER" --password="$MYSQL_PASSWORD" "$MYSQL_DATABASE"

• --optimize | MySQL :: MySQL 8.0 Reference Manual :: 4.5.3 mysqlcheck — A Table Maintenance Program

ちなみに、これはこの方法（ mysqlcheck --optimize ）にかぎったことではありませんが、 InnoDB テーブルでは OPTIMIZE TABLE の代わりに ALTER TABLE ... FORCE が実行されるようになっています。 出力としては「 Table does not support optimize, doing recreate + analyze instead 」というメッセージが出力されます。

出力サンプル:

mysqlcheck --optimize --user="$MYSQL_USER" --password="$MYSQL_PASSWORD" "$MYSQL_DATABASE"
（略）
mydb.table_a
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
（略）

MySQL 公式のドキュメントにも説明があるので、詳細に興味のある方はそちらをご覧ください。

参考 1:

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

• InnoDB Details | MySQL :: MySQL 8.0 Reference Manual :: OPTIMIZE TABLE Statement

参考 2:

Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a “null” ALTER TABLE operation, which can be used to defragment an InnoDB table, as described in Section 15.11.4, “Defragmenting a Table”. Running ALTER TABLE tbl_name FORCE on an InnoDB table performs the same function.

• Table Options | MySQL :: MySQL 8.0 Reference Manual :: 13.1.9 ALTER TABLE Statement

参考

• sql - MySQL OPTIMIZE all tables? - Stack Overflow