はじめに
各種のDB中の集計作業を行うにあたって、以下のようなデータをどうしても扱わなければならない場合は、SQLのみで完結させないことを検討しなければ、サーバリソースを大量に消費した挙句、何時まで経っても作業が完了しないということが有ります。
SQL操作だけに頼らないことを検討したい集計例
explainすることで大体の傾向はわかりますが、おおよそ以下のような要素を含む集計が対象となります。
- 対象のレコード数が巨大である（数百万レコード以上）
- DBの余剰リソースにもともと余裕が無い
- indexのふられていない項目で集計/joinしなければならない
- join対象の項目がやけに多い
- not in
- 巨大なサブクエリ
- is null
- group by
- like
- etc...
ではどうするか？
集計対象のレコードを一旦ファイルに吐き出した上で、ファイルベースの処理をしてしまいましょう。
実践例1
table1, table2, table3のレコード数がそれぞれ数百万レコード~数千万レコードでの規模での以下の集計を考えてみましょう。
SELECT count(id)
FROM table1 JOIN table2 Using(id)
WHERE table1.col1 = 'hoge'
AND table2.col2 is not null
AND id NOT IN (SELECT id FROM table3 WHERE table3.col3 like '%fuga%');
これをそのまま走らせると数十分から数時間、下手したらDBのメモリリソースを使い果たした上でテンポラリテーブルが作られて、ファイルソートが始まって、、となるのですが、上記の作業は以下の操作と等価になります。
まず、集計対象のレコードをファイルに抽出します。
mysql -u *** -p -q -N -e "SELECT id FROM table1 JOIN table2 Using(id) WHERE table1.col1 = 'hoge' AND table2.col2 is not null" > id1.txt
mysql -u *** -p -q -N -e "SELECT id FROM table3 WHERE table3.col3 like '%fuga%'" > id2.txt
※上記2つの操作は完全に別のテーブルに対するSelectなので、DBのcpuコア数などに余裕があればscreenコマンドなどで並行で出力しても大丈夫です。
※ -qオプションを指定することで実行結果が巨大であるかどうかに関係なく、随時出力をするようになりますので、操作中のサーバのメモリリソースの使用を抑える事ができます。
※ -Nオプションでカラム名を表示させないようにしています。
次に、あとでcommコマンドを利用するためにそれぞれのファイルをソートしておきます。
sort id1.txt > id1.sort.txt
sort id2.txt | uniq > id2.sort.txt
※ソートや、ユニークを取る操作に関しても、SQLで行うより、unixコマンドでのファイルベースですと比較的少ないリソース使用量で高速に行うことが出来るケースが多いです。
最後に、id_1.sort.txt に含まれているが、id_2.sort.txtに含まれていないidの抽出をし、カウントします。
comm -23 id_1.sort.txt id_2.sort.txt > id1_not_in_id2.txt
wc -l id1_not_in_id2.txt
※ commコマンドは、ソートされた2つのファイルA/Bの ファイルAにのみ含まれる行 共通行 ファイルBにのみ含まれる行 をそれぞれ出力するコマンドです。 -23をつけることで、共通行およびファイルBにのみ含まれる行の出力を抑制します。
追記
joinコマンドを利用すると、単独のカラム同士でなくても結合できますので、RDBにおけるテーブルのjoinと同様の事がファイルベースで高速にできます。
JOINコマンドを使う際の注意