はったりエンジニアの備忘録

MySQL で別テーブルのデータを使って UPDATE するには、JOIN を使うほかに MySQL 独自の書き方ができます。

UPDATE table_foo, table_bar
SET    table_foo.name = table_bar.name
WHERE  table_foo.id   = table_bar.id;

UPDATE 句の後に更新したいテーブルと参照したいテーブルを並べて内部結合します。 INNER JOIN や LEFT JOIN に比べるとシンプルに書けるので、この書き方を好んで使っていました。

今回、数百万レコードを対象に UPDATE するバッチ処理を書いていると、どうもこの SQL がボトルネックになっていることが分かりました。ためしに INNER JOIN に変えてみると実行時間が短縮されました。

UPDATE     table_foo
INNER JOIN table_bar
ON         table_foo.id   = table_bar.id
SET        table_foo.name = table_bar.name
WHERE      table_foo.id   = table_bar.id;

検証してみた

MySQL 5.1 系と 5.5 系を使って検証してみました。

• 5, 10, 20, 40 万レコードのダミーデータを使用
• LOAD DATA INFILE を使って CSV ファイルからインポート
• 計測ごとに全テーブルを TRUNCATE
• ストレージエンジンは InnoDB

まず MySQL 5.1 の結果です。 20 万までは MySQL 独自の書き方が速いですが、40 万を超えると 0.5 秒以上の差をつけて INNER JOIN が逆転します。

次に MySQL 5.5 の結果です。同じ SQL なのに 5.1 と比べて大幅に改善されていることがわかります。こちらも 20 万を超えると僅差ですが INNER JOIN が逆転しました。

バージョンに関わらず、数百万レコードを扱うときは JOIN を使った結合が速くなるようです。先日リリースされた MySQL 5.6 では InnoDB がさらに改善されたそうなので、この結果も変わってくるかもしれません。