MySQL の高度な学習: 結合の 3 つのアルゴリズムを深く理解する

この記事は MySQL の高度な学習であり、結合接続の原理と結合の 3 つのアルゴリズムを詳しく紹介します。

複数のテーブルをクエリするとき、複数のテーブルを結合するために結合を使用することがよくあります。実際、結合の効率は良くないため、使用を避ける必要があります。その本質は次のとおりです。ループ マッチングの場合、MySQL は 1 つの結合アルゴリズム、Nested-Loop Join のみをサポートしますが、アルゴリズムには複数のバリアントがあり、実際に結合の実行効率が向上します。 [関連する推奨事項: mysql ビデオ チュートリアル ]

1. 単純なネストループ結合 (単純なネストループ接続)

Simple Nested Loop join (NLJ) アルゴリズムは、ループ内の最初のテーブルから一度に 1 行を読み取り、データが一貫しているかどうかを照合するネストされたループに各行を渡します。たとえば、駆動テーブル User と駆動テーブル UserInfo の SQL は select * from User u left join User_info info on u.id = info.user_id です。実際、これはよく使用される for ループです。疑似コードのロジックは、

for(User u:Users){
    for(UserInfo info:UserInfos){
        if(u.id == info.userId){
            // 得到匹配数据
        }
    }
}

単純で粗雑なアルゴリズムである必要があり、User テーブルからデータが取得されるたびに、User_info 内のすべてのレコードが一致するかどうかスキャンされ、最後にデータがマージされます。そして戻ってきました。

駆動テーブル User に 10 個のデータがあり、駆動テーブル UserInfo にも 10 個のデータがある場合、駆動テーブル User は実際に 10 回スキャンされ、駆動テーブルは 10 回スキャンされます* 10=100 回 (ドライバー テーブルがスキャンされるたびに、すべての駆動テーブルがスキャンされます) この効率は非常に低く、データベース、特に駆動テーブルのオーバーヘッドが比較的大きくなります。 各スキャンは、実際にはハードディスクからデータを読み取り、メモリにロードします。これが IO です。現在、IO が最大のボトルネックです。

##2. インデックス ネスト ループ結合(インデックス ネスト ループ結合)

インデックス ネスト ループはインデックスを使用してスキャン数を減らし、効率を向上させるため、 -driver テーブルにはインデックスが必要です。

クエリを実行する場合、ドライバー テーブル (ユーザー) は、関連付けられたフィールドのインデックスに基づいてクエリを実行します。インデックスに一致する値が見つかった場合、テーブル クエリが実行されます。非駆動テーブル (User_info) の関連フィールド (user_id) が主キーの場合、クエリ効率は非常に高くなります (主キー インデックス構造のリーフ ノードには完全な行データ (InnoDB) が含まれています)。主キーではないため、インデックスは毎回照合されます。 最後に、テーブル リターン クエリが必要です (セカンダリ インデックス (非主キー インデックス) の主キー ID に基づくテーブル リターン クエリ) が必要となり、パフォーマンスは明らかに低下します。主キークエリよりも。

上の図のインデックス クエリは、必ずしもテーブルを返すとは限りません。どのような状況でテーブルが返されるかは、インデックスによってクエリされたフィールドが条件を満たすかどうかによって決まります。クエリに必要なフィールド。詳細については、前の記事を参照してください:

知っておくべき基本的なインデックスの知識と B ツリー インデックスの知識

3. ブロックネストループ結合 (キャッシュブロックネストループ接続)

インデックスがある場合は、インデックスメソッドを使用して結合されます。結合列にインデックスがない場合は、インデックスが使用されます。 , 駆動テーブルを何度もスキャンする必要があります。駆動テーブルにアクセスするたびに、テーブル内のレコードがメモリにロードされ、その後、それに一致するためにドライバ テーブルからレコードが取得されます。が完了すると、メモリがクリアされ、その後、ドライバ テーブルからレコードがロードされ、一致する駆動テーブルのレコードがメモリにロードされます。これが何度も繰り返されるため、IO の数が大幅に増加します。駆動テーブルの IO 数を減らすために、Block Nested-Loop Join メソッドが登場しました。

ドライバー テーブルのデータを 1 つずつ取得するのではなく、少しずつ取得するようになりました。ドライバー テーブルの結合に関連する一部のデータ列をキャッシュするために結合バッファーが導入されました (サイズには制限があります)駆動テーブルの各レコードは、結合バッファ内のすべての駆動テーブル レコードと一度に照合され (メモリ内操作)、複数の比較が行われます。単純なネストされたループ内の 1 つのループが 1 つにマージされ、非駆動テーブルのアクセス頻度が減少します。

ドライバー テーブルを一度にロードできるかどうかは、結合バッファーにすべてのデータを保存できるかどうかによって決まります。デフォルトでは、

join_buffer_size=256k です。結合バッファをクエリすると、結合列だけでなく、クエリに参加しているすべての列がキャッシュされます。N 個の結合関連付けを持つ SQL では、N-1 個の結合バッファが割り当てられます。したがって、クエリを実行するときは、より多くの列を結合バッファに格納できるように、不要なフィールドを減らすようにしてください。

join_buffer_size

show 変数のようなキャッシュ サイズを調整できます。'%join_buffer%'この値は、実際の状況に応じて変更できます。

ブロック ネストループ結合アルゴリズムを使用するには、オプティマイザー管理構成 block_nested_loop の optimizer_switch 設定をオンにする必要があります。これはデフォルトで有効になっています。 block_nested_loop ステータスは、show variables like '%optimizer_switch%' で表示できます。

上記 3 つのアルゴリズムを理解できれば十分ですが、実際の作業ではインデックスをうまく活用できれば問題ありません。接続を結合する場合は、関連付けられたフィールドが確立されているかどうかに注意を払う必要がありますが、クエリの効率性を高めるためには、インデックスを適切に使用する必要があります。

元のアドレス: https://juejin.cn/post/7014105037517357093

著者: Ji 氏

プログラミング関連の知識については、 プログラミング入門をご覧ください! ！

以上がMySQL の高度な学習: 結合の 3 つのアルゴリズムを深く理解するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。