ApsaraDB RDS:ApsaraDB RDS for PostgreSQLインスタンスを使用して、リアルタイムの精密マーケティングとユーザーグループの選択を実装する

ソリューション 1

概要

• スキーマ:

  キー: ユーザーID
  タグ1:
  タグ2:
  ...
  タグN: 

• インデックス: タグフィールドごとに1つのインデックス

• 検索方法:

  AND、OR、およびNOTの組み合わせ
  ここでタグaとタグbと... 

• 短所：

  • 多数のタグおよびフィールドが存在する。 この場合、多くのストレージリソースが消費される。

  • データベース内のフィールドの数が上限を超える場合があります。 ほとんどの場合、データベースには最大1,000個のフィールドを含めることができます。 この問題を解決するには、多対多スキーマを使用して、タグごとに1つのエントリを維持します。

  • 選択条件の組み合わせは多様である。 したがって、ユーザ選択を容易にする固定インデックスは利用できない。 この場合、フィールドごとにインデックスを作成すると、ストレージリソースの消費量が大幅に増加します。

  • 新しいグループタグを追加すると、大量のデータを更新する必要があります。

  • クエリのパフォーマンスが悪いです。

手順

1. 各エントリがユーザーグループを表すユーザーグループテーブルを作成します。 サンプルコード：

   テーブルt_tag_dictを作成する (
   タグint primary key, -- ユーザーグループを識別するタグのID。
   info text, -- ユーザーグループの説明。
   crt_time timestamp -- 時間。
   ); 

2. 100,000ユーザーグループタグを作成します。 サンプルコード：

   t_tag_dict値 (1、'Male' 、now()) に挿入します。t_tag_dict値に挿入する (2, 'Female', now());
   t_tag_dict値に挿入します (3、「24歳より古い」、今 ());
   -...
   
   t_tag_dictに挿入する
   generate_series(4,100000) 、md5(random()::text) 、clock_timestamp(); 

3. ユーザーのタグを表すユーザーの各エントリを持つユーザープロファイルテーブルを作成します。 サンプルコード：

   テーブルt_user_tagを作成する (
   uid int8, -- ユーザID。
   tag int, -- ユーザーのタグ。 タグは、ユーザグループを識別する。
   mod_time timestamp, -- 時間。
   主キー (tag,uid)
   ); 

4. 1,000万人の男性ユーザーと1,000万人の女性ユーザーのそれぞれに64のランダムタグを設定します。 合計1.28億のエントリが生成されます。 サンプルコード：

   createまたはreplace関数gen_rand_tag(int,int) は、setof intを
   $$
     select case when random() > 0.5 then 1::int else 2::int end as tag
     union all
     select ceil(random()*$1):: タグとしてint from generate_series(1,$2);
   $$ language sql strict volatile;
   
   t_user_tagに挿入する
   select uid, gen_rand_tag(100000,63) as tag, clock_timestamp()
   generate_series(1,20000000) からuid on conflict (uid,tag) として何もしません。-- タグをインポートするには、次の方法を使用することもできます。シーケンスseqを作成します。vi test.sql
   t_user_tagに挿入する
   select uid, gen_rand_tag(100000,63) as tag, clock_timestamp()
   nextval('seq'::regclass) からuidとして
   競合 (tag、uid) で何もしません。pgbench − Mを調製した − n − r − P1 − f。/test.sql -c 50 -j 50 -t 400000 

5. タグ1と3に一致するユーザーを照会します。 サンプルコード：

   -- ユーザーグループの数を照会します。
   からカウント (*) を選択
   (
   t_user_tag (tag=1) からuidを選択
   交差
   t_user_tag (tag=3) からuidを選択
   ) t;
   -時間: 1494.789 ms (00:01.495)
   
   -ユーザーグループIDを取得します。
   t_user_tag (tag=1) からuidを選択
   交差
   t_user_tag (tag=3) からuidを選択します。-時間: 3246.184 ms (00:03.246) 

