背景
MySQLなどのRDBで、レコード数が数百万から一千万件以上のテーブルでは、主キーで検索しないと非常に遅いです。
WHERE句に渡すIDの数も自然と多くなりがちですが、その際に気をつける点があります
- 渡しすぎると、インデックスを使ってくれない
- テーブル件数の10%〜20%くらい
- MySQLでは65535個を超えるとエラーになる
この解決には、通常、chunksでクエリしてあげて対処します
（以下はlaravelでの例）
$ids = BaseModel::select('id')->get()->pluck('id');
collect($ids)->chunk(5000)->each(function (Collection $chunkedIds) {
$results = DB::table('big_data_table')
->whereIn('id', $chunkedIds)
->get();
foreach ($results as $row) {
}
});
しかしIDが10万個あるとか、非常に多いと、上記でもまだ遅いケースがあります。都度、DBサーバ側とのデータ通信が発生するためと思われます
IDが10万個の場合、上記のコードでは、クエリが20回繰り返され、通信I/Oのコストがかかるのと、返ってくるデータのサイズによっては、通信量が非常に多くなります
今回はこういう課題がある時、一時テーブル利用が効果的になり得るというお話をしたいと思います。
一時テーブルにIDを入れて内結合
こんな形でインデックス付きで一時テーブルを作成します
DB::statement(
"
CREATE TEMPORARY TABLE tempIds
(
tmp_id INT PRIMARY KEY
)
"
);
これに
INSERT INTO tempIds (tmp_id)
SELECT id FROM base_table WHERE xxx;
でIDのみを突っ込んであげて、このtempIdsと、検索対象の巨大テーブルを内結合すれば、通信・レイテンシに関するコストが発生することなく、絞り込みが完了します。
DBサーバ内で行われる上記のINSERTは、通信と比較するとそこまで時間がかかりません。
私の作業ケースでは、10秒以上かかっていたところが1秒未満になりました。
（ただ、インデックスが使われないほどIDが多いケースだと、結合がフルスキャンになってしまうので、通信コストはかかるが少量ずつ送ってインデックスを効かせられるケースとどちらが速いかは不明。また、結合の結果行はそのままアプリ側に返さず、サブクエリに使用していました）
気をつけること
- 一時テーブルの作成時は、マスタDBに接続している必要があり、作成後にレプリカDBに接続を戻すこともできますが、一時テーブル参照が終わってからの方が無難そうです
- 一時テーブルは別々のサブクエリでは使用できないので、複雑なクエリの場合は一時テーブルを複数用意する必要があります（しんどいです）
まとめ
今回、巨大テーブルの扱いで通信量を意識した時、一時テーブル導入が効果的な場合があることをご紹介しました。DBサーバ内で処理させればいいってことですね。
しかし、巨大テーブルを扱う場合は、PHP等での配列サイズも巨大になってしまいがちですので、そちらには別途の対処が必要となりますね。機能的に使いたいテーブルでデータが膨れ上がった場合、本来はパーティション分割やデータマイグレーションを検討したいところですが、それが難しい場面での対処法メモでした。
Discussion