主页 / dba / 问题 / 235064
Accepted
Devin
Asked: 2019-04-18 07:59:47 +0800 CST

大型矩阵/超宽表的数据库解决方案

  • 772

假设我有这两个数据框（简化了我的问题）：

用户

+---------+
| user_id |
+---------+
| 1       |
| 2       |
| ...     |
+---------+

文章

+------------+------------+
| article_id |    date    |
+------------+------------+
| a          | 2019-01-01 |
| b          | 2018-03-03 |
| ...        |            |
+------------+------------+

还有一个用户-文章对的密集矩阵，其中每个值是我预测每个用户想要阅读每篇文章的程度（从 0 到 1）：

+-----+------+------+-----+
|     |  1   |  2   | ... |
+-----+------+------+-----+
| a   | 0.54 | 0.99 | ... |
| b   | 0    | 0.7  | ... |
| ... | ...  | ...  | ... |
+-----+------+------+-----+

我有一个网络应用程序需要做一些事情，比如返回给单个用户最推荐的 10 篇文章，或者给定日期范围内第 11 到 20 篇最推荐的文章等：

query: (user_id=123) AND (news_date IN ('2019-04-01', '2019-05-01')) LIMIT 10 OFFSET 10

+---------+-------+------+
| news_id | score | rank |
+---------+-------+------+
| g       | 0.98  | 11   |
| d       | 0.97  | 12   |
| ...     | ...   | ...  |
| q       | 0.8   | 20   |
+---------+-------+------+

挑战在于我的用户和文章数以万计，因此由于列限制，我不能将矩阵存储为 Postgres 表。

我可以将 Postgres 中的推荐分数存储在一个表中(user_id, article_id, score)，这样查询起来会很快，但是这个表会有 100M+ 行并且更新成本很高，我每天都会这样做。

我目前的解决方案是将单个数据帧(news_id, news_date, user_1_score, user_2_score, ..., user_n_score)作为 gzipped Parquet 文件存储在磁盘上，加载news_date和user_x_score列，然后过滤、排序和切片。唯一的缺点是我的网络主机有一个临时文件系统，所以这个文件需要在应用程序启动时下载。至少在 Web 请求期间获取数据的速度足够快。

我对列式数据存储了解不多，但我觉得其中一种产品可能对我的问题有好处。有人有想法吗？

database-design columnstore

3 个回答

  1. Best Answer

    "but this table would have 100M+ rows and be expensive to update, which I do daily."

    为了反驳这一点，我做了以下事情；

    CREATE TABLE test_article (
    the_series integer,
    user_id integer,
    article_id integer,
    rating numeric
);

    把握好时机，这样我们就有了适当的指标。

    \timing

    然后，我在 test_article 中插入了 1000 万条记录：

    INSERT INTO test_article
SELECT generate_series(1, 10000000), CAST(RANDOM() * 10 + 1 AS INTEGER), CAST(RANDOM() * 100 + 1 AS INTEGER), ROUND(CAST(RANDOM() AS NUMERIC), 2);

    时间：

    INSERT 0 10000000
Time: 33520.809 ms (00:33.521)

    表格内容（样本）：

    test=# SELECT * FROM test_article;

 the_series | user_id | article_id | rating 
------------+---------+------------+--------
          1 |       5 |         85 |   0.95
          2 |       6 |         41 |   0.14
          3 |       5 |         90 |   0.34
          4 |       3 |         18 |   0.32
          5 |       7 |          6 |   0.30
          6 |      10 |         32 |   0.31
          7 |       8 |         70 |   0.84

    我意识到这不是一个完美的基准。为此，必须在 (user_id, article_id) 上有一个UNIQUE索引 - 但是为了使其尽可能真实，我将把它放在这些字段上。我相信这不是一个巨大的扭曲。编辑-见下文-此问题已解决！

    所以，我创建了索引：

    CREATE INDEX user_article_ix ON test_article (user_id, article_id);

    时间：

    CREATE INDEX
Time: 20556.118 ms (00:20.556)

    然后，我插入了 10 万条记录：

    INSERT INTO test_article
SELECT generate_series(1, 100000), CAST(RANDOM() * 10 + 1 AS INTEGER), CAST(RANDOM() * 100 + 1 AS INTEGER), ROUND(CAST(RANDOM() AS NUMERIC), 2);

    时间;

    INSERT 0 100000
