Mysql マルチテーブル結合クエリの実行の詳細について簡単に説明します。

まず、このブログのケースデモンストレーション テーブルを作成します。

create table a(a1 int primary key, a2 int ,index(a2)); -- 両方のフィールドにインデックスがありますcreate table c(c1 int primary key, c2 int ,index(c2), c3 int); -- 両方のフィールドにインデックスがありますcreate table b(b1 int primary key, b2 int); -- 主キー インデックスcreate table d(d1 int, d2 int); -- インデックスなしinsert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
b 値に (1,1)、(2,2)、(3,3)、(4,4)、(5,5)、(6,6)、(7,7)、(8,8)、(9,9)、(10,10) を挿入します。
c に値 (1,1,1)、(2,4,4)、(3,6,6)、(4,5,5)、(5,3,3)、(6,3,3)、(7,2,2)、(8,8,8)、(9,5,5)、(10,3,3) を挿入します。  
d に値 (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10) を挿入します。

ドライバーテーブルの選択方法は？

駆動テーブルの概念は、複数のテーブルがクエリされたときに最初に処理されるテーブルを指します。このテーブルのレコードは、他のテーブルを関連付けるために使用されます。駆動テーブルの決定は、複数のテーブル接続の関連付け順序に直接影響し、後続の関連付け時のクエリ パフォーマンスも決定するため、非常に重要です。

駆動テーブルの選択は在對最終結果集沒影響的前提下，優先選擇結果集最小的那張表作為驅動表原則に従います。ドライバー テーブルを変更すると、接続順序が変更されます。ドライバー テーブルは、最終的な出力結果が変更されない場合にのみ最適化できます。外部結合の場合、駆動テーブルを変更すると、出力結果に影響することがよくあります。たとえば、左結合の左テーブルと右結合の右テーブルです。駆動テーブルが結合の左側または右側を選択した場合、最終的な出力結果は異なる可能性があります。

結果セットを使用して駆動テーブルを選択します。結果セットとは何ですか?結果セットを計算する方法は?選択する前に、MySQL は where 内の各テーブルのスクリーニング条件に従って駆動テーブルとして使用できる各テーブルの結果レコードの推定を行い、各テーブルによって返される行数を推定し、それを select で照会されたフィールドの合計バイト サイズで乗算します。

每行查詢字節數* 預估的行數= 預估結果集

次の規則に従って、where を使用して結果行の数を推定します。

• where 内の対応するテーブルにフィルター条件がない場合、on 内に関連する条件があるかどうかに関係なく、デフォルトはテーブル全体になります。
• where にフィルター条件があるが、インデックスをフィルターに使用できない場合、デフォルトはテーブル全体になります。
• where にフィルター条件があり、インデックスを使用できる場合、返される行数はインデックスに基づいて推定されます。

上記で作成したテーブルをベースとして使用し、次の SQL を例として説明します。

a.a1>5かつc.c1>5のa.a2=c.c2の結合cからa.*、c.c2を選択します。

explain を通じて実行プランを表示します。

説明表示結果の最初の行は駆動テーブルです。この場合、テーブル c が駆動テーブルです。

SQL を変更する場合は、選択内の条件c.c2 c.*に変更します。

a.a1>5かつc.c1>5のa.a2=c.c2の結合cからa.*、c.*を選択します。

explain を通じて実行プランを表示します。

この時点では、駆動テーブルはまだ c です。論理的には、c.* のデータ量は a.* のデータ量よりも大きくなければなりません。結果セット サイズのルールはここでは機能しないようです。

この場合、a が駆動テーブルとして使用され、インデックス c2 を介してテーブル c に関連付けられている場合は、c.* のデータは c2 だけでは取得できないため、再度テーブルに戻ってクエリを実行する必要があり、c2 の主キー c1 を介して再度クエリを実行する必要があります。前の SQL クエリは c2 用なので、追加のクエリは必要ありません。同時に、テーブル a にはフィールドが 2 つしかないため、追加のクエリを必要とせずに、a2 インデックスを通じて a.* を直接取得できます。

まとめると、C テーブルをドライバーとして使用すると結果セットは大きくなりますが、余分なテーブルを返すクエリを削減できるため、MySQL では C テーブルをドライバーとして使用する方が効率的であると考えています。

結果セットは駆動テーブルを選択する際の主要な要素ですが、唯一の要素ではありません。

2 つのテーブルの関連付けクエリの内部ロジックは何ですか?

