从TP到AP:OceanBase SQL引擎的探索和实践

2024年 5月 7日 78.2k 0

3月25日,我们在北京举办了首届「OceanBase 开发者大会」,与开发者共同探讨单机分布式、云原生、HTAP 等数据库前沿趋势,分享全新的产品 Roadmap,交流场景探索和最佳实践。在「产品技术专场」,OceanBase 技术部高级专家朱涛,为大家带来《从 TP 到 AP : OceanBase SQL 引擎的探索和实践》享,以下内容根据演讲实录整理而成。

首先,介绍一下 OceanBase 引擎长什么样,做什么事。其次,OceanBase 一开始是服务蚂蚁内部业务的,我们在 TP 互联网场景层面做了哪些事情让 SQL 跑得更快,才能能服务好这种场景?

从TP到AP:OceanBase SQL引擎的探索和实践-1

之后,OceanBase 开始商业化,逐步服务一些外部客户,包括金融、证券、运营商、保险等行业。当走到外部的时候,我们发现外部 SQL 使用的模式和蚂蚁内部的差异非常大,这对整个 SQL 的执行和优化产生了新的挑战。在面对新的业务场景、 SQL 场景,我们又做了哪些事情?

最后,随着整个 OceanBase TP 能力不断强化和优化,执行能力不断积累,我们逐步开始具备了一定的 AP 处理能力,那么我们在 AP 场景下做了哪些事情和优化?

01. OceanBase SQL 引擎概要介绍

这是 OceanBase SQL 引擎的整体框架,可以看到,当一个查询进入 OceanBase 之后,首先会进入解析模块,这个模块本质上是内核去理解查询请求到底要做什么,理解需求是什么。

充分理解需求后,会进入优化环节,优化器会从众多实现方法中,选出它认为最好的方法交给执行器。

执行器包含了去实操这个方法所有的细节,忠实地根据优化器给出的建议方案把查询做下来,并把最终结果反馈给客户。

从TP到AP:OceanBase SQL引擎的探索和实践-2

举一个不算特别恰当,但有助于理解的例子:

我从上海出差到北京的某个酒店,第二天来参加 OceanBase 开发者大会,那进入优化环节会做什么?

从上海到北京,路径上有两种方案,高铁或飞机;到达北京,从枢纽站到入驻酒店,可以坐地铁或打车;同样,从上海出发,从家到枢纽站也可以坐地铁或打车。整个查询生成了众多执行方案,到底哪个方案比较好,优化器会有一套代价评估系统去评估。

落到这个 Case 很简单,看整体的耗时,比如从出发点出发,在高峰期打车更舒服,但堵车耗时不可控,地铁会更快,所以选择坐地铁到枢纽站;一般而言,飞机飞北京比坐高铁更快,所以选择飞机的路线,从机场起飞到北京;可能我到北京的时间很晚,虽然理论上可以坐地铁,但末班车已经错过,所以选择打车;整个环节,优化器给出一条可选的较优的方案:坐地铁到机场,机场航班到北京,北京打车到酒店。

这个过程,优化器有两个非常重要的点:

第一,枚举计划能力要强。能举出多种不同的优势方案,优化层面叫做计划空间,方案越多,找到更快的执行策略的潜在可能越大;

第二,选计划能力要准。方案不是越多越好,需要有准确的代价模型去判断,哪一个方案更优,避免错误选择。

谈完优化看执行,执行会忠实地完成选择的优化方案。比如,刚才这条路径,包含执行过程中的所有细节:坐地铁要购票、侯车、出发,到站后开始飞机的流程,值机、飞行、落地……对执行来说,关键的是什么?

第一,要有一个好的实现,每个环节要可能的高效,缩短每个环节的时间,整体时间就可以大大缩短。

第二,要设计出不同的交通方式,比如要能造出高铁,让优化路径能走高铁路线,要造出飞机,让优化器能选飞机路线,或者对这个场景,设计出更牛逼的交通方式让优化器能选,要丰富它的执行模式,以上就是 SQL 引擎的概况。

那么,走到具体的业务场景,我们遇到的挑战是什么?

02. 让互联网 TP SQL 跑的快

第一部分,跟大家分享互联网场景(蚂蚁内部的一个TP场景),互联网场景面临的挑战是什么?

