【SQL】パフォーマンスを考えるに当たり必要な「結合」の知識(Oracle)

Oracleの実行計画を理解する上に当たって、理解しなければならないのが、アクセスパスについての知識でした。

【Oracle】実行計画作成後のSQL実行時の動作の仕組み（アクセスパス）

そして、もう一つ理解が必要なのが「結合」に関する知識です。

オプティマイザは、SQLに結合が合った場合は、下記3種類の結合のいずれかを選択して実行することになります。

• ネステッド・ループ結合
• ハッシュ結合
• ソート・マージ結合

テーブルの結合は、SQLのパフォーマンスに大きく影響する部分となります。結合する再に、不適切な結合方法が選択されるとパフォーマンスが大きく劣化してしまいますので、結合についてはしっかり理解するようにしましょう。

一番重要なのは、どのDBMSでも使われる「ネステッド・ループ結合」、次に「ハッシュ結合」、最後に「ソート・マージ結合」（他のアルゴリズムに比べて重要度が下がる）の順番になります。

オープンソース事情(MySQL、PostgreSQL)

なお、オープンソースRDBMSの代表格の「MySQL」では「マージ結合」と「ハッシュ結合」はサポート対象外になります。（PostgreSQLはどれも対応している。）

なので、PostgreSQLはMySQLに比べて大きな二つの表同士のJOINが得意です。

ネステッド・ループ結合

特徴

画面等のオンライン処理（あまりデータ件数が多くない処理）でよく使われます。結合元のテーブルをまず検索してその検索結果数分だけ、結合先のテーブルを検索します。なので処理時間は「結合元テーブルの検索時間」×「結合先テーブルのアクセス数×表示件数」になります。

用途

where句で条件が絞り込まれた後の、テーブルの一部分を結合する際に有効な結合方法。特に、結合対象テーブルの結合条件列に索引が作成されている場合は、データに効率的にアクセスすることが可能。

仕組み

• ハッシュやソートマージに比べて1つのステップでのメモリ消費量は少ない。

ポイント

「結合元テーブル（駆動表）」のレコード数が少なかったり、「結合先テーブル（内部表）」のインデックスが有効になっている場合は高速に動作します。また、結合元テーブル、結合先テーブルをどちらにするかによって、アクセスするデータ量が大きく異なってくるので、結合順序が非常に重要になります。

「結合元テーブル」と「結合先テーブル」の件数に乖離がある場合

「結合元テーブル」のレコード数が少ない方が効率的に結合ができる。

「結合元テーブル」と「結合先テーブル」の件数に大差がない場合

結合条件の列（ON句）が、索引がある方を「結合先テーブル」にする。

ハッシュ結合

結合キーの値をハッシュ値にマッピングする。これもハッシュ表を確保するためのメモリ領域を必要とします。ユースケースとしてはバッチ処理等の大量データ処理で使います。

仕組み

1. まずは小さいテーブルをスキャンして、結合キーに対してハッシュ関数を適用することでハッシュ値に変換する。
2. 次に、もう一方の大きなテーブルをスキャンして、結合キーがそのハッシュ値に存在するかどうか調べる。

小さい方のテーブルからハッシュテーブルを作る理由

ハッシュテーブルはDBMSのワーキングメモリに保持されるためなるべく小さい方が効率が良いため。

特徴

• 出力となるハッシュ値は入力値の順序性を保存しないため、等値結合でしか使用できない。
• 大量レコードの結合や、テーブルの大部分の結合をする場合に有効
• ハッシュ表への検索時間は無視できるほど高速なのでネステッドループ結合の「結合元テーブル」の件数を十分に絞り込めていない場合は有効です。

注意点

ネステッドループに比べて以下の点が注意です。

• 結合テーブルからハッシュテーブルを作成するため、ネステッドループに比べるとメモリ消費量が多い。
• ワーキングメモリ内にハッシュ表が入りきらない場合に、ストレージを使用することになり途端に遅くなる。

なので、同時実行性の高いオンライン処理で使ってしまうとメモリが足らなくなり急に遅くなる可能性があります。基本的にはバッチ処理で使うようにしましょう。

処理時間

「結合元テーブルのアクセス時間」＋「ハッシュ表の作成時間」＋「結合先テーブルのアクセス時間」＋「大規模表のアクセス時間」

ソート・マージ結合

特徴

バッチ処理等の大量データ処理でかつ結合条件でソートした出力を行いたい場合に使います。結合元、結合先、双方のテーブルの索引があった場合に有効に動作します。「結合元テーブル」と「結合先テーブル」でソートした結果を付き合わせて検索します。

こちらもメモリ消費量が大きいという理由で、オンライン処理には向かないです。

仕組み

結合キーでレコードをソートして、順次アクセスを行って2つのテーブルを結合する。結合の前処理としてソートを行うので、そのためのメモリ領域を必要とします。（必要になるのがワーキングメモリ）、少なくともネステッドループよりメモリを多く消費します。

【データベース】メモリの種類（「データキャッシュ」と「ログバッファ」など）

ハッシュ結合との違い

大体はハッシュ結合の方が高速に動作しますが既に最終的な出力結果とソート条件が同じだったりする場合に有効です。

処理時間

「結合元テーブルのアクセス時間」＋「結合先テーブルのアクセス時間」＋「ソート時間」