1、MongoDB+PostgreSQL中文社区南京大会苏宁大规模标签场景应用实践陈华军微信：chenhj_07苏宁易购 数据库研发中心1目录海量用户下精准营销的挑战roaringbitmap在圈人场景中的作用PostgreSQL+roaringbitmap最佳实践如何用分布式PG支撑百亿标签实时查询2如何快速找到目标营销人群(圈人)？精准基于准确的用户画像实时实时查询满足条件的目标人群灵活多变的查询条件组合用户画像的实时更新可扩展支撑数亿甚至数十亿的用户规模支撑百万，亿，甚至百亿/千亿规模的标签3常规技术方案的短板方案问题Hive由于每次查询都需要对亿级的用户表做全量扫描，资源消耗极大，响应时间

2、很长Spark+ElasticSearch利用ES的索引技术结合并行处理，查询性能比Hive有几十倍的提升；业务上经常需要新增标签(字段)，导致必须重新灌全量ES数据，非常耗时。曾经用过的方案4参考：https:/ 搜索“人”到搜索“标签(人群)”用户ID性别城市1男南京2女北京标签类型标签值用户ID集合性别男1,3,5,6,.性别女2,4,7,8城市南京1,4,17,城市北京2,17,98搜”人”搜”标签”记录数十亿级百万级索引数几十，上百一个新增标签修改全量记录仅插入新标签记录计算方式组合条件过滤“人群”集合的交并差运算两种处理方式的对比6如何存储“人群”集合？-Bitmap适合大集合的交

3、并差运算-roaringbitmap是一种已被业界广泛使用的高效的bitmap压缩算法，使用者包括Elasticsearch，Durid，Hive，Spack，InfluxDB等，详见http:/roaringbitmap.org/注：Elasticsearch在查询时内部用roaringbitmap临时存储不同倒排索引的结果集做交并运算。7Roaringbitmap的存储格式Roaringbitmap 将32位的整形拆分成高16位和低16位，高16位作为容器的key，低16位作为value存储在3种不同的容器中。每个容器最多存储65536个值，最多有65536个容器。每种容器存储方法不同适用

4、于不同场景容器类型存储形式容器大小容量容器转换Array有序的short数组基数*2Byte4096基数超过4096时自动转换为Bitset容器Bitset8KB大小的bitset,每个值对应一个特定的bit位8KB65535基数低于4096时自动转换为Array容器runRLE(Run Length Encoding行程长度编码)格式，由成对的short型value+length组成。比如10,11,12,13压缩为10,34B128KB65535调用run优化且run格式存储占用空间更小时实施转换8不包含RUN容器时的序列化格式cookie(固定为12346)sizekey1card1-1

5、offset1container1container2444N4NArray容器:short数组BitSet容器:8KB的bitset每个值平均占用存储空间估算:基数/范围=1/13：使用Bitset容器，每个数值占用范围/基数bit最糟糕的情况下，每个值都分布在不同的容器中，平均1个值占用10字节。9包含RUN容器时的序列化格式cookie(固定为12347)size-1bitmapOfRunkey1card1-1offset1container1container24(N+7)/8（所有run容器对应的bit位设置为1，其它为0）4N4N（可选，N4时没有）Array容器:short数组R

6、un容器:2+4*run个数BitSet容器:8KB的bitsetn_runvalue1length1value2length210参考：https:/ 10，1112pg_roaringbitmap安装从github下载pg_roaringbitmap编译并安装插件登录到目标数据库安装扩展su postgresmakesudo make install13create extension roaringbitmappg_roaringbitmap使用示例(部分功能)Bitmap Calculation(OR,AND,XOR,ANDNOT)Bitmap Aggregate(OR,AND,XOR

7、,BUILD)CardinalitySELECT roaringbitmap(1,2,3)|roaringbitmap(3,4,5);SELECT roaringbitmap(1,2,3)&roaringbitmap(3,4,5);SELECT roaringbitmap(1,2,3)#roaringbitmap(3,4,5);SELECT roaringbitmap(1,2,3)-roaringbitmap(3,4,5);SELECT rb_or_agg(bitmap)FROM t1;SELECT rb_and_agg(bitmap)FROM t1;SELECT rb_xor_agg(bit

