数据库中的历史行管理

Question

和许多数据库一样，我正在设计一个数据库，应该记录每个表中更改的行的先前版本的记录。

这个问题的标准解决方案是为每个数据表保留一个历史表，每当需要更新数据表中的行时，就会将当前行的副本插入到历史表中，然后更新数据表中的行。

这种解决方案的缺点有：

- 需要维护两个表而不是一个（如果表的结构需要更改）。 - 应用程序需要知道两个表而不是一个。 - 表的名称可能需要缩短以保持表名和历史表名的约定（例如，SOME_TABLE，SOME_TABLE_HIST）。

我正在考虑一种不同的解决方案，并想知道它是否可行。对于每个表，我们添加列IS_LAST。

- 当向表中插入行时，它将被插入IS_LAST = 1。 - 当更新行时，将原始行的副本复制到相同的表中，更改IS_LAST = 0，然后根据需要更新原始行（仍保持IS_LAST = 1）。

假设在我的情况下，平均更新行数为10次。还假设至少90％的应用程序操作仅发生在最近版本的行上。

我的数据库是Oracle 10g，因此为了使“活动”表变得轻巧，我们可以将表分成两个分区：IS_LAST = 1分区和IS_LAST = 0分区。

分区是解决历史数据保留问题的好方法吗？

这种解决方案是否限制了其他表的分区潜力？

谢谢！

- Asaf Z

Answer 1

首先的问题应该是：你会如何处理这些数据？如果没有明确的业务需求，就不要去做。

我曾经做过类似的事情，在三年的运行中，只有大约20％的“有效数据”，其余都是“以前的版本”。而且这是1000万+4000万条记录。在过去的三年中，我们有两个请求来调查更改历史记录，但两次请求都很愚蠢-我们记录了记录更改的时间戳，并被要求检查人员是否加班（在下午5点后）。

现在，我们卡在了一个包含80％无用数据的超大型数据库中。

编辑：

既然您要求可能的解决方案，我将描述我们所做的。它与您考虑的解决方案有些不同。

1. 所有表都有代理主键。
2. 所有主键都从单个序列生成。这很好用，因为Oracle可以生成和缓存数字，所以这里没有性能问题。我们使用ORM，希望每个内存中的对象（和相应的数据库记录）都有唯一的标识符。
3. 我们使用ORM，数据库表和类之间的映射信息以属性的形式存在。

我们在单个归档表中记录所有更改，具有以下列：

• id（代理主键）
• 时间戳
• 原始表格
• 原始记录的ID
• 用户ID
• 事务类型（插入、更新、删除）
• 记录数据作为varchar2字段
  • 这是以字段名/值对的形式实际数据。

事情是这样的：

• ORM有insert/update和delete命令。
• 我们为所有业务对象创建了一个基类，该基类覆盖了insert/update和delete命令。
  • insert/update/delete命令使用反射创建字段名/值对形式的字符串。代码查找映射信息并读取字段名、相关值和字段类型。然后我们创建类似于JSON的东西（我们添加了一些修改）。当创建表示对象当前状态的字符串时，它将插入到归档表中。
• 当新的或更新的对象保存到数据库表中时，它将保存到其目标表中，并同时插入一个具有当前值的记录到归档表中。
• 当对象被删除时，我们从其目标表中删除它，并同时插入一个具有事务类型=“DELETE”的归档表中的记录。

优点：

• 我们的数据库中没有为每个表创建归档表。当模式更改时，我们也不需要担心更新归档表。
• 完整的归档数据与“当前数据”分开，因此归档不会对数据库产生任何性能影响。我们将其放置在单独的表空间和单独的磁盘上，它可以正常工作。
• 我们创建了两种查看归档的表单：
  • 通用查看器，可以根据归档表上的过滤器列出归档表。用户可以在表单上输入筛选数据（时间跨度、用户等）。我们在表单字段名/值中显示每个记录，并对每个更改进行颜色编码。用户可以查看每个记录的所有版本，还可以查看是谁何时进行了更改。
  • 发票查看器 - 这个比较复杂，但我们创建了一个表单，显示的发票与原始发票输入表单非常相似，但具有一些显示不同生成版本的附加按钮。创建这个表单需要相当大的工作量。该表单被使用了几次，然后就被遗忘了，因为在当前的工作流中不再需要它。
• 创建归档记录的代码位于单个C#类中。不需要在数据库中的每个表上创建触发器。
• 性能非常好。在高峰期，系统由大约700-800个用户使用。这是ASP.Net应用程序。ASP.Net和Oracle都在一个双Xeon带8Gb RAM的计算机上运行。

缺点：

• 单个表格归档格式比每个数据表都有一个归档表的解决方案更难读取。
• 在归档表中搜索非ID字段很困难 - 我们只能在字符串上使用LIKE运算符进行搜索。

所以，再次检查归档需求。这不是一项琐碎的任务，但收益和使用可能是最小的。

- zendar

---

@zendar，你使用分区吗？ - tuinstoel

@学习：这个答案的要点是他在开始编码之前应该重新考虑是否有必要这样做。我会重新表述我的答案第一行，使其更加清晰明了。 - zendar

Tuin，同意，我不喜欢真实数据在一些虚构的CLOB中的事实。但是拥有一个审计表是一个巨大的优势。我更喜欢（表、列、ID、之前值、之后值、时间戳、用户）。 - Mark Brady

