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

请关注个人小站：http://sqlhis.com/

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

优秀的表设计，消除重复字段等设计技巧
假设我们设计一个行情表，假设有如下字段，通常的设计可能是表格所示

我们先来说一下数据库的存储结构，SQL SERVER的存储单位是数据页，每个数据页为8192字节。这8192字节不是全部都可以拿来存储数据，每个数据页头部和尾部会占用一定的空间，实际可用的空间大概是8000字节。按我们的111字节一行来算，大概每个数据页能存储72行。(数据页剩余空间存不了一行的话，就浪费掉了)。

.股票代码
深交所的代码显示为“000001.SZ”，上交所代码显示为“600000.SH”，如果按字符串存储，需要占用9个字节。这里完全可以通过映射表解决，股票代码用一个整数类型代替，这个数字类型映射到实际的代码，例如：

提供对外接口的时候，只要提供一个视图，将具体的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个字节的空间

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

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

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的占用空间如下：

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个字节

说了这么多，修改数据类型后情况如下，修改过数据类型后占用空间变为64字节，缩减了43%。

总结：

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

应用数据库的压缩功能

SQL SERVER在2008就开始引入了压缩功能，右键点击表，选择存储，选择管理压缩

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

以下是使用Page压缩后的磁盘占用情况，压缩到只剩下3分之一

建表语句加入压缩如下：

　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)