結合数の多いクエリのパフォーマンスについて教えて下さい。

Question

結合数の多いクエリのパフォーマンスについて教えて下さい。

解決済

回答 6

投稿 2014/12/05 00:25

評価
クリップ 2
VIEW 39K+

lupus_dingo

score 275

javaとOracleデータベースの組合せで開発をしています。
javaから複数回クエリを実行する場合、それらをすべてjoinまたは副問合せして1回で実行した方がパフォーマンスは上がると思いますが、
例えば、結合数が100個だとしても同じでしょうか？

仮に500万レコードのテーブルを100個結合または副問合せすることになった場合、少しでもパフォーマンスを上げるにはどのような方法があるでしょうか？
例えば、
where句で条件を記述するか、on句にandで記述するかの違い。
複数条件を記述する場合はよりレコード数を絞れる条件を先に記述するなど。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

クリップを取り消します
良い質問の評価を上げる

以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します

評価を下げられる数の上限に達しました

評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる

teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します

この機能は開放されていません

評価を下げる条件を満たしてません

評価を下げる理由を選択してください
プログラミングに関係のない質問やってほしいことだけを記載した丸投げの質問問題・課題が含まれていない質問意図的に内容が抹消された質問広告と受け取られるような投稿
詳細な説明はこちら

上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。
質問の評価を下げる機能の利用条件

この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
- メールアドレスの認証
  メールアドレスの認証
- 質問評価に関するヘルプページの閲覧
  質問評価に関するヘルプページの閲覧

回答 6 件

評価が高い順
新着順
古い順

+4

はっきり言って、「ケースバイケース」以上の結論は出ませんので、場面に応じてパフォーマンスをチェックする必要があります。

ただ、JOINしたテーブルの複数にまたがるようなWHERE条件をかけていると、「いったんすべてJOINしてからWHERE」という非効率なことになって、大きくパフォーマンスが落ちることがあります。最近自分が遭遇したケースでは、4つのテーブルにまたがるようなSELECTで、WHERE条件が3テーブルにまたがるようにしていた場合に1秒かかったクエリが、事前に2テーブルのWHERE条件を別途で抽出しておいて、WHEREで絞るテーブルを1つにまとめたところ、（事前抽出も含めて）0.1秒以下で終わるようになりました。

あと、毎回100個ものテーブルをJOINするのが適切なのか、検討が必要かもしれません。もっとも、日計処理のように、リアルタイム実行しなくていいものであればそこまで困ることもないのですが。

投稿 2014/12/05 09:22

2014/12/05 09:23

回答の評価を上げる

以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
回答の評価を下げる

下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

2014/12/05 19:48

回答ありがとうございます。

すいません、100個は大げさでした。

＞ただ、JOINしたテーブルの複数にまたがるようなWHERE条件をかけていると、「いったんすべてJOINしてからWHERE」という非効率なことになって、大きくパフォーマンスが落ちることがあります。

これはwhere句で
t1.a=t2.b
t2.b=t3.c
t3.c=t1.d
という条件だと遅くなるという意味でしょうか？

回答ありがとうございます。すいません、100個は大げさでした。＞ただ、JOINしたテーブルの複数にまたがるようなWHERE条件をかけていると、「いったんすべてJOINしてからWHERE」という非効率なことになって、大きくパフォーマンスが落ちることがあります。これはwhere句で t1.a=t2.b t2.b=t3.c t3.c=t1.d という条件だと遅くなるという意味でしょうか？

キャンセル

+4

ケースバイケースだと思います。

OracleのEditionによっても対応策は変わるでしょうし、
そもそも設計から見直したほうがいい気もします。

そのタイミングで人間が使うデータ、という意味ではそんな大量データ見れませんし、
件数の絞り込みもできると思います。

データの分類でテーブル分けしておくなり、パーティション分割するなり、
マテリアライズドビュー使ってサマリテーブル作っておくなり、
そもそもアプリ含めて参照DBを複数たてるなり、
KVSを部分的に使うなり、オンメモリも一部検討するなり、
別の観点での対応が必要かと。

その現場によってできるできないもあるでしょう。

SQLだけでの解決は現実的ではありませんが、
そこまでしか自身の立場で関与できないのであれば、
いろいろSQL文編集しながら実行計画読んで
最適解探していくしかないんじゃないでしょうか。

投稿 2014/12/13 20:00

回答の評価を上げる

以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
回答の評価を下げる

下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

2014/12/16 08:22

