主页 / dba / 问题 / 335813
Accepted
1000k
Asked: 2024-02-14 19:34:01 +0800 CST

评分表优化构想

  • 772

我有一个简单的问题，但找不到直接的答案/解释。抱歉，如果重复

我想为一个小型问答游戏建立一个分数表，用户可以在其中提出问题，他们可以是对的，也可以是错的。所以，我想我有 2 个选择，要么是 1 行 = 1 个用户答案的​​表，如下所示：

用户身份 正确答案 错误的答案
1 0 1
1 0 1
1 1 0
... ... ...

或者一个 1 行 = 1 个用户全局分数的表：

用户身份 正确答案 错误答案
1 42 21
2 100 0
3 12 13
... ... ...

我对数据库/sql优化的经验几乎为0，所以我不知道哪一个是最有效的。

这是我的思考过程：

第一个选项：更好（？）/更快（？）添加/更新分数，因为我不关心当前表状态，我只需要插入一行但是，要获得用户的全局分数，我需要一个重(?) 查询类似

SELECT SUM(正确), SUM(错误) WHERE userId = x;

由于我想在每个页面/请求上显示用户的全局分数，因此我觉得这不是明智的选择。另外，由于 1 行 = 1 个用户答案，因此表格可能会变得非常大。

第一个选项：较慢（？）添加分数，因为我必须更新现有行。意思是对每个答案选择然后更新。不是特别是我的情况，但这个选项不太灵活，因为与第一个选项不同，我无法存储正确/错误回答的问题。但是，这样我就可以获得用户的总分，而无需大量查询。如果 UPDATE 查询可以返回结果行会更好，这样我就不必先 UPDATE 然后 SELECT 来显示。

最后，如果您愿意花时间回答我，您能否简要解释一下其中一个在技术上更好，以及我如何测试它（是否有某种工具/程序）

谢谢

query-performance

2 个回答

  1. Best Answer

    FWIW，我知道您在询问逻辑设计，因此这更多是基于理论的，但是了解您计划使用的数据库系统实际上与答案相关，基于该数据库中可用的功能以促进提高性能。通常，任何有关性能的问题都必须有具体细节的支持。

    第一个选项：

    更好（？）/更快（？）添加/更新分数，因为我不关心当前表状态，我只需插入一行

    从锁定的角度来看，这更好。插入新行不会锁定表中其他行的读取。尽管取决于数据库中使用的事务隔离级别（取决于数据库系统），但如果它使用乐观并发，这将成为一个有争议的问题。乐观并发是一种允许写入者不阻止读取者的方法，反之亦然，通常通过维护行的先前版本并在主动更改时使其可用来实现。

    另外，由于 1 行 = 1 个用户答案，因此表格可能会变得非常大。

    大是一个相对术语。不过，静态数据大小无关紧要。大多数现代数据库系统都可以很好地处理包含数万亿行和 PB 数据的表。这取决于您的用例、如何为这些用例设计查询以及如何相应地构建数据库。

    第二个选项：

    添加分数较慢（？），因为我必须更新现有行。含义选择然后更新

    您只需UPDATE使用WHERE子句进行过滤，因此它仍然是单个查询。有了正确的索引，这应该只需要查找特定用户的分数行，并且应该是一个非常快速的更改。但是您会遇到潜在的阻塞问题，除非您再次使用乐观并发，否则这种担忧就变得毫无意义。

    不是特别是我的情况，但这个选项不太灵活，因为与第一个选项不同，我无法存储正确/错误回答的问题。

    这将是我将其设计为选项一的第一个选择，因为人们似乎很自然地想知道哪些问题被正确回答或没有被回答。这在测验数据库中通常是典型的。

    但是，这样我就可以得到用户的总分，而无需大量查询。

    使用第一个选项，您也可以。根据您使用的数据库系统，列存储索引、物化视图甚至常规行存储索引可能足以有效地实现您的目标。

    如果 UPDATE 查询可以返回结果行会更好，这样我就不必先 UPDATE 然后 SELECT 来显示。

    大多数数据库系统都可以使用DML 语句的OUTPUTor子句来完成此操作。RETURNING

    最后，如果您愿意花时间回答我，您能否简要解释一下其中一个在技术上更好，以及我如何测试它（是否有某种工具/程序）

    这是您问题的实际部分。没有人可以说，找出适合您的用例的唯一方法就是实际向他们扔数据并测试它们。从业务逻辑的角度来看，选项一将是我的首选。我不会担心我还没有遇到的性能问题，并且如前所述，有很多工具可以帮助提高性能。

    • 1

  2. 仅存储总数并没有真正的可比性，因为它几乎无法提供有关实际发生情况的信息。您很可能会比较维护表和表。AnswerUser

    查询或视图要好得多，因为您不会面临更新异常的风险，并且不会使用触发器减慢插入速度。但这是以查询整个集合（至少对于单个用户）来获取分数为代价的。

    如果您使用的是 SQL Server，那么正确的答案是索引视图。服务器自动同步维护视图中的数据（与其他 DBMS 中的物化视图相反）。

    GROUP BY只要您遵循特定限制，主要使用COUNT_BIGand SUMonly 和 only s，这就允许 a INNER JOIN。

    它还必须是模式绑定的，以防止对基表进行重大更改。

    CREATE VIEW dbo.TotalUserScores
WITH SCHEMABINDING
AS

SELECT
  a.UserId,
  COUNT_BIG(*) AS AnswerCount,
  SUM(a.Right) AS RightCount,
  SUM(a.Wrong) AS WrongCount
FROM dbo.Answer a
GROUP BY
  a.userId;

    然后添加索引，该索引必须是唯一的和聚集的（您也可以添加其他索引）：

    CREATE UNIQUE CLUSTERED INDEX CX ON dbo.TotalUserScores (UserId);

    WITH (NOEXPAND)出于各种性能原因，请确保在查询时使用提示。

    • 1

相关问题