MySQL JOIN の原理の詳細な紹介

1.結合構文の概要

join は、複数のテーブルのフィールドを接続するために使用されます。構文は次のとおりです。

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON 条件a

table1: left table2: right table。 。

JOINは、その機能に応じて以下の3つに大別されます。

INNER JOIN（内部結合、または等価結合）：2つのテーブル間で接続一致関係のあるレコードを取得します。

LEFT JOIN (左結合): 左側のテーブル (table1) の完全なレコードを取得します。つまり、右側のテーブル (table2) には対応する一致するレコードがありません。

RIGHT JOIN (右結合): LEFT JOIN とは逆に、右側のテーブル (table2) の完全なレコードが取得されます。つまり、左側のテーブル (table1) には一致する対応するレコードがありません。

注: mysql は完全結合をサポートしていませんが、UNION キーワードを使用して LEFT JOIN と RIGHT JOIN を組み合わせて FULL 結合をシミュレートできます。

最初に実験内の 2 つのテーブルを確認します:

テーブルのコメント、合計行数は 28856

テーブル comments_for、行の合計数は 57、comments_id にはインデックスが付けられ、ID 列が主キーです。

上記の 2 つのテーブルがテストの基礎です。次に、comments_for テーブル comments_id にインデックスが付けられており、ID が主キーになっていることに注目してください。

最近、社内の開発者から MySQL JOIN の JOIN の問題について質問がありました。MySQL JOIN についての理解があまり深くないので、私も多くのドキュメントを確認しましたが、最終的に InsideMySQL 公式アカウントで 2 つの記事を見ました。 JOIN の分析は非常によく書かれていると思います。私が実際に行った JOIN のテストを共有しましょう。まず、MySQL の JOIN アルゴリズムを紹介します。これは 3 つのタイプに分けられます (出典: InsideMySQL):

MySQL は、Hope join とそれをサポートできる他の商用データベースとは異なり、Nested-Loop Join (ネストされたループ リンク) という 1 つの JOIN アルゴリズムのみをサポートします。マージ結合ですが、MySQL の Nested-Loop Join (ネストされたループ リンク) にも多くのバリアントがあり、MySQL が JOIN 操作をより効率的に実行するのに役立ちます:

(1) シンプルな Nested-Loop Join (画像は InsideMySQL から取得)

このアルゴリズムは比較的単純です。ドライバー テーブルから R1 を取得して S テーブルのすべての列と一致させ、次に R2、R3 を取得して、R テーブル内のすべてのデータが一致するまでデータをマージすることができます。 S テーブルへの RN アクセスが必要ですが、単純ではありますが、コストはまだ比較的高いです

(2) インデックスのネストされたループ結合、実装は次のとおりです:

インデックスがネストされているため、セット リレーションシップの非駆動テーブルでは、比較時にレコードを 1 つずつ比較する必要がなくなり、代わりにインデックスを使用して比較を減らし、クエリを高速化できます。これが、関連クエリを実行するときに通常、関連フィールドにインデックスが必要となる主な理由の 1 つです。

このアルゴリズムがリンク クエリを実行すると、ドライバー テーブルは関連するフィールドのインデックスに基づいて検索し、インデックス上で一致する値が見つかった場合、クエリ用のテーブルに戻ります。つまり、返されるだけです。インデックスがサーフェスと一致したとき。ドライバー テーブルの選択に関しては、MySQL オプティマイザーは通常、レコード数が少ないドライバー テーブルを選択します。ただし、SQL が特に複雑な場合は、誤った選択を排除できません。

インデックスネストリンク方式では、非駆動テーブルの関連キーが主キーであればパフォーマンスは非常に高くなりますが、主キーでない場合は返される行数が多いと効率が悪くなります。複数のテーブルを返す操作が必要なため、この値は特に低くなります。まずインデックスを関連付けてから、セカンダリ インデックスの主キー ID に基づいてテーブルを返す操作を実行します。この場合、パフォーマンスは比較的低下します。

(3) ブロック ネストループ結合、次のように実装されます:

インデックスがある場合、MySQL はインデックス ネストループ結合アルゴリズムを使用しようとする場合があります。場合によっては、結合カラムが存在しないことがあります。 .index の場合、現時点での MySQL の選択は、最初に導入された単純なネストループ結合アルゴリズムではなく、ブロックネストループ結合アルゴリズムが優先されます。