回答ありがとうございます。
100テーブルは言い過ぎでした。
クエリを組み込むときは実行計画を見るようにします。

回答ありがとうございます。 100テーブルは言い過ぎでした。クエリを組み込むときは実行計画を見るようにします。

キャンセル

+3

まず、DBの設定、各テーブルの正規化が適切になされていることが前提になります。

データベースサーバー側のリソースにもよりますが、CPUやメモリーが十分であれば、中間処理を省いたり通信部分のコストが削減できる点で、一度に処理した方がパフォーマンスは向上すると思います。

パフォーマンスとは別に、Java側で集計やソートするのは、Java8もしくは専用のライブラリーでも無ければコードが冗長になりがちなので、極力UNIONやJOIN、副問合せを駆使して一度で済ませた方が良い、と個人的には思います。

大量のテーブルを結合する場合、主となるテーブルでの絞込みが十分に行われており、被結合テーブルをカーディナリティ度が高いインデックスのキー（PK含む）で結合することができれば、結合するテーブルの数が多くても別々に実行するよりパフォーマンスが低下することはないでしょう。
ただし、結合の数と組み合わせによっては挙動を推測するのが難しくなるので、やはり一概には言えないところではあります。

それ以外にも、一部の結合には、ビューを作っておくのも手です。

WHEREの中で結合するか、JOIN ... ONの違いは、同じ意味になるならSQLの長さだけの問題程度と思います。どのインデックスが使用されるかの方が重要です。

最後になりますが、
どのような方法でも、DB設定や11gのオプティマイザーの挙動によっては想定外のアクセスパスが使用される可能性もありますので、最終的にはSQLトレースで実測してください。

投稿 2014/12/05 09:38

2014/12/05 13:04

回答の評価を上げる

以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
回答の評価を下げる

下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

2014/12/07 15:07

回答ありがとうございます。

100個はおおげさでした。すいません。
VIEWを作るのも手とありますが、私が思っているVIEWというのは
単純に問い合わせするSQLを保存しておくだけで、
結局は内部で同じものが実行されるので、SQLが見やすくなるだけで
パフォーマンス的には変わらないんじゃないかと勝手に思っているのですが
どうでしょうか？

また、UNION、MINUS、INTERSECTなどの集合演算子は集計を行うのでパフォーマンス的には避けたほうがよいと聞いたことがあるのですがなるべく使わないほうがいいのでしょうか？

回答ありがとうございます。 100個はおおげさでした。すいません。 VIEWを作るのも手とありますが、私が思っているVIEWというのは単純に問い合わせするSQLを保存しておくだけで、結局は内部で同じものが実行されるので、SQLが見やすくなるだけでパフォーマンス的には変わらないんじゃないかと勝手に思っているのですがどうでしょうか？また、UNION、MINUS、INTERSECTなどの集合演算子は集計を行うのでパフォーマンス的には避けたほうがよいと聞いたことがあるのですがなるべく使わないほうがいいのでしょうか？

キャンセル
2014/12/07 16:34

> SQLが見やすくなるだけでパフォーマンス的には変わらない
オプティマイザーは、場合によってはビュー定義のクエリーと追加のクエリーを、効率の良い形式に変換してくれるみたいです。そうなるようにVIEWを定義する、というのはまた一概には言えない話になりそうです。

> UNION、MINUS、INTERSECTなどの集合演算子は集計を行うのでパフォーマンス的には避けたほうがよい
集合させる必要が無いのに使うのは良くありませんが、集合させたければ使っても問題ないでしょう。
集計でも集合でも、まずはできるだけ局所的にデータを絞りこむことです。たとえば10のテーブルをそれぞれ10件まで絞り込んだあとであれば、集計なり集合がそれほどパフォーマンスに悪影響を与えることは無いはずです。

> SQLが見やすくなるだけでパフォーマンス的には変わらないオプティマイザーは、場合によってはビュー定義のクエリーと追加のクエリーを、効率の良い形式に変換してくれるみたいです。そうなるようにVIEWを定義する、というのはまた一概には言えない話になりそうです。 > UNION、MINUS、INTERSECTなどの集合演算子は集計を行うのでパフォーマンス的には避けたほうがよい集合させる必要が無いのに使うのは良くありませんが、集合させたければ使っても問題ないでしょう。集計でも集合でも、まずはできるだけ局所的にデータを絞りこむことです。たとえば10のテーブルをそれぞれ10件まで絞り込んだあとであれば、集計なり集合がそれほどパフォーマンスに悪影響を与えることは無いはずです。

