当前位置: 代码迷 >> 综合 >> 如何对 OceanBase 进行 SQL 诊断和调优
  详细解决方案

如何对 OceanBase 进行 SQL 诊断和调优

热度:25   发布时间:2024-01-17 11:32:35.0
作者简介: 田逸飞(义博):OceanBase高级开发工程师

一、SQL 执行流程

SQL 发送到 OBServer 后,会先由 OBServer 对其进行快速参数化,参数化后的 SQL 进入 Plan Cache 尝试命中计划缓存。

如果找到一个可以使用的计划,则直接将计划交由 SQL 的执行引擎去执行,并将执行完成后的结果返回给用户;如果没有找到可以使用的计划,则会重新为此 SQL 生成计划,完整地执行 SQL 的 Parser、Resolver、Transformer、Optimizer、Code Generator 流程,然后生成一个可用的物理计划,并交由执行引擎执行,同时此计划会被加入到计划缓存,以便后续的 SQL 重新使用。

流程中的 Parser 主要负责语法词法的解析,它会将用户输入的 SQL 基于 lax 和 yacc 生成一个Parse Node Tree,如上图右侧所示,它将用户的 SQL 拆成了一个树状结构,同时做了一些语法解析。使用 OB 时候的语法报错通常就是在 Parser层。

 Resolver 负责对 Parse Node Tree 做语义的分析,主要包括语句的解析、中缀表达式的生成、表达式的类型推导等,并最终将其转化成 OB 在代码中更易于操作的数据结构。

Transformer 负责在确保执行结果相同且正确的情况下,对 SQL 做等价的变换,将其转化成一个对于数据库来说更友好的 SQL 。

举个例子,假设用户写了一个外连接为 L LEFT JOIN R ON L.ID=R.ID ,同时在 R.C2 列上有一个过滤条件 R.C2=XXX。对于外连接来说,如果左侧的一行没能连接到右侧的任意一行,则会输出左侧的一行,并且在右侧列上补 NULL 。

但是因为 where 里面的过滤条件,所有补 NULL 产生的 R 全部会被过滤掉,所以它的效果等价于 inner join 。因此,在这种场景下,Transformer 会把外连接改成内连接。上述全部改动都是基于OB statement 结构做的,而不是直接去改用户的 SQL 。

当 Transformer 将statement 进行了等价的变换之后,statement 会被交给 Optimizer 做查询的优化。Optimizer 优化器主要负责为 SQL 生成一组可选的逻辑计划,并从中选出一个优化器认为最优的计划。生成计划的过程会涉及到路径的选择、连接顺序的选择、连接算法的选择,以及一些分布式计划的生成等。

选出最优计划之后,即得到了一个逻辑计划。但是逻辑计划是无法直接用来执行的,而是需要一个 Code Generator 模块将其翻译成执行引擎能够识别执行的物理计划。Code Generator 会遍历整个逻辑计划,将其中的每一个逻辑算子翻译成物理算[MOU1] 子,最后物理计划会被交给执行引擎实际执行。

OceanBase 的执行引擎使用的是火山模型,它是数据库系统里面一个非常经典且成熟的执行模型。

二、计划缓存

上图是 SQL 计划的获取过程。

前文提到,SQL 会尝试去命中计划缓存,如果能够命中,则直接执行;否则,需要执行一系列流程去生成一个最终的执行计划。

而这一系列流程比较耗时,大概需要毫秒级甚至更久。但是如果走计划缓存,耗时只需要几十到几百微秒。大多数场景下,一条 SQL 完整地执行下来只需要几毫秒,而如果生成计划就已经占了几毫秒,对业务来说是完全无法接受的。

因此,减少这一部分的开销至关重要。

 OceanBase 的计划缓存有两种匹配模式。

