大量データのあるテーブルにはALTER TABLEよりmysqldumpしてインポートのほうがよさげ

2011/7/20 16:30

MySQLで大量にデータのあるテーブルにALTER TABLEなどを実行しようとすると果てしなく時間がかかることがある。

» 大きめのテーブルにカラムやインデックスを追加する際の注意 – Slow Dance

処理中に show processlist; を見ると State の欄にたいてい「copy to tmp table」という状態が出力されていて、その状態が延々と続く。コピーが一体どこまで進んでいるのか把握できないので精神衛生上よろしくない。

で、代替の方法としてさっきの記事の最後のほうで紹介されている「copy to tmp table – 刺身☆ブーメランのはてなダイアリー」に書かれている「mysqldump してから新しく作成したテーブルにインポート」という方法がある。

試してみたらこの方法はかなり良い。まず mysqldump は –quick オプションを使って比較的早く終わる。さらに、ダンプ中に書きこまれているファイルのサイズが増えていくのが見えるので、どの程度ダンプが進んでいるかがわかる。

ダンプしたデータを再インポートする際も、show processlist; には「Info」の欄にINSERT文が表示されるので、どこまでインポートが進んでいるかが把握できる。

全体の実行時間もALTER TABLEより速かった。と言っても自分の環境の場合6時間かかっても終わらなかったものが3時間くらいで終わったというレベルなので、時間がかかることは変わらないが。

ちなみに元データが消えてしまうのはちょっと怖いので、mysqldumpを実施する際に –no-create-info オプションでCREATE TABLE情報は出力しないようにして、元のテーブルを別名にRENAME、新しく元のテーブル名で CREATE TABLE を実行してテーブルを作成し、そのテーブルにダンプデータを流し込むようにした。