ClickHouse: One table to rule them all!

前面几篇笔记我们讨论了存储海量行情数据的个人技术方案。它们之所以被称之为个人方案，并不是因为性能弱，而是指在这些方案中，数据都存储在本地，也只适合单机查询。

数据源很贵 -- 在这个冬天，我们已经听说，某些上了规模的机构，也在让员工共享万得账号了。所以，共享网络存储，从而只需要一个数据账号，就成为合理的需求。更不必说，集中管理才可能让 IT 来进行数据维护，而分析师只需要专注于策略就好。

那些以讹传讹的解决方案¶

都已经 2024 年了，但说到行情数据的存储，你仍然能看到推荐 mysql 的文章。这完全是错误的。不要说 mysql，就是 postgres 来了也不行。不要说 postgres，就是 sqlserver 甚至 oracle 来了都不行。

其它不 work 的方案还包括 mongodb。mongodb 是挺能装的，但是它不适合行情数据这类时序数据的查询。

Influxdb 是最早和最出名的时序数据库。但是它的社区版本性能还是偏弱，特别是它限制了查询的并发度。此外，它的引擎是 Go 语言，这仍然要比 C 慢好几倍。

Dolphinedb 可能性能上强于 Influxdb 不少，但缺点也是社区版对性能的限制太多。Tidb 据说性能不错，但我们没有机会评测过它。

不过如果有犹如王者般的 clickhouse 社区版摆在面前，还有什么必要去评估那些青铜呢？

Why click house is so f**ing fast?¶

Clickhouse 是战斗民族开发的产品。它的开发者是俄国的搜索引擎 Yandex!（提到 Yandex! 时，不能漏了这个感叹号）。搜索引擎天生要处理很多查询和统计分析，所以就催生了这个性能怪兽。

Clickhouse 的优化是全方位的。在硬件级别上，它利用了 SIMD CPU 指令。Clickhouse 特别强调他们使用了 SIMD 指令来进行并行优化，当你安装 clickhouse 时，它提供了一个检测工具，让你检测 SIMD 指令优化能否开启。

Tip

印像中 SIMD 现在也不是多高端的技巧，有好些软件也利用了这种优化，不过 clickhouse 很会找卖点，有点类似小米的全玻璃摄像头的宣传。不过，充分利用硬件指令，确实是优化中很重要的一个方法。

数据结构上，Clickhouse 使用了列存储，这一点，其实像 parquet, hdf5 都是这样存储的。基于列存储，就有很多很好的压缩方案可用，一旦存盘的数据量变小，显然易见 IO 效率也会提升。

但是它基于 merge-tree 的存储引擎，使得在查询上，不仅可以利用所有的 CPU 核和磁盘、还可以利用集群的所有 CPU 核和磁盘。这使得它的查询性能可以随硬件增加线性扩展。

Tip

正如 clickhouse 自己所说的那样，所有这些技术，其它很多数据管理系统也可能在使用。但 clickhouse 能做到更快，关键在于细节优化。而细节优化之所以能更到位，是因为他们不是做通用场景，而是只关注列数据库。

在这一块，确实用了很多大数据的技巧，比如使用了 bloomfilter 的索引。它还留了一些优化技巧给使用者，这也是我们这篇笔记将要介绍的：如何设计一个能存储上百亿条行情数据的数据库，并达到最佳性能。

实战！先存一个小目标¶

尽管在 clickhouse 中，我们可以把分钟线与日线存在一张表里，但是考虑到我们几乎不可能同时取两个不同周期的数据，所以，把它们分别按表存储显然更合理。所以，我们在举例时，就只以分钟线为例：

CREATE TABLE if not exists bars_1m
(
    `frame` DateTime64 CODEC(Delta, ZSTD),
    `symbol` LowCardinality(String),
    `open` Float32 DEFAULT -1 CODEC(Delta, ZSTD),
    `high` Float32 DEFAULT -1 CODEC(Delta, ZSTD),
    `low` Float32 DEFAULT -1 CODEC(Delta, ZSTD),
    `close` Float32 DEFAULT -1 CODEC(Delta, ZSTD),
    `volume` Float64 DEFAULT -1 ,
    `money` Float64 DEFAULT -1 ,
    `factor` Float64 DEFAULT -1 CODEC(Delta, ZSTD)
)
ENGINE = MergeTree
ORDER BY (frame, symbol)

这里我们看到使用 clickhouse 的第一个好处。它完全兼容了 sql 的核心语法。要知道在设计 zillionare 2.0 版时我们被 influxdb 折磨的不行 -- 天知道他们为什么抛弃了原先对 sql 的兼容，而独出心裁地设计了一种全新的查询语言！