这些年,我服务于 OceanBase,观察蚂蚁的业务使用方式,发现绝大多数互联网 SQL 对数据库发起的请求都是单表短查询,对单表短查询而言,关键点就一个:选准索引,索引选准了,基本所有问题就都解决了。

一个短查询,比如要读取一百行数据,就要选准一个索引,实际可能就读一百多行,就可以把查询结果读出来;如果走错了索引,要读一万行才能找出这一百行,这个执行计划是不被接受的。可以这么理解,假如系统每秒最多读取 200万行数据,比如读一百行就得到查询结果,理论上就是 200 万行除以一百,达到这样的 QPS ;如果要读一万行才能查询到结果,就是 200 万除一万,是另外一个低很多的 QPS 。做好单表短查询优化,看似简单,实际是有难度的。

从TP到AP:OceanBase SQL引擎的探索和实践-3

分享两个案例(如上图),第一个案例是拉的内部的一个真实场景,一张表上有30多个索引,从优化的角度意味着,当我要去查这张表的时候,有 30 多种不同的执行可能性,要在这中间找到一个合适的索引,是有一定难度的。

第二个案例,也是两条单表短查询,但是它的谓词,或者 SQL 写法更复杂些。第一条可以看到它写的不是 and 条件,它写的是 or 谓词,作用在两个不同的列上,可能这两个列都有索引,但是针对这个查询不做任何优化,其实没有索引可走。

下一个查询相对复杂一点,做了过滤之后,过滤条件、过滤性可能很差,可能读出来的行数非常多,但是为了保证最终读取的数据很多,业务会做分页,按照 gmt_create 字段去排序,查出前 20 行先看一看,这个查询如果要做得好,最好的办法是 WHERE、 ORDER BY、Limit 这三部分全部压到索引上,全部利用索引优化,准确地从某个起点开始扫二十行,然后把结果返出去。

针对这两个场景,从运维测怎么解?绑索引,每条 SQL 把索引绑死就解决了,优化器选不中,就人为来选,这是绑索引的方法。

针对案例二,要去用一些 Hint 就没那么容易了,要想这种查询跑的快,可能第一条查询,业务要自己改,把 OR 的 SQL 一拆二,拆成两条,拆成 UNION 后分别走索引。第二个也是一样,STATUS IN 这个条件也要一拆二,分别取出 gmt_create 前 20 行,规并之后再取前 20 行,就可以达到效果。改 SQL 是一个比较麻烦的问题,尤其从运维角度,数据搞不定,推给业务程序去搞,业务程序肯定会 diss 你:怎么回事,这都搞不定!

OceanBase 对这种场景,包括索引选择,完全可以做到自动化,大部分场景都可以选准,我们会做索引的裁减,对一个查询而言,很多索引其实根本不相关,先把它全部裁掉,30 个索引先变 5 个索引,5 个索引进入选择范围,然后再去做一些代价评估,找出比较合适的索引,作为最终的索引。

对案例二的查询也简单,不就是改 SQL ?业务不想改,我来改,内核优化器里有改写模块,改写模块会准确的把这个 SQL 翻译出来,OR 条件翻译成 UNION,第二条 ORDER BY Limit 也可以拆成 UNION 去做,可以做到自动改写。

还有一种情况,不排除优化器在选索引的时候,可能会选错。因为优化器是根据一些统计信息或历史数据做选择,当数据不真实时,是可能会犯错的,怎么办?我们有自动汰换策略,当发现这条 SQL 的执行计划多次使用之后,性能不对,就会把执行计划淘汰掉,重新生成。

互联网的 SQL 相对来说,使用模式比较单一,可能从业务层面就会做一些使用方式的控制,我就不做更多展开了。接下来,看一下当 OceanBase 走出蚂蚁,去服务一些保险、运营商等客户时,我们见到 SQL 是什么样的。

03. 让传统业务 TP SQL 跑的快

多表(外)连接 – 连接次序至关重要

分享的第一个场景,是一个连接的场景,互联网场景很多时候会人为限制,发的 SQL 就访问一张表,但其实更多行业的业务系统是不会做限制的,SQL 可能会有很多张表连接在一起,连接之后得到一个结果,可能最终结果不大,但表的连接特别多。我见到比较复杂的场景是一条 SQL 里 17 张表做外连接,对这种多表的外连接,要让它的性能跑得快,光选准索引已经没用,还有一个很重要的点:连接次序一定要是比较好的。

从TP到AP:OceanBase SQL引擎的探索和实践-4

