ClickHouse 进阶指南:优化、物质化视图和真实技巧

如何把 ClickHouse 发挥到极致:ORDER BY、物质化视图、projection、编码、TTL、S3 分层和慢查询诊断。带真实 SQL。

根本原则:读更少的数据
1. 排序键:决定一切的那个决定
2. 物质化视图:把工作挪到插入时
3. Projection:同一张表按两种方式排序
4. 压缩编码:最被低估的调优
- 前后都要测
- 数据类型本身也是压缩
5. 跳过索引:谨慎使用
- 重要的警告
6. TTL 和分层:自己清理自己的数据
7. 插入:把所有人都拖垮的错误
8. 诊断:找出哪里出了问题
9. 更聪明的查询
我的优化清单
关于优化的最后一点反思
常见问题

根本原则:读更少的数据

下面所有内容都是同一个思路的变体。ClickHouse 之所以快,不是因为它处理数据快——它确实也快——而是因为它处理的数据比你以为的少得多。

你发的每条查询都会告诉你它读了多少:

6 rows in set. Elapsed: 0.021 sec. Processed 1.14 million rows, 8.32 MB

这一行才是你的主要指标。如果你的表有 5 亿行,而一条查询处理了 5 亿行,你根本没优化任何东西:你是在做一次很快的全表扫描。目标永远是把这个数字压下去。

压它的工具,按影响力排序:排序键、物质化视图、projection、跳过索引和分区。这个顺序。

1. 排序键:决定一切的那个决定

如果这篇文章你只带走一件事,请带走这一节。

稀疏索引怎么工作

ClickHouse 没有按行的 B 树索引。它按你的 ORDER BY 在磁盘上物理排序数据,每 8,192 行(一个颗粒)存一个 mark,记录那个位置上的键值。整个索引每 TB 只占几 MB。

当你用 ORDER BY 里的列过滤时,ClickHouse 看这个索引,找出可能包含匹配值的颗粒,只读那些。其他一律不碰。

结论很直接:只有当一列在 ORDER BY 的前缀里,它才能加速你的查询。如果你的键是 (evento, fecha, usuario_id),按 evento 过滤极快,按 evento AND fecha 也快,但只按 usuario_id 过滤会逼成全表扫描。顺序的重要性跟 Postgres 复合索引一样,但后果要大得多。

选 ORDER BY 的三条规则

规则 1:总是用来过滤的列放前面。 看你的真实查询,不是假想的。如果 95% 带 WHERE tenant_id = ?,tenant_id 放第一。

规则 2:使用频率一样时,低基数放前面。 一个有 6 个不同值的列(国家)比一个有 50,000 个值的列(usuario_id)更能聚拢数据。把高基数放前面会让颗粒碎片化,既毁压缩又毁跳块。

规则 3:时间列几乎总会进键里,但很少放第一。 把 timestamp 放最前很诱人,因为"所有东西都按日期过滤"。但 (tenant_id, evento, fecha) 通常比 (fecha, tenant_id, evento) 好,因为日期已经和自然插入顺序高度相关。

一个设计良好的表的例子:

