mysqlの実行計画とは何ですか-mysql チュートリアル-php.cn

mysql では、実行プランは、SQL ステートメントを解析、分析、最適化するためにデータベースからユーザーに提供される一連のツールです。実行プランの機能は、1. テーブルの読み取り順序の表示、2. データ読み取り操作の種類、3. 使用可能なインデックスの表示、4. 実際に使用されているインデックスの表示、5. テーブルの表示です。それらの間の参照関係; 6. 各テーブルでクエリされた行数を表示します。

mysqlの実行計画とは何ですか

このチュートリアルの動作環境: Windows7 システム、mysql8 バージョン、Dell G3 コンピューター。

データベースにクエリを実行するときは、通常、SQL ステートメントを使用して必要なデータをクエリします。ただし、SQL がデータベース内でどのように実行されるか、インデックスを使用するかどうか、どのインデックスが使用されるか、どのフィールドとテーブルが検索されるか、それらの順序は何か、どれくらいの時間がかかるかなどはわかりません。この情報を確認するにはどうすればよいでしょうか? MySQL は実行プランという一連のツールを提供します。

1. 実行プランとは何ですか?

実行プランは、SQL ステートメントを解析、分析、最適化するためにデータベースによって提供される一連のツールです。以下の関数機能:

テーブルの読み取り順序の表示;
データ読み取り操作の種類;
使用できるインデックス;
実際に使用されるインデックス;
テーブル間の参照関係;
各テーブルに対してクエリされた行の数。

注: 実行計画は、データベースによって SQL に対して提供される最適化の参照計画にすぎず、必ずしも最適な解決策であるとは限りません。つまり、実行計画を信頼しないでください。実行プランが多すぎる

2. 実行プランの使用方法

#実行プランの使用は非常に簡単で、キーワードの前に Explain を追加するだけです。実行されるSQL。

3. 実行計画情報

mysqlの実行計画とは何ですか

図からわかるように、SQL 実行計画には主に次の情報が含まれています。 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra。

3.1. id

#select はシリアル番号をクエリします。ID が同じ場合、実行順序は上から下になります。 idが異なる場合はid値が大きい方が優先され、レベルが高いほど早く実行されます;

#3.2, select_type

#select_type: 次の値を持つことができる選択ステートメントのタイプを示します;

SIMPLE: 接続クエリとサブクエリを含まない単純なクエリを表します;
PRIMARY: メインクエリ、または最も外側のクエリを表します。クエリステートメント;
UNION: 接続クエリの 2 番目以降のクエリステートメントを示します。 ;
DEPENDENT UNION: UNION 2 番目以降の SELECT ステートメントは外部クエリに依存します;
UNION RESULT: 接続の結果query;
SUBQUERY : サブクエリの最初の SELECT ステートメント;
DEPENDENT SUBQUERY: サブクエリの最初の SELECT ステートメントは、外部クエリ;
DERIVED: SELECT (FROM 句のサブクエリ)。

3.3、table

table: クエリテーブル名を示します。これは次の状況で使用できます。

テーブル名を表示します。別名が指定されている場合、その別名が表示されます;
: クエリ条件がa subquery;
: テーブル 1 とテーブル 2 がユニオンを使用することを示します。

3.4、パーティション

partitions: 一致するパーティション。

3.5. type

type: この列はテーブルの関連付けタイプまたはアクセスタイプを表します。つまり、データベースが行の検索方法を決定します。テーブル内で、データ行レコードのおおよその範囲を見つけます。最良から最悪の順に次のようになります: system > const > eq_ref > ref > range > gt; Index > all

system: レコードは 1 行のみです。システムテーブルの場合、これは const 型の特別な列であり、通常は表示されないため無視できます;
const: インデックスを介した 1 つのヒット、 1 行のデータを照合するため、非常に高速でよく使用されます。PRIMARY KEY または UNIQUE インデックスのクエリでは、const が最適化されていることがわかります。
eq_ref: ユニークインデックススキャン、各インデックスキーについて、テーブル内にそのレコードが 1 つだけ存在します一致する、一般的に使用される主キーまたは一意のインデックススキャン、これは const 以外に最適な結合タイプである可能性があります;
ref : 非一意のインデックススキャン、一致する単一の値を返します =、演算子のインデックス付き列のすべての行;
Range: 指定された範囲内の行のみを取得し、インデックスを使用して行を選択します。通常、 between、、in などのクエリに使用されます。この範囲クエリは、インデックスの 1 つのポイントをスキャンする必要があるだけで、別のポイントで終了するため;
index: インデックスツリーを走査する必要があります;
all: フルテーブルスキャンとは、データベースが最初から最後まで必要な行を見つける必要があることを意味します。通常、これには最適化のためにインデックスを追加する必要があります。

注: SQL を最適化するときは、少なくとも range まで最適化する必要があります。ref、できれば const まで最適化することをお勧めします。

3.6. possible_keys

