用hll插件根据用户喜好推荐相关内容_云数据库 RDS(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