目录一. explain 概述二. explain 关键字的使用方式三. explain 的版本迭代四. explain 只分析SQL语句不执行SQL语句五. explain 输出结果中各个字段的含义六. type 表示检索表数据的方式七. key_len表示使用的索引的长度八. rows 表示预估读取到的行数九. Extra 表示SQL执行时的一些额外信息一. explain 概述有些同学可能不知道我们在数据库中运行 SQL 语句时底层优化器会选择最优的执行方式来执行我们的 SQL 语句也就是说底层执行 SQL 语句的顺序并不一定跟我们写的 SQL 语句的顺序是一致的。MySQL 中有专门优化 SELECT 语句的优化器模块主要功能是通过计算分析系统中收集到的统计信息为客户端请求的 Query(查询) 提供它认为最优的执行计划。这里要注意一点这里最优的执行计划是服务器通过计算得出的但不见得是我们开发人员认为是最优的所以实际开发过程中我们的DBA不一定会采用它所提供的执行计划而是在所提供的执行计划上综合业务逻辑做一些调整而通过 explain 关键字就可以让我们知道数据库对 SQL 语句的执行计划是怎么样的比如多表连接的顺序是什么执行一条 SQL 语句时数据库在执行的时候先做什么再做什么都可以通过 explain 关键字来查看。explain 语句有很多的输出项我们就可以通过这些输出项的数据来对 SQL 语句做判断从而优化那些查询慢的 SQL 语句。二. explain 关键字的使用方式explain 关键字的使用方式极其简单我们只需要将它加在SQL 语句的最前面就可以了如下所示# 加在 SELECT 查询语句的前面 EXPLAIN SELECT * FROM t_patent WHERE pid 115; # 加在 DELETE 删除语句的前面 EXPLAIN DELETE FROM t_patent WHERE pid 110; # 加在 UPDATE 更新语句的前面 EXPLAIN UPDATE t_patent SET t_patent.patent_name 张三 WHERE pid 130;三. explain 的版本迭代1在 MySQL5.6.3 版本以前explain 只能分析 SELECT 语句的执行计划但是在 MySQL5.6.3 版本以后explain 不仅可以分析 SELECT 语句的执行计划还可以分析UPDATEDELETE语句的执行计划只是我们更多的是用于分析 SELECT 查询语句这一点作为了解即可2在 MySQL5.7 以前的版本中想要显示 partitions 需要使用 explain partitions 命令想要显示 filtered 需要使用 explain extended 命令。而 5.7 版本之后默认 explain 将直接显示 partitions 和 filtered 中的信息。也许现在有些同学还不 partitions 和 filtered 是什么这里先不用关心看到下面的讲解自然就会明白了。这里你先可以简单理解为在 5.7 版本之后explain 关键字做了增强explain 将另外两个关键字的功能也包含进去了不需要再像以前一样执行三个关键字。四. explain 只分析SQL语句不执行SQL语句这句话应该很好理解我们使用 explain 关键字执行一条语句时它只是输出当前 SQL 语句最优的执行计划但并没有真正的去执行这条 SQL 语句。我简单举个例子演示一下1如下为 patent 数据库中的 t_patent 表表中有一些数据2然后我使用 explain 关键字查询输出当前 DELETE 语句的执行计划运行成功输出了结果这里暂时不关心结果中的各个字段含义是什么3我们再来查询一下刚才 pid 110 的那条数据是否存在如下我们发现仍然可以查询到 pid 110 的这条数据。通过这个例子也证实了刚才的结论explain 只负责输出最优的执行计划但并没有真正的去执行 SQL 语句。五. explain 输出结果中各个字段的含义刚才在第四小节的演示中我们看到了执行 explain 会输出很多的字段一共有12个每个字段代表了不同的涵义列举如下在这12个字段中我们需要重点关注 typekey_lenrowsExtra 这个四个字段他们是面试过程中最容易问到的点而且是SQL语句调优的关键点我们的 SQL 的调优就是基于这个四个关键字段来调的。六. type 表示检索表数据的方式执行计划中的 type 的一条记录就代表 MySQL 对某个表执行查询时的访问方式也称访问类型。它有众多访问方式查询的效率从高到低依次是system const eq ref fulltext ref or null range index ALL在实际开发过程中我们希望一条 SQL 语句的查询效率越高越好大多数情况下应当把访问方式至少定位在 range 以上ALL(全表扫描)是我们最不希望看到的结果所以在编写 SQL 时要尽量避免此访问类型的发生。七. key_len表示使用的索引的长度key_len 通常应用于联合索引开发过程中一个表会有很多的字段为了提高查询效率我们会为查询频率较高的几个字段建立联合索引通过 explain 就可以得到使用而索引字段的长度从而得知当前SQL语句中都是用到了联合索引中的那个字段以便做进一步的优化。不同的类型的字段计算规则也不一样如下所示1字符串类型计算char(n)算作 n 个字节varchar如果是 utf-8 则算作 3n2 个字节如果是utf-mb4则算作 4n2 个字节2数值类型计算tinyint1字节smaillint2字节int4字节bigint8字节3事件类型计算date3字节timestamp4字节datetime8字节此外字段如果为空需要1个字段记录是否为空。通过 explain 得出的索引时应长度配合上述三种类型长度的计算我们就能精准得出联合索引中的那些字段被使用到那些没有被使用到可以进一步优化sql语句的编写提高程序运行效率。八. rows 表示预估读取到的行数rows 字段的值通常可以搭配 filtered 字段的值rows 表示预估会读取到的行数filtered 则表示经过条件筛选之后剩余记录条数的百分比而两者想成得出的结果通常就是我们最终查询所得到的数据行数。九. Extra 表示SQL执行时的一些额外信息这里的额外信息包含很多种如下1Using Index使用非主键索引数就可以查询到所需要的数据一般是覆盖索引即想要查询的数据都包含在非剧组索引数中不需要进行回表操作。2Using where不通过索引查询所需要的数据这句话也很好理解就是我们 where 筛选条件的字段不是索引字段。那么就会进行全表扫描即 type 字段值为 ALL。3Using index condition表示查询列不被索引覆盖where 条件中是一个索引范围查找过滤玩索引后会回表找到所有符合条件的数据行。例如 SELECT * FROM table where 主键 某个值这种 SQL 语句就符合上面这种情况4Using temporary表示在查询的过程中需要利用临时表来处理查询比如我们使用 union all 取两个表满足条件的并集得到的数据表实际上是一张临时表5Using filesort当查询中包含 order by 操作而且无法利用索引完成的排序操作时若数据量小就在内存中进行排序若数据量多就在硬盘中进行排序。当遇到 Using temporary 或 Using filesort 时我们可以通过添加索引对 SQL 进行优化。6Select table optimized away当我们通过聚合函数访问某些索引数据时在 Extra 中就会显示 Select table optimized away如果通过聚合函数访问普通非索引字段在 Extra 中就会显示 null。
)
)
