我有一个存储大量交易数据的交易表。只有一个月的最新数据才能实时用于交易用途。较新的数据只能用于报告生成或离线分析。
处理此类交易数据的最佳做法是什么?
我是否应该创建一个具有相同列的历史表并将年龄超过一个月的数据从事务表移动到具有批处理作业的历史表?我应该每年创建一个历史表吗?还是只使用一个历史表来托管所有过去的数据?
或者我应该使用数据库分区而不是历史表吗?
或者我应该创建一个数据仓库并将数据移动到其中?
我有一个存储大量交易数据的交易表。只有一个月的最新数据才能实时用于交易用途。较新的数据只能用于报告生成或离线分析。
处理此类交易数据的最佳做法是什么?
我是否应该创建一个具有相同列的历史表并将年龄超过一个月的数据从事务表移动到具有批处理作业的历史表?我应该每年创建一个历史表吗?还是只使用一个历史表来托管所有过去的数据?
或者我应该使用数据库分区而不是历史表吗?
或者我应该创建一个数据仓库并将数据移动到其中?
您应该指定您使用的数据库系统和版本,因为这将影响您对如何构建和管理数据的实际选择。
您在上下文中提到大意味着每天有 100,000 条新记录,所以让我们计划一个更大的数量级,每天 100 万条记录。一个月内有 3000 万条新记录,一年内约有 3.6 亿条记录。虽然 3.6 亿条记录开始变得有些庞大,但在大多数现代数据库系统中,这绝不是无法管理或需要特殊处理的事情。
这将取决于您正在运行的实际查询、写入与读取的服务器的并发性以及报告数据的可接受运行时间,但一年中3.6亿条记录不应该用现代RDBMS吓到您。我管理过包含数十亿条记录的表,并直接从它们中报告出来,通常在相当适中的硬件上在 10 秒内一次查询大约 100 万条记录。
如果您想减少报告的并发性,那么您可以考虑归档较旧的数据(即任何早于最后一个月的数据)或将其迁移到数据仓库中。根据正在执行的查询类型和您使用的数据库系统,您可能拥有列存储索引和过滤索引等可用功能,这些功能可以帮助您直接从表本身改进报告,而无需存档。
正确地构建架构和索引数据将在提高报告能力方面发挥最大作用。
最后,正如 Jonathan Fite 所提到的,分区不会直接提高您的查询性能,但它可以用来改善您对表的管理,因为您可以智能地将数据划分为分区,从而允许您管理不太活跃的数据,同时管理更活跃的数据分区正在使用中。当您需要执行诸如归档数据(通过分区交换)或索引维护之类的事情时,您会看到降低并发性的改进。
如果您对每天 100,000 行的估计是准确的,并且您预计未来几年实际上不会有任何有意义的增长,那么您每年只看到 3600 万行。那时我什至不会考虑分区或归档。对架构良好的模式进行常规索引应该为您提供非常快速的查询运行时。但我个人喜欢计划比我实际预期的更多的数据,就像上面一样。