前回の記事」では、Explainコマンドの詳しい使い方を説明しました。しかし、Explain コマンドは SQL ステートメントの実行プランだけを表示することができ、インデックスがある場合にクエリでインデックスが使用されない理由など、他の実行プランが選択されない理由を表示することはできません。このため、MySQLはオプティマイザトレース機能を提供し、SQL文実行の分析、最適化、選択プロセスのすべてをより詳細に表示します。
オプティマイザトレースは、特定の照会プランが選択された理由をより深く理解したい場合に便利です。EXPLAINが選択されたプランを表示するのに対して、Optimizer Traceはなぜそのプランが選択されたかを表示します。この投稿では、Optimizer Traceで表示される全ての関連情報を詳細に説明し、いくつかの具体的な使用例を示します。
コストに基づく実施計画
オプティマイザトレースに入る前に、MySQL がどのようにして多くの実行計画を選択するのかを学びましょう。
MySQL は実行プランの選択にコストベースのオプティマイザを使用します。各実行プランのコストは、そのプランのクエリに必要なリソースを大まかに反映したもので、主な要因はクエリの計算時にアクセスされる行数です。オプティマイザは、主にストレージエンジンから取得したデータの統計情報とデータ辞書のメタデータ情報に基づいて判断します。フルテーブルスキャンを使用するか、特定のインデックスを使用したスキャンを使用するかを決定し、テーブル結合の順序も決定します。オプティマイザの役割を下図に示します。
オプティマイザは各操作に、ディスクからランダムな1ページのデータを読み出すコ ストの基本単位または最小単位でコストをラベル付けし、他のすべての操作に はその倍数のコストをかけます。そのため、オプティマイザは各実行プランの全操作に基づく総コストを計算することができ、多数の実行プランの中から、最終的に実行するコストが最小のものを選択します。
タグ付けコストは統計に基づいているため、全体像を正しく反映していないサンプルが常に存在し、これが MySQL オプティマイザが誤った最適化を行うことがある主な理由の 1 つです。
Optimizer Trace 基本的な
まず第一に、オプティマイザトレースを使用する方法を見てみましょう、デフォルトでは、この関数は、オフになって、次の方法を使用することができます関数をオンにし、SQLステートメントを分析し、INFORMATION_SCHEMAのOPTIMIZER_TRACEからSQLステートメントの最適化に関する関連情報を見つけるために自分の必要性を実行します。INFORMATION_SCHEMAのOPTIMIZER_TRACE。
# 1. オプティマイザーのトレース機能をオンにする:
SET optimizer_trace="enabled=on";
SELECT ...; # ここに自分のクエリーステートメントを入力する
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# ステートメントの最適化プロセスを見なくなったら、オプティマイザ・トレース機能をオフにする
SET optimizer_trace="enabled=off";
このOPTIMIZER_TRACEテーブルには、以下のように4つのカラムがあります：
- QUERY: クエリ文。
- TRACE: 最適化プロセスを示す JSON 形式のテキスト。
MISSING_BYTES_BEYOND_MAX_MEM_SIZEこのフィールドは、無視されるテキストのバイト数を示します。INSUFFICIENT_PRIVILEGESデフォルト値は0ですが、特別な場合のみ、今はこのフィールドの値を気にしないでください。
この中で最も有益で重要なのは、2番目のTRACE列で、この列はこの後の分析の焦点でもあります。
TRACE カラムの基本フォーマット
TRACEカラムの内容は超大規模なJSONデータで、直接展開し、1つずつパースして推定すると、男の脳が痛むのがわかります。
それでは、この大きなJSONの山の骨格を見てみましょう。これはSQLステートメント処理の3つのフェーズ、すなわち準備フェーズ、最適化フェーズ、実行フェーズを表しています。
次に、ケーススタディが詳しく紹介され、具体的な分野とその意味が説明されます。
なぜクエリはインデックスではなく、フルテーブルスキャンなのですか？
まず第一に、SQL文クエリがインデックスを使用しない多くのケースがあります。ここでは、コストベースのオプティマイザにより、フルテーブルクエリの実行プランがインデックスウォークの実行プランよりもコストが低いと考えられるケースについてのみ説明します。
以下のシナリオでは、明らかにvalカラムにインデックスがあり、valの既存の値にはいくつかの変動性があります。
上記のようにオプティマイザトレースを使用して、join_optimizationのrange_analysisデータを検索します。
上の図からわかるように、MySQLはフルテーブルスキャンのコストとvalをインデックスとして使用した場合のコストを比較し、フルテーブルスキャンの方がスキャンする行数は多いものの、コストは低いことを発見しました。したがって、フルテーブルスキャンの実装が選択されます。
なぜでしょうか？valインデックスを使用した方が4行少なくスキャンできることは明らかです。これは、InnoDBにおけるインデックスを使用したデータ行のクエリの原則に関連しています。
シナリオ内のInnodbエンジンのクエリのレコードは、インデックスの使用によってカバーすることはできません、あなたは、主キーを見つけるためにインデックスを介して、と言うことです必要なフィールドのレコードを取得するテーブルに戻って行う必要がありますし、データの行をチェックするために、対応する列を取り出すには、必然的に高価になります。
そのため、テーブルに戻るデータ量が比較的大きい場合、Mysqlがテーブルに戻る操作のクエリコストを推定すると、コストがかかりすぎるため、インデックスを使用しないという結果になることがよくあります。
一般的に、SQL文がテーブルの行のおよそ5分の1以上を照会し、オーバーライドインデックスを使用できない場合、テーブルにインデックスを戻すコストが大きすぎるため、フルテーブルスキャンが選択されます。そして、この比率は、1行のレコードのバイトサイズが大きくなるにつれてわずかに増加します。
range_analysisのデータでは、複数のインデックス付きカラムを使用するwhere句が、実行時にどのインデックスを使用するかをどのように選択するかを分析することもできます。
Jr.
最後に、MySQLステートメント実行解析のためのexplainとOptimizer Traceを紹介しましたが、次回は具体的なデッドロックシナリオを解析します。
