求大佬优化 3000w 数据多 UNION

This topic created in 1289 days ago, the information mentioned may be changed or developed.

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

projection

order

before

Where

13 replies • 2022-11-10 12:24:08 +08:00

qping

Nov 10, 2022

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

dollck

Nov 10, 2022 via iPhone

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

qping

Nov 10, 2022

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

qping

Nov 10, 2022

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

dollck

Nov 10, 2022 via iPhone

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

dollck

Nov 10, 2022 via iPhone

@qping 可是怎么解决呢

qping

Nov 10, 2022

没注意是 clickhouse ，打扰了

dollck

Nov 10, 2022 via iPhone

@qping 没事谢谢

qping

Nov 10, 2022

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

有试过 clickhouse 的跳表索引吗

dollck

Nov 10, 2022 via iPhone

@qping 没了解过刚接触 clickhouse

lookStupiToForce

Nov 10, 2022

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

Nov 10, 2022

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

wertxx77

Nov 10, 2022

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