SQL 数据库最佳实践 - 使用存档表? [英] SQL Database Best Practices - Use of Archive tables?

查看:50
本文介绍了SQL 数据库最佳实践 - 使用存档表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我不是受过训练的 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 标志的布尔值?
  • 这会在什么时候提高性能?
  • 鉴于可能仍需要查询数据(或与当前数据结合),正确构建此结构的最佳模式是什么?
  • 对此还有什么要说的?

推荐答案

归档的概念是一种物理概念,而非逻辑概念.从逻辑上讲,归档表包含完全相同的实体,应该是同一个表.

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:

  1. 以不同方式优化索引结构.归档表可以有更多索引而不影响工作表上的插入/更新性能.此外,索引可以用完整的页面重建,而工作表通常希望有 50% 完整和平衡的页面.

  1. 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 数据库、数据仓库或具有非规范化数据的数据集市.但是一些组织很难证明额外的数据库系统的成本(并不便宜).向现有数据库添加额外表的障碍要少得多.

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