MySQLの結合の基本原理についての簡単な説明

結合アルゴリズム

MySQL は Nested-Loop Join という 1 つの結合アルゴリズムのみをサポートしていますが、Nested-Loop Join には 3 つのバリエーションがあります。

• 単純なネストループ結合、単純なネスト
• インデックスネストループ結合
• ブロックネストループ結合、結合バッファネスト（一時テーブル）

駆動テーブルと非駆動テーブルの違い

駆動テーブルがメインテーブル、非駆動テーブルがスレーブテーブルです。次のSQLを見るとわかります。Aが駆動テーブル、Bが非駆動テーブルです。

Aから*を選択し、Bを左結合する

A が B に結合する場合、最初にテーブル A をチェックし、次にテーブル B をチェックする必要がありますか?

答えは必ずしもそうではありません。MySQL にはオプティマイザが内蔵されており、クエリ ステートメントに基づいて最適化が行われます。オプティマイザは最初にチェックするテーブルも決定しますが、最初にチェックされるテーブルが駆動テーブルであることは確かであり、その逆も同様です。最初にチェックするテーブルについては、実行プランを確認することで結果を得ることができます。前に explain キーワードを追加するだけです。

A から * を選択して B に参加させる; を説明します。

1. 単純なネストループ結合、単純なネスト、インデックスなし

左結合 B: 毎回、テーブル全体が一致します。テーブル A のデータの各行は、テーブル B で 1 回一致します。つまり、テーブル A に 10 個のレコードがあり、テーブル B に 1000 個のレコードがある場合、クエリ中のスキャン回数は 10 * 1000 となり、クエリはデータを取得するために 10000 回スキャンする必要があることを意味します。

2. インデックスネストループ結合インデックスケース

select * from A join B on A.id=B.id where A.id = 1: クエリを実行すると、ドライバー テーブル A は関連付けられているフィールドのインデックスに従って検索します。インデックスに一致する値が見つかると、クエリがテーブルに返されます。つまり、インデックスが一致した後にのみ、クエリがテーブルに返されます。
非駆動テーブル B の関連フィールド B.id が主キーである場合、パフォーマンスは非常に高くなります。主キーでない場合は、複数のテーブルクエリが実行されます。最初にインデックスが関連付けられ、次にセカンダリインデックスの主キー ID に基づいてテーブルクエリが実行されます。パフォーマンスは主キーよりも遅くなります。

3. ブロックネストループ結合、結合バッファ

インデックスがある場合は、インデックス ネスト ループ結合を使用してテーブルを結合します。結合列にインデックスがない場合は、ブロック ネスト ループ結合が使用されます。結合バッファー。ドライバー テーブルと非ドライバー テーブルの間にはバッファーがあります。クエリを実行すると、ドライバー テーブルのデータが最初にバッファーにキャッシュされ、次に非ドライバー テーブルと一括して照合されます。これは、複数の比較を 1 つの比較に結合する最適化ソリューションです。注: ここでは、関連付けられたテーブルの列だけでなく、選択後の列もキャッシュされます。

バッファサイズ

デフォルトでは、バッファ join_biffer_size の容量は 256k です。データ スペースが 256k より大きい場合、バッファは使用できず、結合は最も単純な Simple Nested-Loop Join に変換されます。ただし、大量のデータをロードするためにバッファ サイズを手動で調整できます。join_biffer_size sql を表示します: show variables like '%join_biffer_size%'

大量データと少量データを持つテーブルの接続順序を選択する方法

小さいテーブルを大きいテーブルに接続するのが最適です。これにより、スキャン回数が減ります。たとえば、大きいテーブルに 1,000 のデータ レコードがあり、小さいテーブルに 10 のデータ レコードしかない場合、最適な接続方法は、小さいテーブルを大きいテーブルに結合することです。なぜこのようにするのでしょうか。

• 大きなテーブルが小さなテーブルに結合され、データが大きなテーブルの 999 行目にある場合、データをクエリするときに、そのデータを見つけるために少なくとも 999 回スキャンする必要があります。
• 小さなテーブルが大きなテーブルに結合され、データが小さなテーブルの9行目にある場合、

詳細

• テーブルをまとめてクエリする場合は、結合する必要があるフィールドのデータ型が同じである必要があるため、3 つを超えるテーブルを使用しないことをお勧めします。
• テーブルを結合する場合は、内部結合が推奨されます。データのクエリでは、外部結合の方が内部結合よりもパフォーマンスを消費します。
• テーブルを結合する際のデータ アクセスを高速化するために、関連付けられたクエリの on 以降の列または using() 内のフィールドにインデックスがあることを確認します。
  

これで、MySQL 結合の基本原理に関するこの記事は終了です。MySQL 結合の基本原理の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。