MySQL EXPLAIN コマンドの詳細な学習

MySQLのEXPLAINコマンドの詳しい説明

MySQLのEXPLAINコマンドは、SQL文のクエリ実行プラン(QEP)に使用されます。このコマンドの出力により、MySQL オプティマイザーが SQL ステートメントをどのように実行するかを理解できます。このコマンドは調整に関する提案を提供しませんが、調整の決定に役立つ重要な情報を提供します。

1 構文MySQL の EXPLAIN 構文は、SELECT ステートメントまたは特定のテーブルで実行できます。テーブルに対して動作する場合、このコマンドは DESC テーブル コマンドと同等です。 UPDATE
および DELETE コマンドもパフォーマンスの向上が必要です。これらのコマンドがテーブルのメイン コードで直接実行されない場合、インデックスを最適に使用するために、これらのコマンドを SELECT ステートメントに変更する必要があります (テーブルで EXPLAIN コマンドを実行するため)。彼ら） 。以下の例を参照してください:

UPDATE table1
SET col1 = X, col2 = Y
WHERE id1 = 9
AND dt >= '2010-01-01';

この UPDATE ステートメントは、次のような SELECT ステートメントに書き換えることができます:

SELECT col1, col2
FROM table1
WHERE id1 = 9
AND dt >= '2010-01-01';

バージョン 5.6.10 では、直接dml ステートメントに対して Explain 分析操作を実行します。

MySQL オプティマイザーはコストに基づいて動作し、QEP ポジションは提供しません。これは、各 SQL ステートメントが実行されると QEP が動的に計算されることを意味します。 MySQL ストアド プロシージャの SQL ステートメントも、実行されるたびに QEP を計算します。ストアド プロシージャ キャッシュはクエリ ツリーのみを解析します。

2 各カラムの詳細な説明

MySQL EXPLAIN コマンドは、SQL ステートメント内のテーブルごとに次の情報を生成できます:

mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G;
  ********************* 1. row ***********************
  id: 1
  select_type: SIMPLE
  table: inventory
  type: ALL
  possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
  rows: 787338
  Extra: Using where

