Clickhouse很快,ESPROC SPL更快
#编程 #sql #database #bigdata

Clickhouse作为一个开源分析数据库,以快速而闻名。真的吗?让我们通过比较测试进行测试并验证它。

Clickhouse vs Oracle

首先,我们在相同的硬件和软件环境下对Clickhouse(简称CH)和Oracle数据库(ora)进行了比较测试,并将国际认可的TPC-H用作测试基准。该测试是执行用8个表的22个SQL语句定义的计算要求(Q1至Q22)。该测试是在12个线程中的一台机器上执行的,总数据量约为100克。由于与TPC-H相对应的SQL语句相对较长,因此我们在这里不列出它们。

Q1是一个简单的计算,即,将单个表遍历到组,然后汇总。测试结果如下图所示:
Image description

我们可以清楚地看到,CH的性能要比ORA更好,这表明Ch的柱状存储效果很好,并且穿越单个表的速度非常快。使ORA慢得多的主要原因是它使用基于行的存储。

但是,如果我们提高了计算复杂性,CH的性能会像上面一样出色吗?让我们在TPC-H的Q2,Q3,Q7中检查一下。测试结果:

Image description

可以看出,当计算变得复杂时,CH的性能会显着降低。具体而言,Q2涉及少量数据,柱状存储的效果并不明显,并且CH的性能几乎与ORA的性能相同。 Q3涉及大量数据,由于柱状存储,CH的性能超过了ORA的数据; Q7还涉及大量数据,但是CH的性能不如ORA的数据,因为计算变得复杂。

完成复杂计算的速度主要取决于性能优化引擎。尽管Ch的柱状存储具有巨大的优势,但其性能仍然被使用基于行存储的ORA所取代,这表明CH的算法优化能力远远不如ORA。

TPC-HS Q8是一个更复杂的计算,需要在子问题中加入多个表。当在CH中执行此计算时,在运行2000秒后仍未获得结果,最可能的原因是CH崩溃了。相比之下,获得ORA的结果需要192秒。对于添加了Q8子问题的Q9计算,在CH中直接提示了错误的误差,而结果是在ORA中234秒后获得的。此外,还有其他一些复杂的操作无法在CH中解决,因此不可能进行整体比较。

CH和ORA均基于SQL,但是,可以在ORA中进行优化的陈述不能正常在CH中执行,这进一步证明了CH的优化引擎相对较差。

据说CH仅擅长执行单个表遍历操作,并且在涉及关联操作时,其性能甚至不如MySQL。从上面的测试结果中,此陈述似乎并非是错误和毫无根据的。因此,那些想要使用CH的人应该考虑一个问题:单个表遍历操作的应用范围是什么?

ESPROC SPL

开源ESPROC SPL还具有高性能作为宣传的优势。让我们将其与CH和ORA进行比较。

我们仍然使用TPC-H进行测试:

Image description

可以看出,对于更复杂的操作Q2,Q3和Q7,SPL的运行速度比CH和ORA快。对于CH无法获得结果的Q8和Q9,在SPL中分别需要37秒和68秒,它们也比ORA快。原因是SPL可以使用更好的算法,并且它们的计算复杂性低于ORA中优化的SQL的算法,并且远低于CH中执行的SQL。此外,借助柱状存储,在Java中开发的SPL最终优于C ++中实现的OR。

现在,我们可以大致得出一个结论,即ESPROC SPL在简单和复杂的计算中表现良好。

但是,对于Q1等简单操作,CH的性能比SPL稍好,这似乎进一步证明了CH在简单的遍历操作方面特别擅长。

等一下,这不是最后的结论。 SPL有一个秘密武器。

SPL在其企业版中提供了柱状光标机制。让我们对SPL(使用柱状光标)和CH的性能进行比较测试。测试是执行最简单的分组和汇总计算,数据量为8亿。 (由于此测试中使用的机器的配置略低于先前TPC-H测试中使用的机器,因此测试结果将有所不同,但并不重要。在这里我们主要关注相对值。)

用于简单的分组和汇总计算,SQL代码:

sql1ï¼

SELECT mod(id, 100) AS Aid, max(amount) AS Amax
FROM test.t
GROUP BY mod(id, 100)

测试结果如下图所示:

