外部結合とサブクエリのどっちが早いか試してみた

おとついの不具合の対応案として挙げた「外部結合を使う案」と「サブクエリを使う案」でどっちが早いか試してみた。

環境/条件等

DBはMySQLではなく実運用で使うDB2を使用。
テーブルは以下。おとついのサンプルからカラムを少し増やしてます。

create table A ( 
  id BIGINT NOT NULL GENERATED AS IDENTITY, 
  a VARCHAR(20) NOT NULL DEFAULT '', 
  b VARCHAR(20) NOT NULL DEFAULT '', 
  c VARCHAR(20) NOT NULL DEFAULT '', 
  PRIMARY KEY(id)
) IN test_32k

create table B ( 
  id BIGINT NOT NULL GENERATED AS IDENTITY, 
  a_id BIGINT NOT NULL,
  a VARCHAR(20) NOT NULL DEFAULT '', 
  b VARCHAR(20) NOT NULL DEFAULT '', 
  c VARCHAR(20) NOT NULL DEFAULT '', 
  FOREIGN KEY (a_id) REFERENCES A(id) ON DELETE CASCADE,
  PRIMARY KEY(id)
) IN test_32k

テーブルにはそれぞれ1万行のデータを追加。
- 各Aのうち1000レコードは、それぞれ10件づつBと関連づけられている。
- 残りの9000レコードは関連するBを持たない。

SQL

計測で使用するSQLは以下の通り。

検索条件が1つの場合と複数の場合で計測。
検索条件が1つの場合100件、複数の場合200件ヒットする

外部結合を使う案

条件が1つの場合:

select 
  A.id, A.a, A.b, A.c  
from A left join B on A.id = B.a_id  
where B.c = '1';

複数の条件を持つ場合:

select 
  A.id, A.a, A.b, A.c  
from A left join B on A.id = B.a_id  
where A.b = '100' or  B.c = '1' or B.c = '2';

サブクエリを使う案

条件が1つの場合:

select 
  A.id, A.a, A.b, A.c  
from A 
where exists ( select B.id from B where B.a_id = A.id and B.c = '1');

複数の条件を持つ場合:

select 
  A.id, A.a, A.b, A.c  
from A 
where A.b = '100' or exists ( select B.id from B where B.a_id = A.id and ( B.c = '1' or B.c = '2' ) );

結果

	検索条件が1つ	検索条件が複数
外部結合	0.026494 seconds	0.102160 seconds
サブクエリ	0.025233 seconds	9.318968 seconds

検索条件が1つの時はあまり変わらないけど、複数の場合はだいぶ違うなー。サブクエリで複数条件の場合、Aの属性での絞り込み条件とサブクエリの条件をORで結合しているため、最適化できてないっぽい。親の属性を条件としない場合やANDで結合するとだいぶ違う。

	処理時間
親の属性を条件としない	0.047816 seconds
ANDで結合する	0.046380 seconds

親の属性を条件としない(200件ヒット) :

select 
  A.id, A.a, A.b, A.c 
from A 
where exists ( select B.id from B where B.a_id = A.id and ( B.c = '1' or B.c = '2' ));

ANDで結合する(2件ヒット) :

select
  A.id, A.a, A.b, A.c 
from A 
where A.b = '0' and exists ( select B.id from B where B.a_id = A.id and ( B.c = '1' or B.c = '2' ));

なんにしても、Aの属性に対する条件とBの属性に対する条件をORで結合して検索できることは必須なので、サブクエリ案はないな。