MySQLのexplain文解析

20039 ワード
MySQL
一、Explainの概要
Mysqlが提供するexplainキーワードは、クエリー文の実行状況を分析するための神器であり、DBAは、クエリー文の実行結果を分析することで、インデックスの追加、インデックスの変更、カバーインデックスの使用など、クエリー文やテーブル構造を最適化することができます.
二、Explain構造
学生表と成績表を作成します.
CREATE TABLE `student_info`(
`id` int not null AUTO_INCREMENT comment '    ',
`stu_name` VARCHAR(10) NOT NULL comment '    ',
`age` tinyint not null comment '  ',
`gender` VARCHAR(1) not null comment '  ',
PRIMARY KEY (id),
key idx_stu_name(stu_name)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

INSERT INTO student_info(stu_name,age,gender)
VALUES ('  ',20,' '),('  ',18,' '),('   ',20,' '),('  ',101,' ');

CREATE TABLE `score_info`(
`id` int not null AUTO_INCREMENT comment '    ',
`stu_id` int not null comment '    ',
`score` tinyint not null comment '  ',
PRIMARY KEY (id),
KEY idx_stu_id(stu_id)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

INSERT INTO score_info(stu_id,score)
VALUES (1,59),(2,60),(3,80),(4,100);

すべてのデータをクエリーし、explainを使用して実行状況を分析します.
mysql> explain select *  from student_info \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

フィールドの意味:id:Select文の識別子、クエリー文の順序select_を表すtype:クエリーのタイプ(単一クエリー、または接続union)table:使用するテーブルpartitions:どのパーティションtypeを使用するか:クエリーの方法またはアクセス方法possible_keys:使用可能なインデックスkey:実際に使用されているインデックスkey_len:インデックス長ref:どのカラムを使用してインデックスとともにテーブルからrowsを選択するか:結果として何行のfiltered:データがフィルタリングされた後、残りの結果のパーセントExtra:追加の実行状況は、以下のいくつかの非常に重要なフィールドの役割を重点的に分析することを説明します.
三、フィールドの詳細
select_type:クエリーのタイプを表します.主にSIMPLE、PRIMARY、UNION、SUBQUERYがあります.それぞれ表接続を使用しないことを表します.主クエリー、UNIONの後のクエリー、サブクエリーの最初のクエリーは以下の通りです.
mysql> explain select * from student_info where id not in  (select id from student_info)\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: student_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: student_info
   partitions: NULL
         type: unique_subquery
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

type:クエリーの方法またはアクセスの方法を表し、一般的な値は次のとおりです.
  • system:テーブルに一致する行が1つしかない
  • const:プライマリ・キーまたはユニーク・インデックスのスキャンで、結果は1行のデータ
    • のみを返します.
        mysql> explain select id from student_info where id =1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
  • ref:通常、マルチテーブルに表示される接続クエリー、通常のインデックス、または接頭辞スキャンルール:
    • を使用します.
    mysql> explain select * from student_info st inner join score_info sc on st.id=sc.stu_id where st.id=1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: st
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sc
   partitions: NULL
         type: ref
possible_keys: idx_stu_id
          key: idx_stu_id
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)
  • range:インデックス範囲でクエリーし、>=、between文:
    • を使用
    mysql> explain select * from student_info where id>1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
  • index:インデックスファイル全体をスキャンすることを示し、インデックスツリーで必要なデータを見つけることができます:
    • mysql> explain select stu_name  from student_info\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_stu_name
      key_len: 32
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
  • ALL:全テーブルスキャンを表します.これはインデックス列が何も使用されていないことに相当します.そのため、非常に時間がかかります.インデックスを構築しても使用しません.インデックスの意味は何ですか.そのため、調整する必要があります.
    • mysql> explain select * from student_info\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

    key:クエリーで使用されるインデックスです.インデックスが作成され、keyが空の場合、インデックスが使用されていない場合に相当します.extraを調整する必要があります.いくつかの追加情報は、主にいくつかのタイプがあります.
  • Using index:上書きインデックススキャンが使用されていることを示します.つまり、クエリされたカラムにインデックスが作成されていることを示します.
    mysql> explain select id,stu_name from student_info\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_stu_name
      key_len: 32
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
  • Using filesort:インデックスによって追加のソート作業が完了できないことを示し、cpuリソースを占有して完了する必要があります.最適化:
    • mysql> explain select * from student_info order by stu_name\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
     //stu_name           ，    mysql         ，  cpu  
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
  • Using tempory:テンポラリ・テーブルが使用されていることを示し、連表クエリに多く発生する場合、最適化が必要です:
    • mysql> explain select * from student_info st inner join score_info sc  on st.id=sc.stu_id order by st.id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: st
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sc
   partitions: NULL
         type: ALL
possible_keys: idx_stu_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
  • Using where:上記のように、表に戻る場合があります.
    • MySQL UNIONとUNIONALLの文法と使い方
    mysql日付時間関数のまとめ