数据库最佳实践：旧数据怎么处理？

Question

我有一个汽车分类广告的数据库。

经过90天后，该广告将不再有效（即到期），但我希望为了档案目的保留该广告。

问题：从数据库设计最佳实践和查询性能的角度来看，是将旧的广告 A）保存在与当前广告相同的表中，还是B）将已过期的广告移动到一个已过期的表中并从当前列表中删除该广告更好？

换句话说，

选项A）：

table_classified_listing:
car_id
expired = true | false
...

选项B):

// only current listing in this table (expired = false)
table_classified_listing:
car_id
...

// only expired listing in this table (expired = true)
expired_table_classified_listing:
car_id
...

更新:

我对A选项的担忧在于，当我在MySQL数据库中运行EXPLAIN时，它说它正在使用expired作为主键进行索引。然而，对于我的查询搜索性能来说，更重要的是使用price字段，因为我正在基于price > X进行搜索。这就是为什么我考虑选择B选项。

- Timk

---

Timk，这里我们使用A，但我建议不要使用is_enabled，除非该字段真的很重要，而是在数据库中记录time_entered，然后在代码中计算过期间隔或使用视图。我可以看到一种使用情况出现，即人们希望查看30天以上的分类列表。 - Evan Carroll

Answer 1

选项A）这样你可以将所有数据放在一个地方，更容易创建用于报告、列出用户历史条目等的查询。任何速度问题都应该通过数据库对该列的索引得到缓解。选项B）是过早优化。

- Matthew Lock

Answer 2

一般建议（您需要填补空白）：

• 性能只在某些情况下才会显著（超过一百万条记录，庞大的行大小...）。

• 您是否将使用“联合”或相同查询来查询这两个表？如果不使用相同查询查询表，则建议使用不同的表（当记录数量增加时，可能会有性能收益，但主要是意义上的收益）。

---

重复的问题在于它可能会增加工作量（编写查询、测试等）。但所有技术（特别是现代技术）都允许您减少或取消重复。

例如，使用ORM，您可以拥有一个抽象实体，该实体将映射到公共字段但没有表，以及映射到您的表的两个子类。没有列信息的重复。ORM还可以创建您的数据库脚本，因此您甚至不需要这些脚本（尽管您应该手动审查它们以用于生产数据库）。

---

更新在问题更新后：

您可以创建所需的索引，不用担心。如果您要查询价格超过X的未过期数据以获取性能，则创建一个索引（已过期，价格）即可:-)

- KLE

---

我没有计划或者预见到需要查询两个表格（供参考）。 - Timk

那么，你的意思是选择选项A。对吗？ - Timk

@Timk 所以这一点很清楚。你有任何行大小或记录数量的数字吗？ - KLE

@Timk 我其实还没有在选项之间做出选择 :-). 但是我根据你的更新更新了我的答案... - KLE

Answer 3

不要使用B，它基本上会分离属性。

我会使用两个日期列，有效开始日期和有效结束日期。

- no_one

Answer 4

以您所描述的方式累积列表，性能下降需要很长时间。而且硬件和软件的性能提升更快。

在确保简单方法无法解决问题之前，不要将事情变得复杂。将其保留在一个表格中。请参阅有关pessimizations的问题 - 这是其中之一。

- dkretz

---

+1 是因为注意到硬件和软件性能往往以比您填充机器数据更快的速度增加。 - Matthew Lock

Answer 5

个人建议将所有过期记录移动到单独的表中。随着数据库规模的增长，您会希望从“活动”记录中获得更好的性能，因为这些记录很可能经常被访问。

所有旧记录都会导致表格大小不断增长，这意味着即使进行了查询优化等操作，查询速度也会变慢。

编辑： 正如其他人提到的，这种方法的一个明显缺点是如果您计划经常合并实时数据和归档数据。如果您始终将它们分开引用，则非常好，但如果没有，则需要大量的连接和联合才能将数据组合在一起 - 这并不理想。

- Gavin H

---

列distinct到期/有效记录索引的性能损失如何？那不应该很快吗？ - kender

Answer 6

至少还有另外两个选项可以解决保留旧数据的一般问题：

