データベースハッシュ接続（MySQLの新機能）の詳細説明

概要

長い間、MySQL が結合を実行するために使用する唯一のアルゴリズムは、ネストされたループ アルゴリズムでした。アルゴリズム) ですが、ネストされたループ アルゴリズムは特定のシナリオでは非常に非効率であり、これは MySQL が批判されている問題でもあります。

MySQL 8.0.18 のリリースにより、MySQL Server はハッシュ結合を使用できるようになりました。この記事では、ハッシュ結合を実装する方法と、それが MySQL でどのように機能するかを簡単に紹介します。いつ使用するか、および機能について説明します。制限。

推奨学習: MySQL チュートリアル

ハッシュ接続の概要

ハッシュ接続とは何ですか?

ハッシュ結合は、リレーショナル データベースで使用される結合アルゴリズムであり、等しい結合条件 (a.b = c.b) を持つ結合にのみ使用できます。一般に、特にインデックスがヒットしない場合には、ネストされたループ アルゴリズムよりも効率的です (プローブの終端が非常に非常に小さい場合を除く)。

簡単に言うと、ハッシュ接続アルゴリズムは、最初に小さなテーブルをメモリ ハッシュ テーブルにロードし、次に大きなテーブルのデータを走査し、ハッシュ テーブル内の修飾されたデータを行ごとに照合し、それを顧客側に返却します。

(ハッシュ テーブルは単なる例であり、実際のハッシュのキーは接続の値であり、その値はデータ行のリンク リストです)

通常、ハッシュ接続は、ビルド フェーズとプローブ フェーズの 2 つのフェーズに分割されます。構築フェーズでは、最初に適切なテーブルが「構築入力」として選択され、ハッシュ テーブルが構築されます。次に、ハッシュ テーブルを検出するために別の「検出入力」テーブルのレコードが走査され、条件を満たすレコードが見つかります。接続条件。

