MySQLクエリ最適化プロセスを理解する

MySQL クエリの最適化には、解析、前処理、最適化という 3 つのステップが必要です。これらのプロセスのいずれかの過程でエラーが発生する可能性があります。この記事では、エラー処理について詳しく説明しません。ただし、MySQL がクエリを実行する方法を理解して、より優れたクエリを作成できるようにするのに役立ちます。

パーサーとプリプロセッサ

最初に、MySQL のパーサーはクエリを一連の命令に分割し、それらから「解析ツリー」を構築します。パーサーは、MySQL の SQL 構文を使用してクエリ ステートメントを変換および検証します。たとえば、パーサーはクエリ内の命令が有効で正しい順序であることを確認し、文字列内の引用符が一致しないなどのエラーをチェックします。

プリプロセッサは、構築された解析ツリーをチェックして、パーサーが処理できない意味情報がないかどうかを確認します。たとえば、テーブルと列の存在がチェックされ、フィールド名とエイリアスが処理されて、列参照が明確であることが保証されます。次に、プリプロセッサが権限をチェックします。これは通常、かなり高速です (サーバーに多数の権限が設定されていない場合)。

クエリオプティマイザー

パーサーとプリプロセッサを通過した後、解析ツリーは有効であると判断され、オプティマイザーによって処理され、最終的にクエリ プランに変換されます。多くの場合、同じ結果を生成するクエリを実行する方法は多数あり、オプティマイザーの役割は最適なオプションを見つけることです。

MySQL はコスト見積もりに基づくオプティマイザーを使用します。つまり、複数の実行プランのコストを予測し、コストが最も低いものを選択します。元の単位コストはランダムな 4KB データ ページの読み取りでしたが、現在はより複雑になり、WHERE 比較条件を実行するコストも含まれるようになりました。 Last_query_cost セッション変数を表示することで、クエリ オプティマイザーによるクエリ ステートメントのコストの見積もりを表示できます。

sakila.film_actor から SQL_NO_CACHE COUNT(*) を選択します。
'Last_query_cost' のようなステータスを表示します。

表示される Last_query_cost は、クエリを完了するために、対応する数のランダム データ ページ アクセスを実行する必要があることをオプティマイザーが見積もっていることを意味します。これは以下の統計的推定に基づいています。

• データ テーブルまたはインデックスによって占有されるデータ ページの数。
• インデックスの候補値。
• データ行、キー、およびキー値の分布に対応するデータ長。
  

オプティマイザーはキャッシュの推定を考慮せず、結果が毎回ディスク I/O から読み取られると想定します。オプティマイザーは、次の理由により、必ずしも最適な実行プランを選択するとは限りません。

• 統計自体は間違っている可能性があります。サーバー側の統計結果はストレージ エンジンに依存し、非常に正確になることも、非常に不正確になることもあります。たとえば、InnoDB は MVCC アーキテクチャのため、テーブルの正確な行数を保持しません。
• 推定コストは実際の実行コストと同じではないため、統計が正確であっても、クエリ コストは MySQL の推定値から多かれ少なかれ逸脱します。ディスク I/O アクセスが順次的に高速化される場合や、結果がすでにキャッシュ内にある場合など、より多くのデータ ページを読み取るクエリ プランの方がコストが安くなることもあります。したがって、オプティマイザー自体は、クエリで発生する I/O 操作の数を認識しません。
• MySQL の人工的な最適化は、予想したものと異なる場合があります。私たちが望むのは実行時間の短縮かもしれませんが、MySQL は速度を追求するだけでなく、コストを最小限に抑えることも目指しています。したがって、コストを転嫁することは必ずしも科学的ではありません。
• MySQL は同時クエリを考慮しないため、クエリの実行速度に影響する可能性があります。
• MySQL は必ずしもコスト見積もりに基づいて最適化するわけではありません。場合によっては、フルテキスト一致条件 (MATCH メソッド) がある場合にフルテキスト インデックスを使用するなど、いくつかのルールに従うだけで済むこともあります。より高速な代替インデックスと非フルテキストクエリ条件がある場合でも、MySQL はクエリをより高速に実行しません。
• オプティマイザーは、ストアド プロシージャやカスタム関数の実行など、制御できない操作のコストを考慮しません。
• オプティマイザーは常にすべての実行プランを予測できるわけではなく、より最適なプランを見落とすこともあります。
  

