有个需求要优化查询
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
还有其他好办法吗
1
zzn 2021-03-17 22:59:01 +08:00
`select * `?
要获取 100000 条裸数据的意思? |
2
Leviathann OP |
3
wmhack 2021-03-17 23:49:13 +08:00 via iPhone
用 clickhouse 就对了,我们目前单表 20 多亿,秒查结果
|
4
zzn 2021-03-17 23:51:02 +08:00
可以试试把更多过滤条件放到 ON (不确定行不行,clickhouse join 一向挺弱的)
不过,这东西的典型场景是 OLAP,鼓励宽表(冗余数据),少 join |
6
liprais 2021-03-18 08:01:58 +08:00 via iPhone
有 date 为啥不用 partition
|
7
echo1937 2021-03-18 08:31:11 +08:00
我们也在使用 clickhouse,刚起步,楼主要不起个群我们一起交流交流
|
8
aec4d 2021-03-18 09:11:23 +08:00 via iPhone
虽然它能连表,但是一般连表性能降低挺厉害,最好是组成大宽表,另外特别注意分区键,如果匹配不上分区键基本就是全表扫描,速度会降低很多,不是说大家都说它牛逼它就无所不能,使用前最好还是思考一下为什么快,又不是魔法
|
9
kerro1990 2021-03-18 09:19:36 +08:00
clickhouse 单表 70 亿,查询在毫秒级响应
|
10
Leviathann OP |
11
winnie2012 2021-03-18 10:28:48 +08:00
仔细审了下题,这个需求跟 B 表没关系,为啥要关联 B
|
12
kssdxw 2021-03-18 10:59:59 +08:00
引擎看看用的是不是 mergetree,记得设置 PARTITION BY,join 最好先 where 再 join,尽量小表 join 大表不然很慢
|
13
Leviathann OP @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 的基数大概是几十万 |
14
Leviathann OP @winnie2012 需要满足同 app_id+date 时,A 表的 shop_id 包含在 B 表 shop_id_bitmap 里...
|
15
rapperx2 2021-03-18 16:21:04 +08:00
目前 15 亿 稳稳的秒查
|
16
kele1997 2021-03-18 18:30:06 +08:00
你可以试试先用上面的查询查出 A 表的主键,然后使用 select * from A where A.id in {.......} ,估计会占内存少一些
|
17
Leviathann OP 之前有一个想法,把 B 表的 bitmap 聚合成一个 map,以 date 为 key,shop_id_bitmap 为 value,这样把表的维度压缩成 0 就能直接写在 where 条件里过滤 A 表的记录,但是翻了下文档好像并不支持这种用法,map 的 value 只支持几个简单的数据类型
最后还是决定加字段了 clickhouse 的单表 where 还是超级强力的 |