用 Explain 命令分析 MySQL 的 SQL 执行

用户头像
程序员历小冰
关注
发布于: 2020 年 06 月 15 日
用 Explain 命令分析 MySQL 的 SQL 执行

在上一篇文章《MySQL常见加锁场景分析》中,我们聊到行锁是加在索引上的,但是复杂的 SQL 往往包含多个条件,涉及多个索引,找出 SQL 执行时使用了哪些索引对分析加锁场景至关重要。

比如下面这样的 SQL:

mysql> delete from t1 where id = 1 or val = 1

其中 id 和 val 都是索引,那么执行时使用到了哪些索引,加了哪些锁呢?为此,我们需要使用 explain 来获取 MySQL 执行这条 SQL 的执行计划。

什么是执行计划呢?简单来说,就是 SQL 在数据库中执行时的表现情况,通常用于 SQL 性能分析、优化和加锁分析等场景,执行过程会在 MySQL 查询过程中由解析器,预处理器和查询优化器共同生成。

MySQL 查询过程



如果能搞清楚 MySQL 是如何优化和执行查询的,不仅对优化查询一定会有帮助,还可以通过分析使用到的索引来判断最终的加锁场景。

下图是MySQL执行一个查询的过程。实际上每一步都比想象中的复杂,尤其优化器,更复杂也更难理解。本文只给予简单的介绍。





MySQL查询过程如下:

  • 客户端发送一条查询给服务器。

  • 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。

  • 服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划。

  • MySQL根据优化器生成的执行计划,再调用存储引擎的API来执行查询。

  • 将结果返回给客户端。

执行计划



MySQL会解析查询,并创建内部数据结构(解析树),并对其进行各种优化,包括重写查询、决定表的读取顺序、选择合适的索引等。

用户可通过关键字提示(hint)优化器,从而影响优化器的决策过程。也可以通过 explain 了解 数据库是如何进行优化决策的,并提供一个参考基准,便于用户重构查询和数据库表的 schema、修改数据库配置等,使查询尽可能高效。

下面,我们依次介绍 explain 中相关输出参数,并以实际例子解释这些参数的含义。

select_type



查询数据的操作类型,有如下

  • simple 简单查询,不包含子查询或 union,如下图所示,就是最简单的查询语句。





  • primary 是 SQL 中包含复杂的子查询,此时最外层查询标记为该值。

  • derived 是 SQL 中 from 子句中包含的子查询被标记为该值,MySQL 会递归执行这些子查询,把结果放在临时表。下图展示了上述两种类型。





  • subquery 是 SQL 在 select 或者 where 里包含的子查询,被标记为该值。





  • dependent subquery:子查询中的第一个 select,取决于外侧的查询,一般是 in 中的子查询。





  • union 是 SQL 在出现在 union 关键字之后的第二个 select ,被标记为该值;若 union 包含在 from 的子查询中,外层select 被标记为 derived。

  • union result 从 union 表获取结果的 select。下图展示了 union 和 union result 的 SQL 案例。





  • dependent union 也是 union 关键字之后的第二个或者后边的那个 select 语句,和 dependent subquery 一样,取决于外面的查询。





type



表的连接类型,其性能由高到低排列为 system,const,eq_ref,ref,range,index 和 all。

  • system 表示表只有一行记录,相当于系统表。如下图所示,因为 from 的子查询派生的表只有一行数据,所以 primary 的表连接类型为 system。





  • const 通过索引一次就找到,只匹配一行数据,用于常数值比较PRIMARY KEY 或者 UNIQUE索引。





  • eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常用于主键或唯一索引扫描。对于每个来自前边的表的行组合,从该表中读取一行。它是除了 const 类型外最好的连接类型。





  • ref 非唯一性索引扫描,返回匹配某个单独值的所有行,和 eq_ref 的区别是索引是非唯一索引,具体案例如下所示。





  • range 只检查给定范围的行,使用一个索引来选择行,当使用 =, between, >, <, 和 in 等操作符,并使用常数比较关键列时。如下图所示,其中 id 为唯一索引,而 val 是非唯一索引。





  • index 与 ALL 类型类似,唯一区别就是只遍历索引树读取索引值,比 ALL 读取所有数据行要稍微快一些,因为索引文件通常比数据文件小。这里涉及 MySQL 的索引覆盖

  • ALL 全表扫描,通常情况下性能很差,应该避免。