Time: 996.115 ms

    不到1秒！

    因此，将大量记录插入链接表（也称为关联实体- 也称为连接表、关联表......）似乎没有问题

    因此，我非常建议您将此作为解决方案！

    user_id 和 article_id 的唯一组合。

    经过一番哀嚎和咬牙切齿后，我终于弄清楚了如何使用 generate_series 使 user_id 和 article_id 的组合是唯一的（因为任何给定的用户只能对一篇文章有​​一个当前评分）。

    我不会展示每一步，只展示有助于独特性的那些 - 基于以上内容：

    "secret sauce"是这样的：

    INSERT INTO test_article (user_id, article_id) 
SELECT * FROM
(
  WITH x AS
  (
    SELECT generate_series(1, 500) AS bill
  ),
  y AS
  (
    SELECT generate_series(1, 20000) AS fred
  )
  SELECT * FROM x
  CROSS JOIN y
) AS z
ORDER BY bill, fred;

    它涉及到CROSS JOIN一个 500 个表（即用户）和一个 20,000 个表（即文章） - 你们中间的精明会意识到它们的乘积是 10,000,000（见上文）。

    现在，user_id 和 article_id 的组合保证是唯一的，因为使用 (sample)，bill = 2 和 fred = 3，你得到

    bill | fred 
------+------
    1 |    1
    1 |    2
    1 |    3
    2 |    1
    2 |    2
    2 |    3

    每条记录都是独一无二的——等等！

    无论如何，我使用这个构造来测试是否有欺骗性：

    SELECT (user_id, article_id)::text, count(*)
FROM test_article
WHERE 1 = (SELECT 1)
GROUP BY user_id, article_id
HAVING count(*) > 1

    时间：4s。

    然后，您可以制作 (user_id, article_id) PRIMARY KEY（未显示 - 只花了大约 30 秒）。

    然后，要添加 100,000 条记录，您不理会用户（仍然是 1 - 500），但您将文章的 generate_series() 修改为 20,001 到 20200（即 200 x 50 = 100,000）并执行与INSERT上述相同的操作。速度极快 - 即使使用PRIMARY KEY(< 1s)。

    获取特定用户的所有文章是 v. 快速 (~ 25 ms)

    test=# EXPLAIN(ANALYZE, BUFFERS) SELECT * FROM test_article WHERE user_id = 77;
                                                                  QUERY PLAN                                                           
 Index Scan using test_article_pkey on test_article  (cost=0.44..65174.74 rows=44503 width=44) (actual time=0.074..21.837 rows=20200 lo
ops=1)
   Index Cond: (user_id = 77)
   Buffers: shared hit=40371 read=361 dirtied=271
 Planning Time: 0.131 ms
 Execution Time: 23.475 ms
(5 rows)

Time: 24.187 ms

    和 pièce de résistance，在PK(< 1 ms) 上的点搜索：

    test=# EXPLAIN(ANALYZE, BUFFERS) SELECT * FROM test_article WHERE user_id = 77 AND article_id = 4567;
                                                            QUERY PLAN                                                            

 Index Scan using test_article_pkey on test_article  (cost=0.44..10.22 rows=2 width=44) (actual time=0.038..0.040 rows=1 loops=1)
   Index Cond: ((user_id = 77) AND (article_id = 4567))
   Buffers: shared hit=4
 Planning Time: 0.219 ms
 Execution Time: 0.078 ms
(5 rows)

Time: 0.947 ms
    • 2

  2. 使用关系数据库时，不要用矩阵来思考，而是用关系术语来思考。您所描述的是用户和文章之间典型的多对多关系，通常使用关系（链接）表来实现，正如您所提到的。

    列组织的数据存储不是答案，主要是因为它只是相同旧关系模型的不同物理实现，因此受到相同的表宽度和更新性能限制。

    如果您关于“100+M 行更新成本高”的陈述是基于实际性能测试的，那么您应该就更新性能提出一个具体问题，我相信我们将能够提供帮助。如果这只是你的假设，我建议你试试看它是否成立。

    • 1

  3. 您可能会考虑使用 SQL Server。带COLUMN_SET列的表最多可以有 30,000 个稀疏列，性能确实很棒。SQL Server 2017+ 也与 Linux 兼容。

    我在这里写了一篇关于它的博客文章。

    • 0

相关问题