MySQL ステートメントにおける IN と Exists の比較分析

背景

最近、SQL 文を書くときに、IN と Exists のどちらを選択するか迷ったので、両方の方法の SQL を書き出して実行効率を比較してみました。IN のクエリ効率は Exists よりもはるかに高いことがわかったので、IN の効率は Exists よりも優れていると自然に思いました。しかし、物事の真相を突き止めるという原則に沿って、この結論がすべてのシナリオに当てはまるかどうか、またなぜこのような結果になるのかを知りたいです。
ネットで関連情報を調べたところ、大まかにまとめると、外部テーブルが小さく内部テーブルが大きい場合は Exists が適用でき、外部テーブルが大きく内部テーブルが小さい場合は IN が適用できるということです。すると、SQL ステートメントで外部テーブルに 10,000 件のデータしかなく、内部テーブルに 300,000 件のデータがあるため、混乱してしまいます。インターネットによると、Exists の効率は IN よりも高いはずですが、私の結果は正反対です。！
「調査がなければ、発言する権利はない」！そこで、IN と Exists の実際の実行プロセスを研究し、実践的な観点から根本的な原因を見つけようとしたところ、このブログ投稿に至りました。

実験データ

私の実験データには、t_author テーブルと t_poetry テーブルの 2 つのテーブルが含まれています。
対応するテーブル内のデータの量:

t_author テーブル、13355 件のレコード;
t_poetry テーブル、289,917 件のレコード。

対応するテーブル構造は次のとおりです。

テーブルt_poetryを作成します (
id bigint(20) NOT NULL AUTO_INCREMENT、
poetry_id bigint(20) NOT NULL COMMENT '詩のID',
poetry_name varchar(200) NOT NULL COMMENT '詩の名前',
<font color=red> author_id bigint(20) NOT NULL COMMENT '著者ID'</font>
主キー ( id )、
ユニークキーpid_idx ( poetry_id ) BTREE 使用、
キーaid_idx ( author_id ) BTREE の使用
) エンジン=InnoDB AUTO_INCREMENT=291270 デフォルト文字セット=utf8mb4

テーブルt_authorを作成します (
id int(15) NOT NULL AUTO_INCREMENT,
author_id bigint(20) NOT NULL,</font>
author_name varchar(32) NOT NULL,
dynasty varchar(16) NOT NULL,
poetry_num int(8) NOT NULL デフォルト '0'
主キー ( id )、
BTREE を使用した一意のキーauthorid_idx ( author_id )
) エンジン=InnoDB AUTO_INCREMENT=13339 デフォルト文字セット=utf8mb4

実行プロセスにおける実行計画分析

SQL の例: select * from tabA where tabA.x in (select x from tabB where y>0 );

実行計画:
（１）tabBテーブルのサブクエリを実行して結果セットBを取得し、tabBテーブルのインデックスyを使用する。
（2）tabAテーブルに対してクエリを実行します。クエリ条件は、tabA.xが結果セットBにあることです。tabAテーブルのインデックスxを使用できます。

実行プロセスが存在する

SQL の例: select from tabA where exists (select from tabB where y>0);

実行計画:

（1）まずtabAテーブルからすべてのレコードを取得します。
(2) テーブル tabA の各レコードについて、テーブル tabB を行ごとに関連付けて、テーブル tabB のサブクエリがデータを返すかどうかを判断します。バージョン 5.5 以降では、ブロックネストループを使用します。
（３）サブクエリがデータを返す場合、tabAの現在のレコードが結果セットに返されます。
tabA はテーブルデータ全体を走査することと同等であり、tabB はインデックスを使用できます。

実験手順

この実験では、同じ結果セットの IN および Exists SQL ステートメントを分析します。
IN を含む SQL ステートメント:

t_author taからauthor_idを選択します
(t_poetry tp から author_id を選択し、tp.poetry_id>3650 となる)。

Exists を含む SQL ステートメント:

