Explainキーワードに基づいてMySQLインデックス機能を最適化する方法

EXPLAIN は、MySQL がインデックスを使用して選択ステートメントを処理し、テーブルを結合する方法を示します。より適切なインデックスを選択し、より最適化されたクエリステートメントを記述するのに役立ちます。簡単に言えば、その機能はクエリのパフォーマンスを分析することです。

explain キーワードの使い方は非常に簡単で、select クエリステートメントの前に置くだけです。

MySQL がインデックスを使用するかどうかを確認するには、タイプを確認するだけです。 all の場合、このクエリステートメントはすべての行を走査し、インデックスを使用しないことを意味します。

たとえば、explain select * from company_info where cname like '%小%' と記述します。

select * from company_info where cname like '小%' の説明

1) id 列の数字が大きいほど、先に実行されます。数字が同じ場合は、上から下に実行されます。id 列が null の場合、これは結果セットであり、クエリに使用する必要がないことを意味します。

2) 一般的な select_type 列には次のものがあります。

A: simple: は、結合操作を必要としないか、サブクエリを含まない単純な選択クエリを示します。結合クエリがある場合、外部クエリは単純で、
B: primary: 結合演算を必要とするか、サブクエリを含む選択。最も外側の単位クエリの select_type は primary です。そしてただ一つ
C: ユニオン: ユニオンで接続された 2 つの選択クエリ。最初のクエリは派生テーブルです。最初のテーブルを除き、2 番目以降のテーブルの select_type はユニオンです。
D: 従属ユニオン: ユニオンと同様に、ユニオンまたはユニオンオールステートメントに表示されますが、このクエリは外部クエリの影響を受けます。
E: ユニオン結果: ユニオンの結果セットが含まれます。ユニオンおよびユニオンすべてのステートメントでは、クエリに参加する必要がないため、ID フィールドは null になります。
F: サブクエリ: from 句に含まれるサブクエリを除き、他の場所に出現するサブクエリはサブクエリになる場合があります。
G: 従属サブクエリ: 従属結合と同様に、このサブクエリのクエリが外部テーブルのクエリの影響を受けることを意味します。
H: 派生: from 句に現れるサブクエリは派生テーブルとも呼ばれます。他のデータベースでは、インラインビューまたはネストされた選択と呼ばれることもあります。

3) テーブル

クエリテーブル名が表示されます。クエリがエイリアスを使用する場合、エイリアスがここに表示されます。データテーブルに対する操作が含まれない場合は、null として表示されます。山括弧で囲まれた <derived N> として表示される場合は、これが一時テーブルであることを意味します。その後ろの N は実行プランの ID であり、結果がこのクエリから取得されることを示します。山括弧で囲まれた <union M,N> の場合は、<derived N> と似ており、これも一時テーブルであり、この結果が ID M,N のユニオンクエリの結果セットから取得されたことを示します。

4) タイプ

最良から最悪まで: system、const、eq_ref、ref、fulltext、ref_or_null、unique_subquery、index_subquery、range、index_merge、index、ALL。all を除く他のすべてのタイプはインデックスを使用できます。index_merge を除く他のタイプは 1 つのインデックスのみを使用できます。

A: システム: テーブルには 1 行のデータのみが含まれているか、空のテーブルであり、MyISAM テーブルと Memory テーブルにのみ使用できます。 Innodbエンジンテーブルの場合、この場合の型列は通常allまたはindexです。
B: const: 一意のインデックスまたは主キーを使用し、返されるレコードが where 条件内の 1 行のレコードと等しくなければならない場合、型は通常 const になります。他のデータベースではユニークインデックススキャンとも呼ばれる
C: eq_ref: 2 つのテーブルに接続するためのクエリプランに表示されます。駆動テーブルは 1 行のデータのみを返します。この行のデータは 2 番目のテーブルの主キーまたは一意のインデックスであり、null であってはなりません。一意のインデックスと主キーが複数の列である場合、すべての列が比較に使用される場合にのみ eq_ref が表示されます。
D: ref: eq_ref とは異なり、接続順序は必要なく、主キーや一意のインデックス要件もありません。等しい条件で検索するときに表示され、補助インデックスを使用した等しい値の検索によく使用されます。また、複数列の主キーと一意のインデックスでは、最初の列以外の列を等値検索として使用することもあります。つまり、一意でないデータを返す等値検索が発生する可能性があります。
E: fulltext: 全文インデックスの取得。全文インデックスの優先度が高いことに注意してください。全文インデックスと通常のインデックスが同時に存在する場合、MySQL はコストに関係なく全文インデックスを優先します。
F: ref_or_null: ref メソッドと似ていますが、null 値の比較が追加されています。実際にはあまり使用されていません。
G: unique_subquery: where内のフォーム内サブクエリに使用され、サブクエリは重複のない一意の値を返します。
H: index_subquery: 補助インデックスを使用するフォーム内サブクエリまたは定数リストで使用されます。サブクエリは重複した値を返す可能性があり、インデックスを使用してサブクエリの重複を排除できます。
I: 範囲: インデックス範囲スキャン。>、<、is null、between、in、like などの演算子を使用するクエリでよく使用されます。
J: index_merge: クエリが 2 つ以上のインデックスを使用し、最終的に交差または結合を取ることを示します。一般的な and or 条件では、異なるインデックスが使用されます。公式の順位は ref_or_null の後ですが、実際にはすべてのインデックスを読み取る必要があるため、ほとんどの場合、パフォーマンスは range ほど良くない可能性があります。
K: インデックス: インデックスの完全なテーブルスキャン。インデックスを最初から最後までスキャンします。データファイルの読み取りを必要としないクエリや、インデックスの並べ替えやグループ化を使用できるクエリを処理するには、インデックス列を使用するのが一般的です。
L: all: テーブル全体のデータファイルをスキャンし、サーバーレベルでフィルター処理して、要件を満たすレコードを返します。

