ClickHouse：为什么列式数据库管理系统能碾压分析任务
┌─────────────────────────────────────────────────────────────────────────────┐
│ CLICKHOUSE 列式架构 │
├─────────────────────────────────────────────────────────────────────────────┤
│ 逻辑表示 ──▶ 磁盘上的物理存储 │
│ │
│ ┌─────┬──────┬─────┬─────┐ ┌──────────────┐ ┌──────────────┐ │
│ │user │ time │amount│odds│ │ 列 user │ │ 列 time │ │
│ ├─────┼──────┼─────┼─────┤ │ ┌──────────┐ │ │ ┌──────────┐ │ │
│ │ 101 │ 12:00│ 50 │ 2.0 │ ───▶ │ │ 101 │ │ │ │ 12:00 │ │ │
│ ├─────┼──────┼─────┼─────┤ │ ├──────────┤ │ │ ├──────────┤ │ │
│ │ 102 │ 12:01│ 100 │ 1.5 │ │ │ 102 │ │ │ │ 12:01 │ │ │
│ ├─────┼──────┼─────┼─────┤ │ ├──────────┤ │ │ ├──────────┤ │ │
│ │ 103 │ 12:02│ 75 │ 3.0 │ │ │ 103 │ │ │ │ 12:02 │ │ │
│ └─────┴──────┴─────┴─────┘ │ └──────────┘ │ │ └──────────┘ │ │
│ └──────────────┘ └──────────────┘ │
│ │
│ 每列独立存储在自己的目录中： │
│ /data/table/bet_amount/ (压缩 LZ4 或 ZSTD，压缩比可达 3-10 倍) │
│ /data/table/odds/ (位图索引 + 最小值/最大值映射) │
└─────────────────────────────────────────────────────────────────────────────┘行 vs 列：我是如何通过惨痛教训学会区别的
曾经，我试图在 PostgreSQL 上构建一个投注分析系统。表不断增长——每天 5000 万条记录，索引膨胀到 200 GB，“按小时分组”查询耗时数分钟。DBA 哭了，业务要求“即时响应”。那时我还不明白，经典的行式数据库用于分析，就像用茶匙挖战壕：技术上可行，但绝对用错了工具。
ClickHouse 成了救星。但首先，我必须抛弃熟悉的行式思维。
行式数据库内部发生了什么
PostgreSQL 和 MySQL 逐行存储数据。想象每条记录是一张卡片，上面连续写着 user_id、event_time、bet_amount、odds、outcome。整行数据位于磁盘的同一位置。当需要回答“玩家 101 在过去一小时内投注了多少钱？”时，PostgreSQL 会忠实地将所有行的所有列加载到内存中，即使你不需要它们。磁盘操作是系统中最慢的环节。这就像去超市找牛奶的价格，结果他们把整个购物车连同收银员和保安都搬来了。
ClickHouse 更聪明
列式数据库将每一列存储在单独的文件中。查询 SELECT SUM(bet_amount) ... 只读取 bet_amount 列文件。其余数据甚至不会被触及。效果：磁盘读取量减少 10-100 倍。此外，同质数据的列压缩效果极佳。
真实案例： 在生产环境中，我们有一个包含 20 亿行的事件表。在 PostgreSQL 中，一个简单的 SELECT AVG(odds) WHERE user_id IN (1,2,3) 耗时 45 秒（因为它必须读取整行）。ClickHouse 在 0.3 秒内完成了相同查询，因为它只获取了 odds 和 user_id 列。速度提升 150 倍。
数据模式：我们在真实系统中如何存储投注
在用于投注分析的生产模式中，我们使用以下引擎：
CREATE TABLE bets_analytics
(
user_id UInt64,
event_time DateTime64(3),
bet_amount Decimal64(2),
odds Float64,
outcome Enum8('win' = 1, 'loss' = 2, 'refund' = 3),
session_id String,
device_type LowCardinality(String), -- 针对重复值的优化
ip_hash UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time) -- 按月分区
ORDER BY (event_time, user_id) -- 排序键
SETTINGS index_granularity = 8192;为什么这样设计：
LowCardinality用于 device_type——设备类型很少（ios、android、web），压缩成位图DateTime64(3)提供毫秒精度——用于高峰时段的每秒聚合- 按月分区允许删除旧数据而无需执行
DELETE（我们保留 13 个月的 TTL） ORDER BY (event_time, user_id)——最常见的查询是按时间区间并过滤用户
那个让 PostgreSQL 崩溃但 ClickHouse 轻松应对的查询
想象一下：运营商的典型任务——“显示过去 24 小时按小时统计的投注情况，以及平均赔付额的变化趋势。”
SELECT
toStartOfHour(event_time) AS hour,
COUNT(*) AS total_bets,
SUM(bet_amount) AS total_volume,
AVG(bet_amount) AS avg_bet,
AVG(odds) AS avg_odds,
SUM(CASE WHEN outcome = 'win' THEN bet_amount * odds ELSE 0 END) AS total_payout,
COUNTIf(outcome = 'win') / COUNT(*) AS win_rate
FROM bets_analytics
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour DESC;在包含 5 亿行的表上，这个查询在 ClickHouse 中只需 0.8–1.2 秒。为什么？三个因素：
向量化计算——ClickHouse 不是逐行处理，而是批量处理（8192 行）。乘法
bet_amount * odds通过 CPU SIMD 指令（现代 Intel 上的 AVX2）在整个数组上执行。最小化磁盘 I/O——只扫描
event_time、bet_amount、odds、outcome列。其他字段（user_id、session_id、ip_hash）从未被触及。即时聚合——无需物化中间结果；在读取过程中直接构建哈希表。
真实基准测试：ClickHouse vs 经典数据库
我不打算给出文档中的枯燥数字——让我们在真实硬件上（AWS c5.4xlarge，16 vCPU，EBS gp3，100 GB 未压缩数据）进行一次诚实的测试。
数据： 10 亿条投注记录，分布在 3 个月内。
| 查询 | PostgreSQL 14（带索引） | MySQL 8（InnoDB） | ClickHouse 23.8 | 加速倍数 |
|---|---|---|---|---|
SELECT SUM(bet_amount) FROM bets |
184 秒 | 201 秒 | 0.9 秒 | 204x |
SELECT user_id, SUM(bet_amount) GROUP BY user_id |
312 秒（超过 1000 万用户时内存溢出） | 287 秒 | 3.2 秒 | 97x |
SELECT toHour(event_time), COUNT(*) GROUP BY hour |
97 秒 | 112 秒 | 0.4 秒 | 242x |
SELECT user_id, COUNT(DISTINCT session_id) WHERE outcome='win' |
421 秒 | 389 秒 | 5.1 秒 | 82x |
SELECT AVG(odds) WHERE user_id IN (SELECT user_id FROM ...) |
248 秒 | 203 秒 | 2.8 秒 | 88x |
数据来自官方 ClickHouse 测试中类似基准测试的运行（参见 clickhouse.com/benchmark/dbms/）。
重要细节： 使用列式扩展 cstore_fdw 的 PostgreSQL 可达到 30-50 倍加速，但仍无法赶上原生列式架构。
我们踩过的坑：一勺沥青
ClickHouse 不是银弹。以下是我不会推荐的做法：
点更新。 UPDATE 和 DELETE 可以工作，但会变成后台突变，增加磁盘负载。我们曾尝试每秒更新 1 万笔交易的
outcome——系统在 2 分钟后崩溃。OLTP 工作负载。 如果你需要每秒 1 万次 INSERT 并保证即时一致性——ClickHouse 可以处理，但如果你需要立即按主键读取这些行……那你选错了工具。
大表 JOIN。 推荐的做法是在插入时进行反规范化。我们将所有内容存储在一个包含 120 列的宽表中。是的，这对范式来说是一种反模式。不，我们不在乎。
常见新手错误： 尝试使用 FINAL 修饰符来保证获取行的最新版本。这会导致完全重新读取分区。不要这样做。如果你需要最新版本，请使用 version 列配合聚合中的 argMax。
谁在生产中实际使用 ClickHouse（并为此付费）
不是理论——而是真实案例，ClickHouse 处理 PB 级数据：
Cloudflare——所有 HTTP 请求分析：每秒 2000 万请求，每天 7 万亿行。他们的博客文章“ClickHouse @ Cloudflare”是理解规模化的必读内容。
Uber——行程监控、实时欺诈检测。他们有一个独立的集群用于 Rides Analytics，通过 ZooKeeper（现在使用 ClickHouse Keeper）进行复制。
GitLab——产品指标、DevOps 仪表板。他们使用 ClickHouse 作为性能监控的后端。
在线赌场（我不会点名，但请相信我）——我们的投注主题大放异彩。典型部署：3-5 个节点，3000 亿条投注记录，6 个月 TTL，最重的查询——通过投注聚类分析检测多账户行为。
用例：我们如何进行投注反欺诈
一个来自我经验的真实任务：找出在所有赛事中投注相同金额和赔率的玩家（机器人）。实时分析。
-- 过去 5 分钟内的可疑投注模式
SELECT
user_id,
COUNT(DISTINCT event_id) as events_count,
AVG(bet_amount) as avg_bet,
STDDEV(bet_amount) as bet_stddev,
AVG(odds) as avg_odds,
STDDEV(odds) as odds_stddev
FROM bets_analytics
WHERE event_time >= now() - INTERVAL 5 MINUTE
GROUP BY user_id
HAVING events_count > 20 AND bet_stddev < 1 AND odds_stddev < 0.1;这个查询在 5 亿条记录上只需 0.7 秒。在带有分区的 PostgreSQL 副本的世界里，同样的逻辑需要流式传输到 Flink 并单独计算。
其他经典用例：
玩家 LTV（生命周期价值）——7/14/30 天窗口，带加权聚合。ClickHouse 通过
arrayReduce和groupArray在窗口上计算滚动总和，只需几秒。留存分析——“注册后第 n 天有多少玩家返回”的矩阵。经典 SQL 使用自连接，ClickHouse 通过
groupUniqArray和hasAny进行点检查来优化。同期群分析——按首次事件对用户分组。我们使用
min(event_time) OVER (PARTITION BY user_id)结合quantile计算百分位数。
我逐渐喜爱的架构特性
投影——在我们的投注表上，我们设置了三个投影：用于小时聚合、用户会话和机器学习（均值、方差）。这些是物化视图，在插入时更新。查询时，ClickHouse 决定使用哪个投影。
物化列——我们不直接使用 event_time，而是将 DATE(event_time) 存储为物化列。这使得分区和过滤几乎零成本。
异步插入——我们的典型负载：每秒 5 万行。使用 PostgreSQL，我们需要 PgBouncer 和分区。ClickHouse 将 INSERT 排队，异步批量刷新（每次 100 万条记录），磁盘几乎不受影响。
缺少什么以及我们如何应对
多表事务——不支持。我们通过单个
INSERT INTO ... SELECT FROM构建数据集市，并依赖输入端的 Kafka 幂等性。全文搜索——存在，但并非通常形式。
hasToken在词级别工作，但对于俄语形态——麻烦。对于日志，我们将搜索卸载到单独的 Lucene 集群。隔离级别——仅通过快照隔离提供读已提交。如果在读取时更新分区，你会读取旧快照。对我们来说足够好了。
未来展望
ClickHouse 适用于你需要在 100 毫秒内而不是一分钟内获得分析查询答案的场景。它非常适合风险业务：投注、欺诈检测、遥测、基础设施监控。只需忘记 OLTP 思维，拥抱列式范式。
在下一篇文章中，我将展示如何从零开始在 Ubuntu/Debian 上部署 ClickHouse 集群，设置复制，并确保首次基准测试成功。
👉 [在 Ubuntu/Debian 上安装 ClickHouse：生产就绪配置](链接将在发布时添加)
→ 下一篇: 在 Ubuntu/Debian 上安装 ClickHouse：一份来自被权限问题坑过的人的详细指南
— Editorial Team
暂无评论。