什么叫连接次序比较好?

举个例子(如上图), 4 张表做连接 T1、T2、T3、T4,其中 T2 是大表,其它都是小表,如果严格按照连接次序来做,如左上图,T1 跟 T2 连,再跟 T3、T4 连接,中间路径三次连接都是小表和大表连接,开销相对较高。这个 Case 想要做好也简单,把T2的连接往后移,T1 先和几张小表连,连完再和大表连,如右图,性能会好很多。

从运维侧去解,还是改 SQL,人为调外连接写的次序,其实从业务侧还是比较难改的,需要判定哪些是大表,哪些往后移,哪些往前放,整个过程比较复杂,尤其 17 张表本身就很难判定。

是否可以像刚才索引那样绑 Hint,让优化器走到这种路径,也不一定。很多数据库系统支持内连接交换,但不支持外连接,因为外连接的行为更复杂,有些性质是不满足的,很多场景不能交换,所以很多数据库的优化其实根本不具备这样的能力,去产生右图二这样的计划,所以更别谈用 Hint 去指导它,走到这个执行计划上。

如今,OceanBase 实现了一套非常完备的连接枚举算法,可以调整内连接和外连接的次序,可以调整外连接、反连接、半连接,甚至还可以改变一个外连接的连接类型,把它变成内连接或反连接等,可以做到场景自动优化。

无处不在的子查询

分享的第二个场景是子查询,子查询很有意思,从业务角度很喜欢写,因为语义特别直观。

从TP到AP:OceanBase SQL引擎的探索和实践-5

我看到外部业务,子查询使用模式其实更复杂,一是子查询出现的位置更奇怪,常见的子查询会出现在 FROM 或 WHERE 里面,但如案例一,子查询是出现在赋值操作的右值上,一个值等于另一个子查询计算出来的结果。案例二的子查询是出现在 WHERE 里,但还是有一些变化,不是直接出现在 WHERE 里的根谓词,而是嵌在 OR 条件里,如果它是一个根谓词,很多数据库都做了优化,把 EXISTS 子查询变成一个半连接,但嵌在 OR 里是做不了的。案例三更复杂了,写了一大堆子查询,子查询出现在投影里,这还是一个简化案例,就已经六个子查询了。

从业务侧,如何解决这种查询慢的问题?

还是改 SQL,一般来说,子查询的语义跟连接很接近,子查询有可能改成连接,但要保证业务改对,还是有难度,因为两者语义还是有细微差别,容易改错,这也不是特别现实的做法。

OceanBase 查询改写模块,实现了丰富的子查询优化策略,只要不是嵌套非常深的子查询,都可以把它变成连接,变成连接后,就成了连接枚举的问题,可以采取不同的连接算法去优化它。

案例三还有一个比较有意思的点,这个场景里面写的 S1、S2、S3,这三个子查询结构是非常相同的,读的是一个表, WHERE 条件也一样,只是投影项计算的函数不同,对于 OceanBase 而言,把它变成连接后,还可以做一个更极端的优化,把三个子查询合成一个,这部分的计算直接由三倍变成一倍,性能会更好。

04. 让 AP 业务 SQL 跑的快

随着 OceanBase 服务不同场景的 TP 优化,我们逐渐积累了很多优化能力和执行能力。从内核来说, AP 和 TP 没有太大实质性的区别,比如 TP 里出现很多表的连接, AP 里面同样会出现;当做了连接枚举的优化,对 AP 场景一样适用。所以随着 TP 服务的场景越来越多,优化能力和执行能力足够强了,我们可以开始做一些 AP 的探索和实践。

接下来,讲一下——

  • AP 场景的 SQL 大概是什么样?
  • 遇到的挑战是什么?
  • 我们做了哪些针对性的优化?

数据倾斜/负载倾斜 – 协同工作,拒绝摸鱼

第一个场景,跟大家分享一个最典型 AP 场景。

AP SQL 特征是什么?读取数据量、计算量特别大,一个线程搞不定,最直观的方法是开多个线程一起并行跑,如果一个线程要跑一个小时,开 10 个线程是不是 6 分钟就跑完了,假如每个线程都兢兢业业,是可以达到线性效率,但实际做不到,因为如果数据倾斜、负载倾斜,10 个线程里,只有 1 个线程拼命干活,其他 9 个都在围观、摸鱼,跑下来还是一个小时。

