データ抽出バッチ処理速度改善

6万件の会員データ(会員テーブル)があったとして、
その中から特定の条件の会員情報を3万件程抜き出すバッチ処理があった場合。
バッチ処理内のデータ抽出処理の箇所は下記①・②とでどちらが速度的に早いのでしょうか？

①
SQL文にて
SELECT * FROM 会員テーブル WHERE user_id in (特定条件下の会員リスト)
にて一気に抽出する。

②
SELECT * FROM 会員テーブル
にて一旦全会員分のデータ抽出後に、PHPのプログラムにてforeachでループさせて、
特定条件下の会員を絞り込む。

元々は①だったのですが、処理時間がかかり過ぎていたので、

行動規範の内容に同意します

回答8件

ベストアンサー

DB側でできることはDB側でやるのが原則ですが、50%取り出すなら微妙ですね。構成とデータ量とクエリによっては全件取り出してプログラム側で処理する方が良いかもしれません。

MySQL5.6以前にはINでサブクエリを使うと極端に遅いという癖がありました。こういうこともあるので、何が遅いかは必ず確認した方が良いです。

EXPLAINを使うとSQLの実行計画を確認できます。
https://dev.mysql.com/doc/refman/5.6/ja/execution-plan-information.html

闇雲にインデックスを張るとかえって性能が低下する場合があります。バッチ側で変化がなくても、他のアプリケーションで影響がでることもあります。インデックスがないことが原因だということを確認してからにしましょう

投稿2017/08/21 06:00

suzukis

総合スコア1449

whxtna

2017/08/22 02:20

ご回答ありがとうございます。自分も”MySQL5.6以前にはINでサブクエリを使うと極端に遅いという癖”こちらを懸念しておりました。回答いただいている内容を参考にし、再確認しようと思います。

行動規範の内容に同意します

3万件/6万件だとインデックスの効果はあまりないでしょうね。

4.正攻法として、ボトルネックを探して改善していく。
ネットワークが遅いのか、I/O処理が遅いのか、実行時(サーバ側)の問題かなどで対処法は全然違ってきます。

5.特定条件下の会員リストを持つテーブルを作る(user_idのみのテーブル)
特定条件下の会員リストの変更頻度が少なければこれが最速になりそうです。

SQL
1/* 
2DELETE FROM user_id_table;
3INSERT INTO user_id_table(user_id) VALUES (2),(3),(6),(8)
4*/
5SELECT 会員テーブル.* FROM 会員テーブル INNER JOIN user_id_table ON 会員テーブル.user_id = user_id_table.user_id

6.可能であれば、会員テーブルに出力フラグのカラムを追加する

投稿2017/08/21 07:17

hihijiji

総合スコア4150

whxtna

2017/08/22 02:29

ご回答ありがとうございます。参考にさせていただきます。

行動規範の内容に同意します

テンポラリテーブルをつくり、検索したいidを流し込んでおいて
inner joinで処理してはいかがでしょうか？
もちろん適正なインデックスは付加する必要はあると思います

また、そのid群が再利用する可能性が高いなら、適当な出力用のフラグを
新たにカラムとして登録しておけば良いと思います

投稿2017/08/21 05:41

編集2017/08/21 05:42

yambejp

総合スコア114784

whxtna

2017/08/22 02:18

ご回答ありがとうございます。参考にさせていただきます。

行動規範の内容に同意します

皆さんの記載の通り、どちらが重たいのか確認したほうが良さそうですね。

SQL
1(a).SELECT * FROM 会員テーブル WHERE user_id in (特定条件下の会員リスト)
2(b).特定条件下の会員リスト

・INからEXISTSに変更してみる
・(a)は50%とのことなので、インデックス効果が微妙かと。
・(b)は改善の余地はありそうです。抽出条件に沿ったインデックスを付与するなり、一時テーブルを作って、JOINしてみたらいかがでしょうか。
・列指定を[*]としているのであれば、抽出項目を必要分にしてデータ量を減らすとかでしょうか。

投稿2017/08/21 11:58

roast_chicken

総合スコア254

whxtna

2017/08/22 02:33

ご回答ありがとうございます。参考にさせていただきます。

行動規範の内容に同意します

一般に、インデックスが有効に利くのは全データに対するヒット率が10%を切るあたりではないかと思います。
インデックス検索＋インデックスで見つけたデータにランダムアクセス、という処理が、I/O としてみた場合に単純にI/O回数が多く、遅くなるからです（SSD であってもシーケンシャルアクセスよりランダムアクセスが遅いのは言わずもがな）。
また、SELECT 対象の列にも依存します。仮に対象列の情報がすべてインデックスの構成要素であるならば、実データにアクセスする必要がない（インデックスだけ見ればよい）ので、早くなります。

全体の半分が対象となると、実際の抽出 SQL でも、全件取り出して要らないものを捨てている可能性が高いです。
※全件を、それも sort なしで取り出すということは、DB からみるとレコードを記録しているファイルをシーケンシャルアクセスすればよいことになるため、インデックス経由のランダムアクセスより早くなります

投稿2017/08/21 11:29