中小型数据存储方案探讨

中型数据:客户端200~500台以下,单表记录200~500万条以下,数据库总大小200G以下。

小型数据:客户端20~50台以下,单表记录20~50万条以下,数据库总大小20G以下。

程序一旦涉及大规模、并发性、实时性,技术难点就出来了。这里的小型数据,特指对实时性和并发性有较高要求的小型数据存储,否则就没必要讨论小型数据了。

       常用的数据库存储方案有Oracle、DB2、SQL Server、Access、MySQL、SQLite、文件(二进制文件、文本文件、INI文件)。

       各类数据库的功能支持请参考:http://zh.wikipedia.org/wiki/%E5%85%B3%E7%B3%BB%E5%9E%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AE%A1%E7%90%86%E7%B3%BB%E7%BB%9F%E6%AF%94%E8%BE%83。本人理解:DB2非常好的大型数据库,Oracle大中型数据库偏大型,MS SQL Server大中型偏中,Access桌面级、MySQL网页级,SQLite嵌入级,文件属于原始级。

如果是大型数据,数据库知识扎实不需要经常找学习参考资料的,选择DB2;否则选择学习资料多的Oracle。这两个款数据库,是顶尖的专业数据库,值得信赖。如果是中型数据,选择Oracle,最好不好选择SQL Server,因为MS做数据库不够专业,而且不跨平台,性能、稳定性等不如Oracle。如果是中小数据,Web数据库用MySQL,C/S数据库用SQL Server,如果没有网络直接访问,而是通过服务器访问本地数据库,而且并发性、实时性、稳定性要求高,用SQLite+二进制文件。如果是小型数据库,或者数据关系复杂,用二进制文件。很多视频或图片数据是以独立文件存储,而数据库仅存文件链接路径。

      

数据存储要求最重要的几点如下:

1.    数据类型支持;

2.    数据完整,而数据量最小,有便于数据操作;

3.    并发性;

4.    实时性;

5.    关联查询速度快;

6.    统计分析;

7.    触发器、存储器等支持;

8.    数据备份、恢复。

9.    网络支持;

10. 稳定性;

11. 跨平台性。

  有跨平台要求的,就排除微软产品。稳定性,所有的数据库基本没问题,如果非要有,微软产品稍微差一点点,因为他不够专业、不跨平台、不开源,所以经历的环境、用户不够丰富,正如他的操作系统,即使用户量大,但是他的稳定性、实时性等并不是很好的。如需要网络支持,选择大中型数据库。数据备份大型数据库有工具管理,小数据库可直接文件拷贝备份。触发器、存储器、复杂的统计分析等都是大型数据库才具备的功能。一般情况,前面5项才是数据库必须具备而且最注重的功能。第1项,很多关系型数据库支持的不够,第3项很多轻量级数据库在支持的不够,第4项SQL Server和Access支持的不够。第2项是数据库设计的原则。

  数据类型支持和实时性是本文特色,并发性是数据库服务器有一定要求,关联查询提升查询速度,关系数据库都支持。如果对触发器、存储器、复杂的统计分析等有过高要求,本人建议充分利用关系型数据库的优势和二进制文件的优势。

案例需求:数据量,中型,要求并发性和实时性,不需要触发器和过于强大统计分析功能。有多张表关联查询需求,尽量减少数据冗余。

场景:对某一模拟量设备实时测量,1秒一个float点值,1天下来就86400条记录。(100个设备,每个设备一天86400条值,存储30天)

 

方案1:按照传统的关系数据库设计,两张表。

       表1:设备表Tb_Device

设备序号

设备名称

设备类型

设备用途

所属部门

1

AK1-6

道岔

火车轨道控制

工务部…

···

···

···

···

···

       表2:设备测量历史Tb_History

测量序号

测量值

测量时间

被测设备(外键)

1

120.63

2013-06-27 10:03:58

1

···

···

···

···

86400

38.165

2013-06-28 10:03:58

1

···

···

···

···

       上面2张表严格按照关系数据库范式设计的,但是表2有两个不足。第一、被测设备(外键),其每86400条记录的内容一致,数据冗余之嫌;第二、一天1个设备就86400条记录,那么上百个设备一天就近千万条记录,30天呢?数据表过大,插入、查询、统计等操作效率可以想象。

       改进步骤1

表2_1:设备测量历史Tb_History_1

测量序号

测量值

测量时间

被测设备(外键)

1

120.63

2013-06-27 10:03:58

1

···

···

···

86400

38.165

2013-06-28 10:03:58

···

···

···

