如何最好地处理历史数据的存储？
我正在尝试确定应该如何存储历史交易数据。
我是否应该将其存储在一个表中，每次记录都会重新插入新的时间戳？
我是否应该将历史数据分解为单独的“历史”表，并仅将当前数据保留在“活动”表中。
如果是这样，我该如何最好地做到这一点？ 使用自动将数据复制到历史表的触发器？ 或者我的应用程序中有逻辑？
根据 Welbog 的评论进行更新：
将会有大量的历史数据（数十万行 - 最终可能数百万行），
主要是对历史数据运行搜索和报告操作。
性能是一个问题。 搜索不必运行整夜才能产生结果。
I'm trying to determine how I should store historical transactional data.
Should I store it in a single table where the record just gets reinserted with a new timestamp each time?
Should I break out the historical data into a separate 'history' table and only keep current data in the 'active' table.
If so, how do I best do that? With a trigger that automatically copies the data to the history table? Or with logic in my application?
Update per Welbog's comment:
There will be large amounts of historical data (hundreds of thousands of rows - eventually potentially millions)
Primarily searches and reporting operations will be run on the historical data.
Performance is a concern. The searches shouldn't have to run all night to produce results.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果需求仅用于报告，请考虑构建单独的数据仓库。 这使您可以使用缓慢变化的维度等数据结构，这些数据结构对于历史报告要好得多，但在事务系统中效果不佳。 由此产生的组合还将历史报告从生产数据库中移出，这将是性能和维护方面的胜利。
如果您需要在应用程序中提供此历史记录，那么您应该实现某种版本控制或逻辑删除功能，或者使所有内容完全相反并重述（即事务永远不会被删除，只是撤销并重述）。 仔细考虑您是否真的需要它，因为它会增加很多复杂性。 制作一个可以正确重建历史状态的事务应用程序比看起来要困难得多。 财务软件（例如保险承保系统）在这方面的表现比您想象的要多得多。
如果您仅需要历史记录用于审核日志记录，请创建影子表和审核日志记录触发器。 这比尝试在应用程序中正确且全面地实现审核日志记录要简单得多，也更可靠。 触发器还将从应用程序外部的源获取对数据库的更改。
If the requirement is solely for reporting, consider building a separate data warehouse. This lets you use data structures like slowly changing dimensions that are much better for historical reporting but don't work well in a transactional system. The resulting combination also moves the historical reporting off your production database which will be a performance and maintenance win.
If you need this history to be available within the application then you should implement some sort of versioning or logical deletion feature or make everything fully contra and restate (i.e. transactions never get deleted, just reversed out and restated). Think very carefully about whether you really need this as it will add a lot of complexity. Making a transactional application that can reconstruct historical state correctly is considerably harder than it looks. Financial software (e.g. insurance underwriting sytems) fails to do this a lot more than you might think.
If you need the history solely for audit logging, make shadow tables and audit logging triggers. This is much simpler and more robust than trying to correctly and comprehensively implement audit logging within the application. The triggers will also pick up changes to the database from sources outside the application.
这个问题符合业务逻辑。 首先了解您的业务需求，然后从那里开始。 数据仓库是这种情况的一个很好的解决方案。 ETL 将为您提供处理数据流的多种选择。 您关于“历史”与“活跃”的基本概念是非常正确的。 如果将历史数据及其所有维度和事实表保存在数据仓库中，您的历史数据将更加高效和灵活。
This question goes along the line of Business Logic. Know your business requirements first then start from there. A Data Warehouse is a nice solution for this kind of situation. ETL will give you lots of options in dealing with data flows. Your basic concept of 'History' vs 'Active' is quite correct. Your history data will be more efficient and flexible if kept in a data warehouse with all their dimension and fact tables.