キャンセル
2014/12/15 18:30

＞集合させる必要が無いのに使うのは良くありませんが、集合させたければ使っても問題ないでしょう。

実行計画を見るようにするのが一番ですね。見るクセをつけようとおもいます。

＞集合させる必要が無いのに使うのは良くありませんが、集合させたければ使っても問題ないでしょう。実行計画を見るようにするのが一番ですね。見るクセをつけようとおもいます。

キャンセル

+2

失礼します。
以前ある現場で、１時間待っても帰ってこなかったクエリが、結合でレコードの少ないテーブル（マスタなど）から先に参照させることで、２～３秒で返ってきました。

ご参考まで。

投稿 2014/12/07 19:50

回答の評価を上げる

以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
回答の評価を下げる

下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

2014/12/07 20:36

おっしゃるとおりで、とても正しいやり方だと思います。
左外部結合時に左側にマスタをもつのは正しい。
私が考えたのは数万行あるテーブル二つ以上を連結する場合の話で、マスターテーブルとトランザクションテーブルを結合するような場合はARADDIOさんのおっしゃっているようなやり方をすべきだと思います。

おっしゃるとおりで、とても正しいやり方だと思います。左外部結合時に左側にマスタをもつのは正しい。私が考えたのは数万行あるテーブル二つ以上を連結する場合の話で、マスターテーブルとトランザクションテーブルを結合するような場合はARADDIOさんのおっしゃっているようなやり方をすべきだと思います。

キャンセル
2014/12/09 16:49

回答ありがとうございます。

外部結合で左右のテーブルを入れ替えてしまうと、出力結果が変わってしまいますが、入れ替わっても問題ない場合の話でしょうか？

回答ありがとうございます。外部結合で左右のテーブルを入れ替えてしまうと、出力結果が変わってしまいますが、入れ替わっても問題ない場合の話でしょうか？

キャンセル
2014/12/09 18:04

＞ShinpeiYamamotoさん
どのテーブルのレコード数も多い場合ですね。
その場合は別途対応が必要になりますね。失礼いたしました。

＞lupus_dingoさん
問題ない場合（あんまりないと思いますが）入れ替えでいいと思います。
私がイメージしていたのはLEADING句を使うやり方でした。

＞ShinpeiYamamotoさんどのテーブルのレコード数も多い場合ですね。その場合は別途対応が必要になりますね。失礼いたしました。＞lupus_dingoさん問題ない場合（あんまりないと思いますが）入れ替えでいいと思います。私がイメージしていたのはLEADING句を使うやり方でした。

キャンセル
2014/12/15 20:35

leading句初めて聞きました。

ヒントの1つなんですね。
参考にさせていただきます。

leading句初めて聞きました。ヒントの1つなんですね。参考にさせていただきます。

キャンセル

+1

細かいクエリの話は他の方が言っておられるので省きます。

500万レコードのテーブル100個に跨る様なリクエストが発生する様な状況は、設計段階で排除すべきです。
それだけ複雑なテーブル構造で最適なSQLを作り、最適なチューニングを行うのは、もはや職人芸と言っても過言ではないです。
（理論上は、実行計画的、且つインフラ的に最適化されていれば性能は担保できると思いますが）

そういったいわゆるビッグデータ系のデータを扱う場合、RDBではそもそも限界があるので、
全てを基本プライマリキーのみで検索できるように設計した上で、NoSQL系DBを利用すべきでしょう。
もしくはカラムナー系のDBを利用するかですね。
もし客先要件等のめんどくさい理由からOracleしか使えないという事であれば、
MySQLをNoSQLDBとして利用するノウハウを流用できるかもしれません。

ちなみにですが、DBサーバを複数台に分散している様な場合には、物理的なサーバ間を跨ぐようなリクエストをすると、
どう頑張ってもパフォーマンスは一定以上に上がらないので、ご注意ください。

以上、ご参考になれば幸いです。

投稿 2014/12/05 14:16

回答の評価を上げる

以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
回答の評価を下げる

下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

2014/12/07 15:10

回答ありがとうございます。

100個はおおげさでした。すみません。
確かにもしそうなったら設計の段階で排除すべきことですね。
初めて聞いた単語がいくつかあるので勉強してみます。

回答ありがとうございます。 100個はおおげさでした。すみません。確かにもしそうなったら設計の段階で排除すべきことですね。初めて聞いた単語がいくつかあるので勉強してみます。

キャンセル

