MySQL道普請便り

第156回 pt-archiverを使って部分的にテーブルから情報を取得する

この記事を読むのに必要な時間:およそ 4 分

今までbackupに関しては,第153回 mysqlpumpを使ってバックアップを取ってみる第15回 mysqldumpを使ってバックアップするなどで紹介をしてきましたが,今回は第9回 pt-query-digestを使って遅いクエリーを発見する第116回 pt-upgradeを使ってインデックスチューニングの成果を確認するで紹介したPerocona Toolkitの1つ,pt-archiverを使って部分的に取得する方法を紹介します。

検証環境

今回は,第125回 phpMyAdminでDockerで建てたMySQLにアクセスするで記載したdocker-composeを利用して作成します。手元で簡単に試せるように,GitHubのわたしのレポジトリにサンプルコードとして置いてあるので,気軽に試したい方はgit cloneして試してみてください。試すにはdockerとdocker-composeが必要です。

pt-achiverを使用する

pt-achiverは,この連載でも何回か紹介しているPercona Toolkitのうちの1つで,MySQLのテーブルから別のテーブルやファイルとしてテーブルにある行を,文字通りアーカイブするためのツールになります。公式のドキュメントがあります。ぜひ一度よく読んで挙動を理解してから実行を行ってください。

では,pt-achiverを実際に実行してみましょう。今回は郵便番号データベースから北海道の行を取得してみます。

$ pt-archiver --souce u=root,p=password,h=127.0.01,D=zipcode,t=zipcode --file zipcode --no-delete --where "prefecture='北海道'"

さて,上記のように実行してみようとしたら,以下のようにエラーが発生してしまいます。

Cannot find an ascendable index in table at /usr/bin/pt-archiver line 3233.

こちらのエラー内容ですが,pt-achiverではINDEXや主キーが無いテーブルでは使用することができません。今回北海道の行を取得するので,それに合ったINDEXを以下のように貼りましょう。

root@267e35517618:/# mysql -uroot -ppassword zipcode
mysql> ALTER TABLE zipcode ADD INDEX index_prefecture(prefecture);

これでもう一度実行してみましょう。--sourceにはDSN形式で接続情報を記載します。今回はローカルのファイルにデータを取得するため--fileオプションで指定します。--no-deleteオプションですが,pt-archiverはアーカイブ済みの行をデータベース上から消してしまうため,消えてもらっては困る場合は必ずこのオプションを指定しましょう。とくに本番サーバに実行する場合には注意をして実行しましょう。

--whereオプションでWHERE句に指定したいWHERE句を書くことで取得ができます。

$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete --where "prefecture='北海道'"
$ cat zipcode
01101   060     0600000 ホッカイドウ サッポロシチュウオウク    イカニケイサイガナイバアイ 北海道 札幌市中央区  以下に掲載がない場合
$

このように,取得できていることがわかります。mysqlコマンドラインクライアントで取得したときと同様に,タブ区切りの文字になっています。ただ,なぜか1件しか取得されていません。このように,想定外の挙動が起きた場合に便利なのが,--dry-runオプションです。こちらを着けて実行してみます。

$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete --where "prefecture='北海道'" --dry-run
SELECT /*!40001 SQL_NO_CACHE */ `code`,`old_zipcode`,`zip_code`,`prefecture_kana`,`city_kana`,`town_kana`,`prefecture`,`city`,`town` FROM `zipcode`.`zipcode` FORCE INDEX(`index_prefecture`) WHERE (prefecture='北海道') ORDER BY `prefecture` LIMIT 1
SELECT /*!40001 SQL_NO_CACHE */ `code`,`old_zipcode`,`zip_code`,`prefecture_kana`,`city_kana`,`town_kana`,`prefecture`,`city`,`town` FROM `zipcode`.`zipcode` FORCE INDEX(`index_prefecture`) WHERE (prefecture='北海道') AND ((`prefecture` > ?)) ORDER BY `prefecture` LIMIT 1

上記のように,今回の指定オプションで実行されるSQLが出力されます。先ほど作成したINDEXがFORCE INDEXで利用されていることがわかります。そして最後にLIMIT 1がされているため,今回は1件しか取得されていません。北海道は,以下のようなクエリで8248件あることがわかりました。

mysql> select prefecture, COUNT(*) FROM zipcode where prefecture = '北海道';
+------------+----------+
| prefecture | COUNT(*) |
+------------+----------+
| 北海道     |     8248 |
+------------+----------+
1 row in set (0.01 sec)

北海道の8248件を--limitオプションで指定をして全部取得をしてみましょう。取得した値は上書きされてしまうので,1回zipcodeファイルを削除してからもう一度実行してみます。

$ rm zipcode 
$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete --where "prefecture='北海道'" --limit 8248 --statistics
$ cat zipcode |wc -l
    8248

ということで,取得ができていることがわかります。

著者プロフィール

木村浩一郎(きむらこういちろう)

株式会社オプティム 技術統括本部のエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter:@kk2170