通知系统的有效实现——我应该使用还是避免join ?

Question

The Tables

让我们假设我们有一个条目表:

CREATE TABLE articles
(
    id      integer PRIMARY KEY,
    last_update timestamp NOT NULL,
    ...
);

用户可以收藏文章:

CREATE TABLE bookmarks
(
    user        integer NOT NULL REFERENCES users(id),
    article     integer NOT NULL REFERENCES articles(id),
    PRIMARY KEY(user, article),
    last_seen   timestamp NOT NULL
);

要实现的功能

我现在要做的是通知用户在用户最后一次看到它们之后已更新的文章。整个系统的访问是通过一个web界面。每当一个页面被请求时，系统应该检查是否应该通知用户更新的文章(类似于SO页面顶部的通知栏)。

如果上面两个表都包含数千万行，那么这种特性的最佳和最有效的实现是什么?

我的解决方案#1

可以这样做一个简单的连接:

SELECT ... FROM articles, bookmarks WHERE bookmarks.user = 1234
AND bookmarks.article = articles.article AND last_seen < last_update;

然而，我担心如果用户有很多书签文章(这种情况可能比你想象的要频繁)，特别是如果数据库(在我的例子中是PostgreSQL)必须遍历articles主键上的索引，那么做这个JOIN可能会很昂贵。此外，last_seen < last_update谓词只能在访问磁盘上的行之后进行检查。

我的解决方案#2

另一种方法更困难，但在我的情况下可能更好。它涉及到通过一个通知列展开书签表:

CREATE TABLE bookmarks
(
    user        integer NOT NULL REFERENCES users(id),
    article     integer NOT NULL REFERENCES articles(id),
    PRIMARY KEY(user, article),
    last_seen   timestamp NOT NULL,
    notify      boolean NOT NULL DEFAULT false
);
CREATE INDEX bookmark_article_idx ON bookmarks (article);

当一篇文章被更新时，更新操作应该触发将notify设置为true，对于每个将这篇文章加入书签的用户。这样做的一大缺点是，如果一篇文章被添加了很多书签，那么为很多行设置notify为true可能会很昂贵。这样做的好处是，检查通知就像这样简单:

SELECT article FROM bookmarks WHERE user = 1234 AND notify = true;

最终想法

我认为，如果页面浏览量(以及系统检查通知的次数)超过文章的更新次数，第二种方法可能会更有效。然而，情况可能并非总是如此。可能有很多收藏了很多文章的用户每个月只登录一次，花几分钟，而其他人几乎每分钟都要查看更新。

还有第三种方法，它涉及一个通知表，一旦文章更新，系统就会在其中为每个用户插入通知。然而，我认为这是方法#2的低效变体，因为它涉及保存通知。

当两个表都包含数百万行时，什么方法是最有效的?你有其他更好的方法吗?

Answer 1

我当然会选择解决方案一，确保articles在(article,last_update)上有一个索引

Answer 2

归一化理论直接带你到解决方案#1。而不是问哪个设计更快，您可能想问，我如何让我的服务器有效地执行这个查询给定我的bog标准BCNF表。: -)

如果您的服务器不能足够快地执行您的查询(无论的值足够在您的情况下)，您需要一个更快的服务器。为什么?因为性能只会随着用户和行的添加而降低。规范化是为了最小化更新和更新异常而发明的。充分利用它，否则将花费数小时的时间和系统中难以检测到的错误。

Answer 3

我看到了第三个解决方案，使事情更有趣。它是两种溶液的混合物。我会假设系统在白天或晚上很少使用，并且每天/每晚运行一次以标记所有新书签。

仅仅这一点就会使"新文章为您更新!"的信息延迟一天，这不是您想要的。但是我会存储一个额外的列"updated today"(enum"Yes"，"No"或tinyint)，在文章更新时设置为"Yes"，在夜间更新运行时重置为"No"。

然后显示带有"is changed"标记的所有书签的"has changes"(来自nightly cron)，并添加select from version 1的信息，但仅限于今天更改的文章。

可能大多数文章不是每天更新的，所以你应该赢。

当然，我会同意测量答案，但是您需要很多假设来创建一个好的基准。