MySQL道普請便り

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

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

便利なオプションに関して

その他の便利なオプションとして,--statisticsオプションがあります。このオプションを付けて実行すると,以下のように実行時にどういう処理が行われたのかが確認できるようになってます。

$ 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

Started at 2021-10-01T12:48:55, ended at 2021-10-01T12:49:09
Source: D=zipcode,h=127.0.0.1,p=...,t=zipcode,u=root
SELECT 8248
INSERT 0
DELETE 0
Action          Count       Time        Pct
commit           8249    11.7163      89.55
print_file       8248     0.3384       2.59
select              2     0.0300       0.23
other               0     0.9989       7.64

データの取得時に一貫性を保ちたいこともあると思いますが,その場合は--for-updateオプションを付けてみると,SELECT ~ FOR UPDDATE 構文を利用してロックが取られるので指定しましょう。

kimurakouichirounoMacBook-Pro:for_training_sql koichiro.kimura$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete --for-update --dry-run --where "prefecture='北海道'"
zipcode
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 FOR UPDATE
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 FOR UPDATE

上記のようにdry-runをしてみると,後ろにFOR UPDATEが付与されていることがわかります。

また,--whereオプションは必須のオプションとなるため,全件の取得をしたい場合はどうしたら良いのか? と悩むこともあると思いますが,その場合は1=1のような必ずtrueになる条件式を入れてあげれば取得することができます。

mysql> select count(*) from zipcode;
+----------+
| count(*) |
+----------+
|   124340 |
+----------+
1 row in set (0.02 sec)

zipcodeの行数は124340行になるので,LIMITに指定して試してみましょう。

$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --no-delete  --where "1=1"   --limit 124340
$ cat zipcode |wc -l
  124340

以上のように取得ができることがわかります。

最後に,--no-deleteオプションを付けないとどうなるかを確認していきたいと思います。

こちらのオプションは付けず実行してしまうと対象のDBから行が削除されてしまうという様に説明をしましたが,実際どうなってしまうか確認してみましょう。実行結果も確認がしたいので--statisticsオプションを付けて実行してみましょう。

$ rm zipcode
$ pt-archiver --source u=root,p=password,h=127.0.0.1,D=zipcode,t=zipcode --file zipcode --statistics  --where "1=1"   --limit 124340
Started at 2021-10-01T13:14:04, ended at 2021-10-01T13:23:09
Source: D=zipcode,h=127.0.0.1,p=...,t=zipcode,u=root
SELECT 124340
INSERT 0
DELETE 124340
Action          Count       Time        Pct
commit         124341   378.4032      69.43
deleting       124340   148.2469      27.20
print_file     124340     2.4886       0.46
select              2     0.3625       0.07
other               0    15.4997       2.84

deletingが実行されていることがわかります。MySQLに入って確認してみましょう。

mysql> select COUNT(*) from zipcode;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

ということで,データを取得した後のデータが無くなってしまっていることがわかります。実行する際には--no-deleteオプションが必要かどうか,必ずちゃんと確認してから実行をするようにしましょう。

まとめ

pt-archiverを使用すると,部分的にファイルを抜き出して利用するということがかんたんにできるので,行数がすごく多いテーブルから抽出して分析をしたい場合などに便利です。mysqlクライアントから抜き出して同様の事もできるのですが,NULLの処理など面倒なことが多いです。そのため,このpt-archiverを利用してデータの一部を取得して利用できるのは非常に便利です。

ただし,--no-deleteオプションを着け忘れて実行してしまうと,解説を行ったとおりDB上からデータが無くなってしまうため注意が必要です。本番に直接かけるような事は控えたほうが安全だと思います。

また,取得したファイルのロードには,LOAD DATA INFILE構文を使用する必要があるため,FILE権限が必要な事にも注意が必要です。

著者プロフィール

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

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

Twitter:@kk2170