database-design - MySQL 对大量 "rotation"统计数据的想法？

Question

我有一个在线游戏，我记录了很多游戏统计数据。这些统计数据表会很快变大，我必须小心，因为一旦表变得足够大，仅仅记录更多的统计数据就会导致游戏性能变得非常糟糕。

我的策略不是很好，就是保持统计表很小。我有一个每 24 小时创建一个新表的自动过程，以防止性能变得过于失控。但我的解决方案是丑陋的，是一种统计表的“轮换”。我使用 innodb 并设置了几个索引来提高性能，然后我只保留了 30 个这样的表(每个表都是 24 小时，所以我节省了一个月的统计数据)。每 24 小时，我的自动化流程会删除“stats30”表，然后将所有编号的表重命名为更高的数字，然后创建一个新的空白表，简称为“stats”。这是“实时”表，其中正在积极记录统计信息。

这些表格基本上记录了游戏中每个玩家和他们互动的每个其他玩家之间的每笔交易，因此数据呈指数级爆炸。当发生新交易时，它会检查当天这两个玩家之间的交易是否已经有一行。如果有，它会更新行以更改其事务。否则，它会创建一个新行。一天互动 1000 次的一对玩家和只互动一次的一对玩家在当天的表中都只有一行。数据库上的每个操作都涉及一个 SELECT，然后是一个 UPDATE 或一个 INSERT，所以它在当前设计的读取和写入之间非常均匀。相对于单个 SELECT、UPDATE 和 INSERT，在更大的意义上读取数据，即用于分析统计数据和多个玩家的情况非常少。每天大约创建 150,000 行。

我知道这可能会更好。我不能轻易减少我正在记录的数据量，但我担心 1.performance 和 2.simplicity。例如，我可以通过每 4 小时创建一个新表来进一步提高性能，但随后我不得不处理 180 个表。相反，我可以通过只使用一张 table 来简化它，然后一切都会戛然而止。

请注意，我确实需要更新这些表中的行，所以我不能使用 ARCHIVE 存储引擎之类的东西，但我只需要在“实时”统计表上插入或更新。

还有一个小问题，当每天轮换过程发生时，当时进入的任何查询可能会丢失。 (如果它正在重命名所有表并创建一个新表的过程中，新条目可能会失败。)丢失一些插入不是一个大问题，而是一种不会发生此错误的解决方案，或者可以“以原子方式完成” “会更好。

感谢您提供任何可能有帮助的想法! :)

Answer 1

每天有 15 万行，平均数是多少？一行的大小？
这些行是否包含您可以通过保留引用来最小化的冗余数据？

一般来说，保持表很小总是一件好事，这样索引更新就会快速进行。此外，正如上面 Ben S 提到的，您的查询至少应该优化，以便无法访问缺少索引的列等。如果您已经使用 EXPLAIN 和 mysql 服务器的慢查询日志，您可以找到一些可能的问题启用它。

可以帮助您解决性能问题的一件事是 memcached 守护程序。使用它你可以延迟写入你的数据库，从而消除一些 Steam ，并且仍然不会受到脏缓存等的影响。尽管取决于您使用的应用程序框架(如果有的话)，但需要一些工作才能将其实现到您的应用程序中。

出于存档和统计目的，我建议您查看 InfoBright ( http://www.infobright.org/ )。它是一个开源 MySQL 替代品(基于 MySQL)。它的指定用途是成为数据仓库存储。不过，您可以将其用于各种大容量数据分析。
它有一个非常好的压缩功能，在我们的例子中，将大约 23TB 的原始数据减少到大约 1.2TB 的压缩数据。我想不用说，查询压缩数据的特定行可能/将会非常慢。但是对于统计数据来说，它非常快。因此，如果您不查询特定行，而是分析诸如“在 12 月 8 日到 2 月 9 日之间使用值 foo > bar 更新了多少行”之类的内容，它将为您提供非常好的性能。事实上，当您使用 DB 时，它会分析您的使用情况并创建一个知识网格，该网格将为您的特定查询优化其数据。

我想到的下一个问题是……如果您“仅”保留一天或几小时的统计数据/ session 数据，那么关系数据库是否适合这项工作？
在不知道您的应用程序的确切性质的情况下，我可以想象某种类型的内存 session (例如，可以驻留在一个 terracotta 集群中)，它们经常写入事务日志并提交其数据可能更适合。但正如我所说，这在很大程度上取决于您的应用程序的性质和所讨论的数据量。