这意味着如果我们在团队内安装了 clickhouse -- 这常常是 IT 的活，分析师也就能直接上手 -- 因为做数据分析的人，你们都是懂 sql 的。

这里有一些技巧，是普通的 SQL 中没有的。

首先是 frame 字段中的 CODEC(Delta, ZSTD) 压缩。它巧妙地通过行间数据的差值，将列数据转换成为一个稀疏的数据向量 -- 这样可以大大减少存储空间和读取时间。实际上，在行情数据中，大量的时间戳都是相同的，或者只有很小的 delta。比如，如果我们把 5000 多支个股的分钟数据存入一张表，那么我们常常会看到连续 5000 个相同的时间戳，这些都可以存为 0！

OHLC 中的 default 值也给得颇有讲究。如果某天某个标的停牌，那么它的 OHLC 等数据就是空值。clickhouse 允许我们存空值。但这样一来，clickhouse 必须使用另外的文件来存储空值，并在查询时再通过 join 把空值连接起来。这会花掉一些时间。所以，这里我们使用了一个不可能的值作为默认值，这样所有的数据仍然存在一起，将会加快存储和运算速度。

OHLC 数据的变化都很小，所以我们也通过 DELTA 编码压缩它们。而成交量和成交额的跳动则可能很大，启用压缩就会得不偿失。

我们能做这些优化，是因为我们知道数据的分布特性 -- 就像数据分析师也必须懂得数据的分布特性一样 -- clickhouse 也是这样才能做好优化。

我们在 OHLC 数据上使用了单精度，但对 factor 却使用了 64 位浮点数。这是必要的，尽管看上去它们都很小，但是，OHLC 数据的取值范围很小，不会有精度问题，而 factor 数据则不一样，它必须更准确。

Note

为什么我们会知道这个区别？因为我们在设计 zillionare 时，遇到过这个问题。当我们把 factor 设计为 32 位精度时，在回测时，计算用户的资产时，我们发现在 1 亿左右的资金时，出现了几元或者几十元的差额。你也许可以去看一下你正在使用的框架，是否有同样的问题。

Symbol 字段我们也使用了一种优化。通过这种编码，我们实际上在存储 Symbol 时，存储的是整数而非字符串，这样会大大提高存储效率和查询速度。这个方法，如果你熟悉 Pandas 的性能优化，就应该已经见过了 -- 它就是类似于 pandas 的 categorize 优化。

最后，我们把 frame 和 symbo 设为主键。我们的大多数查询将基于这样两个字段的比较。

我们设计的表，性能究竟怎么样？

让我们先分别存入 100 万条、1000 万条和 1 亿条数据，并且分别计算插入时间和查询时间。在准备数据时，我们使用了全随机的数据，这点很重要。如果我们都使用相同的数据，那么速度会快一些。

写入 1 百万条数据，用时 8.3 秒，查询结果为 200 行时，用时 0.1 秒。
写入 1 千万条数据，用时 77.7 秒，查询结果为 2000 行时，用时 0.7 秒。
写入 1 亿条数据，用时 16 分钟左右，查询结果为 20200 行时，用时 13.7 秒。

这个结果已经很优秀了。但还看上去并没有超出预期，对吧？作为对比，我们在 influxdb 上，返回 100 万条记录时，花费 55 秒左右，其中网络传输和客户端重新组装数据占约 30 秒），这样看来，clickhouse在这一局很可能还没有超过influxdb。另外，在 1000 万级别下，优化到极致的 mysql 也能做到0.7秒以内的查询，不过它处理不了上亿条数据。

为什么没有惊喜？我去看了一下我的测试环境：

一个只有 8CPU,8GB 的虚拟机（当然底层是磁盘阵列），并且我已经开了 4 个 vscode 窗口，这样系统只剩下0.6GB的自由内存。我们测试Influxdb时，使用的是物理机，48CPU+96GB内存，总记录是30多亿条。

改天再找机会在同样的环境下进行对比测试。不过，clickhouse 员工已经在类似的行情数据库上进行了测试：

在一台 macbook pro 上，在 2.4 亿条记录中，进行 argmax 的查询，只用了 0.9 秒！这个速度虽然不够跑高频，但已足够多数场景下使用了。

不过，clickhouse 的测试与我们的测试有很大差别：

在 clickhouse 的测试中，它返回的数据量很小；而在我们的测试中，要求查询返回了 2 万条数据。

这是另一个优化方向。把能做的事情放到 clickhouse server 端做。也就是，很多因子的计算，之前我们需要取数据回 python 端再计算的，现在如果有可能，直接让 clickhouse 来做，我们只要结果。

这是我们后续笔记要发表的内容。