专栏首页程序员历小冰用 Explain 命令分析 MySQL 的 SQL 执行

用 Explain 命令分析 MySQL 的 SQL 执行

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

比如下面这样的 SQL:

其中 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 类型外最好的连接类型。

如下图所示,对表 t1 查询的 type 是 ALL,表示全表扫描,然后 t1 中每一行数据都来跟 t2.id 这个主键索引进行对比,所以 t2 表的查询就是 eq_ref。

  • 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 进行过滤。

如下图所示,因为 id 和 val 都是有索引的,所以 select * 也是可以直接使用覆盖索引读取数据,所以 extra 中有 using index。而因为只使用 val 索引读取了3行数据,还是通过 where 子句进行过滤,filtered为 55%,所以 extra 中使用了 using where。

  • 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 等。

本文分享自微信公众号 - 程序员历小冰(gh_bcc90a2a52c5),作者:历小冰

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql探索(一):B-Tree索引

    MySQL是目前业界最为流行的关系型数据库之一,而索引的优化也是数据库性能优化的关键之一。所以,充分地了解MySQL索引有助于提升开发人员对MySQL数...

    程序员历小冰
  • 用 Explain 命令分析 MySQL 的 SQL 执行

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

    程序员历小冰
  • MySQL探秘(七):InnoDB行锁算法

     在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

    程序员历小冰
  • MySQL见闻录 - 入门之旅(五)

    天天听人家说 ”查询优化“,以前用sqlite的时候总是不能理解,优化啥?不就那么些语句嘛。 入门MySQL之初,老师讲过一些,大致有点了解。入门(二)的时候...

    看、未来
  • EF Core索引

    索引是跨多个数据存储区的常见概念。 尽管它们在数据存储中的实现可能会有所不同,但也可用于基于列(或一组列)更高效地进行查找。

    跟着阿笨一起玩NET
  • 一条SQL的奇妙旅行

    工作中我们经常查询数据库,用一个查询,得到想要的数据。可有想过,我们得到答案经过了哪些磨难?经历了哪些诱惑?

    ITester软件测试小栈
  • Java面试高频知识点汇总 数据库专题

    MySQL的默认存储引擎是InnoDB,并且在5.7版本的所有存储引擎中只有InnoDB是事务性存储引擎,也就是说只有InnoDB支持事务。

    Steve Wang
  • 你真的知道如何优化SQL么?

    虽然使用Explain不能够马上调优我们的SQL,它也不能给予我们一些调整建议,但是它能够让我们了解MySQL 优化器是如何执行SQL 语句的

    Java学习录
  • DELETE选错执行计划的困境

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    bisal
  • 98%的人不知道的MySQL优化器原理

    | 作者 梁东阳,数据库研发中心数据库内核工程师,负责腾讯云MySQL的内核开发。 ---- 在日常运维中,相信不少人都收藏了很多关于查询优化的方法论和小技巧...

    腾讯云数据库 TencentDB

扫码关注云+社区

领取腾讯云代金券