求大佬优化 3000w 数据多 UNION

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

已注册用户请登录

这是一个创建于 1074 天前的主题，其中的信息可能已经有所发展或是发生改变。

我有一个 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)
特别感谢大佬们，这对我非常重要

13 条回复 2022-11-10 12:24:08 +08:00

qping

2022-11-10 09:42:09 +08:00

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

dollck

2022-11-10 09:43:31 +08:00 via iPhone

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

qping

202-11-10 09:46:14 +08:00

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

qping

2022-11-10 09:48:32 +08:00

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

dollck

2022-11-10 09:48:51 +08:00 via iPhone

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

dollck

2022-11-10 09:49:34 +08:00 via iPhone

@qping 可是怎么解决呢

qping

2022-11-10 09:49:51 +08:00

没注意是 clickhouse ，打扰了

dollck

2022-11-10 09:50:20 +08:00 via iPhone

@qping 没事谢谢

qping

2022-11-10 09:52:49 +08:00

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

有试过 clickhouse 的跳表索引吗

dollck

2022-11-10 09:58:31 +08:00 via iPhone

@qping 没了解过刚接触 clickhouse

lookStupiToForce

2022-11-10 10:21:11 +08:00

其他支持 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

2022-11-10 12:10:53 +08:00

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

wertxx77

2022-11-10 12:24:08 +08:00

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