8.数据库存储(LEVELI&II)的难点:节省你的磁盘

原文链接http://sqlhis.com/index.php/2020/05/15/8-数据库存储leveliii的难点:节省你的磁盘/

对于高频数据的存储,想节省磁盘空间,可以从两个方面入手:
.优秀的表设计,消除重复字段等设计技巧
.应用数据库的压缩功能

优秀的表设计,消除重复字段等设计技巧
假设我们设计一个行情表,假设有如下字段,通常的设计可能是表格所示
8.数据库存储(LEVELI&II)的难点:节省你的磁盘
我们先来说一下数据库的存储结构,SQL SERVER的存储单位是数据页,每个数据页为8192字节。这8192字节不是全部都可以拿来存储数据,每个数据页头部和尾部会占用一定的空间,实际可用的空间大概是8000字节。按我们的111字节一行来算,大概每个数据页能存储72行。(数据页剩余空间存不了一行的话,就浪费掉了)。

.股票代码
深交所的代码显示为“000001.SZ”,上交所代码显示为“600000.SH”,如果按字符串存储,需要占用9个字节。这里完全可以通过映射表解决,股票代码用一个整数类型代替,这个数字类型映射到实际的代码,例如:
8.数据库存储(LEVELI&II)的难点:节省你的磁盘
提供对外接口的时候,只要提供一个视图,将具体的ID映射到股票代码即可。

SQL SERVER 有以下整数类型:
【bigint】

从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据(所有数字)。存储大小为 8 个字节。

【int】

从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据(所有数字)。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。

【smallint】

从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据。存储大小为 2 个字节。

【tinyint】

从 0 到 255 的整型数据。存储大小为 1 字节。

深沪交易所的证券代码大约都是在10000以上,如果使用smallint类型的话,算上负数大概只有6万多,容量不够,所以最终使用int类型,占用4个字节,只算正数都可以容纳20多亿。

.股票名称
股票名称由于是中文字符,如果按4个字符算的话,就占用了8个字节,股票名称是不应该存储到行情表中的,股票名称在一天之内不会变化,那股票名称直接就放到前面的股票代码映射表中是否可以?

答案不可以,因为沪深交易所的股票名称虽然在一天之内不会发生变化,但是在不同的日期还是会变化的,为了存储这些变化,需要新建一张拉链表。

8.数据库存储(LEVELI&II)的难点:节省你的磁盘
最终实现后,股票名称无需再行情表中存储,又节省了8个字节的空间

.交易日期
上面的设计,交易日期使用datetime存储,占用8个字节。首先,确认一下实际需要的精度,这里不需要具体的时间,只需要日期。所以有好几个类型都可以使用

8.数据库存储(LEVELI&II)的难点:节省你的磁盘

用数字表示日期也是一种常用的做法,

INT类型的范围只看正数,从0~2,147,483,647,足够覆盖00000000到99991231

SMALLINT的范围只看正数,从0~32767,可以覆盖89年,算上负数的话可以覆盖178年,理论上也是够了,比较麻烦的这个列是索引列,假设我们按从19900101日开始,每天+1这样存储,在查询的时候,需要将日期转换到存储的数字。有可能转换起来比较麻烦,或者会增加代码量,增加使用者的负担。

最终建议使用DATE类型进行存储,使用INT类型也是可以的。如果要用SMALLINT,需要好好考虑如何写转换视图,让转换视图可以充分的利用索引。

.交易时间
上面的设计,直接使用了time类型,time类型如果不带参数,默认是5个字节

实际上交易所返回的时间是精确到毫秒,所以如果要使用time类型,正确的方式是使用time(3),

如果不使用time类型,使用int类型也是可以的,交易时间的范围是92500000~150000000,占用字节数为4

使用int类型的好处是进行范围搜索的时候,直接如下代码(10点到10点01分之间的数据)

where TradeTime between 100000000 and 100100000

使用time就要麻烦一些,至少从代码角度看,瞧这个代码比较费时

where TradeTime between ‘10:00:00.000’ and ‘10:01:00.000’

成交价

成交价和后面的数值全部使用了DECIMAL (19,4),decimal的占用空间如下:

8.数据库存储(LEVELI&II)的难点:节省你的磁盘

DECIMAL(19,4) 这里的19是精度,4是小数位数

也就是可以存储从-999999999999999.9999到999999999999999.9999,整数部分15位,已经非常大了。

从上面的表格可以看到,精度10-19都是9个字节,也就是说DECIMAL(10,4)和DECILAM(19,4)占用的空间是相同的,所以当然选DECIMAL(19,4).

有了以上基础,分析一下成交价格的范围。成交价格从0.0001到9999.9999应该足够了,所以完全可以选择DECIMAL(9,4) 占用5个字节。

除此以外,完全可以选择使用INT存储,只占用4个字节,存储的时候将数据*10000,输出的时候在视图里面将数值除以10000即可。

成交量和成交额

成交量和成交金额本身会比较大,单日超过100亿也是正常的。DECIMAL(19,4)整数部分还有15位,足够大了。

当然我们还可以使用BIGINT,BIGINT占用8个字节,可以存19位数,足够大了。

成交量必然是整数,成交额=成交价单价,单价有小数,这里可以在存储的时候100,然后展示的时候在视图里面除以100j即可。

还有一个问题,高频数据实际上用户定性的分析,精度其实没必要像财务系统那样锱铢必较,对于成交量这种数据,完全也可以使用float和real存储,其中使用real存储的话,只占4个字节

8.数据库存储(LEVELI&II)的难点:节省你的磁盘
说了这么多,修改数据类型后情况如下,修改过数据类型后占用空间变为64字节,缩减了43%。

8.数据库存储(LEVELI&II)的难点:节省你的磁盘

总结

对于这种大数据量表,要特别重视表设计,想进办法节省存储空间,优秀的设计和一般的设计,占用空间可以直接差1倍。当然,也不能为了存储和忽略易用性,主键的存储类型要非常慎重,涉及查询能不能有效利用索引的问题。

应用数据库的压缩功能

SQL SERVER在2008就开始引入了压缩功能,右键点击表,选择存储,选择管理压缩
8.数据库存储(LEVELI&II)的难点:节省你的磁盘

弹出窗口可以选择压缩类型,使用Page压缩。

8.数据库存储(LEVELI&II)的难点:节省你的磁盘

以下是使用Page压缩后的磁盘占用情况,压缩到只剩下3分之一
8.数据库存储(LEVELI&II)的难点:节省你的磁盘

建表语句加入压缩如下:

 CREATE TABLE TickData(
             SecId             INT         NOT NULL,                       --股票编码
             BizDate           INT         NOT NULL,                       --交易日期
             TradeTime         INT         NOT NULL,                       --交易时间
             RN                            TINYINT NOT NULL,               --相同时间排序
             Price             INT          NULL,                          --成交价
             CONSTRAINT PK_TickData PRIMARY KEY CLUSTERED
             (SecId,BizDate,TradeTime,RN)
)WITH(DATA_COMPRESSION = PAGE )
GO

使用脚本压缩未压缩的表
ALTER TABLE [dbo].[TickData] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

8.数据库存储(LEVELI&II)的难点:节省你的磁盘

上一篇:【赵强老师】什么是PL/SQL?


下一篇:微服务实践 SpringBoot+Thrift+MyBatis+MySql+Redis