萬億級電商廣告 - 毫秒級科技(視覺挖掘姊妹篇) - 阿裡雲RDS PostgreSQL, HybridDB for PostgreSQL最佳實踐

摘要： 標籤PostgreSQL , BRIN , 資料規整, 商鋪瀏覽次數, 商品購買次數, 條件圈人幕後單機支援一萬億(100TB級)資料的毫秒級圈人，怎麼做到？擁有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的原理請參考（你只要記住它是幾乎0成本的索引即可。）：

《PostgreSQLBRIN索引的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=?;

《PostgreSQLbitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

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)

前面已經講了，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個HEAPBLOCK。

四、多個有條件的估算方法類似。

例如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的資料邊界近乎完美。

3、分區+資料規整，萬億等級時使用的資料優化方法。

4、HDB 規整 +metascan ，metascan與BRIN類似，是阿裡雲在HDB for PostgreSQL這個產品上，加的一個核心屬性。原始的Greenplum是沒有這個屬性的。

5、並行APPENDSCAN，拆成多個磁碟分割表格後，PostgreSQL通過appendscan並行，可以並行掃描磁碟分割表格。升階整體的效能。

6、多欄位索引通過bitmapAnd, bitmapOr合併，提高資料遮罩精度，降低掃描量，升階查詢效能。

《視覺挖掘與PostGIS空間資料庫的完美邂逅 - 廣告行銷圈人》

《PostgreSQLon ECS多雲盤的部署、快照備份和復原》

《PostgreSQL10.0 preview sharding美化 - 支援Append節點並行》

《恭迎萬億級行銷(圈人)瀟灑的邁入毫秒年齡 - 萬億user_tags級即時推薦系統資料庫設計》

《PostgreSQL9.5+ 高效磁碟分割表格實現 - pg_pathman》

《PostgreSQL10.0 preview 功能美化 - 內建磁碟分割表格》

《PostgreSQLbitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

相關產品：

1. 雲資料庫RDS

2. 剖析型資料庫

3. 安全管家

4. 雲端服務器ECS