万亿级电商广告 - brin黑科技带你(最低成本)玩转毫秒级圈人(视觉挖掘姊妹篇) - 阿里云RDS PostgreSQL, HybridDB for PostgreSQL最佳实践

背景

单机支持一万亿(100TB级)数据的毫秒级圈人，怎么做到？拥有PostgreSQL即可。

本文的应用场景来自电商的按条件圈人的广告业务。我在另外两篇文档中，分别使用了 空间数据库的视觉挖掘特性、PostgreSQL的GIN倒排索引 两种方法来实现毫秒级别的圈人。

如下：

《视觉挖掘与PostGIS空间数据库的完美邂逅 - 广告营销\圈人》

《恭迎万亿级营销(圈人)潇洒的迈入毫秒时代 - 万亿user_tags级实时推荐系统数据库设计》

既然PostgreSQL是全世界最先进的开源数据库，干一件事情，当然是有很多种方法的，对于对技术追求无止境的我，随时都有灵感冒出来，去解决一切业务上的问题。（这主要得益于PostgreSQL的先进，以及一颗热爱PostgreSQL的心。）

相比以上两种方法，BRIN的成本更低廉，效果却更赞。一定是你不可多得的选择。（仅仅增加一步数据规则即可，而广告业务通常数据都是APPEND ONLY的静态数据，数据规整是可以在业务逻辑中加进来的，不会破坏整体的美感。本方案通过业务方验证切实可行。）

废话少说，进入主题。

业务介绍

业务场景和前面两篇文档介绍的一样。

比如一家店铺，如何找到它的目标消费群体？

要回答这个问题，首先我们需要收集一些数据，比如：

1. 这家店铺以及其他的同类店铺的浏览、购买群体。

我们在逛电商时，会产生一些行为的记录，比如在什么时间，逛了哪些店铺，看了哪些商品，最后在哪家店铺购买了什么商品。

然后，对于单个商店来说，有哪些用户逛过他们的商店，购买过哪些商品，可以抽取出一部分人群。

2. 得到这些用户群体后，筛选出有同类消费欲望、或者具备相同属性的群体。

对这部分人群的属性进行分析，可以获得一个更大范围的群体，从而可以对这部分群体进行营销。

业务设计

假设

日用户浏览统计

1、日活用户一亿

2、日活店铺1亿

3、平均每个用户浏览店铺数64家

4、数据量64亿

周用户浏览统计

1、周活用户5亿

2、周活店铺2亿

3、平均每个用户浏览店铺数256家

4、总数据量1280亿

在搞活动时，假设体量*10 最大 1.28万亿。

表结构设计

1、用户浏览统计表，每天从分析系统技术，并生成新的数据，通过阿里云RDS PG的OSS外部表接口，并行导入RDS PG。

create table bi_user_tmall_vis(    
  uid int8, -- 用户ID    
  bid int8, -- 店铺ID，(商品ID使用其他表来表示，结果和查询需求类似，不再赘述)    
  cnt int   -- 浏览次数，(商品浏览次数、购买次数，使用其他表来表示，结果和查询需求类似，不再赘述)    
);    

业务查询需求

1、查询浏览了某个店铺，多少次到多少次，多少次以内，多少次以上的用户ID。

这个方法的目的是找出某一家指定店铺的目标群体。（既然浏览了你的商品，必然对你的店铺感兴趣咯。）

2、同样是以上条件，只不过是由多个OR的条件组成。

BRIN黑科技

BRIN的原理请参考（你只要记住它是几乎0成本的索引即可。）：

《PostgreSQL BRIN索引的pages_per_range选项优化与内核代码优化思考》

数据规整

为了得到好的查询效率，必须使用规整，按店铺ID和浏览次数规整。

规整方法如下：

insert into bi_user_tmall_vis1 select * from bi_user_tmall_vis1 order by bid,cnt;    

规整后，再建立bid,cnt联合BRIN索引。

create index idx_bi1 on bi_user_tmall_vis1 using brin (bid,cnt) WITH (pages_per_range='256');    

业务额外需求 - 多属性过滤

用户可能还需要对用户本身的其他属性进行过滤，例如性别、是否老顾客、年龄段等等。

那么也就是说，表结构并没有前面那么简单，只不过为了简化DEMO我做了筛检。

当需要多个查询需求时，有三种优化方法:

1、联合索引

2、多索引，然后利用PostgreSQL的bitmapAnd, bitmapOr合并索引，SKIP扫描

3、使用表分区，将其他查询条件作为分区字段进行分区。

方法的目的都是降低扫描量，提高查询效率。

多级分区表

阿里云HybridDB for PostgreSQL支持多级分区语法。

PostgreSQL则通过多级继承可以实现多级分区。同时，PostgreSQL 10或者pg_pathman插件，都支持多级分区。

(分区键外过滤优化) 利用PG内置多索引 BitmapAnd BitmapOr 扫描功能

例子

create table test(c1 int , c2 int, c3 int);    

索引1

create index idx1 on test (c1);    

索引2

create index idx3 on test (c3);    

当查询索引1和索引2的条件是，PG会自动合并这两个索引。

-- bitmapAnd scan    
    
select * from test where c1 = ? and c3=?;    
    
-- bitmapOr scan    
select * from test where c1 = ? or c3=?;    

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

64亿单表 性能DEMO

1、写入64亿测试数据

vi test.sql    
    
insert into bi_user_tmall_vis select random()*2000000000,random()*100000000,random()*1000 from generate_series(1,10000);    
    
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -t 20000    

表大小:

postgres=# \dt+ bi_user_tmall_vis    
                          List of relations    
 Schema |       Name        | Type  |  Owner   |  Size  | Description     
--------+-------------------+-------+----------+--------+-------------    
 public | bi_user_tmall_vis | table | postgres | 311 GB |     
(1 row)    

2、数据规整

create table bi_user_tmall_vis1 (like bi_user_tmall_vis);    
    
nohup psql -c "set work_mem='128GB';set maintenance_work_mem='128GB';insert into bi_user_tmall_vis1 select * from bi_user_tmall_vis order by bid,cnt;" >/dev/null 2>&1 &    

3、创建brin索引

create index idx_bi on bi_user_tmall_vis1 using brin (bid,cnt) WITH (pages_per_range='512');    

索引大小

3MB左右，夸张吧，311GB的表，索引只有3MB大小。    

4、选出浏览任意店铺，次数在N次到M次之间的用户

 public | idx_bi                 | index | postgres | bi_user_tmall_vis1 | 3336 kB    |       
      
      
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where bid=1 and cnt between 1 and 100;      
                                                               QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------      
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=521.47..105255.40 rows=7351 width=20) (actual time=16.024..25.791 rows=4 loops=1)      
   Output: uid, bid, cnt      
   Recheck Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))      
   Rows Removed by Index Recheck: 80380      
   Heap Blocks: lossy=512      
   Buffers: shared hit=529 read=511      
   ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=16.010..16.010 rows=5120 loops=1)      
         Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))      
         Buffers: shared hit=528      
 Planning time: 0.238 ms      
 Execution time: 25.822 ms      
(11 rows)      
      
      
      
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where (bid=1 and cnt between 1 and 100) or (bid=2000 and cnt <10000) or (bid=12000 and cnt <10000);      
                                  QUERY PLAN       
--------------------------------------------------------------------------------------------------------------------------------------------------------    
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=1674.17..315338.06 rows=153721 width=20) (actual time=47.115..78.014 rows=138 loops=1)      
   Output: uid, bid, cnt      
   Recheck Cond: (((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100)) OR ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000)) OR ((bi_user_tmall_vis1.bid = 12000) AND (bi_user_tmall_vis1.cnt < 10000)))      
   Rows Removed by Index Recheck: 241014      
   Heap Blocks: lossy=1536      
   Buffers: shared hit=2608 read=512      
   ->  BitmapOr  (cost=1674.17..1674.17 rows=241151 width=0) (actual time=47.099..47.099 rows=0 loops=1)      
         Buffers: shared hit=1584      
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=16.167..16.167 rows=5120 loops=1)      
               Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))      
               Buffers: shared hit=528      
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=15.494..15.494 rows=5120 loops=1)      
               Index Cond: ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000))      
               Buffers: shared hit=528      
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=15.437..15.437 rows=5120 loops=1)      
               Index Cond: ((bi_user_tmall_vis1.bid = 12000) AND (bi_user_tmall_vis1.cnt < 10000))      
               Buffers: shared hit=528      
 Planning time: 0.145 ms      
 Execution time: 78.062 ms      
(19 rows)      

索引精度	单表数据量	单表大小	索引大小	1个条件	2个条件	3个条件
pages_per_range=1	64亿	311GB	1.6GB	8.2秒	-	-
pages_per_range=128	64亿	311GB	13MB	62毫秒	-	191毫秒
pages_per_range=256	64亿	311GB	6MB	33毫秒	-	105毫秒
pages_per_range=512	64亿	311GB	3MB	25毫秒	-	78毫秒
pages_per_range=sqrt(pg_class.relpages)=6384	64亿	311GB	300KB	97毫秒	112毫秒	139毫秒

pages_per_range的优化

前面已经讲了，BRIN索引IO方面的成本分为两块，

1、扫描BRIN索引本身的块。

2、扫描HEAP表的块。

两者加起来就是IO方面的成本。其他的就是CPU过滤每一条记录的CPU运算成本。

BRIN索引扫描的IO成本估算

一、单个条件的查询，命中多少个HEAP数据块由两个因素决定：

1、单个条件值占用了多少个数据块，例如bid=1这个条件，有100万条记录，经过前面提到的数据规整，占用到了5000个数据块。

2、pages_per_range的精度，例如精度为512。也就是说至少扫描512个HEAP块。

以上两者取最大值。那么一次查询需要查询5000个HEAP数据块。

二、单个条件的查询，需要扫描多少个BRIN索引数据块则由索引本身的大小决定。

pages_per_range=512时，BRIN索引的大小为3MB左右。

三、单个条件的查询，BRIN索引扫描的IO成本，需要扫描3MB+5000个HEAP BLOCK。

四、多个条件的估算方法类似。

例如3个条件，那么需要扫描3倍的（HEAP BLOCK+BRIN IDX BLOCK）成本。

所以该怎么选择BRIN索引精度参数pages_per_range呢？

pages_per_range的计算方法

给一个衡量标准，10个条件，要求秒级内返回。

如何计算10个条件的BLOCK成本：

1、评估一个等值条件占用多少条记录(A)：

1、pg_stats.n_distinct >= 1 时  
  
(pg_class.reltuples/pg_stats.n_distinct)  
  
2、pg_stats.n_distinct < 1 时  
  
(pg_class.reltuples*pg_stats.n_distinct)/pg_class.reltuples  
  
3、pg_stats.n_distinct = -1 时  
  
1  

2、评估相关性(B)：

B = abs(pg_stats.correlation)  

3、评估一个等值条件占用多少个HEAP块(C)。

C = A / B  

4、评估pages_per_range=1时，BRIN索引占用多少个数据块（D）。

D = pg_class.relpages/(pg_class.reltuples/pg_class.relpages)  

5、评估pages_per_range=n时，BRIN索引占用多少个数据块（E）

E = D / n   

6、评估pages_per_range=n时，10个查询条件需要扫描多少个BRIN索引块(F)。

F = 10 * E  

7、评估pages_per_range=n时，10个查询条件需要扫描多少个HEAP块(G)。

G = 10 * C  

8、评估pages_per_range=n时，10个查询条件需要扫描多少个HEAP块(H)。

H = F + G  

有了这个公式，你就可以计算到底设置多大的pages_per_range，10个查询条件可以秒级以内返回了。

一万亿体量设计

周统计数据，平时的体量是千亿，搞活动万亿。

前面我们测试的是单表64亿，查询性能完全没有问题（毫秒级返回）。

那么万亿级别怎么搞呢？实际上按店铺、商品ID分区，用分区表即可解决。

例如HASH分区。

按店铺hash分成64个区，每个区1亿。

按店铺hash分成640个区，每个区2 - 20亿。

已测64亿单表性能完全不是问题。你还会担心几亿的小表吗？

PostgreSQL分区使用方法介绍：

https://github.com/postgrespro/pg_pathman

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》

《PostgreSQL 10.0 preview 功能增强 - 内置分区表》

云端产品

阿里云 RDS PostgreSQL

阿里云 HybridDB for PostgreSQL

小结

本文用到的技术点如下：

1、BRIN，帮助用户0成本，高效过滤数据。

64亿单表，任意店铺条件圈人毫秒级响应。

2、数据规整。提高字段线性相关性，一劳永逸。让BRIN的数据边界近乎完美。

数据规则以及提速的方法：

2.1 导入时就按目标顺序写入。

2.2 PG 11 并行排序。

2.3 分区表并行排序。

3、分区+数据规整，万亿级别时使用的数据优化方法。

4、HDB 规整 + metascan ，metascan与BRIN类似，是阿里云在HDB for PostgreSQL这个产品上，加的一个内核特性。原始的Greenplum是没有这个特性的。

5、并行APPEND SCAN，拆成多个分区表后，PostgreSQL通过append scan并行，可以并行扫描分区表。提升整体的性能。

6、多字段索引通过bitmapAnd, bitmapOr合并，提高数据过滤精度，降低扫描量，提升查询性能。

参考

《视觉挖掘与PostGIS空间数据库的完美邂逅 - 广告营销\圈人》

《PostgreSQL on ECS多云盘的部署、快照备份和恢复》

《PostgreSQL 10.0 preview sharding增强 - 支持Append节点并行》

《恭迎万亿级营销(圈人)潇洒的迈入毫秒时代 - 万亿user_tags级实时推荐系统数据库设计》

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》

《PostgreSQL 10.0 preview 功能增强 - 内置分区表》

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

digoal’s 大量PostgreSQL文章入口