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

MySQLはクエリと連携してポリシーを実行します。
一つのUNIONクエリを例にとって、MySQLがUNIONクエリを実行すると、一連の単一クエリ文として扱い、対応する結果を仮テーブルに入れて、最終的に読み返します。MySQLでは、個々のクエリは、一時テーブルから読み出しても同じです。
この場合、MySQLの共同クエリは簡単に実行されます。ここでの共同クエリは入れ子ループの共同クエリとして扱われます。これは、MySQLがデータテーブルからデータ行を読み出すサイクルを実行することを意味しますが、次のテーブルから適合データ行を読み出すための入れ子ループを実行します。このプロセスは、共同クエリ内のすべての一致するデータ行を見つけるまで継続される。そしてSELECT文で必要な列によって戻り結果を構築します。以下のクエリー文のように、

SELECT tb1.col1, tb2.col2
FROM tb1 INNER JOIN tb2 USING(col3)
WHERE tb1.col1 IN(5,6);

実際にMySQLに変換される可能性のある疑似コードは、以下のようなものである。

outer_iter = iterator over tb1 where col1 IN(5,6);
outer_row = outer_iter.next;
while outer_row
	inner_iter = iterator over tb2 where col3 = outer_row.col3;
	inner_row = inner_iter.next
    while inner_row
    	output [outer_row.col1, inner_row.col2];
        inner_row = inner_iter.next;
	end
    outer_row = outer.iter.next;
end

疑似コードに変換したら下記のようになります。

outer_iter = iterator over tb1 where col1 IN(5,6);
outer_row = outer_iter.next;
while outer_row
	inner_iter = iterator over tb2 where col3 = outer_row.col3;
	inner_row = inner_iter.next
    if inner_row
        while inner_row
            output [outer_row.col1, inner_row.col2];
            inner_row = inner_iter.next;
        end
    else
    	output [outer_row.col1, NULL];
	end
    outer_row = outer.iter.next;
end

もう一つの方法では、検索計画を可視化する方法は、コース図を使用する形です。下の図は内連結検索のコース図を示しています。

MySQLが実行する各種クエリは基本的に同じ方式です。例えば、FROM条件で先にサブクエリが必要な場合も、結果を仮テーブルに入れてから仮テーブルを普通テーブルとしてまとめて処理します。MySQLが共同クエリを実行する場合も、一時テーブルを使用して、右接続クエリを等価左接続に書き換えます。簡単に言えば、現在のバージョンのMySQLは、できるだけ各種類のクエリをこのような方法に変えて処理しています（最新バージョンのMySQL 5.6以降、より多くの複雑な処理方式が導入されました）。
もちろん、すべての合法的なSQLクエリ文がこのようにすることができるわけではありません。
計画を実行する
MySQLは他の多くのデータベース製品と違って、クエリ文をバイトコードで生成してクエリ計画を実行しません。実際には、調査実行計画はツリーであり、クエリ実行エンジンはこのツリーに基づいてクエリ結果を生成する。最終的な照会計画は、最初のクエリを再構築するために十分な情報を含んでいる。検索文でEXPLAN EXTENDED（MySQL 8以降はEXTENDEDは不要）を実行し、SHOW WARNINGSを実行すれば、再構築後のクエリーが見られます。

複数のテーブルクエリについては、概念的にはツリーで表現することができる。例えば、4つのテーブルのクエリーは、下のツリーのように見えるかもしれない。これはコンピュータの中でバランスツリーと呼ばれています。
しかし、これはMySQLがクエリーを実行する方法ではない。前に述べたように、MySQLはいつもデータテーブルから始まり、次のテーブルからマッチするデータ行を探します。したがって、MySQLのクエリプランは、ツリーの左深接続のように見えます。

共同クエリ最適化器
MySQLのクエリ最適化器の中で最も重要な部分は、共同クエリ最適化器であり、それによって複数のテーブルクエリ実行プロセスの最適な順序を決定する。通常、複数の共同クエリの順序によって同じ結果が得られる。これらのスキームの価格を推定し、最低価格のスキームを選択して実行しようとする最適化器を共同で照会する。
以下はクエリーと同じ結果であるが、異なる順序の共同クエリの例である。

SELECT film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name
FROM 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の駆動時計です」と話しています。Explinを用いて解析した結果は以下の通りである。

******** 1.row ********
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra:
******** 2.row ********
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY, idx_fk_film_id
key: PRIMARY
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: USING index
******** 3.row ********
id: 1
select_type: SIMPLE
table: film
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.film_id
rows: 1
Extra: 

この実行計画は私たちの予想とは大きく違っています。MySQLはまずactorテーブルから始まり、そして順序は逆です。これは本当に効果的ですか？EXPLANにSTRAIGHT_を追加できます。JOINは最適化を避けるために：

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

******** 1.row ********
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
******** 2.row ********
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY, idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: USING index
******** 3.row ********
id: 1
select_type: SIMPLE
table: actor
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.actor_id
rows: 1
Extra: 

これはなぜMySQLが逆順序でクエリを実行する必要があるのかを説明しています。これは検査のデータ行がより少なくなります。

film表を先に調べたらfilm_が必要です。actorとactorは951回の照会を行います。

actorテーブルを前にすると、他のテーブルに200回の問い合わせが必要になります。

この例から、MySQLの共同クエリ最適化器は、照会テーブルの順序を調整することによってクエリの価格を低減することができることが分かる。並べ替え後の共同クエリは通常、非常に効果的な最適化であり、通常は数倍の性能の向上である。性能の向上がなければ、STRAIGHT_も使えます。JOINは並び替えを避けるために、私達自身が一番いいと思う照会方法を使います。このような状況は実際に出会うことが少ないです。ほとんどの場合、共同調査の最適化器は人より優れています。
共同クエリ最適化器ビューは、クエリ実行ツリーを最小完了価格で構築する。可能であれば、すべてのシングルスケジュールから、可能なすべてのツリーの組み合わせをチェックします。不幸なことに、N枚の共同クエリにはN個の階乗の組み合わせの数があります。これはすべての可能な照会計画と呼ばれる検索空間の数が非常に速い。10つのテーブルの共同インデックスは3628800の異なる方法があります。検索空間が大きすぎると、検索の最適化が非常に長くなります。この時、サービス端末はフル量の分析を行います。貪欲アルゴリズムのような方法で最適化を完成します。この数量はoptimizer_を通ります。search_depthシステム変数制御は、自分でパラメータを変更することができます。