从TP到AP:OceanBase SQL引擎的探索和实践-6

分享一个运营商的数据倾斜的实际案例,帮大家理解这个问题。这是一个大表,有数十个分区,某个分区上存在一个大账号,它的数据达到了总体的 90%,这边我画了结果集,有一行记录数特别多,大概 2300 多万行,我要统计每分区某个字段上不同值的个数,开一组线程,每个线程去计算部分分区的结果,计算完了汇总起来就完事了。可以想象并行化跑的效果,对于一些小分区,线程很早就做完并汇报上去,但负责大分区的线程要算很久,其它分区都在等着这个线程结束。这种场景加并行没有用,不会得到收益,因为没有让每个线程都干起活来。

从运维去解决这个问题,我写了一条,调整分区模式和 SQL,理论上可行,但对每个场景的操作方法是不一样的,真正实操不太可行。真正要做的话,要从内核层面对数据倾斜、负载倾斜的场景做并行化算法的优化,要想办法把计算任务切细、切均匀,分散给每个线程,真正调用每个线程的能力,才可能把查询性能做得更快。

上面举的是分组的例子,除此之外,我们对于扫表,连接,窗口函数计算,包括增删改的并行化之后的数据倾斜、负载倾斜,也都做了针对性的优化。整体上达到什么效果?对这种计算任务,跑得慢没关系,加并行好了,一个不够加两个,两个不够加三个,总会跑得更快。

大表聚合 – “聪明”的执行引擎

下面是一个大表聚合的场景,这个场景业务 SQL 也很常见,我们做一些报表,很多时候会扫一张大表,算一张聚合结果汇报给老板看今天的业绩如何。

从TP到AP:OceanBase SQL引擎的探索和实践-7

大表聚合,通常数据量非常大,要做聚合操作,一个常见的优化叫做预聚合,就是把大表的数据拆成很多份,让每个线程分别去做一些分组聚合,得到局部分组聚合结果,然后这些线程再交换数据,把同一分组的结果交给一个线程,每个线程再去统一汇总,得到最终结果往上报,这是很典型的优化。

好处是什么?假设我现在有 10 亿行数据,最终可能有 100 个分组,理论上每个线程局部分组的结果,必然不会超过 100 行,把这 100 个分组重新汇总,相对来说代价不会特别高,所以很多数据库系统都会做这个优化来提升效果。但是这个优化也不一定好,假如分组没什么效果,很多数据是不重复的, 10 亿数据分组后最终是 9 亿行,这有什么用呢?做这种场景优化,多做了一次分组操作,性能反而会变差。

针对这种场景——

从运维侧,可以控制优化器的行为,很多优化器都会提供这样的能力:让你控制下压 or 不下压。

从内核侧,优化器自己做决断,根据一些统计信息,判断优化做与否,这是常见的做法,但也不是很有效。举个典型的数据倾斜的场景,很可能出现小账号上不要下压,大账号上要下压,刚才那种策略是一股脑的,要么全下压,要么全不下压。

OceanBase 发现这个问题后,是让执行引擎变得更聪明,不在优化器层面去做决策,而是交给执行层,执行根据计算过程中的实际情况,去决定做不做预聚合优化, OceanBase 对各种场景基本都支持了,包括分组、去重、窗口函数。

下一个场景,是多语句跑批任务场景。

多语句跑批任务 – 读取和写入并重

很多时候,AP 任务不是一条 SQL,它是一个任务。任务里,第一条 SQL 操作一些数据,产生一些临时结果集交给第二条 SQL;第二条 SQL 再做一些计算分析,写到另外一张临时表里,结果发给第三条 SQL;第三条 SQL 再去读取,做一些聚合分析,写到最终的结果集。

从TP到AP:OceanBase SQL引擎的探索和实践-8

我画了一个简化案例(如上),Q1、Q2、Q3,整个过程操作的数据量很大,读取量、计算量、写入量都非常大,这是第一个点的问题。

第二个点的问题,Q1 依赖的表都已经有统计信息了,它的查询优化是有保证的,可以做一个合理判断,选择合适的执行计划。但是 Q2、Q3 分别用到了上一个查询产生的临时表,临时表的统计信息哪里来?没有, Q2、Q3 极有可能因为没有 TMP2、TMP3 的统计信息,产生非常离谱的执行计划,跑得更慢。

