ClickHouse大表关联遇内存不足或性能缓慢问题求助

我有3个超大规模表（每个表容量超100GB、数百万行数据）：events、page_views和sessions，三者为1-n关联关系。为消除复杂分析查询中耗时的关联操作，我计划构建非规范化宽表events_wide，每条事件对应一行，并关联对应的page_views和sessions字段。

我创建了物化视图events_mv，意图在events插入新数据时，自动关联对应的page_view和session数据并写入events_wide。但插入单条新事件时，操作要么无法完成，要么触发内存不足报错。

甚至执行如下简单关联查询，也会抛出内存超限错误：Memory limit (for user) exceeded: would use 99.21 GiB。我的环境是配备24GB以上内存的ClickHouse Cloud生产实例：

SELECT
    -- 选择events和page_views中的列
FROM events AS e
LEFT JOIN page_views AS p ON p.property_id = e.property_id AND p.id = e.page_view_id
LIMIT 3;

我尝试过以下优化手段，但均未解决问题：

• 调整3个表的主键顺序（(property_id, created_at, id) 与 (property_id, id, created_at)）
• 切换不同的关联算法（partial_merge、auto、grace_hash）
• 使用ANY LEFT JOIN

我推测UUID类型的ID可能是问题诱因之一，但无法修改ID类型。

以下是采用(property_id, id, created_at)主键的表结构：

CREATE TABLE events
(
    id UUID,
    created_at DateTime('UTC'),
    property_id Int,
    page_view_id Nullable(UUID),
    session_id Nullable(UUID),
    ...
) ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(created_at)
PRIMARY KEY (property_id, id, created_at)
ORDER BY (property_id, id, created_at);

CREATE TABLE page_views
(
    id UUID,
    created_at DateTime('UTC'),
    modified_at DateTime('UTC'),
    session_id Nullable(UUID),
    ...
) ENGINE = ReplacingMergeTree(modified_at)
PARTITION BY toYYYYMM(created_at)
PRIMARY KEY (property_id, id, created_at)
ORDER BY (property_id, id, created_at);

CREATE TABLE sessions
(
    id UUID,
    created_at DateTime('UTC'),
    modified_at DateTime('UTC'),
    property_id Int,
    ...
) ENGINE = ReplacingMergeTree(modified_at)
PARTITION BY toYYYYMM(created_at)
PRIMARY KEY (property_id, id, created_at)
ORDER BY (property_id, id, created_at);


CREATE TABLE events_wide
(
    id UUID,
    created_at DateTime('UTC'),
    property_id Int,
    page_view_id Nullable(UUID),
    session_id Nullable(UUID),
    ...
    -- page_views列
    p_created_at DateTime('UTC'),
    p_modified_at DateTime('UTC'),
    ...
    -- sessions列
    s_created_at DateTime('UTC'),
    s_modified_at DateTime('UTC'),
    ...
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
PRIMARY KEY (property_id, created_at)
ORDER BY (property_id, created_at, id);


CREATE MATERIALIZED VIEW events_mv TO events_wide AS
SELECT
    e.id AS id,
    e.created_at AS created_at,
    e.session_id AS session_id,
    e.property_id AS property_id,
    e.page_view_id AS page_view_id,
    ...
    -- page_views列
    p.created_at AS p_created_at,
    p.modified_at AS p_modified_at,
    ...
    -- sessions列
    s.created_at AS s_created_at,
    s.modified_at AS s_modified_at ,
    ...
FROM events AS e
LEFT JOIN page_views AS p ON p.property_id = e.property_id AND p.id = e.page_view_id
LEFT JOIN sessions AS s ON s.property_id = e.property_id AND s.id = e.session_id
SETTINGS join_algorithm = 'partial_merge';

1. 给关联ID添加二级索引 #

针对page_views.id和sessions.id创建Bloom Filter索引，快速过滤不匹配的行，减少关联时的数据扫描量：

ALTER TABLE page_views ADD INDEX idx_id id TYPE bloom_filter;
ALTER TABLE sessions ADD INDEX idx_id id TYPE bloom_filter;

2. 限制物化视图的单次处理数据量 #

当前物化视图可能因ReplacingMergeTree特性扫描大量历史数据，可通过设置参数限制单次处理的行数和字节数：

ALTER MATERIALIZED VIEW events_mv MODIFY SETTINGS max_rows_to_read = 1000000, max_bytes_to_read = 1000000000;

同时在物化视图的SELECT语句中添加分区过滤，仅处理新增数据：

CREATE MATERIALIZED VIEW events_mv TO events_wide AS
SELECT
    -- 字段列表
FROM events AS e
LEFT JOIN page_views AS p ON p.property_id = e.property_id AND p.id = e.page_view_id
LEFT JOIN sessions AS s ON s.property_id = e.property_id AND s.id = e.session_id
WHERE toYYYYMM(e.created_at) = toYYYYMM(now()) -- 仅同步当月数据
SETTINGS join_algorithm = 'grace_hash';

3. 调整关联查询的内存参数 #

针对ClickHouse Cloud实例，提高单查询内存上限并优化grace_hash关联的磁盘阈值：

CREATE MATERIALIZED VIEW events_mv TO events_wide AS
SELECT
    -- 字段列表
FROM events AS e
LEFT JOIN page_views AS p ON p.property_id = e.property_id AND p.id = e.page_view_id
LEFT JOIN sessions AS s ON s.property_id = e.property_id AND s.id = e.session_id
SETTINGS 
    join_algorithm = 'grace_hash',
    grace_hash_join_max_bytes = 10000000000, -- 10GB磁盘临时存储阈值
    max_memory_usage_per_query = 20000000000; -- 20GB单查询内存上限

4. 改用字典表替代关联查询 #

将sessions和page_views转为内存字典，直接从内存中按ID快速查询数据，避免全表扫描：

-- 创建page_views字典
CREATE DICTIONARY page_views_dict
(
    id UUID,
    created_at DateTime('UTC'),
    modified_at DateTime('UTC'),
    session_id Nullable(UUID),
    property_id Int
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE page_views))
LAYOUT(FLAT())
LIFETIME(MIN 300 MAX 3600); -- 每5-60分钟刷新一次

-- 创建sessions字典
CREATE DICTIONARY sessions_dict
(
    id UUID,
    created_at DateTime('UTC'),
    modified_at DateTime('UTC'),
    property_id Int
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE sessions))
LAYOUT(FLAT())
LIFETIME(MIN 300 MAX 3600);

然后在物化视图中使用字典函数获取数据：

CREATE MATERIALIZED VIEW events_mv TO events_wide AS
SELECT
    e.id AS id,
    e.created_at AS created_at,
    e.session_id AS session_id,
    e.property_id AS property_id,
    e.page_view_id AS page_view_id,
    -- 从字典获取page_views字段
    dictGet('page_views_dict', 'created_at', e.page_view_id) AS p_created_at,
    dictGet('page_views_dict', 'modified_at', e.page_view_id) AS p_modified_at,
    -- 从字典获取sessions字段
    dictGet('sessions_dict', 'created_at', e.session_id) AS s_created_at,
    dictGet('sessions_dict', 'modified_at', e.session_id) AS s_modified_at,
    ...
FROM events AS e;

5. 采用离线批量同步替代实时物化视图 #

如果实时同步压力过大，可放弃物化视图，改用定时任务批量同步：每隔1小时查询events中最近1小时的新增数据，关联对应sessions和page_views数据后插入events_wide，控制单次处理的数据量。

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