IN 演算子の高速化

IN演算子の高速化について紹介します。

INをEXISTSに変更する

IN演算子をEXISTSに変更することで高速化できるケースがあります。

IN演算子の後にサブクエリがある場合、SQLはサブクエリから実行されます。 一方 EXISTS演算子は後にサブクエリがある場合でも、メインクエリから実行されます。

例

下記のテーブルを例にします。

products

id	model	name	category	price
1	C-XM01	モーダンチェア	チェア	56000
2	X-XD05	ラージデスク	テーブル	87000
3	A-DA40	ラウンドダイニングチェア	チェア	28000
4	O-XX100	ナチュラルオフィス	チェア	13800
5	R-D400	ラウンドダイニングテーブル	テーブル	128000
6	R7000	ウッドキャビネット	その他	32000
7	B-200	リネンベッド	ベッド	184500
8	B-250	ホワイトダブルベッド	ベッド	324850
9	W-80	ワーキングチェア	チェア	45000
10	EG-10X	エルゴノミクスデスク	テーブル	88500

sale

id	category
1	チェア
2	ベッド

select * from products where category in (select category from sale)

上記のSQLであれば、IN以降の副問い合わせは入力として処理されるため、下記のコードと同様になります。

foreach(_record in execsql("select category from sale"))
 match = execsql("select * from products where category = _record");
 if (match==true){
   (結果セットに挿入)
 }
}

select * from products where exists (select id from sale where sale.category = products.category)

一方、上記のSQLの場合は、相関問い合わせ(相関クエリ/相関サブクエリ)になり、メインのクエリの結果に対してサブクエリでの評価をするため、下記のコードと同様になります。

foreach(_record in execsql("select * from products")){
 match = execsql("select id from sale where _record.category = sale.category");
 if (match==true){
   (結果セットに挿入)
 }
}

IN演算子を用いた場合、サブクエリの結果は展開されメモリ等の一時テーブルに記録されます。 そのため、サブクエリの結果サイズが大きい場合はパフォーマンスの低下を起こす可能性があります。
今回の例ではproductsテーブルのレコードサイズが増えると速度が低下する見込みです。

速度比較

以下のSQLの実行時間を比較します。

SQL : IN演算子利用

select * from (大きなテーブル) as T
where (フィールド名) not in (select (パターン列名) from (パターンのテーブル))

SQL : EXISTS演算子利用

select * from (大きなテーブル) as T
where not exists (select id from (パターンのテーブル) where T.(フィールド名) = (パターン列名) )

(パターンのテーブルは約20レコード、大きなテーブルは270万レコード)

結果

	IN演算子	EXISTS演算子
3回の実行速度平均	1:15	1:35

今回の例ではパターンのテーブルのサイズが小さかったためか、INよりEXISTSのほうが若干遅い結果となりました。 一般的にEXISTSのほうが高速とされていますが、昨今のデータベースエンジンでは、 クエリの最適化や実行計画が良くなっているためか、あまり速度差が出ないのかもしれません。