尧图网站建设 
思路单表日增 3000 万在 ClickHouse 里属于“正常量级”性能瓶颈不在数据量而在三个设计错位——用OFFSET做深分页、ORDER BY与表的主键顺序不匹配、以及缺少针对常用排序模式的物理布局, 对症下药后最后几页和多字段排序都能从几十秒降到几十毫秒, 下面按“先改查询、再改表结构、最后补运维”的顺序展开一、先搞清楚为什么慢现象原因对策翻到最后几页越来越慢OFFSET N LIMIT M需要扫描/跳过前 N 行再返回 M 行,页码越深浪费的 I/O 和计算越多若无法顺序读还要排序改用 Keyset游标分页按user_id/status排序慢MergeTree 磁盘上只按ORDER BY声明的顺序有序, 若查询排序列不是主键前缀就是全局无序需要外部排序可能 spill 到磁盘调整ORDER BY或建Projection/物化表created_at范围查询也慢分区键或主键前缀没带时间无法做分区裁剪和 granule 裁剪扫了不该扫的数据时间进入分区键和主键前缀count()算总页数慢大范围精确计数要扫大量 granule前端展示“共 98765 页”本身也是坏体验不展示总页数无过滤走 trivial count有过滤用预聚合二、表结构把物理布局定对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 字段与 CodecLowCardinality适合status这种低基数字段过滤和排序都更快DeltaZSTD适合单调递增的时间、id压缩效果好宽字段拆列payload这种大文本不参与筛选/排序单独存减少扫描 IO, ClickHouse 是列存收益明显二级跳过索引对user_id、status可加INDEX ... TYPE set(...)加速过滤但不能加速排序INDEX idx_status status TYPE set(256) GRANULARITY 4三、最后几页彻底弃用 OFFSET3.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会重写 part30M/天的表跑一次很痛, 能用 TTL 就别用 DELETE, 能追加就别更新, 必须更新就走版本列 ReplacingMergeTree查询时FINAL或用aggregating预合并不走 FINALTTL 自动清理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 万行维度行数说明日增3000 万约 350 行/秒月增约 9 亿按月分区年增约 109.5 亿长期需冷热分离假设压缩后每行 150~300 B不含大 payloadpayload 另计维度压缩后容量估算日增4.5~9 GB月增135~270 GB年增1.6~3.3 TB2 副本×22~3 个 Projection额外 ×1.5~3取决于列数和压缩比最近 30 天热数据约 9 亿行约 135~270 GB扩展建议先单分片垂直扩容优化表结构、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).长期状态快照表 / 物化汇总表做预聚合, 冷热分离, 必要时分片总结:查询模式决定物理布局分页用游标排序用投影计数用预聚合产品限制跳页 clickhouse 单表每天新增3000万数据, 然后针对于查询,怎么优化:特别是查询最后几页数据,以及查询的时候还要根据某几个字段进行排序的情况
思路单表日增 3000 万在 ClickHouse 里属于“正常量级”性能瓶颈不在数据量而在三个设计错位——用OFFSET做深分页、ORDER BY与表的主键顺序不匹配、以及缺少针对常用排序模式的物理布局, 对症下药后最后几页和多字段排序都能从几十秒降到几十毫秒, 下面按“先改查询、再改表结构、最后补运维”的顺序展开一、先搞清楚为什么慢现象原因对策翻到最后几页越来越慢OFFSET N LIMIT M需要扫描/跳过前 N 行再返回 M 行,页码越深浪费的 I/O 和计算越多若无法顺序读还要排序改用 Keyset游标分页按user_id/status排序慢MergeTree 磁盘上只按ORDER BY声明的顺序有序, 若查询排序列不是主键前缀就是全局无序需要外部排序可能 spill 到磁盘调整ORDER BY或建Projection/物化表created_at范围查询也慢分区键或主键前缀没带时间无法做分区裁剪和 granule 裁剪扫了不该扫的数据时间进入分区键和主键前缀count()算总页数慢大范围精确计数要扫大量 granule前端展示“共 98765 页”本身也是坏体验不展示总页数无过滤走 trivial count有过滤用预聚合二、表结构把物理布局定对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 字段与 CodecLowCardinality适合status这种低基数字段过滤和排序都更快DeltaZSTD适合单调递增的时间、id压缩效果好宽字段拆列payload这种大文本不参与筛选/排序单独存减少扫描 IO, ClickHouse 是列存收益明显二级跳过索引对user_id、status可加INDEX ... TYPE set(...)加速过滤但不能加速排序INDEX idx_status status TYPE set(256) GRANULARITY 4三、最后几页彻底弃用 OFFSET3.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会重写 part30M/天的表跑一次很痛, 能用 TTL 就别用 DELETE, 能追加就别更新, 必须更新就走版本列 ReplacingMergeTree查询时FINAL或用aggregating预合并不走 FINALTTL 自动清理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 万行维度行数说明日增3000 万约 350 行/秒月增约 9 亿按月分区年增约 109.5 亿长期需冷热分离假设压缩后每行 150~300 B不含大 payloadpayload 另计维度压缩后容量估算日增4.5~9 GB月增135~270 GB年增1.6~3.3 TB2 副本×22~3 个 Projection额外 ×1.5~3取决于列数和压缩比最近 30 天热数据约 9 亿行约 135~270 GB扩展建议先单分片垂直扩容优化表结构、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).长期状态快照表 / 物化汇总表做预聚合, 冷热分离, 必要时分片总结:查询模式决定物理布局分页用游标排序用投影计数用预聚合产品限制跳页