MySQL テーブル間の関連付けクエリでは、名前が示すようにネストされたループ結合であるネストされたループ結合アルゴリズムが使用されます。ただし、シナリオに応じて、インデックス ネストされたループ結合、シンプル ネストされたループ結合、ブロック ネストされたループ結合、ベッチされたキー アクセス結合など、さまざまなバリエーションが存在する場合があります。

• 使用索引場合、 Index Nested-Loop joinとBatched Key Access join 2 つのアルゴリズムがあります。
• 未使用索引場合は、 Simple Nested-Loop joinとBlock Nested-Loop join 2 つのアルゴリズムがあります。

まず、インデックスを使用したケースを見てみましょう。ブログの冒頭で作成したテーブルを使用すると、SQL は次のようになります。

a.a1>4 で a.a2=c.c2 の結合 c から a.*,c.* を選択します。

explain を通じて実行プランを表示します。

まず、最初のステップのロジックに従って駆動テーブル a を決定し、 a.a1>4、a を介してレコード a1=5 を照会し、このレコードの c2 を c テーブルに関連付け、c2 インデックスの主キー c1 を取得します。次に、c1 の値を使用してクラスター化インデックスの c.* を照会し、完全な結果を形成してネット バッファーに格納し、次に条件 a.a1>4、a に従って次のレコードを取得し、このプロセスを繰り返します。プロセス図は次のとおりです。

駆動テーブルは、インデックス ネスト ループ結合アルゴリズムを使用してインデックスを介して関連付けられ、MSYQL 結合バッファーは使用されません。ドライバテーブルのフィルタ条件に従って、駆動テーブルのインデックスが 1 つずつ関連付けられます。一致するレコードが関連付けられるたびに、そのレコードがネット バッファに格納され、関連付けが続行されます。このバッファ領域は net_buffer_length パラメータによって制御され、最小値は 4k、最大値は 16M、デフォルトは 1M です。 ネット バッファがいっぱいの場合は、クライアントに送信し、ネット バッファをクリアして、前のプロセスを続行します。

以上のプロセスから、駆動テーブルの各レコードが被駆動テーブルに関連付けられている場合、インデックスに含まれていないデータが必要な場合は、一度テーブルに戻ってクラスター化インデックス上のレコードをクエリする必要があることがわかります。これはランダムクエリプロセスです。各レコードはランダムクエリであり、パフォーマンスはあまり高くありません。 MySQL はこの状況を選択的に最適化し、このランダム クエリをシーケンシャル クエリに変換します。実行プロセスは次のとおりです。

このとき、バッチ キー アクセス結合アルゴリズムが使用されます。名前の通り、バッチ キー アクセス接続です。

where 条件に従って駆動テーブルを 1 つずつクエリし、レコードに一致するデータ行を結合バッファーに格納し、関連付けられたインデックスに従って駆動テーブルのインデックス レコードを取得して read_rnd_buffer に格納します。 join buffer と read_rnd_buffer にはどちらもサイズ制限があります。どちらかが上限に達すると、このバッチのデータ処理は停止します。処理後、データはクリアされ、次のバッチが実行されます。つまり、駆動テーブル内の条件を満たすデータは、一度にすべて処理できない場合があり、バッチで処理する必要があります。

バッチ制限に達すると、read_rnd_buffer 内の駆動テーブルのインデックスは主キーの昇順でソートされ、クエリのためにテーブルに戻るときに近似順次クエリを実行できるようになります。

上図の通り、左側はソート前のランダムクエリの模式図、右側はソート後のMRR（ Multi-Range Read ）を用いたシーケンシャルクエリの模式図です。

MySQL の InnoDB エンジンのデータはクラスター化インデックスに従って並べられているため、非クラスター化インデックスが主キーに従って並べられている場合、主キーを使用してクエリが実行され、ランダム クエリがシーケンシャル クエリに変換されます。コンピューターのシーケンシャル クエリには事前読み取りメカニズムがあり、1 ページのデータを読み取るときに最大 1M のデータが読み取られます。ここで、順次読み取りが役に立ちます。

BKA アルゴリズムは、駆動されたテーブルをテーブルに戻す必要がある場合に実行ロジックを最適化できます。テーブルを返す必要がない場合は、当然 BKA アルゴリズムは必要ありません。

BKA 最適化アルゴリズムを使用する場合は、SQL ステートメントを実行する前に設定する必要があります。

optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on' を設定します。

最初の 2 つのパラメータは、MRR ( Multi-Range Read ) を有効にするために使用されます。これを行う理由は、BKA アルゴリズムの最適化は MRR に依存する必要があるためです。公式ドキュメントによると、現在のオプティマイザー戦略は、消費を判断するときに MRR を使用しない傾向がありますmrr_cost_basedをオフに設定することは、MRR が常に使用されることを意味します。 ）

