MySQL-Explain总结

聪聪

hello，大家好，我是聪聪。

最近在梳理MySQL常用知识点，将已有知识点进行结构化、方便后续回稳固。

今天就来总结梳理一下常用的explain命令。

explian命名是用来获取查询执行计划的信息，查询优化器是如何决定执行查询的主要方法。

# 1. 如何使用

使用非常简单，只需在查询语句select关键字前添加explain即可。MySQL会在该查询方法上进行标记，当执行查询时，标记会返回该执行计划中的每一步信息，而不是执行它，并且会返回多行信息，显示执行计划中的每一步和执行顺序。

mysql> explain select * from t_user_info;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.01 sec)

mysql> explain select * from t_user_info a LEFT JOIN t_user_info b on a.id = b.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL         |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_db.a.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  • 查询中的每个表输出一行，如果查询是两个表关联则会输出两行。
  • 别名表算作一个表。
  • 上面将两个表自关联，输出记录也是两行。这里一个子查询、一个union结果都可以称之为一个"表"。

# 2. 介绍列属性含义

学习explain命令，必然要知道该命令获取查询计划的每一步时，输出各列含义。

# 2.1 id列

包含一个编号，标志select所属行，当查询语句中没有子查询、没有联合查询，只有唯一select时，该值将显示1。否则内层查询语句一般会顺序编号，对应其在原始语句中的位置。

mysql> explain select (select 1 from t_user_info) from t_user_info;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | t_user_info | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
|  2 | SUBQUERY    | t_user_info | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8

MySQL将select语句分为简单查询、复杂查询。复杂类型查询又可分为：

  • 简单子查询
  • 派生表，可以理解为from 语句中的子查询。
  • union查询。

# 2.2 select_type列

该列表示了查询语句是简单还是复杂，如果是复杂的，那么对应下面复杂类型中的哪一种。

  • SIMPLE：表示不包括子查询和union。

当然如果查询存在复杂的子查询，那么外层查询标记为PRIMARY，内层查询则标记为：

  • SUBQUERY：包含在select列表中的子查询。可以理解为不在from子语句中的部分标记为该关键字。如上面SQL所示。explain select (select 1 from t_user_info) from t_user_info;
  • DERIVED：包含在from语句中的子查询select。服务器将其结果放到临时表，内部称之为派生表。
  • UNION：union中第二个或后面的查询语句。
  • DEPENDENT UNION：union中的第二个或后面的SELECT语句,取决于外面的查询
  • UNION RESULT：union结果
  • DEPENDENT SUBQUERY：子查询中第一个select，取决于外面查询。

# 2.3 table列

该列表示该行正在访问哪个表。通常情况下就是该表名称或者别名。

# 2.4 partitions列。

当前查询记录匹配的分区，未分配区的表返回NULL。

# 2.5 type列

关联类型，访问类型。可以理解为MySQL决定如何查询表中的行。下面几种取值性能从最差到最好。

  • ALL： 全表扫描，性能最差。使用了limit除外不进行全表扫描。
  • index：和全表扫描一样，只不过扫描按照索引顺序进行，而不是行顺序。优点是避免了排序，缺点是需要承担按照索引顺序读取整个表的性能开销。
    • 如果在Extra列中看到Using index则说明，MySQL正在使用覆盖索引，只扫描索引数据，而不是按照索引顺序扫描每一行数据，相比索引次序来讲，性能开销较小。
  • **range：**范围扫描，表示索引检索了指定范围的行，通常用于有限制的索引扫描。
    • 相对全索引扫描要一些，不用遍历全部索引。
    • 当使用索引进行查询使用in()、or()匹配列表时，也会显示为范围扫描。当然这和下面where语句带有between、>=等操作不同的，这类访问类型其实是范围条件。
    • 比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
  • ref：该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询。
    • 使用非唯一索引或者唯一性索引的非唯一性前缀时才会发生。
    • 当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
  • eq_ref：当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const。
  • system、const：该表只有一行（相当于系统表），system是const类型的特例。理解为MySQL将查询的部分进行了优化，将其变为常量。
    • 可以在where语句中使用主键查询，就会将查询结果转化为常量，就能够提高查询效率。
  • NULL：MySQL能够在优化阶段分解查询语句，使其在执行阶段不用再访问表或索引。
    • 从索引列中去除最小数据，可以单独通过查询索引完成，不需要在执行时访问表
    • explain select min(id) from t_user_info.

# 2.6 possible_keys 列

该列表示查询时可以使用哪些索引。是基于查询访问的列和使用比较操作符来判断的，在SQL优化阶段创建，该索引列表可能对于后续优化执行过程并没有用。

# 2.7 key列

表示MySQL实际选择的索引。

  • 如果该索引没有出现在possible_keys列表中。那么可以肯定的是MySQL使用该索引肯定是选择了覆盖索引。
  • possible_keys提示哪一个索引嫩够有助于高效的查询。
  • key执行优化采用哪一个所以可以最小化查询成本。

# 2.8 key_len列

MySQL在索引里使用的字节数。

# 2.9 ref列

表示将哪个字段或常量和key列所使用的字段进行比较。

# 2.10 rows列

MySQL估算会扫描的行数，数值越小越好。

  • 估计为了找到所需的行记录而要读取的行数。
  • 该数字是内嵌循环关联计算里的循环数目。

# 2.11 filtered列

表示符合查询条件的数据百分比，最大100。

  • 用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。
  • 表示表中复核条件的记录的百分比做出的悲观估算。

# 2.12 Extra列

扩展信息。

  • Using index：仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。
    • 覆盖索引，避免访问表。
    • explain select id from t_user_info
  • Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现using where信息。例如上面范围条件时。
    • 在存储引擎执行后再进行过滤。
    • 并不是所有where语句均会出现Using where。出现时表示：查询可以受益于不同索引。
  • Using temporary：表示在对MySQL查询结果进行排序时，使用了临时表,,这样的查询效率是比外部排序更低的，常见于order by和group by。
    • explain select t.username from t_user_info t group by t.username ;

上面整理了explain一些常见的参数及其说明，但是实际进行SQL优化时，情况相对复杂，需要了解这些参数含义，明白当前SQL可以从哪些方面进行优化，索引优化、索引覆盖、是否回表，连表查询是否增加缓存buffer从而减少join次数等。

# 3. 参考文件

了解更多内容，可以关注我的微信公众号，更多首发文章。 wechat

1. 如何使用
2. 介绍列属性含义
3. 参考文件