MySQLクエリー分析(explain)
インデックスを作成するデータ列が不明な場合は、EXPLAIN SELECTコマンドからヘルプを得ることができます.これは、普通のSELECTコマンドにEXPLAINキーワードを接頭辞として追加するだけです.このキーワードがあれば、MySQLはそのSELECTコマンドを実行するのではなく、分析します.MySQLでは、クエリーの実行プロセスや使用したインデックス(あれば)などの情報をテーブル形式で列挙します.ここで私は基本的に各情報フィールドの意味を述べて、説明を展開しないで、私達はいくつかの肝心な点(肝心な点以下は赤色で太く表示します)に注意して大体理解することができますほほほ~~
例
id
SQLが実行する順序のID.sqlは内側から外側に実行され,以上の観察からsqlはidに従って大きいから小さいまで実行されることが分かった.
select_type
クエリタイプ1,
table
表の名前.実際のテーブル名ではなく、derivedxが表示される場合があります(xは数字で、私の理解はいくつかのステップで実行された結果です).
type
接続操作のタイプ.この列は、接続にどのカテゴリが使用されているか、インデックスが使用されているかを示す重要な列です.様々なタイプの関連関係の中で、最も効率的なのはsystemで、次いでconst、eq_の順です.ref、ref、range、index、All.一般的に、クエリが少なくともrangeレベルに達することを保証する必要があります.refに達することが望ましいです.そうしないと、パフォーマンスの問題が発生する可能性があります.
Systemテーブルには1行しかありません:systemテーブル.これはconst接続タイプの特殊な状況です.
constテーブルのレコードの最大値は、このクエリ(インデックスはプライマリ・キーまたはインデックスのみ)に一致します.1行しかないので、この値は実際に定数です.MYSQLはまずこの値を読んでから定数として扱うからです.
eq_ref接続では、MYSQLがクエリー時に、前のテーブルから各レコードの結合に対してテーブルからレコードを読み出し、クエリーでプライマリ・キーまたはユニーク・キーのすべてをインデックスで使用する場合に使用します.
refという接続タイプは、クエリが唯一またはプライマリ・キーではないキーまたはこれらのタイプの部分(例えば、一番左の接頭辞を使用する)を使用した場合にのみ発生します.前のテーブルの各ローの結合について、すべてのレコードがテーブルから読み出されます.このタイプは、インデックスに基づいて一致するレコードの数に大きく依存します(少ないほど良いです).
rangeこの接続タイプは、インデックスを使用して、>または
Indexという接続タイプは、前のテーブルの各レコードを組み合わせて完全にスキャンします(インデックスは一般的にテーブルデータより小さいため、ALLよりも優れています).
allという接続タイプは、前の各レコードを組み合わせて完全にスキャンします.これは一般的に悪いので、できるだけ避けるべきです.テーブル全体をスキャンするからです.この問題を解決するために、より多くのインデックスを追加することができます.
possible_key
MySQLがデータレコードを検索するときに選択できるインデックス名.ここでのインデックス名は、インデックスの作成時に指定されたインデックスニックネームです.インデックスにニックネームがない場合、デフォルトではインデックスの最初のカラムの名前が表示されます(前節で挙げた例ではfirstname).デフォルトのインデックス名の意味は明らかではありません.
key
MySQLで実際に使用されているインデックスが表示されます.keyデータ列は、MySQLが実際に選択したインデックスであり、空(またはNULL)の場合、MySQLはインデックスを使用しません.
key_len
インデックスで使用される部分の長さは、バイト単位です.key_lenの値は、コンビネーションインデックスでmysqlが本当にどのインデックスを使用しているかを示します.前例ではkey_lenは102であり、firstnameは50バイト、lastnameは50バイト、ageは2バイト(smallintストレージサイズは2バイト)である.MySQLがインデックスのfirstnameセクションのみを使用する場合、key_lenは50になります.精度を損なわない場合key_lenデータ列の値は小さいほど良い(より速いという意味).
ref
キーとともにテーブルから行を選択する列または定数を表示します.refデータ列は、関連関係の別のデータテーブルのデータ列の名前を与えます.
rows
MySQLは、正しい結果が見つかる前にスキャンしなければならないレコードの数を考えています.明らかに、ここで最も理想的な数字は1です.
extra
付加情報Using indexとUsing whereは比較的に多く出会うことができて、重点的に覚えておくことができて、その他の私はあまり出会ったことがなくて理解することができて、具体的な問題に出会って調べることができます
Distinctは、MYSQLが行に一致する行を見つけたら、検索しません.
Not exists MYSQLはLEFTJOINを最適化し、LEFTJOIN規格に一致する行を見つけたら検索しません.
Range checked for eachでは理想的なインデックスが見つかりません.そのため、MYSQLは、前のテーブルからの各ローの組合せについて、どのインデックスを使用するかをチェックし、テーブルからローを返します.インデックスを使用する最も遅い接続の1つです
Using filesortがこれを見たとき、クエリーは最適化する必要があります.MYSQLは、返されたローをどのようにソートするかを発見するために追加のステップが必要です.接続タイプとソートキー値と一致条件を格納するすべての行の行ポインタに基づいて、すべての行をソートします.
Using indexカラムデータは、インデックス内の情報のみを使用して実際の行動を読み取らなかったテーブルから返されます.これは、テーブルのすべての要求カラムが同じインデックスの部分である場合に発生します.
Using temporaryがこれを見たとき、クエリは最適化する必要があります.ここで、MYSQLは、結果を格納するために一時テーブルを作成する必要があります.これは、通常、GROUP BYではなく、異なるカラムセットに対してORDER BY上で発生します.
Using whereは、WHERE従文を使用して、次のテーブルに一致するローまたはユーザーに返されるローを制限します.テーブル内のすべてのローを返したくない場合、接続タイプALLまたはindexが発生したり、クエリに問題が発生したりします.
例
explain select user_id, avg(score) from t_user where enabled = 1 group by user_id having ave(score) > 60;
+------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where |
| 2 | UNION | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where |
| NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
id
SQLが実行する順序のID.sqlは内側から外側に実行され,以上の観察からsqlはidに従って大きいから小さいまで実行されることが分かった.
select_type
クエリタイプ1,
SIMPLE:UNIONやサブクエリなどを使用しない2,PRIMARY:最外層のselect 3,DERIVED:派生テーブルのSELECT(FRサブクエリ)4,UNION:UNIONの2番目または後のSELECT文5,UNION RESULT:UNIONの結果.6、DEPENDENT UNION:UNIONの2番目またはそれ以降のSELECT文は、外部のクエリ7、SUBQUERY:サブクエリの1番目のSELECT 8、DEPENDENT SUBQUERY:サブクエリの1番目のSELECTに依存し、外部のクエリに依存するtable
表の名前.実際のテーブル名ではなく、derivedxが表示される場合があります(xは数字で、私の理解はいくつかのステップで実行された結果です).
type
接続操作のタイプ.この列は、接続にどのカテゴリが使用されているか、インデックスが使用されているかを示す重要な列です.様々なタイプの関連関係の中で、最も効率的なのはsystemで、次いでconst、eq_の順です.ref、ref、range、index、All.一般的に、クエリが少なくともrangeレベルに達することを保証する必要があります.refに達することが望ましいです.そうしないと、パフォーマンスの問題が発生する可能性があります.
Systemテーブルには1行しかありません:systemテーブル.これはconst接続タイプの特殊な状況です.
constテーブルのレコードの最大値は、このクエリ(インデックスはプライマリ・キーまたはインデックスのみ)に一致します.1行しかないので、この値は実際に定数です.MYSQLはまずこの値を読んでから定数として扱うからです.
eq_ref接続では、MYSQLがクエリー時に、前のテーブルから各レコードの結合に対してテーブルからレコードを読み出し、クエリーでプライマリ・キーまたはユニーク・キーのすべてをインデックスで使用する場合に使用します.
refという接続タイプは、クエリが唯一またはプライマリ・キーではないキーまたはこれらのタイプの部分(例えば、一番左の接頭辞を使用する)を使用した場合にのみ発生します.前のテーブルの各ローの結合について、すべてのレコードがテーブルから読み出されます.このタイプは、インデックスに基づいて一致するレコードの数に大きく依存します(少ないほど良いです).
rangeこの接続タイプは、インデックスを使用して、>または
Indexという接続タイプは、前のテーブルの各レコードを組み合わせて完全にスキャンします(インデックスは一般的にテーブルデータより小さいため、ALLよりも優れています).
allという接続タイプは、前の各レコードを組み合わせて完全にスキャンします.これは一般的に悪いので、できるだけ避けるべきです.テーブル全体をスキャンするからです.この問題を解決するために、より多くのインデックスを追加することができます.
possible_key
MySQLがデータレコードを検索するときに選択できるインデックス名.ここでのインデックス名は、インデックスの作成時に指定されたインデックスニックネームです.インデックスにニックネームがない場合、デフォルトではインデックスの最初のカラムの名前が表示されます(前節で挙げた例ではfirstname).デフォルトのインデックス名の意味は明らかではありません.
key
MySQLで実際に使用されているインデックスが表示されます.keyデータ列は、MySQLが実際に選択したインデックスであり、空(またはNULL)の場合、MySQLはインデックスを使用しません.
key_len
インデックスで使用される部分の長さは、バイト単位です.key_lenの値は、コンビネーションインデックスでmysqlが本当にどのインデックスを使用しているかを示します.前例ではkey_lenは102であり、firstnameは50バイト、lastnameは50バイト、ageは2バイト(smallintストレージサイズは2バイト)である.MySQLがインデックスのfirstnameセクションのみを使用する場合、key_lenは50になります.精度を損なわない場合key_lenデータ列の値は小さいほど良い(より速いという意味).
ref
キーとともにテーブルから行を選択する列または定数を表示します.refデータ列は、関連関係の別のデータテーブルのデータ列の名前を与えます.
rows
MySQLは、正しい結果が見つかる前にスキャンしなければならないレコードの数を考えています.明らかに、ここで最も理想的な数字は1です.
extra
付加情報Using indexとUsing whereは比較的に多く出会うことができて、重点的に覚えておくことができて、その他の私はあまり出会ったことがなくて理解することができて、具体的な問題に出会って調べることができます
Distinctは、MYSQLが行に一致する行を見つけたら、検索しません.
Not exists MYSQLはLEFTJOINを最適化し、LEFTJOIN規格に一致する行を見つけたら検索しません.
Range checked for eachでは理想的なインデックスが見つかりません.そのため、MYSQLは、前のテーブルからの各ローの組合せについて、どのインデックスを使用するかをチェックし、テーブルからローを返します.インデックスを使用する最も遅い接続の1つです
Using filesortがこれを見たとき、クエリーは最適化する必要があります.MYSQLは、返されたローをどのようにソートするかを発見するために追加のステップが必要です.接続タイプとソートキー値と一致条件を格納するすべての行の行ポインタに基づいて、すべての行をソートします.
Using indexカラムデータは、インデックス内の情報のみを使用して実際の行動を読み取らなかったテーブルから返されます.これは、テーブルのすべての要求カラムが同じインデックスの部分である場合に発生します.
Using temporaryがこれを見たとき、クエリは最適化する必要があります.ここで、MYSQLは、結果を格納するために一時テーブルを作成する必要があります.これは、通常、GROUP BYではなく、異なるカラムセットに対してORDER BY上で発生します.
Using whereは、WHERE従文を使用して、次のテーブルに一致するローまたはユーザーに返されるローを制限します.テーブル内のすべてのローを返したくない場合、接続タイプALLまたはindexが発生したり、クエリに問題が発生したりします.