具有庞大数据集的数据库结构的建议

在我看来,这个问题将没有精确的答案,因为需要过于复杂的分析和深入研究我们系统的细节。 我们已经分发了传感器网。信息收集在一个数据库中并进一步处理。 当前的数据库设计是每月分区一个巨大的表。我们尝试将其保持在10亿(通常为6到8亿条记录),因此填充率为每天2千万至5千万条记录。 数据库服务器目前是MS SQL 2008 R2,但我们从2005年开始并在项目开发期间进行升级。 该表本身包含SensorId,MessageTypeId,ReceiveDate和Data字段。目前的解决方案是将传感器数据保存在数据字段(二进制,16字节固定长度)中,并部分解码其类型并将其存储在messageTypeId中。 我们通过传感器发送不同类型的消息类型(电流大约为200),并且可以根据需要进一步增加。 主要处理在应用服务器上完成,该服务器按需获取记录(按类型,sensorId和日期范围),解码并执行所需的处理。当前速度对于这样的数据量是足够的。 我们要求将系统的容量增加10-20倍,我们担心的是我们目前的解决方案能够做到这一点。 我们还有两个想要“优化”结构的想法,我想讨论。 1传感器的数据可以分为多种类型,为了简单起见,我将使用2个主要数据:(值)级别数据(具有值范围的模拟数据),状态数据(固定数值) 因此,我们可以使用以下规则将我们的表重新设计为一堆小表: 对于每个固定类型值(状态类型)使用SensorId和ReceiveDate创建它自己的表(所以我们避免存储类型和二进制blob),所有依赖(扩展)状态将存储在类似外键的自己的表中,所以如果我们有
State
A
B
,并依赖(或附加)状态为
1
2
,我们以表
StateA_1
StateA_2
StateB_1
StateB_2
结束。因此,表名由它代表的固定状态组成。 对于每个模拟数据,我们创建单独的表,它将是类似的第一类型,但是可以包含具有传感器值的附加字段; 优点: 仅存储所需的数据量(当前我们的二进制blob数据包含空间到最长值)和减小的DB大小; 为了获取特定类型的数据,我们得到访问权限表而不是按类型过滤; 缺点: AFAIK,它违反了推荐的做法; 需要框架开发来自动化表管理,因为DBA将手动维护它; 表格数量可能相当大,因为需要全面覆盖可能的值; 因为引入新传感器数据或甚至已定义状态的新状态值,DB模式更改因此可能需要复杂的更改; 复杂的管理导致容易出错; 也许数据库引擎在这样的表格或粒化中插入值? DB结构不固定(不断变化); 可能所有的消息都超过了一些专业人士,但如果我们获得显着的性能提升和/或(不太优选但也有价值)存储空间,我们可能会遵循这种方式。 2可能只是每个传感器的分割表(它将是大约100 000个表)或更好的传感器范围和/或移动到具有专用服务器的不同数据库,但我们希望尽可能避免硬件跨度。 3保持原样。 4切换到不同类型的DBMS,例如面向列的DBMS(HBase和类似的)。 你怎么看?也许你可以为进一步阅读建议资源? 更新: 传感器的一些数据甚至可以在一个月的延迟(通常是1-2周延迟)到达系统的性质,有些总是在线,某种传感器最终有内存和上线。每个传感器消息都有相关的事件引发日期和服务器接收日期,因此我们可以区分最近收集的数据。处理包括一些统计计算,参数偏差检测等。我们构建了汇总报告以便快速查看,但是当我们从传感器更新旧数据(已经处理)获取数据时,我们必须从头开始重建一些报告,因为它们取决于所有可用的无法使用数据和聚合值。所以我们通常会保留3个月的数据以便快速访问和其他存档。我们努力减少存储数据所需的数量,但我们认为我们需要一切来保持结果的准确性。 UPDATE2: 这里有主要数据表。正如我在评论中提到的,我们在“需要速度”期间删除了它的所有依赖关系和约束,因此它仅用于存储。
CREATE TABLE [Messages](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [sourceId] [int] NOT NULL,
    [messageDate] [datetime] NOT NULL,
    [serverDate] [datetime] NOT NULL,
    [messageTypeId] [smallint] NOT NULL,
    [data] [binary](16) NOT NULL
)
其中一台服务器的示例数据:
id      sourceId    messageDate          serverDate messageTypeId   data
1591363304  54  2010-11-20 04:45:36.813 2010-11-20 04:45:39.813 257 0x00000000000000D2ED6F42DDA2F24100

1588602646  195 2010-11-19 10:07:21.247 2010-11-19 10:08:05.993 258 0x02C4ADFB080000CFD6AC00FBFBFBFB4D

1588607651  195 2010-11-19 10:09:43.150 2010-11-19 10:09:43.150 258 0x02E4AD1B280000CCD2A9001B1B1B1B77
    
已邀请:
只是想提出一些想法,希望它们是有用的 - 它们是我正在考虑/思考/研究的一些事情。 分区 - 你提到表是按月分区的。是您自己手动分区,还是使用Enterprise Edition中提供的分区功能?如果是手动,请考虑使用内置的分区功能将数据分区更多,这样可以提高可扩展性/性能。 Kimberly Tripp在MSDN上发表的这篇“Partitioned Tables and Indexes”文章很棒 - 很多很棒的信息在那里,我不会通过释义来做一个不公正的事情!值得考虑的是,每个传感器手动创建1个表,这可能更难以维护/实施,因此增加了复杂性(简单=好)。当然,只有你有企业版。 过滤的索引 - 请查看此MSDN文章 当然还有硬件元素 - 毫无疑问,带有大量RAM /快速磁盘等的多汁服务器将发挥作用。     
一种与数据库无关的技术是切换到记录值的变化 - 每分钟至少有n条记录。因此,例如,如果传感器没有1发送类似的东西:
Id  Date              Value
-----------------------------
1 2010-10-12 11:15:00 100
1 2010-10-12 11:15:02 100
1 2010-10-12 11:15:03 100
1 2010-10-12 11:15:04 105
然后只有第一个和最后一个记录将在DB中结束。为确保传感器处于“实时”状态,每分钟最少输入3条记录。这样可以减少数据量。 不确定这是否有帮助,或者在您的应用程序中是否可行 - 只是一个想法。 编辑 是否可以根据访问概率归档数据?说旧数据比新数据更不可能被访问是否正确?如果是这样,您可能需要看一下Bill Inmon的下一代数据仓库的DW 2.0架构,他讨论了基于不同DW区域(交互式,集成式,近线式,存档式)移动数据的模型。访问概率。访问时间从非常快(交互区)到非常慢(存档)。每个区域都有不同的硬件要求。目标是防止大量数据堵塞DW。     
存储方面,你可能会很好。 SQL Server将处理它。 令我担心的是您的服务器将承担的负载。如果您经常收到交易,那么今天每秒约有400笔交易。将此值提高20倍,您可以查看每秒约8,000笔交易。考虑到您正在报告相同的数据,这不是一个小数字...... 顺便说一句,我是否正确理解您在处理传感器数据时丢弃传感器数据?那么你的总数据集将是“滚动”的10亿行?或者你只是附加数据?     
您可以将日期时间戳存储为整数。我相信datetime标记使用8个字节,整数只在SQL中使用4个。你不得不离开这一年,但由于你按月分区,这可能不是问题。 因此'12 / 25/2010 23:22:59'将被存储为1225232259 -MMDDHHMMSS 只是一个想法...     

要回复问题请先登录注册