MySQLの共同クエリ最適化メカニズムの詳細な説明

MySQL フェデレーテッドクエリ実行戦略。

UNION クエリを例に挙げてみましょう。MySQL は UNION クエリを実行すると、それを一連の単一クエリステートメントとして扱い、対応する結果を一時テーブルに格納し、最終的にそれらを読み取って返します。 MySQL では、各独立したクエリは結合クエリであり、一時テーブルから結果を返す場合も同様です。

この場合、MySQL の結合クエリの実行は単純です。ここでは、結合クエリがネストされたループ結合クエリとして扱われます。つまり、MySQL は 1 つのループを実行して 1 つのテーブルから行を読み取り、次にネストされたループを実行して次のテーブルから一致する行を読み取ります。このプロセスは、結合クエリ内の一致する行がすべて見つかるまで続行されます。次に、SELECT ステートメントで必要な列に従って戻り結果を構築します。次のクエリステートメントに示すように:

tb1.col1、tb2.col2を選択します
tb1 から tb2 を内部結合し (col3) を使用する
tb1.col1 が(5,6)の場合;

MySQL が実行する実際の疑似コードは次のとおりです。

outer_iter = tb1 上の反復子、col1 IN(5,6);
外側の行 = 外側のイター.next;
外側の行
	inner_iter = tb2 の反復子、col3 = outer_row.col3;
	inner_row = inner_iter.next
    inner_row の間
    	[outer_row.col1, inner_row.col2]を出力します。
        inner_row を inner_iter の次の行に挿入します。
	終わり
    外側の行 = 外側の iter.next;
終わり

擬似コードに変換すると次のようになります

outer_iter = tb1 上の反復子、col1 IN(5,6);
外側の行 = 外側のイター.next;
外側の行
	inner_iter = tb2 の反復子、col3 = outer_row.col3;
	inner_row = inner_iter.next
    内側の行の場合
        inner_row の間
            [outer_row.col1, inner_row.col2]を出力します。
            inner_row を inner_iter の次の行に挿入します。
        終わり
    それ以外
    	出力[outer_row.col1, NULL];
	終わり
    外側の行 = 外側の iter.next;
終わり

クエリプランを視覚化する別の方法は、スイムレーンダイアグラムを使用することです。次の図は、内部結合クエリのスイムレーン図を示しています。

MySQL は基本的に同じ方法であらゆる種類のクエリを実行します。たとえば、FROM 条件で最初にサブクエリを実行する必要がある場合、結果は最初に一時テーブルに格納され、次に一時テーブルが通常のテーブルとして扱われ、処理のために結合されます。 MySQL は、ユニオンクエリを実行するときに一時テーブルも使用し、右結合クエリを同等の左結合に書き換えます。つまり、現在のバージョンの MySQL では、さまざまなクエリを可能な限りこの処理方法に変換します (最新バージョンの MySQL5.6 以降では、より複雑な処理方法が導入されました)。

もちろん、すべての正当な SQL クエリステートメントがこれを実行できるわけではなく、一部のクエリではこの方法ではパフォーマンスが低下する可能性があります。

実行計画

他の多くのデータベース製品とは異なり、MySQL はクエリプランを実行するためのクエリステートメントのバイトコードを生成しません。実際、クエリ実行プランは命令のツリーであり、クエリ実行エンジンはこのツリーに基づいてクエリ結果を生成します。最終的なクエリプランには、元のクエリを再構築するのに十分な情報が含まれています。クエリ文に対して EXPLAIN EXTENDED を実行し（MySQL 8 以降では EXTENDED を追加する必要はありません）、その後 SHOW WARNINGS を実行すると、再構築されたクエリを確認できます。

概念的には、複数テーブルのクエリはツリーで表すことができます。たとえば、4 つのテーブルを含むクエリは次のツリーのようになります。これをコンピューターではバランスツリーと呼びます。

ただし、これは MySQL がクエリを実行する方法ではありません。前述したように、MySQL は常に 1 つのテーブルから開始し、次のテーブルで一致する行を検索します。したがって、MySQL のクエリプランは、次の左深結合ツリーのようになります。

フェデレーテッドクエリオプティマイザー

MySQL のクエリオプティマイザの最も重要な部分は、複数テーブルクエリを実行する最適な順序を決定する共同クエリオプティマイザです。多くの場合、複数の結合クエリのシーケンスを使用することで同じ結果が得られます。フェデレーテッドクエリオプティマイザーは、これらのプランのコストを見積もってから、実行するプランとして最もコストが低いプランを選択します。

以下は、同じ結果を返すが異なる順序で返されるユニオンクエリの例です。

