SE の雑記

クエリ チューニングの高度な概念 に記載されていますが、結合演算子の違いについて、シンプルなクエリを使って、軽くまとめてみたいと思います。

■ベースとなるテーブルと初期データ

今回使用するクエリのベースとなるテーブルについては、以下のものを使用します。

SET NOCOUNT ON
GO
DROP TABLE IF EXISTS T1
DROP TABLE IF EXISTS T2
GO
CREATE TABLE T1 (C1 int, C2 uniqueidentifier)
CREATE TABLE T2 (C1 int, C2 uniqueidentifier)
GO
SET NOCOUNT ON
GO
DECLARE @cnt int = 1
BEGIN TRAN
WHILE(@cnt <= 10000)
BEGIN
	INSERT INTO T1 VALUES(@cnt, NEWID())
	INSERT INTO T2 VALUES(@cnt, NEWID())
	SET @cnt += 1
END
COMMIT TRAN
GO

今回はシンプルなクエリで実行しますので、簡単なテーブル構造としています。
データの内容として、T1.C1 と T2.C1 は 1:1 なのですが、テーブルの定義としてはなにも設定していない状態をスタートとしています。
 

それでは、各結合の方式を確認してみたいと思います。
今回は明示的に結合にヒントを設定することで特定の操作を行うようにしています。
各結合のクエリを実行する前には、テーブルのインデックスの状態は未設定の状態としています。

■ループ結合

ループ結合でデータを取得するために、以下のクエリを実行します。

SET STATISTICS IO ON
GO
SELECT
	T1.C1,
	T2.C2
FROM
	T1
	LEFT LOOP JOIN T2
	ON T1.C1 = T2.C1
GO

 
この時の実行プランが以下になります。

結合については、Nested Loop となっていることが確認できますね。
Nested Loops プラン表示操作
このプランで確認するポイントは、以下の個所となります。

今回は、T1 / T2 ともに、10,000 件のデータを格納しています。
ループ結合の場合は、ベースとなるテーブルを元に、参照テーブルで合致する行があるかを各行に対して確認する必要があります。
今回は、T1 がベースとなるテーブルとなり、T2 が参照テーブルとなっていますので、T2 から、100,000,000 (T1 : 10,000 × T2 : 10,000)? 件の行を確認していることが、実行プランから確認できますね。
T2 のデータに関しては、一度ワークテーブルに格納され、そこから参照が行われていますので、STATISTICS IO の情報は以下のような形で出力されています。

Warning: The join order has been enforced because a local join hint is used.
Table 'T2'. Scan count 4, logical reads 152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 4, logical reads 550712, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 5, logical reads 38, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 
ループ結合については、ベーステーブルの各行に対して、参照テーブルから合致する行があるかを走査する必要があるため、データ件数によって、どの程度の情報が流れているのかを実行プランから把握することは重要となります。
今回のケースであれば、インデックスを設定し、行の特定がシンプルになることで T2 から確認しなくてはいけない走査数については減らすことができます。

CREATE NONCLUSTERED INDEX NCIX_T2_C1 ON T2 (C1) INCLUDE(C2)
GO

 

「Table Spool」の操作がなくなっていますので、ワークテーブルの作成がなくなっていることも確認できますね。

Warning: The join order has been enforced because a local join hint is used.
Table 'T2'. Scan count 10000, logical reads 21352, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 1, logical reads 38, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

■マージ結合

マージ結合でデータを取得するために、以下のクエリを実行します。
# 自分の環境では、後述の制約の設定後に並列クエリではなくなったため、設定前後でプランの条件をそろえるために MAXDOP 1 を設定しています。

SET STATISTICS IO ON
GO
SELECT
	T1.C1,
	T2.C2
FROM
	T1
	LEFT MERGE JOIN T2
	ON T1.C1 = T2.C1
OPTION (MAXDOP 1)
GO

 
この時の実行プランが以下になります。

マージ結合が使われていることが確認できますね。
Merge Join プラン表示操作
マージ結合は「ソートマージ結合」とも呼ばれ、両テーブルが結合列を基準にソートされている状態で、比較を行っています。
そのため、現状はインデックスを設定していませんので両テーブルでソートが発生していることが確認できますね。
 
