MySQL 結合バッファの原理

MySQL 結合バッファの原理

1. MySQL 結合バッファ

MySQL が結合操作を処理するプロセスにおいて、結合バッファは重要な概念であり、MySQL テーブル結合の重要な最適化方法です。この概念は実装が複雑ではありませんが、MySQL の結合接続を最適化するための重要な方法です。これにより、「ブルート フォース」接続を実行する際の結合クエリの効率が大幅に向上します。

この概念の正式な説明は、MySQL ドキュメントのこの概念の説明から来ています。説明は短いですが、簡潔で、この最適化の主な実装のアイデアを説明しています。
次のような結合があると仮定します。

テーブル名 タイプ
t1範囲
t2参照
t3 すべて
結合は次のように実行されます。
 
- t1の行が範囲内に一致する間
 - 参照キーに従ってt2のすべての行を読み取ります
  - t1、t2の使用済みフィールドをキャッシュに保存する
  - キャッシュがいっぱいの場合
    - t3のすべての行を読み取ります
      - t3行をキャッシュ内のすべてのt1、t2の組み合わせと比較する
        - 行が結合条件を満たす場合は、クライアントに送信します
    - キャッシュを空にする
 
- t3のすべての行を読み取ります
 - t3行をキャッシュに保存されているすべてのt1、t2の組み合わせと比較する
   - 行が結合条件を満たす場合は、クライアントに送信します

2. 結合バッファキャッシュのストレージスペースの割り当て

次の関数では、table_count は、すべての結合テーブル内でこのテーブルの前の非定数テーブルの数を示します。これは、このテーブルが、以前のすべてのテーブル内の「読み取りが必要な」レコードをキャッシュする必要があるためです (tables[i].table->read_set が設定されます)。

二重ループを実行するたびに、キャッシュするフィールドの記述構造 (および対応するデータ ソース) がコピーされます。つまり、二重ループは割り当てとメタデータの保存のみを目的としており、最終的な cache->buff=(uchar*) my_malloc(size,MYF(0)) は、条件を満たすレコード コンテンツの実際の割り当てです。