针对第一个挑战,读取量大、写入量大,OceanBase 从 3.X 版本开始,就已经支持并行化写入能力,多个线程一起往数据库里倒腾。

针对第二个挑战,统计信息的问题,运维解决方案就是跑批任务里再插一些统计信息收集的 SQL,让它收集信息,但对大表来说是不可想象的,因为收集统计信息也是耗时的,可能会增加整个跑批任务的执行时间。OceanBase 在 4.X 做了在线统计信息收集,Q1 在插入 T1 表数据的同时,已经开始收集统计信息了,等到 T1 表插完,TMP1 的统计信息已经完成, TMP2 去做优化就没问题了。同样,Q2 在插的 TMP2 时候也已经在收集了。

最后一个场景,这其实不是一个真的业务场景,是一个从内核角度去看整个优化的复杂性,所以相对会有点抽象。

分区化,并行化带来的复杂性

当我们去跑一些 AP 任务,很多时候表很大,会要求开一些并行,也会用多机模式,表也会是分区表,整个查询跑起来是并行化、分布式化的,所以面临的优化问题,不是在优化一个单机数据库上的串行计划,而是优化分布式场景下并行化的分布式计划,区别就是多了一个维度,数据是分区的,这会产生什么效果?执行计划会爆炸。

比如串行场景下,我原本就十个计划,但变成分区模式,乘上分区的因素,可能会变成一百个计划,要在一百个里面选出最好的,其实是有难的,很多数据库或传统的研究针对这种场景的优化,会选择一个折中策略:先假定所有的表都是没分区过的,所有表都在一个机器上,生成一个最好的串行计划,然后把串行计划分布式化、并行化。

但这种做法会有什么问题呢?

从TP到AP:OceanBase SQL引擎的探索和实践-9

我们来看一个场景,这也是我在银行场景里见到的真实案例。两个表的连接,引用的表都是 T1 表,但里面有一个子查询,子查询对 T1 做了分组聚合,当我把 T1 表建成一个单分区表时,查询性能很稳定,2s。当把 T1 表建成分区表时,查询偶尔会出现 15 秒的慢查询,为什么会这样?

仔细分析后发现,在串行场景下(右边第一个图),最优的两个执行计划分别是对这两个表做归并连接或嵌套循环连接,两个计划性能非常接近,差不多就是2s,其中归并连接性能会略好一点。但我把T1分区化之后,执行计划会增加一个迭代分区的过程,归并连接增加迭代分区之后,整个性能还是差不多 2s 多一点,但嵌套循环连接不是,性能一下变成了 15s,因为嵌套循环连接的右支执行次数是根据左表的行数来定的,左表有 2 万行,右表就要执行 2 万次,相当于迭代分区的操作,被放大了 2 万次,放大 2 万次后,性能差异就出来了。

所以我们发现,当去做一个分布式场景下的优化,传统思路已经不是特别合适了,所以 OceanBase 在 4.X 版本上做了一个非常大的变化,做了一个新的分布式计划枚举方案,不会先选一个最好的串行计划,而是直接生成所有可能的分布式执行计划,然后在分布式执行计划空间里,选出一个最好的做输出,给到执行去执行,这样就可以避免 Bad Case。

05. 总结

最后总结,整个 SQL 引擎的发展是伴随着业务发展一起往前走的,业务的 SQL 模式在变化,我们的 SQL 引擎能力,优化和执行能力都在往前走。

从TP到AP:OceanBase SQL引擎的探索和实践-10

OceanBase 1.X 和 2.X 过程中,主要服务的是互联网 TP 场景,对优化器来说,很多都是聚焦在索引选择或简单的连接枚举上,执行器的整体能力更多的是让查询能跑起来,而非跑得更快。

2.X 和 3.X 过程中,我们开始更多地服务传统 TP 场景,这个场景下对优化器的要求更高,需要有非常强的连接枚举、改写能力等,执行器也是一样的,外部对性能要求更高,需要新的执行策略。

到了 3.X 和 4.X,从优化器的角度,一方面要去强化对 AP 场景的优化能力,另外一方面希望 TP 场景跑得更稳,让执行计划选得更准,所以我们做了一些特性,如执行计划的灰度演进,或者更强的 Hint 能力,可以让业务侧更好地控制优化器的执行计划;从执行引擎上而言就更多了,比如常见的向量化优化,硬件特性挖掘,更好的并行下压和自适应技术等。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论