Post​​greSQL案例精選1 - 實時精準營銷、人群圈選

Post​​greSQL案例精選1 - 實時精準營銷、人群圈選

Posted by MySQL on 2020-03-25 14:30:00

行業 :

幾乎所有行業 , 如互聯網、新零售、教育、遊戲等 .

應用場景

 

根據目標群體的特徵 , 快速提取目標群體. 例如 ,

  • 在電商行業中, 商家在搞運營活動前, 根據活動的目標群體的特徵, 圈選出一批目標用戶進行廣告推送或活動條件的命中.
  • 在遊戲行業中, 運營經常會根據遊戲玩家的某些特徵圈選, 針對性的發放大禮包, 激活遊戲活躍度.
  • 在教育行業中, 根據學生不同的特徵, 推送不同的有針對性的習題, 提升學生的弱項.
  • 在搜索、門戶、視頻網站等業務中, 根據用戶的關注熱點, 近期行為的不同, 根據群體推送內容.

場景痛點

業務特點 :

  1. 數據量龐大, 運算量大
  2. 用戶標籤多, 字段多, 存儲佔用空間多
  3. 字段多, 可能超過數據庫的字段數限制, 一般數據庫最多支持1000 多個字段.
  4. 使用數組替代多字段存儲標籤, 需要數據庫支持倒排索引, 不是所有數據庫都支持倒排索引
  5. 使用數組代替多字段存儲標籤, 加上倒排索引, 存儲空間會暴增
  6. 圈選條件組合多樣化, 沒有固定索引可以優化, 每個字段一個索引, 存儲空間暴增
  7. 性能要求高, 實時營銷, 要求秒級響應
  8. 數據更新時效要求高, 用戶畫像要求近實時的更新, 如果不能準實時更新很可能圈選到的用戶不精準( 例如用戶昨天在瀏覽手機. 昨晚已下單, 但是數據未更新, 那麼作為手機賣家圈選時這個用戶就會被選中, 但是實際上已經不是目標群體)

業務痛點 : 在常見的產品如 MySQL 中, 無法在有限的資源下, 滿足實時圈選目標群體的需求.

技術方案

方案 1

表結構 :

KEY: 用戶 ID  

標籤 1:   

標籤 2:   

…  

標籤 N:  

Copy

索引 :

每個標籤字段一個索引  

Copy

搜索方法 :

and , or , not 組合  

where 標籤 a and 標籤 b and …  

Copy

缺陷 :

  • 用戶標籤多, 字段多, 存儲佔用空間多
  • 字段多, 可能超過數據庫的字段數限制, 一般數據庫最多支持1000多個字段.
  • 圈選條件組合多樣化, 沒有固定索引可以優化, 每個字段一個索引, 存儲空間暴增
  • 新增一個新多用戶群體(標籤)時, 需要更新大量數據
  • 查詢性能差
  • 方案1也可以是多對多的結構, 每個標籤一條記錄, 解決字段數限制的問題.
    • 當然了, 字段數限制還可以通過拆表來解決, 但是查詢的時候就可能需要跨表JOIN了

方案 2

表結構 :

KEY ：用戶 ID  

VALUES ：標籤數組  

Copy

索引 :

標籤數組字段 : GIN 倒排索引  

Copy

搜索方法 :

與、或、非  

where VALUES @> array[标签s] — 与

where VALUES && array[标签s] — 或

where not VALUES @> array[标签s] — 非

Copy

缺陷 :

  • 使用數組替代多字段存儲標籤, 需要數據庫支持倒排索引, 不是所有數據庫都支持倒排索引
  • 使用數組代替多字段存儲標籤, 加上倒排索引, 存儲空間會暴增
  • 新增一個新多用戶群體(標籤)時, 需要更新大量數據

方案 3

表結構 :

KEY ：標籤 ID  

VALUES: 用戶 bitmap  

Copy

索引 :

標籤 ID 字段 : Btree 索引  

Copy

搜索方法 :