···

       把86400条记录的重复的被测设备值合并成一个值。当然,这已经不是关系数据库了,而且更重要的第二个缺陷没解决。超大数据量不利于数据库操作,特别是中小数据库系统。解决办法是分割数据文件,把表记录条数控制在200万以下。按时间或设备分割,具体分割策略得根据实际的数据操作来决定,以达到最好的性能。分割实现方法最少有以下两种:一、关系数据表按照日期及设备每天动态创建一张表存放当天数据;二、利用二进制文件存储数据,按照日期及设备创建存储文件。数据操作的时候,先确定应该操作那张数据表或哪个二进制数据文件,一旦数据分割不但极大减少了一个文件或表的数据量,而且带来了另一个极大的益处——不同数据表或文件有利于并发读写操作。

       改进步骤2

对于第二个缺陷,有两种方式解决,方法一如下:

表2_2_1:设备测量历史Tb_History_xx_xxxx

测量序号

测量值

测量时间

被测设备(外键)

1

120.63

2013-06-27 10:03:58

1(取消了)

···

···

···

86400

38.165

2013-06-28 10:03:58

  按照日期和设备名称不同,分割表,每1天为每1个设备创建一张数据表,如此,一张表存放86400条记录。例如设备AK46在2013年06月12日的表明可以取名为Tb_History_AK46_20130612。数据表操作时,先根据日期及设备名称,确定数据表,然后操作数据表数据。如此可以解决数据库操作并发性和实时性等问题。100个设备30天会创建3000张表。

如果有统计需求,比如要求每一个设备每一天有一个统计最大值、最小值、平均值。那么可以设计一张设备测量值日报表,如下所示:

表3:设备测量日报表Tb_Daily

日报表序号

设备序号(外键)

最大值/时间

最小值/时间

平均值

1

1

206.4/2013-06-02 21:33:08

36.02/2013-06-02 4:17:26

112.19

2

4

230.76/2013-06-02 13:15:03

163.3/2013-06-02 20:16:05

200.138

···

···

···

···

···

       此表,一个设备一天一条记录,100个设备30天才3000条记录。

 

对于第二个缺陷,方法二如下:

       采用二进制文件存储设备测量历史数据,为每一个设备每一天创建一个文件,数据操作时先根据设备名及时间找到对应是数据文件,再操作数据,其优缺点与上表2_2_1差不多。如需要统计信息,参考上日报表设计。其定义就是一个文件概要信息和索引组成的头信息,加上循环存储数据记录。

二进制文件 = 文件头 + 数据内容

文件头 = 创建时间(4B)+设备信息(可大可小)+一条记录大小(4B)+总记录数(4B)+当前记录号(4B)

一条数据记录 = 时间(4B)+ 值(4B)

 

       用这两种方法分割数据表,100个设备30天会创建3000张表。数据表过多可能导致衍生出表的管理额外工作量,或者有些数据库系统不支持过多的用户表。更重要的是,如此设计数据表,可能对有些数据操作带来很大不便,例如需要查询的不是某个时间段某个设备的测量历史数据,而是查询某时刻所有设备的测量数据。这样的查询需求就需要去每个设备表(100个表)查询某一时刻返回一个结果,然后再拼接最后查询响应。效率可以想象。要这两种查询都能很好的响应,设计存储数据方案如下。

 

       改进步骤3

       采用二进制文件存储,按照日期每天创建一个二进制文件,文件定义如上定义,只是改进每条记录的定义。

                     一条数据记录 = { 时间(4B)+ 值(4B)}

       100个设备,那么一条数据记录就是100个{ 时间(4B)+ 值(4B)},共800 Byte。一天86400条,共800*86400=69120000,约69M。如此定义,可以很好满足上述两种查询。如果担心69M文件过大,不利于文件操作的性能,可按照设备类型归类各种设备等手段分割文件,缩小文件大小。每条记录中的100个设备8字节数据排列顺序可以按照设备列表数据定义顺序排序,也可以在文件头定义一个设备顺序。特别提醒,如果查询某一个设备某一段时间数据,检索每一秒钟的数据记录时,没必要去一条完整数据800B,可以直接根据设备顺序,在二进制位文件里取指定偏移的8B数据。

 

       改进步骤3有一个问题,作为纯粹的文件结构,其数据检索肯定没有专门的数据库系统效率高,我们可以通过文件头索引部分来弥补,但是数据库的跨表关联查询,是文件系统无法弥补的。如果设备表的设备类型、所属部门可能都是外键,数据库系统很容易关联跨表查询,而文件系统只能读设备信息文件、设备类型定义文件、部门定义文件等多次磁盘文件,效率可以想象。所以我们可以充分利用关系数据库的数据完整性、关联性、高效索引的特性,利用二进制文件的数据组织形式自定义性、数据分割带来的并发性和实时性的优势结合,构建性能好、并发强、实时性高、稳定性强、数据组织结构定义丰富、数据最大限度完整而又不冗余的数据存储方案:SQLite+二进制文件。

原文地址:https://www.cnblogs.com/fyhui/p/3193335.html