MySQL explain実行計画解読
引用:
実際のプロジェクト開発では、実際にクエリーをしたときにデータベースで何が起こったのか、データベースソフトウェアがテーブルをどのようにスキャンし、インデックスをどのように使用しているのか分からないため、私たちが感じることができるのは
sql文が実行される時間は,データ規模が大きくない場合,クエリは瞬時であるため,sql文を書く際に性能の問題を考慮することは少ない.しかし、千万、億などのデータ規模が大きくなると、同じsql文を実行しても結果が出ないことに気づき、データ規模がクエリーの速度を制限していることがわかりました.だから、
も重要です.
質問:
クエリーの前に、クエリーがどのローに関連するのか、どのインデックスを使用するのか、実行時間を事前に見積もることができますか?答えは可能で、mysqlは対応する機能と文法を提供してこの機能を実現します.
分析:
MySqlはEXPLAIN構文を提供してクエリー分析を行い、SQL文の前に「EXPLAIN」を追加すればよい.たとえば、次のSQL文を分析します.
explain select * from table where table.id = 1
上のsql文を実行すると、次のヘッダー情報が表示されます.
table | type | possible_keys | key | key_len | ref | rows | Extra
EXPLAIN列の解釈
tableこの行のデータがどのテーブルに関するであるかを示す.
typeこれは重要な列で、接続がどのタイプを使用しているかを示します.最良から最悪までの接続タイプはconst、eq_reg、ref、range、indexhe、ALLの説明:異なる接続タイプの解釈(効率の高低の順にソート) system:テーブルは1行のみ:systemテーブル.これはconst接続タイプの特殊な状況です. const:テーブル内のレコードの最大値は、このクエリに一致します(インデックスはプライマリ・キーまたはインデックスのみ).1行しかないので、この値は実際に定数です.MYSQLはまずこの値を読んでから定数として扱っているからです. eq_ref:接続では、MYSQLはクエリー時に、前のテーブルから各レコードの結合に対してテーブルからレコードを読み出し、クエリーでプライマリ・キーまたは唯一のキーのすべてをインデックスで使用した場合に使用します. ref:この接続タイプは、クエリが唯一またはプライマリ・キーではないキーまたはこれらのタイプの部分(例えば、一番左のプレフィックスを使用する)を使用した場合にのみ発生します.前のテーブルの各ローの結合について、すべてのレコードがテーブルから読み出されます.このタイプは、インデックスに一致するレコードの数に大きく依存します.少ないほど良いです. range:この接続タイプは、インデックスを使用して、>または
index:この接続タイプは、インデックスが一般的にテーブルデータより小さいため、前のテーブルの各レコードを組み合わせて完全にスキャンします. ALL:この接続タイプは、前の各レコードを組み合わせて完全にスキャンします.これは一般的に悪いので、できるだけ避けるべきです. possible_keysは、このテーブルに適用可能なインデックスを表示します.空の場合、可能なインデックスはありません.関連するドメインに対してWHERE文から適切な文を選択することができる.
keyが実際に使用したインデックス.NULLの場合、インデックスは使用されません.少ない場合、MYSQLは最適化不足のインデックスを選択します.この場合、SELECT文でUSE INDEX(indexname)を使用してインデックスを強制的に使用するか、IGNORE INDEX(indexname)を使用してMYSQLにインデックスを無視するように強制することができる.
key_lenが使用するインデックスの長さ.精度を損なうことなく、長さが短いほどとなる.
refはインデックスのどの列が使用されているかを示し、できれば定数である.
rows MYSQLは、要求データを返すためにチェックする必要があると判断した行数 Extra MYSQLがクエリをどのように解析するかに関する追加情報.表4.3で説明するが、ここで見られる悪い例は、MYSQLがインデックスを使用できないことを意味するUsing temporaryおよびUsing filesortであり、結果として、検索が遅い説明である:extra列が返す記述の意味 Distinct:mysqlが行に一致する行を見つけたら、検索しません. Not exists:mysqlはLEFTJOINを最適化し、LEFTJOIN規格に一致する行が見つかったら検索しません. Range checked for each Record(index map:#):理想的なインデックスが見つかりません.そのため、前のテーブルからの各ローの組合せについてmysqlは、どのインデックスを使用するかをチェックし、テーブルからローを返すために使用します.インデックスを使用する最も遅い接続の1つです. Using filesort:これを見たとき、クエリーを最適化する必要があります.mysqlは、返されたローをどのようにソートするかを発見するために追加のステップが必要です.接続タイプと、ソートキー値と一致条件を格納するすべての行の行ポインタに基づいて、すべての行をソートします. Using index:カラムデータは、インデックス内の情報のみを使用して実際の行動を読み取らなかったテーブルから返されます.これは、テーブルのすべての要求列が同じインデックスの部分である場合に発生します. Using temporary:これを見たとき、クエリーを最適化する必要があります.ここでmysqlは、結果を格納するために一時テーブルを作成する必要があります.これは、通常、GROUP BYではなく、異なるカラムセットに対してORDER BY上で発生します. Where used:WHERE従文を使用して、次のテーブルに一致するローまたはユーザーに返されるローを制限します.テーブル内のすべてのローを返したくない場合、接続タイプALLまたはindexが発生したり、クエリに問題が発生したりします. したがって、explain構文が返す各結果を理解すると、クエリのほぼ実行時間がわかります.クエリにインデックスが使用されていない場合や、スキャンするローが多すぎる場合は、明らかな遅延を感じることができます.したがって、クエリーの方法を変更するか、新しいインデックスを作成する必要があります.mysqlのexplain構文は、クエリーを書き換え、テーブルの構造とインデックスの設定を最適化し、クエリーの効率を最大化するのに役立ちます.もちろん、大規模なデータ量の場合、インデックスの作成とメンテナンスのコストも高く、長い時間と大きな空間が必要になることが多く、異なるカラムの組み合わせでインデックスを作成すると、空間のオーバーヘッドが大きくなります.したがって、インデックスは頻繁にクエリーする必要があるフィールドに設定することが望ましい.