Mysql优化之explain你真的会吗？

爱被打了一巴掌 2022-11-12 08:20 154阅读 0赞

# 一、explain是什么？ #

## 1、 定义 ##

`EXPLAIN`是mysql中的一个命令，可以模拟优化器执行SQL语句并返回执行计划。通过执行计划，我们可以分析查询语句或表结构的性能瓶颈，从而进行SQL优化。

## 2、用法 ##

通过上面的执行计划我们可以了解：

1.  表的执行顺序
2.  数据读取操作的操作类型
3.  哪些索引可以使用
4.  哪些索引被实际使用
5.  每张表有多少行被优化器查询

看到这个表，也许你会两眼一抹黑，这咋看呀？接着往下看，你一定可以从执行计划中轻松得出以上结果。

# 二、名词段解释 #

![名词][fa522d00c4ac1a6c79eb027faef6fb32.png]

## 1. id ##

定义：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

存在以下三种情况：

1.  id相同，执行顺序由上至下

1.  id不同，如果是子查询，id序号对递增，id值越大优先级越高，越先执行

1.  id相同而不同（不同的：id越大越先执行；相同的：由上至下按顺序执行）

## 2、select\_type ##

定义：查询的类型，主要用于区别普通查询、联合查询和子查询等复合查询。

1.  SIMPLE：简单的select查询，查询中不包含子查询和联合查询（union）

1.  PRIMARY：若查询中包含任何的子查询，最外层查询则标记为`PRIMARY`
2.  SUBQUERY：在select或where列表中包含的子查询

1.  DERIVED：在FROM列表中包含的子查询被标记为（DERIVED）,Mysql会递归执行这些子查询，把结果放在临时表里。

1.  UNION：如果第二个SELECT出现在UNION之后，则标记为UNION;如果UNION包含在FROM子句的子查询中，外层SELECT被标记为DERIVED
2.  UNION RESULT：从UNION表获取结果的SELECT

## 3、table ##

定义：顾名思义，表名。

## 4、partitions（分区） ##

定义：显示表分区的分区名

## 5、type ##

定义：type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次为`system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all`，常见的有`system > const > eq_ref > ref > range > index > all`

1.  system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可忽略不计。

1.  const：表示通过索引一次就找到了，const用于比较primary key或者unique索引，只匹配一行数据，速度很快。

1.  eq\_ref：唯一性索引扫描，对于每个索引键，表中只有一条数据与之匹配，常见于主键或唯一索引扫描。

1.  ref：非唯一性索引扫描，返回匹配某个单独值的所有行

1.  range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引，一般是where语句中出现了between、<、>或in等查询。

1.  index：full index scan,全索引树扫描，通常比all快，因为索引文件比数据文件小很多。

1.  all：全表扫描，当数据量达到百万级别，性能会下降明显，需要建立索引；当然小数据量的全表扫描是没有问题的。

## 6、possible\_keys ##

定义：显示可能应用在这张表中的索引，一个或多个（查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）

补充说明：从执行计划关键字`possible_keys`中可以看出，sql语句执行可能用到`idx_username`和`idx_username_age_salary`两个索引。如果为null，就是没有可能使用的索引。

## 7、key ##

定义：实际使用的索引。如果为null，则没有使用索引。

1.  常规举例

补充说明：possible\_keys，可能使用的所以为`idx_username,idx_username_age_salary`; key,实际使用的索引为`idx_username_age_salary`

1.  覆盖索引

看到覆盖索引也许你两眼一抹黑，别急，先记住这么一个名词，继续往下看。

补充说明：如果查询中使用了覆盖索引，则该索引只会出现在key中，不会出现在possible\_keys中。

## 8、key\_len ##

定义：表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精度的情况下，key\_len越短越好。换句话说，key\_len显示的值为索引字段的最大可能长度，即key\_len是通过表定义计算而得，不是通过表内检索出来的，不过具体怎么算出来的没有研究。

## 9、ref ##

定义：显示索引哪一列别使用了，如果可能的话，是一个常数。指明哪些列或常量被用于查找索引列上的值。

补充说明：ref对呀的值为`db.department.id`,表示数据库db中的department表中的id列。

补充说明：username对应的值为john,所以是常量const

## 10、rows ##

定义：根据表的统计信息及索引使用情况，大致估算出找到所需的记录需要读取的行数。

## 11、filtered ##

定义：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。值越大越好。

## 12、Extra ##

定义：包含不适合在其它列中显示但十分重要的的额外信息

1.  Using filesort：说明Mysql使用一个外部索引排序，而不是按照表内的索引进行排序（Mysql中无法使用索引完成的排序操作称为“文件排序”）。

补充说明：虽然使用了idx\_username\_age\_salary索引，但是排序规则不是按照这个索引来的，而是索引文件外排序。因此sql语句的排序规则尽量按照索引的字段来排序，避免文件外排序，降低性能。

1.  using temporary：表示使用了临时表保存了中间结果，Mysql在对查询结果排序时使用临时表，常见于排序order by和分组gruop by。

补充说明：产生临时表很消耗性能

1.  using index：表示相应的select操作中使用了覆盖索引，避免访问了数据行，效率不错。如果同时出现了using where，表示在索引中查找；如果没有，表明是从索引中读取数据，而非查找。

> 覆盖索引/索引覆盖(Covering index)：select数据列只需要从索引中就可以全部获取，不必读取数据行，换句话说查询列要被所建的索引覆盖。

注意：要想使用覆盖索引，一定要从select列表中获取自己想要的列，不可使用select \*;如果使用所有列做索引会导致索引过于庞大。

1.  using where：sql语句中使用了where过滤
2.  using join buffer：使用了连接缓存
3.  impossible where：where子句中总是false，不可能查到任何数据
4.  select table optimized away：在没有group by的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储优化count(\*)操作，不必等到执行阶段在进行计算，执行计划完成的阶段就完成优化。
5.  distinct：优化distinct操作，再找到第一匹配的元组后就停止找同样值的操作。

[fa522d00c4ac1a6c79eb027faef6fb32.png]: /images/20221022/cee61f25022641c0a23bff640fd82e9f.png