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

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

以下もご興味があるかもしれません:
  • MySQL初心者はグループ化や集計クエリの煩わしさから解放されます
  • MySQLのジョイントクエリについて詳しく説明します
  • MySQLの結合クエリ、ユニオンクエリ、サブクエリの原理と使用例の詳細な説明
  • MySQL マルチテーブル共同クエリ操作例の分析
  • MySQL データベースの集計クエリと結合クエリ操作

<<:  Windows で nginx を素早くインストールし、自動的に起動するように設定する

>>:  Webデザイン講座(4):素材と表現について

推薦する

要素のel-tree複数選択ツリー(チェックボックス)親子ノードの関連付けが関連付けられていません

属性チェック-厳密公式ドキュメントでは、チェックボックスが表示されるときに親項目と子項目を互いに関連...

Vue マルチページ構成の詳細

目次1. 複数ページの違い2. SPAとMPA 3. Vue Cli スキャフォールディング構成1....

Javascriptジェネレータの紹介と使用

ジェネレータとは何ですか?ジェネレーターは関数内で実行されるコードです。値を返した後、一時停止し、呼...

Ubuntu 20.04にSogou入力方式をインストールする詳細な手順

1. Fcitx入力フレームワークをインストールする関連する依存ライブラリとフレームワークは自動的に...

Docker での Redis の最も詳細なインストールと構成 (画像とテキスト付き)

1. Dockerに適したRedisのバージョンを見つけるdocker hubで見つけることができ...

GIFアニメーション効果を模倣した自動ビデオ再生を実現するWeChatアプレットの例

需要背景:ミニプログラムページに GIF ダイナミック画像を挿入しますが、GIF 画像は通常サイズが...

1 つの記事で Node.js の非同期プログラミングを学ぶ

目次 はじめに 同期 非同期とブロッキング JavaScript のノンブロッキング コールバック ...

MySQL でインデックス構造として B+ ツリーを使用する利点は何ですか?

序文MySQL では、Innodb と MyIsam の両方がインデックス構造として B+ ツリーを...

OpenSSL は双方向認証のチュートリアルを実装します (サーバーとクライアントのコード付き)

1. 背景1.1 問題点最近の製品テスト レポートでは、PKI ベースの認証方法の使用が推奨されて...

Pagoda Panel のインストール時にサーバーがデータベースにリモート接続できない問題の解決策

自分のウェブサイトを構築する予定なので、618 プロモーションを利用して Tencent Cloud...

Docker+DockerCompose を使用して Web アプリケーションをカプセル化する方法

目次テクノロジースタックバックエンドビルドAPIフロントエンドウェブ構築ゲートウェイ建設ゲートウェイ...

共有サイドバーを実装するためのネイティブJS

この記事では、ネイティブ JS で実装された共有サイドバーを紹介します。効果は次のとおりです。 以下...

CSS3はアニメーション効果を実現するためにvar()とcalc()関数を使用する。

ナレッジポイントをプレビューします。アニメーションフレーム背景グラデーションvar() と calc...

HTML 終了タグの問題と W3C 標準

W3C の原則によれば、XML の各開始タグには対応する終了タグが必要です。つまり、<html...

CSSスタイルシートを効率的に使用するためのヒント: スタイルシートの力を最大限に活用する

インターネット経済の継続的な発展に伴い、インターネット上の専門ウェブサイト、公共サービスウェブサイト...