聚合bitmap: 与、或、非  
and_agg(bitmaps) where KEY in (标签s) -- 与  
or_agg(bitmaps) where KEY in (标签s) -- 或  
except(bitmap1,bitmap2) -- 非  
Copy

缺陷

  • bitmap最大長度為1GB, 用戶數超過長度需要使用offset, 方法如下:
    • o offset0_bitmap, offset1gb_bitmap, …
  • 用戶ID需要是數字(建議連續數值), 如果沒有數值型UID, 需要治理, 建立映射表.

優勢 :

  • 表存儲佔用空間小
  • 索引存儲佔用空間小
    • 僅需一個Btree索引, 索引記錄數少(有多少標籤, 就有多少條記錄, 通常標籤數在百萬以內)
  • 新增一個新多用戶群體(標籤)時, 不需要更新大量數據, 僅需新增一條新人群的bitmap記錄
  • 查詢性能極好

DEMO 介紹

通用操作

  1. 購買RDS PG 12
  2. 購買RDS MySQL 8.0
  3. 配置白名單
  4. 創建用戶
  5. 創建數據庫

方案 1 DEMO

MySQL 不支持數組類型、倒排索引、位圖功能 , 所以僅支持方案 1.

  1. MySQL 8.0

  2. PG 12

1 、創建人群表 , 每條記錄代表一個人群 .

create table t_tag_dict (

tag int primary key, -- 标签(人群)id

info text, -- 人群描述

crt_time timestamp -- 时间

);

Copy

2 、生成 10 萬個人群 ( 即標籤 )

insert into t_tag_dict values (1, '男', now());

insert into t_tag_dict values (2, '女', now());

insert into t_tag_dict values (3, '大于24岁', now()); -- ...

insert into t_tag_dict

select generate_series(4,100000), md5(random()::text), clock_timestamp();

Copy

3 、創建用戶畫像表 ( 每個用戶 N 條記錄 , 每條記錄代表這個用戶貼的某個標籤 )

create table t_user_tag (

uid int8, -- 用户id

tag int, -- 用户对应标签（人群）

mod_time timestamp, -- 时间

primary key (tag,uid)

);

Copy

4 、給 2000 萬個用戶打標 , 每個用戶 64 個隨機標籤 , 其中男、女各一半 , 一共 12.8 億條記錄

create or replace function gen_rand_tag(int,int) returns setof int as

$$

select case when random() > 0.5 then 1::int else 2::int end as tag

union all

select ceil(random()*$1)::int as tag from generate_series(1,$2);

$$ language sql strict volatile;


insert into t_user_tag

select uid, gen_rand_tag(100000,63) as tag, clock_timestamp()

from generate_series(1,20000000) as uid on conflict (uid,tag) do nothing;

— 或使用如下方法加速導入  

create sequence seq;
vi test.sql

insert into t_user_tag

select uid, gen_rand_tag(100000,63) as tag, clock_timestamp()

from nextval('seq'::regclass) as uid

on conflict(tag,uid) do nothing;


pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 400000

Copy

5 、查詢包含 1,3 標籤的人群

1 、人群數量  

select count(*) from(

select uid from t_user_tag where tag=1

intersectselect uid from t_user_tag where tag=3

) t;

-- Time: 1494.789 ms (00:01.495)

2 、提取人群 ID 

select uid from t_user_tag where tag=1

intersect

select uid from t_user_tag where tag=3;

-- Time: 3246.184 ms (00:03.246)

Copy

6 、查詢包含 1 或 3 或 10 或 200 標籤的人群

1 、人群數量  

select count(*) from(

select uid from t_user_tag where tag=1

union

select uid from t_user_tag where tag=3

union

select uid from t_user_tag where tag=10

union

select uid from t_user_tag where tag=200

) t;

-- Time: 3577.714 ms (00:03.578)

2 、提取人群 ID

select uid from t_user_tag where tag=1

union

select uid from t_user_tag where tag=3

union

select uid from t_user_tag where tag=10

union

select uid from t_user_tag where tag=200;

-- Time: 5682.458 ms (00:05.682)

Copy

7 、空間佔用情況 :