6. タグ1、3、10、または200に一致するユーザーを照会します。 サンプルコード：

   -- ユーザーグループの数を照会します。
   からカウント (*) を選択
   (
   t_user_tag (tag=1) からuidを選択
   ユニオン
   t_user_tag (tag=3) からuidを選択
   ユニオン
   t_user_tagからuidを選択します。tag=10
   ユニオン
   t_user_tagからuidを選択します。tag=200
   ) t;
   -時間: 3577.714 ms (00:03.578)
   
   -ユーザーグループIDを取得します。
   t_user_tag (tag=1) からuidを選択
   ユニオン
   t_user_tag (tag=3) からuidを選択
   ユニオン
   t_user_tagからuidを選択します。tag=10
   ユニオン
   t_user_tagからuidを選択します。tag=200;
   -時間: 5682.458 ms (00:05.682) 

ソリューション 2

概要

• スキーマ:

  キー: ユーザーID
  値: タグ

  の配列

• インデックス:

  タグ配列フィールド: GIN

• 検索方法:

  AND、OR、およびNOT
  ここでVALUES @> 配列 [タグ]-AND
  where VALUES && array [タグ] -- OR
  not VALUES @> 配列 [タグ]-NOT 

• 短所：

  • 複数のフィールドの代わりに配列を使用してタグを格納する場合、データベースはGINをサポートする必要があります。 ただし、すべてのデータベースがGINをサポートするわけではありません。

  • 複数のフィールドの代わりに配列を使用してタグを格納し、GINを使用すると、ストレージリソースの消費が劇的に増加します。

  • 新しいグループタグを追加すると、大量のデータを更新する必要があります。

手順

1. 各エントリがユーザーグループを表すユーザーグループテーブルを作成します。 サンプルコード：

   テーブルt_tag_dictを作成する (
   タグint primary key, -- ユーザーグループを識別するタグのID。
   info text, -- ユーザーグループの説明。
   crt_time timestamp -- 時間。
   ); 

2. 100,000ユーザーグループタグを作成します。 サンプルコード：

   t_tag_dict値 (1, 'Male', now()) に挿入します。t_tag_dict値に挿入する (2, 'Female', now());
   t_tag_dict値に挿入します (3、「24歳より古い」、今 ());
   -...
   
   t_tag_dictに挿入する
   generate_series(4,100000) 、md5(random()::text) 、clock_timestamp(); 

3. 各ユーザーの単一のエントリがユーザーのタグの配列を表すユーザープロファイルテーブルを作成します。 サンプルコード：

   テーブルt_user_tagsを作成する (
   uid int8プライマリ・キー -- ユーザID。
   タグint[], -- ユーザーのタグの配列。 タグは、ユーザグループを識別する。
   mod_time timestamp -- 時間。
   ); 

4. ランダムなタグ配列を生成する関数を作成します。 サンプルコード：

   関数gen_rand_tags(int、int) を作成または置換すると、int[] が $$として返されます。
     generate_series(1,$2) からarray_agg(ceil(random()*$1)::int) を選択します。$$ language sql strict; 

5. 100,000のタグから8つのタグをランダムに選択します。 サンプルコード：

   gen_rand_tags(100000、8) を選択します。
   
                      gen_rand_tags
   ---------------------------------------------------
    {43494,46038、74102,25308、99129,40893、33653,29690}
   (1行) 

6. 2,000万人のユーザーにタグを付けます。 各ユーザーには64個のランダムタグがあります。 ユーザーの半分は男性で、残りの半分は女性です。 サンプルコード：

   t_user_tagsに挿入
   select generate_series(1,10000000),
   array_append(gen_rand_tags(100000、63) 、1), now();
   
   t_user_tagsに挿入する
   select generate_series(10000001,20000000),
   array_append(gen_rand_tags(100000、63) 、2), now(); 

7. グループタグフィールドのGINを作成します。 サンプルコード：

   gin (タグ) を使用してt_user_tagsにインデックスidx_t_user_tags_1を作成します。

8. タグ1と3に一致するユーザーを照会します。 サンプルコード：

   -- ユーザーグループの数を照会します。
   選択count(uid) from t_user_tags where tags @> array[1,3];
   
   -ユーザーグループIDを取得します。
   t_user_tagsからuidを選択します。タグ @> 配列 [1,3]; 

