SQL) INNER JOINとサブクエリの処理速度の違いについて

Question

実現したいこと

inner joinとサブクエリについて整理しています。以下2点について教えていただけると幸いです。
・まとめた理解が正しいかどうか
・INNER JOIN とサブクエリのパフォーマンスの違い

発生している問題・分からないこと

JOINSとサブクエリの違いについて

やったこと

JOINSとサブクエリをサンプルテーブルをもとにイメージを掴みました

ユースケース

workspace_id=1を持つmessagesテーブルのレコードを取得する

JOINS使用

SELECT *
FROM messages
INNER JOIN channels
  ON messages.channel_id=channels.id
WHERE channels.workspace_id=1;

サブクエリ使用

SELECT *
FROM messages
WHERE EXISTS (
  SELECT 1
  FROM channels
  WHERE messages.channel_id=channels.id
  AND channels.workspace_id=1
);

結果

どちらも同じでした。（内容も含め）

31000 rows in set 

JOINSとサブクエリの出力の違い

最終的に出力されるカラムの量が違うとの認識です。

• JOINS　　：当然結合したテーブル（カラム）全て出力される
• サブクエリ：サブクエリ内で指定したテーブル（カラム）はあくまで「参照」、出力されない

サブクエリのイメージ

以下のクエリを実行した場合の処理の流れは以下の通り

SELECT *
FROM messages
WHERE EXISTS (
  SELECT 1
  FROM channels
  WHERE messages.channel_id=channels.id
  AND channels.workspace_id=1
);

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

INNER JOIN とサブクエリのパフォーマンスについて調べたところ以下の優先順位が良いとのことでした。

1. INNER JOIN （最も高速なケースが多い）
2. サブクエリでの事前絞り込み（大規模データの場合に有効）

3. LEFT JOIN + WHERE（通常は最も遅い）

ですが、結合するテーブルが多くなるとその分パフォーマンスが落ち、サブクエリのように余計なテーブルを結合しなくて済むのはパフォーマンス向上につながると思っています。
ケースにより違うとは思いますがパフォーマンスについてどう考えるべきか教えていただければと思います

参考記事
https://zenn.dev/btc/articles/241227_mysql_vs_inner_left_join_sub_query
https://qiita.com/yoshiokatsuneo@github/items/1b0c283c93604323c88d
https://www.okb-shelf.work/entry/subquery_left_join

補足

特になし

Comments

[utm.]

MySqlであれば実行するクエリーの前にEXPLAINを付ければ実行計画というのが見れて何となくSQLがどのようにクエリーを捉えるのかが分かります。

データの持ち方によるといえば元も子もないですが、結合するとパフォーマンスが下がるというのは必ずしも正しいとは言えないかと思います。
なぜなら、例えばイメージですが、channelというテーブルにIDという列があるとして、このIDは挿入された順序で1から番号を振るとします。その時、10個目のデータを見たければIDの列以外を見る必要がありませんし、単に線形探索をするとO(10)ですが、1～10までが綺麗に並んでいるだとか、その位置を特定する何らかの基準があれば10と言うだけでその位置まで飛べますので、O(1)と非常に高速になり得ます。これは結合をしようと全く同じことが言えます。
全ての例に当てはまるとは言い難いですけれど。

つまりそれがインデックスです。

SQLの速さは検索の効率が直結するかと思います。
セルのデータ量云々はパフォーマンスのコンテキストでは気にするレベルのものでは無いことがほとんどでしょうし、必要であればそれを取得しなければなりませんから、改善することも出来ないです。

質問のコンテキストや理解していることをより明確にすれば、より回答しやすいかも知れません。
例えばSelect *を使用しているため、joinすれば列数は変わります。

[utm.]

参考記事の方を拝見させていただきました。
注釈が無いように思うのですが同じクエリーでサブクエリを用いたものとINNER JOINを用いたものを比較するのは若干説明に混乱が見られるかと思います。
というか実際に質問者さんが混乱しているかと思います。質問に記載の内容からして

[tarousan]

EXPLAINを付与して実行してみようと思います。ありがとうございます！

今回は以下の目的もあってアウトプットが正しいのか見ていただきたいという意図がありました
・クエリのアプローチも色々あるため、INNER JOINとサブクエリでどのような違いがあるのか把握したい
・簡単なサンプルで理解を深め、応用ができるようにしたい

もしかしたらこの比較自体が意味のないものでしたら申し訳ありません！

Answer 1

「AよりBの手段の方が速いよ」という記事やコメントは大量に転がっていますが、一切信じないでください。

私がわりと明るいのはMySQLですが、
「この方法遅いよ」→「5.6で検索アルゴリズムのアップデート入って超高速になった」
こういうのばっかりで、古い記事を集めてもまじで無意味というか徒労に終わるだけです

特にMySQLの5系はマイナーアップデートでも相当の改良が入っており
5.6や8系の進化はとてもじゃないが追い切れないレベルです

一つだけ信じても良いものはExplainで払い出された実行計画だけ
それでアラート的な情報が含まれていなければ、とりあえず大丈夫と考えて良いです

MySQLのEXPLAINを徹底解説!! - 漢のコンピュータ道
https://nippondanji.blogspot.com/2009/03/mysqlexplain.html

少々古い記事ですが、この考えはまだまだ使えるかと思います。

Comments

[yambejp]

mysqlの場合オプティマイザが最適化を図るので単純なクエリだけでは推測しづらいですね

[miyabi-sun]

MySQLのオプティマイザは意味不明なレベルで賢いですよね
あの方法は非推奨です→いつの間にか内部でクエリ組み替えて高速に動作するようになったで！とか

[tarousan]

とても参考になります。いただいたリンクも勉強してみようと思います！

Answer 2

質問の意図がわかりかねますが、基本的にはリレーションするデータのユニーク性や、貼ってあるインデックス、null設定などによって最適なリレーション処理は変わってきます

Comments

[tarousan]

すいません、質問の意図としてはクエリの引き方の違いを少し深ぼって理解したいためでした。

確かにこの辺りは関係するところだと思います！
それぞれユニーク、インデックス、nullなどの一応の理解はできているものの、クエリのパフォーマンスにどう影響するのかなど理解を深めていきたいと思います。