静的整数
join_init_cache(THD *thd、JOIN_TAB *テーブル、uint テーブル数)
{
…
  (i=0; i < テーブル数; i++) の場合
  {
    bool have_bit_fields = FALSE;
    uint null_fields=0、used_fields;
    フィールド **f_ptr、*field;
    MY_BITMAP *read_set = tables[i].table->read_set;
    (f_ptr=tables[i].table->field、used_fields=tables[i].used_fields; の場合
 使用されるフィールド;
 f_ptr++)
    {
      フィールド = *f_ptr;
      bitmap_is_set(read_set, field->field_index) の場合
      {
使用されるフィールド--;
長さ+=フィールド->fill_cache_field(コピー);
…
      }
  }
 
  cache->length=長さ+blobs*sizeof(char*);
  キャッシュ->blobs=blobs;
  *blob_ptr=0; /* シーケンス終了 */
  サイズ=max(thd->variables.join_buff_size、cache->length);
  if (!(cache->buff=(uchar*) my_malloc(size,MYF(0))))
    DBUG_RETURN(1); /* キャッシュを使用しない */ /* purecov: 検査済み */
  cache->end=cache->buff+size;
  reset_cache_write(キャッシュ);
  0 を返します。
}

3. 通常のマルチテーブルクエリの実装

もちろん、この「普通」は「単純」かつ「直感的」とも理解でき、ほとんどの場合、実行プロセスも同様です。通常のクエリは実際には各テーブルへの再帰呼び出しであり、行列の乗算とまったく同じです。この対応は非常に直感的で、非常に一般的です。

この通常のクエリアクションはsub_select関数を通じて実装され、基本的には以下を実行します。

tsecer_select()
{
(r = 最初; r != 最後; r = 次)
{
if(sofartest())
{
次のテーブル.tsecer_select()
}
}
}

ここでの sofartest() は、「現在読み込まれているすべてのテーブルを使用して判断を行う」ことを意味し、これは where にプッシュダウンされた式です。たとえば、クエリ select * from a, b where aa > 10 and bb + aa = 10 では、テーブル a が読み取られた後、aa > 10 であるかどうかのチェックがすでに可能になっています。もちろん、これは擬似コードでもない記述方法であり、実際のコードは以下に対応します。

列挙型ネストループ状態
sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
{
…
    エラー= (*join_tab->read_first_record)(join_tab);
    rc = 評価_join_record(結合、join_tab、エラー);
…
  while (rc == NESTED_LOOP_OK)
  {
    エラー = info->read_record(info);
    rc = 評価_join_record(結合、join_tab、エラー);
  }
…
  rc を返します。
}
静的列挙型ネストループ状態
結合レコードを評価する(JOIN *結合、JOIN_TAB *結合タブ、
                     整数エラー)
{
…
  if (選択条件)
  {
    select_cond_result = テスト(select_cond->val_int());
 
    /* 条件を評価する際にエラーがないかチェックする */
    if (join->thd->is_error())
      NESTED_LOOP_ERROR を返します。
  }
…
    もし(見つかったら)
    {
      列挙型 enum_nested_loop_state rc;
      /* 現在の部分結合に対して join_tab からの一致が見つかりました。 */
      rc = (*join_tab->next_select)(join、join_tab+1、0);
      if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
        rc を返します。
      (join->return_tab < join_tab) の場合
        NESTED_LOOP_OK を返します。
      /*
        これがSELECT DISTINCTクエリであるかどうかをテストします。
        フィールドリストにありませんでした。この場合、中止することができます
        結果に新しい行を追加できないため、行が見つかりました。
      */
      if (not_used_in_distinct && found_records != join->found_records)
        NESTED_LOOP_NO_MORE_ROWS を返します。
    }
…
}

ご覧のとおり、これは再帰であり、デカルト積セットを生成するために使用され、プログラム実装と数学的表現の両方の点で非常に簡潔でわかりやすいものです。
MySQL 実装では、tsecer_select 関数の for ループは sub_select の while ループとほぼ同等であり、tsecer_select 関数のループ本体の内容は、evaluate_join_record 関数に配置されます。ここで、sofartest は、evaluate_join_record::test(select_cond->val_int()) に対応し、tsecer_select の nexttable.tsecer_select() ステートメントは、evaluate_join_record::(*join_tab->next_select)(join, join_tab+1, 0) に対応します。

4. 結合バッファの実装を選択する

結合バッファキャッシュを使用する場合、next_select関数はsub_select_cacheを指します。

列挙型ネストループ状態
sub_select_cache(JOIN *join、JOIN_TAB *join_tab、bool end_of_records)
{
  enum_nested_loop_state rc;
 
  (レコードの終了)
  {
    rc = flush_cached_records(join、join_tab、FALSE);
    (rc == NESTED_LOOP_OK || rc == NESTED_LOOP_NO_MORE_ROWS) の場合
      rc = サブセレクト(結合、結合タブ、レコードの終了);
    rc を返します。
  }
  if (join->thd->killed) // ユーザーによって中止された場合
  {
    join->thd->send_kill_message();
    return NESTED_LOOP_KILLED; /* purecov: 検査済み */
  }
  (join_tab->use_quick != 2 || test_if_quick_select(join_tab) <= 0) の場合
  {
    if (!store_record_in_cache(&join_tab->cache))
      return NESTED_LOOP_OK; // キャッシュにはまだ余裕があります
    flush_cached_records(join,join_tab,FALSE) を返します。
  }
  rc = flush_cached_records(join、join_tab、TRUE);
  (rc == NESTED_LOOP_OK || rc == NESTED_LOOP_NO_MORE_ROWS) の場合
    rc = サブセレクト(結合、結合タブ、レコードの終了);
  rc を返します。
}

MySQL ドキュメントの指示と組み合わせると、ここでのコードの意味は非常に明白になります。冒頭のend_of_recordsの判定は、

    if (!store_record_in_cache(&join_tab->cache))
      return NESTED_LOOP_OK; // キャッシュにはまだ余裕があります
    flush_cached_records(join,join_tab,FALSE) を返します。

対応する

  - t1、t2の使用済みフィールドをキャッシュに保存する
  - キャッシュがいっぱいの場合

store_record_in_cache 関数は、キャッシュがいっぱいかどうかを判断します。キャッシュにさらにキャッシュを保持できる場合は、前のテーブルの結合されたレコードをキャッシュに保存し、NESTED_LOOP_OK を返します。注: テーブルスキャンはここから開始されないため、この場所はキャッシュ全体の最適化の鍵となると言えます。一方、キャッシュデータがいっぱいの場合は、flush_cached_records 関数が呼び出され、次の処理が実行されます。

    - t3のすべての行を読み取ります
      - t3行をキャッシュ内のすべてのt1、t2の組み合わせと比較する
        - 行が結合条件を満たす場合は、クライアントに送信します
    - キャッシュを空にする

このプロセスの特別な点は、テーブルの各レコードをキャッシュ内の t1 と t2 のすべての組み合わせと比較して、プッシュダウンの where 条件が満たされているかどうか (行が結合条件を満たしている場合) を判断し、次に join_tab->next_select 関数が実行される (クライアントに送信する) ことです。

静的列挙型ネストループ状態
キャッシュされたレコードをフラッシュします(JOIN *join、JOIN_TAB *join_tab、bool skip_last)
{
…
  情報= &join_tab->read_record;
  する
  // テーブル t3 内のすべてのレコードを走査します...
        (i=(join_tab->cache.records- (skip_last ? 1 : 0)) ; i-- > 0 ;) の場合
        キャッシュ内のすべての t1 および t2 レコードの組み合わせを走査します read_cached_record(join_tab);
          skip_record = FALSE;
          if (select && select->skip_record(join->thd, &skip_record))
          ##
            reset_cache_write(&join_tab->cache);
            NESTED_LOOP_ERROR を返します。
          }
          レコードをスキップする場合
          //プッシュダウンの where 条件を満たす//次のテーブルのトラバーサルを実行する rc= (join_tab->next_select)(join,join_tab+1,0);
            if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
            {
              reset_cache_write(&join_tab->cache);
              rc を返します。
            }
          }
