用hll外掛程式根據使用者喜好推薦相關內容 - ApsaraDB RDS

本文介紹如何使用hll外掛程式實現根據使用者喜好推薦相關內容。

前提條件

背景資訊

推薦系統在互連網應用中主要用於提升使用者粘性、提高轉化率，常見情境如下：

電商網站根據使用者購買習慣，推薦商品。
音樂網站根據使用者收聽喜好，推薦音樂。
新聞網站根據使用者瀏覽習慣，推薦新聞。
應用網站根據使用者下載和使用應用習慣，推薦應用。

本文以音樂網站為例介紹如何設計推薦系統資料庫，以及不同設計方法的差異。

設計背景

使用者（uid）完整聽完的歌曲（vid），該歌曲有對應的標籤（tags），同時一個歌曲可以有多個標籤，由此形成映射關係：
```
uid ->> tags ->> musics    
```
根據使用者每個tag下的歌曲數排行，得到tag熱度：
```
tag(count distinct music)    
...   
```

統計前5個標籤（tags）及權重：

tag1:40%    
tag2:20%    
tag3:15%    
tag4:15%    
tag5:10%

從標籤（tags）的歌曲庫中，排除使用者聽過的，然後根據這些歌曲的推薦權重（例如播放次數倒排），按比例推薦新的歌曲。

基於hll近似計算的設計

使用hll來儲存使用者（uid）聽完的歌曲ID（vid），相比普通設計有如下優勢：

資料量小，使用近似hll hash聚集代替真實值。
查詢效率高，支援索引，不需要計算，毫秒響應。
支援hash union、add等操作，適合滑窗計算，滿足更多業務需求。

說明

hll外掛程式使用方法請參見基數統計（hll）。

串連RDS PostgreSQL執行個體，建立測試表。每個標籤儲存一個hll，hll中儲存該標籤內使用者完整聽完的歌曲的ID hash值。

CREATE TABLE t_like (
    uid           INT,
    tagid         INT,            -- 標籤
    w1 hll, w1_mod_time TIMESTAMP, -- 周一聽完的歌曲對應的vid構成的hash。   
    w2 hll, w2_mod_time TIMESTAMP, -- 周二聽完的歌曲對應的vid構成的hash。
    w3 hll, w3_mod_time TIMESTAMP, -- 周三聽完的歌曲對應的vid構成的hash。    
    w4 hll, w4_mod_time TIMESTAMP, -- 周四聽完的歌曲對應的vid構成的hash。    
    w5 hll, w5_mod_time TIMESTAMP, -- 周五聽完的歌曲對應的vid構成的hash。    
    w6 hll, w6_mod_time TIMESTAMP, -- 周六聽完的歌曲對應的vid構成的hash。    
    w7 hll, w7_mod_time TIMESTAMP, -- 周日聽完的歌曲對應的vid構成的hash。
    whole hll,                     -- 所有
    PRIMARY KEY (uid, tagid)
);

說明

w1~w7是根據業務設定的，如果業務只關心1天的資料，就只需要設定一個欄位。

當使用者聽完一首歌時，將該資訊寫入當前日期對應的欄位。如果該欄位已存在值且最後修改時間不是今天，則覆蓋該值；否則，追加hash值。

使用的是INSERT INTO ON CONFLICT文法，樣本如下：

-- 插入使用者觀看記錄的hash值
INSERT INTO t_like (
    uid,
    tagid,
    w5,
    w5_mod_time,
    whole
)
VALUES (
    1,                                   -- uid
    200,                                 -- 標籤 ID
    hll_hash_integer(12346) || hll_empty(),  -- 觀看過的 vid（可拼接多個）
    NOW(),
    hll_hash_integer(12346) || hll_empty()   -- 觀看過的 vid
)
ON CONFLICT (uid, tagid) DO UPDATE
SET
    w5 =
        CASE
            WHEN DATE(t_like.w5_mod_time) <> CURRENT_DATE
            THEN EXCLUDED.w5
            ELSE hll_union(COALESCE(t_like.w5, hll_empty()),
                           EXCLUDED.w5)
        END,
    w5_mod_time = EXCLUDED.w5_mod_time,
    whole       = hll_union(COALESCE(t_like.whole, hll_empty()),
                            EXCLUDED.whole)
WHERE
    hll_union(COALESCE(t_like.w5,   hll_empty()), EXCLUDED.w5)    <> COALESCE(t_like.w5,   hll_empty())
    OR
    hll_union(COALESCE(t_like.whole,hll_empty()), EXCLUDED.whole) <> COALESCE(t_like.whole,hll_empty());

