負荷の少ないSQL文を書きたいのですが…

Question

SQLの書き方がおかしくて
負荷が掛かる書き方になっていないか教えて欲しいです

前提

MySQL

団体テーブル
※団体に関する情報を扱うテーブル

作品テーブル
※団体が発表している作品に関する情報を扱うテーブル

抽出条件や欲しい結果

団体テーブル、作品テーブル両方に各種条件を指定します。
最終的に上記条件に該当する団体の各種項目（一部）一覧を出したいです

自分で考えたSQL

SELECT 出したい項目１, 出したい項目２,・・・
FROM 団体テーブル
WHERE 団体ID IN (SELECT 団体ID
FROM 作品テーブル
WHERE ここに作品に関する色んな条件を記載
GROUP BY 団体ID)
AND ここに団体に関する色んな条件を記載
ORDER BY 好きな並び順を記載

INで書く書き方が力技じゃないかと不安です…

Comments

[退会済みユーザー]

この質問内容だと情報量が少なすぎて回答が付かないと思います。
テーブルの構造や実行計画を貼ると回答が付くかもしれません。

[testnm]

なるほど、実はまだ構想段階でまだ作成していません。
ちゃんと作って情報揃ってから再度質問を検討してみます。
丁寧にご指摘ありがとうございました。

Answer 1

表示を目的とする場合に、お薦めしたいSQLの組み立て方は、
・条件は取得したい集合の駆動表のプライマリーキーだけにする
です。

SQLでの抽出の際、プライマリーキーを条件にするのが最速です。
また、条件をプライマリーキーのみとする事により、取得したい集合と条件の集合に分離されるので、チューニングも施しやすくなります。
勿論、取得する集合と条件の集合を分離するので、冗長さを感じる記述にはなりますが、安定した抽出速度を得る事が出来ます。

これは、苦労してチューニングを行った後の変更で、また苦労のやり直しになる事を改善する目的で行き着いた、最速ではなく安定を目的としたものです。
とはいえ、これが最速になる場合もあります。
取得と条件を分離する事によって、取得や条件一方のみでのテーブル参照なども可能で、変更があっても極端な性能劣化となりにくいのです。

質問の内容で考えると以下の様になります。
※団体IDが団体のプライマリーだと想定。

SELECT 出したい項目１, 出したい項目２,・・・
FROM 団体
WHERE 団体ID IN ( -- 条件用SQL
        SELECT distinct t1.団体ID
        FROM 団体 t1 left join 作品 t2
             on  t1.団体ID=t2.団体ID
        WHERE ここに作品に関する色んな条件を記載
          AND ここに団体に関する色んな条件を記載
      )
ORDER BY 好きな並び順を記載

この例での主なチューニングは条件部分のSQLになります。
状況によって相関によるExistsを使用するなどもありますが、条件として分離できていますから、
駆動表が団体であるので、サブクエリーではなく、join してwhere条件とする方が良いかと思います。

Comments

[angel_p_57]

それは結局 in 条件の中で、作品テに対する全検索が走ることになるので、あんまり元のと変わらない ( 元々インデクスが使える形なら悪化もありうる ) SQLになるのでは。
in じゃなくて exists の方が勝るように思います。( RDBMSがin条件をexists相当に解釈してくれるなら同じですが )

[sazi]

性能的に分離しない方が勝る場合も当然あります。
目的は安定した性能を提供する事なので。
> in じゃなくて exists の方が勝る
相関としてexistsを使用する場合で、existsが勝るのは、主表側の件数が少ない時です。
プライマリーだけを条件にするという目的ではinの方が勝ります。

[sazi]

また、インデックスのチューニングに於いては、取得と条件でインデックス項目が異なる場合がありますが、分離する事で別なインデックスとして定義する事ができるようになります。

[angel_p_57]

> existsが勝るのは、主表側の件数が少ない時
名前的に「1つの団体が複数の作品を作っていて、団体テーブルの団体idは作品テーブルの団体idの外部キー」なのは前提にして良いでしょう…。

[sazi]

先ず、angel_p_57さんの回答にある
> 団体IDが先に絞れれば作品テーブルの検索範囲も小さくなること
ですが、相関では範囲ではなく回数です
> 団体IDが先に絞れれば作品テーブルの検索回数も少なくなること
それから
> existsが勝るのは、主表側の件数が少ない時
については、「プライマリーだけを条件にするという目的ではinの方が勝ります。」というように絞り込みの条件が無い前提です。
私の回答の趣旨は最速ではなく、あくまで安定を目的としています。

existsの議論は私の回答の条件部分について使用する事に関しての異論はありません。

Answer 2

MySQLでの癖があるかも知れませんが、一般的な話として回答します。
それから前提として、検索条件がどのようにインデクスを使えるかによって状況が全然違いますので、この場で「これが最善」というのは言えません。候補の中から実際に試して比較してもらうしかないと思います。

で、まず

抽出条件や欲しい結果

これははっきり言うと、「条件を満たす作品を『1つでも』作った団体」を抽出するのが主目的ですよね。なので、書き方としては質問にある in 条件と、もう1つ exists 条件を使う2通りが考えられます。
元のSQLの group by は多分不要 ( in条件の中なので、RDBMSが自動的に distinct 相当にしてくれるはず ) ですが、あってもそんな影響はない気がしていて、これはこれで前者の書き方として妥当かと思います。

もう1つの exists 条件を使う場合、次のようになります。

select 【項目】 from 団体 as td
where
 【団体に関する条件】
  and exists (
    select * from 作品 as ts
    where
      ts.団体id = td.団体id
      and 【作品に関する条件】
  )
order by 【オーダーの指定】

で、2つのSQLの比較ですが傾向としては次のように言えると思います。

• in条件で書く場合、素直に考えると作品の全検索が走る。作品の抽出条件でインデクスがうまく使えないならこれがボトルネックになりそう。逆に、絞り込みがうまく行くようなインデクスが用意してあるケースならこちらの方がよさそう
• exists条件で書く場合は、作品テーブルで団体idを使ったインデクスがあることが絶対。( この件がなくても、そういうインデクスは作ってないとマズいと思う )
  団体テーブルを全部見ることになるけど、作品テーブルよりは規模は小さいはずで、かつ団体IDが先に絞れれば作品テーブルの検索範囲も小さくなること、団体ごとに1つ作品が見つかった時点で検索を打ち切れることから、おそらく一般的にはこちらの方が有利

ただ、どちらにしてもテーブル・インデクスの実態によって変わるので、あくまで一般論ということで念のためご注意ください。

Comments

[testnm]

おお！こういうのが知りたかったです。
ありがとうございます。後はインデクス作って色々試してみます。

[sazi]

> こういうのが知りたかったです。

理解されずに残念です。

[testnm]

理解力が低くてすみません。
あと一回目の回答しかないと思って後半は見ていませんでした。

＞作品の抽出条件でインデクスがうまく使えないならこれがボトルネックになりそう。
作品名称を部分検索される可能性が一番高いので
インデクスが上手く機能する気があまりしないため
説明を聞いてINはやめようと思っています。（浅い知識ですが、部分検索で高速になるイメージがあまりない）

一旦JOINで実装して、結合するキー・よく使われる可能性が高い項目にインデスクを張って調整してみる予定です。