Oceanbase 和 TiDB 粗浅对比之 - 执行计划

一、前言

OceanBase 和 TiDB 作为国内 2 款的比较流行的兼容 MySQL 协议的开源数据库使用者也越来越多，两种数据库不仅在架构原理上有较大差异，在开源方式上有较大的不同：

TiDB 采用的 Apache License 2.0 开源协议，其第一行代码提交就是在 github 上，和企业版相比社区版只是不包含访问白名单和审计 2 个插件功能，其他与企业版完全相同且同步发版(之前闭源的 tiflash 也于 2022.4.1 完全开源)。

OceanBase 社区版采用国内的木兰公共协议 MulanPubL-2.0 开源，官方划分成社区版、云服务版、企业版三种类型，开源的社区版与企业版相比存在较多功能缺失或性能降低(如目前对比版本不支持 oracle 兼容、不支持闪回、不支持 analyze 语句、ocp 不支持备份功能等)，且社区版本版发布与企业版不同步。另外 ob 的文档和资源相比较 tidb 还不够完善和丰富。

本文针对 tidb、oceanbase 在执行计划的相关内容进行粗浅的对比，也对学习做个总结，对比版本为 OceanBase3.1.2-CE(2022-03-29 发版 )、TiDB v5.2.3(2021-12-31 发版)

二、查看执行计划

• TiDB：

(1)    explain SQL 方式：该方式只是展示可能的执行计划并非实际的执行计划，目前各数据库都存在此问题使用 explain 方式并不是真正 SQL 执行时的计划，少数情况下会存在不一致。

(2)    explain analyze 方式：该方式会真正执行 SQL 并展示执行时的执行计划，执行计划中增加实际的执行信息包括实际返回行数、各算子时间和调用及资源消耗等。

(3)    select tidb_decode_plan()方式: tidb 的慢 SQL 日志里会以 hash 值方式记录慢 SQL 的执行计划，然后使用 tidb_decode_plan()函数即可解析。

(4)    dashboard 查看：tidb 的 PD 组件包含 dashboard 功能，慢 SQL、SQL 统计页面可以查看每个 SQL 的执行计划

• OceanBase:

(1)    explain SQL 方式：包含 BASIC、OUTLINE、EXTENDED、EXTENDED_NOADDR、PARTITIONS、FORMAT = {TRADITIONAL| JSON}多个展示选项，除了 extended 方式大部分情况展示的内容基本一致，extended 方式时会增加 hint、outline、plan type、optimizerinfo 等信息。

(2)    使用系统视图方式：oceanbase 在实现上一直努力方便 oracle dba 使用，通过 v$pla{n}_{c}ach{e}_{p}la{n}_{e}xplain/v$plan_cache_plan_stat 等视图可以查看执行计划及算子的执信息(如行数、时间等)，类似 oracle 的 v$sql、v$sql_plan 等视图

(3)    因未部署 ob 图形化管理平台 ocp，因此未看 SQL 执行计划的页面展示。

三、执行计划内容

• TiDB:

TiDB 的执行计划展示与 oracle 类似，以缩进的方式展示算子间的层次关系，同时使用折线进行算子连接展示，当 SQL 复杂执行步骤较多时可以很明显看出处于同一缩进深度的算子，explian 方式下执行计划包括算子信息(id 列)、预估行数(estRows 列)，访问对象(access object 列)、过滤条件和操作信息(operator info 列)


使用 explain analyze 或查看慢 SQL 中记录的执行计划时还包括每个算子实际的返回行数(actRows 列)、算子的执行时间和分布统计(execution info 列)、内存占用(memory)、磁盘读(dsik)

execution info 列展示的内容如下：

execution info 因为是和每个算子展示成一行，且信息较多输出时较多换行，对执行计划阅读有些影响，如果能放到下面进行额外展示的话，就能使执行计划步骤展示看起来更方便些。

• OceanBase:

Oceanbase 将执行计划划分为了本地执行计划、远程执行计划、分布式执行计划。执行计划展示非常接近 oracle 的展示方式，explain basic 下展示执行计划和 output&filter。 树形执行计划中包括算子展示 id、算子内容(OPERATOR 列)、访问的对象信息(NAME 列)、预估行数(EST. ROWS)、评估的成本(COST)。output&filter 展示的列过滤和投影后列信息，相比 oracle 展示的内容没有 access 信息，且列值可读性差。


在 exteneded 方式下还包括 SQL 使用 HINT、SQL 执行生产的 outline(outline 部分基本和 oracle 一致)、优化器的执行信息 optimizer info。


