logo标签列表

如何存储历史数据？

database-designversioning
185
一些同事和我进行了一场关于存储历史数据的最佳方式的辩论。目前，对于某些系统，我使用单独的表来存储历史数据，并保留一个原始表用于当前有效记录。例如，假设我有表FOO。在我的系统下，所有活动记录都会进入FOO，而所有历史记录都会进入FOO_Hist。FOO中许多不同的字段可以被用户更新，因此我希望保持逐步准确的记录。FOO_Hist保存与FOO相同的字段，除了自增的HIST_ID。每次更新FOO时，我执行一个插入语句到FOO_Hist，类似于：insert into FOO_HIST select * from FOO where id = @id。
我的同事认为这是糟糕的设计，因为我不应该为历史原因将一个表完全复制一遍，而应该在当前表中插入另一条记录，并标记其用于历史目的。
是否有处理历史数据存储的标准方法？对我来说，我认为不应该在同一个表中混杂我的历史记录和活动记录，因为它可能会超过一百万条记录（我考虑长期）。
你或你的公司如何处理这个问题？
我正在使用MS SQL Server 2008，但我想保持答案通用和任意DBMS。
将数据库中的记录进行版本控制的方法是什么？ - Marco Eckstein
11个回答
93

在操作系统中直接支持历史数据会使应用程序比预期的更加复杂。通常情况下，除非您需要在系统内操作记录的历史版本，否则我不建议这样做。

如果您仔细看一下，大多数历史数据的需求可以归为以下两类：

  • 审计日志：最好使用审计表来完成。通过读取系统数据字典中的元数据，编写一个生成脚本以创建审计日志表和触发器的工具相当容易。这种工具可以用于将审计日志添加到大多数系统中。如果您想要实现数据仓库（见下文），还可以使用此子系统进行更改数据抓取。

  • 历史报告：报告历史状态、“按照”位置或随时间变化的分析报告。对于简单的历史报告需求，可能可以通过查询上述审计日志表来满足。如果您有更复杂的需求，则实施数据集市以供报告可能比尝试将历史直接集成到操作系统中更经济实惠。

    慢速变化的维度是追踪和查询历史状态的最简单机制，并且许多历史跟踪可以自动化。编写通用处理程序并不那么困难。通常，历史报告不必使用最新数据，因此批量刷新机制通常是可以的。这使您的核心和报告系统架构相对简单。

如果您的需求属于以上两类中的任一类，则最好不要在操作系统中存储历史数据。将历史功能分离到另一个子系统中可能会更加轻松，并产生更适合其预期目的的事务性和审计/报告数据库。

我想我明白你的意思了。所以我用我的FOO_Hist表所做的实际上是创建了一个审计表。我没有使用触发器在更新时插入审计表，而是在程序中运行了一个语句。这样正确吗？ - Aaron
7
基本上是这样的。不过最好使用触发器来进行此类审计日志记录；触发器可以确保任何更改（包括手动数据修复）都会在审计日志中记录下来。如果您需要审计的表超过10-20个，建议使用一个触发器生成工具，整体效率可能更高。如果审计日志的磁盘流量成为问题，您可以将审计日志表放置在另一组磁盘上。 - ConcernedOfTunbridgeWells
45

我认为没有一个特定的标准方法，但是我想提供一种可能的方法。我在Oracle工作，我们的内部Web应用程序框架利用XML存储应用程序数据。

我们使用称为Master-Detail模型的东西，最简单的情况包括:

主表，例如称为Widgets，通常只包含一个ID。通常包含不随时间变化/不是历史性的数据。

详细/历史表，例如称为Widget_Details，至少包含以下内容:

  • ID - 主键。详细/历史ID
  • MASTER_ID - 例如在这种情况下称为“WIDGET_ID”，这是指向主记录的FK
  • START_DATETIME - 时间戳，表示该数据库行的开始
  • END_DATETIME - 时间戳，表示该数据库行的结束
  • STATUS_CONTROL - 单个字符列，指示行的状态。“C”表示当前，“NULL”或“A”表示历史/归档。我们之所以使用它，是因为我们不能索引于END_DATETIME为空
  • CREATED_BY_WUA_ID - 存储导致创建该行的帐户的ID
  • XMLDATA - 存储实际数据

因此，实体从主表和详细表各自开始有1行。详细信息具有NULL的结束日期和“C”状态控制。 当发生更新时，当前行被更新为当前时间的END_DATETIME，并将status_control设置为NULL（或'A'如果更喜欢）。在详细表中创建一个新行，仍与同一主关联，其status_control为'C'，进行更新的人员的ID和存储在XMLDATA列中的新数据。

这是我们历史模型的基础。Create/Update逻辑由Oracle PL/SQL包处理，因此您只需传递当前ID，您的用户ID和新的XML数据给函数，它内部执行所有更新/插入操作以在历史模型中表示该数据。开始和结束时间指示表中该行活动的时间。

存储空间价格便宜，我们通常不会删除数据而是保留审计轨迹。这样可以让我们在任何时候查看数据的样貌。通过索引 status_control = 'C' 或使用视图，杂乱无序并不是一个问题。显然，您的查询需要考虑到始终使用记录的当前版本（NULL end_datetime 和 status_control = 'C'）。

