标签
PostgreSQL , BRIN , 数据规整 , 商铺浏览次数 , 商品购买次数 , 条件圈人
背景
单机支持一万亿(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 功能增强 - 内置分区表》
云端产品
小结
本文用到的技术点如下：
1、BRIN，帮助用户0成本，高效过滤数据。
64亿单表，任意店铺条件圈人毫秒级响应。
2、数据规整。提高字段线性相关性，一劳永逸。让BRIN的数据边界近乎完美。
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》