同じタグがついた質問を見る

score 0 · Accepted Answer · 2014-12-07T16:18

ベストアンサー

0

自分の経験上、JOINは遅いという認識です。
JOINでは指定したテーブルのすべてのレコードに対してON句に指定した条件で照合が行われます。WHERE句で指定された条件はJOINで作成された仮想表に対して集約を行うので、母数に変化はありません。

基本的には、数万行から数十万行のデータをもつテーブルを連結する場合、

①ON句に指定する結合条件にインデックスサーチが効くようにする。結合条件で指定する列にINDEXを張る、もしくは主キーを使う。複合主キーの場合は、主キーに使用される列すべてをそろえて指定しインデックスサーチがきくようにする。

②サブクエリを用いてあらかじめWHERE句で集約を行い、JOINされるテーブルのレコードの母数を減らす。

のような工夫をおこないます。

あとはそうですね、ロジックの組み方にもよりますが分けてとった方が早くなる、ということもよくあります。
数万回もクエリを投げると明らかに遅くなると思いますが、数回に分ける程度で済むのであれば、無理してJOINで結合して取得するより圧倒的に早いという印象です。
取得してからアプリケーションのロジックで結合を行う必要があったとしても、何かしらの条件で集約された結果に対しての照合になるので、照合・比較の回数は圧倒的に減ることになるからです。

実行計画などを確認して、結合でどのあたりがネックになるか確認しながら作業を進めることをお勧めします。

参考：表の結合を極めるチューニング

参考になれば幸いです。

投稿 2014/12/07 16:18

2014/12/07 21:27

回答の評価を上げる

以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
回答の評価を下げる

下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

2014/12/09 16:41

回答ありがとうございます。

＞JOINでは指定したテーブルのすべてのレコードに対してON句に指定した条件で照合が行われます。WHERE句で指定された条件はJOINで作成された仮想表に対して集約を行うので、母数に変化はありません。

結合したテーブルはon句に条件を書くことで母数を減らせるんですね。これからはなるべくwhere句ではなくon句に条件をandで書くようにします。

回答ありがとうございます。＞JOINでは指定したテーブルのすべてのレコードに対してON句に指定した条件で照合が行われます。WHERE句で指定された条件はJOINで作成された仮想表に対して集約を行うので、母数に変化はありません。結合したテーブルはon句に条件を書くことで母数を減らせるんですね。これからはなるべくwhere句ではなくon句に条件をandで書くようにします。

キャンセル
2014/12/13 17:03

申し訳ない。私の説明が悪かったと思います。ON句に条件を追加しても母数が減ることはありません。それは誤りです。
そうではなくて、JOINする対象のテーブルをサブクエリ等で仮想表とし、whereによる条件指定で結合対象のレコードを減らしておくのがよい、と申し上げたつもりでした。
①JOINを行う場合、母数となる集合にたいして集約を行った上でJOINした方が早い
②ON句に指定する条件をインデックスの張られた列にする。複合主キーの場合、すべての主キー列を指定する
ということです。

申し訳ない。私の説明が悪かったと思います。ON句に条件を追加しても母数が減ることはありません。それは誤りです。そうではなくて、JOINする対象のテーブルをサブクエリ等で仮想表とし、whereによる条件指定で結合対象のレコードを減らしておくのがよい、と申し上げたつもりでした。 ①JOINを行う場合、母数となる集合にたいして集約を行った上でJOINした方が早い ②ON句に指定する条件をインデックスの張られた列にする。複合主キーの場合、すべての主キー列を指定するということです。

キャンセル
2014/12/15 18:22

select * from a
inner join b
on a.id=b.id
よりも、
select * from a
inner join (
select id from b
where id in (111,222,333)
) b
on a.id=b.id

というように、joinでviewにした方がパフォーマンスがいいということですね。
知識不足ですみません。
勉強になります。

select * from a inner join b on a.id=b.id よりも、 select * from a inner join ( select id from b where id in (111,222,333) ) b on a.id=b.id というように、joinでviewにした方がパフォーマンスがいいということですね。知識不足ですみません。勉強になります。

キャンセル

質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう！

結合数の多いクエリのパフォーマンスについて教えて下さい。

関連した質問

同じタグがついた質問を見る

質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう！

結合数の多いクエリのパフォーマンスについて教えて下さい。

15分調べてもわからないことは、teratailで質問しよう！

関連した質問

同じタグがついた質問を見る

SNSアカウントでログイン

teratailアカウントでログイン