possiblekeys,key 和 keylen



possible_key 列指出 MySQL 可能使用哪个索引在该表中查找。如果该列为 NULL,则没有使用相关索引。需要检查 where 子句条件来创建合适的索引提高查询效率。

key 列显示 MySQL 实际决定使用的索引。如果没有选择索引,则值为 NULL。

key_len 显示 MySQL 决定使用索引的长度。如果键为 NULL,则本列也为 NULL,使用的索引长度,在保证精确度的情况下,越短越好。因为越短,索引文件越小,需要的 I/O次数也越少。





由上图可以看出,对于 select * from t2 where id = 1 or val = 1这个语句,可以使用 PRIMARY 或者 idxt2val 索引,实际使用了 idxt2val 索引,索引的长度为5。

这些其实是我们分析加锁场景最为关心的字段,后续文章会具体讲解如何根据这些字段和其他工具一起判断复杂 SQL 到底加了哪些锁。

ref



ref 列表示使用其他表的哪个列或者常数来从表中选择行。如下图所示,从 t2 读取数据时,要判断 t2.id = t1.id,所以 ref 就是 mysql.t1.id





rows 和 filtered



rows 列显示 MySQL 认为它执行查询时必须检查的行数。

filtered 列表明了 SQL 语句执行后返回结果的行数占读取行数的百分比,值越大越好。MySQL 会使用 Table Filter 来读取出来的行数据进行过滤,理论上,读取出来的行等于返回结果的行数时效率最高,过滤的比率越多,效率越低。





如上图所示,t1表中有三条数据,rows 为 3,表示所有行都要读取出来。根据 val = 3 这个 table filter 过滤,只返回一行数据,所以 filtered 比例为33.33%,

extra



包含不适合在其他列中显示但十分重要的额外信息。常见的值如下

  • using index 表示 select 操作使用了覆盖索引,避免了访问表的数据行,效率不错。

  • using where 子句用于限制哪一行。也就是读取数据后使用了 Table Filter 进行过滤。





  • using filesort MySQL 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,若出现该值,应该优化 SQL 语句。如下图所示,其中 val 列没有索引,所以无法使用索引顺序排序读取。





  • using temporary 使用临时表保存中间结果,比如,MySQL 在对查询结果排序时使用临时表,常用于 order by 和 group by,如果出现该值,应该优化 SQL。根据我的经验,group by 一个无索引列,或者ORDER BY 或 GROUP BY 的列不是来自JOIN语句序列的第一个表,就会产生临时表。

  • using join buffer 使用连接缓存。如下图所示,展示了连接缓存和临时表。关于连接缓存的内容,大家可以自行查阅,后续有时间在写文章解释。





  • distinct 发现第一个匹配后,停止为当前的行组合搜索更多的行

后记



通过 explain 了解到 SQL 的执行计划后,我们不仅可以了解 SQL 执行时使用的索引,判断加锁场景,还可以针对其他信息对 SQL 进行优化分析,比如将 type 类型从 index 优化到 ref 等。

个人博客,欢迎来玩



发布于: 2020 年 06 月 15 日 阅读数: 58
用户头像

程序员历小冰

关注

程序员历小冰 2018.04.28 加入

历小冰的技术博客,专注于探讨后端生态的点点滴滴,内容包括微服务、分布式、数据库、性能调优和各类源码分析。

评论

发布
暂无评论
用 Explain 命令分析 MySQL 的 SQL 执行