說明

實際業務中也可以批量合并更新，針對單個使用者的單個標籤彙總更新，使用hll union降低更新率。

查詢使用者ID為1（uid=1）在最近兩天內的前10個標籤。樣本如下：

SELECT
    tagid,
    hll_cardinality(
        hll_union(
            COALESCE(w4, hll_empty()),
            COALESCE(w5, hll_empty())
        )
    ) AS vids
FROM t_like
WHERE uid = 1
ORDER BY 2 DESC
LIMIT 10;

返回結果：

 tagid | vids     
-------+------    
   200 |    2    
(1 row)

執行以下命令，建立索引。

CREATE INDEX idx_t_like_1
ON t_like (
    uid,
    hll_cardinality(
        hll_union(
            COALESCE(w4, hll_empty()),
            COALESCE(w5, hll_empty())
        )
    )
);

執行以下命令，查看執行計畫。

EXPLAIN
SELECT
    tagid,
    hll_cardinality(
        hll_union(
            COALESCE(w4, hll_empty()),
            COALESCE(w5, hll_empty())
        )
    ) AS vids
FROM t_like
WHERE uid = 1
ORDER BY 2 DESC
LIMIT 10;
                                        QUERY PLAN                                             
-------------------------------------------------------------------------------------------    
 Limit  (cost=0.11..0.15 rows=1 width=12)    
   ->  Index Scan Backward using idx_t_like_1 on t_like  (cost=0.11..0.15 rows=1 width=12)    
         Index Cond: (uid = 1)    
(3 rows)

返回結果：

                                        QUERY PLAN                                             
-------------------------------------------------------------------------------------------    
 Limit  (cost=0.11..0.15 rows=1 width=12)    
   ->  Index Scan Backward using idx_t_like_1 on t_like  (cost=0.11..0.15 rows=1 width=12)    
         Index Cond: (uid = 1)    
(3 rows)

使用pgbench向測試表中插入資料，本文以與RDS PostgreSQL處於同一VPC的ECS執行個體為例。

說明

pgbench是一個在PostgreSQL上運行基準測試的簡單工具，請確保目標ECS已安裝PostgreSQL用戶端。該命令的更多用法，請參見PostgreSQL官方文檔。

在ECS中使用vi test.sql命令，建立測試SQL檔案test.sql，並插入以下內容。

\set uid    random(1, 50000)
\set tagid  random(1, 5000)
\set vid    random(1, 10000000)

INSERT INTO t_like (
    uid,
    tagid,
    w5,
    w5_mod_time,
    whole
)
VALUES (
    :uid,
    :tagid,
    hll_hash_integer(:vid) || hll_empty(),
    NOW(),
    hll_hash_integer(:vid) || hll_empty()
)
ON CONFLICT (uid, tagid) DO UPDATE
SET
    w5 =
        CASE
            WHEN DATE(t_like.w5_mod_time) <> CURRENT_DATE
            THEN EXCLUDED.w5
            ELSE hll_union(
                     COALESCE(t_like.w5, hll_empty()),
                     EXCLUDED.w5
                 )
        END,
    w5_mod_time = EXCLUDED.w5_mod_time,
    whole       = hll_union(
                     COALESCE(t_like.whole, hll_empty()),
                     EXCLUDED.whole
                 )
WHERE
      hll_union(COALESCE(t_like.w5,   hll_empty()), EXCLUDED.w5)   
    <> COALESCE(t_like.w5,   hll_empty())
   OR
      hll_union(COALESCE(t_like.whole, hll_empty()), EXCLUDED.whole)
    <> COALESCE(t_like.whole, hll_empty());

執行以下命令，插入測試資料。

pgbench \
  -M prepared \
  -n \
  -r \
  -P 1 \
  -c 32 \
  -j 32 \
  -T 120 \
  -f ./test.sql \
  -h pgm-****.pg.rds.aliyuncs.com \   # RDS PostgreSQL串連地址
  -p 5432 \                           # 連接埠
  -U testdbuser \                     # 資料庫帳號
  testdb                              # 目標資料庫名稱

返回結果：

