乐趣区

关于数据库:Oceanbase-和-TiDB-粗浅对比之-执行计划

jiezi

一、前言

         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$plan\_cache\_plan\_explain/ 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 列展现的内容如下：
tikv\_task:{proc max:640ms, min:120ms, p80:260ms, p95:470ms, iters:4859, tasks:27}, scan\_detail: {total\_process\_keys: 4861956, total\_keys: 4861983, rocksdb: {delete\_skipped\_count: 420892, key\_skipped\_count: 4861956, block: {cache\_hit\_count: 202, read\_count: 18548, read\_byte: 493.0 MB}}}

         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$plan\_cache\_plan\_explain/ v$plan\_cache\_plan\_stat 也能够按条件过滤慢 SQL，不过查问时如果没有指定 ip\port\tenant\plan\_id 等条件是数据返回空行，即便 count(*) 整个基表表也是返回空。

         此外还能够通过 v$sql\_audit 视图查问会话和 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$plan\_cache\_plan\_explain/ 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/ti…

         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

退出移动版