clickhouse 单表每天新增3000万数据, 然后针对于查询,怎么优化:特别是查询最后几页数据,以及查询的时候还要根据某几个字段进行排序的情况

思路：单表日增 3000 万在 ClickHouse 里属于“正常量级”，性能瓶颈不在数据量，而在三个设计错位——用 OFFSET 做深分页、ORDER BY 与表的主键顺序不匹配、以及缺少针对常用排序模式的物理布局, 对症下药后，最后几页和多字段排序都能从几十秒降到几十毫秒, 下面按“先改查询、再改表结构、最后补运维”的顺序展开

一、先搞清楚为什么慢

二、表结构：把物理布局定对

2.1 建表示例

CREATE TABLE events
(
id UInt64, — 全局唯一，用作 tie-breaker
user_id UInt64 CODEC(Delta, ZSTD(1)),
status LowCardinality(String) CODEC(ZSTD(1)), — 低基数字典编码，必做
created_at DateTime CODEC(Delta(4), ZSTD(1)),
tenant_id UInt32 DEFAULT 0,
— 其他宽字段单独放，别跟着一起被扫描
payload String CODEC(ZSTD(3))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at) — 月分区：日增3000万，月约9亿；避免日分区导致一年365个part的管理开销与查询跨天合并放大
ORDER BY (created_at, user_id, id) — 主键 = 稀疏索引 + 磁盘排序
PRIMARY KEY (created_at, user_id) — 必须是 ORDER BY 的前缀
SETTINGS
index_granularity = 8192,
min_bytes_for_wide_part = 0, — 老版本强制 wide part，利于投影和压缩
ttl_only_drop_parts = 1; — TTL 过期直接删 part，减少 mutation

注意：上面的 ORDER BY 是示例, 实际应按最高频查询模式调整, 例如：

• 多租户查询总带 tenant_id：可考虑 ORDER BY (tenant_id, created_at, user_id, id)

• 查某个用户近期记录极高频：考虑 Projection ORDER BY (user_id, created_at, id)，而不是简单依赖 (created_at, user_id, id)

• 按用户分组取最新一条更多：考虑单独建状态快照表

几个要点：

• (1).created_at 放主键第一位, 所有查询都带时间范围，这能保证 granule 级裁剪, 注意它同时是分区键的前缀，重复没关系（分区裁剪在更上层）
• (2).id 垫在最后保证唯一性, 这是后面游标分页能成立的前提，否则相同时间戳的行会出现分页漏数或重复
• (3).类型和 Codec 是白给的性能：LowCardinality 对 status 这种字段能让过滤和排序都快一个数量级；Delta 对单调递增的 id/时间几乎免费压缩
• (4).宽表拆列：不参与筛选/排序的大文本单独存，减少扫描时的 IO, ClickHouse 是列存的，这点收益很大
• (5).如果业务上“查某个用户的近期记录”极高频，可以把 ORDER BY 改成 (created_at, user_id, id) 已覆盖；如果“按用户分组取最新一条”更多，考虑另建一张 ReplacingMergeTree(created_at) 的状态快照表

2.2 分区设计：按月还是按天？

日增 3000 万行：

• 月分区：每月约 9 亿行，仍在可控范围

• 月分区优势：分区数量少，后台 merge 压力小；DROP PARTITION 删除整月数据是瞬间元数据操作

• 日分区：一年约 365 个分区，分区管理、part 数量、跨天查询合并压力更大

• 什么时候按天分区：

  • 业务有严格按天删除的保留策略，例如只保留 30 天

  • 单月数据量增长到数十亿

  • 单分区写入或查询压力过大

2.3 ORDER BY / PRIMARY KEY 设计原则

• (1).最常用于范围过滤的列放前面：事件表通常所有查询都带时间范围，所以 created_at 放第一位，保证 granule 级裁剪

• (2).唯一列垫在最后：id 放最后，保证游标分页稳定, 否则相同时间戳的行可能导致分页漏数或重复

• (3).PRIMARY KEY 必须是 ORDER BY 的前缀：例如 ORDER BY (created_at, user_id, id)，PRIMARY KEY (created_at, user_id) 合法

• (4).排序方向要一致：查询 ORDER BY 的列顺序和方向，要尽量与表或 Projection 的排序键前缀一致，且全 ASC 或全 DESC, 否则 optimize_read_in_order 可能失效