public | t_user_tag      | table | postgres | 62 GB      |

public | t_user_tag_pkey | index | postgres | t_user_tag | 61 GB |

Copy

方案 2 DEMO

  1. PG 12

1 、創建人群表 , 每條記錄代表一個人群 .

create table t_tag_dict (

tag int primary key, -- 标签(人群)id

info text, -- 人群描述

crt_time timestamp -- 时间

);

Copy

2 、生成 10 萬個人群 ( 即標籤 )

insert into t_tag_dict values (1, '男', now());

insert into t_tag_dict values (2, '女', now());

insert into t_tag_dict values (3, '大于24岁', now());

-- ...

insert into t_tag_dict select generate_series(4,100000), md5(random()::text), clock_timestamp();

Copy

3 、創建用戶畫像表 ( 每個用戶一條記錄 , 用數組表示這個用戶歸屬哪些標籤 )

create table t_user_tags (

uid int8 primary key, -- 用户id

tags int[], -- 用户标签（人群）数组

mod_time timestamp -- 时间 );

Copy

4 、創建生成隨機打標數組的函數

create or replace function gen_rand_tags(int,int) returns int[] as $$

select array_agg(ceil(random()*$1)::int) from generate_series(1,$2);

$$ language sql strict;

Copy

4.1 、在 10 萬個標籤內隨機提取 8 個標籤 :

select gen_rand_tags(100000, 8);

en_rand_tags

---------------------------------------------------

{43494,46038,74102,25308,99129,40893,33653,29690} (1 row) Copy

5 、給 2000 萬個用戶打標 , 每個用戶 64 個隨機標籤 , 其中男、女各一半

insert into t_user_tags

select generate_series(1,10000000), array_append(gen_rand_tags(100000, 63),1), now();
insert into t_user_tags

select generate_series(10000001,20000000), array_append(gen_rand_tags(100000, 63),2), now();

Copy

6 、創建標籤（人群）字段倒排索引

create index idx_t_user_tags_1 on t_user_tags using gin (tags);

Copy

7 、查詢包含 1,3 標籤的人群

1 、人群數量  

select count(uid) from t_user_tags where tags @> array[1,3];

2 、提取人群 ID  

select uid from t_user_tags where tags @> array[1,3];

Copy

8 、查詢包含 1 或 3 或 10 或 200 標籤的人群

1 、人群數量

select count(uid) from t_user_tags where tags && array[1,3,10,200];

2 、提取人群 ID  

select uid from t_user_tags where tags && array[1,3,10,200];

Copy

方案 3 DEMO

  1. PG 12

RDS PG 12 已支持位圖功能 , 常用說明：

安裝插件 – create extension roaringbitmap;  

  

bitmap 輸出格式 – set roaringbitmap.output_format='bytea|array';  

  

bitmap 取值範圍 – 40 億（ int4 ）   

  

構造 bitmap – rb_build(int4[])   

  

bitmap 轉換為數組或多條記錄 - rb_to_array(rb) – rb_iterate(rb)   

  

bitmap 內包含對像個數 – rb_cardinality(rb)   

  

邏輯運算 : 與、或、異或、差  

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 roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}');

聚合運算 : build rb 、與、或、異或  

SELECT rb_build_agg(e) FROM generate_series(1,100) e;

SELECT rb_or_agg(bitmap) FROM t1;

SELECT rb_and_agg(bitmap) FROM t1;

SELECT rb_xor_agg(bitmap) FROM t1;

聚合併統計對像數 ( 與、或、異或 )  

rb_or_cardinality_agg

rb_and_cardinality_agg

rb_xor_cardinality_agg

邏輯判斷 : 包含、相交、相等、不相等

Opperator Input Output Desc Example Result

@> roaringbitmap,roaringbitmap bool contains roaringbitmap('{1,2,3}') @> roaringbitmap('{3,4,5}') f

@> roaringbitmap,integer bool contains roaringbitmap('{1,2,3,4,5}') @> 3 t

<@ roaringbitmap,roaringbitmap bool is contained by roaringbitmap('{1,2,3}') f