9. タグ1、3、10、または200に一致するユーザーを照会します。 サンプルコード：

   -- ユーザーグループの数を照会します。
   t_user_tagsからカウント (uid) を選択します。タグ&配列 [1,3,10,200];
   
   -ユーザーグループIDを取得します。
   t_user_tagsからuidを選択し、タグ&配列 [1,3,10,200]; 

ソリューション 3

ソリューション3では、roaringbitmap拡張機能を使用して高速クエリを実装します。 詳細については、「roaringbitmap拡張機能の使用」をご参照ください。

概要

• スキーマ:

  KEY: タグID
  値: ユーザービットマップ 

• インデックス:

  タグIDフィールド: B-tree index

• 検索方法:

  集計ビットマップ: AND、OR、およびNOT
  and_agg (ビットマップ) ここでKEY in (タグ) -- AND
  or_agg (ビットマップ) ここでKEY in (タグ) -- OR
  (bitmap1、bitmap2) を除く-いいえ 

• 利点:

  • テーブルは少数のストレージリソースを消費します。

  • インデックスは少数のストレージリソースを消費します。 インデックスエントリを格納するために必要なBツリーインデックスは1つだけです。 インデックスエントリの数は、タグの数に等しい。 ほとんどの場合、タグの数は百万未満です。

  • ユーザーグループにタグを追加する場合は、グループビットマップエントリを追加するだけです。 この場合、大量のデータを更新する必要はありません。

  • クエリのパフォーマンスは優れています。

• 短所：

  • ビットマップの最大長は1 GBです。 ユーザー数が最大長を超える場合は、オフセットを使用する必要があります。

    offset0_bitmap、offset1gb_bitmap、... 

  • ユーザーIDは数字で構成する必要があり、連続する番号を推奨します。 数値ユーザーIDが存在しない場合は、マッピングテーブルを作成する必要があります。

手順

1. roaringbitmap拡張機能をインストールします。 サンプルコード：

   拡張機能roaringbitmapを作成します。

2. ユーザータグを含むビットマップテーブルを作成します。 サンプルコード：

   テーブルt_tag_usersを作成する (
     tagid int primary key, -- タグIDまたはユーザーグループID。 
     uid_offset int, -- ユーザーIDをINT8からINT4に変換します。    
     userbits roaringbitmap, -- ユーザIDのビットマップ。  
     mod_time timestamp -- 時間。 
   );

3. データを挿入して、ユーザーIDタグを含むビットマップテーブルを生成します。 サンプルコード：

   t_tag_usersに挿入
   からユーザービットとしてtagid、uid_offset、rb_build_agg(uid::int) を選択します。(
   選択 
     tagidとしてunnest (タグ) 、 
     (uid / (2 ^ 31)::int8) as uid_offset, 
     mod(uid, (2 ^ 31)::int8) as uid
   t_user_tagsから
   ) t
   tagidによるグループ、uid_offset; 

4. タグ1と3に一致するユーザーを照会します。 サンプルコード：

   -- ユーザーグループの数を照会します。
   select sum(ub) から
   (
   uid_offset、rb_and_cardinality_agg (ユーザービット) をubとして選択
   t_tag_usersから
   where tagid in (1,3)
   uid_offsetによるグループ
   ) t;
   
   -ユーザーグループIDを取得します。
   uid_offset、rb_and_agg (ユーザービット) をubとして選択
   t_tag_usersから
   where tagid in (1,3)
   グループをuid_offset; 

5. タグ1、3、10、または200に一致するユーザーを照会します。 サンプルコード：

   -- ユーザーグループの数を照会します。
   select sum(ub) から
   (
   uid_offset、rb_or_cardinality_agg (ユーザービット) をubとして選択
   t_tag_usersから
   where tagid in (1,3,10,200)
   uid_offsetによるグループ
   ) t;
   
   -ユーザーグループIDを取得します。
   uid_offset、rb_or_agg (ユーザービット) をubとして選択
   t_tag_usersから
   where tagid in (1,3,10,200)
   グループをuid_offset;