如何存储历史数据

我和一些同事就存储历史数据的最佳方式展开了辩论。目前，对于某些系统，我使用一个单独的表来存储历史数据，并为当前活动记录保留一个原始表。那么，假设我有一个 FOO 表。在我的系统下，所有活动记录将进入 FOO，所有历史记录将进入 FOO _ Hist。FOO 中有许多不同的字段可以由用户更新，所以我想保持一个准确的帐户一切更新。FOO _ HIST 拥有与 FOO 完全相同的字段，但自动递增的 HIST _ ID 除外。每次更新 FOO 时，我都会在 FOO _ Hist 中执行类似于 insert into FOO_HIST select * from FOO where id = @id的插入语句。

我的同事说，这是一个糟糕的设计，因为出于历史原因，我不应该有一个表的精确副本，而应该只是将另一条记录插入到活动表中，并用一个标志表明这是出于历史目的。

是否有处理历史数据存储的标准？在我看来，我不想把我的活动记录和我所有的历史记录放在同一张表中，因为它可能超过100万条记录(我考虑的是长期记录)。

你或你的公司如何处理这个问题？

我使用的是 MSSQLServer2008，但我希望保持任何 DBMS 的通用和任意答案。

145928 次浏览
小开

你可以把桌子分开，不是吗？

”使用 SQLServer2008的分区表和索引策略 当数据库表的大小增长到数百 GB 或更大时，加载新数据、删除旧数据和维护索引就会变得更加困难。仅仅是表的绝对大小就会导致这样的操作花费更长的时间。即使是必须加载或删除的数据也可能非常大，这使得表上的 INSERT 和 DELETE 操作不切实际。2008年 Microsoft SQL Server 的数据库软件提供了表格分区，使这些操作更易于管理。”

小开
最佳答案

在操作系统中直接支持历史数据将使您的应用程序比原来复杂得多。一般来说，我不建议这样做，除非你有一个硬性要求操作历史版本的记录内的系统。

如果仔细观察，历史数据的大多数需求可以分为两类:

  • 审计日志记录: 最好使用审计表。编写一个工具来生成脚本，通过从系统数据字典中读取元数据来创建审计日志表和触发器，这相当容易。这种类型的工具可以用来改进大多数系统上的审计日志记录。如果希望实现数据仓库，还可以使用此子系统进行更改后的数据捕获(见下文)。

  • 历史报道: 关于历史状态的报道，以及随着时间推移的分析性报道。通过查询上述类型的审计日志记录表，可以满足简单的历史报告要求。如果您有更复杂的需求，那么为报表实现一个数据集市可能比尝试将历史直接集成到操作系统更经济。到目前为止，缓慢变化的维度是跟踪和查询历史状态最简单的机制，并且大部分历史跟踪都可以自动化。通用处理程序并不难编写。通常，历史报告不必使用最新的数据，因此批处理刷新机制通常是可行的。这使您的核心和报告系统架构相对简单。

如果您的需求属于这两种类别之一，那么您最好不要在操作系统中存储历史数据。将历史功能分离到另一个子系统中可能总体上会减少工作量，并且产生的事务数据库和审计/报告数据库能够更好地满足其预期目的。

小开

我不认为有一个特定的标准方法来做这件事，但我认为我会扔在一个可能的方法。我在 Oracle 工作，我们的内部 Web 应用程序框架使用 XML 来存储应用程序数据。

我们使用一种叫做 Master-Details 的模型，它最简单的组成部分是:

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

详细信息/历史表 例如称为 Widget_Details的表中至少包含:

  • ID-主键。详细信息/历史 ID
  • MASTER _ ID-例如，在这个例子中称为“ WIDGET _ ID”，这是对主记录的 FK
  • START _ DATETIME-指示该数据库行开始的时间戳
  • END _ DATETIME-指示该数据库行结束的时间戳
  • STATUS _ CONTROL-单个字符列表示行的状态。“ C”表示当前、 NULL 或“ A”将是历史/存档。我们之所以使用它，是因为我们无法在 END _ DATETIME 为 NULL 时进行索引
  • CREATION _ BY _ WUA _ ID ——存储导致创建行的帐户的 ID
  • XMLDATA ——存储实际数据

