mysql EXPLAIN的参数解析

最新推荐文章于 2024-04-10 22:10:43 发布

云养猫v2

最新推荐文章于 2024-04-10 22:10:43 发布

阅读量330

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_39590058/article/details/104904830

版权

mysql 专栏收录该内容

35 篇文章 1 订阅

订阅专栏

+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+

使用explain关键字可以模拟优化器执行sql查询语句，从而得知MySQL 是如何处理sql语句。

id

select 查询的序列号，包含一组可以重复的数字，表示查询中执行sql语句的顺序。一般有三种情况：
第一种：id全部相同，sql的执行顺序是由上至下；
第二种：id全部不同，sql的执行顺序是根据id大的优先执行；
第三种：id既存在相同，又存在不同的。先根据id大的优先执行，再根据相同id从上至下的执行。

select_type

select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询

simple：简单的select 查询，查询中不包含子查询或者union
primary：查询中若包含任何复杂的子查询，最外层查询则被标记为primary
subquery：在select或where 列表中包含了子查询
derived：在**from列表中包含的子查询被标记为derived（衍生）**MySQL会递归执行这些子查询，把结果放在临时表里。
union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived
union result：从union表获取结果的select

partitions

表所使用的分区，如果要统计十年公司订单的金额，可以把数据分为十个区，每一年代表一个区。这样可以大大的提高查询效率。

type

这是一个非常重要的参数，连接类型，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all。

对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。

all：（full table scan）全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。
index：（full index scan） index类型和ALL类型一样，区别就是index类型是扫描的索引树。以下两种情况会触发：

1, 如果索引是查询的覆盖索引，就是说索引查询的数据可以满足查询中所需的所有数据，则只扫描索引树，不需要回表查询。在这种情况下，explain 的 Extra 列的结果是 Using index。仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。
2, 全表扫描会按索引的顺序来查找数据行。Using index不会出现在Extra列中。
range：只有给定范围内的行才能被检索，使用索引来查询出多行。输出行中的类决定了会使用哪个索引。

# 范围查找
SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

ref：触发联合索引最左原则，或者这个索引不是主键，也不是唯一索引（换句话说，如果这个在这个索引基础之上查询的结果多于一行）

# 根据索引（非主键，非唯一索引），匹配到多行
SELECT * FROM ref_table WHERE key_column=expr;

# 多表关联查询，单个索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

# 多表关联查询，联合索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

eq_ref：读取本表中和关联表表中的每行组合成的一行。当连接使用索引的所有部分时, 索引是主键或唯一非 NULL 索引时。除了system和const之外，这是最好的连接类型了。当我们使用主键索引或者唯一索引的时候，且这个索引的所有组成部分都被用上，才能是该类型。

# 多表关联查询，联合索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

const：表示最多只有一行匹配，const用于比较primary key 或者unique索引。因为只匹配一行数据，

# 单一主键
SELECT * FROM tbl_name WHERE primary_key=1;

system：表只有一行，这是一个const type 的特殊情况

possible_keys

显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用。

key

显示查询语句实际使用的索引。若为null，则表示没有使用索引。

key_len

显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

ref

显示索引的哪一列或常量被用于查找索引列上的值。

rows

rows就是mysql认为必须要逐行去检查和判断的记录的条数，值越大越不好。

extra

Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现这个更要立刻优化sql。
Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。
覆盖索引（Covering Index）：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。
Using index condition：会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。
Using where：表示不能从索引中得到结果（覆盖索引），需要通过索引回表查询数据。
Using join buffer：表明使用了连接缓存。
impossible where： where 语句的值总是false，不可用，不能用来获取任何元素。
distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。