mysql最適化explainメモ

今日explainを使ってsqlの実行状況を見ていると忘れてしまうものがあるので、この文章を作って知識点の記憶を強化します.
1.explainの役割
exlain実行結果はmysqlストレージエンジンがselect文をインデックスで処理する方法を示しており、より効率的なsql文を書くのに役立ち、mysqlの埋もれた能力を発揮することができます.実はupdate文もexplainlaiを使ってその使用状況を調べることができて、必要なのはupdate文をselectに変更すればいいので、後の条件は変わりません.updateがレコードを変更する場合も、このレコードを先に見つける必要があるため、updateをselectに置き換えてexplainでデータを検索するときにインデックスを使用する場合を表示できます.
2.使用方法
select文の前にexplainをつければいいです.例えばexplain select*from user where uanme=「dd」;
3.使用例
 

mysql> explain select log_id,login_time from stat.stat_user_login_log order by login_time;
+----+-------------+---------------------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table               | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | stat_user_login_log | index | NULL          | login_time | 4       | NULL |   32 | Using index |
+----+-------------+---------------------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

 
4.結果の詳細
第1列 id :select識別子selectのクエリー・シーケンス番号
2列目 select_type:selectタイプは、次のいずれかです.
Simple:通常のselect、マルチテーブルリンク(union)またはサブクエリを使用しない
primary:一番外側のselect
union:unionの2番目または後ろのselect
dependent subquery:サブクエリの最初のselectは、外部のクエリに依存します.
derived:テーブルのselect(from句のサブクエリ)をエクスポートする
第3列table:出力された行が参照するテーブル
第4列type:結合タイプ、以下に各種の結合タイプを提供し、最適なタイプから最悪のタイプにソートする
System:テーブルに1行しかありません.これはconst接続タイプの特例です
const:テーブルには、クエリーの開始時に読み込まれる一致するローが最大1つしかありません.constテーブルは1回しか読み込まないので速いです!constは、PRIMARY KEYまたはUNIQUEインデックスのすべての部分を定数値で比較する場合に使用します.次のクエリでは、tbl_nameはconstテーブルで使用できます.

SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1=1  primary_key_part2=2；

　　eq_ref:前のテーブルからの行の組合せごとに、そのテーブルから1行を読み出します.これはconstタイプを除いて最良の結合タイプかもしれません.1つのインデックスのすべての部分で結合されて使用され、インデックスはUNIQUEまたはPRIMARY KEYです.
eq_refは= オペレータが比較するインデックス付きカラム.比較値は、定数またはテーブルの前に読み込まれたテーブルを使用するカラムの式です.
次の例では、MySQLはeq_を使用できます.ref結合はref_を処理するtables:
　　

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;
 
SELECT * FROM ref_table,other_table  WHERE ef_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;

　　 ref:前のテーブルからのローの組合せごとに、一致するインデックス値を持つすべてのローがこのテーブルから読み出されます.結合がキーの左端の接頭辞のみを使用する場合、またはキーがUNIQUEまたはPRIMARY KEYでない場合(換言すれば、結合がキーに基づいて単一のローを選択できない場合)はrefを使用します.使用するキーがわずかなローにしか一致しない場合は、この結合タイプが良いです.
refは、=または<=>オペレータのインデックス付きカラムを使用することができます.
次の例では、MySQLはref結合を使用してref_を処理できます.tables:

SELECT * FROM ref_table WHERE key_column=expr;
 
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;
 
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1

 

ref_or_null:       ref，     MySQL        NULL   。                   。

次の例では、MySQLはref_を使用できます.or_null結合はref_を処理するtables:

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

　　index_merge:この結合タイプは、インデックス集計最適化メソッドが使用されていることを示します.この場合、key列には使用するインデックスのリストが含まれています.key_lenには、使用するインデックスの最も長いキー要素が含まれています.
      unique_subquery:このタイプは、次の形式のINサブクエリのrefを置き換えます.

value IN (SELECT primary_key FROM single_table WHERE some_expr)

      unique_subqueryはインデックス検索関数であり、サブクエリを完全に置き換えることができ、より効率的です.
      index_subquery:この結合タイプはunique_に似ています.subquery.INサブクエリは置換できますが、次のサブクエリのユニークでないインデックスにのみ適用されます.

value IN (SELECT key_column FROM single_table WHERE some_expr)

   range:指定した範囲のローのみを取得し、インデックスを使用してローを選択します.key列には、どのインデックスが使用されているかが表示されます.key_lenには、使用するインデックスの最長キー要素が含まれます.このタイプではref列はNULLです.
=、<>、>、>=、<、<=、IS NULL、<=>、BETWEENまたはINオペレータを使用して、キーワード列を定数で比較する場合はrangeを使用します.

SELECT * FROM tbl_name
WHERE key_column = 10;
 
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
 
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
 
SELECT * FROM tbl_name
WHERE key_part1= 10 AND key_part2 IN (10,20,30);