Oceanbase 中关于路径访问的算子较少，目前只有 TABLE GET(直接主键定位)、TABLE SCAN(全表或索引扫描回表)、LOOKUP TABLE(全局索引扫描回表)，针对执行计划中是否使用索引需要结合 name 列是否有索引以及 filter 中 is_index_back=true 判断，对于扫描方式不够直接和方便，比如索引全扫描、索引范围扫描、是否使用覆盖索引等。对于分区信息的显示 ocenbase 和 oracle 一样展示的分区 partition id，tidb 内展示的是分区名更直观一些。

四、慢 SQL 记录

• TiDB:

超过 slow_launch_time 参数值的 SQL 会被记录到 tidb_slow_query.log。可通过 information_schema.CLUSTER_SLOW_QUERY 或 dashboard 查看。

• Oceanbase:

执行时间超过 trace_log_slow_query_watermark 参数值设置的会记录到 observer.log。

使用视图 v$pla{n}_{c}ach{e}_{p}la{n}_{e}xplain/v$plan_cache_plan_stat 也可以按条件过滤慢 SQL，不过查询时如果没有指定 ip\port\tenant\plan_id 等条件是数据返回空行，即使 count(*)整个基表表也是返回空。

此外还可以通过 v$sq{l}_{a}udit视图查询会话和SQL执行信息，其类似于oracle的v$session 视图。

五、HINT

对于 hint 使用 OceanBase 和 tidb 的方式基本一样，oceanbase 中除了常规的 hint 外，还可以像 oracle 一样使用 outline data 作为 hint 内容。

六、执行计划绑定

• TiDB:

TiDB 执行计划绑定功能叫 SPM(sql plan managment)包括手动绑定执行计划、自动捕获执行计划和演进功能。执行 SQL 绑定时会将 SQL 进行标准化进行变量值的替换和空格转换等，在执行 SQL 时会将 SQL 进行标准化，与标准化后的 SQL 进行比对，如果一直则使用绑定的执行计划。TiDB 中绑定 SQL 与原始 SQL 大小写不一致、空格换行不一致等不影响绑定使用。TiDB 内不能使用 SQL_digest/plan_digest 等 hash 值方式进行 SQL 绑定，在创建和删除绑定时都必须使用原始 SQL 和 HINT SQL，对于较长的复杂 SQL 不是很方便。

执行计划绑定详细信息可参考官方文档和专栏文章：https://tidb.io/blog/83b454f1

• OceanBase:

Oceanbase 的执行计划绑定可使用 2 种方式，2 个从概念上都参考了 oracle，一个是使用 outline 方式进行执行计划绑定，一个是使用 SPM 方式进行绑定和执行计划捕获和演进(开源版不支持 SPM)。Outline 使用方式和 tidb 创建 SQL binding 类似都是使用 HINT SQL 和原始 SQL 绑定，不过 oceanbase 的 SQL 绑定严格要求原始 SQL 和 HINT SQL 必须完全一致（类似 oracle 的 sql_id 计算），大小写和空格对绑定有影响。Oceanbase 支持使用 SQL_ID、PLAN_ID 的值进行执行计划绑定，方便绑定操作。

无论 TiDB 还是 OceanBase 两个都不支持 HINT SQL 使用 force index 类提示绑定执行计划。


Oceanbase 的 SPM 执行计划管理和 oracle 非常类似，都是使用 dbms_spm 包进行管理，其语法基本一致，同样通过几个参数控制是否进行自动绑定和演进。

七、执行计划缓存

• TiDB:

使用 Prepare/execute 方式，Prepare 时将参数化的 SQL 查询解析成 AST（抽象语法树），每次 Execute 时根据保存的 AST 和具体的参数值生成执行计划，对于 Prepare 的语句在第一次 execute 时会检查该语句是否可以使用执行计划缓存(比如包含分区表、子查询的语句不能缓存)，如果可以则将语句执行计划放入到缓存中，后续的 execute 会首先检查缓存中是否有执行计划可用，有的话则进行合法性检查，通过后使用缓存的执行计划，否则重新生成执行计划放入到缓存中。

缓存是 session 级的，以 LRU 链表方式管理，链表元素为 kv 对，key 由库名、prepare 语句标识、schema 版本、SQL_Mode、timezone 组成，value 是执行计划。通过 prepared-plan-cache 下的相关选项可以控制是否启用缓存、缓存条目数和占内存大小。

• OceanBase:

Oceanbase 内除了可以使用 prepare 方式外，oceanbase 对执行计划缓存参照 oracle 做了大量工作。和 Oracle rac 类似每个 observer 只管理自己节点上的缓存，不同节点相同 SQL 缓存的执行计划可能不同。

