SQL数据库最佳实践 - 使用归档表？ [英] SQL Database Best Practices - Use of Archive tables?
问题描述
我不是经过训练的DBA，而是执行一些SQL任务，并且有这个问题：
I'm not a trained DBA, but perform some SQL tasks and have this question:
在SQL数据库中，我注意到使用模仿另一个的归档表表具有完全相同的字段，并且当该数据被视为归档时用于接收来自原始表的行。由于我看到这些表位于同一个数据库和同一个驱动器上的示例，所以我的假设是为了提高性能。这样的表格中没有超过约1000万行...
In SQL databases I've noticed the use archive tables that mimic another table with the exact same fields and which are used to accept rows from the original table when that data is deemed for archiving. Since I've seen examples where those tables reside in the same database and on the same drive, my assumption is that this was done to increase performance. Such tables didn't have more than a about 10 million rows in them...
- 为什么要这样做而不是使用列指定行的状态，例如in / active标志的布尔值？
- 这样会提高成效？
- 由于数据可能仍然需要查询（或与当前数据相结合），因此，正确构建这些数据的最佳模式是什么？
- 还有什么要说的吗？
- Why would this be done instead of using a column to designate the status of the row, such as a boolean for an in/active flag?
- At what point would this improve performance ?
- What would be the best pattern to structure this correctly, given that the data may still need to be queried (or unioned with current data) ?
- What else is there to say about this ?
推荐答案
归档的概念是一个物理，不合逻辑，一个。逻辑上，归档表包含完全相同的实体，应该是同一个表。
The notion of archiving is a physical, not logical, one. Logically the archive table contains the exact same entity and ought to be the same table.
物理问题往往是务实的。总体观念是数据库变得越来越大（大/慢））。归档记录可以更容易地执行以下操作：
Physical concerns tend to be pragmatic. The overarching notion is that the "database is getting too (big/slow"). Archiving records makes it easier to do things like:
-
不同的优化索引结构。存档表可以具有更多的索引，而不会影响工作表上的插入/更新性能。此外，索引可以用完整的页面进行重建，而工作表通常希望具有50％的页面满足和平衡。
Optimize the index structure differently. Archive tables can have more indexes without affecting insert/update performance on the working table. In addition, the indexes can be rebuilt with full pages, while the working table will generally want to have pages that are 50% full and balanced.
优化存储媒体不同。您可以将存档表放在可能具有更多容量的较慢/较便宜的磁盘驱动器上。
Optimize storage media differently. You can put the archive table on slower/less expensive disk drives that maybe have more capacity.
不同地优化备份策略。工作表可能需要热备份或日志传送，而存档表可以使用快照。
Optimize backup strategies differently. Working tables may require hot backups or log shipping while archive tables can use snapshots.
如果您正在使用复制，则可以不同地优化复制。如果归档表仅每天通过夜间批次更新一次，则可以使用快照而不是事务复制。
Optimize replication differently, if you are using it. If an archive table is only updated once per day via nightly batch, you can use snapshot as opposed to transactional replication.
不同级别的访问。也许你想要归档表的不同的安全访问级别。
Different levels of access. Perhaps you want different security access levels for the archive table.
锁定争用。如果你的工作表很热，你宁愿让你的MIS开发人员访问归档表，当他们运行某些东西时，不太可能停止你的操作，并忘记指定脏的读取语义。
Lock contention. If you working table is very hot you'd rather have your MIS developers access the archive table where they are less likely to halt your operations when they run something and forget to specify dirty read semantics.
最佳做法是不使用归档表，而是将数据从OLTP数据库移动到具有非规范化数据的MIS数据库，数据仓库或数据集市。但是一些组织将无法证明额外的DB系统的成本（这并不便宜）。向现有数据库添加附加表的障碍要少得多。
The best practice would not to use archive tables but to move the data from the OLTP database to an MIS database, data warehouse, or data marts with denormalized data. But some organizations will have trouble justifying the cost of an additional DB system (which aren't cheap). There are far fewer hurdles to adding an additional table to an existing DB.
这篇关于SQL数据库最佳实践 - 使用归档表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
