求大佬优化 3000w 数据多 UNION

[dollck]

我有一个 3000w 行的数据表，用户输入数据后，需要在表内 6 个字段依次查询是否与数据匹配，试过 EXPLAIN SYNTAX 但没有用 现在运行时间差不多 3-4s 之内 大家有办法吗 语句如下：

WITH A AS (SELECT * FROM otherinfor)
SELECT * FROM A where value1 = '1'UNION DISTINCT
SELECT * FROM A where value2 = '1'UNION DISTINCT
SELECT * FROM A where value3 = '1'UNION DISTINCT
SELECT * FROM A where value4 = '1'UNION DISTINCT
SELECT * FROM A where value5 = '1'UNION DISTINCT
SELECT * FROM A where value6 = '1'
下面是贴了 explain 的：

Distinct
Union
Expression ((Projection + Before ORDER BY))
Filter ((WHERE + (Projection + Before ORDER BY)))
ReadFromMergeTree (default.otherinfor)
Expression ((Projection + Before ORDER BY))
Filter ((WHERE + (Projection + Before ORDER BY)))
ReadFromMergeTree (default.otherinfor)
Expression ((Projection + Before ORDER BY))
Filter ((WHERE + (Projection + Before ORDER BY)))
ReadFromMergeTree (default.otherinfor)
Expression ((Projection + Before ORDER BY))
Filter ((WHERE + (Projection + Before ORDER BY)))
ReadFromMergeTree (default.otherinfor)
Expression ((Projection + Before ORDER BY))
Filter ((WHERE + (Projection + Before ORDER BY)))
ReadFromMergeTree (default.otherinfor)
Expression ((Projection + Before ORDER BY))
Limit (preliminary LIMIT (without OFFSET))
Filter ((WHERE + (Projection + Before ORDER BY)))
ReadFromMergeTree (default.otherinfor)
特别感谢大佬们，这对我非常重要

[qping]

为什么要用 union 的形式， 而不是把用户数据代入到 sql 查询？

[dollck]

运行时间大概是
@qping 1 就是数据

[qping]

SELECT * FROM A where value1 = '1' or ...... or value6 = '1'
和这样写有啥区别

[qping]

以我浅薄的 mysql 基础， 十分不靠谱的推断： 你那么写，会建 6 个临时表，对所有数据扫描 6 次，然后还要算上 union 去重的消耗

[dollck]

@qping csdn 说 union 比 or 要好，但我运行下来也没什么大的变化

[dollck]

@qping 可是 怎么解决呢

[qping]

没注意是 clickhouse ，打扰了

[dollck]

@qping 没事 谢谢

[qping]

你的场景，我猜是用户输入了一个东西，需要在 6 个字段都匹配，然后返回所有匹配的数据。

有试过 clickhouse 的跳表索引吗

[dollck]

@qping 没了解过 刚接触 clickhouse

[lookStupiToForce]

其他支持 array 字段类型 和 倒排索引 的数据库倒是有法子
就是把这六个列的数据当作一个 array 存起来，查的时候用倒排索引去查

1. add array column
2. set new_array_column = array[value1, value2, ..., value6]
3. select * from A where new_array_column @> array['1']

这样只用查一次倒排索引就能解决问题，可以避免 union 查 6 次索引的开销，而且你题目中还要用 distinct 去重，也就是说要对六个结果集进行一次综合排序，这额外开销太大了。

不知道 clickhouse 支不支持上面说的两样关键东西[array 字段类型]和[倒排索引]，支持的话就好办，不支持的话 op 你只能参照这个基本思想去聚合六个字段的内容到一个字段，自己想办法结合查询条件去实现这个字段怎么存内容了

[ggex]

查询字段有试过创建索引吗

[wertxx77]

同意楼上说的将 UNION 换成 OR 的形式。此外，ClickHouse 查询影响最大的还是组合主键的列顺序，建议将能过滤数据量大列的放在第一位。