数据库设计 – 数十亿行数据的最佳数据库和表格设计[已关闭]

我正在编写一个需要存储和分析大量电气和温度数据的应用程序.

基本上我需要在过去几年中存储大量的每小时用电量测量值,并且需要在数十万个位置存储大量的每小时用电量,然后以非常复杂的方式分析数据.

我需要存储的信息(目前)是位置ID,时间戳(日期和时间),温度和电力使用情况.

关于需要存储的数据量,这是一个近似值,但是沿着这些方向：
20 000个地点,每月720个记录(每小时测量,每月约720个小时),120个月(10年后)和未来许多年.简单计算得出以下结果：

20 000 locations x 720 records x 120 months (10 years back) = 1 728 000 000 records.

这些是过去的记录,每月将导入新记录,因此每月约有20 000 x 720 = 14 400 000条新记录.

总的位置也将稳步增长.

在所有这些数据上,需要执行以下操作：

>检索特定日期和时间段的数据：日期01.01.2013和01.01.2017之间以及07:00和13:00之间某个位置ID的所有记录.
>对特定日期和时间范围的简单数学运算,例如,在07:00至13:00之间,某个位置ID的MIN,MAX和AVG温度和电力使用时间为5年.

数据将按月编写,但将由数百名用户(至少)不断阅读,因此读取速度更为重要.

我没有NoSQL数据库的经验,但从我收集的数据来看,它们是在这里使用的最佳解决方案.
我已经阅读了最流行的NoSQL数据库,但由于它们完全不同并且允许非常不同的表架构,我还无法确定最佳数据库使用的是什么.

我的主要选择是Cassandra和MongoDB,但是由于我的知识非常有限,而且在大数据和NoSQL方面没有真正的经验我不太确定.我还读到Po??streSQL也能很好地处理这么多数据.

我的问题如下：

>我应该使用NoSQL数据库来处理如此大量的数据.如果没有,我可以坚持MySQL吗？
>我应该使用什么数据库？
>我应该将日期和时间保存在单独的索引(如果可能)列中,以便在特定时间和日期期间快速检索和处理数据,还是可以通过将时间戳保留在单个列中来完成？
>这里是一个时间序列数据建模方法,如果没有,你能给我一个好桌子设计的指针吗？

谢谢.

解决方法

>不要使用NoSQL.数据结构高度适合
关系数据库完美.
>我个人使用SQL Server 2016,我在该数据量中应用计算没有问题.它最初是在一个
PostgreSQL实例,当我开始我的工作,它无法处理
小型AWS实例上的数据量.
>我强烈建议提取日期的小时部分并将其与日期本身分开存储.相信我,从我的错误中吸取教训！
>我按列表(DATE,TIME,DATAPOINT_ID,VALUE)存储大部分数据,但这不是人们想要解释数据的方式.准备好针对数据和大量的数据透视进行一些可怕的查询.不要害怕为结果集创建一个非规范化表,这些结果集太大而无法动态计算.

一般提示：我将大部分数据存储在两个数据库之间,第一个是直接时间序列数据并进行了规范化.我的第二个数据库非常规范化并包含预先聚合的数据.与我的系统一样快,我并不是因为用户甚至不想等待30秒才能加载报告这一事实 – 即使我个人认为30秒内处理2 TB的数据也非常快.

要详细说明为什么我建议将小时与日期分开存储,以下是我这样做的几个原因：

>电子数据的呈现方式是按小时结束 – 因此,01：00实际上是前一小时的电力平均值,00：00是小时结束24小时.(这很重要,因为您实际上必须搜索两个日期包括24小时值 – 您要查找的日期加上第二天的第一个标记.)但是,天气数据实际上是以前向方式显示的(实际和下一小时的预测值).根据我对这些数据的经验,消费者希望分析天气对电价/需求的影响.如果您使用直接日期比较,您实际上将比较前一小时的平均价格与下一小时的平均温度,即使时间戳相同.将日期与日期分开存储允许您将转换应用于时间,而不会将性能影响应用于将计算应用于DATETIME列.
>表现.我想说我生成的报告中至少有90％是图表,通常用单个日期或一系列日期来计算小时的价格.必须从日期中拆分时间可能会降低用于生成报告的查询的速度,具体取决于您要查看的日期范围.消费者想要看到过去30年的同一日期(实际上天气需要产生30年法线)并不少见 – 这可能很慢.当然你可以优化你的查询并添加索引,并相信我有一些疯狂的索引,我宁愿没有,但它使系统运行得很快.
>生产力.我讨厌不止一次写同一段代码.我曾经将日期和时间存储在同一列中,直到我不得不一遍又一遍地写相同的查询来提取时间部分.过了一会儿,我厌倦了不得不这样做并把它提取到自己的专栏.您编写的代码越少,其中出现错误的可能性就越小.此外,必须编写更少的代码意味着您可以更快地获取报告,没有人希望整天等待报告.
>最终用户.并非所有最终用户都是超级用户(即知道如何编写SQL).将数据存储为可以轻松地将其带入Excel(或其他类似工具)的格式将使您成为办公室中的英雄.如果用户无法轻松访问或操作数据,他们将无法使用您的系统.相信我,几年前我设计了完美的系统,没有人因为这个原因而使用它.数据库设计不仅仅是遵守一组预定义的规则/指南,而是关于使系统可用.

正如我上面所说的,这完全取决于我的个人经历,让我告诉你,经历了几年艰苦的工作并且进行了大量的重新设计.不要做我做的事情,从错误中吸取教训并确保在决定数据库时让系统的最终用户(或开发人员,报告作者等……)参与进来.

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!