それでは T1 / T2 にインデックスを設定してみます。

CREATE NONCLUSTERED INDEX NCIX_T1_C1 ON T1 (C1)
GO
CREATE NONCLUSTERED INDEX NCIX_T2_C1 ON T2 (C1) INCLUDE(C2)
GO

インデックスを設定したことで、ソートがなくなり、シンプルな実行プランになりましたね。

マージ結合については、確認のポイントはもう一つあります。
それが、「Merge ManyToMany」(多対多) の操作となります。
これについては、以下の記載がわかりやすいかと。
マージ結合について

Merge Join 操作は、標準の操作または多対多操作のいずれかになります。多対多のマージ結合では、行を格納するために一時テーブルが使用されます。各入力に重複する値がある場合、一方の入力の各重複値が処理されるとき、他方の入力は重複値の先頭に戻る必要があります

Merge Join プラン表示操作

Merge Join 操作の Argument 列には、この操作によって一対多の結合が実行されている場合は MERGE:() 述語が、多対多の結合が実行されている場合は MANY-TO-MANY MERGE:() 述語が設定されます。また、Argument 列には、操作の実行に使用される列のコンマ区切りリストも設定されます。Merge Join 操作には、それぞれの列を基準に並べ替えられた 2 つの入力が必要です。この並べ替えを行うときは、クエリ プランに明示的な並べ替え操作を挿入することが可能です。明示的な並べ替えが必要でない場合、Merge Join 操作は特に効果的です。たとえば、データベースに適切な B ツリー インデックスがある場合、またはマージ結合とロールアップを含むグループ化など、複数の操作で並べ替え順序を利用できる場合などです。

今回、格納されているデータとしては、1 対 1 となっているのですが、現状のマージ結合は多対多の操作が行われています。

多対多の操作を行うための一時テーブルの作成については STATISTICS IO の情報から確認することができます。
# パフォーマンスモニターの「SQLServer:Access Methods\Worktables Created/sec」で確認することもできます。

Warning: The join order has been enforced because a local join hint is used.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T2'. Scan count 1, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 1, logical reads 25, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 
データとして 1 対 1 となるように格納されていても、SQL Server はそのようなデータとなっているという判断をすることができません。
それでは、以下のクエリを実行して制約を付けてみたいと思います。

ALTER TABLE T1 ADD CONSTRAINT AK_T1_C1 UNIQUE (C1)
GO

これで、結合列に使用している T1 の C1 はユニークになっているということが SQL Server が判断できるようになりました。
設定後の実行プランがこちらです。

パッと見は変わっていないように見えますが、「Merge Join」のコストが変わっている (98% → 39%) ことが確認できますね。
一意制約を設定したことで、「多対多」が「False」になりました。

これにより、STATISTICS IO の結果から Worktable の情報がなくなっていることが確認できますね。

Warning: The join order has been enforced because a local join hint is used.
Table 'T2'. Scan count 1, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 1, logical reads 25, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Worktable については tempdb の負荷となるケースがありますので、多対多の状態がどのようになっているかを意識することも、マージ結合を使用する場合のポイントとなります。

■ハッシュ結合

ハッシュ結合でデータを取得するために、以下のクエリを実行します。

SET STATISTICS IO ON
GO
SELECT
	T1.C1,
	T2.C2
FROM
	T1
	LEFT HASH JOIN T2
	ON T1.C1 = T2.C1
GO

 
この時の実行プランが以下になります。

ハッシュ結合が行われていることが確認できますね。
Hash Match プラン表示操作
ハッシュについては、ハッシュテーブルを作成するための Worktable や、Workfile の作成が行われていることが、STATISTICS IO から確認できます。

Warning: The join order has been enforced because a local join hint is used.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T2'. Scan count 1, logical reads 38, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T1'. Scan count 1, logical reads 38, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

こちらもインメモリで実行されない場合は、tempdb の負荷が発生しますので、警告が発生していないかは意識する必要があります。