この QEP は、インデックスが使用されていないことを示します (つまり、 、テーブル全体のスキャン）そして、クエリを満たすために大量の行が処理されます。同じ SELECT ステートメントの場合、最適化された QEP は次のようになります。

  ********************* 1. row ***********************
  id: 1
  select_type: SIMPLE
  table: inventory
  type: ref
  possible_keys: item_id
  key: item_id
  key_len: 4
  ref: const
  rows: 1
  Extra:

この QEP では、インデックスが使用されており、1 行のデータのみがフェッチされることが推定されています。

QEP の各行のすべてのリストは次のとおりです:

 id select_type
 table パーティション (この列は EXPLAIN PARTITIONS 構文でのみ表示されます)
 possible_keys
 key
 key_len
 ref
 行
 フィルター処理 (この列は EXPLAINED EXTENDED 構文でのみ表示されます)
 追加

これらの列には、各テーブルの SELECT ステートメントの QEP が表示されます。テーブルは、SQL 実行中に (たとえば、サブクエリやマージ操作から) 生成された物理スキーマ テーブルまたは内部一時テーブルに関連付けられている場合があります。
詳細については、MySQL リファレンス マニュアル (http://www.php.cn/) を参照してください。

2.1 key

key 列は、オプティマイザーが使用するために選択したインデックスを示します。一般に、SQL クエリではテーブルごとに 1 つのインデックスのみが使用されます。インデックスのマージには、特定のテーブルで 2 つ以上のインデックスが使用されている場合など、いくつかの例外があります。 以下は、QEP のキー列の例です。 キー: item_id
キー: NULL
キー: first、last
SHOW CREATE TABLE f5d188ed2c074f8b944552db028f98a1 コマンドは、テーブルとインデックス列の詳細を表示する最も簡単な方法です。キー列に関連する列には、 possible_keys、rows、key_len も含まれます。


2.2 ROWS

rows 列は、MySQL オプティマイザが累積結果セットに存在するすべての行について分析しようとした行数の推定値を提供します。 QEP を使用すると、この難しい統計を簡単に説明できます。 クエリ内の読み取り操作の合計数は、行を結合する前の各行の行値の継続的な累積に基づいています。これは入れ子になった行アルゴリズムです。
2 つのテーブルを接続する QEP を例に挙げます。条件 id=1 で見つかった最初の行の rows 値は 1 で、これは最初のテーブルに対する読み取り操作と同等です。 2 行目は id=2 で見つかり、行の値は 5 です。これは、現在の累積値 1 に一致する 5 つの読み取りに相当します。両方の表を参照すると、読み取り操作の合計数は 6 です。別の QEP
では、最初の行の値は 5 で、2 行目の値は 1 です。これは、最初のテーブルの 5 回の読み取り (5 つの累積ごとに 1 回) に相当します。したがって、2 つのテーブルの読み取り操作の合計数は 10 (5+5) 回になります。

最良の推定値は 1 です。通常、これは、探している行が主キーまたは一意キーによってテーブル内で見つかる場合に発生します。
以下の QEP では、外側の入れ子になったループは id=1 で見つけることができ、その推定物理行番号は 1 です。 2 番目のループでは 10 行が処理されました。

 ********************* 1. row ***********************
 id: 1
 select_type: SIMPLE
 table: p
 type: const
 possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: const
 rows: 1
 Extra:
 ********************* 2. row ***********************
 id: 1
 select_type: SIMPLE
 table: c
 type: ref
 possible_keys: parent_id
 key: parent_id
 key_len: 4
 ref: const
 rows: 10
 Extra:

SHOW STATUS コマンドを使用して、実際の行操作を表示できます。このコマンドは、物理行の操作を確認する最良の方法を提供します。以下の例を参照してください:

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | Handler_read_first    | 0     |
  | Handler_read_key      | 0     | 
  | Handler_read_last     | 0     |
  | Handler_read_next     | 0     |
  | Handler_read_prev     | 0     |
  | Handler_read_rnd      | 0     |
  | Handler_read_rnd_next | 11    |
  +-----------------------+-------+
  7 rows in set (0.00 sec)

次の QEP では、id=1 で見つかった外側のネストされたループには推定 160 行があります。 2 番目のループは 1 行と推定されます。

 ********************* 1. row ***********************
  id: 1
  select_type: SIMPLE
  table: p
  type: ALL
  possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
  rows: 160
  Extra:
 ********************* 2. row ***********************
  id: 1
  select type: SIMPLE
  table: c
  type: ref
  possible_keys: PRIMARY,parent_id
  key: parent_id
  key_len: 4
  ref: test.p.parent_id
  rows: 1
  Extra: Using where

実際の行操作は SHOW STATUS コマンドで確認でき、物理読み取り操作の数が大幅に増加していることがわかります。以下の例をご覧ください:

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
 +--------------------------------------+---------+
 | Variable_name | Value |
 +--------------------------------------+---------+
 | Handler_read_first | 1 |
 | Handler_read_key | 164 |
 | Handler_read_last | 0 |
 | Handler_read_next | 107 |
 | Handler_read_prev | 0 |
 | Handler_read_rnd | 0 |
 | Handler_read_rnd_next | 161 |
 +--------------------------------------+---------+
 相关的QEP 列还包括key列。

 
 2.3 possible_keys
 possible_keys 列指出优化器为查询选定的索引。
 一个会列出大量可能的索引(例如多于3 个)的QEP 意味着备选索引数量太多了，同时也可能提示存在一个无效的单列索引。
 可以用第2 章详细介绍过的SHOW INDEXES 命令来检查索引是否有效且是否具有合适的基数。
 为查询确定QEP 的速度也会影响到查询的性能。如果发现有大量的可能的索引，则意味着这些索引没有被使用到。
 相关的QEP 列还包括key 列。
 
 2.4 key_len
 key_len 列定义了用于SQL 语句的连接条件的键的长度。此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。
 此列的一些示例值如下所示：
 
 此列的一些示例值如下所示：
 key_len: 4 // INT NOT NULL
 key_len: 5 // INT NULL
 key_len: 30 // CHAR(30) NOT NULL
 key_len: 32 // VARCHAR(30) NOT NULL
 key_len: 92 // VARCHAR(30) NULL CHARSET=utf8
 
 从这些示例中可以看出，是否可以为空、可变长度的列以及key_len 列的值只和用在连接和WHERE 条件中的索引的列
 有关。索引中的其他列会在ORDER BY 或者GROUP BY 语句中被用到。下面这个来自于著名的开源博客软件WordPress 的表展示了
 如何以最佳方式使用带有定义好的表索引的SQL 语句：

 CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  PRIMARY KEY (`ID`),
  KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
 ) DEFAULT CHARSET=utf8
 
  CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  PRIMARY KEY (`ID`),
  KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
 ) DEFAULT CHARSET=utf8

 这个表的索引包括post_type、post_status、post_date 以及ID列。下面是一个演示索引列用法的SQL 查询：
 EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01';
 
 这个查询的QEP 返回的key_len 是62。这说明只有post_type列上的索引用到了(因为(20×3)+2=62)。尽管查询在WHERE 语句
 中使用了post_type 和post_date 列，但只有post_type 部分被用到了。其他索引没有被使用的原因是MySQL 只能使用定义索引的
 最左边部分。为了更好地利用这个索引，可以修改这个查询来调整索引的列。请看下面的示例：

 mysql> EXPLAIN SELECT ID, post_title
 -> FROM wp_posts
 -> WHERE post_type='post'
 -> AND post_status='publish'
 -> AND post_date > '2010-06-01';

 
 在SELECT查询的添加一个post_status 列的限制条件后，QEP显示key_len 的值为132，这意味着post_type、post_status、post_date
 三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，这个索引的主码列ID 的定义是使用MyISAM 存储索
 引的遗留痕迹。当使用InnoDB 存储引擎时，在非主码索引中包含主码列是多余的，这可以从key_len 的用法看出来。
 相关的QEP 列还包括带有Using index 值的Extra 列。
 
 2.5 table
 table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表
 的标识符，如派生表、子查询或集合。下面是QEP 中table 列的一些示例：
 table: item
 table: 6db98b0ebf978836f3b742968b1383c4
 table: 3d79052a4b39814111818a40e6c7009c
 表中N 和M 的值参考了另一个符合id 列值的table 行。相关的QEP 列还有select_type

 2.6 select_type
 select_type 列提供了各种表示table 列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能
 的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。

 1. SIMPLE
 对于不包含子查询和其他复杂语法的简单查询，这是一个常 见的类型。

 2. PRIMARY
 这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。
 
 3. DERIVED
 当一个表不是一个物理表时，那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的
 示例：
 mysql> EXPLAIN SELECT MAX(id)
 -> FROM (SELECT id FROM users WHERE first = 'west') c;

