如何让ClickHouse分布式表利用分片表约束优化查询

背景 #

我有一个events分布式表，以及对应的一组sharded_events分片表。参考物化列优化思路，我在分片表上创建了从JSON字段提取的物化列，并添加约束让查询优化器自动替换查询中的JSON函数调用为物化列，具体操作如下：

-- 调整物化列定义并完成物化
ALTER TABLE sharded_events on cluster posthog 
ALTER COLUMN materialized_$host_2 
TYPE VARCHAR DEFAULT JSONExtractString(properties, '$host');

ALTER table sharded_events on cluster posthog 
MATERIALIZE column materialized_$host_2;

alter table sharded_events on cluster posthog 
alter column materialized_$host_2 
type varchar materialized JSONExtractString(properties, '$host');

-- 添加约束，让优化器识别JSON函数与物化列的等价关系
ALTER TABLE sharded_events on CLUSTER posthog 
ADD CONSTRAINT host_2_constraint 
ASSUME JSONExtractString(properties, '$host') = materialized_$host_2

操作完成后，分片表的查询性能大幅提升：

• 开启优化设置的查询（耗时<0.1秒）：

select count(*) from sharded_events 
where JSONExtractString(properties, '$host') like '%-ca%' 
limit 1 format Vertical 
SETTINGS use_query_cache = 0, optimize_using_constraints =1, 
optimize_substitute_columns = 1, convert_query_to_cnf =1, optimize_append_index=1;

• 未开启优化的查询（耗时>2秒）：

select count(*) from sharded_events 
where JSONExtractString(properties, '$host') like '%-ca%' 
limit 1 format Vertical 
SETTINGS use_query_cache = 0;

当前问题 #

分布式表events无法添加类似约束，导致使用JSONExtractString(properties, '$host')的查询无法自动利用物化列优化：

• 直接用JSON函数的查询（耗时>2秒）：

select count(*) from events 
where JSONExtractString(properties, '$host') like '%-ca%' 
limit 1 format Vertical 
SETTINGS use_query_cache = 0, optimize_using_constraints =1, 
optimize_substitute_columns = 1, convert_query_to_cnf =1, optimize_append_index=1;

• 直接使用物化列的查询（耗时<0.1秒）：

select count(*) from events 
where materialized_$host_2 like '%-ca%' 
limit 1 format Vertical 
SETTINGS use_query_cache = 0, optimize_using_constraints =1, 
optimize_substitute_columns = 1, convert_query_to_cnf =1, optimize_append_index=1;

尝试给分布式表添加约束时，执行以下语句报错：

ALTER TABLE events on CLUSTER posthog 
ADD CONSTRAINT host_2_constraint 
ASSUME JSONExtractString(properties, '$host') = materialized_$host_2

错误信息：

DB::Exception: Alter of type 'ADD_CONSTRAINT' is not supported by storage Distributed. (NOT_IMPLEMENTED) (version 23.12.5.81 (official build)). (NOT_IMPLEMENTED)

解决方案 #

1. 重建分布式表并添加约束 #

分布式表仅作为逻辑层，不存储实际数据，因此可以删除后重新创建，在表定义中直接加入CONSTRAINT：

-- 删除原分布式表（不会影响分片表数据）
DROP TABLE IF EXISTS posthog.events ON CLUSTER posthog;

-- 重建分布式表，包含物化列和约束
CREATE TABLE posthog.events
(
    `uuid` UUID,
    `event` String,
    -- 其他原有列...
    `materialized_$host_2` String MATERIALIZED JSONExtractString(properties, '$host'),
    CONSTRAINT host_2_constraint ASSUME JSONExtractString(properties, '$host') = materialized_$host_2
)
ENGINE = Distributed('posthog', 'posthog', 'sharded_events', sipHash64(distinct_id));

2. 全局开启优化器设置 #

将以下优化设置添加到ClickHouse的配置文件（如config.xml或users.xml）中，避免每次查询手动指定：

<!-- 在config.xml中全局开启优化项 -->
<optimize_using_constraints>1</optimize_using_constraints>
<optimize_substitute_columns>1</optimize_substitute_columns>
<convert_query_to_cnf>1</convert_query_to_cnf>
<optimize_append_index>1</optimize_append_index>

3. 备选：使用视图封装查询 #

如果无法重建分布式表，可以创建视图将JSON函数调用映射到物化列，统一查询入口：

CREATE VIEW posthog.events_optimized AS
SELECT 
    uuid,
    event,
    -- 其他原有列...
    materialized_$host_2 AS host,
    materialized_$host_2
FROM posthog.events;

之后查询视图时直接使用host列即可利用物化列优化。

验证 #

重建分布式表并开启优化设置后，执行原查询：

select count(*) from events 
where JSONExtractString(properties, '$host') like '%-ca%' 
limit 1 format Vertical 
SETTINGS use_query_cache = 0;

此时查询耗时应与直接使用物化列的查询一致，达到<0.1秒的优化效果。

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