列式数据库 clickhouse 单机能处理十几亿数据吗

[Leviathann]

有个需求要优化查询 A 表有 18 亿数据，大概每天千万条，每个数据都有 app_id , shop_id 和 date ，然后是 order by date

B 表则是每个 app_id 一天一条数据，每条数据有一个存了符合某种情况的 shop_id 的 bitmap

现在要查满足某种情况的 shop 在 A 表里的数据，limit 100000

试了下

select * from A inner join B on A.app_id = B.app_id and A.date = B.date 
where app_id = 1 
and date between '2021-03-10' and '2021-03-16'
and bitmapContains(B.bitmap, A.shop_id) 
limit 10;

这样完全都跑不动，报内存不够了，昨天的数据也就千万级左右，用子查询先把 B 表用 date 过滤一下再 join 也没用

我猜想 clickhouse 是先全 join 起来再用 where 过滤

查了下文档发现能构造 map，感觉可以用 B 表构造一个 date-bitmap 的 map 然后直接 where 就行

但是直接复制文档上的构造 map 的 sql 都报语法错误，好像是我这个版本根本不支持 map

还有其他好办法吗

[zzn]

`select * `?
要获取 100000 条裸数据的意思？

[Leviathann]

@zzn
是的
差不多就是 select * ，因为主要内容就一个长度在 2 、30 左右的字符串，其他的都是相关信息，基本都是 uint8 、时间啥的，包括上面提到的一共就 9 列

[wmhack]

用 clickhouse 就对了，我们目前单表 20 多亿，秒查结果

[zzn]

可以试试把更多过滤条件放到 ON （不确定行不行，clickhouse join 一向挺弱的）

不过，这东西的典型场景是 OLAP，鼓励宽表（冗余数据），少 join

[ericls]

@zzn exactly!

[liprais]

有 date 为啥不用 partition

[echo1937]

我们也在使用 clickhouse，刚起步，楼主要不起个群我们一起交流交流

[aec4d]

虽然它能连表，但是一般连表性能降低挺厉害，最好是组成大宽表，另外特别注意分区键，如果匹配不上分区键基本就是全表扫描，速度会降低很多，不是说大家都说它牛逼它就无所不能，使用前最好还是思考一下为什么快，又不是魔法

[kerro1990]

clickhouse 单表 70 亿，查询在毫秒级响应

[Leviathann]

@liprais
@aec4d
说实话我没搞懂这个 partition 和 order by 之间的关系
是数据按照 partition 分文件，然后文件内部用 order by 排序并按照粒度做了一个索引，如果查询的东西不再某个颗粒的索引范围就直接跳过这个颗粒包含的数据？
这个 A 表是用 app_id, toYYYYMM(date)...

[winnie2012]

仔细审了下题，这个需求跟 B 表没关系，为啥要关联 B

[kssdxw]

引擎看看用的是不是 mergetree，记得设置 PARTITION BY，join 最好先 where 再 join，尽量小表 join 大表不然很慢

[Leviathann]

@kssdxw
A 是 replace merge tree
B 是 aggregating merge tree

我先用子查询把 A 限制到 10000 行，把 B 表 限制到 1 行，然后再 join B 表，
最外面的 select 只选 A 表的 app_id date 和一个字符串字段（长度 2 、30 左右）
结果都要 6.7 秒

join B 表的时候是结果有多少条就会把 B 表的 bitmap 复制多少次吗...这个 bitmap 的基数大概是几十万

[Leviathann]

@winnie2012 需要满足同 app_id+date 时，A 表的 shop_id 包含在 B 表 shop_id_bitmap 里...

[rapperx2]

目前 15 亿 稳稳的秒查

[kele1997]

你可以试试先用上面的查询查出 A 表的主键，然后使用 select * from A where A.id in {.......} ，估计会占内存少一些

[Leviathann]

之前有一个想法，把 B 表的 bitmap 聚合成一个 map，以 date 为 key，shop_id_bitmap 为 value，这样把表的维度压缩成 0 就能直接写在 where 条件里过滤 A 表的记录，但是翻了下文档好像并不支持这种用法，map 的 value 只支持几个简单的数据类型
最后还是决定加字段了
clickhouse 的单表 where 还是超级强力的