优化ClickHouse中含大型IN数组的SQL查询

针对长IN子句（2500+值）及多组IN条件的场景，结合ClickHouse的特性，给出以下实用优化方案：

1. 用arrayContains替代长IN列表 #

ClickHouse对数组操作的效率远高于解析超长IN列表（IN会被拆分为多个OR条件，解析和执行成本高）。将IN中的值转为数组，通过arrayContains做判断，多组IN条件则叠加多个arrayContains：

SELECT
    param1, param2, ..., paramN
FROM
    table1 l
LEFT JOIN table2 USING (id1)
WHERE
    arrayContains(['value1', 'value2', ..., 'valueN'], any_parameter)
    AND arrayContains(['valA', 'valB', ...], another_parameter) -- 多组IN条件叠加
ORDER BY
    another_param1 DESC, id1
LIMIT 0,25

如果值是数值类型，直接用数值数组即可，无需引号。

2. 用arrayJoin生成临时行集替代IN #

当IN中的值数量极大（比如超过10000），可以通过arrayJoin将数组转为临时行集，再用IN子查询关联，比直接写长IN列表更高效：

SELECT
    param1, param2, ..., paramN
FROM
    table1 l
LEFT JOIN table2 USING (id1)
WHERE
    any_parameter IN (SELECT * FROM arrayJoin(['value1', 'value2', ..., 'valueN']))
    AND another_parameter IN (SELECT * FROM arrayJoin(['valA', 'valB', ...]))
ORDER BY
    another_param1 DESC, id1
LIMIT 0,25

这种方式避免了SQL语句过长，同时ClickHouse对arrayJoin的处理比手动创建临时表更轻量。

3. 优化JOIN与排序逻辑，利用索引加速 #

• 简化JOIN语句：原查询中对table2的子查询包裹可以直接去掉，ClickHouse会自动只选取JOIN需要的列，减少查询计划复杂度：

  -- 简化后的JOIN写法
  LEFT JOIN table2 USING (id1)
  

• 利用排序键加速ORDER BY和LIMIT：如果another_param1 DESC, id1是高频排序字段，建表时将其设为排序键：

  -- 建表时指定排序键
  CREATE TABLE table1 (
      param1 String, param2 Int64, ..., id1 String, another_param1 Int64
  ) ENGINE = MergeTree()
  ORDER BY (another_param1 DESC, id1);
  

  这样查询时无需额外排序，直接利用有序数据快速返回LIMIT的25条结果。

4. 会话级临时表替代全局临时表 #

如果必须用临时表方案，不要创建全局临时表，改用会话级临时表（默认会话结束自动销毁），避免临时表积累：

-- 创建会话级临时表，引擎用Memory（内存存储，速度快）
CREATE TEMPORARY TABLE temp_filter_vals (val String) ENGINE = Memory
AS SELECT * FROM arrayJoin(['value1', 'value2', ..., 'valueN']);

-- 查询时关联临时表
SELECT
    param1, param2, ..., paramN
FROM
    table1 l
LEFT JOIN table2 USING (id1)
WHERE
    any_parameter IN (SELECT val FROM temp_filter_vals)
ORDER BY
    another_param1 DESC, id1
LIMIT 0,25;

会话结束后临时表自动销毁，不会产生大量残留临时表。

5. 预计算物化视图（针对高频过滤条件） #

如果某些IN取值集合是高频查询的，提前创建物化视图预筛选数据：

-- 创建物化视图，预筛选符合高频条件的数据，并按排序键存储
CREATE MATERIALIZED VIEW mv_table1_high_freq_filtered
ENGINE = MergeTree()
ORDER BY (another_param1 DESC, id1)
AS SELECT param1, param2, ..., paramN, id1, another_param1
FROM table1
WHERE any_parameter IN ('高频值1', '高频值2', ...);

-- 查询时直接从物化视图取数，无需再做过滤和排序
SELECT param1, param2, ..., paramN
FROM mv_table1_high_freq_filtered
LEFT JOIN table2 USING (id1)
LIMIT 0,25;

物化视图会定期同步源表数据，查询时直接返回预处理后的结果，速度大幅提升。

6. 移除不必要的ALL JOIN #

原查询中的ALL LEFT JOIN会保留所有匹配的重复行，如果业务不需要重复结果，直接改用普通LEFT JOIN，减少数据处理量：

-- 去掉ALL，改用普通LEFT JOIN
FROM table1 l LEFT JOIN table2 USING (id1)

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