データベース最適化命令の復習-explian

14770 ワード

出典:https://segmentfault.com/a/1190000008131735
explianの使い方を復習してみると、この文章は素晴らしいので、ちょうどこの機会を利用してもう一度試してみました.

の準備を

EXPLAINの使用を簡単に説明するには、まず2つのテスト用のテーブルを作成し、対応するデータを追加する必要があります.

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);

CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

上に2つのテーブルが作成されています.user_infoとorder_info.データが追加されてインデックスが追加されていません.

Explian情報

実行可能文

EXPLAIN SELECT * FROM user_info

id: 1
select_type: SIMPLE
table: user_info
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)

各列の意味は以下の通りです:重要なのは太くしました

id:SELECTクエリの識別子.SELECTごとに一意の識別子が自動的に割り当てられます.

select_type:SELECT検索のタイプ.

table:クエリーされたテーブル

partitions:一致するパーティション

type:joinタイプ

possible_keys:今回のクエリで選択可能なインデックス

key:今回のクエリで正確に使用するインデックス.

ref:keyとともに使用されるフィールドまたは定数は

です.

rows:このクエリが合計何行スキャンされたかを表示します.これは推定値です.

filtered:このクエリ条件でフィルタされたデータの割合を示す

extra:追加情報

select_type

select_typeはクエリーのタイプを表し、その一般的な値は次のとおりです.

SIMPLEは、UNIONクエリまたはサブクエリ

が含まれていないことを示す

PRIMARY、このクエリが最外層のクエリであることを示す

UNIONは、このクエリがUNIONの2番目またはそれ以降のクエリであることを示す

である.

DEPENDENT UNION、UNIONの2番目または後のクエリ文は、外部のクエリ

に依存します.

ユニオンRESULT,ユニオンの結果

SUBQUERY、サブクエリの最初のSELECT

DEPENDENT SUBQUERY:サブクエリの最初のSELECTは、外部クエリに依存する.すなわち、サブクエリは、外部クエリの結果に依存する.

最も一般的なクエリーカテゴリはSIMPLEのはずです.例えば、私たちのクエリーにサブクエリーがなく、UNIONクエリーもない場合、通常はSIMPLEタイプです.例えば、さっきのクエリーのように!SIMPLEです
UNIONクエリーを使用した場合

EXPLAIN
(SELECT * FROM user_info  WHERE id IN (1, 2, 3))
   UNION
(SELECT * FROM user_info WHERE id IN (3, 4, 5));

EXPLAIN出力の結果は次のようになります.

+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| 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)

table

クエリーに関連する派生テーブル

type

typeフィールドは比較的重要であり、クエリーが効率的かどうかを判断する重要な根拠を提供する.typeフィールドにより,今回のクエリが全テーブルスキャンであるかインデックススキャンであるかなどを判断する.

typeでよく使用される値は、次のとおりです。

system:テーブルにデータが1つしかない.このタイプは特殊なconstタイプです.

const:プライマリ・キーまたは一意のインデックスに対する等値クエリー・スキャンで、最大1行のデータのみが返されます.constクエリは、1回だけ読み込むので非常に高速です.

例えば、次のクエリでは、プライマリ・キー・インデックスが使用されるため、typeはconstタイプである.

explain select * from user_info where id = 1
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

eq_ref:このタイプは通常、複数のテーブルのjoinクエリに表示され、前のテーブルの各結果に対して、後のテーブルの1行の結果にしか一致しないことを示す.また、クエリの比較動作は、通常=であり、クエリの効率が高い.例:

EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 314
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: test.order_info.user_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

ref:このタイプは、通常、複数のテーブルのjoinクエリ、非一意または非プライマリ・キー・インデックス、または最も左のプレフィックス・ルール・インデックスを使用するクエリに対して現れる.

たとえば、次の例ではrefタイプのクエリーを使用します.

EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: ref
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.01 sec)

range:インデックス範囲クエリーを使用して、インデックスフィールド範囲からテーブルのデータレコードの一部を取得することを示す.このタイプは通常=,<>,>,>=,BETWEEN,IN()操作に現れる.typeがrangeの場合、EXPLAINが出力するrefフィールドはNULLであり、key_lenフィールドは、今回のクエリで使用するインデックスの最も長いものです.

たとえば、次の例は範囲クエリーです.

mysql> EXPLAIN SELECT *
    ->         FROM user_info
    ->         WHERE id BETWEEN 2 AND 8 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

index:フルインデックススキャン(full index scan)を示す、ALLタイプと同様であるが、ALLタイプはフルテーブルスキャンである、indexタイプはすべてのインデックスのみをスキャンし、データをスキャンする.

indexタイプは、データをスキャンすることなく、クエリーするデータをインデックスツリーで直接取得することができる.この場合、ExtraフィールドにUsing indexが表示する.例:

mysql> EXPLAIN SELECT name FROM  user_info
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: name_index
      key_len: 152
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

上記の例では、クエリのnameフィールドはちょうどインデックスであるため、クエリテーブルのデータを必要とせずに、インデックスから直接データを取得することでクエリのニーズを満たすことができる.このような場合、typeの値はindexである、Extraの値はUsing indexである.