• (5).不要盲目把低基数列放最前：status 基数低，放最前通常不能有效缩小范围, 它更适合做 Projection、跳过索引或物化汇总

2.4 字段与 Codec

• LowCardinality：适合 status 这种低基数字段，过滤和排序都更快

• Delta + ZSTD：适合单调递增的时间、id，压缩效果好

• 宽字段拆列：payload 这种大文本不参与筛选/排序，单独存，减少扫描 IO, ClickHouse 是列存，收益明显

• 二级跳过索引：对 user_id、status 可加 INDEX … TYPE set(…)，加速过滤，但不能加速排序

INDEX idx_status status TYPE set(256) GRANULARITY 4

三、最后几页：彻底弃用 OFFSET

3.1 游标分页（Keyset / Seek 分页）

不要“跳过 N 行”，而是“从上一页最后一行之后开始”, 每页返回时附带一个游标，通常是排序键的值, 下一页查询用：

WHERE (排序键…) < 上一页最后一行游标
ORDER BY 排序键…
LIMIT N

元组比较 (a, b) < (x, y) 在 ClickHouse 里是字典序比较, 正序倒序都支持：

• DESC 场景用 <

• ASC 场景用 >

关键点：WHERE 条件能利用排序键直接定位数据位置，复杂度约为 O(log n)，不会随页码加深而线性退化

SQL如下:

— 第一页
SELECT id, user_id, status, created_at
FROM events
WHERE created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-09-24 00:00:00'
AND status = 'active'
ORDER BY created_at DESC, id DESC
LIMIT 20;

— 后续页：用上一页最后一条的 (created_at, id) 当游标
SELECT id, user_id, status, created_at
FROM events
WHERE created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-09-24 00:00:00'
AND status = 'active'
AND (created_at, id) < (toDateTime('2026-09-20 15:42:01'), 18873625)
ORDER BY created_at DESC, id DESC
LIMIT 20;

关键点：

• 元组比较 (a, b) < (x, y) 在 ClickHouse 里是字典序比较，正序倒序都支持，所以 DESC 场景直接用 < 即可，不需要自己拼 OR 条件
• 排序键里出现非等值条件的列时，要把它们一起放进元组, 只要 ORDER BY 的列都在元组里，就能继续走索引顺序读，不用排序——这是从 O(N) 退化成 O(1) 的核心
• 前端“上一页”怎么做？每页缓存首尾两个游标，或者每次取 LIMIT 21，多取的那条用来判断“还有下一页”, 双向翻页通常靠客户端维护游标栈
• 万一排序字段允许 NULL，记得 IS NULL 的排序位置要和 CH 一致（CH 里 NULL 最大），否则游标衔接会错

3.2 产品层面必须配合的两件事

• 不要展示总页数: 精确 count() 在亿级数据上做范围计数很贵, 替代方案：
  • ① 只展示“下一页”
  • ② 用 SELECT count() FROM events SETTINGS optimize_trivial_count_scan = 1 只在无过滤条件时走 trivial count（毫秒级）
  • ③ 有过滤时用物化视图预聚合每日/每状态的计数，给个近似值足够
• 不允许跳页就罢了，允许的话设上限: 比如最多翻到第 100 页，超过就提示“请缩小时间范围或增加筛选条件”, 这是所有大数据库的通用做法，不是妥协

3.3 如果业务硬要“随机跳页”

折中方案：

• 后台定时任务每天跑一次，把每个常见筛选组合下每隔 K 行的锚点（created_at, id）写进一张小表 page_bookmarks，前端跳页时先查锚点再转成游标查询, 锚点表一天也就几万行，查询成本可忽略, 代价是要维护一致性，适合筛选维度固定、数据只增不改的场景
• 可用 row_number()，但必须用时间范围严格限制扫描数据量

SELECT *
FROM (
SELECT
*,
row_number() OVER (
ORDER BY created_at DESC, user_id DESC, id DESC
) AS rn
FROM events
WHERE created_at >= today() – 30
)
WHERE rn BETWEEN 1001 AND 1020;

关键：WHERE created_at >= today() – 30 这类范围条件不能少，否则性能同样退化

四、多字段排序：Projection(投影) 是正解