Image description

使用柱状光标机制后,简单遍历和分组计算中的SPL性能与CH相同。如果在Q1测试中使用柱状光标,SPL也将达到与Ch。

相同的性能

在测试期间,我们发现,当将8亿个数据存储为文本格式时,它们占据了15克磁盘空间,但它们在CH中占据5.4克空间,而SPL中的8克则占据了8g。它表明CH和SPL压缩数据,CH的压缩率较高,这进一步证明了CH的存储引擎实际上很好。但是,由于SPL的性能与CH相同,因此表明SPL的存储引擎和算法优化都很好,并且高性能计算能力更加平衡。

当前版本的SPL用Java编写。在Java中,读取数据后,生成计算对象的速度非常慢,而C ++中CH中没有这样的问题。对于复杂的操作,由于数据阅读时间的比例不是很高,因此在Java中生成缓慢的对象影响的性能并不明显。但是,对于简单的遍历操作,由于数据阅读时间的比例很高,因此SPL的运行速度比上一个测试中的CH慢。柱状光标优化了数据读取方案,并且不再生成许多小对象,这使生成对象的数量大大减少。这样,性能差距就会缩小。从仅存储的角度来看,Spl和Ch。

之间没有明显的区别

接下来,让我们看一下关于传统计算TOPN的比较测试。 SQL代码:

sql2ï¼

SELECT * FROM test.t ORDER BY amount DESC LIMIT 100

比较测试结果:

Image description

仅查看CH的SQL2,计算TOPN的常规方法是在完整排序之后获取前N数据。当数据量较大时,如果进行全面分类,则性能将非常差。 SQL2的测试结果表明,应像SPL一样在CH中进行优化,并且避免了完整分类,因此,既快速执行,spl却更快。

换句话说,无论是简单或复杂的操作,ESPROC SPL都会表现更好。

进一步的差距

差距不仅如此。

如前所述,CH和ORA都使用SQL,并且都基于关系模型,因此它们都面临SQL优化的问题。 TPC-H测试证明,可以在CH中优化可以在ORA中进行优化的某些场景,甚至无法在CH中获得计算结果。因此,对于ORA无法优化的计算,CH更不可能优化。例如,我们将SQL1的简单分组和汇总计算更改为两个分组和汇总结果,然后加入它们。用Ch的SQL编写的代码大致是这样的:

sql3:

SELECT *
FROM (
 SELECT mod(id, 100) AS Aid, max(amount) AS Amax
 FROM test.t
 GROUP BY mod(id, 100)
 ) A
 JOIN (
 SELECT floor(id / 200000) AS Bid, min(amount) AS Bmin
 FROM test.t
 GROUP BY floor(id / 200000)
 ) B
 ON A.Aid = B.Bid

在这种情况下,比较测试结果表明CH中的计算时间加倍,并且在SPL中保持不变:

Image description

造成此结果的原因是SPL不仅使用柱状光标,还使用多功能遍历机制。该机制允许SPL通过一个遍历计算多个分组结果,从而大大减少了对硬盘的访问量。相比之下,CH的SQL不能编写此类操作,只能依靠CH本身的优化能力。但是,CH算法的优化能力非常差,并且其优化引擎在该测试中不起作用,因此必须穿越两次。结果,性能降低了一半。

用于实现多功能遍历的SPL代码非常简单,大致如下:

    A   B
1   =file("topn.ctx").open().cursor@mv(id,amount)
2   cursor A1   =A2.groups(id%100:Aid;max(amount):Amax)
3   cursor  =A3.groups(id\200000:Bid;min(amount):Bmin)
4   =A2.join@i(Aid,A3:Bid,Bid,Bmin)

现在,我们将SQL2中TOPN的常规计算更改为分组后计算组内顶部。 SQL代码:

sql4:

SELECT
    gid,
    groupArray(100)(amount) AS amount
FROM
(
    SELECT
        mod(id, 10) AS gid,
        amount
    FROM test.topn
    ORDER BY
        gid ASC,
        amount DESC
) AS a
GROUP BY gid

对分组TOPN计算的比较测试的结果如下:

Image description