possible_keys: この列は、クエリ が検索に使用できるインデックス を示します。 Explain では、 possible_keys に列があるにもかかわらず、 key に NULL が表示される状況が発生することがあります。これは、テーブルにデータがあまりなく、データベースはインデックスがこのクエリには役に立たないと判断するため、完全なインデックスが選択されるからです。テーブルクエリ。
列が NULL の場合、関連付けられたインデックスはありません。この場合、where 句をチェックして適切なインデックスを作成してクエリのパフォーマンスを向上できるかどうかを確認し、explain を使用してその効果を確認できます。

3.7, key

key: データベース で実際に使用することに決めたキー (インデックス) を表示します。インデックスが選択されていない場合、キーの値は NULL になります。インデックスは強制的に使用または無視することができます。

3.8, key_len

key_len: この列には、インデックス内のデータベースによって使用されるバイト数 が表示されます。この値は、インデックスで使用される特定のバイト数を計算するために使用できます。

String type

char(n): n バイトの長さ
varchar(n): 2 バイトの格納文字列長 (utf- の場合) 8 の場合、長さは 3n 2

numeric type

tinyint: 1 byte
smallint: 2 bytes
int: 4 bytes
bigint: 8 bytes

時刻タイプ

日付: 3 バイト
タイムスタンプ: 4 バイト
日時: 8 バイト

フィールドが NULL であることが許可されている場合、NULL かどうかを記録するために 1 バイトが必要です

注: インデックスの最大長は 768 バイトです。文字列が長すぎる場合、データベースは左側のプレフィックスインデックスと同様の処理を実行し、インデックス作成のために文字の前半を抽出します。

3.9、ref

ref: この列には、キー列レコードのインデックスで使用される

テーブルルックアップ値が表示されます。定数 、一般的なものは次のとおりです: const (定数)、func、null、フィールド名 (例: film.id)

3.10、rows

rows: この列は、読み取りおよびスキャンされるデータベースによって推定される数値

です。これは結果セット内の行数ではないため、値が小さいほど優れています。

3.11、フィルター済みフィルター済み:

結果の行数を、読み取られた行数のパーセンテージとして返します

、値が大きいほど良いです。

3.12、追加追加: この列には、他の列には含まれていない

追加情報

が表示されます。

##存在しません: データベースはクエリに対して LEFT JOIN 最適化を実行できます。LEFT JOIN 標準に一致する行が見つかった後は、前の行の組み合わせについてテーブル内の行はそれ以上チェックされません。
各レコードに対して範囲がチェックされました (インデックスマップ: #): データベースは使用できる適切なインデックスを見つけられませんでしたが、前のテーブルの列の値がわかっている場合は、部分的にインデックスである可能性があることがわかりました。
filesort を使用する (キーポイント): データベースは、インデックス順にテーブルから行を読み取るのではなく、外部インデックスを使用して結果を並べ替えます。このとき、mysql は接続タイプに従って対象となるすべてのレコードを参照し、並べ替えキーワードと行ポインターを保存してから、キーワードを並べ替えて行情報を順番に取得します。この場合、通常は
インデックスを使用して最適化する
;
インデックスの使用 (キーポイント): インデックス内の情報のみを使用することから始めるツリーテーブル内の列情報を取得するために実際の行を読み取るためにこれ以上の検索は必要ありません。つまり、select はテーブルクエリに戻ることなくカバーインデックスを使用します
;
一時的な使用 (強調): データベースは、クエリを処理するために 一時テーブルを作成する必要があります
. この状況は、order by および group by でよく見られます。この問題が発生した場合、通常は最適化が必要です。最初に行うことは、インデックスを使用して最適化することです。
using where: データベースは、ストレージエンジンが行を取得した後にフィルタリングします。つまり、最初にデータ行全体を読み取り、where 条件に従ってチェックします。一致する場合は保持され、一致しない場合は破棄されます。
using インデックス条件: where の使用と同様、クエリ列はインデックスで完全にはカバーされていません。where 条件は先頭の列の範囲です。
using sort_union(... )、union(...) の使用、intersect(.. .) の使用: これらの関数は、index_merge 結合タイプのインデックススキャンをマージする方法を示しています。
group-by にインデックスを使用: テーブルにアクセスするためのインデックスを使用する方法と同様に、group-by にインデックスを使用すると、group by またはクエリを実行するために使用できるインデックスがデータベースで見つかったことを意味します。個別のクエリ。実際のテーブルにアクセスするためにハードディスクを追加検索する代わりに、すべての列;
null: クエリされた列はインデックスの対象外であり、where フィルター条件はインデックスの先頭の列。インデックスが使用されていることを意味しますが、一部のフィールドはインデックスの対象ではないため、「テーブルリターン」を通じて実装する必要があります。インデックスは純粋に使用されるわけではなく、インデックスがまったく使用されないわけでもありません。インデックスが使用されているがテーブルを返す操作が必要な場合、テーブルを返す操作は避けてください。