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 システム変数によって制御され、自分で変更できます。