常排的四个字段（id / user_id / status / created_at）不可能同时满足, ClickHouse 的 Projection 就是为这个场景生的：它是同一张表的另一份物理副本，可以有自己的 ORDER BY，优化器会自动命中，SQL 一行都不用改

— 命中：ORDER BY user_id, created_at 的查询
ALTER TABLE events ADD PROJECTION p_user_created
(
SELECT * ORDER BY (user_id, created_at, id)
);

— 命中：ORDER BY status, created_at 的查询（status 基数低，效果极好）
ALTER TABLE events ADD PROJECTION p_status_created
(
SELECT * ORDER BY (status, created_at, id)
);

— 加完必须物化（重写已有数据，耗时，建议低峰期 + 分批）
ALTER TABLE events MATERIALIZE PROJECTION p_user_created;
ALTER TABLE events MATERIALIZE PROJECTION p_status_created;

使用注意事项：

• 数量控制在 2~3 个以内: 每个 projection 都是全量数据的副本，写放大和存储都会线性增长（30M/天 × 3 份 ≈ 存储翻 3 倍，实际因压缩比不同略低）, 只给真正高频的排序组合建
• 新写入的数据会自动维护 projection, 只有历史数据需要 MATERIALIZE
• 老版本（< 22.8）需要开 SET allow_experimental_projection_optimization = 1，调试期可以加 SET force_optimize_projection = 1 验证是否命中，上线后关掉
• projection 里只 SELECT 查询真正用到的列，能省存储
• 判断是否命中：看 EXPLAIN PIPELINE 里有没有 ReadFromMergeTree(projection_name)，或者查 system.projection_parts

备选方案（projection 不合适时用）

• (1).二级跳过索引：对 user_id、status 建 INDEX idx_status status TYPE set(256) GRANULARITY 4, 它只能加速过滤，不能加速排序，但配合 status = 'x' 这类高选择性条件效果明显，成本极低，值得顺手加上
• (2).物化汇总表：如果排序只是为了“列表 + 聚合统计”，用 AggregatingMergeTree 预算好，查询量级直接从行级降到组级
• (3).状态快照表：如果最常见的需求是“看每个用户当前状态的记录”，单独建一张 ReplacingMergeTree(id, created_at)，按 user_id 去重只留最新一条，数据量从 9 亿降到用户数级别，排序和分页瞬间变快, 这是业务建模层面的优化，收益往往最大
• (4).冷热分离：最近 30天热数据放 SSD 上的主表，历史数据归档到冷存储（S3/HDFS）或通过 StoragePolicy 分层, 分页基本只发生在热数据上
• 物化视图预排序: 建一个专门按目标排序键组织的物化视图表

CREATE TABLE events_by_amount
(
…
)
ENGINE = MergeTree()
ORDER BY (amount, create_time, id);

CREATE MATERIALIZED VIEW mv_events_by_amount
TO events_by_amount
AS SELECT * FROM events;

查询时直接查 events_by_amount, 缺点是全量副本，存储翻倍

分布式表下的多字段排序: 在分片集群中，多字段排序通常是全局排序, 分布式表会：

因此：

• Keyset 分页在分布式下依然有效

• 但要求每个分片都有匹配的物理排序或 Projection

• 否则每个分片都要全量排序，整体仍然慢

• force_optimize_skip_unused_shards = 1 只在查询条件包含分片键且能安全跳过分片时开启，否则可能报错，不是无条件必开

如果业务必须支持任意字段排序 + 跳页, 这是 ClickHouse 的弱项, 可以考虑：

• (1).产品限制

  • 强制时间范围 + 高选择性过滤

  • 只支持游标分页或有限跳页

  • 任意排序只开放最近 N 天数据

• (2).为高频排序组合建 Projection / 物化表

  • 覆盖 80% 的排序需求

  • 剩余长尾不做在线支持

• (3).row_number() + 时间范围

  • 仅适合后台管理系统、低并发、有限时间范围

• (4).引入外部系统

  • Elasticsearch、Doris、StarRocks 等更适合任意字段排序和跳页

  • ClickHouse 负责分析和高吞吐写入，外部系统负责搜索式分页

• (5).离线预计算

  • 对榜单、TopN、常用筛选组合做离线表

  • 在线只查预计算结果