5)、可能なキー

クエリで使用される可能性のあるすべてのインデックスがここにリストされます

6) キー

実際に使用されているインデックスを照会します。select_type が index_merge の場合、ここには 2 つ以上のインデックスが表示されることがあります。その他の select_types の場合、ここには 1 つのインデックスのみが表示されます。

7) キーの長さ

クエリの処理に使用されるインデックスの長さ。単一列インデックスの場合は、インデックス全体の長さが含まれます。複数列インデックスの場合は、クエリですべての列が使用されない可能性があります。使用される列の具体的な数はここで計算されます。使用されない列はここでは計算されません。この列の値に注意し、複数列インデックスの合計長を計算して、すべての列が使用されているかどうかを確認します。 MySQL の ICP 機能で使用されるインデックスはカウントされないことに注意してください。また、key_len は where 条件で使用されるインデックスの長さのみを計算し、インデックスがソートやグループ化に使用されている場合でも、key_len では計算されません。

8) 参照

クエリが定数等値クエリの場合、ここに const が表示されます。結合クエリの場合、駆動テーブルの実行プランには、駆動テーブルの関連フィールドが表示されます。条件で式または関数が使用されている場合、または条件列に内部暗黙的な変換がある場合は、func として表示される場合があります。

9) 行

これは実行プラン内のスキャン行の推定数であり、正確な値ではありません。

10) 追加

この列には多くの情報を表示できます。数十種類ありますが、よく使われるものは次のとおりです。

A: distinctive: distinctiveキーワードはselect部分で使用されます
B: テーブルは使用されません: from 句のないクエリまたはデュアルクエリ
C: not in() 形式のサブクエリまたは not exists 演算子を使用する結合クエリは、アンチ結合と呼ばれます。つまり、一般的な結合クエリは最初に内部テーブルをクエリし、次に外部テーブルをクエリしますが、反結合は最初に外部テーブルをクエリし、次に内部テーブルをクエリします。
D: filesort の使用: これは、ソート中にインデックスを使用できない場合に発生します。 order by および group by ステートメントでよく使用されます
E: インデックスの使用: クエリを実行するときに、クエリのためにテーブルに戻る必要はありません。クエリされたデータは、インデックスを通じて直接取得できます。
F: 結合バッファ (ブロックネストループ) の使用、結合バッファ (バッチキーアクセス) の使用: 5.6.x 以降のバージョンでは、関連するクエリの BNL および BKA 機能が最適化されます。主な目的は、内部テーブル内のループ数を減らし、順次スキャンクエリを比較することです。
G: sort_union を使用、using_union、intersect を使用、sort_intersection を使用:
交差の使用：およびの各インデックスを使用する条件を示し、この情報は、処理結果から交差が取得されることを示します。
ユニオンの使用: またはを使用したインデックスを使用する条件を接続するときに、処理結果からユニオンが取得されることを示します。
sort_union の使用と sort_intersection の使用: 前の 2 つと似ていますが、大量の情報を照会するために and と or を使用するときに表示されます。最初に主キーが照会され、次にソートとマージが実行されてから、レコードが読み取られて返されます。
H: 一時を使用: 中間結果を格納するために一時テーブルが使用されることを示します。一時テーブルには、メモリ一時テーブルとディスク一時テーブルがあります。これらは実行プランでは確認できず、ステータス変数 used_tmp_table および used_tmp_disk_table をチェックすることによってのみ確認できます。
I: where: を使用すると、ストレージエンジンによって返されるすべてのレコードがクエリ条件を満たすわけではなく、サーバーレベルでフィルター処理する必要があることを示します。クエリ条件は、制限条件とチェック条件に分かれています。5.6 より前は、ストレージエンジンは制限条件に基づいてデータをスキャンして返すことしかできず、その後、サーバーレイヤーはチェック条件に基づいてフィルター処理し、実際にクエリを満たしたデータを返していました。 5.6.x 以降のバージョンでは、チェック条件をストレージエンジンレイヤーにプッシュできる ICP 機能がサポートされています。チェック条件と制限条件を満たさないデータは直接読み取られないため、ストレージエンジンによってスキャンされるレコードの数が大幅に削減されます。追加の列にはインデックス条件の使用が表示されます
J: firstmatch(tb_name): 5.6.x で導入されたサブクエリを最適化するための新機能の 1 つで、in() タイプのサブクエリを含む where 句でよく見られます。内部テーブルのデータ量が多い場合、これが発生することがあります
K: losescan(m..n): 5.6.x 以降に導入されたサブクエリを最適化するための新機能の 1 つです。これは、サブクエリが in() タイプのサブクエリで重複レコードを返す場合に発生する可能性があります。

これら以外にも、クエリデータ辞書ライブラリは多数存在し、実行プラン中に結果を取得できないプロンプトメッセージもいくつか見つかります。

11）、フィルタリング

この列は、explain Extended が使用されている場合に表示されます。バージョン 5.7 以降ではこのフィールドがデフォルトで存在するため、explain Extended は不要になりました。このフィールドは、ストレージエンジンによって返されたデータがサーバーレベルでフィルター処理された後に、クエリを満たすレコード数の比率を示します。これはパーセンテージであり、特定のレコード数ではないことに注意してください。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

以下もご興味があるかもしれません: