clickhouse的bitmap表优化，从50s优化到1s的记录

背景：

服务器个数：ck小集群4台

单台服务器内存：256G

CPU：48核

bitmap存储结构，一条数据大概在2M左右

 

表的结构如下：

CREATE TABLE yiche_index.dms_pds_user_dvid_interest_bitmap
(
    `dt` LowCardinality(String) COMMENT '日期',
    `dim_type` LowCardinality(String) COMMENT '维度类型',
    `dim_id` LowCardinality(String) COMMENT '维度值',
    `devid_bmp` AggregateFunction(groupBitmap, UInt32) COMMENT '明细'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/yiche_index/dms_pds_user_dvid_interest_bitmap', '{replica}')
PARTITION BY toYYYYMMDD(toDate(dt))
PRIMARY KEY (dim_type, dim_id)
ORDER BY (dim_type, dim_id)
SETTINGS index_granularity = 8192

其中的字段devid_bmp存储的是设备ID明细;

sql如下：

select bitmapCardinality (groupBitmapOrState(devid_bmp)) from ( select devid_bmp from dms_pds_user_dvid_interest_bitmap_all prewhere dt >= '2021-01-01' and dt <= '2021-03-31' and dim_id = '奥迪' and dim_type = '3' );

当前的操作，查询需要消耗50s+ ， 在Grafana上监控发现，瓶颈在IO

 

分析查询慢的原因：

首先要了解下索引的查询过程：

假如我的bitmap表总行数是9999行 ，但是建表指定索引粒度是：index_granularity = 8192 ， 那么clickhouse就会根据粒度来划分存储区间（primary.idx）

也就是说，索引的主键1~8192是在一个索引区间内，其他的主键在另一个索引区间内

有了这个索引区间的划分之后，索引的查询查询过程就大致分成如下几步：

1、where dim_id=789 会分成一个闭区间：[789 , 789]

2、使用上一步查询的dim_id划分出的区间与生成的索引区间做交集：[789 , 789] ∩ {[1 , 8192] , [8192 , 9999]}

3、第一步交集处理后，发现索引区间的[1 , 8192]是包含[789 , 789]

到这一步就可以定位到为啥上面的sql慢了，直接背景介绍时候说过，一条bitmap数据大小是2M，但是索引区间划分是8192

也就是说我查询一天的数据，会产生：2M*8192条记录，这些记录是在本地表（查询的是分布式表），在计算的时候要产生密集的IO;

如果查询的是3个月时间，那么产生的IO粗略看就是：2M*8192*90天

所以要优化也非常简单：我们bitmap表的特点是：条数少，但每条都很大（2M），因此减小索引间隔粒度大小即可

比如将索引间隔粒度改变为4：SETTINGS index_granularity = 4

重写灌入数据后再次查询，同等条件，同等结果。本次查询只需要1s