为数据库应用程序留下审计跟踪/更改历史记录的有效策略？
人们已经成功地采用了哪些策略来维护相当复杂的数据库中的数据更改历史记录。 我经常使用和开发的应用程序之一确实可以从跟踪记录随时间变化的更全面的方法中受益。 例如，现在记录可以有多个时间戳和修改的用户字段，但我们目前没有记录多个更改的方案，例如回滚操作时。 在完美的世界中，可以在每次保存后重建记录，等等。
有关数据库的一些信息：
- 需要具有每周增长数千条记录的能力
- 50-60个表
- 主要修订表可能有每条数百万条记录
- 设置合理数量的外键和索引
- 使用 PostgreSQL 8.x
What are some strategies that people have had success with for maintaining a change history for data in a fairly complex database. One of the applications that I frequently use and develop for could really benefit from a more comprehensive way of tracking how records have changed over time. For instance, right now records can have a number of timestamp and modified user fields, but we currently don't have a scheme for logging multiple change, for instance if an operation is rolled back. In a perfect world, it would be possible to reconstruct the record as it was after each save, etc.
Some info on the DB:
- Needs to have the capacity to grow by thousands of records per week
- 50-60 Tables
- Main revisioned tables may have several million records each
- Reasonable amount of foreign keys and indexes set
- Using PostgreSQL 8.x
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您可以使用的一种策略是 MVCC（多值并发控制）。 在此方案中，您永远不会对任何表进行更新，您只需进行插入，维护每条记录的版本号。 这样做的优点是可以提供任何时间点的精确快照，并且还可以完全避免困扰许多数据库的更新锁定问题。
但它会产生一个巨大的数据库，并且 select all 需要一个额外的子句来选择记录的当前版本。
One strategy you could use is MVCC, Multi-Value Concurrency Control. In this scheme, you never do updates to any of your tables, you just do inserts, maintaining version numbers for each record. This has the advantage of providing an exact snapshot from any point in time, and it also completely sidesteps the update lock problems that plague many databases.
But it makes for a huge database, and selects all require an extra clause to select the current version of a record.
如果您使用 Hibernate，请查看 JBoss Envers。 来自项目主页：
这有点类似于 Eric 的方法< /a>，但可能要少得多的努力。 但不知道您使用什么语言/技术来访问数据库。
If you are using Hibernate, take a look at JBoss Envers. From the project homepage:
This is somewhat similar to Eric's approach, but probably much less effort. Don't know, what language/technology you use to access the database, though.
过去我使用触发器来构建数据库更新/插入/删除日志记录。
每次对特定表执行上述操作之一时，您都可以将记录插入到日志记录表中，该记录表跟踪该操作、数据库用户执行的操作、时间戳、执行操作的表以及先前的值。
可能有一个更好的答案，因为我认为这需要您在执行实际删除或更新之前缓存该值。 但你可以用它来进行回滚。
In the past I have used triggers to construct db update/insert/delete logging.
You could insert a record each time one of the above actions is done on a specific table into a logging table that keeps track of the action, what db user did it, timestamp, table it was performed on, and previous value.
There is probably a better answer though as this would require you to cache the value before the actual delete or update was performed I think. But you could use this to do rollbacks.
使用触发器的唯一问题是它会增加任何插入/更新/删除的性能开销。 为了获得更高的可扩展性和性能，您希望将数据库事务保持在最低限度。 通过触发器进行审核会增加执行交易所需的时间，并且根据交易量可能会导致性能问题。
另一种方法是探索数据库是否提供任何挖掘“重做”日志的方法，就像 Oracle 中的情况一样。 重做日志是数据库在发生故障并需要恢复时用来重新创建数据的内容。
The only problem with using Triggers is that it adds to performance overhead of any insert/update/delete. For higher scalability and performance, you would like to keep the database transaction to a minimum. Auditing via triggers increase the time required to do the transaction and depending on the volume may cause performance issues.
another way is to explore if the database provides any way of mining the "Redo" logs as is the case in Oracle. Redo logs is what the database uses to recreate the data in case it fails and has to recover.
与触发器类似（甚至与触发器类似），您可以让每个事务异步触发日志记录事件，并让另一个进程（或只是线程）实际处理日志记录。 根据您的应用程序，有多种方法可以实现这一点。 我建议让应用程序触发该事件，以便它不会对您的第一个事务造成不必要的负载（这有时会导致级联审核日志锁定）。
此外，您可以通过将审核数据库保留在单独的位置来提高主数据库的性能。
Similar to a trigger (or even with) you can have every transaction fire a logging event asynchronously and have another process (or just thread) actually handle the logging. There would be many ways to implement this depending upon your application. I suggest having the application fire the event so that it does not cause unnecessary load on your first transaction (which sometimes leads to locks from cascading audit logs).
In addition, you may be able to improve performance to the primary database by keeping the audit database in a separate location.
我使用 SQL Server，而不是 PostgreSQL，所以我不确定这是否适合您，但 Pop Rivett 有一篇关于创建审计跟踪的精彩文章：
Pop rivett 的 SQL Server FAQ No.5：Pop on the Audit Trail
构建一个审计表，然后为每个要审计的表创建一个触发器。
提示：使用 Codesmith 构建触发器。
I use SQL Server, not PostgreSQL, so I'm not sure if this will work for you or not, but Pop Rivett had a great article on creating an audit trail here:
Pop rivett's SQL Server FAQ No.5: Pop on the Audit Trail
Build an audit table, then create a trigger for each table you want to audit.
Hint: use Codesmith to build your triggers.