…
  } while (!(error=info->read_record(info)));

5. このプロセスを説明するために例を挙げてみましょう

この実装の核となる考え方は複雑ではなく、具体的な例と組み合わせるとさらにシンプルで直感的になります。
たとえば、2 つの単純なテーブルを使用します。1 つは x の値を格納し、もう 1 つは y の値を保存します。結合操作を通じて、これら 2 つのテーブルで x を満たすすべての値を計算します。

x + y

y == 5 * 5 は、「斜辺は 3、脚は 4、辺は 5」という最も一般的な古典的なピタゴラス数の値です。

mysql> テーブル harry (x int) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.03 秒)
 
mysql> ハリー値(1)、(2)、(3)、(4)、(5)を挿入します。
クエリは正常、5 行が影響を受けました (0.00 秒)
記録: 5 重複: 0 警告: 0
 
mysql> テーブル tsecer (y int) を作成します。                   
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
 
mysql> tsecer値(1)、(2)、(3)、(4)、(5)を挿入します。     
クエリは正常、5 行が影響を受けました (0.00 秒)
記録: 5 重複: 0 警告: 0
 
mysql> explain select * from harry, tsecer where x * x + y * y = 5 * 5;
+----+-------------+--------+-------+---------------+-------+-------+------+--------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+--------+-------+---------------+-------+-------+------+--------------------------------+
| 1 | シンプル | ハリー | すべて | NULL | NULL | NULL | NULL | 5 | |
| 1 | SIMPLE | tsecer | ALL | NULL | NULL | NULL | NULL | 5 | where の使用; 結合バッファの使用 |
+----+-------------+--------+-------+---------------+-------+-------+------+--------------------------------+
セット内の 2 行 (0.00 秒)
 
マイSQL>

1. joinbufferを使用しない

結合バッファがない場合、harry テーブルの各 x 値に対して、対応する tsecer テーブルを完全にスキャンし、x と y の組み合わせを使用して x が満たされているかどうかを判断する必要があります。

x + y

条件 y == 5 * 5。 x には合計 5 つの値があるため、tsecer はテーブル全体を 5 回スキャンする必要があります。

2. ジョインバッファを使用する

