結合数の多いクエリのパフォーマンスについて教えて下さい。
解決済
回答 6
投稿
- 評価
- クリップ 2
- VIEW 48K+
javaから複数回クエリを実行する場合、それらをすべてjoinまたは副問合せして1回で実行した方がパフォーマンスは上がると思いますが、
例えば、結合数が100個だとしても同じでしょうか?
仮に500万レコードのテーブルを100個結合または副問合せすることになった場合、少しでもパフォーマンスを上げるにはどのような方法があるでしょうか?
例えば、
where句で条件を記述するか、on句にandで記述するかの違い。
複数条件を記述する場合はよりレコード数を絞れる条件を先に記述するなど。
-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 過去に投稿した質問と同じ内容の質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
+4
ただ、JOINしたテーブルの複数にまたがるようなWHERE条件をかけていると、「いったんすべてJOINしてからWHERE」という非効率なことになって、大きくパフォーマンスが落ちることがあります。最近自分が遭遇したケースでは、4つのテーブルにまたがるようなSELECTで、WHERE条件が3テーブルにまたがるようにしていた場合に1秒かかったクエリが、事前に2テーブルのWHERE条件を別途で抽出しておいて、WHEREで絞るテーブルを1つにまとめたところ、(事前抽出も含めて)0.1秒以下で終わるようになりました。
あと、毎回100個ものテーブルをJOINするのが適切なのか、検討が必要かもしれません。もっとも、日計処理のように、リアルタイム実行しなくていいものであればそこまで困ることもないのですが。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
+4
OracleのEditionによっても対応策は変わるでしょうし、
そもそも設計から見直したほうがいい気もします。
そのタイミングで人間が使うデータ、という意味ではそんな大量データ見れませんし、
件数の絞り込みもできると思います。
データの分類でテーブル分けしておくなり、パーティション分割するなり、
マテリアライズドビュー使ってサマリテーブル作っておくなり、
そもそもアプリ含めて参照DBを複数たてるなり、
KVSを部分的に使うなり、オンメモリも一部検討するなり、
別の観点での対応が必要かと。
その現場によってできるできないもあるでしょう。
SQLだけでの解決は現実的ではありませんが、
そこまでしか自身の立場で関与できないのであれば、
いろいろSQL文編集しながら実行計画読んで
最適解探していくしかないんじゃないでしょうか。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
+3
データベースサーバー側のリソースにもよりますが、CPUやメモリーが十分であれば、中間処理を省いたり通信部分のコストが削減できる点で、一度に処理した方がパフォーマンスは向上すると思います。
パフォーマンスとは別に、Java側で集計やソートするのは、Java8もしくは専用のライブラリーでも無ければコードが冗長になりがちなので、極力
UNION
やJOIN
、副問合せを駆使して一度で済ませた方が良い、と個人的には思います。
大量のテーブルを結合する場合、主となるテーブルでの絞込みが十分に行われており、被結合テーブルをカーディナリティ度が高いインデックスのキー(PK含む)で結合することができれば、結合するテーブルの数が多くても別々に実行するよりパフォーマンスが低下することはないでしょう。
ただし、結合の数と組み合わせによっては挙動を推測するのが難しくなるので、やはり一概には言えないところではあります。
それ以外にも、一部の結合には、ビューを作っておくのも手です。
WHERE
の中で結合するか、JOIN ... ON
の違いは、同じ意味になるならSQLの長さだけの問題程度と思います。どのインデックスが使用されるかの方が重要です。
最後になりますが、
どのような方法でも、DB設定や11gのオプティマイザーの挙動によっては想定外のアクセスパスが使用される可能性もありますので、最終的にはSQLトレースで実測してください。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
+2
以前ある現場で、1時間待っても帰ってこなかったクエリが、結合でレコードの少ないテーブル(マスタなど)から先に参照させることで、2~3秒で返ってきました。
ご参考まで。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
+1
500万レコードのテーブル100個に跨る様なリクエストが発生する様な状況は、設計段階で排除すべきです。
それだけ複雑なテーブル構造で最適なSQLを作り、最適なチューニングを行うのは、もはや職人芸と言っても過言ではないです。
(理論上は、実行計画的、且つインフラ的に最適化されていれば性能は担保できると思いますが)
そういったいわゆるビッグデータ系のデータを扱う場合、RDBではそもそも限界があるので、
全てを基本プライマリキーのみで検索できるように設計した上で、NoSQL系DBを利用すべきでしょう。
もしくはカラムナー系のDBを利用するかですね。
もし客先要件等のめんどくさい理由からOracleしか使えないという事であれば、
MySQLをNoSQLDBとして利用するノウハウを流用できるかもしれません。
ちなみにですが、DBサーバを複数台に分散している様な場合には、物理的なサーバ間を跨ぐようなリクエストをすると、
どう頑張ってもパフォーマンスは一定以上に上がらないので、ご注意ください。
以上、ご参考になれば幸いです。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
checkベストアンサー
0
JOINでは指定したテーブルのすべてのレコードに対してON句に指定した条件で照合が行われます。WHERE句で指定された条件はJOINで作成された仮想表に対して集約を行うので、母数に変化はありません。
基本的には、数万行から数十万行のデータをもつテーブルを連結する場合、
①ON句に指定する結合条件にインデックスサーチが効くようにする。結合条件で指定する列にINDEXを張る、もしくは主キーを使う。複合主キーの場合は、主キーに使用される列すべてをそろえて指定しインデックスサーチがきくようにする。
②サブクエリを用いてあらかじめWHERE句で集約を行い、JOINされるテーブルのレコードの母数を減らす。
のような工夫をおこないます。
あとはそうですね、ロジックの組み方にもよりますが分けてとった方が早くなる、ということもよくあります。
数万回もクエリを投げると明らかに遅くなると思いますが、数回に分ける程度で済むのであれば、無理してJOINで結合して取得するより圧倒的に早いという印象です。
取得してからアプリケーションのロジックで結合を行う必要があったとしても、何かしらの条件で集約された結果に対しての照合になるので、照合・比較の回数は圧倒的に減ることになるからです。
実行計画などを確認して、結合でどのあたりがネックになるか確認しながら作業を進めることをお勧めします。
参考:表の結合を極めるチューニング
参考になれば幸いです。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう!
- ただいまの回答率 88.23%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる
2014/12/05 19:48
すいません、100個は大げさでした。
>ただ、JOINしたテーブルの複数にまたがるようなWHERE条件をかけていると、「いったんすべてJOINしてからWHERE」という非効率なことになって、大きくパフォーマンスが落ちることがあります。
これはwhere句で
t1.a=t2.b
t2.b=t3.c
t3.c=t1.d
という条件だと遅くなるという意味でしょうか?