ALL:全テーブルスキャンを表し、このタイプのクエリーは性能が最も悪いクエリーの一つである.通常、私たちのクエリーはALLタイプのクエリーを発生するべきではありません.このようなクエリーはデータ量が大きい場合、データベースの性能に大きな災難があるからです.1つのクエリがALLタイプのクエリである場合、一般的には、対応するフィールドにインデックスを追加して回避することができる.

次に、全テーブルスキャンの例を示します.全テーブルスキャン時にpossible_keysフィールドとkeyフィールドはいずれもNULLである、インデックスが使用されていないことを示し、rowsが非常に大きいため、クエリ全体の効率が非常に低い.

mysql> EXPLAIN SELECT age FROM  user_info WHERE age = 20
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

typeタイプの性能比較
通常、異なるtypeタイプのパフォーマンス関係は次のとおりです.
ALL < index < range ~ index_merge < ref < eq_ref

possible_keys

possible_keysはMySQLがクエリー時に使用できるインデックスを表す.一部のインデックスがpossible_にあってもkeysに表示されますが、このインデックスがMySQLで実際に使用されるとは限りません.MySQLがクエリーで具体的にどのインデックスを使用したかは、keyフィールドによって決定されます.

key

このフィールドは、現在のクエリでMySQLが実際に使用するインデックスです.

key_len

クエリー・オプティマイザがインデックスを使用するバイト数を示す.このフィールドは、組合せインデックスが完全に使用するか、または最も左のフィールドのみが使用されるかを評価することができる.key_lenの計算ルールは次のとおりです.

文字列char(n):nバイト長varchar(n):utf 8符号化であれば3 n+2バイト;utf 8 mb 4の符号化である、4 n+2バイトである.

数値タイプ:TINYINT:1バイトSMALLINT:2バイトMEDIUMINT:3バイトINT:4バイトBIGINT:8バイト

時間タイプDATE:3バイトTIMESTAMP:4バイトDATETIME:8バイト

フィールド属性:NULL属性は1バイトを占有する.フィールドがNOT NULLの場合、この属性はありません.

簡単な栗を2つ挙げましょう.

 EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: range
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: NULL
         rows: 5
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

上の例はテーブルorder_からinfoでは指定された内容がクエリーされますが、このテーブルの構築文では、テーブルorder_infoには連合インデックスがあります.

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

ただし、このクエリ文

 WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH'

で、先にuser_を行うのでidの範囲クエリーですが、最左接頭辞マッチングの原則に従って、範囲クエリーに遭遇するとインデックスのマッチングが停止します.そのため、実際に使用しているインデックスのフィールドはuser_のみです.id、従ってEXPLAINでは、表示されるkey_lenは9.なぜならuser_idフィールドはBIGINTである8バイトを占有し、NULL属性は1バイトを占有するため、合計9バイトである.user_をidフィールドがBIGINT(20)NOT NULL DEFAULT'0'に変更されるとkey_lengthは8であるべきだ.
左の接頭辞マッチングの原則のため、クエリは連合インデックスのuser_にのみ使用されます.idフィールドであるため、効率は高くない.次の例を見てみましょう.

EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1'
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: ref
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 161
          ref: const,const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

今回のクエリーでは、範囲クエリーは使用されていません.key_lenの値は161である.どうしてですか.私たちのクエリ条件WHERE user_id = 1 AND product_name='p 1'では、結合インデックスの最初の2つのフィールドのみが使用されるため、keyLen(user_id)+keyLen(product_name)=9+50*3+2=161

rows

rowsも重要なフィールドです.MySQLクエリー・オプティマイザは、統計情報に基づいて、SQLが結果セットを検索するためにスキャンするデータの行数を推定する.この値はSQLの効率の良し悪しを非常に直感的に示しており、原則的にrowsが少ないほど良い.

Extra

EXplainの追加情報の多くはExtraフィールドに表示されます.一般的には、次のようなものがあります.

Using filesort ExtraにUsing filesortがある場合、MySQLは追加のソート操作が必要であり、インデックス順でソート効果を達成できないことを示す.一般にUsing filesortがあるが、このようなクエリCPUのリソース消費が大きいため、最適化は排除することを提案する.例えば、

mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 253
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

私たちのインデックスは

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

ただし、上記のクエリではproduct_に基づいてnameでソートするため、インデックスを使用して最適化することができず、さらにUsing filesortが生成する.ソート基準をORDER BY user_に変更するとid, product_名前、それではUsing filesortは現れません.例:

mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 253
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Using index「インデックススキャンを上書き」は、クエリがインデックスツリーで必要なデータを検索できることを示し、テーブルデータファイルをスキャンする必要はなく、パフォーマンスが良いことを示すことが多い

Using temporaryクエリは一時テーブルを使用し、一般的にソート、グループ化、マルチテーブルjoinの場合、クエリの効率が高くなく、最適化を提案する.

SDK ListViewは、1行1列の文字色/背景色を変更します.

呉恩達は第4課の第1週を深く勉強します