mysql - 我应该在非常常见的查询中使用 count(*) 还是存储数字

Question

我有两个表(实际上更多，但只对这两个感兴趣)。

USER_ACTION (ID, ID_USER, ID_ACTION, TMST) AS A
ACTION (ID, DATA, NUM_USERS) AS B

但是，B.NUM_USERS 实际上表示 USER_ACTION 中具有 A.ID_ACTION = B.ID 的记录数

作为性能优化，这是可以接受的(一个非常常见的查询经常恢复 ACTION 数据)，或者因为这破坏了关系模型，所以这是一个坏主意，查询应该是:

SELECT B.ID, B.DATA, count(*) AS NUM_USERS 
FROM ACTION B JOIN USER_ACTION A ON A.ID_ACTION = B.ID
WHERE B.ID = ?
GROUP BY B.ID 

如果第二个选项是正确答案，我是否应该放置任何索引来优化此查询？

-- 编辑--

使用当前模型运行解释后，但匿名化(8 个用户执行所选操作):

id  select_type table   type    possible_keys       key         key_len     ref     rows    Extra
1   SIMPLE      B       const   PRIMARY             PRIMARY         8       const   1   
1   SIMPLE      A       ref     FK_USER_ACTION  FK_USER_ACTION      8       const   8   Using index

Answer 1

我建议保留您所描述的查询，并在 USER_ACTION 中的 ID_ACTION 和 ACTION 中的 ID 上添加索引。

您的 where 过滤器和分组依据都将受益于 ACTION 上的索引，并且与 USER_ACTION 的连接应该是 eq_ref 连接(了解更多信息 http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/ )，这在大多数情况下会很快。在查询前面使用 EXPLAIN EXTENDED 来验证操作计划。如果您开始注意到任何缓慢的情况，您还可以使用 (ID, DATA) 上的复合索引对 ACTION 中的 DATA 进行索引。这会给你一个覆盖索引，但我怀疑用 ID 索引 DATA 的成本是否真的值得(更多信息:http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/ ...旧，但仍然适用)

一般来说，如果许多 count() 是数千行，您可能需要考虑通过物化 View 或 cron 作业或其他方式建立汇总表。计算(例如)100k 行仍然比预先计算要慢。但基本上，除非您处理 USER_ACTION 中需要进行 count(*) 才能返回结果的数千行，否则您不会注意到太慢。坦率地说，我认为您不会遇到这个问题......所以您应该可以使用您描述的连接和我所做的索引。使用 EXPLAIN EXTENDED 来验证这一点。另请注意，如果您使用 INNODB(例如 http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html )，LRU 可能会在这里发挥一点作用。只是需要注意的事情，您想要实现的概念的存在。