含Array Join与Group By的ClickHouse查询优化求助

表结构与原查询情况 #

我有一张包含9000万+记录的student表，建表语句如下：

CREATE TABLE student(
id integer,
student_id FixedString(15) NOT NULL,
teacher_array Nested(
teacher_id String,
teacher_name String,
teacher_role_id smallint
),
subject_array Nested(
subject_id String,
subject_name String,
subject_category_id smallint
),
year integer NOT NULL
)
ENGINE=MergeTree()
PRIMARY KEY id
PARTITION BY year
ORDER BY id
SETTINGS index_granularity = 8192

以下查询执行耗时5秒，期望优化到500毫秒内完成，尝试过uniq和groupBitmap函数后，执行时间仍在2秒左右：

SELECT count(distinct id) as student_count,
(
SELECT count(distinct id)
FROM student
ARRAY JOIN teacher_array
WHERE hasAny(subject_array.subject_category_id, [1, 2]) AND (teacher_array.teacher_role_id NOT IN (1))
) AS total_student_count,
count(*) OVER () AS total_result_count,
teacher_array.teacher_role_id AS teacher_id
FROM
(
SELECT *
FROM student
ARRAY JOIN subject_array
)
ARRAY JOIN teacher_array
WHERE (subject_array.subject_category_id IN (1, 2)) AND (teacher_array.teacher_role_id NOT IN (1))
GROUP BY teacher_array.teacher_role_id
ORDER BY student_count DESC
LIMIT 0, 10

优化方案 #

1. 调整表结构与索引，降低存储与过滤成本 #

• 优化Nested字段类型：将teacher_array.teacher_role_id和subject_array.subject_category_id改为LowCardinality(smallint)，这类字段属于低基数枚举类型，LowCardinality能大幅减少存储体积，加速过滤与聚合操作。
• 重构ORDER BY/PRIMARY KEY：原表主键与排序键仅为id，无法利用过滤条件加速数据扫描。修改为ORDER BY (year, id)（若按年份查询频繁），或进一步将高频过滤的subject_array.subject_category_id前置为ORDER BY (year, subject_array.subject_category_id, id)，让MergeTree能通过索引快速定位符合条件的数据块，减少全表扫描范围。
• 开启自适应索引粒度：将index_granularity改为8192, 1024（ClickHouse 20.10+版本支持），让系统根据数据分布自动调整索引粒度，优化小范围查询的性能。

2. 重构查询逻辑，减少中间数据生成 #

原查询先ARRAY JOIN subject_array再ARRAY JOIN teacher_array会产生大量笛卡尔积中间数据，同时子查询重复扫描全表，可做如下优化：

WITH
    -- 一次扫描计算符合条件的总学生数，避免重复全表扫描
    (SELECT uniq(id) FROM student 
     WHERE hasAny(subject_array.subject_category_id, [1,2]) 
       AND arrayExists(t -> t.teacher_role_id != 1, teacher_array)) AS total_student_count
SELECT
    uniq(id) AS student_count,
    total_student_count,
    count() OVER () AS total_result_count,
    teacher_array.teacher_role_id AS teacher_id
FROM student
-- 先过滤符合科目条件的学生，再关联老师数组，减少后续处理的数据量
WHERE hasAny(subject_array.subject_category_id, [1,2])
ARRAY JOIN teacher_array
WHERE teacher_array.teacher_role_id != 1
GROUP BY teacher_array.teacher_role_id
ORDER BY student_count DESC
LIMIT 10

核心优化点：

• 先通过hasAny过滤出满足科目条件的学生，再对这些学生的teacher_array做ARRAY JOIN，大幅减少中间数据量。
• 用arrayExists替代子查询中的ARRAY JOIN，直接在原表层面判断学生是否存在符合条件的老师，避免生成不必要的关联数据。
• 用WITH子句预计算总学生数，避免重复扫描全表。

3. 利用物化视图预聚合，加速查询 #

对于高频执行的统计查询，可创建聚合物化视图预计算结果，避免每次查询都扫描全表：

CREATE MATERIALIZED VIEW mv_student_teacher_role_stats
ENGINE = AggregatingMergeTree()
PARTITION BY year
ORDER BY (teacher_role_id, year)
AS
SELECT
    year,
    teacher_array.teacher_role_id,
    groupBitmap(id) AS student_bitmap, -- 用Bitmap存储学生ID，高效去重与合并
    count() AS role_record_count
FROM student
WHERE hasAny(subject_array.subject_category_id, [1,2])
ARRAY JOIN teacher_array
WHERE teacher_array.teacher_role_id != 1
GROUP BY year, teacher_array.teacher_role_id

基于物化视图的查询语句：

WITH
    -- 合并所有角色的Bitmap，计算总符合条件的学生数
    (SELECT bitmapCardinality(groupBitmapMerge(student_bitmap)) 
     FROM mv_student_teacher_role_stats) AS total_student_count
SELECT
    bitmapCardinality(student_bitmap) AS student_count,
    total_student_count,
    sum(role_record_count) OVER () AS total_result_count,
    teacher_role_id AS teacher_id
FROM mv_student_teacher_role_stats
GROUP BY teacher_role_id, student_bitmap, role_record_count
ORDER BY student_count DESC
LIMIT 10

物化视图会在后台自动同步原表数据，查询时直接读取预聚合结果，性能可提升数倍。

4. 配置层面优化 #

• 提升内存与线程限制：修改config.xml或会话级配置，设置max_memory_usage = 32G（根据服务器内存调整）、max_threads = 16（CPU核心数的1-2倍），让查询能利用更多资源并行执行。
• 开启数据缓存：设置use_uncompressed_cache = 1，将常用的未压缩数据缓存到内存，重复查询时无需重新解压。

内容的提问来源于stack exchange，提问作者Divya Raj K