x の各値について、tsecer テーブルは実行時にまずこの値を joinbuffer にキャッシュします。バッファの内容が空でない場合、この時点での x の値はバッファに格納され、その後直接返されます。join バッファがいっぱいになるか、最後のレコードになると、tsecer テーブルのスキャンが開始されます。tsecer テーブルから読み取られた各レコードについて、以前にキャッシュされた各レコードと組み合わせて、独自の判定条件を満たしているかどうかを確認します。
これまで見てきた例では、harry テーブルの 5 つの値はすべてキャッシュ内にあります。tsecer テーブルのスキャン中に、tsecer から読み取られた各レコードについて、キャッシュ内の「each」キャッシュと組み合わせて、組み合わせた結果が条件を満たしているかどうかが判断されます。いずれかのグループが条件を満たしている場合は、next_select に進みます。
バッファを使用したこの例では、tsecer テーブルでスキャンが 1 回だけ実行されていることがわかります。一般的に、データベース スキャン コードは最も高くなります (ディスク読み取りが含まれるため)。バッファを使用すると、tsecer テーブルのスキャンが 1 回に減るため、特に複数のテーブルが関係する場合や各テーブルのレコード数が多い場合に、効率が大幅に向上します。

3. キャッシュを最適化できる理由

本質的に、この効率向上の理由は、テーブルから取得される各レコードの「利用率」が向上するためです。直感的なスキャン方法を使用する場合、完全なテーブルスキャンでは 1 つの組み合わせのみが一致しますが、バッファを使用した後は、キャッシュ内のすべての組み合わせが一致します。

上記はMySQL結合バッファ原理の詳細な内容です。MySQL結合バッファの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • 7つのMySQL JOINタイプのまとめ
  • MYSQL データベースの基礎 - 結合操作の原理
  • mysql-joinsの具体的な使用方法
  • MySQL 結合クエリ構文と例
  • MySQL におけるさまざまな一般的な結合テーブルクエリの例の概要
  • MySQLの7つのJOINの具体的な使い方

<<:  JavaScript でフォロー広告を実装するためのサンプルコード

>>:  熟練デザイナーの7つの原則(2):色の使い方

推薦する

React Router で履歴リダイレクトを使用する方法

react-routerでは、コンポーネント内のジャンプは<Link>で使用できます。し...

Centos7.5 構成 Java 環境のインストール Tomcat の説明

Tomcat は Java 言語をベースにした Web サーバー ソフトウェアです。この記事では主に...

vue.js ルーターのネストされたルート

序文:ルートでは、主要部分は同じでも、基礎となる構造が異なることがあります。たとえば、ホームページに...

JS デコレータ パターンと TypeScript デコレータ

目次デコレータパターンの紹介TypeScript のデコレータデコレータの使用デコレーターファクトリ...

Linuxファイルを表示するコマンドの詳細な説明

Linuxファイルを表示する方法ファイルの内容を表示するコマンド: catは最初の行からコンテンツを...

MySQLの共通関数を使用してJSONを処理する方法

公式ドキュメント: JSON 関数名前説明JSON_APPEND() JSONドキュメントにデータを...

Linux システムによって報告される xfs_vm_releasepage 警告問題に対処する方法

問題の説明最近、いくつかのマシンで、一日のさまざまな時間に次の警告メッセージが表示されました。 3月...

MySQL 8.0.22 のインストールと設定のグラフィックチュートリアル

MySQL8.0.22のインストールと設定(超詳細)参考までに、具体的な内容は次のとおりです。みなさ...

Docker MySQLコンテナデータベースへの変更が有効にならない問題を解決する

公式の MySQL イメージを使用するには、構成ファイル、DB データ ファイル ディレクトリなどの...

JavaScript でオブジェクトをエレガントに扱う 6 つの方法

目次序文1. オブジェクト.freeze() 2. オブジェクト.seal() 3. オブジェクト....

Linux でショートカットアイコンを設定する方法

序文Linux でショートカットを作成すると、アプリケーションをより速く開くことができます。ここで、...

IIS を使用して X-Forwarded-For ヘッダー (XFF) を呼び出して訪問者の実際の IP を記録する 2 つの方法

問題: IIS を通じて公開された Web サイトは F5 デバイスの背後に配置されています。透過的...

Echart Bar の 2 列チャート スタイルの最も完全な詳細な説明

目次序文インストールと設定1. Echartsをインストールする2. Echartsをグローバルに導...

MySQL の例 DTID マスタースレーブ原理の分析

目次1. GTIDの基本概念2. GTIDの利点3. GTIDの仕組み4. 従来のレプリケーションに...

CSS を使用してデータ ホットスポット効果を実現する方法

効果は以下のとおりです。 分析する1. ここでは、点を囲む 3 つの円がズームアニメーションを実行し...