index:この結合タイプはALLと同じで、インデックスツリーのみがスキャンされます.これは、インデックス・ファイルが通常データ・ファイルより小さいため、ALLよりも高速です.
クエリが単一インデックスの一部であるカラムのみを使用する場合、MySQLはこの結合タイプを使用できます.
  ALL:以前のテーブルからの行の組合せごとに、完全なテーブルスキャンを行います.テーブルがconstにマークされていない最初のテーブルである場合、これは通常よくなく、通常はその場合に悪い.通常、ALLを使用するのではなく、より多くのインデックスを追加して、前のテーブルの定数値またはカラム値に基づいてローを取得できます.
第5列possible_keys:possible_keys列は、MySQLがテーブルにローを見つけるためにどのインデックスを使用できるかを示します.なお、この列はEXPLAIN出力に示すテーブルの順序とは完全に独立している.これはpossible_keysのいくつかのキーは、実際には生成されたテーブルの順序で使用できません.
カラムがNULLの場合、関連するインデックスはありません.この場合、WHERE句が特定の列またはインデックスに適した列を参照しているかどうかを確認することで、クエリーのパフォーマンスを向上させることができます.もしそうであれば、適切なインデックスを作成し、EXPLAINでクエリーを再度チェックします.
1枚のテーブルに何のインデックスがあるかを見極めるためにSHOW INDEX FROMを使います tbl_name.
6列目  key:key列には、MySQLが実際に使用を決定したキー(インデックス)が表示されます.インデックスが選択されていない場合は、キーはNULLです.MySQLにpossibleの使用を強制または無視するにはkeys列のインデックスは、クエリーでFORCE INDEX、USE INDEX、またはIGNORE INDEXを使用します.
 
7列目   key_len:key_len列にはMySQLが使用するキーの長さを決定します.キーがNULLの場合、長さはNULLです.注意key_len値は、MySQLが実際に複数のキーワードのいくつかの部分を使用することを決定します.
第8列   ref:ref列には、keyとともにテーブルから行を選択する列または定数が表示されます.
9列目   rows:rows列には、MySQLがクエリーを実行する際にチェックする必要があると考えているローの数が表示されます.
10列目   Extra:この列にはMySQLによるクエリー解決の詳細が含まれています.列に表示できる異なるテキスト文字列について説明します.
       Distinct:MySQLが1番目の一致する行を発見した後、現在の行の組合せに対してより多くの行を検索することを停止します.
       Not exists:MySQLはクエリのLEFTJOIN最適化を行い、LEFTJOIN規格に一致する1つのローを発見した後、前のローの組合せのためにこのテーブル内でより多くのローをチェックしなくなった.
次に、このように最適化できるクエリー・タイプの例を示します.

SELECT *  t1 LEFT JOIN t2 ON t1.id=t2.id
  WHERE t2.id IS NULL；

t 2.idは、NOT NULLとして定義されるものとする.この場合、MySQLは、t 1.idの値を使用してt 1をスキャンし、t 2のローを検索する.MySQLがt 2で一致する行を発見した場合、t 2.idは決してNULLではなく、t 2内の同じid値の行をスキャンしないことを知っています.すなわち、t 1の各ローについて、MySQLは、t 2内に実際に一致するローがどれだけあるかにかかわらず、t 2で1回だけ検索する必要がある.
     range checked for each record:MySQLでは使用可能なインデックスは見つかりませんが、前のテーブルからのカラム値が既知であれば、インデックスの一部が使用できる可能性があります.前のテーブルの行の組合せごとに、MySQLがrangeまたはindex_を使用できるかどうかを確認します.mergeアクセスメソッドは、ローを要求します.
これは速くありませんが、インデックスのない結合を実行するよりもずっと速いです.
       Using filesort:MySQLでは、ソート順にローを取得する方法を見つけるために、追加の転送が必要です.ソートは、結合タイプに従ってすべてのローを参照し、WHERE句に一致するすべてのローに対してソートキーとローのポインタを保存することによって完了します.キーワードがソートされ、ソート順にローが取得されます.
        Using index:テーブル内のカラム情報を取得するために、インデックス・ツリーのみを使用する情報から、実際のローを読み取る必要がなくなります.このポリシーは、クエリが単一のインデックスの一部であるカラムのみを使用する場合に使用できます.
       Using temporary:クエリを解決するために、MySQLは結果を格納するための一時テーブルを作成する必要があります.通常、クエリに列を異なる状況でリストできるGROUP BYおよびORDER BY句が含まれている場合.
      Using where:WHERE句は、次のテーブルに一致するローまたは顧客に送信するローを制限します.テーブルからすべてのローを要求またはチェックしない限り、Extra値がUsing whereではなく、テーブル結合タイプがALLまたはindexの場合、クエリにエラーが発生する可能性があります.
クエリーをできるだけ速くするには、Using filesortを見つけてください. とUsing temporaryのExtra値です.
        Using sort_union(...), Using union(...), Using intersect(...)
これらの関数はindex_の方法を示しています.merge結合タイプ連結インデックススキャン.
      Using index for group-by:テーブルにアクセスするUsing indexのように、Using index for group-byはMySQLがGROUP BYまたはDISTINCTクエリーのすべての列を検索するために使用できるインデックスを発見したことを示し、ハードディスク(HDD)を追加して実際のテーブルにアクセスしないでください.また、インデックスは最も効果的な方法で使用され、グループごとに少量のインデックスエントリのみが読み込まれるようにします.