transaction type: ./test.sql    
scaling factor: 1    
query mode: prepared    
number of clients: 32    
number of threads: 32    
duration: 120 s    
number of transactions actually processed: 24636321    
latency average = 0.156 ms    
latency stddev = 0.339 ms    
tps = 205301.110313 (including connections establishing)    
tps = 205354.851711 (excluding connections establishing)    
statement latencies in milliseconds:    
         0.001  \set uid random(1,5000000)    
         0.001  \set tagid random(1,5000)    
         0.000  \set vid random(1,10000000)    
         0.154  insert into t_like (

串連RDS PostgreSQL執行個體，執行以下命令，以查詢特定UID的標籤熱度排序。

SELECT
    tagid,
    hll_cardinality(
        hll_union(
            COALESCE(w4, hll_empty()),
            COALESCE(w5, hll_empty())
        )
    ) AS vids
FROM t_like
WHERE uid = 1
ORDER BY 2 DESC
LIMIT 10;

返回結果：

 tagid | vids     
-------+------    
   200 |    2    
  1413 |    1    
  1996 |    1    
  2642 |    1    
  3664 |    1    
  4340 |    1    
(6 rows)    
    
Time: 0.688 ms

說明

回應時間為0.688毫秒。

其他需求樣本

過濾使用者已經聽過的歌曲。例如，判斷一個歌曲ID（vid）是否在這個hash裡。

不精確操作

不包含：

SELECT
    whole || hll_hash_integer(1) = whole
FROM t_like
WHERE uid = 1
  AND tagid = 200;  -- 返回false表示不包含vid:1。    
 
 ?column?     
----------    
 f    
(1 row)

包含：

SELECT
    whole || hll_hash_integer(12345) = whole
FROM t_like
WHERE uid = 1
  AND tagid = 200;    -- 返回true表示包含vid:12345。
      
 ?column?     
----------    
 t    
(1 row)

精確操作

建立已聽歌曲測試表，樣本如下：

CREATE TABLE t_like_lossless (
    uid INT,
    vid INT,
    PRIMARY KEY (uid, vid)
);

普通設計

該設計適用於所有資料庫，但其缺點在於，當資料量龐大時，可能需要使用彙總查詢，從而導致效率較低。

串連RDS PostgreSQL執行個體，建立測試表。建表語句樣本如下所示：

CREATE TABLE t_like (
    uid       INT,          -- 使用者ID
    tagid     INT,          -- 歌曲標籤ID
    vid       INT,          -- 歌曲ID
    mod_time  TIMESTAMP,    -- 最後一次更新時間，僅與上次時間超過 1 天時更新
    PRIMARY KEY (uid, tagid, vid)
);

使用pgbench向測試表中插入資料，本文以與RDS PostgreSQL處於同一VPC的ECS執行個體為例。

說明

pgbench是一個在PostgreSQL上運行基準測試的簡單工具，請確保目標ECS已安裝PostgreSQL用戶端。該命令的更多用法，請參見PostgreSQL官方文檔。

在ECS中使用vi test.sql命令，建立測試SQL檔案test.sql，並插入以下內容。

\set uid    random(1, 50000)
\set tagid  random(1, 5000)
\set vid    random(1, 10000000)

INSERT INTO t_like
VALUES (:uid, :tagid, :vid, NOW())
ON CONFLICT (uid, tagid, vid) DO UPDATE
    SET mod_time = EXCLUDED.mod_time
WHERE
    EXCLUDED.mod_time - t_like.mod_time > INTERVAL '1 day';

執行以下命令，插入測試資料。

pgbench \
  -M prepared \
  -n \
  -r \
  -P 1 \
  -c 32 \
  -j 32 \
  -T 120 \
  -f ./test.sql \
  -h pgm-****.pg.rds.aliyuncs.com \   # RDS PostgreSQL串連地址
  -p 5432 \                           # 連接埠
  -U testdbuser \                     # 資料庫帳號
  testdb                              # 目標資料庫名稱

串連RDS PostgreSQL執行個體，執行以下命令以統計最近一天的前十個標籤（top 10 tags）。

SELECT
    tagid,
    COUNT(*)
FROM t_like
WHERE uid = 1
  AND NOW() - mod_time < INTERVAL '1 day'
GROUP BY tagid
ORDER BY COUNT(*) DESC
LIMIT 10;

返回樣本：

 tagid | count   
-------+-------  
  2519 |     4  
  3049 |     4  
  3648 |     4  
  1777 |     3  
  1352 |     3  
  1491 |     3  
  1064 |     3  
   572 |     3  
   692 |     3  
   301 |     3  
(10 rows)  
  
Time: 3.947 ms