単純なネストループ結合と比較して、ブロックネストループ結合には、結合バッファーを使用してドライバーテーブルのすべてのクエリ JOIN 関連列を JOIN BUFFER にバッファリングする追加の中間処理プロセスがあります。バッチ テーブルと非ドライバ テーブルも比較されます。これも実装すると、複数の比較を 1 つにマージして、非ドライバ テーブルのアクセス頻度を減らすことができます。つまり、S テーブルにアクセスする必要があるのは 1 回だけです。この方法では、非駆動テーブルが複数回アクセスされることはなく、この場合にのみ結合バッファーにアクセスされます。

MySQL では、パラメータ join_buffer_size を通じて結合バッファの値を設定し、操作を実行できます。デフォルトの join_buffer_size=256K では、MySQL は検索中に、関連するカラムをキャッシュするだけでなく、選択されたカラムを含むすべての必要なカラムを結合バッファにキャッシュします。 N 個の JOIN 関連付けを持つ SQL では、実行中に N-1 個の結合バッファーが割り当てられます。上記の紹介は終了しました、特定の例を見てみましょう（1）フルテーブルは出力情報に参加します：

完全なテーブル table で参照できます。 スキャンする場合、comments_for がドライバー テーブルとして使用されます。関連フィールドにはインデックスが付けられているため、非駆動テーブルのコメントと一致するようにインデックス idx_commentsid に対して完全なインデックス スキャンが実行され、1 行を参照できます。毎回一致しました。このとき、Index Nested-Loop Join が使用され、テーブル全体がインデックスを介して照合されます。comments_for テーブルの大きさがコメントよりもはるかに小さいため、MySQL は小さなテーブル comments_for を駆動として優先していることがわかります。テーブル。

(2) フルテーブルJOIN + フィルタリング条件

EXPLAIN SELECT * FROM comments gc
JOIN comments_for gcf ON gc.comments_id=gcf.comments_id;

このとき、まず、Index Nested-Loop Join を使用します。 1 つの非ドライバに一致します。 comments_for テーブルのインデックス idx_commentsid はシーク一致し、最終的な一致結果は 1 つのエントリに影響すると予想されます。このように、非駆動テーブルの idx_commentsid インデックスに対して 1 つのアクセス操作のみが実行されます。そして効率は比較的高いです。

(3) 関連するフィールドにインデックスがない状況を見てみましょう:

SELECT * FROM comments gc
JOIN comments_for gcf ON gc.comments_id=gcf.comments_id
WHERE gc.comments_id =2056

実行計画を見てみましょう:

実行計画から次のことができます。このテーブルの JOIN はブロック ネスト ループ結合であることを確認してください。まず、小さなテーブル comments_for (わずか 57 行) がドライバ テーブルとして使用され、次に、comments_for の必要なデータが JOIN バッファにキャッシュされます。コメント テーブルはバッチでスキャンされます。つまり、結合バッファーが comments_for のキャッシュ データを格納するのに十分な大きさであれば、A 一致のみが実行されます。

そして、実行計画に非常に明確なプロンプトが表示されます: where を使用する; 結合バッファーを使用する (入れ子になったループをブロック)

一般に、これが発生した場合、SQL を最適化する必要があることがわかります。

この場合、MySQL は単純なネストループ結合という暴力的な方法も選択することに注意してください。このオプティマイザーがどのように選択されるのかはわかりませんが、CBO がベースになっているため、一般的にはブロックネストループ結合が使用されます。オーバーヘッドに関しては、ブロック ネストループ結合のパフォーマンスは、単純ネストループ結合よりもはるかに優れています。

(4) 左側の結合を見てください

EXPLAIN SELECT * FROM comments gc
JOIN comments_for gcf ON gc.order_id=gcf.product_id

実行計画を見てください:

この場合、関連するフィールドがインデックス付けされているため、Index Nested-Loopと言います。 Join しますが、フィルタリング条件がない場合、最初のテーブルが JOIN の駆動テーブルとして選択され、非駆動テーブルのインデックスがインデックス ネストループ結合に関連付けられます。

フィルター条件 gc.comments_id =2056 を追加すると、非駆動テーブルのインデックス ネストループ結合がフィルターで除外され、非常に効率的になります。

次の場合:

EXPLAIN SELECT * FROM comments gc
LEFT JOIN comments_for gcf ON gc.comments_id=gcf.comments_id

gcf テーブルを介してフィルタリングすると、gcf テーブルがデフォルトでドライバ テーブルとして選択されます。これは、フィルタリングされていることは明らかであり、詳細については、実行計画を参照してください:

この時点では、結合は基本的には継続する予定です。間違いを指摘してください。私は真剣に修正します。 。 。 。 。

以上がMySQL JOIN の原理の詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。