实质上，一个实体的开始是在主数据中有一行，在细节中有一行。具有 NULL 结束日期和 STATUS _ CONTROL 为‘ C’的详细信息。 当发生更新时，当前行更新为当前时间的 END _ DATETIME，status _ control 设置为 NULL (如果喜欢，则设置为‘ A’)。在详细信息表中创建了一个新行，仍然链接到同一个主机，其中包含 status _ control‘ C’、进行更新的用户的 id 以及存储在 XMLDATA 列中的新数据。

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

存储是便宜的，我们一般不删除数据，并希望保持审计跟踪。这使我们可以看到我们的数据在任何给定的时间看起来是什么样子。通过索引 status _ control = ‘ C’或者使用 View，杂乱并不是一个问题。显然，您的查询需要考虑您应该始终使用记录的当前版本(NULL end _ datetime 和 status _ control = ‘ C’)。

小开

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

如果你很快尝试另一种方法，你就会面临问题:

  • 维修费用
  • 更多的选择标志
  • 查询速度减慢
  • 表格、索引的增长
小开

真正的问题是，是否需要将历史数据和活动数据一起用于报告？如果是这样，将它们保存在一个表中，分区并为活动记录创建一个视图，以便在活动查询中使用。如果你只是偶尔需要看看它们(为了研究法律问题或其他类似问题) ，那么把它们放在一个单独的表格中。

小开

另一个选择是以[每天 | 每小时 | 无论什么]为基础归档操作数据。

基本上，这个想法是创建一个计划的 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
小开

我知道这个老文章，但只是想补充几点。 这些问题的标准是什么最适合这种情况。理解这种存储的需要，以及历史/审计/变更跟踪数据的潜在用途非常重要。

审计(安全目的) : 对所有可审计表使用公共表。定义结构来存储列名称、前值和后值字段。

Archive/History : 对于跟踪以前的地址、电话号码等情况，如果活动事务表模式在未来没有显著变化(如果历史表必须具有相同的结构) ，那么创建一个单独的表 FOO _ HIST 会更好。 如果预期表规范化，数据类型更改列的添加/删除，则以 xml 格式存储历史数据。定义具有以下列(ID、 Date、 Schema Version、 XMLData)的表。这将很容易处理模式更改。但是您必须处理 xml，这可能会给数据检索带来一定程度的复杂性。

小开

您可以使用 MSSQLServer 审核功能。在 SQLServer2012版本中，您可以在所有版本中找到此功能:

Http://technet.microsoft.com/en-us/library/cc280386.aspx

小开

可以在表上创建物化/索引视图。根据您的需求，您可以对视图进行全部或部分更新。创建 mview 和 log 时请参见此文件。如何在 SQLServer 中创建物化视图？

小开

只是想添加一个选项，我开始使用，因为我使用 Azure SQL 和多表的东西是太麻烦我。我在表上添加了一个插入/更新/删除触发器，然后使用“ FOR JSON AUTO”特性将之前/之后的更改转换为 JSON。

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

在更改之前/之后返回记录的 JSON 表示形式。然后，我将这些值存储在一个历史表中，其中包含更改发生时间的时间戳(我还存储了当前关注记录的 ID)。使用序列化过程，我可以控制在模式更改的情况下如何回填数据。

我从这个链接 给你学到了这一点

小开

在 SQLServer2016及以上版本，有一个新的功能称为 < strong > 时间表 ，旨在解决这个挑战与 开发人员的最小努力。时态表的概念与变更数据捕获(Change Data Capture，CDC)相似，但不同的是，时态表抽象出了在使用 CDC 时必须手动完成的大部分工作。


提交答案