ApsaraDB RDS：即時精準營銷（人群圈選）

方案一

方案概覽

• 表結構：

  KEY: 使用者ID
  標籤1: 
  標籤2: 
  ...
  標籤N:

• 表索引：每個標籤欄位一個索引。

• 搜尋方法：

  and , or , not 組合
  where 標籤a and 標籤b and ...

• 方案缺陷：

  • 使用者標籤多、欄位多，佔用儲存空間也多。

  • 欄位多，可能會超過資料庫的欄位數限制，一般資料庫最多支援1000多個欄位。也可以設定為多對多的結構，每個標籤一條記錄，解決欄位數限制的問題。

  • 圈選條件組合多樣化，沒有固定索引可以最佳化，如果每個欄位一個索引，儲存空間又會暴增。

  • 新增一個使用者群體標籤時，需要更新大量資料。

  • 查詢效能差。

操作樣本

1. 建立人群表，每條記錄代表一個人群。樣本如下：

   create table t_tag_dict (
   tag int primary key,   -- 標籤(人群)id
   info text,  -- 人群描述
   crt_time timestamp  -- 時間
   ); 

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();

3. 建立使用者畫像表，每個使用者N條記錄，每條記錄代表這個使用者貼的某個標籤。樣本如下：

   create table t_user_tag (
   uid int8,   -- 使用者id
   tag int,            -- 使用者對應標籤（人群）
   mod_time timestamp,     -- 時間
   primary key (tag,uid)
   ); 

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

5. 查詢包含標籤1和3的人群。樣本如下：

   -- 人群數量
   select count(*) from 
   (
   select uid from t_user_tag where tag=1 
   intersect
   select uid from t_user_tag where tag=3
   ) t;
   -- Time: 1494.789 ms (00:01.495)
   
   -- 提取人群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)

6. 查詢包含標籤1、3、10或200的人群。樣本如下：

   -- 人群數量
   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)
   
   -- 提取人群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)

方案二

方案概覽

• 表結構：

  KEY：使用者ID
  VALUES：標籤數組

• 表索引：

  標籤數組欄位: GIN倒排索引

• 搜尋方法

  與、或、非
  where VALUES @> array[標籤s] -- 與
  where VALUES && array[標籤s] -- 或
  where not VALUES @> array[標籤s] -- 非

• 方案缺陷

  • 使用數組替代多欄位儲存標籤，需要資料庫支援倒排索引，但並不是所有資料庫都支援倒排索引。

  • 使用數組替代多欄位儲存標籤，加上倒排索引，儲存空間會暴增。

  • 新增一個使用者群體標籤時，需要更新大量資料。

操作樣本

1. 建立人群表，每條記錄代表一個人群。樣本如下：

   create table t_tag_dict (
   tag int primary key,   -- 標籤(人群)id
   info text,  -- 人群描述
   crt_time timestamp  -- 時間
   ); 

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();

3. 建立使用者畫像表，每個使用者一條記錄，用數組表示這個使用者歸屬哪些標籤。樣本如下：

   create table t_user_tags (
   uid int8 primary key,   -- 使用者id
   tags int[],            -- 使用者標籤（人群）數組
   mod_time timestamp     -- 時間
   ); 

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;

5. 在10萬個標籤內隨機提取8個標籤。樣本如下：

   select gen_rand_tags(100000, 8);
   
                      gen_rand_tags                   
   ---------------------------------------------------
    {43494,46038,74102,25308,99129,40893,33653,29690}
   (1 row)

6. 給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();

7. 建立人群標籤欄位倒排索引。樣本如下：

   create index idx_t_user_tags_1 on t_user_tags using gin (tags);

8. 查詢包含標籤1和3的人群。樣本如下：

   -- 人群數量
   select count(uid) from t_user_tags where tags @> array[1,3];
   
   -- 提取人群ID
   select uid from t_user_tags where tags @> array[1,3];

9. 查詢包含標籤1、3、10或200的人群。樣本如下：

   -- 人群數量
   select count(uid) from t_user_tags where tags && array[1,3,10,200];
   
   -- 提取人群ID
   select uid from t_user_tags where tags && array[1,3,10,200];

方案三

本方案通過roaringbitmap外掛程式實現快速查詢，相關說明請參見位元影像計算（roaringbitmap）。

方案概覽

• 表結構：

  KEY：標籤ID
  VALUES: 使用者bitmap

• 表索引：

  標籤ID欄位: Btree索引

• 搜尋方法：

  彙總bitmap: 與、或、非
  and_agg(bitmaps) where KEY in (標籤s) -- 與
  or_agg(bitmaps) where KEY in (標籤s) -- 或
  except(bitmap1,bitmap2) -- 非

• 方案優勢：

  • 表格儲存體佔用空間小。

  • 索引儲存佔用空間小。僅需一個Btree索引，索引記錄數少（有多少標籤，就有多少條記錄，通常標籤數在百萬以內）。

  • 新增一個使用者群體標籤時，不需要更新大量資料，僅需新增一條人群的bitmap記錄。

  • 查詢效能極好。

• 方案缺陷：

  • bitmap最大長度為1GB，使用者數超過長度時需要使用offset，方法如下：

    offset0_bitmap, offset1gb_bitmap, ... 

  • 使用者ID需要是數字（建議連續數值），如果沒有數值型UID，需要建立映射表。

操作樣本

1. 安裝外掛程式。樣本如下：

   create extension roaringbitmap;

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       -- 時間 
   );

3. 產生UID標籤bitmap表。樣本如下：

   insert into t_tag_users 
   select tagid, uid_offset, rb_build_agg(uid::int) as userbits from 
   (
   select 
     unnest(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; 

4. 查詢包含標籤1和3的人群。樣本如下：

   -- 人群數量
   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;
   
   -- 提取人群ID
   select uid_offset,rb_and_agg(userbits) as ub 
   from t_tag_users 
   where tagid in (1,3) 
   group by uid_offset;

5. 查詢包含標籤1、3、10或200的人群。樣本如下：

   -- 人群數量
   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;
   
   -- 提取人群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;