上の図は、都市に対応する州をクエリする例です。市が建設入力であると仮定します。建設フェーズ中に、サーバーは市のハッシュ テーブルを構築し、市のテーブルを走査し、ハッシュ テーブルに行を順番に入れます。キーは hash(province_id) で、値は対応する都市行。 `

プローブフェーズ中に、サーバーはプローブ入力 (州) からの行の読み取りを開始します。各行について、hash(province.province_id) 値を検索キーとして使用して、一致する行がないかハッシュ テーブルが調査されます。

つまり、コンストラクション入力をすべてメモリにロードできる場合、各検出ラインは 1 回だけスキャンされ、定数時間検索を使用して 2 つの入力間の一致ラインを見つけることができます。

データが多すぎてメモリに入れられない場合はどうすればよいですか?

すべてのビルド入力をメモリにロードするのが最も効率的であることは間違いありませんが、場合によっては、テーブル全体をメモリにロードするにはメモリが十分ではないため、バッチで処理する必要があります。

一般的な方法は 2 つあります:

バッチで処理するためにメモリにロードする

#1. 最大メモリを読み取ると、レコードを収容するハッシュ テーブルを作成し、入力を構築し、ハッシュ テーブルを生成します。

2. 検出入力をスキャンして、ハッシュ テーブルのこの部分の完全な検出を実行します。

3. ハッシュ テーブルをクリーンアップして再起動します。すべての処理が完了するまでこのプロセスを続けます。

このメソッドでは、検出入力テーブル全体が複数回スキャンされます。

ファイルへの書き込み処理

1. ハッシュ テーブルのビルド フェーズ中にメモリがなくなると、サーバーは残りのビルド入力を小さなファイルで多くのディスクに書き込みます。 、計算後にすべての小さなファイル ブロックをメモリに読み込むことができ、ハッシュ テーブルが作成されます (ファイル ブロックが大きすぎて後でメモリにロードできず、再度分割する必要があることを避けるため);

2. 検出フェーズでは、検出行がディスクに書き込まれたビルド入力の行と一致する可能性があるため、検出入力もディスクに書き込む必要があります;

3. 検出フェーズの後完了すると、ブロック ファイルがディスクから読み取られてメモリにロードされます ハッシュ テーブルで、検出入力から対応するブロック ファイルを読み取り、一致する項目を検出します;

4. 処理後、次のペアに移動しますすべての処理が完了するまでファイルをブロックします。

MySQL でのハッシュ ジョインの実装

MySQL は、2 つの入力のうち小さい方をビルド入力 (バイト単位で計算) として選択し、十分なメモリがある場合にいくつかの入力を選択します。場合によっては、ビルド入力は処理のためにメモリにロードされますが、十分でない場合には、ファイルに書き込むことによって処理されます。

join_buffer_size システム変数を使用して、ハッシュ接続のメモリ使用量を制御できます。ハッシュ接続で使用されるメモリは、この量を超えることはできません。この量を超えると、MySQL は処理にファイルを使用します。

メモリが join_buffer_size を超え、ファイルが open_files_limit を超える場合、実行が失敗する可能性があります。

次の 2 つの解決策を使用できます。

#● join_buffer_size を増やして、ディスクへのハッシュ接続のオーバーフローを回避します

#● open_files_limit を増やす

MySQL はどのような状況でハッシュ結合を使用しますか?

MySQL バージョン 8.0.18 では、1 つ以上の等しい結合条件を使用してテーブルが結合されており、結合条件に使用できるインデックスがない場合は、ハッシュ結合が使用されます。 MySQL は、インデックスが使用可能な場合、ネストされたループをサポートするためにインデックス ルックアップを使用することを好みます。

デフォルトでは、MySQL は可能な限りハッシュ結合を使用しますが、これは次の 2 つの方法で有効または無効にできます:

#● グローバル変数またはセッション変数を設定します (hash_join = on または hash_join = off);

SET optimizer_switch="hash_join=off";

# ヒント (HASH_JOIN または NO_HASH_JOIN) を使用します。

次のクエリを例として使用します:

EXPLAIN FORMAT = tree
SELECT
  city.name AS city_name,
  province.name AS province_name
FROM
  city
  JOIN province
    ON city.province_id = province.province_id;

出力は次のとおりです:

| -> Inner hash join (city.province_id = province.province_id)  (cost=1333.82 rows=1329)
    -> Table scan on city  (cost=0.14 rows=391)
    -> Hash
        -> Table scan on province  (cost=3.65 rows=34)

ハッシュ結合は、複数の結合クエリでも使用できます。等しい値の結合の場合は、ハッシュ接続を使用できます。

たとえば、次のクエリ:

EXPLAIN FORMAT= TREE
SELECT
  city.name AS city_name,
  province.name AS province_name,
  country.name AS country_name
FROM
  city
  JOIN province
    ON city.province_id = province.province_id
    AND city.id < 50
  JOIN country
    ON province.province_id = country.id

出力は次のようになります:

| -> Inner hash join (city.province_id = country.id)  (cost=23.27 rows=2)
    -> Filter: (city.id < 50)  (cost=5.32 rows=5)
        -> Index range scan on city using PRIMARY  (cost=5.32 rows=49)
    -> Hash
        -> Inner hash join (province.province_id = country.id)  (cost=4.00 rows=3)
            -> Table scan on province  (cost=0.59 rows=34)
            -> Hash
                -> Table scan on country  (cost=0.35 rows=1)

ハッシュ接続は「デカルト積」にも適用できます。つまり、クエリ条件は指定されていません。

EXPLAIN FORMAT= TREE
SELECT
  *
FROM
  city
  JOIN province;

出力は次のとおりです:

| -> Inner hash join  (cost=1333.82 rows=13294)
    -> Table scan on city  (cost=1.17 rows=391)
    -> Hash
        -> Table scan on province  (cost=3.65 rows=34)

MySQL がハッシュ結合を使用しないのはどのような状況ですか?

1. 現在、MySQL ハッシュ結合は内部結合のみをサポートしていますが、アンチ結合、セミ結合、および外部結合は引き続きブロックのネストされたループを使用して実行されます。

2. インデックスが使用可能な場合、MySQL はネストされたループをサポートするためにインデックス ルックアップを使用することを優先します;

3. 同等のクエリがない場合は、ネストされたループが使用されます。

は次のとおりです:

EXPLAIN FORMAT=TREE
SELECT
  *
FROM
  city
  JOIN province
    ON city.province_id < province.province_id;

出力は次のとおりです:

| <not executable by iterator executor>

ステートメントの実行でハッシュ接続が使用されているかどうかを確認するには?

EXPLAIN FORMAT= TREE は、MySQL 8.0.16 以降のバージョンで使用できます。TREE は、ツリーのような出力を提供し、従来の形式よりもクエリ処理をより正確に記述します。これが唯一の表示形式です。ギリシャ語接続の使用形式。

さらに、EXPLAIN ANALYZE を使用してハッシュ接続情報を表示することもできます。

---

上記は、MySQL コミュニティ Server 8.0.18 に基づいています。

以上がデータベースハッシュ接続（MySQLの新機能）の詳細説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。