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):色の使い方

推薦する

CSS変数var()の使い方を理解する必要があります

Web プロジェクトがどんどん大きくなると、CSS は天文学的な大きさと複雑さを増します。この問題を...

魔法のMySQLデッドロックトラブルシューティング記録

背景MySQL のデッドロックについて言えば、私は以前 MySQL のロックに関する基本的な紹介記事...

HTML は、Web ページの作成者が学習して習得しなければならないものです。

HTML を学ぶメリットは何ですか? 1: ウェブサイトやブログのウェブ構造を簡単に変更できます。...

Docker+K8S クラスタ環境構築と分散アプリケーション展開

1. Dockerをインストールする yumでdockerをインストール #サービスを開始する sy...

便利なモバイルスクロールプラグイン BetterScroll

目次スクロールをスムーズにするBetterScrollのスクロール体験慣性ローリングエッジリバウンド...

88 秒で 1,000 万件のレコードを MySQL データベース テーブルに挿入する方法

私が使用しているデータベースはMySQLデータベースバージョン5.7ですまずデータベーステーブルを自...

JavaScript のマクロタスクとマイクロタスクの詳細

目次1. マイクロタスクとは何ですか? 2. マクロタスクとは何ですか? 3. 事例3.1 結論4....

MySQL ストレステストツール Mysqlslap の使用

1. MySQL独自のストレステストツールMysqlslap mysqlslap は、mysql に...

Linux システムで Tomcat のポート 80 を使用する方法

アプリケーションシナリオ多くの場合、Linux サーバーに tomcat や nginx などのソフ...

MySQL 5.7.17 無料インストールバージョンの設定方法グラフィックチュートリアル (Windows10)

1. 概要ネットでいろいろ検索してみたところ、Linux システム向けではなく、現在の新しいバージ...

マウスのドラッグ効果を実現するJavaScript

この記事では、マウスドラッグ効果を実現するためのJavaScriptの具体的なコードを参考までに紹介...

Centos8 で yum を使用して rabbitmq をインストールするチュートリアル

/etc/yum.repos.d/フォルダに入るrabbitmq-erlang.repo ファイルを...

CSS 属性を使用してマウス イベントをブロックする方法 (マウス クリックは上位の要素を貫通する可能性があります)

由来: 数日前、テスターから写真を見るという要件が送られてきました。 この要件を見たとき、私は少し混...

Vue でスクロールバーのスタイルを変更する方法

目次まず、スクロール バーのスタイルを変更するには、疑似要素-webkit-scrollbarを使用...

MySQL テーブルの追加、削除、変更、クエリの基本チュートリアル

1. 作成する [テーブル名] (フィールド1、フィールド2、...) 値 (値1、値2、...) ...