V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
dollck
V2EX  ›  ClickHouse

求大佬优化 3000w 数据多 UNION

  •  
  •   dollck ·
    edwin0n0 · 2022-11-10 09:36:23 +08:00 via iPhone · 2298 次点击
    这是一个创建于 736 天前的主题,其中的信息可能已经有所发展或是发生改变。
    我有一个 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
        1
    qping  
       2022-11-10 09:42:09 +08:00
    为什么要用 union 的形式, 而不是把用户数据代入到 sql 查询?
    dollck
        2
    dollck  
    OP
       2022-11-10 09:43:31 +08:00 via iPhone
    运行时间大概是
    @qping 1 就是数据
    qping
        3
    qping  
       2022-11-10 09:46:14 +08:00
    SELECT * FROM A where value1 = '1' or ...... or value6 = '1'
    和这样写有啥区别
    qping
        4
    qping  
       2022-11-10 09:48:32 +08:00
    以我浅薄的 mysql 基础, 十分不靠谱的推断: 你那么写,会建 6 个临时表,对所有数据扫描 6 次,然后还要算上 union 去重的消耗
    dollck
        5
    dollck  
    OP
       2022-11-10 09:48:51 +08:00 via iPhone
    @qping csdn 说 union 比 or 要好,但我运行下来也没什么大的变化
    dollck
        6
    dollck  
    OP
       2022-11-10 09:49:34 +08:00 via iPhone
    @qping 可是 怎么解决呢
    qping
        7
    qping  
       2022-11-10 09:49:51 +08:00   ❤️ 1
    没注意是 clickhouse ,打扰了
    dollck
        8
    dollck  
    OP
       2022-11-10 09:50:20 +08:00 via iPhone
    @qping 没事 谢谢
    qping
        9
    qping  
       2022-11-10 09:52:49 +08:00
    你的场景,我猜是用户输入了一个东西,需要在 6 个字段都匹配,然后返回所有匹配的数据。

    有试过 clickhouse 的跳表索引吗
    dollck
        10
    dollck  
    OP
       2022-11-10 09:58:31 +08:00 via iPhone
    @qping 没了解过 刚接触 clickhouse
    lookStupiToForce
        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 你只能参照这个基本思想去聚合六个字段的内容到一个字段,自己想办法结合查询条件去实现这个字段怎么存内容了
    ggex
        12
    ggex  
       2022-11-10 12:10:53 +08:00
    查询字段有试过创建索引吗
    wertxx77
        13
    wertxx77  
       2022-11-10 12:24:08 +08:00
    同意楼上说的将 UNION 换成 OR 的形式。此外,ClickHouse 查询影响最大的还是组合主键的列顺序,建议将能过滤数据量大列的放在第一位。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5348 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 41ms · UTC 08:22 · PVG 16:22 · LAX 00:22 · JFK 03:22
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.