在5000亿数据中大海捞针，需要怎样的硬件做支撑?

成本账，咱也要算呐

最近看到一篇由Alexander Zaitsev撰写的,关于ClickHouse性能对比的文章(文末会有报告的原文地址)，觉得很有意思，所以拿来与大家分享一下。它有趣的地方在于，这是一篇针对性能对标的，性能对标测评。这算不算螳螂捕蝉，黄雀在后呢？

这篇文章的对标场景，来自于ScyllaDB的一篇测评报告，而在这篇报告中，ScyllaDB历数了从2001年至今，NoSql数据库性能达到的几个关键里程碑。而如今他们宣称，ScyllaDB能够在5000亿的数据下，以10亿行/每秒的性能处理数据。 在ScyllaDB的测试方案中，他们模拟了物联网的使用场景。数据来自于家庭安装的温度传感器，这些温度传感器每隔 1 分钟读数一次。

所以，1百万个传感器，1年下来的数据量将会是： 1,000,000 * 24 * 60 * 365 = 5256 亿 在服务器硬件方面，他们采用了packet.com的云服务，使用了83个 n2.xlarge.x86 实例作为database节点，以及24个 c2.medium.x86 实例作为辅助的worker节点。

这真可谓是超豪华阵容，节点配置分别如下：

测试目标是为了解答如下几个问题:

1. 从时间跨度为3个月的数据中，分别找到温度最高和最低的那一天，以及这些读数来自于哪个传感器； 2. 从整年的数据中，分别找到温度最高和最低的那一天，以及这些读数来自于哪个传感器；

3. 在测试数据中,事先埋入了一些坏的测点，在查询中排除这些坏的测点(像不像大海捞针)。

说了那么多的背景，我们的主角要出场了,该轮到ClickHouse的黑科技了。

Alexander在看到这篇文章之后，觉得他们的方案太不经济了。如果使用ClickHouse的黑魔法，能够在达到同样业务需求的背景下，拥有更好的成本效益。一不做二不休，Alexander决定使用一台NUC迷你主机作为ClickHouse的测试硬件。

丧心病狂的硬件对比如下：

在ClickHouse的对比方案中，它与对手的配置差距总是如此之大。

然而，即便在内存相差1000倍的情况下，ClickHouse仍然比对手拥有10~100倍的性能。

耳听为虚眼见为实，真的有这么夸张的性能吗? 光道听途说可不行， 咱必须得亲自论证一番，可是我身边也没迷你主机啊，只能拿笔记本 + 虚拟机凑活了，就是下面这台：

颈椎病治疗时间到 我这可不是一台普通的笔记本，它是一台贴了ClickHouse信仰贴纸的，拥有20%性能加成的战斗本。 虚拟机系统Centos 7, 配置4 cores, 32G:

uname -a
Linux ch6.nauu.com 3.10.0-1062.el7.x86_64 #1 SMP Wed Aug 7 18:08:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
[root@ch6 ~]#
[root@ch6 ~]# free -g
             total        used        free      shared  buff/cache   available
Mem:             31           0          30           0           0          30
Swap:             1           0           1
[root@ch6 ~]# grep 'core id' /proc/cpuinfo | sort -u | wc -l
4

ClickHouse版本19.17.4 按照Alexander的例子,首先需要创建一张保存传感器数据的测试表:

CREATE TABLE sensors_data ( \
   sensor_id Int32 Codec(DoubleDelta, LZ4), \
   time DateTime Codec(DoubleDelta, LZ4), \
   date ALIAS toDate(time), \
   temperature Decimal(5,2) Codec(T64, LZ4) \
) Engine = MergeTree \
PARTITION BY toYYYYMM(time) \
ORDER BY (sensor_id, time);

在这张数据表的定义中，可谓是做到了极致的优化。包括：

• 编码和压缩算法优化

