ClickHouse 挺快，esProc SPL 更快

╰半夏微凉° 2023-09-28 21:37 42阅读 0赞

开源分析数据库ClickHouse以快著称，真的如此吗？我们通过对比测试来验证一下。

### ClickHouse vs Oracle ###

先用ClickHouse（简称CH）、Oracle数据库（简称ORA）一起在相同的软硬件环境下做对比测试。测试基准使用国际广泛认可的TPC-H，针对8张表，完成22条SQL语句定义的计算需求（Q1到Q22）。测试采用单机12线程，数据总规模100G。TPC-H对应的SQL都比较长，这里就不详细列出了。

Q1是简单的单表遍历计算分组汇总，对比测试结果如下：

![在这里插入图片描述][1eedc9d6621f48c6b9d092c99fa72f02.png_pic_center]

CH计算Q1的表现要好于ORA，说明CH的列式存储做得不错，单表遍历速度很快。而ORA主要吃亏在使用了行式存储，明显要慢得多了。

但是，如果我们加大计算复杂度，CH的表现怎么样呢？继续看TPC-H的Q2、Q3、Q7，测试结果如下：

![在这里插入图片描述][ea9916440ed54028898a6aa06b270c7c.png_pic_center]

计算变得复杂之后，CH性能出现了明显的下降。Q2涉及数据量较少，列存作用不大，CH性能和ORA几乎一样。Q3数据量较大，CH占了列存的便宜后超过了ORA。Q7数据也较大，但是计算复杂，CH性能还不如ORA。

做复杂计算快不快，主要看性能优化引擎做的好不好。CH的列存占据了巨大的存储优势，但竟然被ORA用行式存储赶上，这说明CH的算法优化能力远不如ORA。

TPC-H的Q8是更复杂一些的计算，子查询中有多表连接，CH跑了2000多秒还没有出结果，应该是卡死了，ORA跑了192秒。Q9在Q8的子查询中增加了like，CH直接报内存不足的错误了，ORA跑了234秒。其它还有些复杂运算是CH跑不出来的，就没法做个总体比较了。

CH和ORA都基于SQL语言，但是ORA能优化出来的语句，CH却跑不出来，更证明CH的优化引擎能力比较差。

坊间传说，CH只擅长做单表遍历运算，有关联运算时甚至跑不过MySQL，看来并非虚妄胡说。想用CH的同学要掂量一下了，这种场景到底能有多大的适应面？

### esProc SPL登场 ###

开源esProc SPL也是以高性能作为宣传点，那么我们再来比较一下。

仍然是跑TPC-H来看 ：

![在这里插入图片描述][0f277cc39fcf42cfaea79f1a312abad9.png_pic_center]

Q2、Q3、Q7这些较复杂的运算，SPL比CH和ORA跑的都快。CH跑不出结果的Q8、Q9，SPL分别跑了37秒和68秒，也比ORA快。原因在于SPL可以采用更优的算法，其计算复杂度低于被ORA优化过的SQL，更远低于CH执行的SQL，再加上列存，最终是用Java开发的SPL跑赢了C++实现的CH和ORA。

大概可以得到结论，esProc SPL无论做简单计算，还是复杂计算性能都非常好。

不过，Q1这种简单运算，CH比SPL还是略胜了一筹。似乎可以进一步证明前面的结论，即CH特别擅长简单遍历运算。

且慢，SPL还有秘密武器。

SPL的企业版中提供了**列式游标**机制，我们再来对比测试一下：在8亿条数据量下，做最简单的分组汇总计算，对比SPL（使用列式游标）和CH的性能。（采用的机器配置比前面做TPC-H测试时略低，因此测出的结果不同，不过这里主要看相对值。）

简单分组汇总对应CH的SQL语句是：

SQL1：

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

这个测试的结果是下图这样：

![在这里插入图片描述][8a27fc4b67b649ea9fc907edee1e23d0.png_pic_center]

SPL使用列式游标机制之后，简单遍历分组计算的性能也和CH一样了。如果在TPC-H的Q1测试中也使用列式游标，SPL也会达到和CH同样的性能。

测试过程中发现，8亿条数据存成文本格式占用磁盘15G，在CH中占用5.4G，SPL占用8G。说明CH和SPL都采用了压缩存储，CH的压缩比更高些，也进一步证明CH的存储引擎做得确实不错。不过，SPL也可以达到和CH同样的性能，这说明SPL存储引擎和算法优化做得都比较好，高性能计算能力更加均衡。

当前版本的SPL是用Java写的，Java读数后生成用于计算的对象的速度很慢，而用C++开发的CH则没有这个问题。对于复杂的运算，读数时间占比不高，Java生成对象慢造成的拖累还不明显；而对于简单的遍历运算，读数时间占比很高，所以前面测试中SPL就会比CH更慢。列式游标优化了读数方案，不再生成一个个小对象，使对象生成次数大幅降低，这时候就能把差距拉回来了。单纯从存储本身看，SPL和CH相比并没有明显的优劣之分。

