mysql explain（分析インデックス）の使い方の詳しい説明

EXPLAIN は、MySQL がインデックスを使用して選択ステートメントを処理し、テーブルを結合する方法を示します。より適切なインデックスを選択し、より最適化されたクエリ ステートメントを記述するのに役立ちます。

これを使用するには、次のように、select ステートメントの前に explain を追加するだけです。

select * from statuses_status where id=11; を説明します。

説明欄の説明

テーブル: この行のデータがどのテーブルに関するものかを表示します

type: これは重要な列であり、使用されている接続のタイプを示します。結合タイプは、最良から最悪までconst、eq_reg、ref、range、indexhe、およびallです。

possible_keys: このテーブルに適用できるインデックスを表示します。空の場合、インデックスは作成できません。関連するドメインのwhere句から適切なステートメントを選択できます。

key: 実際に使用されるインデックス。 null の場合、インデックスは使用されません。まれに、MySQL が十分に最適ではないインデックスを選択する場合があります。この場合、select ステートメントで use index (indexname) を使用してインデックスの使用を強制したり、 ignore index (indexname) を使用して MySQL にインデックスを無視させたりすることができます。

key_len: 使用されるインデックスの長さ。長さが短いほど、精度を失わずに良くなります。

ref: インデックスのどの列が使用されているかを示します（可能な場合は定数）。

行数: 要求されたデータを返すためにMySQLがチェックする必要があると考える行数

extra: MySQL がクエリを解析する方法に関する追加情報。これについては表 4.3 で説明しますが、ここで確認できる悪い例は、temporary の使用と filesort の使用です。つまり、MySQL はインデックスをまったく使用できず、結果として取得が非常に遅くなります。

追加列に返される説明の意味

Distinct: MySQL は結合内の行と一致する行を見つけると、それ以上検索を行いません。

存在しない: MySQL は左結合を最適化します。左結合の条件に一致する行が見つかると、それ以上検索は行われません。