针对每一个列字段，都单独声明了它的encode编码算法和compresss压缩算法，这是ClickHouse的一个新特性。简而言之，如果是时间序列的数据，推荐使用DoubleDelta和LZ4的组合；而在数据模式不明确的情况下，可以使用T64和LZ4的组合。关于算法的这一块的介绍，以后有时间可以专门写一篇说明；

• 存储开销优化

date类型使用了 ALIAS 计算字段，降低了存储开销；

• 索引优化

分区Key和主键Key，很好的匹配了业务的查询条件。如此一来，在后续的查询中，就能够充分利用MergeTree的分区索引和一级索引。

有了数据表之后，就可以开始模拟测试数据了，Alexander使用了numbers_mt函数，模拟了5256亿温度数据:

INSERT INTO sensors_data (sensor_id, time, temperature) \
WITH \
 toDateTime(toDate('2019-01-01')) as start_time,  \
 1000000 as num_sensors, \
 365 as num_days, \
 24*60 as num_minutes, \
 num_days * num_minutes as total_minutes \
SELECT \
 intDiv(number, num_minutes) % num_sensors as sensor_id,  \
 start_time + (intDiv(number, num_minutes*num_sensors) as day)*24*60*60 + (number % num_minutes as minute)*60 time,  \
 60 + 20*sin(cityHash64(sensor_id)) \
 + 15*sin(2*pi()/num_days*day)  \
 + 10*sin(2*pi()/num_minutes*minute)*(1 + rand(1)%100/2000)  \
 + if(sensor_id = 473869 and  \
      time between '2019-08-27 13:00:00' and '2019-08-27 13:05:00', -50 + rand(2)%100, 0)  \
 as temperature \
FROM numbers_mt(525600000000) \
SETTINGS max_block_size=1048576;

接下来干什么呢？接下来可以去睡觉了。由于是单线程写入，我粗略计算了一下，大概只要40~50个小时，数据就能全部写进去了！！！

在这段等待的时间，我们继续往下分析。当数据写完之后，这张 sensors_data 数据表并不能直接作为查询表使用，还需要进一步为它创建物化视图:

CREATE MATERIALIZED VIEW sensors_data_daily( \
  sensor_id Int32 Codec(DoubleDelta, LZ4), \
  date Datetime Codec(DoubleDelta, LZ4), \
  temp_min SimpleAggregateFunction(min, Decimal(5,2)), \
  temp_max SimpleAggregateFunction(max, Decimal(5,2)) \
) Engine = AggregatingMergeTree \
PARTITION BY toYYYYMM(date) \
ORDER BY (sensor_id, date) \
POPULATE \
AS  \
SELECT sensor_id, date,  \
   min(temperature) as temp_min, \
   max(temperature) as temp_max \
FROM sensors_data \
GROUP BY sensor_id, date;

物化视图会自动同步sensors_data的数据。

由于使用了AggregatingMergeTree表引擎，数据在AggregatingMergeTree合并分区的过程中，会以分区目录为单位，按照 sensor_id和date预先聚合。

所以，这里其实是玩了一个ClickHouse的常用技巧，那就是利用物化视图进行了预聚合的优化。使用物化视图和MergeTree组合使用，是ClickHouse的杀手锏之一。

在这个例子中，有点类似数据立方体的意思，通过预聚合, 将聚合结果预先存在表内，在之后查询的过程中，可以从结果直接返回。

与此同时，预先聚合还能有效的减少数据行，在这个例子中，最终能将视图内的数据行减少1400倍之多。

最后，正如ScyllaDB的自己的文章中所说，他们的测试并不是一场 apples to apples 的比较。同样的，我想Alexander拿ClickHouse做的这场比较，也不是针尖对麦芒的。它们两者之间有着太多的不同，它们使用了不同的数据模型、不同的架构等等。 但是通过这个案例的对比，大家可以认识到ClickHouse确实是经济和性能的完美平衡。如果你的业务也有类似的场景，使用ClickHouse将会是一种不错的选择。

由于我的数据还没写完。。。 关于论证的结果，只能留在下一篇了。

阅读原文，即可找到测评报告的原文地址。