• (6).缓存热门页

  • 对前几页、热门筛选组合做结果缓存

五、查询写法与 Session 设置清单

写法层面

• 时间范围条件必须写，且尽量写成 >= / < 的半开区间，落在分区键上才能裁剪分区
• 过滤条件里选择性最高的放前面，ClickHouse 会自动推成 PREWHERE, 也可以显式写 PREWHERE status = 'x'
• 绝不写 SELECT *，只取展示需要的列
• ORDER BY 的列顺序要和表/projection 的主键前缀一致，且方向一致（全 DESC 或全 ASC），否则 optimize_read_in_order 失效
• 避免 ORDER BY expr 里套函数（如 ORDER BY toStartOfDay(created_at)），会破坏顺序读

设置项（可按需落到 profile 里）

SET optimize_read_in_order = 1; — 默认开启，关键：匹配主键时免排序
SET max_bytes_before_external_sort = 2e9; — 排序前先spill，防止OOM杀查询
SET max_memory_usage = 8e9;
SET timeout_overflow_mode = 'break'; — 超时返回已算出的行，列表页体验好
SET max_execution_time = 30;
SET allow_experimental_projection_optimization = 1;
SET force_optimize_skip_unused_shards = 1; — 分布式表必开

排查手段: EXPLAIN PIPELINE 看是否走了projection、是否有Sorting节点, system.query_log 里盯read_rows/selected_rows 比值和 memory_usage、ProfileEvents.SortTime, 如果 read_rows 接近全表而 selected_rows 很小,说明索引没命中

六、写入与运维（日增 3000 万的坑）

• 批量写入：单次 1万~10万行、每秒不超过 1 个 part/partition, 30M/天 ≈ 平均 350 行/秒，压力不大，但要防“每秒一条”的小批量堆积 part, 可用 async_insert 或 Kafka/Bulk 缓冲
• 避免高频变更：ALTER UPDATE/DELETE 会重写 part，30M/天的表跑一次很痛, 能用 TTL 就别用 DELETE, 能追加就别更新, 必须更新就走版本列 + ReplacingMergeTree，查询时 FINAL（或用 aggregating 预合并不走 FINAL）
• TTL 自动清理：TTL created_at + INTERVAL 180 DAY DELETE，配 ttl_only_drop_parts = 1 直接删整 part
• 定期 OPTIMIZE / 合并策略：一般不需要手动OPTIMIZE, 关注 system.parts 里 part 数量，异常增长说明写入粒度有问题
• 监控告警：part 数、merge 队列、mutation 队列、查询 P99、外部排序 spill 次数

七、容量与扩展性粗估

按一行 200 字节原始、压缩比 4~6 倍算：30M/天 ≈ 6GB 原始 ≈ 1~1.5GB/天落盘, 一个月 9 亿行约 30~45GB，一年 360GB 左右, 单台 64G 内存 + NVMe 的机器扛这个量级的列表查询完全没问题；瓶颈通常在深分页和任意排序，而不是存储, 真要水平扩展就上Distributed 表 + 分片键 xxHash64(user_id) % N，注意分片键一旦定了别改，且分页跨分片时仍然要靠游标（不能用 OFFSET）, 具体扩展数据如下: 

假设日增 3000 万行：

假设压缩后每行 150~300 B（不含大 payload；payload 另计）：

扩展建议：

• 先单分片垂直扩容，优化表结构、Projection、查询写法

• 若日增超过 1 亿行，或高并发查询 P99 明显升高，再考虑 2~4 分片

• 分片后每个分片都要有匹配的本地排序或 Projection，否则全局排序仍慢

八、落地优先级（按投入产出排）

• (1).立刻做：OFFSET 改游标分页 + 去掉总页数展示, （零成本，收益最大，直接解决“最后几页慢”）
• (2).一周内：核对 ORDER BY 主键是否以 created_at 开头, 补 LowCardinality/Codec；加时间范围强制过滤（不改 SQL 就能快几倍）
• (3).一个月内：给最高频的 1~2 个排序组合建 projection（大概率是 status + created_at 和 user_id + created_at）, 补跳过索引
• (4).长期：状态快照表 / 物化汇总表做预聚合, 冷热分离, 必要时分片

总结: 查询模式决定物理布局；分页用游标；排序用投影；计数用预聚合；产品限制跳页