各レコードの範囲がチェックされます (インデックス マップ: #): 理想的なインデックスが見つからなかったため、MySQL は前のテーブルの行の組み合わせごとに、使用するインデックスをチェックし、それを使用してテーブルから行を返します。これはインデックスを使用する最も遅い接続の1つです

filesort の使用: これが表示される場合は、クエリを最適化する必要があります。 MySQL では、返される行をどのように順序付けるかを検出するために追加の手順を実行する必要があります。結合タイプに基づいてすべての行をソートし、条件に一致するすべての行のソートキー値と行ポインターを保存します。

インデックスの使用: 列データは、実際に読み取ることなく、インデックスの情報のみを使用してテーブルから返されます。これは、テーブルに対して要求されたすべての列が同じインデックスの一部である場合に発生します。

一時的な使用 このメッセージが表示される場合は、クエリを最適化する必要があります。ここで、MySQL は結果を格納するための一時テーブルを作成する必要があります。これは通常、group by ではなく、異なる列セットで order by を実行した場合に発生します。

where 句は、次のテーブルと一致する行やユーザーに返される行を制限するために使用されます。これは、テーブル内のすべての行を返す必要がなく、結合タイプが all または index の場合、またはクエリに問題がある場合に発生する可能性があります。さまざまな結合タイプの説明 (効率の順に並べ替え)

システム テーブルには、システム テーブルという 1 つの行のみが含まれます。これはconst接続型の特別なケースです

const: このクエリに一致するテーブル内のレコードの最大値 (インデックスは主キーまたは一意のインデックスにすることができます)。行が 1 つしかないため、MySQL は最初に値を読み取ってからそれを定数として扱うため、この値は実際には定数です。

eq_ref: 接続時に、MySQL はクエリ内の各レコードに対して前のテーブルからレコードを読み取ります。クエリが主キーまたは一意キーのインデックスを使用する場合に使用されます。

ref: この結合タイプは、クエリが一意または主キーではないキー、またはこれらのタイプのいずれかの一部 (たとえば、左端のプレフィックスを使用) を使用する場合にのみ発生します。前のテーブルとの各行の結合ごとに、すべてのレコードがテーブルから読み取られます。このタイプは、インデックスと一致するレコードの数に大きく依存します。少ないほど良いです。

範囲: この結合タイプは、インデックスを使用して行の範囲を返します。たとえば、何かを検索するために > または < を使用した場合などです。

インデックス: この結合タイプは、前のテーブルのすべてのレコードの完全スキャンを実行します (インデックスは一般にテーブルデータよりも小さいため、すべてよりも優れています)

all: この結合タイプは、以前の各レコードの完全スキャンを実行します。これは通常、好ましくないため、避ける必要があります。

分析インデックス分析

ここでは、記述した SQL がインデックスを最適に使用しているかどうかを分析できるように、explain の各フィールドを詳細に分析します。

まずselect_type: 選択クエリは単純なタイプと複雑なタイプに分かれています

複合型はサブクエリ（サブクエリ）とサブクエリを含むリスト（ドリブン）に分けられます。

単純：

駆動:

タイプに関する詳細な紹介:

システム、const、eq_ref、ref、範囲、インデックス、すべて
all : テーブル全体のスキャン
index : インデックス順にスキャンし、最初にインデックスを読み取り、次に実際の行を読み取ります。結果は完全なテーブルスキャンのままですが、主な利点はソートを回避できることです。インデックスがソートされているからです。
range : 範囲形式でスキャンします。

説明 select * from a where a_id > 1\G

ref : 非一意インデックスアクセス (通常のインデックスのみ)

テーブル a(a_id int not null、キー(a_id)) を作成します。
値(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)を挿入します。
mysql> explain select * from a where a_id=1\G

eq_ref : 一意のインデックス検索を使用する (主キーまたは一意のインデックス)
const : 定数クエリ

クエリ プロセス全体を通じて、このテーブルには最大で 1 つの一致する行があります。たとえば、主キー id=1 の場合、必ず 1 行だけになります。必要な結果は、テーブル データを 1 回だけ読み取ることで取得でき、テーブル データは実行プランが分解されるときに読み取られます。

結果が1でない場合は、インデックスや範囲などの他の型になります。

system : システムクエリ

null : 最適化プロセス中に結果が取得され、テーブルまたはインデックスにはアクセスされません。

possible_keys : 使用できるインデックス

key : 実際に使用されたインデックス

key_line : インデックスフィールドの最大可能長

ref :

キー列に選択されたインデックスの検索方法を示します。一般的な値には、const、func、NULL、特定のフィールド名などがあります。キー列が NULL の場合、つまりインデックスが使用されていない場合は、この値もそれに応じて NULL になります。

rows : スキャンする行数の推定値

Extra : 上記の情報に加えて追加情報を表示します

インデックスの使用

このクエリはカバーリング インデックスを使用します。つまり、テーブルにアクセスしなくても、インデックスを通じて結果を返すことができます。

「インデックスを使用」が表示されない場合は、テーブルデータが読み取られていることを意味します。

where の使用

MySQL サーバーがストレージ エンジンから行を受け取った後に「ポスト フィルタリング」を実行することを示します。いわゆる「ポストフィルタリング」とは、まずデータ行全体を読み取り、次にこの行が where 句の条件を満たしているかどうかをチェックすることです。条件を満たしている場合は保持され、条件を満たしていない場合は破棄されます。チェックは行が読み取られた後に行われるため、ポストフィルタリングと呼ばれます。

一時的な使用

一時テーブルの使用

テーブルを作成し、データを挿入します。

テーブル a(a_id int, b_id int) を作成します。
値(1,1),(1,1),(2,1),(2,2),(3,1)を挿入します。
mysql> explain 選択の区別 a_id から a\G

Extra ：一時的な使用

MySQL は、個別の操作を実装するために一時テーブルを使用します。

ファイルソートの使用

クエリで要求される順序が使用されるインデックスの順序と一致している場合は、インデックスがソートされているため、結果はインデックスの順序で読み込まれ、返されます。そうでない場合は、結果を取得した後、クエリで要求される順序でソートする必要があります。このとき、Using filesort が表示されます。

ID による順序で * を選択します。

インデックスのない列の場合、order byを実行するとfilesortが表示されます。