MySQLの全テーブルを最適化(OPTIMIZE TABLE)する

PHP

2018/12/16 (更新日：2019/03/31)

パッケージソフトの開発会社や、業務系システムの開発会社にいた頃はデータベースといえばSQL ServerやOracleでした。

独立してからはオープンソース化の流れとライセンスの都合上でPostgreSQLを触る機会が多くなりましたが、MySQLを本格的に触り始めたのはわりと最近になります。MariaDBで興味を持ったのがきっかけですね。

Webサイトで使うデータベースを決めたら、まずメンテナンス用のスクリプトを組むのですが、はて、MySQLの場合はvacuumコマンド^※がないのかな、と不思議に思いました。

PostgreSQLもバージョンアップによってvacuumしなくて良くなった！と聞いたときには、え、そんなバカな、と思いつつも一応調べてみるとauto vacuumできるようになったというだけでしたし。

MySQLのInnoDBは追記型データベースではないと言ってもメンテナンスコマンドはあるハズだろうと少し調べてみました。

そもそもMySQLでvacuumは必要なのか

MySQLのInnoDBはPostgreSQLの追記型データベースとは違い、vacuumは不要………………と思いきや、UPDATEはともかくDELETE実行時はやはり大量の断片化が起こるのだとか。

実際、WordPressをインストールしてしばらく使っていたら多少の断片化が出来ていたのでそういうものなのでしょう。もしかしたらデータベースエンジンが自動的に最適化してくれるのかも知れませんが、通常のWeb運営においては朝方とかアクセスの少ない時間帯の予想がつきますから、その時間に合わせて自分で最適化したほうが良いかなぁー。

ほら、WindowsでもHDDのデフラグは自動化されてるけど、明らかにしばらく使わないときはとりあえず手動でデフラグ実行したりしませんか。あるいは24時間稼働させてるPCなら深夜のPC使っていない時間帯に自動実行させたり。

ま、そんな感じ（？）でMySQLテーブルの最適化も実装してみましょう。

MySQLでのテーブル最適化方法

コマンドラインで実行するかOPTIMIZE TABLEのSQLを投げます。

コマンドラインで最適化

mysqlcheck -o [データベース名]

もしくは全データベースを対象にこんな感じ。

mysqlcheck -o --all-databases

レンタルサーバー上でmysqlcheckコマンドが使えるかわからなかったので、うちはPHP上でOPTIMIZE TABLEを実行することにしました。

OPTIMIZE TABELを全テーブルに実行

PostgreSQLでは「vacuum」とSQLクエリに投げるだけで全テーブル最適化してくれたのですが、OPTIMIZE TABLEの場合はひとつひとつテーブル名を指定しなければならないのでPHPで作りました。

$cfg ['PDO_DB'] = '[データベース名]';
$cfg ['PDO_DSN'] = 'mysql:host=localhost;charset=utf8;dbname='.$cfg['PDO_DB'];
$cfg ['PDO_USER'] = '[ユーザー名]';
$cfg ['PDO_PASS'] = '[パスワード]';
//DB接続
$pdo_option = array(
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
	PDO::ATTR_EMULATE_PREPARES => false,
	PDO::ATTR_STRINGIFY_FETCHES => false
);
$conn = new PDO($cfg['PDO_DSN'], $cfg['PDO_USER'], $cfg['PDO_PASS'], $pdo_option);
//テーブル最適化
$result = $conn->query("SHOW TABLE STATUS");
while ($row = $result->fetch()) {
	if ($row['Data_free'] > 0) {
		$conn->query("OPTIMIZE TABLE ".$row['Name']);
	}
}

まぁ何のことはなく、SHOW TABLE STATUSでテーブル一覧を取得し、1つ1つOPTIMIZE TABLEを実行しているだけです。