存在するt_author taから選択
(t_poetry tp から * を選択、ただし tp.poetry_id>3650 かつ tp.author_id=ta.author_id);

最初の実験のデータ

t_author テーブル、13355 件のレコード。t_poetry テーブル、poetry_id>293650 のサブクエリフィルター結果セット、121 件のレコード。

実行結果

exists を使用すると 0.94 秒かかり、in を使用すると 0.03 秒かかります。INは Exists よりも効率的です。

原因分析

t_poetry テーブルのサブクエリの結果セットは非常に小さく、どちらも t_poetry テーブルのインデックスを使用でき、t_poetry サブクエリの消費量は基本的に同じです。 2 つの違いは、in を使用する場合、t_author テーブルはインデックスを使用できることです。

exists を使用すると、t_author テーブルが完全にスキャンされます。

サブクエリの結果セットが小さい場合、クエリ時間は主に t_author テーブルのトラバーサルに反映されます。

2番目の実験データ

t_author テーブル、13,355 件のレコード。t_poetry テーブル、poetry_id>3650 のサブクエリフィルター結果セット、287,838 件のレコード。

実行時間

exists を使用すると 0.12 秒かかり、 in を使用すると 0.48 秒かかります。 Exists は IN よりも効率的です。

原因分析

2 つの実験のインデックスの使用法は、最初の実験と一致しています。唯一の違いは、サブクエリフィルタリング結果セットのサイズです。ただし、実験結果は最初のものとは異なります。この場合、サブクエリの結果セットは非常に大きくなります。MySQL クエリプランを見てみましょう。
in を使用する場合、サブクエリの結果セットが非常に大きいため、t_author テーブルと t_poetry テーブルの両方がフルテーブルスキャンに近くなります。このとき、t_author テーブルをトラバースする時間消費の差は、全体的な効率では無視できます。実行プランには、<auto_key> という行がもう 1 行あります。フルテーブルスキャンに近い場合、MySQL オプティマイザは auto_key を使用して t_author テーブルをトラバースします。

exists を使用する場合、データ量の変化によって実行プランは変わりません。ただし、サブクエリの結果セットが大きいため、MySQL バージョン 5.5 以降では、exists のクエリ結果をマッチングするときに Block Nested-Loop (ブロックネストループ、結合バッファを導入、キャッシュ機能に類似) が使用されます。これは、クエリの効率に大きな影響を与え始め、特にサブクエリの結果セットが大きい場合は、クエリのマッチング効率を大幅に向上させることができます。

実験的結論

上記の 2 つの実験とその結果に基づいて、IN と Exists の実行プロセスを明確に理解し、IN と Exists の適用可能なシナリオをまとめることができます。

IN クエリは内部テーブルと外部テーブルの両方でインデックスを使用できますが、Exists クエリは内部テーブルでのみインデックスを使用できます。サブクエリの結果セットが大きく、外部テーブルが小さい場合、Exists のブロックネストループが役割を果たし始め、外部テーブルでインデックスを使用できないという欠点を補い、クエリの効率は IN よりも高くなります。サブクエリの結果セットが小さく、外部テーブルが大きい場合、Exists のブロックネストループの最適化効果は明らかではなく、IN の外部テーブルインデックスの利点が大きな役割を果たします。この場合、IN のクエリ効率は Exists よりも優れています。オンラインで言われていることは不正確です。実際、「テーブルのサイズ」は内部テーブルと外部テーブルではなく、外部テーブルとサブクエリの結果セットを指します。最後のポイントは最も重要な点でもあります。世界には絶対的な真実はありません。物事の本質を把握し、さまざまなシナリオで実践的な検証を行うことが、最も信頼性が高く効果的な方法です。実験中に発見された問題に関する補足情報

異なるデータセットで上記の存在するステートメントを分析すると、データセットが大きいほど、消費時間が短くなることが分かりました。これは非常に奇妙なことです。
具体的なクエリ条件は次のとおりです。