4. DEPENDENT SUBQUERY
この select-type 値は、サブクエリを使用するために定義されています。次の SQL ステートメントはこの値を提供します:
mysql> EXPLAIN SELECT p.*
-> FROM 親 p
-> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);

5. UNION
これは UNION ステートメントの SQL 要素の 1 つです。
6. UNION RESULT
これは、UNION ステートメントで定義された一連のテーブルの戻り結果です。 select_type がこの値の場合、多くの場合、table の値が 3d79052a4b39814111818a40e6c7009c であることがわかります。これは、一致する ID 行がこのセットの一部であることを意味します。次の SQL は UNION および UNION RESULT の選択タイプを生成します:
mysql> EXPLAIN SELECT p.* FROMparent p WHERE p.val
LIKE 'a%'
-> UNION
-> WHERE p.id > 5;

2.7 パーティション パーティション列は、指定されたテーブルで使用されるパーティションを表します。この列は、EXPLAIN
PARTITIONS ステートメントにのみ表示されます。

2.8 追加 追加列には、さまざまな種類の MySQL オプティマイザー パスに関するさまざまな
追加情報が提供されます。追加のカラムには複数の値を含めることができ、さまざまな値を含めることができます。これらの値は、MySQL の新しいバージョンのリリースに伴い増加し続けています。以下は、
で一般的に使用される値のリストです。値のより包括的なリストは、次のアドレスから見つけることができます:
http://www.php.cn/。

1. where の使用

この値は、クエリが where ステートメントを使用して結果を処理すること (テーブル全体のスキャンの実行など) を示します。インデックスも使用されている場合、必要なデータを取得して読み取りバッファを処理することにより、行制約が実現されます。

2. 一時テーブルの使用

この値は、内部一時テーブル (メモリベース) の使用を示します。クエリでは複数の一時テーブルを使用する場合があります。 MySQL がクエリ実行中に一時テーブルを作成する理由は数多くあります。一般的な 2 つの理由は、異なるテーブルの列に対して

DISTINCT を使用すること、または異なる ORDER BY 列と GROUP BY 列を使用することです。 詳細については、http://www.php.cn/ of_query_execution_and_use_of_temp_tables をご覧ください。
一時テーブルにディスクベースの MyISAM ストレージ エンジンを強制的に使用させることができます
。これには主に 2 つの理由があります:
 内部一時テーブルが占有するスペースが min (tmp_table_size, max_
heap_table_size) システム変数の制限を超えている
 TEXT/BLOB カラムが使用されている


