MySQL Case-通过optimizer_trace看MySQL优化器行为

  欧陆资讯     |      2024-06-18 20:56

我们在日常维护数据库的时候,如果遇到慢语句查询的时候,我们一般会怎么做?执行EXPLAIN去查看它的执行计划?是的。我们经常会这么做,然后看到执行计划展示给我们的一些信息,告诉我们MySQL是如何执行语句的。但是,执行计划往往只给我们带来了最基础的分析信息,比如是否有使用索引,还有一些其他供我们分析的信息,比如使用了临时表、排序等等。
下面是此次案例的SQL文本:


下面是mariadb(版本5.5.64-MariaDB)执行计划:用xxx代替上述SQL


下面是MySQL5.7(版本5.7.32-log)执行计划:用xxx代替上述SQL


上述两个不同环境下,执行计划一摸一样,但是MariaDB执行0.02秒,MySQL5.7要执行5秒。
这个时候,下一步动作可能就要去看optimizer_trace寻找原因

OPTIMIZER_TRACE是什么呢?
它有点类似于Oracle的10053,会给你做简单版的transform,考虑给你做一定程度的优化,同时它也有跟踪功能,跟踪执行的语句的解析优化执行的过程,并将跟踪到的信息记录到INFORMATION_SCHEMA的OPTIMIZER_TRACE表中,可以通过optimizer_trace系统变量启停跟踪功能,MySQL从5.6开始提供了相关的功能,但是MySQL默认是关闭它的,我们在需要使用的时候才会手动去开启。

optimizer_trace可以是会话或者是全局开启,但是每个会话都只能跟踪它自己执行的语句,表中默认只记录最后一个查询的跟踪结果(表中记录的跟踪结果数可以通过optimizer_trace的参数设置)

可跟踪语句对象包括:SELECT/INSERT/REPLACE/UPDATE/DELETE、EXPLAIN、SET、DO、CALL、DECLARE CASE IF RETURN


optimizer_trace

  • enabled:启用/禁用optimizer_trace功能
  • one_line:决定了跟踪信息的存储方式,为on表示使用单行存储,否则以JSON树的标准展示形式存储。单行存储中跟踪结果中没有空格,造成可读性极差,但对于JSON解析器来说是可以解析的,将该参数打开唯一的优势就是节省空间,一般不建议开启

optimizer_trace_features:该变量中存储了跟踪信息中可控的打印项,可以通过调整该变量,控制在INFORMATION_SCHEMA.OPTIMIZER_TRACE表中的trace列需要打印的JSON项和不需要打印的JSON项。默认打开该参数下的所有项
optimizer_trace_max_mem_size :optimizer_trace内存的大小,如果跟踪信息超过这个大小,信息将会被截断
optimizer_trace_limit & optimizer_trace_offset

  • 这两个参数神似于SELECT语句中的“LIMIT offset, row_count”,optimizer_trace_limit 约束的是跟踪信息存储的个数,optimizer_trace_offset 则是约束偏移量。和 LIMIT 一样,optimizer_trace_offset 从0开始计算(最老的一个查询记录的偏移量为0)
  • optimizer_trace_offset 的正负值,不需要太过于去纠结,如下表所示,其实offset 0=offset -5 ,它们是一个等价的关系,仅仅是表述方式不同。这样的表述方式和python中的切片的表述是一致的,了解python的童鞋们都知道,切片的时候经常用到-1取列表中最后一个数值或者是反向取值。

抓取optimizer_trace步骤:


整理如下


抓取结果整体结构

整个OPTIMIZER_TRACE的重点就是TRACE的JSON树。TRACE中的JSON树大部分都又臭又长,个人更建议使用带有收缩代码格式的编辑器去围观这棵树,能更清晰地理顺这棵树,如下图所示,我们先来看看TRACE的大框架。

在TRACE的JSON中有三个步骤构成: join_preparation(准备阶段)、join_optimization(优化阶段)、join_execution(执行阶段)。



json代码如下



这里忽略掉...

此SQL的optimizer_trace的执行部分,存在几百次的相同的重复单元,如下:


说明嵌套查询几百次,主要耗时在这里。

将SQL改写成如下形式:


sql立即返回结果

作者:姚崇

Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证,擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库。