企业官网建设流程全解析

思路：单表日增 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)

• 查某个用户近期记录极高频：考虑 ProjectionORDER 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, 缺点是全量副本，存储翻倍

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

1. 接收查询

2. 将查询下发到各分片

3. 各分片本地执行

4. 协调节点合并结果，并做最终排序/聚合

因此：

• 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).长期：状态快照表 / 物化汇总表做预聚合, 冷热分离, 必要时分片

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