3. filesort を使用する

これは ORDER BY ステートメントの結果です。これは CPU に負荷がかかるプロセスになる可能性があります。
適切なインデックスを選択し、インデックスを使用してクエリ結果を並べ替えることにより、パフォーマンスを向上させることができます。詳細な手順については、第 4 章を参照してください。

4. インデックスの使用
この値は、クエリ テーブルの要件を満たすためにインデックスのみを使用でき、テーブル データに直接アクセスする必要がないことを強調します。これらの
値を理解するには、第 5 章の詳細な例を参照してください。

5. 結合バッファーの使用
この値は、結合条件を取得するときにインデックスが使用されず、中間結果を保存するために結合バッファーが必要であることを強調します。
この値が表示される場合は、クエリの特定の条件によっては、パフォーマンスを向上させるためにインデックスを追加する必要がある場合があることに注意してください。

6. 不可能 where
この値は、where ステートメントの結果、条件を満たす行が存在しないことを強調します。以下の例を参照してください:
mysql> EXPLAIN SELECT * FROM user WHERE 1=2;

7. 最適化されたテーブルを選択します
この値は、オプティマイザーが集約関数から 1 つの行のみを返すことを意味します。結果 。以下の例を参照してください:

8. Distinct この値は、MySQL が最初に一致する行を見つけた後、他の行の検索を停止することを意味します。

9. インデックスのマージ MySQL が特定のテーブルで複数のインデックスを使用することを決定すると、使用されるインデックスとマージの種類の詳細を示す次の形式のいずれかが表示されます。
 sort_union(...)の使い方

 Union(...)の使い方

 intersect(...)の使い方
2.9 id

idカラムは、QEPで表示されるテーブルへの連続参照です。

2.10 ref ref 列は、インデックス比較に使用される列または定数を識別するために使用できます。

 2.11 filtered
 filtered 列给出了一个百分比的值，这个百分比值和rows 列的值一起使用，可以估计出那些将要和QEP 中的前一个表进行连
 接的行的数目。前一个表就是指id 列的值比当前表的id 小的表。这一列只有在EXPLAIN EXTENDED 语句中才会出现。

 2.12 type
 type 列代表QEP 中指定的表使用的连接方式。下面是最常用的几种连接方式：
  const 当这个表最多只有一行匹配的行时出现system 这是const 的特例，当表只有一个row 时会出现
  eq_ref 这个值表示有一行是为了每个之前确定的表而读取的
  ref 这个值表示所有具有匹配的索引值的行都被用到
  range 这个值表示所有符合一个给定范围值的索引行都被用到
  ALL 这个值表示需要一次全表扫描其他类型的值还有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。
  想了解更多信息可以访问http://www.php.cn/。
 

 3 解释EXPLAIN 输出结果
 理解你的应用程序(包括技术和实现可能性)和优化SQL 语句同等重要。下面给出一个从父子关系中获取孤立的父辈记录的商
 业需求的例子。这个查询可以用三种不同的方式构造。尽管会产生相同的结果，但QEP 会显示三种不同的路径。

 mysql> EXPLAIN SELECT p.*
 -> FROM parent p
 -> WHERE p.id NOT IN (SELECT c.parent_id FROM child
 c)\G
 ********************* 1. row ***********************
 id: 1
 select type: PRIMARY
 table: p
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 160
 Extra: Using where
 ********************* 2. row ***********************
 id: 2
 select_type: DEPENDENT SUBQUERY
 table: c
 type: index_subquery
 possible_keys: parent_id
 key: parent_id
 key_len: 4
 ref: func
 rows: 1
 Extra: Using index
 2 rows in set (0.00 sec)

 EXPLAIN SELECT p.* FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE c.child_id IS NULL\G
 ********************* 1. row ***********************
 id: 1
 select_type: SIMPLE
 table: p
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 160
 Extra:
 ********************* 2. row ***********************
 id: 1
 select_type: SIMPLE
 table: c
 type: ref
 possible_keys: parent_id
 key: parent_id
 key_len: 4
 ref: test.p.id
 rows: 1
 Extra: Using where; Using index; Not exists
 2 rows in set (0.00 sec)

 以上就是MySQL EXPLAIN 命令详解学习的内容，更多相关内容请关注PHP中文网（www.php.cn）！