CREATE TABLE eventos
(
    tenant_id   UInt32,
    fecha       Date,
    timestamp   DateTime CODEC(Delta, ZSTD(1)),
    evento      LowCardinality(String),
    pais        LowCardinality(String),
    usuario_id  UInt32 CODEC(Delta, ZSTD(1)),
    url         String CODEC(ZSTD(3)),
    duracion_ms UInt32 CODEC(T64, ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(fecha)
ORDER BY (tenant_id, evento, fecha, usuario_id);

把主键和排序键分开

一个很少人用的细节:你可以有一个长 ORDER BY 和一个更短的 PRIMARY KEY。

ENGINE = MergeTree
ORDER BY (tenant_id, evento, fecha, usuario_id)
PRIMARY KEY (tenant_id, evento)

ORDER BY 控制物理顺序(从而控制压缩),而 PRIMARY KEY 控制内存里索引存什么。如果你的排序键有五列,而你只用前两列过滤,这能减少索引的内存占用,且不损失任何东西。

怎么验证你的键有效

打开 trace,看丢弃了多少颗粒:

SET send_logs_level = 'trace';

SELECT count() FROM eventos WHERE tenant_id = 42 AND evento = 'purchase';

日志里你会看到这样的行:

Key condition: (column 0 in [42, 42]), (column 1 in ['purchase', 'purchase'])
Selected 3/48 parts by partition key, 3 parts by primary key, 14/61035 marks by primary key

14/61035 marks 意思是你读了表的 0.02%。这是一个选得好的键。如果你看到 61035/61035,说明你的过滤没用上索引。

2. 物质化视图:把工作挪到插入时

这是影响力最大、又最被误解的特性。

ClickHouse 的物质化视图不是缓存、也不是会定期刷新的表。它是一个插入触发器:每当一个数据块到达源表,查询就在这个块上跑,结果写到目标表。原始数据再也不会被读。

这有一个关键含义:物质化视图只看得到它创建之后插入的数据。 历史数据必须手动回填。

标准模式

三块:源表、聚合目标表、连接它们的视图。

-- 1. 带聚合状态的目标表
CREATE TABLE eventos_diarios
(
    tenant_id       UInt32,
    fecha           Date,
    evento          LowCardinality(String),
    pais            LowCardinality(String),
    total           UInt64,
    usuarios_unicos AggregateFunction(uniq, UInt32),
    duracion_media  AggregateFunction(avg, UInt32)
)
ENGINE = SummingMergeTree
ORDER BY (tenant_id, fecha, evento, pais);

-- 2. 喂它的物质化视图
CREATE MATERIALIZED VIEW mv_eventos_diarios
TO eventos_diarios
AS
SELECT
    tenant_id,
    fecha,
    evento,
    pais,
    count()                 AS total,
    uniqState(usuario_id)   AS usuarios_unicos,
    avgState(duracion_ms)   AS duracion_media
FROM eventos
GROUP BY tenant_id, fecha, evento, pais;

注意 uniqState 和 avgState。-State 后缀存的是聚合的中间状态,而不是最终结果。这正是让你能正确合并来自不同块的聚合的东西。

查询时,你用 -Merge 后缀:

SELECT
    fecha,
    evento,
    sum(total)                    AS eventos,
    uniqMerge(usuarios_unicos)    AS usuarios,
    avgMerge(duracion_media)      AS duracion
FROM eventos_diarios
WHERE tenant_id = 42
  AND fecha >= today() - 30
GROUP BY fecha, evento
ORDER BY fecha;

为什么这如此重要: 这条查询读的是几千行预聚合数据,而不是几亿行原始数据。差别不是 2 倍,是两到三个数量级。而且因为计算在插入时做,成本被分散到时间上,而不是集中在你用户看 dashboard 的那一刻。

常见错误:用 avg() 而不是 avgState(),查询时又对平均值取平均。这会给出错误结果,因为平均的平均不是平均。聚合状态正是为避免这个而存在。

可刷新的物质化视图

最近几个版本也有可刷新的变体,它确实会周期性重跑完整查询:

CREATE MATERIALIZED VIEW mv_resumen
REFRESH EVERY 1 HOUR
ENGINE = MergeTree ORDER BY fecha
AS SELECT ... ;

在需要和会变的维度表做 JOIN 时有用——增量视图处理不好这种情况。更贵,但更容易推理。

3. Projection:同一张表按两种方式排序

这里是经典问题:你的 ORDER BY 为按 tenant_id 过滤做了优化,但你也需要按 url 过滤的快查询。你只能有一个物理顺序。

Projection 通过在同一个表里存一份按另一种方式排序的额外副本来解决这个问题。ClickHouse 自动选择用哪个。

ALTER TABLE eventos ADD PROJECTION proj_por_url
(
    SELECT
        url,
        fecha,
        count(),
        avg(duracion_ms)
    GROUP BY url, fecha
);

-- 对已有数据物化
ALTER TABLE eventos MATERIALIZE PROJECTION proj_por_url;

现在按 url 分组的查询会用 projection,你不用改 SQL。用 EXPLAIN 验证:

EXPLAIN indexes = 1
SELECT url, count() FROM eventos WHERE fecha >= today() - 7 GROUP BY url;

Projection vs. 物质化视图。 显而易见的问题。我的标准:

用 projection:当你想要同一份数据以另一种顺序、或同一张表的一个派生聚合,并且你看重它的透明性(不改查询、一致性自动)。

用 物质化视图:当你需要变换数据、写到带不同 TTL 的表、链多个阶段、或合并多个来源。以及目标表需要独立存活的时候。

projection 的代价是存储(数据存两份)和插入速度。别给一张持续接收插入的表加五个 projection。

4. 压缩编码:最被低估的调优

磁盘上字节更少 = I/O 更少 = 查询更快。压缩不是省存储的话题,是性能的话题。

ClickHouse 默认用 LZ4,效果不错,但按列调会有真实收益。官方文档按重要性推荐:

ZSTD 作底。 它提供最好的压缩比,ZSTD(1) 对大多数类型是个好默认。超过 ZSTD(3) 很少值得那点插入成本。

Delta 用于日期和整数序列。 对单调序列或相邻值差异小的场景非常有效。时间戳和自增 ID 是经典案例。如果一阶导数的结果还不够小,试 DoubleDelta。

Delta 改进 ZSTD。 它们组合得好:CODEC(Delta, ZSTD(1)) 通常胜过单独用任何一个。

LZ4 如果和 ZSTD 打平。 如果你拿到接近的压缩比,优先 LZ4,因为它解压更快、CPU 占用更低。实际中 ZSTD 在大多数情况下以明显优势胜出。

T64 用于小范围或稀疏数据。 当块内值范围窄时有效。避免用于随机数。

Gorilla 用于传感器类浮点。 为变化小的仪表读数设计。

实践中:

CREATE TABLE metricas
(
    timestamp  DateTime      CODEC(Delta, ZSTD(1)),
    sensor_id  UInt32        CODEC(Delta, ZSTD(1)),
    temperatura Float32      CODEC(Gorilla, ZSTD(1)),
    estado     LowCardinality(String),
    payload    String        CODEC(ZSTD(3))
)
ENGINE = MergeTree
ORDER BY (sensor_id, timestamp);

前后都要测

SELECT
    name,
    formatReadableSize(sum(data_compressed_bytes))   AS comprimido,
    formatReadableSize(sum(data_uncompressed_bytes)) AS sin_comprimir,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table = 'metricas'
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC;

按压缩大小排序,只攻最大的两三列。给一列只占 3 MB 的列调编码是浪费时间。

关于 compact part 的提示: 如果你在 compressed_size 里看到零,那是因为你的 part 是 compact 而不是 wide 类型(小插入时发生)。由 min_bytes_for_wide_part 和 min_rows_for_wide_part 控制。

数据类型本身也是压缩

在动编码之前,先复查类型。一个 0 到 100 的值用 UInt8 而不是 Int64 是免费的 8 倍缩减。不同值少于约 10,000 的列用 LowCardinality(String)。日期用 DateTime 而不是 String。ClickHouse 文档显示,仅靠优化类型和排序键,同一个数据集压缩后从 50 GB 降到了 25 GB。

5. 跳过索引:谨慎使用

data skipping indexes 让你能按不在 ORDER BY 里的列过滤、跳过块。听起来像万能解,几乎从来都不是。

ALTER TABLE eventos ADD INDEX idx_pais pais TYPE set(100) GRANULARITY 4;
ALTER TABLE eventos MATERIALIZE INDEX idx_pais;

主要类型:

minmax 存每块的最小和最大值。应用最便宜。对近似有序的列和范围过滤理想。

set(N) 每块存最多 N 个不同值。全局高基数但每块内低基数时好用。

bloom_filter 用于在大值集合里测成员关系。对数组和 map 工作。

text 是真正用于全文搜索的倒排索引。这是今天推荐的;老的 tokenbf_v1 和 ngrambf_v1 已标记为弃用。

重要的警告

官方文档对此很明确,值得重复:"给一个你经常查的列加索引"这种自然冲动,在 ClickHouse 里通常是错的。

跳过索引只有在主键和被索引列之间有强相关时才有用。如果那一列的值在整个表里随机散布,每个块都会包含一些,什么也跳不掉。你为索引付出了代价(插入和查询上),换来的收益是零。

在加跳过索引之前,按这个顺序试:改 ORDER BY、加一个 projection、或建一个物质化视图。跳过索引是最后手段,不是第一手段。

确实发光的场景:稀有但重要的值。在日志表上对 error_code 加一个 set 索引,能跳过绝大多数没有错误的块。

注意这个细节,因为每个人第一次都在这里栽跟头:ClickHouse 要求 TTL 的 GROUP BY 列是主键的前缀。如果你的表按 (sensor_id, timestamp) 排序,你却按 toStartOfHour(timestamp) 分组,你会得到 TTL Expression GROUP BY key should be a prefix of primary key 错误。解决办法是把取整后的表达式放进 ORDER BY 本身,就像上面例子那样。

S3 分层

让长期保留在低成本下可行的技巧。配置一个 S3 磁盘和存储策略:

<!-- /etc/clickhouse-server/config.d/storage.xml -->
<clickhouse>
  <storage_configuration>
    <disks>
      <s3_frio>
        <type>s3</type>
        <endpoint>https://mi-bucket.s3.eu-west-1.amazonaws.com/clickhouse/</endpoint>
        <access_key_id>TU_ACCESS_KEY</access_key_id>
        <secret_access_key>TU_SECRET_KEY</secret_access_key>
      </s3_frio>
    </disks>
    <policies>
      <caliente_frio>
        <volumes>
          <caliente><disk>default</disk></caliente>
          <frio><disk>s3_frio</disk></frio>
        </volumes>
      </caliente_frio>
    </policies>
  </storage_configuration>
</clickhouse>

然后应用它:

ALTER TABLE eventos MODIFY SETTING storage_policy = 'caliente_frio';

ALTER TABLE eventos MODIFY TTL
    fecha + INTERVAL 30 DAY TO VOLUME 'frio',
    fecha + INTERVAL 365 DAY DELETE;

最近 30 天在本地 SSD(快),其余在 S3(便宜),全部一年后删除。对小项目,这正是把"我只存 30 天,因为付不起更多"变成"我花几分钱存两年"的东西。 对冷数据的查询当然更慢,但它们仍然能跑,而且是最少被跑的那些。

7. 插入:把所有人都拖垮的错误

生产环境的头号问题不是慢查询,是 Too many parts。

每次 INSERT 都在磁盘上创建一个新 part。part 在后台合并,但如果你插得比合并快,它们就堆积,直到服务器拒绝写入。

规则:批量插入

每次至少 1,000 行;理想是 10,000 到 100,000。在你的应用里缓冲,按大小或按时间(哪个先到)刷出。

替代:异步插入

如果你的架构不容易缓冲——比如很多进程各自写单个事件——那就让 ClickHouse 替你做:

SET async_insert = 1;
SET wait_for_async_insert = 1;

ClickHouse 在服务器侧缓冲里累积,按批量刷出。wait_for_async_insert = 1 时客户端等真正的写入确认(更安全);为 0 时立即得到确认(更快,但如果服务器在 flush 前挂了,你可能丢数据)。

用 async_insert_max_data_size 和 async_insert_busy_timeout_ms 调缓冲行为。

诊断

SELECT
    table,
    count()                                  AS num_parts,
    sum(rows)                                AS filas,
    formatReadableSize(sum(bytes_on_disk))   AS tamano
FROM system.parts
WHERE active AND database = 'creacosas'
GROUP BY table
ORDER BY num_parts DESC;

如果一张表有几百个活跃 part,你的插入模式就是问题。

而且,OPTIMIZE TABLE ... FINAL 不是解决方案。它强制做一次在大表上非常昂贵的完整合并,还不修根因。官方文档明确建议避免它。

8. 诊断:找出哪里出了问题

查询日志是你最好的工具

ClickHouse 把每条查询连同指标记录下来:

SELECT
    query_duration_ms,
    formatReadableQuantity(read_rows)    AS filas_leidas,
    formatReadableSize(read_bytes)       AS bytes_leidos,
    formatReadableSize(memory_usage)     AS memoria,
    substring(query, 1, 120)             AS consulta
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 20;

按耗时排序,攻最差的那批。把 read_rows 和表的总行数对比:如果接近,说明你的过滤没用上索引。

EXPLAIN

EXPLAIN indexes = 1
SELECT ... ;

它显示用了哪些索引和 projection,每步丢弃了多少颗粒。

PREWHERE

ClickHouse 通常会自动应用这个优化,但你可以强制。PREWHERE 先读过滤列,丢弃行,然后再读其余列:

SELECT url, payload_grande
FROM eventos
PREWHERE evento = 'error'      -- 先只读这一列
WHERE duracion_ms > 5000;

在你按小列过滤、又选大列的时候特别有用。

查询缓存

对带相同重复查询的 dashboard:

SELECT ... SETTINGS use_query_cache = 1;

全局配 TTL。它不是银弹——只在查询逐字重复时有用——但在一个几个人同时看的 dashboard 上,它能省不少。

9. 更聪明的查询

精度不重要时用近似聚合。 uniq() 用 HyperLogLog,比 uniqExact() 快得多、内存也轻得多。对一个"唯一用户"的 dashboard,0.5% 的误差无伤大雅。

用字典代替 JOIN。 如果你反复和一张小维度表 JOIN,把它作为内存字典加载:

CREATE DICTIONARY dic_paises (
    codigo String,
    nombre String
)
PRIMARY KEY codigo
SOURCE(CLICKHOUSE(TABLE 'paises'))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(3600);

SELECT dictGet('dic_paises', 'nombre', pais) AS pais_nombre, count()
FROM eventos GROUP BY pais;

一个 dictGet 是一次内存哈希查找:比 JOIN 快几个数量级。

组合子函数。 -If、-Array、-Merge 能省掉整个子查询:

SELECT
    fecha,
    countIf(evento = 'purchase')                      AS compras,
    countIf(evento = 'signup')                        AS registros,
    avgIf(duracion_ms, evento = 'pageview')           AS duracion_pageview
FROM eventos
GROUP BY fecha;

一次扫数据,而不是三次。

用 windowFunnel 做转化漏斗。 在标准 SQL 里算漏斗很痛苦。在这里它是一个函数:

SELECT
    nivel,
    count() AS usuarios
FROM (
    SELECT
        usuario_id,
        windowFunnel(3600)(
            timestamp,
            evento = 'pageview',
            evento = 'signup',
            evento = 'purchase'
        ) AS nivel
    FROM eventos
    WHERE fecha >= today() - 30
    GROUP BY usuario_id
)
GROUP BY nivel
ORDER BY nivel;