如何在需要使用集合操作的SQL Server数据库中设计更高可扩展性？

Question

想象一个电影应用程序，根据以下非常简单的算法向用户推荐下一部电影：

• 电影应该是用户新看到的
• 用户没有将电影标记为“不感兴趣”

这是 SQL Server 数据库的简单设计：

Movies:
    Id bigint
    Name nvarchar(100)

SeenMovies:
    Id bigint
    UserId bigint
    MovieId bigint

NotInterestedFlags:
    Id bigint
    UserId bigint
    MovieId bigint

为了获取下一部电影，我们运行以下查询：

select top 1 *
from Movies 
where Id not in 
(
    select MovieId 
    from SeenMovies 
    where UserId = 89283
)
and Id not in 
(
    select MovieId 
    from NotInterestedFlags
    where UserId = 89283
)

随着应用程序的使用和数据量的增加，这个设计变得越来越慢。

因此，对于一个拥有100K部电影和超过1000万客户的虚构数据库，如何改变这个设计以使其水平扩展呢？

- Ali Fallah

---

您的UserID字段上是否有索引（聚集或覆盖）？ - Dan Guzman

@DanGuzman，AMAIK 任何索引都不是水平扩展的解决方案。如果我错了，请纠正我。但我认为索引对 OP 没有帮助。 - Saeed Neamati

@SaeedNeamati，同意索引本身并不是水平扩展的解决方案。不过它可能是性能问题根本原因的解决方案。 - Dan Guzman

为什么在SeenMovies和NotInterestedMovies中需要Id列？此外，我建议使用一个单一的User/Movie表，其中包含一个tinyint列，用于告诉您用户和电影组合出现在该表中的原因 - 即1-已观看，2-不感兴趣（如果您将来想要包括更多原因，tinyInt可以容纳0-255之间的值，因此结构中不需要进行任何更改）。 - Zohar Peled

对我来说，这似乎是更好的设计。当前的设计使用多个表格，它们（至少在这个角度上）具有相同的含义。这就像为喜剧和戏剧创建不同的表格一样。如果一个月后系统引入了不选择电影的新原因怎么办？这种扩展性也应该被考虑到。 - Zohar Peled

Answer 1

以下是我建议的类似代码内容。

我假设SeenMovies和NotInterestedFlags已经按照UserId进行聚集或至少索引化，而Movies则按MovieId进行聚集。如果没有，请先添加此类索引。

我确定在您所讨论的这种数据量下，每个单独查询不应该有性能问题，因为一旦我们将查询限制为特定用户，对于该用户来说，SeenMovies和NotInterestedFlags最多只有几千行。

SELECT TOP 1
    Movies.*

FROM
    Users

CROSS JOIN
    Movies

WHERE 
    NOT EXISTS
    (
        SELECT NULL
        FROM SeenMovies
        WHERE 
            SeenMovies.UserId = Users.Id
            AND
            SeenMovies.MovieId = Movies.Id 
    )
    AND 
    NOT EXISTS
    (
        SELECT NULL
        FROM NotInterestedFlags
        WHERE 
            NotInterestedFlags.UserId = Users.Id
            AND
            NotInterestedFlags.MovieId = Movies.Id 
    )
    AND
    Users.Id = 89283

如果即使使用适当的索引仍然表现不佳，我只能想象首先将SeenMovies和NotInterestedFlags中的MovieId条目进行UNION，然后再将其与Movies进行EXCEPT操作，可能会提供更好的性能。

另一方面，如果系统的整体性能在承载许多用户的负载下降低，您可能需要查看为每个用户准备未看过和未列入黑名单电影列表，从中查询TOP 1。

然后，当用户观看电影或将其列入黑名单（或添加新电影）时，这个新表将与单独的SeenMovies和NotInterestedFlags表同时修改。

同样，如果这不能足够帮助性能，那么您将不得不考虑实施每天批处理作业，预先准备每个用户的10部未看过和未列入黑名单的电影列表，然后查询并逐个向用户提供。

我认为，坦白地说，如果您有1000万用户的前景，您可能负担得起专家编写代码或评估现有系统。

- Steve

Answer 2

为每个用户创建一个“收藏”的电影缓存。使用索引视图可能效果很好。重点不是每次用户想要查看列表时都运行完整查询，而是偶尔更新收藏夹。这些单独的列表以及用户标志表可以通过某些用户属性水平扩展。用户位置可能是未来云迁移的一个不错选择。

- Y.B.

---

左反半连接（Left Anti Semi Join）通常比左连接（left join）更快，即使用NOT EXISTS，请参见http://sqlity.net/en/1360/a-join-a-day-the-left-anti-semi-join/。 - Steve Ford

@Y.B. 1000万行数据并不算很多。如果仅通过添加正确的索引或修复查询就可以实现数量级和稳定性的改进，那么调查分片是没有意义的。 - Panagiotis Kanavos

@PanagiotisKanavos 好的，问题是如何扩展而不是如何建立索引。 当几千个客户同时查找他们的下一个最喜欢的电影时，没有索引是无用的。 - Y.B.

@Y.B. 如果你修复性能问题，就不需要进行扩展。如果你只是在问题上投入资金而不解决明显的问题，那么你将无法扩展。你只会让供应商或云服务提供商感到高兴。最好的情况是，你可能会看到初始改进，但性能仍将以相同的速度下降。最糟糕的情况是，如果你使用错误类型的分区或不复制电影表，性能将远远不如预期。 - Panagiotis Kanavos

@PanagiotisKanavos，总是最简单的查询让系统崩溃。想象一下，10,000个用户每秒运行他们的10,000个简单查询。就像DoS攻击一样，只不过是合法的。 - Y.B.