MySQL クエリ オプティマイザーは、多くの最適化方法を使用してクエリ ステートメントをクエリ実行プランに変換する非常に複雑な部分です。通常、最適化には静的最適化と動的最適化の 2 種類があります。静的最適化は、解析ツリーを検査するだけで実行できます。たとえば、オプティマイザーは数学的な演算ルールを通じて WHERE 条件を方程式に変換できます。静的最適化は、WHERE 条件内の定数値などの特定の値とは関係ありません。これらは一度実行され、異なる値でクエリが再度実行された場合でも有効なままです。これは「コンパイル時の最適化」として理解できます。

対照的に、動的最適化はコンテキストに固有であり、さまざまな要因に依存します。たとえば、WHERE 条件の値や、インデックス内の対応するデータ行の数などです。このプロセスはクエリごとに再推定する必要があり、「実行時の最適化」として理解できます。 MySQL の一般的な最適化方法を以下に示します。

• 結合クエリの並べ替え: データ テーブルは、必ずしもクエリ ステートメントの順序で結合する必要はありません。クエリを結合するための最適な順序を決定することは、非常に重要な最適化です。
• 外部結合を内部結合に変換します。外部結合は、必ずしも外部結合としてクエリする必要はありません。 WHERE 条件やデータ テーブル構造などのいくつかの要因により、外部結合クエリが内部結合と同等になる場合があります。 MySQL はこれらのケースを認識し、結合クエリを書き換えることができます。
• 数学的に同等な式を適用する: MySQL は、式を簡略化するために数学的に同等な変換を適用します。これは、定数を展開および縮小し、不可能なケースと定数式を排除することによって実行できます。たとえば、式 (5=5 AND a>5) は (a>5) に簡略化されます。同様に、(a 5 AND b=c AND a=5。これらのルールは、条件付きのクエリに非常に役立ちます。
• COUNT()、MIN()、および MAX() の最適化: 多くの場合、インデックスと null 値を持つ列は、MySQL がこれらの関数を最適化するのに役立ちます。たとえば、バイナリ ツリーの左端の列の最小値を検索する場合、MySQL はインデックス内の最初のデータ行のみを要求できます。これはクエリの最適化フェーズ中に実行することもでき、クエリの残りの部分では定数値として扱うことができます。最大値を照会する場合も同様で、最後の u 行だけを読み取る必要があります。サーバーがこの最適化を使用する場合、EXPLAIN に「Select tables optimize away」と表示されます。これは、オプティマイザーがクエリ プランからテーブルを削除し、定数に置き換えたことを意味します。同様に、WHERE 条件が指定されていない場合、COUNT(*) クエリは、一部のストレージ エンジン (テーブル内の正確な行数を常に保存する MyISAM など) でも最適化できます。
• 定数式の評価と簡略化: MySQL は、式を定数に簡略化できることを検出すると、最適化フェーズでその簡略化を実行します。たとえば、ユーザー定義変数は、クエリ中に変更されない場合は定数に変換できます。驚くべきことに、最適化フェーズでは、クエリと思われる一部のステートメントも定数に変換されます。例としては、インデックスの MIN() があります。この状況は、主キーまたは独立したインデックスに対する定数クエリにも拡張できます。 WHERE 条件でこのようなインデックスの定数が指定されている場合、オプティマイザは MySQL がクエリの先頭で対応する値を探す必要があることを認識します。この値は、クエリの残りの部分では定数として扱われます。次に例を示します。

EXPLAIN SELECT film.film_id、film_actor.actor_id
sakila.filmより
	INNER JOIN sakila.film_actor USING(film_id)
ここで、film.film_id = 1;

MySQL はこのクエリを 2 つのステップに分割するため、分析結果には 2 つの行が含まれます。最初のステップは、フィルム テーブル内の対応するデータ行を見つけることです。クエリは主キー film_id に基づいているため、MySQL はデータが 1 行しかないことを認識します。 したがって、このときのクエリ解析結果のrefは定数となります。 2 番目のステップでは、MySQL は film_id を既知の値として扱うため、film_actor のクエリの ref も定数になります。その他の同様のシナリオとしては、WHERE、USING、または ON 条件の制約が等式である場合が挙げられます。この例では、MySQL は USING 条件の film_id がすべてのクエリで同じ値であり、この値は WHERE 条件の film_id と同じでなければならないことを認識しています。

• カバーリング インデックス: クエリに必要なすべての列がインデックスに含まれている場合、MySQL は行データの読み取りを回避するためにインデックス データを使用することがあります。
• サブクエリの最適化: MySQL は、一部のタイプのサブクエリをより効率的なバリアントに変換し、独立したクエリではなくインデックス クエリに簡素化することができます。
• 早期中止: MySQL は、クエリ結果を満たした後、クエリ プロセスを早期に中止できます。最も明白な例は LIMIT 条件です。早期解約が必要となる状況もいくつかあります。たとえば、次の例に示すように、MySQL は可能性のある条件を検出した後、クエリ全体を中止することができます。

EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id=1;

分析結果の「追加」フィールドに、「const テーブルを読み取った後に不可能な WHERE が検出されました」と表示されます。早期終了が発生する可能性があるその他の状況としては、次のようなものがあります。

フィルム.フィルムIDを選択
sakila.filmより
	左外部結合 sakila.film_actor USING (film_id)
sakila.film_actor.film_id が NULL の場合;

このクエリでは、俳優が出演している映画は除外されます。各映画には複数の俳優が登場する場合がありますが、俳優が見つかると、MySQL は現在の映画の処理を停止し、次の映画に進みます。 DISTINCT と NOT EXISTS でも同様の状況が発生します。

• 同等性の伝播: MySQL は、クエリに保持されている列が同等であるかどうかを認識します。たとえば、JOIN 条件では、WHERE 条件は次のクエリと同じ列に影響します。

フィルム.フィルムIDを選択
sakila.filmより
	INNER JOIN sakila.film_actor USING(film_id)
ここで、film.film_id > 500;

MySQL は、WHERE 制約が film テーブルだけでなく film_actor テーブルにも適用されることを認識します。ただし、この最適化効果は他のデータベースでは達成されない可能性があります。

• IN クエリの比較: 多くのデータベース サーバーでは、IN クエリは複数の OR 条件に相当し、2 つは論理的に同等です。しかし、MySQL ではそうではありません。MySQL は IN クエリのリスト値をソートし、バイナリ検索を使用してクエリ値がリスト内にあるかどうかを確認します。これにより、アルゴリズムの複雑さが O(n) から O(log n) に軽減されます。

実際、MySQL では上記に挙げたもの以外にも多くの最適化方法が使用されており、ここですべてを列挙することは不可能です。 MySQL のオプティマイザーの複雑さと、それがどれほどスマートであるかを思い出してください。したがって、MySQL オプティマイザに改善の余地がなくなるまでクエリ ステートメントを無期限に最適化するのではなく、オプティマイザがその役割を果たせるようにする必要があります。もちろん、MySQL のオプティマイザーは非常にスマートですが、必ずしも最良の結果が得られるとは限りません。最良の結果がわかっていても、MySQL がそれを知らない場合もあります。この場合、クエリ ステートメントを最適化して MySQL が最適化作業を完了できるようにすることができますが、クエリ ヒントを追加したり、クエリを書き直したり、データ テーブルの設計を変更したり、インデックスを追加したりする必要がある場合もあります。

上記は、MySQL クエリ最適化プロセスを理解するための詳細です。MySQL クエリ最適化の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。