CH中计算的TOPN的速度比常规TOPN计算的速度慢42倍,这表明CH在这种情况下可能执行分类动作。也就是说,在计算变得复杂后,CH的优化引擎也无效。与SQL不同,SPL将TOPN视为一个聚合操作,并使用与Sum和Count等操作中使用的计算逻辑相同的计算逻辑,也就是说,它只需要一次遍历原始数据。这样,分组后计算组内顶部的顶部与求和和计数相同,这避免了分类计算。因此,在SPL中计算的TOPN的计算速度比Ch。

快22倍

此外,用于计算分组的TOPN的SPL代码并不复杂:

    A
1   =file("topn.ctx").open().cursor@mv(id,amount)
2   =A1.groups(id%10:gid;top(10;-amount)).news(#2;gid,~.amount)

不仅快速运行

让我们看一下电子商务系统中的常见渠道操作。 SPL代码仍然非常简洁:

    A   B
1   =["etype1","etype2","etype3"]   =file("event.ctx").open()
2   =B1.cursor(id,etime,etype;etime>=date("2021-01-10") && etime<date("2021-01-25") && A1.contain(etype) && …)
3   =A2.group(id).(~.sort(etime))   =A3.new(~.select@1(etype==A1(1)):first,~:all).select(first)
4   =B3.(A1.(t=if(#==1,t1=first.etime,if(t,all.select@1(etype==A1.~ && etime>t && etime<t1+7).etime, null))))
5   =A4.groups(;count(~(1)):STEP1,count(~(2)):STEP2,count(~(3)):STEP3)

CH的SQL无法实施此类计算。让我们以ORA为例,在SQL中查看三步漏斗中的写作方法:

with e1 as (
     select gid,1 as step1,min(etime) as t1
     from T
     where etime>= to_date('2021-01-10', 'yyyy-MM-dd') and etime<to_date('2021-01-25', 'yyyy-MM-dd')
            and eventtype='eventtype1' and …
    group by 1
),
with e2 as (
    select gid,1 as step2,min(e1.t1) as t1,min(e2.etime) as t2
    from T as e2
    inner join e1 on e2.gid = e1.gid
    where e2.etime>= to_date('2021-01-10', 'yyyy-MM-dd') and e2.etime<to_date('2021-01-25', 'yyyy-MM-dd') and e2.etime > t1
            and e2.etime < t1 + 7
            and eventtype='eventtype2' and …
    group by 1
),
with e3 as (
    select gid,1 as step3,min(e2.t1) as t1,min(e3.etime) as t3
    from T as e3
    inner join e2 on e3.gid = e2.gid
    where e3.etime>= to_date('2021-01-10', 'yyyy-MM-dd') and e3.etime<to_date('2021-01-25', 'yyyy-MM-dd') and e3.etime > t2
            and e3.etime < t1 + 7
            and eventtype='eventtype3' and …
    group by 1
)
Select
  sum(step1) as step1,
  sum(step2) as step2,
  sum(step3) as step3
from
  e1
  left join e2 on e1.gid = e2.gid
  left join e3 on e2.gid = e3.gid

使用ORA的SQL实现三步漏斗操作时,需要编写30行代码,这很难理解。此外,此代码与漏斗的步骤数量有关,每一个额外的步骤都需要添加一个子问题。相反,SPL更简单,相同的SPL代码可以处理任何数量的步骤。

对于如此复杂的SQL代码,很难编写,更不用说性能优化了。

ch的SQL远不如ORA,通常无法编码如此复杂的逻辑,并且只能在外部编写C ++代码以实现。换句话说,在这种情况下,只能使用CH的存储引擎。尽管C ++中的外部计算有可能获得良好的性能,但开发成本很高。有许多类似的例子,这些示例都不能直接在ch。

中实施。

总而言之,使用CH计算一些简单的场景(例如穿越单个表)确实非常快,并且表现与SPL几乎相同。但是,对于高性能计算,我们不应仅考虑简单计算的速度,而应该考虑各种情况。对于复杂的操作,SPL不仅在性能方面胜过CH,而且编码更简单。 SPL能够处理高性能数据计算的所有方案,可以说SPL完全击败了CH。

起源:https://blog.scudata.com/clickhouse-is-fast-esproc-spl-is-faster/
SPL源代码:https://github.com/SPLWare/esProc