<@ integer,roaringbitmap bool is contained by 3 t

&& roaringbitmap,roaringbitmap bool overlap (have elements in common) roaringbitmap('{1,2,3}') && roaringbitmap('{3,4,5}') t

= roaringbitmap,roaringbitmap bool equal roaringbitmap('{1,2,3}') = roaringbitmap('{3,4,5}') f

<> roaringbitmap,roaringbitmap bool not equal roaringbitmap('{1,2,3}') <> roaringbitmap('{3,4,5}') t

Copy

當 uid 超過 int4(40 億 ) 時 , 使用 offset 轉換 , 轉換方法如下 :

  • https://github.com/digoal/blog/blob/master/202001/20200110_03.md

其他使用方法參考 :

  • https://pgxn.org/dist/pg_roaringbitmap/

1 、安裝插件

create extension roaringbitmap;  

Copy

2 、創建標籤，用戶 bitmap 表

create table t_tag_users (  
tagid int primary key, -- 用户标签（人群）id  
uid_offset int, -- 由于userid是int8类型，roaringbitmap内部使用int4

存儲，需要轉換一下。

userbits roaringbitmap, -- 用户id聚合的 bitmap 
mod_time timestamp -- 时间 
); Copy

3 、生成標籤， uid bitmap

insert into t_tag_users

select tagid, uid_offset, rb_build_agg(uid::int) as userbits from

(

selectunnest(tags) as tagid,

(uid / (2^31)::int8) as uid_offset,

mod(uid, (2^31)::int8) as uid

from t_user_tags

) t

group by tagid, uid_offset;

Copy

4 、查詢包含 1,3 標籤的人群

1 、人群數量  

select sum(ub) from

(

select uid_offset,

rb_and_cardinality_agg(userbits) as ub

from t_tag_users

where tagid in (1,3)

group by uid_offset

) t;

2 、提取人群 ID

select uid_offset,rb_and_agg(userbits) as ub from t_tag_users where tagid in (1,3) group by uid_offset; 
Copy

5 、查詢包含 1 或 3 或 10 或 200 標籤的人群

1 、人群數量 

select sum(ub) from( select uid_offset,rb_or_cardinality_agg(userbits) as ub from t_tag_users where tagid in (1,3,10,200) group by uid_offset ) t;

2 、提取人群 ID 

select uid_offset,rb_or_agg(userbits) as ub from t_tag_users where tagid in (1,3,10,200) group by uid_offset; Copy

方案對比

環境 :

數據庫

計算規格

存儲規格

MySQL 8.0

8C 32G

1500GB ESSD

PG 12

8C 32G

1500GB ESSD

 

性能對比 :

CASE (12.8億user/tags) (2000萬, 64 tags/user)

方案 1 (MySQL、PG) 多對多 :常規方案

方案 2 (PG) 一對多 :數組、倒排索引

方案 3 (PG) 一對多 :位圖

方案 3 vs方案1 提升 %

與查詢圈選用戶速度

1.5秒

42毫秒

1.5毫秒

99900%

 

或查詢圈選用戶速度

3.6秒

3秒

1.7毫秒

211665%

空間佔用(表)

62GB

3126MB

1390MB

4467%

空間佔用(索引)

61GB

3139MB

2MB

3123100%

build索引速度

-

20分鐘

0

-

RDS PG 方案價值:

1 、RDS PG 支持了位圖功能(roaringbitmap), 可以非常高效率的生成、壓縮、解析位圖數據, 支持最常見的與、或、非、異或等位圖聚合操作, 提取位圖的ID 、選擇性, 判斷ID 是否存在等操作. 2 、使用RDS PG 數據庫, 滿足了用戶在億級以上用戶, 百萬~ 千萬量級標籤的大數據量下實時精準營銷、快速圈選用戶的需求. 3 、對比MySQL 的方案, RDS PG 方案優勢非常明顯, 是一個低成本, 高效率的解決方案.

· 節約存儲空間8948%,

· 平均性能提升155782.5%,

· 最高性能提升211665%.