526互联

MySql explain命令返回结果解读

发布时间 2023-09-27 16:38:21作者: isyues

1. explain命令是什么东西？

explain 是MySql提供的SQL语句查询性能的工具，是我们优化SQL的重要指标手段，要看懂explain返回的结果集就尤为重要

2. explain命令返回列解读

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | PRIMARY     | SC      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   18 |    10.00 | Using where                                |
|  1 | PRIMARY     | Student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where; Using join buffer (hash join) |
|  2 | SUBQUERY    | Teacher | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where                                |
|  2 | SUBQUERY    | Course  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (hash join) |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

2.1 id

一个编号，表示select所属的行。如果查询中没有子查询或关联查询，那么只会有唯一的SELECT，每一行的该列中都将显示一个1，否则，内层的SELECT语句一般会顺序编号，对应于其在原始语句中的位置。id越大执行优先级越高，id相同则认为是一组，从上往下执行，id为NULL最后执行

2.2 select_type

查询类型，表示当前SQL语句是简单查询还是复杂查询

  • SIMPLE：简单查询，不包含任何连接查询和子查询。
  • PRIMARY：查询中若查询包含任何复杂的子部分，最外层的select被标记为PRIMARY。
  • UNION：当前SQL语句是连接查询时，表示连接查询的第二个SELECT语句或者第二个后面的SELECT语句。
  • DEPENDENT UNION：含义与UNION几乎相同，但是DEPENDENT UNION取决于外层的查询语句。
  • UNION RESULT：表示UNION连接查询的结果信息。
  • SUBQUERY：包含在SELECT子句（不在from子句中）中的子查询的SELECT，结果不依赖于外部查询。
  • DEPENDENT SUBQUERY：含义与SUBQUERY几乎相同，但是DEPENDENTSUBQUERY取决于外层的查询语句。
  • DERIVED：表示FROM子句中的子查询。
  • MATERIALIZED：表示实例化子查询。
  • UNCACHEABLE SUBQUERY：表示不缓存子查询的结果数据，重新计算外部查询的每一行数据。
  • UNCACHEABLE UNION：表示不缓存连接查询的结果数据，每次执行连接查询时都会重新计算数据结果。

2.3 table 正在访问哪个表

2.4 partitions 如果当前数据表是分区表，则表示查询结果匹配的分区

2.5 type (重要)

其取值从最优到最差依次为:

null > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge> unique_subquery > index_subquery > range > index > ALL

  • NULL: 这种访问方式意味着MySQ能在优化阶段分解查询语句，在执行阶段甚至不需要再访问表或者索引。例如，从一个索引列里选取最小值可以通过单独查询索引来完成，不需要在执行时访问表。
  • system：查询的数据表中只有一行数据，是const类型的特例
  • const：当MySQL能对查询的某部分进行优化并将其转换成一个常量时，它就会使用这些访问类型。比如通过将某一行的主键访问WHERE字句的方式来查询主键：SELECT id from t where id = 1。此时MySQL就能把这个查询转换为一个常量。
  • eq_ref: 使用这种索引查找，MySQL清楚的知道最多只返回一条符合条件的记录，使用主键或者唯一值索引查找时能看到这种方法。MySQL对于这种访问类型的优化做得非常好，因为它知道到无需估计匹配行的范文或者在找到匹配行后再继续查找（因为值不会重复）。
  • ref: 索引访问，也叫索引查找。返回所有匹配某个单个值的行，然而它可能会找到符合条件的多个行。此类索引访问只有当使用非唯一性索引或者唯一索引的非唯一性前缀时才会发生。把他叫ref是因为他要和某个参考值相比较。这个参考值或者是一个常数，或者来自多表查询前一个表里的结果值
  • fulltext：当查询条件使用了全文索引时，type的取值为fulltext
  • ref_or_null：类似于ref，但是当查询语句的连接条件或者查询条件包含的列有NULL值时，MySQL会进行额外查询，经常被用于解析子查询。典型的场景为 is null
  • index_merge: 当查询语句使用索引合并优化时，type的取值为index_merge。此时，key列会显示使用到的所有索引，key_len显示使用到的索引的最长键长值
  • unique_subquery: 当查询语句的查询条件为IN的语句，并且IN语句中的查询字段为数据表的主键或者非空唯一索引字段时，type的取值为unique_subquery。
  • index_subquery: 与unique_subquery类似，但是IN语句中的查询字段为数据表中的非唯一索引字段。
  • range: 范围扫描，就是一个有限制的索引扫描，使用一个索引来检索给定范围的行，不需要遍历全部索引。范围扫描通常出现在between,>,<,>=等操作中。in()和OR也会显示范围扫描，但这两者其实是不同的访问类型，性能上也有差异。此类查找的开销根ref索引访问的开销相当。
  • index：跟全表扫描一样，只是MySQL扫描表时按照索引次序进行而不是行，主要优点是避免了排序；缺点是要承担按索引次序读取整个表的开销。
  • ALL：全表扫描，从头到尾的查找所需要的行。但仍然存在例外，例如使用了 LIMIT ，或者Extra 列中显示 Using distinct/not exists。

2.6 possible_keys

执行查询语句时可能用到的索引，但是在实际查询中未必会用到。当此列为NULL时，说明没有可使用的索引，此时可以通过建立索引来提高查询的性能。

2.7 key (重要)

执行查询语句时MySQL实际会使用到的索引。如果MySQL实际没有使用索引，则此列为NULL。

2.8 key_len (重要)

执行查询语句时实际用到的索引按照字节计算的长度值，可以通过此字段计算MySQL实际上使用了复合索引中的多少字段。如果key列值为NULL，则key_len列值也为NULL。

2.9 ref

数据表中的哪个列或者哪个常量用来和key列中的索引做比较来检索数据。如果此列的值为func，则说明使用了某些函数的结果数据与key列中的索引做比较来检索数据。

2.10 rows (重要)

查询数据时必须查找的数据行数，当数据表的存储引擎为InnoDB时，值为MySQL的预估值。

2.11 Extra

在执行查询语句时额外的详细信息

  • Using index：表示MySQL将使用覆盖索引，这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。
  • Using index condition：在5.6版本后加入的新特性索引下推（Index Condition Pushdown，ICP），在索引遍历过程中，对索引中包含的字段先做判断（即使该字段没有使用到索引），直接过滤掉不满足条件的记录，减少回表次数。
  • Using where：意味着MySQL服务器将在存储引擎检索行后再进行过滤。就是先通过索引读取整行数据，再按 WHRER条件进行检查，符合就留下，不符合就丢弃。查询的列未被索引覆盖。
  • Using temporary：MySQL需要创建一张临时表来中间结果并进一步处理，比如union、group by、distinct等，出现这种情况一般是要进行优化的，首先是想到用索引来优化。
  • Using filesort：MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行，即filesort（文件排序）。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。filesort有两种，一种是内存排序，一种是磁盘排序，无法得知。
  • Distinct: 一旦MySQL找到了与行相联合匹配的行，就不再搜索了，常见于关联查询。
  • No tables used：Query语句中使用from dual 或不含任何from子句。
  • Using join buffer：使用了连接缓存，join语句用到了缓冲区。