• 按日期分区，然后通过回滚日期或分离分区来处理。 或者，将每个分区实现为单独的表，然后使用 union-all 视图将它们连接起来。 在后一种情况下，通常最好使用粗粒度的分区（月而不是日）。 MySQL 应该能够支持这两种解决方案，而且分区还有一个额外的好处，即提高与查询大量表数据相关的查询性能。
• 导出所有要保留的数据，截断表，然后重新加载。 真的 - 当你删除大量数据时，重新加载可能比删除更快。 许多数据库不需要这样做 - 至少在几年内他们没有这么多数据，然后他们的管理员发现他们需要升级硬件或清除一整年的数据。 此时，通常这种策略是最好的。

回到您提供的两个解决方案：

• 将数据保留在同一张表中。对于您的数据量来说，这可能是最好的方法。但是，在某个时候（7年后？）您可能仍然想要删除它，那么您可以有一个小异步作业来进行滴水式删除，可以删除分区或者可以导出/重新加载。
• 将归档数据保留在不同的表中。如果您可以利用不同（较少）的硬件来访问不经常访问的归档数据，例如单独的服务器、更少的CPU、不同的便宜/慢速磁盘、较小的内存缓冲区等，则此方法最为有用。MySQL没有足够的可配置性来执行其中的一些操作。另一个原因是，如果您的查询经常执行表扫描，并且通过将大多数数据移出来可以显著提高性能，则情况可能如此。您正在使用MySQL - 它具有臭名昭著的不成熟的优化器/计划程序，并且您没有使用分区。因此，每当无法使用索引时，您将进行表扫描。如果您需要快速查询，拥有小型服务器或大量行，则我会将旧数据保留在单独的表中。但是，以下可能是更好的方法：
• 将数据保留在两个表中，但第一个表拥有100％的数据（新旧数据），而第二个表仅拥有最新的数据。采用此方法的原因是可能会生成各种子集或聚合 - 现在具有最新数据的表只是其中之一。这些子集/聚合并不是绝对必要的 - 您始终可以查询主表。但是，分析查询往往会对数据库造成很大的压力 - 这些表可以使它们非常快速。老实说，任何值得花时间分析的过程都值得分析。

- KenFar

Answer 7

我的理解是：

• 由于这些是分类列表，数据的性质是“短暂的”，并且会过期。
• 因此，过期数据的数量可能超过“当前”的未过期数据。

如果我理解得正确，下一个问题是您的过期数据使用频率有多高？用于什么目的？正如@ghills所指出的，sql-union可能会使您减速。

如果过期数据不需要在线，将其归档到单独的表中可能是有意义的。特别是如果过期行数可以超过活动行数。

如果您将它们保留在同一张表中，“where expired=false”可能会成为您的恒久伴侣，并且由于选择性很低（即许多过期行），在“过期”列上建索引将不会带来太多好处（Oracle具有位图索引-但这可能根本不适用于此）。

- blispr

Answer 8

我会将它们放在同一张表中。否则，(a) 你会有两个具有相同列的表。每次更改数据时，你都必须记得同时更改这两个表。迟早有人会忘记——或者想到一个好主意，认为一个表中的数据在另一个表中不需要——现在你的设计变得更加复杂了。很快你就要写两遍完全相同的逻辑：一次从“当前”表中检索，再一次从“归档”表中检索。但是，当有人对一段代码进行更改并忘记对另一段代码进行相同的更改时，问题就来了。接下来的人就无法确定它们是否不同，因为它们应该不同还是只是有人忘了。等等。(b) 你可能会有查询想要同时访问这两个表，比如“告诉我过去12个月中询问价格超过20000美元的所有广告”，其中一些广告可能是当前的，而其他的则是已归档的。这些查询现在变成了联合或复杂的连接，而不是简单地不包括“过期为真”或“过期为假”的标志。

关于性能问题，这很容易：创建一个包含您需要包含的任何内容的多字段键。过期+价格或过期+型号名称似乎是常见的键。您可能希望将过期放在第一位，因为您的大多数查询可能都希望非过期记录，但我只是猜测。选择值得索引的内容是一个复杂的决定，但当多个字段上有明显的常见查询时，就应该进行索引。

- Jay

Answer 9

没有所谓的通用最佳实践。但是，如果表格趋向于变得非常庞大，而您的搜索需要太长时间，那么您可能需要将项目存档到单独的表格中。否则，您可以实现适当的索引以加快速度。这真的取决于您考虑的数据量和类型。

- S M Kamran