@tuinstoel - 因政治问题而陷入困境。每个人都知道每笔交易都会被审计，我们必须将其保留，即使没有人查看存档。2. 您关于搜索的观点是正确的，我会在“cons”中添加备注。 - zendar

你可以记得每次添加新的“真实”表时都要添加一个额外的表……或者在存储方面多花一点钱。这是“数据库”大小的唯一影响。 - Mark Brady

Answer 2

我会创建两个表格：一个用于存储IsLast类型的值，另一个用于存储历史值。然后我会设置触发器，在每次更新isLast时将该值插入到历史表中。

- zvolkov

---

用Oracle数据库的话，为什么还要麻烦呢？只需在那一列上进行分区并开启行迁移即可。这些都是内置功能，为什么还要重写并维护两个表呢？ - Mark Brady

Answer 3

如果我只需要保留1或2个历史表，我会完全按照Tuinstoel的建议去做。但是，如果你需要在几十个表上执行此操作，我会更倾向于zendar所描述的解决方案。原因如下。

如何回答以下问题：

- 昨天一切正常，今天发生了什么变化？ - 用户SMITHG是否进行了任何更改？

这些问题需要对每个表进行一次查询，无论是单独的_hist表还是表内的分区。不管怎样，都需要大量的查询列表。如果你有一个像这样的中央表，那就轻而易举了。

table_name, Column_name, PK, Before_value, After_value, User, timestamp

插入操作只有后续值，

删除操作只有先前的值，

更新操作两者都有，但仅针对更改的列。

一些变化

如果您愿意，可以包括I/U/D列

您可以在插入操作中排除列值，只记录PK和I，因为正确的值仍然在表中。

由于这是Oracle，您可以按table_name进行分区，因此实际上每个真实表都有一个hist“表”。

您可以轻松回答上述问题，我认为这些问题非常简单，并且它可以处理您可以使用分区或_hist表回答的每个问题。

- Mark Brady

Answer 4

由于您正在使用Oracle，您可以检查Oracle Flashback Technology。它记录数据库中所有更改的更改，包括数据和结构。它还记录时间戳和用户名。

我没有使用过它，但它看起来很有能力。

- zendar

---

这只是一个LLG功能，如果他们正在使用它，那太好了，否则，这不是一个选项。 - Matthew Watson

Flashback是在9i版本中引入的，大约8-9年前。在10g版本中，它得到了相当成熟的发展。 - zendar

Answer 5

和其他人一样，我使用ORM（Propel），其中包含自定义的保存和删除方法。这些方法覆盖了ORM中提供的标准保存和删除方法。它们检查哪些列已更改，并为每个更改的列在更改表中创建1行。

change表的模式： change_pk、user_fk、user_name、session_id、ip_address、method、table_name、row_fk、field_name、field_value、most_recent、date_time

例如： 1、4232、'Gnarls Barkley'、'f2ff3f8822ff23'、'234.432.324.694'、'UPDATE'、'User'、4232、'first_name'、'Gnarles'、'Y'、'2009-08-20 10:10:10';

- lo_fye

Answer 6

我想到的主要限制是，您的表中将有大量历史数据，这意味着需要考虑索引问题，并可能会在CRUD查询中引入额外的复杂性。

您不想使用似乎是这种情况下通常的解决方案，有什么特别的原因吗？

- ahockley

---

这是Oracle，分区可以解决您的问题。您只需在Is_last上进行分区并打开行迁移，您的查询将永远不会看到旧数据。 - Mark Brady

Answer 7

你如何定义主键？由于在同一张表中保留历史行，将会有许多具有相同主键的行。

此外，当单个“真实”行被更改多次时，似乎没有办法知道历史行的顺序。

（我曾经参与的一个项目中，我们使用CodeSmith生成了所有历史表和触发器，这非常有效。）

- Ian Ringrose

Answer 8

我会使用 IS_LAST=1 分区和 IS_LAST=0 系统。因为它被分区了，所以它会很快（分区修剪）并且您永远不必查询普通表和历史表的联合。

我会使用 IS_LAST='Y'/'N' 而不是 1/0。1和0毫无意义。

有一个特殊的技巧可以帮助保证每个实体只有一行具有 IS_LAST='Y' ：您可以创建一个基于函数的唯一索引，该函数返回 null 当 IS_LAST='N' 并在 IS_LAST='Y' 时返回 id。详见：http://www.akadia.com/services/ora_function_based_index_1.html

- tuinstoel

Answer 9

基于时间跟踪它是否有助于您每天实现所需的效果，以及在业务结束或最低交易量时间（如果您执行了将尾随数据移动到历史表的过程）是否有助于您？这样，所有更新都将是插入操作，无需锁定。敬礼，安迪

- Andy

Answer 10

这完全取决于你拥有什么：

• 你是运行标准版还是企业版？分区仅作为企业版的选项包含在内。更多信息请参见此处。
• 如果你正在寻找一种简单的解决方案，不想维护自己的代码，那么你可以考虑使用Workspace Manager来完成它。然而，我发现有一些限制（例如，Oracle Text索引维护似乎很困难，甚至不可能，尽管我只在10gR2上看过它）。
• 否则，我会选择zvolkov的解决方案（带有触发器写入历史表的实时表）或Mark Brady的解决方案（更改日志）。我已经使用了这两种模式，每种模式都有其优缺点。
• @zendar：闪回查询仅适用于你拥有的撤消时间。它不是长期的解决方案，只是一个回顾最多几个小时的解决方案（取决于你指定了多少撤消保留）。

- Chris R. Donnelly