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 指令优化能否开启。

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

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

在这一块，确实用了很多大数据的技巧，比如使用了 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 数据则不一样，它必须更准确。

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 来做，我们只要结果。

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

量化数据本地化方案全系列发布在大富翁量化网站的这个合集下，欢迎前往一次性读完！