.net - 在sql server中管理大数据

Question

我们团队面临一个查询大量数据的问题，sql server blocks inserting into table。

我们正在处理一个涉及保存和查询大量数据的 .NET 项目。该项目包含两个数据库，一个是实时数据库，另一个是历史数据库，都是 SQL Server 数据库，它们位于不同的机器上。两个数据库的表结构完全一样，只有四张表。

Realtime DB包含少量的实时数据，里面的数据是不断更新的。历史数据库包含所有更新历史。当项目运行时，它会将更新查询发送到实时数据库，并将查询插入历史数据库。每天将在历史数据库上执行大约 200 万次插入。查询是异步执行的。

历史数据库还将用于数据检索和报告目的。人们将在服务器上运行查询，他们将运行什么查询是我们无法控制的。我们现在面临的问题是，当一个返回很多行的查询运行时，连接池会在短时间内被占满，导致连接超时，导致数据丢失。

我们试过调整表的索引，增加连接池的最大大小和增加超时时间，但都不能从根本上解决问题。在生产中，该项目将运行 5 年，届时数据库中将有 40 亿行。

我想我真正的问题是人们通常如何在 SQL Server 中处理大数据，如何在具有数百万或数十亿行的表上同时管理插入和选择。

Answer 1

您的架构存在一个基本错误，即它没有将历史数据库视为实时数据库。它实际上是，尽管它的名字，因为数据是实时插入的。随心所欲地调用它并拆分插入和更新，但您仍然需要对当前体系结构进行修复。

要解决此问题，您可以从概念上添加/重新配置第三个数据库，这将临时解耦插入。您可以创建一个作业(例如 SSIS 包)，在非高峰时间将行批量插入数据库，而不是实时提供历史数据库。这可以是一天一次，比如凌晨 2 点，或者一天中的多次。这取决于您的业务。假设非高峰传输和查询发生在不同的时间，定期批量加载将允许快速批量传输，同时不会减慢对历史数据执行的查询。权衡是您的历史数据不是秒级的，但这可能已经足够了。当然，您需要将实时插入存储在传输之间的某个位置。这就是我提到第三个数据库的原因，但您可以简单地将临时存储折叠到实时数据库中，而不会影响后端用户。

这是经常做的事情，在野外直接回答你的最后一个问题。您通过一些试图避开事务处理和查询的周期传输过程将事务处理数据库(实时数据库)与分析处理(历史、OLAP、数据仓库等)分开，通常通过一些计划任务。您还可以使用队列系统(例如 MSMQ、RabbitMQ 等)作为实时数据库和历史数据库之间的中间存储。这将分离两个数据库，同时还允许更接近实时的历史数据查询可用性。

如果计划的批量传输或队列不是一个可行的选项，您可以去规范化。弄清楚正在收集哪些数据以及如何聚合这些数据，并专门为这些查询创建非规范化表。

祝你好运。