求大佬优化 3000w 数据多 UNION
dollck · edwin0n0 · 2022-11-10 09:36:23 +08:00 via iPhone · 2347 次点击这是一个创建于 751 天前的主题，其中的信息可能已经有所发展或是发生改变。
我有一个 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)
特别感谢大佬们，这对我非常重要
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
 |
1
qping 2022-11-10 09:42:09 +08:00
为什么要用 union 的形式， 而不是把用户数据代入到 sql 查询？
|
|
3
qping 2022-11-10 09:46:14 +08:00
SELECT * FROM A where value1 = '1' or ...... or value6 = '1'
和这样写有啥区别 |
|
4
qping 2022-11-10 09:48:32 +08:00
以我浅薄的 mysql 基础， 十分不靠谱的推断： 你那么写，会建 6 个临时表，对所有数据扫描 6 次，然后还要算上 union 去重的消耗
|
|
7
qping 2022-11-10 09:49:51 +08:00  1
没注意是 clickhouse ，打扰了
|
|
9
qping 2022-11-10 09:52:49 +08:00
你的场景，我猜是用户输入了一个东西，需要在 6 个字段都匹配，然后返回所有匹配的数据。
有试过 clickhouse 的跳表索引吗 |
 |
11
lookStupiToForce 2022-11-10 10:21:11 +08:00 1
其他支持 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 你只能参照这个基本思想去聚合六个字段的内容到一个字段，自己想办法结合查询条件去实现这个字段怎么存内容了 |
 |
12
ggex 2022-11-10 12:10:53 +08:00
查询字段有试过创建索引吗
|
 |
13
wertxx77 2022-11-10 12:24:08 +08:00
同意楼上说的将 UNION 换成 OR 的形式。此外，ClickHouse 查询影响最大的还是组合主键的列顺序，建议将能过滤数据量大列的放在第一位。
|
Select Language