接下来再看常规TopN的对比测试，CH的SQL是：

SQL2：

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

对比测试结果是这样的：

![在这里插入图片描述][3dde1406a184432d8f39709da99e7c11.png_pic_center]

单看CH的SQL2，常规TopN的计算方法是全排序后取出前N条数据。数据量很大时，如果真地做全排序，性能会非常差。SQL2的测试结果说明，CH应该和SPL一样做了优化，没有全排序，所以两者性能都很快，SPL稍快一些。

也就是说，无论简单运算还是复杂运算，esProc SPL都能更胜一筹。

### 进一步的差距 ###

差距还不止于此。

正如前面所说，CH和ORA使用SQL语言，都是基于关系模型的，所以都面临SQL优化的问题。TPC-H测试证明，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则不变

![在这里插入图片描述][ecc3be2d331243b8aa82c7c94dee3369.png_pic_center]

这是因为SPL不仅使用了列式游标，还使用了**遍历复用**机制，能在一次遍历过程中计算出多种分组结果，可以减少很多硬盘访问量。CH使用的SQL无法写出这样的运算，只能靠CH自身的优化能力了。而CH算法优化能力又很差，其优化引擎在这个测试中没有起作用，只能遍历两次，所以性能下降了一倍。

SPL实现遍历复用的代码很简单，大致是这样：

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

再将SQL2常规TopN计算，调整为分组后求组内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测试的对比结果是下面这样的：

![在这里插入图片描述][a818477b519146cda568f6442656ef28.png_pic_center]

CH做分组TopN计算比常规TopN慢了42倍，说明CH在这种情况下很可能做了排序动作。也就是说，情况复杂化之后，CH的优化引擎又不起作用了。与SQL不同，SPL把TopN看成是一种聚合运算，和sum、count这类运算的计算逻辑是一样的，都只需要对原数据遍历一次。这样，分组求组内TopN就和分组求和、计数一样了，可以避免排序计算。因此，SPL计算分组TopN比CH快了22倍。

而且，SPL计算分组TopN的代码也不复杂：

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

### 不只是跑得快 ###

再来看看电商系统中常见的漏斗运算。SPL的代码依然很简洁：

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

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写出来要三十多行，理解起来有相当的难度。而且这段代码和漏斗的步骤数量相关，每增加一步数就要再增加一段子查询。相比之下，SPL就简单得多，处理任意步骤数都是这段代码。

这种复杂的SQL，写出来都很费劲，性能优化更无从谈起。

而CH的SQL还远不如ORA，基本上写不出这么复杂的逻辑，只能在外部写C++代码实现。也就是说，这种情况下只能利用CH的存储引擎。虽然用C++在外部计算有可能获得很好的性能，但开发成本非常高。类似的例子还有很多，CH都无法直接实现。

总结一下：CH计算某些简单场景（比如单表遍历）确实很快，和SPL的性能差不多。但是，高性能计算不能只看简单情况快不快，还要权衡各种场景。对于复杂运算而言，SPL不仅性能远超CH，代码编写也简单很多。SPL能覆盖高性能数据计算的全场景，可以说是完胜CH。

### SPL资料 ###

*  [SPL下载][SPL]
 *  [SPL源代码][SPL 1]

[1eedc9d6621f48c6b9d092c99fa72f02.png_pic_center]: https://img-blog.csdnimg.cn/1eedc9d6621f48c6b9d092c99fa72f02.png#pic_center
[ea9916440ed54028898a6aa06b270c7c.png_pic_center]: https://img-blog.csdnimg.cn/ea9916440ed54028898a6aa06b270c7c.png#pic_center
[0f277cc39fcf42cfaea79f1a312abad9.png_pic_center]: https://img-blog.csdnimg.cn/0f277cc39fcf42cfaea79f1a312abad9.png#pic_center
[8a27fc4b67b649ea9fc907edee1e23d0.png_pic_center]: https://img-blog.csdnimg.cn/8a27fc4b67b649ea9fc907edee1e23d0.png#pic_center
[3dde1406a184432d8f39709da99e7c11.png_pic_center]: https://img-blog.csdnimg.cn/3dde1406a184432d8f39709da99e7c11.png#pic_center
[ecc3be2d331243b8aa82c7c94dee3369.png_pic_center]: https://img-blog.csdnimg.cn/ecc3be2d331243b8aa82c7c94dee3369.png#pic_center
[a818477b519146cda568f6442656ef28.png_pic_center]: https://img-blog.csdnimg.cn/a818477b519146cda568f6442656ef28.png#pic_center
[SPL]: http://c.raqsoft.com.cn/article/1595816810031
[SPL 1]: https://github.com/SPLWare/esProc