把MySQL表拆分成多个表之前，应该考虑表的大小？

Question

问题: 我们有一个非常大的表格，并且不断增长。其中大部分记录 (约80%) 是历史数据（具有过去当前日期的“DATE”字段），它们很少被查询，而一小部分记录（约20%）是当前数据（“DATE”字段在当前日期之后），大多数查询都搜索这些当前记录。

考虑两种可能的情况，哪种更好（考虑总体实现难度和性能等因素）：

1. 将大表拆分为历史数据和当前数据两个表。每天将过期日期的记录从当前表移动到历史表中。

2. 在同一个表格中保存记录（将DATA字段定义为索引）。

方案A需要更多的实现和维护工作，并且需要在每天的基础上移动日期，并导致负载过重，而方案B则意味着要搜索一个大型数据库（尽管已经建立索引）。这是否会导致内存问题？哪种方案更值得推荐？还有其他建议吗？

- cybergeek654

---

完全取决于数据、硬件和索引。过度分区的表在某些情况下（如允许的打开文件太少，允许的打开表数量太小）可能会降低性能，而不是提高性能。通常，一个规范化良好的表，其中包含>100GB的数据，不应该成为问题。 - Norbert

根据您之后需要对归档数据执行的操作，您可能还想了解一下ARCHIVE存储引擎。http://dev.mysql.com/doc/refman/5.6/en/archive-storage-engine.html - CBroe

Answer 1

你通常不希望将一个大表拆分成多个小表，但是将当前表和历史表分开的做法是完全合理的。这样可以根据查询需求对当前表进行优化。根据你提供的信息，我可能会选择使用两个表，因为它允许进行这样的优化。

然而，不要拆分历史数据，而应该使用分区技术。具体请参考文档。需要注意的是，在查询时需要在where子句中指定分区键以利用这些分区。对于大型表格，这是很常见的做法。

- Gordon Linoff

---

感谢 Gordon 的回复。所以你推荐使用方法 A。在这种情况下，我应该有一个每日维护操作和数据库，例如：“检查 CURRENT 表，找到 DATA 字段过期的记录，并将这些字段移动到 HISTORICAL 表中”。这个维护操作不会对服务器造成严重负载吗？ - cybergeek654

你能解释一下“不要分割历史数据”是什么意思吗？为什么不能这样做？我的问题是将所有数据拆分为当前和历史数据。你的意思是采用B方法，根据日期进行分区，然后重新分区吗？ - cybergeek654

@cybergeek654...如果你想要考虑将历史数据分割，不用麻烦了。使用分区即可。当我第一次看到这个问题时，我以为你会尝试使用更多的表格。 - Gordon Linoff

Answer 2

问题：历史数据对系统功能是否必要，还是这些记录是为了其他目的（例如审计）而存储的？也许现在是时候将历史数据移动到档案中进行清理了。

- Jason Rice

---

不，历史数据不用于系统级功能。 - cybergeek654

如果我选择方案A，那么历史记录就是指日期字段在当前日期之前的记录，它们占据了整个数据库的很大一部分，而且关于它们的查询非常少。 - cybergeek654

Answer 3

根据我的经验，大多数拥有大数据的系统都有历史表。在我所见过的大多数情况下，当前数据和历史数据都有不同的用户组。前端用户使用当前数据来处理客户的当前或最近交易，而历史数据通常由不必直接与客户/客户端交谈的用户组使用。

不要过多担心实施和维护问题，因为我认为你的主要考虑因素都是关于性能的。实现只是一次性的事情，在将程序移入生产后会按指定频率运行（如每周、每月或每年进行归档）。维护非常简单，一旦实施完成，您就可以忘记它。您只需要确保充分测试程序即可。

对于标准化的历史表，表具有相同的结构和字段名称，这使得数据复制更加容易。这样，一个人可以在表之间进行连接。

如果选择不拆分数据，则会继续添加索引。但是在某个时间点上，您仍然会再次遇到相同的问题。

- Eddie Banz

---

感谢 Eddie 的回答。在我的情况下，同一用户组可以访问当前和历史数据。尽管历史数据查询所占的比例要小得多。 - cybergeek654

大多数拥有大数据集（即大小不再按记录计算，而是按千兆字节计算）的公司之所以要分割或归档旧数据，其中一个原因是大部分旧数据与当前日常业务无关。您和您的团队将需要每天确定这些历史数据的频率或相关性。我认为您和您的团队将需要进行非常彻底的研究，以说服用户组和管理层是否应该拥有归档表。 - Eddie Banz