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

Question

就实现以下结果的最佳方式提出一些意见之后:

我想在我的 MySQL 数据库中存储可以由用户投票的产品(每票值(value) +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

如果我的网站取得巨大成功(我们都可以梦想)并且我有成千上万的用户对数千种产品进行投票，我担心每次页面浏览都执行这样的计数在服务器资源方面会非常低效。

一种更简单的方法是在每次添加投票时在产品表中增加一个“投票”列。

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

虽然这对资源更友好 - 我丢失了数据(例如，我无法再阻止一个人投票两次，因为没有他们的投票事件记录)。

我的问题是:
i) 我是否过于担心服务器资源并且应该坚持使用三表选项？ (即，我是否需要更加相信数据库处理大型查询的能力)
ii) 是他们在不丢失信息的情况下实现结果的更有效方式

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 不包含文本、blob 等，它是纯整数，所以占用资源较少。

其次，您有更多通往应用程序中新实体的途径，例如过去 24 小时的总投票数、过去 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、捕获等，您的查询只能做这么多。