mysql - mysql获取未读文章的数据库设计(100M以上行表)

Question

此信息非常浓缩。

有 2 个表。

文章

-----------------------------------
|id | weight | text               |
-----------------------------------
|1  | 10     | blah               |
|2  | 100    | blah               |
|3  | 50     | blah               |
|4  | 1000   | blah               |
-----------------------------------

阅读

-----------------------------------
| user_id | article_id            |
-----------------------------------
| 1       | 4                     |
| 1       | 2                     |
| 1       | 3                     |
| 2       | 3                     |
| 2       | 4                     |
-----------------------------------

我想使用以下查询获取未读文章(非常简洁)

SELECT 
    a.* 
FROM 
    article a LEFT OUTER JOIN read r ON r.article_id = a.id and r.user_id = 1
WHERE 
    r.id IS NULL
ORDER BY
    a.weight DESC
LIMIT 10

important information

1. the number of read table rows keeps under 1000 per user. (remove old data)
2. weight column in article table is changed frequently. (It means order not fixed)

问题是..(当用户数:超过 1M 时)

1. 利用read表获取未读文章的方法(不是in，outer join不重要)
   • 读表行数超过1G

目前运行良好(当前读取表行数:100M)。但我必须准备下一步，因为用户数量正在迅速增加。

在这种情况下，大型服务的最佳方式是什么？

(分片？分区表？还是重新设计架构？)

提前致谢

Answer 1

• 向 article 添加一列。这将是一个标志，说明文章是否已读/未读。 (不要将其设为用户计数或时间戳；这会减慢后续步骤。)
• 每当用户阅读文章时，检查标记并根据需要进行更改。
• 有 `INDEX(flag, weight, id) -- 这将使您的查询几乎立即运行。这在那个百万行的表上应该没问题。

一个问题:由于您正在清除(1000 篇之后)，一些“已读”文章可能会变成“未读”。为了解决这个问题，分批清除，并收集被清除的文章的不同列表。然后执行重新计算标志的繁琐任务，但只是针对那些文章。 INDEX(article_id) 会有帮助；使用 EXISTS (SELECT * FROM read WHERE article_id = $aid)。 (这可能会变成批量操作，而不是一次一个辅助。)

另一个问题:十亿行表上的辅助键代价高昂——它们可能会导致大量 I/O。在尝试解决此问题之前，请为这两个表提供 SHOW CREATE TABLE，以及任何其他常见的 SELECTs。选择正确的索引和数据类型对于十亿行表的性能非常很重要。