ClickHouse事件收集表设计优化：兼顾查询与Join性能

针对常规查询性能良好但基于eventId的Join查询效率低下的问题，以下是几个兼顾两种场景的优化方案，可根据存储资源、写入量、查询模式选择：

方案1：为eventId添加跳数索引（MinMax索引） #

在两张MergeTree表中分别添加针对eventId的MinMax跳数索引，无需修改原有排序键，保留常规查询的性能优势，同时加速Join时的eventId等值/范围查找：

-- 给events_requests添加eventId的MinMax索引
ALTER TABLE events_requests ADD INDEX event_id_idx eventId TYPE minmax GRANULARITY 8192;

-- 给events_results添加eventId的MinMax索引
ALTER TABLE events_results ADD INDEX event_id_idx eventId TYPE minmax GRANULARITY 8192;

• 优势：不改动原有排序逻辑，写入开销极低，仅占用少量额外存储；ClickHouse可通过索引快速定位包含目标eventId的数据块，避免全表扫描。
• 注意：若eventId是全局唯一的自增ID，MinMax索引的效果会更显著，因为每个数据块的eventId范围明确，能快速过滤无关块。

方案2：添加eventId排序的Projection投影 #

利用ClickHouse的Projection功能，为两张表分别创建基于eventId排序的投影，让常规查询沿用原有排序键，Join查询自动触发投影使用：

-- 为events_requests创建eventId排序的投影
ALTER TABLE events_requests ADD PROJECTION proj_event_id (
    SELECT * ORDER BY eventId
);

-- 为events_results创建eventId排序的投影
ALTER TABLE events_results ADD PROJECTION proj_event_id (
    SELECT * ORDER BY eventId
);

• 优势：无需修改原表结构，投影异步构建不影响写入性能；查询时ClickHouse会自动判断是否使用投影（如当查询以eventId为关联条件时），兼顾两种场景的性能。
• 顾虑化解：投影仅存储排序后的额外数据，若存储资源充足，这是性价比最高的方案；可通过设置TTL清理旧数据的投影，减少存储占用。

方案3：调整排序键，兼顾时间维度与eventId #

如果允许微调原排序键，可将eventId调整到排序键的关键位置，同时保留时间维度的前缀，平衡常规时间范围查询和Join性能：

-- 修改events_requests的排序键
CREATE TABLE events_requests_new(
    eventId Int64,
    sentTime Datetime64,
    eventType LowCardinality(String)
) ENGINE MergeTree ORDER BY (toStartOfDay(sentTime), eventId, eventType);

-- 修改events_results的排序键
CREATE TABLE events_results_new(
    eventId Int64,
    endTime Datetime64,
    handlerId Int64,
    result LowCardinality(String)
) ENGINE MergeTree ORDER BY (toStartOfDay(endTime), eventId, handlerId, result);

• 优势：常规时间范围查询仍能利用toStartOfDay(sentTime)/endTime的前缀过滤，而eventId紧随其后，当Join时，同天内的eventId是有序的，ClickHouse可快速定位匹配数据。
• 注意：若常规查询经常跨天且不指定时间范围，该方案的常规查询性能可能略有下降，但多数场景下影响极小。

方案4：创建eventId排序的物化视图（适合写入量较小场景） #

若存储资源充足且写入压力不大，可创建基于eventId排序的物化视图，专门用于Join查询，原表保留用于常规查询：

-- 创建events_requests的物化视图
CREATE MATERIALIZED VIEW mv_events_requests_by_eventId
ENGINE MergeTree ORDER BY eventId
AS SELECT * FROM events_requests;

-- 创建events_results的物化视图
CREATE MATERIALIZED VIEW mv_events_results_by_eventId
ENGINE MergeTree ORDER BY eventId
AS SELECT * FROM events_results;

• 优势：物化视图的数据预排序，Join时性能最优；原表不受影响，常规查询仍保持原有效率。
• 顾虑化解：若写入量较大，可开启物化视图的异步刷新，避免同步写入带来的延迟；同时可给物化视图设置TTL，自动清理过期数据，减少存储冗余。

方案5：集群环境下用eventId作为分片键 #

如果使用ClickHouse集群，将eventId设置为Distributed表的分片键，让相同eventId的数据落在同一节点，实现本地Join，避免跨节点数据传输：

-- 创建分布式表时指定分片键为eventId
CREATE TABLE events_requests_distributed
ENGINE = Distributed('cluster_name', 'database', 'events_requests', eventId);

CREATE TABLE events_results_distributed
ENGINE = Distributed('cluster_name', 'database', 'events_results', eventId);

• 优势：Join操作仅在本地节点完成，大幅降低跨节点网络开销，Join性能提升明显；同时不影响单节点的常规查询性能。

内容的提问来源于stack exchange，提问作者Sean Goldfarb