嗨，克里斯，如果你这样做，ID（主键）必须更改对吧？如果它被其他人使用，那么与另一个表的关系怎么办？ - projo
@projo 你的主表上的ID是PK，从概念上来说，这是你所处理的任何概念的“PK”。明细表上的ID是用于识别主表的历史版本（在明细表中的另一列）。在建立关系时，通常会引用你的概念的真正PK（即主表上的ID或明细表上的MASTER_ID列），并使用STATUS_CONTROL = 'C'确保获取当前版本。或者你可以引用明细ID来将某些内容与特定时间点相关联。 - Chris Cameron-Mills
1
我们正在使用相同的方法。但是现在我想知道，是仅存储START_DATETIME还是同时存储END_DATETIME更好。 - bat_ventzi
我的经验有几种变化。如果您的实体已“结束”，即已存档或已删除，则实际上可能没有带有“C”状态控制的详细记录，即没有当前行，尽管您不知道何时发生这种情况。 或者，您可以在最后一行上设置一个end_datetime，并且存在一个“ended”“C”行可以指示实体现在已被删除/存档。 最后，您可以通过另一列STATUS来表示这一点，您可能已经拥有该列。 - Chris Cameron-Mills
@ChrisCameron-Mills，你所建议的方法在我们有一个主表和一个单一细节表时会更有效。但如果细节表依赖于其他几个随时间变化的表怎么办？一个可能的思路是将类似的列添加到所有这些表中以跟踪版本控制。但那不会太复杂了吗？ - Chris
17

我认为你的方法是正确的。历史表应该是主表的一份无索引副本，还要确保表中有更新时间戳。

如果你很快尝试另一种方法，你将会面临以下问题：

  • 维护开销
  • 在查询中有更多的标志
  • 查询减慢
  • 表和索引的增长
8
在SQL Server 2016及以上版本中，有一个名为“时间表”的新功能，旨在通过最小化开发人员的工作量来解决这个挑战。时间表的概念类似于Change Data Capture（CDC），但不同之处在于，如果您使用CDC，大部分必须手动完成的工作都已被时间表抽象化了。 （点击此处查看详情）
2

我想添加一个选项，因为我使用Azure SQL，多表格的操作对我来说太过繁琐。我在我的表上添加了一个插入/更新/删除触发器，然后使用“FOR JSON AUTO”功能将更改前/后转换为JSON格式。

 SET @beforeJson = (SELECT * FROM DELETED FOR JSON AUTO)
SET @afterJson = (SELECT * FROM INSERTED FOR JSON AUTO)

这将返回变更前/后记录的JSON表示。然后，我将这些值与更改发生时间戳（我还存储有关当前记录的ID）一起存储在历史表中。使用序列化过程，我可以控制在模式更改时如何回填数据。

我从这个链接此处了解到这个方法。

1
1

我知道这是一篇旧帖子，但我想补充几点。

对于这样的问题，标准就是找到最适合情况的方法。了解存储的需求以及历史/审计/更改跟踪数据的潜在用途非常重要。

审计（安全目的）：为所有可审计的表使用一个公共表。定义结构来存储列名、之前值和之后值字段。

归档/历史：对于像跟踪以前的地址、电话号码等的情况，如果您的活动事务表模式在未来不会发生重大变化（如果您的历史表必须具有相同的结构），则创建一个单独的表FOO_HIST更好。如果您预计表规范化、数据类型更改添加/删除列，请以xml格式存储历史数据。定义一个具有以下列（ID、日期、模式版本、XMLData）的表。这将轻松处理模式更改。但是，您必须处理xml，这可能会引入一定程度的数据检索复杂性。

0

另一个选择是按[每日|每小时|其他]基础归档运行数据。大多数数据库引擎支持将数据提取到归档中。

基本上，这个想法是创建一个预定的 Windows 或 CRON 作业，该作业：

  1. 确定操作数据库中的当前表
  2. 选择每个表中的所有数据并导出成 CSV 或 XML 文件
  3. 压缩导出的数据为 ZIP 文件，最好在文件名中使用生成时间戳以便更容易地进行归档。

许多 SQL 数据库引擎都带有可用于此目的的工具。例如，在 Linux 上使用 MySQL 时，可以使用以下命令在 CRON 作业中安排提取：

mysqldump --all-databases --xml --lock-tables=false -ppassword | gzip -c | cat > /media/bak/servername-$(date +%Y-%m-%d)-mysql.xml.gz
2
这对历史数据来说一点也不合适，因为如果有人在存档周期内更改了一个值并将其改回，那么这些更新就会丢失。而且没有简单的方法来查看一个实体随时间的变化或部分恢复一个实体。 - Sgoettschkes
0
0

你可以只对表进行分区，不是吗？

“使用 SQL Server 2008 进行分区表和索引策略 当数据库表的大小增长到数百千兆字节或更多时，加载新数据、删除旧数据和维护索引可能会变得更加困难。仅仅是表的庞大大小就导致这些操作需要花费更长的时间。即使要加载或删除的数据也可能非常庞大，使得在表上执行 INSERT 和 DELETE 操作变得不切实际。Microsoft SQL Server 2008 数据库软件提供了表分区功能，以使此类操作更易于管理。”

我可以对表进行分区，但是在处理历史数据时是否符合标准呢？历史数据应该包含在同一张作为活动数据的表中吗？这些都是我想要讨论的问题。此外，这也与 SQL Server 2008 相关，不是随意决定的。 - Aaron
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接