最後に、パラメータをオンにした後、explain を使用して実行プランを表示します。

上記はすべて、駆動テーブルに関連付けられたインデックスがある場合です。次に、駆動テーブルに関連付けられたインデックスがない場合を見てみましょう。

インデックス結合が使用されていない場合、最も単純なSimple Nested-Loop join where 条件に従って駆動テーブルからデータの一部をフェッチし、駆動テーブル全体をスキャンして、条件を満たすレコードを最終結果セットに配置することです。この方法では、駆動テーブルの各レコードに、駆動テーブルの完全なテーブル スキャンが伴います。これは、単純なネスト ループ結合です。

もちろん、MySQL は Simple Nested-Loop 結合を直接使用するのではなく、最適化します。駆動テーブルのデータを 1 つずつ取得するのではなく、複数のレコードを取得します。つまり、レコードを 1 つずつ取得します。これを Block Nested-Loop 結合と呼びます。データのバッチが取得されるたびに、結合バッファのサイズが上限となり、駆動テーブルが完全にスキャンされ、各データが結合バッファ内のすべての行と照合され、一致したデータが最終結果セットに格納されます。これにより、駆動テーブルがスキャンされる回数が大幅に削減されます。

BNL ( Block Nested-Loop join ) とBKA ( Batched Key Access join ) のプロセスは多少似ていますが、 read_rnd_bufferステップはありません。

サンプルSQLは次のとおりです。

a.a1>7 の場合、a.a2=d.d2 の結合 d から a.*、d.* を選択します。

実行プランを表示するには、explain を使用します。

複数テーブルの結合を実行するにはどうすればいいですか?これは、最初に 2 つのテーブルを結合し、次に 3 番目のテーブルを関連付けた結果セットですか、それとも、全体にわたって実行される 1 つのレコードがありますか?

実際、結合アルゴリズムの名前「ネスト ループ結合」を見ると、最初に 2 つのテーブルを結合して結果を取得し、それらを順番に結合するのではなく、複数のテーブルのネスト ループ結合であることがわかります。その形式は次のようになります。

テーブル1の行1をwhere{でフィルタリング
	table2の行2はtable1.index1によって関連付けられ、where{でフィルタリングされます
		table3 の row3 は table2.index2 に関連付けられ、where{ でフィルタリングされます。
			ネットバッファに入れてクライアントに送信します。
		}
	}	
}

さまざまな結合方法では、次のような状況が考えられます。

Index Nested-Loop join :

SQL は次のとおりです。

a から a.*、b.*、c.* を選択し、c を a.a2=c.c2 で結合し、b を c.c2=b.b2 で結合し、b.b1>4 である;

explain を通じて実行プランを表示します。

内部実行プロセスは次のとおりです。

実行前に、MySQL 実行プログラムは各テーブルの関連付け順序を決定します。まず、駆動テーブル b の最初のレコード b5 が where 条件によってフィルタリングされ、次にこのレコードの関連フィールド b2 が 2 番目のテーブル a のインデックス a2 に関連付けられます。インデックスの位置は Btree によって特定されます。一致するインデックスが複数存在する場合があります。前の項目が一致したら、where に a2 のフィルタリング条件があるかどうか、またその条件がインデックス外のデータを必要とするかどうかを確認します。必要な場合は、テーブルに戻り、a2 インデックスの主キーを使用してデータを照会し、判断します。次に、結合した情報を使用して、第 3 章の表 C を同様に関連付けます。

Block Nested-Loop joinとBatched Key Access join : これら 2 つの結合アルゴリズムはIndex Nested-Loop joinアルゴリズムに似ていますが、結合バッファーを使用できるため、1 行ではなく、毎回駆動テーブルからデータのバッチをフィルター処理できます。同時に、各結合キーワードは結合バッファに対応しており、つまり、駆動テーブルと 2 番目のテーブルは 1 つの結合バッファを使用し、取得されたブロック結果セットと 3 番目の章のテーブルは 1 つの結合バッファを使用します。

このブログでは、主に上記の 3 つの問題、駆動テーブルの決定方法、2 つのテーブル間の関連付けの実行詳細、および複数のテーブル間の関連付けの実行プロセスについて説明します。

これで、MySQL マルチテーブル結合クエリの実行詳細に関するこの記事は終了です。MySQL マルチテーブル結合クエリの関連コンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。