优化记录历史数据库的最佳方法
我有一个保存记录历史的数据库。对于记录的每次更新，系统都会通过将“状态”列设置为“0”来“停用”前一条记录（及其所有子记录）。
现在这还不是问题……但最终这个系统将会有很多记录，而现在历史比速度更重要。但插入的记录越多，搜索速度就越慢。
归档记录的最佳方法是什么？我建议创建一个克隆的存档数据库来保存数据。我还有一个想法，将所有以前的记录存储到一个 xml 文件中，如果我们需要挖掘存档记录，可以稍后读取/加载该文件。
I have a database that keeps record history. For each update to a record, the system will "deactivate" the previous record (along with all it's children), by setting the "Status" column to "0".
Now it's not a problem yet...but eventually this system is going to have a lot of records, and history is more important than speed right now. But the more records inserted, the slower searches become.
What is the best approach to archive the records? I've had suggestions to create a cloned archive database to hold the data. I've also had the idea to storing all previous records into a xml file, that can be read / loaded later if we need to dig up archived records.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您的 DBMS 支持，您可以创建一个仅包含活动记录的单独分区。您还可以向 Status 添加索引，以便 select ... from tbl where status=1 的速度不会太慢。
http://msdn.microsoft.com/en-us/library/ms187802。 ASPX
You could create a separate partition containing only the active record if your DBMS supports it. You can also add an index to Status so that the select ... from tbl where status=1 isn't incredibly slow.
http://msdn.microsoft.com/en-us/library/ms187802.aspx