Oceanbase 将 SQL 文本进行参数化处理后作为执行计划缓存的键值 key，value 是执行计划。Oceanbase 的 SQL 匹配也参考了 oracle，引入了 cursor_sharing 参数和 HINT，参数值为 excat 要求 SQL 匹配必须完全一样，包括空格、大小写、字段值等。参数值为 force 时则以参数化后的 SQL 进行匹配。

除此之外 ocenabase 也引入了自适应游标共享 ACS 功能，针对一个 SQL 在使用不同字段值时使用不同的执行计划，通过参数可控制是否开启该功能。

缓存的执行计划可通过通过 v$pla{n}_{c}ach{e}_{p}la{n}_{e}xplain/v$plan_cache_plan_stat 查看。

开源版不支持 cursor_sharing 和 ACS 功能。

八、统计信息

• TiDB:

tidb 统计信息收集包括自动统计信息收集和手动统计信息收集。自动统计信息收集根据表的情况和参数 tidb_auto_analyze_start_time/tidb_evolve_plan_task_end_time/ tidb_auto_analyze_ratio 决定何时进行统计信息收集。手动统计信息收集根据需要随时执行 analyze SQL。

TiDB 支持 feedback 特性，即在 SQL 执行时根据实际的执行信息去更新统计信息，以使统计信息根据准确和及时更新，不过由于 feedback 特性会导致一些问题，改特性默认为关闭。Oracle 数据库在 11g 引入该特性时也引起一些问题，大部分情况 DBA 会将该功能关闭。

Tidb 内的统计信息可以使用 show stats_meta/stats_buckets/stats_histograms 等查看。

关于统计信息收集的更详细收集可参考：https://tidb.io/blog/92447a59

• OceanBase:

Oceanbase 社区版不支持 analyze 语句收集统计信息(商业版 3.2 才引入)，存储层进行合并时更新统计信息，可以手工触发合并操作进行更新。SQL 执行时从 memtable 进行动态采样，采样比例固定，无法更改。

相关统计信息可从_all_table_stat，__all_column_stat, __all_histogram_stat 等系统视图查看。


九、SQL trace

• TiDB:

tidb 直接使用 trace SQL 执行即可展示 trace 结果。Operation 列展示函数调用层次和访问的 region 信息，startTS 了展示该步的开始时间，duartion 展示该步的消耗时间。


• OceanBase:

OceanBase 的 trace 使用类似和结果类似于 mysql 的 Profiling。执行过程如下：

(1)    开启 trace: SET ob_enable_trace_log = 1;

(2)    执行 SQL

(3)    Show trace 查看，然后 SET ob_enable_trace_log =0 关闭

从展示结果上看其信息的直观性和可用性上不如 tidb。


十、遇到的问题

• TiDB:

(1)    执行计划中不显示不显示子查询的表信息，无法判断使用的扫描方式

该问题目前暂未完成修复: https://github.com/pingcap/tidb/issues/22076

oceanbase 执行计划如下：


• OceanBase:

(1)    对于子查询中不存在的列不会报错仍然继续执行

Tidb 执行如下：


(2)    Oceanbase 无法使用索引

按 id 列进行小范围查询时无法使用 id 列索引，执行手工合并后仍然是全表扫描执行计划。


tidb 执行计划：


(3)    不同的 index hint 方式导致执行计划不同



TIDB 执行计划：


(4)    explian 展示的执行计划不能使用绑定后的 Outline ，数据字典内记录的执行计划使用了索引



Tidb 执行计划：


(5)    执行 Prepare 后会导致会话断开，再次执行后成功，对于交互式客户端 oceanbase 不支持显示查询结果。



TiDB 执行计划：


(6)    Obproxy 可能会和多个后端 observer 建立连接，导致相同会话执行的慢 SQL 会被记录到多个 observer 的 observer.log 内(ob 内使用数据字典查询慢 SQL 信息会更好些)。

十一、总结

个人认为从功能上看 oceanbase 的执行计划管理要 TiDB 更丰富些，如 SPM、ACS 等，但从实际使用看无论是操作的复杂性、执行计划的可读性、优化器的可靠性都要由于 oceanbase。Oceanbase 在各方面在努力的向 oracle 兼容，比如系统视图、SPM 管理、自适应游标共享、等待事件等，因架构不同、经验积累等和 oracle 比还是有着不小的差距。

针对 TiDB 建议如下:

(1)    执行计划绑定管理可以使用 sql_digest、plan_digest 等，可避免使用 SQL 语句

(2)    执行计划缓存做成全局管理方式，避免多个会话对相同 SQL 进行缓存，浪费内存空间

(3)    Explain analyze 的 execution info 在执行计划下面独立展示，否则执行计划太长不方便阅读。

作者：@h5n1 发布时间：2022/4/12

原文链接：https://tidb.net/blog/f1fd1733