MySQL オプティマイザーによるハッシュ結合の使用を妨害する

この記事では、mysql に関する関連知識を提供します。主に、MySQL オプティマイザーによるハッシュ結合の使用を妨げる方法を紹介します。この記事では、それについて詳しく紹介します。みんなが協力してくれることを願っています。

推奨される学習: mysql ビデオ チュートリアル

GreatSQL コミュニティのオリジナル コンテンツを許可なく使用することはできません。編集者に連絡して指示してください。起源。 GreatSQL は MySQL の国内ブランチ バージョンであり、その使用法は MySQL と一致しています。

まえがき

データベース オプティマイザーは人間の脳に相当します。ほとんどの場合、正しい意思決定を行い、正しい実行計画を策定し、効率的なパスを見つけることができますが、結局のところ、特定の固定ルールやアルゴリズムに基づく判断は、人間の脳ほど柔軟ではない場合があります。オプティマイザーが間違った実行計画を選択したと判断した場合はどうすればよいでしょうか? ステートメントにヒントを追加して、どのパスを選択するかを促します。最適化手法。

Oracle では、複数のテーブルを接続するときにどのテーブル接続方法を選択するかをオプティマイザに指示するためのより柔軟なヒント (use_nl、no_use_nl など) を提供していることがわかっています。 Nest Loop Join、use_hash、no_use_hash を使用して、ハッシュ結合を使用するかどうかを制御します。

しかし、MySQL には長い間 1 つのテーブル接続方法しかありませんでした。それは Nest Loop Join です。ハッシュ結合は MySQL バージョン 8.0.18 まで登場しなかったため、MySQL は提供しませんコントロール テーブルの接続メソッドです。使用できる豊富なヒントがたくさんあります。hash_join および no_hash_join ヒントはほんの一部です。これらはバージョン 8.0.18 にのみ存在します。 19 以降のバージョンではこのヒントが使用されます。放棄されました。2 つのテーブル間でハッシュ結合を実行したい場合はどうすればよいでしょうか?

実験

MySQL8.0.25のスタンドアロン環境で実験してみます。 2 つのテーブルを作成し、それぞれ 10,000 行のデータを挿入し、主キーを使用して 2 つのテーブル間で関連するクエリを実行します。

create table t1(id int primary key,c1 int,c2 int);
create table t2(id int primary key,c1 int,c2 int);
delimiter //
CREATE PROCEDURE p_test()
BEGIN
declare i int;
set i=1;
while i<10001 do
insert into t1 values(i,i,i);
insert into t2 values(i,i,i);
SET i = i + 1;
end while;
END;
//
delimiter ;

下の図に示すように、2 つのテーブルが主キー フィールドを使用してクエリを関連付けている場合に、実際の実行プランをクエリします。

非クエリを使用して 2 つのテーブルをクエリします。 -index フィールド 関連クエリの実際の実行計画は、次の図に示すとおりです。

#実行計画から、関連フィールドにインデックスがあることがわかります。オプティマイザは駆動テーブルのテーブルを選択します。結合する場合はネスト ループ結合が優先され、インデックスが使用できない場合はハッシュ結合が優先されます。

これに基づいて、no_index プロンプトを使用して、ステートメントによる関連フィールドのインデックスの使用を禁止できます。

#上記の実行計画から、no_index プロンプトを使用した後、オプティマイザーがハッシュ結合の使用を選択したことがわかります。

インデックスの選択性が良くない場合、オプティマイザがインデックスを使用してネスト ループ結合を実行することを選択することは非常に非効率的です。

実験の 2 つのテーブルの列 c1 のデータを変更して選択性を低くし、列 c1 に通常のインデックスを構築します。

update t1 set c1=1 where id<5000;
update t2 set c1=1 where id<5000;
create index idx_t1 on t1(c1);
create index idx_t2 on t2(c1);

SQL を実行すると:

select t1.*,t2.* from t1 join t2 on t1.c1=t2.c1;

このクエリ結果は大量のデータを返します。駆動テーブルの関連フィールドの c1 列のインデックス選択性が低くなります。この時点では、ただし、オプティマイザは Nest Loop Join の使用を選択します。ハッシュ結合とネストループ結合のパフォーマンスの違いを実験によって検証できます。

ハッシュ結合を使用する場合の所要時間は、Nest Loop Join を使用する場合の 1/6 であることがわかりますが、オプティマイザがコストに基づいて見積もると、ハッシュ結合を使用するコストはNest Loop Join は、Nest Loop Join を使用するよりもコストが高くなります。ハッシュ結合を使用する方がはるかにコストが低いため、Nest Loop Join を選択します。このとき、関連フィールドのインデックスの使用を禁止するヒントを追加する必要があります。駆動テーブルで毎回フルテーブルスキャンを行うコストは非常に高いため、プロセッサはこの最適化を推定した後、ハッシュ結合を実行することを選択します。

MySQL 公式ドキュメントには、ハッシュ結合の最適化に影響を与えるために BNL および NO_BNL ヒントを使用することが記載されていますが、実験によりテーブル接続関連には影響がないことが証明されています。インデックスが使用可能な場合、オプティマイザはコストを見積もった後、ドリブン テーブルのネストされたループ結合に BNL フル テーブル スキャンを使用せず、代わりにハッシュ結合の使用を選択するため、このシナリオでは NO_BNL は役に立ちません。

では、このインデックスは必要ないので、削除することはできないのでしょうか? no_index のヒントヒントを使用する必要があるのはなぜですか? ビジネスでの使用シナリオが非常に多くあることを知っておく必要があります。ここでは使用しません。このインデックスを他の場所で使用すると、インデックスの効率が大幅に向上する可能性があります。この時点では、このステートメントを使用するだけで、ヒントの利点が強調表示されます。

概要

Nest Loop Join には、応答が最も速い接続方法であり、返されるデータの量が少ないシナリオに適しているという利点があります。 2 つの大きなテーブルが接続されており、大量のデータが返され、関連付けられたフィールドのインデックスが比較的非効率的である場合、ハッシュ結合を使用する方が効率的です。no_index ヒントを使用して、関連付けられたフィールドの非効率的なインデックスを無効にすることができます。フィールドに、オプティマイザにハッシュ結合を選択するよう求めます。

推奨学習: mysql ビデオ チュートリアル

以上がMySQL オプティマイザーによるハッシュ結合の使用を妨害するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。