8、map)FROM t1;SELECT rb_build_agg(e)FROM generate_series(1,100)e;SELECT rb_cardinality(rb_build(1,2,3);14pg_roaringbitmap性能参考测试SQL：select rb_cardinality(rb_or_agg(bitmap)from bitmaptb数据量bitmap基数（注1）整形范围表大小(字节)查询时间(ms)平均记录大小(字节)平均每整形占用空间(字节)并行度计算速度(次/core/秒)1000000011w52.932525223446061 1000

9、0000110w52.005525223467394 00w52.639525223473093 亿52.23525223417098 3417098 w6826721281407.813686.823551608 0w7656079361868.86777.722675428 000w377.3413413.421142246 0亿696.1781411

10、4.121064696 1064696 01w2048000000010334.64120482.02483810 010w2827771084821462.61128282.82232963 01000w406924.88441934.2246762 010亿1.04045E+11531798.6951040410.429402 9402 1000w34.80813024051.311361 10亿2200985600

11、6086.90722009862.21164 164 01000w(注2)14.31613024050.1311400 010亿2076681011241784.536207668102.112424注1）该基数值只是插入到bitmap中的随机数，实际基数在测试数据去重后小于该数注2）实际基数在测试数据去重后大概是500w，实际每整数占用大小应该大约是0.26注3）测试环境:16C/128G/3000G SSD物理机+CentOS 7.3+PostgreSQL10.2+pg_roaringbitmap0.315超大基数bitma

12、p的存取如果使用unnest(rb_to_array()获取大结果集，不仅速度慢，而且受array类型最大1GB的限制，在bit位超过7000万时发生错误。postgres=#select count(*)from unnest(rb_to_array(rb_fill(1,10,100,1,70000000);ERROR:invalid memory alloc request size 6基于Roaringbitmap二进制格式的存取采用基于Roaringbitmap二进制格式的传输，不仅降低资源消耗，性能也有几十倍以上的提升。示例如下:create table tes

13、ttb(id int,bitmap roaringbitmap);String sql=select bitmap:bytea from testtb where id=?;PreparedStatement stmt=conn.prepareStatement(sql);stmt.setInt(1,1);ResultSet rs=stmt.executeQuery();while(rs.next()RoaringBitmap rb=new RoaringBitmap();DataInputStream is=new DataInputStream(rs.getBinaryStream(1);

14、rb.deserialize(is);is.close();rs.close();stmt.close();表定义:读取bitmap数据：17String sql=INSERT INTO testtb(id,bitmap)VALUES(?,?:bytea:roaringbitmap);PreparedStatementstmt=conn.prepareStatement(sql);RoaringBitmaprb=RoaringBitmap.bitmapOf();for(int i=0;i Distributed Subplan 3_1-HashAggregate(cost=0.00.0.00

15、rows=0 width=0)Group Key:remote_scan.brand_cd-Custom Scan(Citus Real-Time)(cost=0.00.0.00 rows=0 width=0)Task Count:32Tasks Shown:One of 32-TaskNode:host=10.47.147.130 port=6432 dbname=app_db-GroupAggregate(cost=17.65.17.72 rows=3 width=96)Group Key:brand_cd-Sort (cost=17.65.17.66 rows=3 width=96)So

16、rt Key:brand_cd-Seq Scan on member_order_102033 member_order(cost=0.00.17.62 rows=3 width=96)Filter:(statis_date=2019-05-20:date)Task Count:1Tasks Shown:All-TaskNode:host=10.47.147.130 port=6432 dbname=app_db-Limit (cost=53.22.53.47 rows=100 width=48)-Sort (cost=53.22.55.72 rows=1000 width=48)Sort K

17、ey:(rb_andnot_cardinality(intermediate_result.bitmap_cur,intermediate_result.bitmap_sum)DESC-Function Scan on read_intermediate_resultintermediate_result(cost=0.00.15.00 rows=1000 width=48)SQL：执行计划：WK上初次rb_or_agg聚合CN上最终rb_or_agg聚合Citus多CN架构(Citus MX)24PostgreSQLAppWorkerPostgreSQLCoordinatormetadata

18、tb1tb1_1PostgreSQLWorkertb1_2PostgreSQLWorker(MX node)metadatatb1tb1_1PostgreSQLWorker(MX node)metadatatb1tb1_2仅支持DMLPostgreSQLCoordinatormetadatatb1App普通Citus集群Citus MX集群支持DDL&DML优化：把最终聚合分散到所有WK上2.创建中间表25set citus.shard_count=8;-建议中间表分片数设置为等于worker数create unlogged table tb_dispatch(brand_cd text,-其

19、他维度(略)bitmap_cur roaringbitmap,bitmap_sum roaringbitmap);elect create_distributed_table(tb_dispatch,brand_cd,colocate_with=none);在CN节点的postgresql.conf中添加下面的参数1.配置Citus多CN架构citus.replication_model=streaming从CN复制元数据到所有worker节点SELECT start_metadata_sync_to_node($cituswk1_ip,$cituswk1_port);SELECT start

20、_metadata_sync_to_node($cituswk2_ip,$cituswk2_port);SELECT start_metadata_sync_to_node($cituswk3_ip,$cituswk3_port);选择其中一个分组维度作为分片字段3.初次聚合并将结果写入到中间表（在所有分片上并发执行）with tmp as(SELECT brand_cd,rb_and_cardinality(rb_or_agg(bitmap_cur),rb_or_agg(bitmap_sum)AS oldusercount,-老买家数rb_andnot_cardinality(rb_or_a

21、gg(bitmap_cur),rb_or_agg(bitmap_sum)AS newusercount-新买家数FROM tb_dispatchGROUP BY brand_cd)select*from tmp order by newusercount desc limit 100;26truncate tb_dispatch;select run_command_on_shards(member_order:regclass,$insert into tb_dispatchSELECT brand_cd,rb_or_agg(bitmap_cur)AS bitmap_cur,rb_or_ag

22、g(bitmap_sum)AS bitmap_sumFROM%sWHERE statis_date=20190520GROUP BY brand_cd$);4.从中间表收集数据进行最终聚合 该SQL将并行在每个分片上产生一个SELECT和N(中间表分片数)个INSERT。如果原始表有32个分片,中间表有8个分片，将在WK上同时产生32+32*8=288个连接。因此该SQL不适宜过多并发执行。本例中中间结果6000w1.在WK上对中间表进行初次聚合（本例中初次聚合结果集100w）2.在CN上收集WK上初次聚合的结果进行最终聚合注:中间表的分片字段包含在最终查询的分组字段中，不同分片上初次聚合的结果不存在重复。优化后，性能提升10倍，执行时间压缩到10秒以下参考https:/