一、前言

作为一名合格的开发人员，与数据库打交道是必不可少的，尤其是在业务规模和数据体量大规模增长的条件下，应用系统大部分请求读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

Explain则是mysql提供给开发人员用于对Select语句进行分析的命令，可以对查询语句进行分析，并输出Select执行的详细信息，以供开发人员进行针对性的优化。

Expalain的核心指标是rows,绝大部分rows小的语句执行一定很快，所以优化语句基本上都是在优化rows

二、Explain使用

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

➤ 通过EXPLAIN，我们可以分析出以下结果：

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

➤ 使用方式如下：

EXPLAIN +SQL语句

EXPLAIN SELECT * FROM t1

1、执行Explain后的信息

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

• id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
• select_type ：查询类型 或者是 其他操作类型
• table ：正在访问哪个表
• partitions ：匹配的分区
• type ：访问的类型
• possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
• key ：实际使用到的索引，如果为NULL，则没有使用索引
• key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
• ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
• rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
• filtered ：查询的表行占表的百分比
• Extra ：包含不适合在其它列中显示但十分重要的额外信息

  2. Explain各字段含义

  2.1 id

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

  id的结果共有3中情况

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

  

  id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

  

  id相同，且同时存在，可以认为是一组，从上往下执行，在所有组中，id值越大，优先级越高，越先执行。

  

  2.2 select_type

  常见和常用的值有如下几种：

  分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

  SIMPLE ：简单的select查询，查询中不包含子查询或者UNION。

  PRIMARY ：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

  SUBQUERY： 在SELECT或WHERE列表中包含了子查询。

  DERIVED： 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

  UNION： 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

  UNION RESULT： 从UNION表获取结果的SELECT

  2.3 table

  指的就是当前执行的表

  2.4 type（重要）

  type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：

  从最好到最差依次是：

  system > const > eq_ref > ref > ref_or_null > index_merge > range > index > all

  一般来说，得保证查询至少达到range级别，最好能达到ref。

  system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
  const：表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

  eq_ref ：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描,，性能仅次于system及const。

  -- 多表关联查询，单行匹配
  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;

  ref ：非主键和唯一索引的扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，即哪些列或常量被用于查找索引列上的值

  主要出现在联合索引中，满足部分索引的情况下，比如当满足索引的最左前缀规则时

  TIPS

  最左前缀原则，指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3)，那么，如果查询条件是：

  • WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都可以使用该索引；
  • WHERE column1 = 2、WHERE column1 = 1 AND column3 = 3 就无法匹配所有索引，只能匹配到 column1 就会停止匹配，导致索引的利用效率低。

  ref_or_null：该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询。

  SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

  index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引

  unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。

  例如：

  value IN (SELECT primary_key FROM single_table WHERE some_expr)

  index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引

  value IN (SELECT key_column FROM single_table WHERE some_expr)

  range： 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

  index ：Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
  all： Full Table Scan 将遍历全表以找到匹配的行，性能最差

  2.5 possible_keys 和 key

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

  key：实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中

  2.6 key_len

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

  表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.

  key_len 的计算规则如下:

  字符串 ：(utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许 Null) + 2(变长列)

  非字符串 ：数值类型 /时间类型 + 1(允许 Null) 

  • 字符串

    • char(n): n 字节长度

    • varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.

  • 数值类型:

    • TINYINT: 1字节

    • SMALLINT: 2字节

    • MEDIUMINT: 3字节

    • INT: 4字节

    • BIGINT: 8字节

  • 时间类型

    • DATE: 3字节

    • TIMESTAMP: 4字节

    • DATETIME: 8字节

  • 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

    说一下索引长度对查询的影响：

    一般来说如果索引占用的字节数约大，尤其是使用联合索引时，相对应的索引树占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。而且伴随着表数据量的不断增大，会导致这颗索引树占用磁盘空间最后会非常大。

    如果在使用explain时，发现key_len的长度过于大，就需要根据实际业务场景，来优化索引字段。

    这也是优化一个小方向

    2.7 ref

    显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

    2.8 rows

    根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

    

    查询优化的最终目标，是读取行数时减少的，这样查询的效率才会提高，这个是一个重要的指标。

    2.9 filtered

    表示符合查询条件的数据百分比，最大100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。

    TIPS

    在MySQL 5.7之前，想要显示此字段需使用explain extended命令；

    MySQL.5.7及更高版本，explain默认就会展示filtered

    2.9 Extra（重要）

    包含不适合在其他列中显式但十分重要的额外信息

    2.9.1 Using filesort（性能差）

    常见于使用order by的查询语句，并且排序的字段不是索引字段。

    当Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain不会显示的告诉客户端用哪种排序。

    MySQL中无法利用索引完成的排序操作称为“文件排序”。

    2.9.2 Using temporary

    常见于排序order by和分组查询group by，并且排序的字段不是索引字段。

    使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。

    2.9.3 Using index

    表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

    覆盖索引（Covering Index）：覆盖索引是指可以直接在索引列中得到想要的结果，而不用去回表），此时效率最高

    Using index for group-by

    数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。详见 “GROUP BY Optimization”

    -- name字段有索引
    explain SELECT name FROM t1 group by name
    

    2.9.4 Using where

    表明使用了where过滤

    2.9.5 Using join buffer

    强调在获取连接条件时没有用到索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。或者在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

    2.9.6 impossible where

    where子句的值总是false，不能用来获取任何元组

    SELECT * FROM t_user WHERE id = '1' and id = '2'

    2.9.7 select tables optimized away

    explain select min(id) from t1;

    优化器确定：

    ①最多返回1行；

    ②要产生该行的数据，要读取一组确定的行，时会出现此提示。

    一般在用某些聚合函数访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询时展示，

    2.9.8 distinct

    优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

    总结：

    • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

    • EXPLAIN不考虑各种Cache

    • EXPLAIN不能显示MySQL在执行查询时所作的优化工作

    • 部分统计信息是估算的，并非精确值

    • EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

    参考文献：

    MySQL - explan - key_len_mysql 索引key len 影响效率吗-CSDN博客

    [MySQL高级](一) EXPLAIN用法和结果分析_explain 用法-CSDN博客

    https://juejin.cn/post/7073761727850119199

    CSDN

    全网最全 | MySQL EXPLAIN 完全解读 | 周立的博客 - 关注Spring Cloud、Docker