①   Force 模式,也是 OB 默认的匹配模式。 

    Force 模式下,首先会对 SQL 里面的常量进行参数化,再对参数化后的计划进行匹配。

    什么是参数化呢?如上图右侧SQL语句, select c1,c2 from t1 where c1=1,这里的1 是一个常量。参数化就是将常量替换成一个通配符“?”。所以参数化之后,这条 SQL 就变成了 select c1,c2 from t1 where c1=?。

参数化之后,文本与此条 SQL 一样的 SQL 都可以共享一个计划。比如这里 where C1=1、C1=2、C1=3的语句都可以共用同一个执行计划。但它的不足之处在于 SQL 命中的计划可能并不是一个最优的执行计划。

依然以上述 SQL 为例,假设对于 t1 中,c1=1 的值占了1%,c1=0 的值占了99%,即表里的c1 只有 1 和 0 两个取值。

假设一开始计划缓存是空,先来了一个 c1=1 的 SQL,就会用这条 SQL 去生成计划。如果c1=1 的过滤性非常好的,则会选一个走 c1 索引的计划,相当于在 c1 的索引上做一次扫描,然后做索引回表,并将计划缓存到计划缓存中。但是如果之后来了一个 c1=0 的 SQL,那么它就会命中刚刚生成的计划走索引扫描。但是对于 c1=0的条件,索引其实不是最优的,

因为它不能过滤掉大量数据。索引扫描完成后它还有 99% 的数据要做索引回表。在这种场景下,索引往往不如基表扫描快。

②   Exact 模式。

Exact模式下,做计划匹配的时候不会做参数化,而是直接用原始的 SQL 去做匹配,匹配的时候要求 SQL 的文本完全相同。比如大小写、空格的数量以及参数都要相同才能共享计划。

它的优点在于不同的参数都能选到自己最优的计划。不足之处也非常明显——计划的命中率非常低。比如通过主键去查找表里某一行具体的数据,主键在表中的每一个值都是不同的,也就意味着它们都无法共享计划,每一个主键值都要重新生成一次计划,这就导致计划的命中率非常低。而且大量的计划会导致计划缓存的内存会大幅膨胀。

因此,一般情况下,推荐使用 force 模式。

可以通过设置系统变量 cursor_sharing=exact/force 来控制选择哪个模式,也可以通过 Hint:/*CURSOR_SHARING_EXACT*/ 去强制某一个 SQL 使用 exact 模式。

上图展示了一些计划共享的例子。

第一组 SQL 参数化之后都会变成 select c1,c2 from t1 where c1=? and c2 =?。它们参数化后的 SQL 文本是相同的,因此可以共享计划。

第二组 SQL 参数化之后变成了select c1,c2 from t1 where c1=? order by ?。

但实际上它们的语义是不一样的,第一条 order by 1,意味着要对 select 里的第一列做排序,而第二条意味着要对 select 里的第二列做排序。如果它们共享计划,两个结果一定会出现问题。

为了防止这样错误的共享计划,OceanBase 提供了一个添加约束的方式。比如对于第一条 SQL ,要求 order by=1 的时候才能使用它生成的计划,同理对于第二条 SQL 会要求 order by=2 的时候才能使用它生成的计划。

第三组 SQL 参数化之后会变成select c1,c2 from t1 where c1=? and ? = ?。而它在 OB 中也是无法共享计划的。因为1 = 1是一个恒true的条件,而 1 = 2 是一个恒 false 的条件。对于这种恒true、恒false 的条件,OB在改写优化的过程中会对其做一些特殊处理,简化这些计划的生成过程和生成的计划。一旦不同的恒true、恒false条件共享了计划,也会导致一些结果错误。

对于上述场景, OceanBase 也会抽取出约束。比如对于第一条 SQL,它会要求第二个参数等于第三个参数的计算结果是 true 的时候才能共享这条 SQL 产生生成的计划;对于第二条SQL,它会要求第二个参数等于第三个参数的计算结果是 false 的时候才能共享第二条 SQL 产生的计划。

第四组 SQL 的问题在于 select 的大小写不同&