film.film_id、film.title、film.release_year、actor.actor_id、actor.first_name、actor.last_name を選択してください
sakila.filmより
INNER JOIN sakila.film_actor USING(film_id)
INNER JOIN sakila.actor USING(actor_id);

ここでクエリを実行する方法はいくつかある可能性があります。たとえば、MySQL は film テーブルから開始し、film_actor の film_id インデックスを使用して対応する actor_di 値を見つけ、次に actor テーブルの主キーを使用して対応する actor データ行を見つけます。 Oracle ユーザーは次のように述べる場合があります: 「film テーブルは film_actor の駆動テーブルであり、film_actor は actor テーブルの駆動テーブルです。」 Explain 解析を使用した結果は次のとおりです。

******** 1行目 ********
id: 1
選択タイプ: シンプル
表: 俳優
タイプ: すべて
可能なキー: プライマリ
キー: NULL
キー長さ: NULL
参照: NULL
行数: 200
余分な：
******** 2行目 ********
id: 1
選択タイプ: シンプル
テーブル: 映画俳優
タイプ: ref
可能なキー: PRIMARY、idx_fk_film_id
キー: PRIMARY
キーの長さ: 2
参照: sakila.film.film_id
行数: 1
追加: USING インデックス
******** 3行目 ********
id: 1
選択タイプ: シンプル
表: フィルム
タイプ: eq_ref
可能なキー: プライマリ
キー: PRIMARY
キーの長さ: 2
参照: sakila.film_actor.film_id
行数: 1
余分な：

この実行計画は私たちが予想していたものとは大きく異なります。 MySQL は最初に actor テーブルから開始し、その後逆の順序で進みます。これは実際により効率的ですか?最適化を回避するために、EXPLAIN に STRAIGHT_JOIN を追加できます。

EXPLAIN SELECT STRAIGHT_JOIN film.film_id、film.title、film.release_year、actor.actor_id、actor.first_name、actor.last_name
sakila.filmより
INNER JOIN sakila.film_actor USING(film_id)
INNER JOIN sakila.actor USING(actor_id);

******** 1行目 ********
id: 1
選択タイプ: シンプル
表: フィルム
タイプ: すべて
可能なキー: プライマリ
キー: NULL
キー長さ: NULL
参照: NULL
行数: 951
余分な：
******** 2行目 ********
id: 1
選択タイプ: シンプル
テーブル: 映画俳優
タイプ: ref
可能なキー: PRIMARY、idx_fk_film_id
キー: idx_fk_film_id
キーの長さ: 2
参照: sakila.film.film_id
行数: 1
追加: USING インデックス
******** 3行目 ********
id: 1
選択タイプ: シンプル
表: 俳優
タイプ: eq_ref
可能なキー: プライマリ
キー: PRIMARY
キーの長さ: 2
参照: sakila.film_actor.actor_id
行数: 1
余分な：

これは、MySQL がクエリを逆の順序で実行する必要があり、その結果検査される行数が少なくなる理由を説明しています。

最初にfilmテーブルをクエリすると、film_actorとactor（最も外側のループ）に対して951回のクエリが必要になります。
俳優テーブルを最前面に持ってくると、他のテーブルを 200 回クエリするだけで済みます。

この例から、MySQL の共同クエリオプティマイザーは、クエリテーブルの順序を調整することでクエリコストを削減できることがわかります。結合クエリの順序変更は通常、非常に効果的な最適化であり、パフォーマンスが数倍向上することがよくあります。パフォーマンスが改善されない場合は、STRAIGHT_JOIN を使用して並べ替えを回避し、最適と思われるクエリ方法を使用することもできます。実際にはこのような状況に遭遇することはほとんどなく、ほとんどの場合、共同クエリオプティマイザーは人間よりも優れた結果をもたらします。

クエリオプティマイザーは、ユニオンを参照して、完了コストが最も低いクエリ実行ツリーを構築します。可能であれば、すべての単一テーブルプランから開始し、すべての可能なサブツリーの組み合わせをチェックします。残念ながら、N 個のテーブルの結合クエリでは、可能な組み合わせは N の階乗になります。これは、すべての可能なクエリプランの検索スペースと呼ばれ、急速に増加します。 10 個のテーブルの結合インデックスには、3,628,800 通りの方法があります。検索空間が大きくなりすぎると、クエリの最適化に非常に長い時間がかかります。この時点で、サーバーは完全な分析の実行を停止し、貪欲アルゴリズムに似た方法で最適化を完了します。この数値は optimizer_search_depth システム変数によって制御され、自分で変更できます。

以下もご興味があるかもしれません: