星型架构基准中的ClickHouse和ColumnStore

OpenSource Analytical Databases领域有两个新版本，这让我想评估它们在星型架构基准测试中的表现。

MariaDB10.5附带了内置的ColumnStore和一个有趣的功能，可以在同一实例中将数据从InnoDB实时复制到ColumnStore，他们将其命名为“混合事务和分析处理”。

对于ClickHouse，不是一个单独的版本，而是在过去三年中不断改进，以支持联接查询、支持更新数据以及改进与MySQL的集成。

对于基准测试，我将使用比例因子为2500的星型架构基准测试，其大小为：

Star Schema基准测试规定执行13个查询，我将测量每个查询的执行时间。

附注：ClickHouse文档提供了星型架构查询的示例，但是，它们采用了一种反规格化方法，这种方法是有效的，但不允许测试使用3向联接的原始查询的执行情况，这是我在本实验中的目标。

我想看到的第一个结果是将150亿行加载到数据库需要多长时间，以及最终的表大小是多少。

现在，我想比较ColumnStore和ClickHouse的查询执行时间。我将使用尽可能接近原始的查询，但是对于ClickHouse，我仍然需要调整查询以便能够执行它们。

我们可以看到，ClickHouse在除一个查询3.1之外的所有查询中的表现都要好于其他查询，有时还会有很大的差距。另外，查询4.1对于ClickHouse来说尤其困难。

我提到过要在ClickHouse中运行查询，它们仍然需要修改，所以让我们来看看。

如果三年前(请参阅我前面提到的博客，ClickHouse in a General Analytical Workload-based a Star Schema Benchmark)，一个3向联接查询，如：

SELECT SUM(LO_REAGE)，P_MFGR，TO Year(LO_ORDERDATE)YOD FROM LINORDERFULL，CUSTERFULL，PARTFULL WHERE C_REGION=C_CUSTKEY=C_CUSTKEY AND P_PARTKEY=LO_PARTKEY GROUP BY BY P_MFGR，YOD ORDER BY P_MFGR，YOD；

SELECT SUM(LO_REAGE)、P_MFGR、TOYear(LO_ORDERDATE)as yodFROM(SELECT LO_PARTKEY，LO_ORDERDATE，LO_REAGENT FROM LINEL ALL INTER JOIN(SELECT C_REGION，C_CUSTKEY AS LO_CUSTKEY FROM customerFull)WHERE C_CUSTKEY='；ASIA')使用(LO_CUSTKEY)WHERE(LO_CUSTKEY)WHERE C_REGION='；ASIA'。

SELECT to Year(LO_ORDERDATE)AS Year，C_Nation，SUM(LO_REAGENT-LO_SUPPLYCOST)AS ProfitfROM linderINNER JOIN CUSTKEY=LO_CUSTKEYNER JOIN SUPPORTER ON LO_SUPPKEY=S_SUPPKEYINNER JOIN PART ON P_PARTKEY=LO_PARTKEYWHERE(C。))按年分组，C_NATIONORER按年ASC，C_国家ASC。

WHERE(C_Region='；America'；)AND(S_Region='；America'；)AND((P_MFGR='；MFGR#1'；)OR(P_MFGR='；MFGR#2'；))。

尽管ClickHouse中的查询看起来像普通联接查询，但ClickHouse没有查询优化器。这意味着表将按照您编写的顺序进行联接，并且ClickHouse不会尝试更改表顺序以实现最佳执行。这项任务仍由开发人员负责。

在人工干预下，如果我们以以下形式编写ClickHouse的查询3.1，仍然有可能改进它(感谢Altinity的Alexander Zaytsev的帮助！)：

SELECT C_NION，S_NION，TO Year(LO_ORDERDATE)作为年份，SUM(LO_REAGE)作为收入FROM LINEORDER INNER JOIN客户on C_CUSTKEY=LO_CUSTKEYNER加入供应商On LO_SUPPKEY=S_SUPPKEYWHERE LO_CUSTKEY in(SELECT DISTINCT C_CUSTKEY from Customer WHERE WHERE C_Region='；Asia)(SELECT DISTINCT C_CUSTKEY from Customer WHERE where C_Region='；Asia。=1997GROUP BY C_NION，S_NION，Year ORDER By Year ASC，Income DESC；

查询3.1的执行时间从7分33秒减少到5分44秒。

也可以改进其他查询，但我希望将它们保留在原始形式。

更新查询不是星型架构基准测试的一部分，但是这个问题很常见。

我们是否可以更新数据以及更新查询的执行情况，因为这对分析数据库来说可能是一个挑战？

为此，ClickHouse引入了一种特殊的更新语法，类似于ALTER TABLE…。UPDATE突出显示这不是典型的UPDATE语句。

同样值得注意的是，ClickHouse中的ALTER TABLE UPDATE是以异步方式执行的，这意味着在完成之前，您可能会看到应用更改期间的不一致。

请注意，此查询将更新15BN行，这不是一项微不足道的任务！

我们可以看到应用这些更改需要多长时间。为此，我将运行以下查询。

结果：在ALTER TABLE查询之后，SELECT查询大约需要2分钟来反映新值。

我等了6个小时才完成查询，在这一点上，我不得不终止它，因为我不能再等了。

ClickHouse-更新是异步运行的，并且不是事务性的，这可能会导致读取查询在运行更新时返回不一致的结果。

ColumnStore-更新是同步的和事务性的。这可能就是更新150亿行需要这么长时间的原因。

因此，很明显，如果要选择首选项，我会在给定查询执行时间、数据加载时间和新改进的连接语法的情况下选择ClickHouse。

ColumnStore的好处是它位于单个类似于MySQL的包(MariaDB风格)中，并且有一种将数据流从InnoDB集成到ColumnStore的简单方法。

ClickHouse对与MySQL的集成进行了改进(参见ClickHouse和MySQL-更好地结合在一起，但仍有改进的空间！)。

架构：支持x86_64 CPU：支持48个在线CPU列表：0-47个线程/核：每个插槽支持2个核：支持12个插槽：支持2个NUMA节点：支持2个CPU家族：支持6个型号：支持支持63个CPU型号：支持0-47个线程/内核：支持12个插槽：支持2个NUMA节点：支持2个CPU家族：支持6个型号：支持8个英特尔(R)至强(R)CPU E5-2680个型号名称：支持英特尔(R)至强(R)CPU E5-2680