MySQL - 如何优化查询以计算投票数

Question

我想请教一下如何实现以下目标：

我希望将可以由用户投票的产品存储在我的MySQL数据库中（每个投票的价值为+1）。我还想能够查看每个用户总共投票的次数。

在我看来，以下表结构是理想的：

  table: product          table: user            table: user_product_vote       
+----+-------------+    +----+-------------+    +----+------------+---------+ 
| id |   product   |    | id | username    |    | id | product_id | user_id |
+----+-------------+    +----+-------------+    +----+------------+---------+
| 1  | bananas     |    | 1  | matthew     |    | 1  | 1          | 2       |
| 2  | apples      |    | 2  | mark        |    | 2  | 2          | 2       |
| .. | ..          |    | .. | ..          |    | .. | ..         | ..      |

我可以针对每个产品或用户在user_product_vote表中进行计数。

例如，当我想查找香蕉并在网页上显示投票数时，我可以执行以下查询：

SELECT p.product AS product, COUNT( v.id ) as votes
FROM product p
LEFT JOIN user_product_vote v ON p.id = v.product_id
WHERE p.id =1

如果我的网站获得了巨大的成功（我们都可以梦想），并且有成千上万的用户在数以千计的产品上进行投票，我担心每个页面视图都执行这样的COUNT操作会在服务器资源方面非常低效。

更简单的方法是在产品表中设置一个“投票”列，并在添加投票时递增该列。

  table: product               
+----+-------------+-------+
| id |   product   | votes |
+----+-------------+-------+
| 1  | bananas     | 2     |
| 2  | apples      | 5     |
| .. | ..          | ..    |

虽然这样更加节省资源，但我会失去数据（例如，我不能再防止一个人投票两次，因为没有他们投票活动的记录）。

我的问题是：
i）我是否过于担心服务器资源，应该坚持使用三个表的选项？（即，我是否需要更信任数据库处理大型查询的能力）
ii）是否有更有效的方法来实现结果而不丢失信息

- So Over It

---

另一个你遇到的问题可能是你从未拥有过一个拥有大量流量的网站，所以你对php/mysql的能力不确定。我向你保证，mysql可以处理成千上万次的查询，而不会降低太多性能。 - RobertPitt

Answer 1

当你开始构建一个应用程序时，你应该始终牢记资源、空间、速度等因素，永远不要过于担心资源问题。如果你的网站流量急剧增长，而你没有考虑资源问题，那么你就会遇到问题。

至于投票系统，个人认为应该保持如下投票方式：

table: product          table: user             table: user_product_vote       
+----+-------------+    +----+-------------+    +----+------------+---------+ 
| id |   product   |    | id | username    |    | id | product_id | user_id |
+----+-------------+    +----+-------------+    +----+------------+---------+
| 1  | bananas     |    | 1  | matthew     |    | 1  | 1          | 2       |
| 2  | apples      |    | 2  | mark        |    | 2  | 2          | 2       |
| .. | ..          |    | .. | ..          |    | .. | ..         | ..      |

原因：

首先，user_product_vote 不包含文本、二进制数据等，它纯粹是整数，因此它所占用的资源更少。

其次，在您的应用程序中，您可以更好地了解新实体的入口，例如过去 24 小时内的总投票数、最高评分产品等。

以这个例子为例：

table: user_product_vote       
+----+------------+---------+-----------+------+ 
| id | product_id | user_id | vote_type | time |
+----+------------+---------+-----------+------+
| 1  | 1          | 2       | product   |224.. |
| 2  | 2          | 2       | page      |218.. |
| .. | ..         | ..      | ..        | ..   |

还有一个简单的查询：

SELECT COUNT(id) as total FROM user_product_vote WHERE vote_type = 'product' AND time BETWEEN(....) ORDER BY time DESC LIMIT 20

另一件事是，如果用户在1AM投票，然后在2PM尝试再次投票，您可以轻松检查他们上次投票的时间，并确定是否应该允许他们再次投票。

如果您坚持使用增量示例，将会错过很多机会。

关于您的count()，无论您如何优化查询，在大规模情况下都不会有太大的差别。

对于极大的用户基础，您的资源使用情况将从不同的角度进行考虑，例如负载均衡器、主要服务器设置、Apache、缓存等等，您的查询只能做到这么多。

- RobertPitt

Answer 2

如果我的网站非常成功（我们都可以梦想），有成千上万的用户在数以千计的产品上投票，我担心每次页面查看都执行这样的COUNT会在服务器资源方面效率极低。

不要浪费时间解决想象中的问题。mysql完全能够在几分之一秒内处理数千条记录 - 这就是数据库存在的原因。清晰简洁的数据库和代码结构比神秘的“优化”更重要，而后者是没有必要的。

- user187291

Answer 3

为什么不混合使用呢？只需在产品和用户表中拥有最终计数，这样就不必每次都进行计数，并且拥有投票表，以避免重复发布。

编辑： 进一步解释一下，产品和用户表将拥有一个名为“投票”的列。每次在user_product_vote中插入成功时，增加相关的用户和产品记录。这将避免重复投票，也不必每次运行复杂的计数查询。

编辑： 还假设您已经在product_id和user_id上创建了唯一索引，在这种情况下，任何重复尝试都将自动失败，您无需在插入之前检查表。您只需要确保插入查询运行并获得了“id”字段的有效值即可。

- Sabeen Malik

Answer 4

您必须平衡网站快速运行的愿望（第二个模式最好）和计算特定用户投票并防止重复投票的能力（我会选择第一个模式）。因为您仅在user_product_vote表中使用整数列，所以我不认为性能会受到太大影响。多对多关系很常见，就像您使用user_product_vote一样。如果您确实想要计算特定用户的投票并防止重复投票，那么user_product_vote是我能想到的唯一干净的实现方式，因为任何其他方式都可能导致稀疏记录、重复记录和各种问题。

- Chris Laplante

Answer 5

您不希望每次有人投票时直接使用聚合更新产品表 - 这将锁定产品行，从而影响正在使用产品的其他查询。

假设并非所有产品查询都需要包括投票列，则可以保留单独的productvotes表以保留运行总数，并将userproductvote表作为执行用户对产品业务规则/审计的手段。

- StuartLC