在上亿级记录的表结构设计时,每节省一个字节都会节省大量的存储空间,而存储就是钱啊,所以设计这种类型的表,必须以最偏执的态度去对待,不冗余任何信息;但已经存在于业务系统中的巨型表结构该如何优化呢?
如下面这个表结构,记录数在亿级,存储空间需要30g左右(含索引)该如何优化该表结构呢?
create table tbf_user_logs
(
id number(11,0) not null enable,
trace_id varchar2(32) enable,
trace_ip varchar2(15) enable,
create_date date,
user_count number(4,0),
user_id_list clob,
user_nick varchar2(32),
user_nick_list clob,
user_key varchar2(32),
addr_mac varchar2(32),
constraint pk_user_logs_id primary key (id) using index tablespace tbs_userlog_ind
);
复制首先
发现user_id_list与user_nick_list都不可能超过1k,所以没有必要用clob,如果改成varchar2(1000)的话,至少可以节省40个字节,
因为每个clob字段即是存一个字节,也需要20个字节来管理这些数据,如果clob比较大的话,元数据最大可以达到72字节.
其次
user_id_list与user_nick_list是有一对一的冗余关系,所以可以去掉其中的一个,如果去掉user_id_nick的话,将来可能要与用户表进行关联,求出nick,所以直接去掉user_id_list好啦,这样平均下来这个字段最少可以节省32个字节,因为用户ID是guid,用字符存储的话,必须32字节.
user_key与user_nick是一对一的冗余关系,同上,去掉user_key可以节省32字节.
第三
分析user_nick_list与user_nick的关系,发现user_nick_list中总冗余了最后一次登陆用户的nick,如果user_Nick_list去掉最后一次登陆用户的nick,数据整样很完整,最后一次登陆用户的nick存储在user_nick,这样平均每行节省10个字节.
第四
由于trace_id字段是md5算法产生的,其实也是guid,如果用varchar2来存储,必须32个字节,如果用raw(16进制)来存储的话,只需要16字节,这样每行又节省16字节.
修改后的表结构:
create table tbf_user_logs
(
id number(11,0) ,
trace_id raw(16) ,
trace_ip varchar2(15),
create_date date,
user_count number(4,0),
user_id_list varchar2(1000), --不再使用
user_nick varchar2(32),
user_nick_list varchar2(1000),
user_key varchar2(32), --不再使用
addr_mac varchar2(32),
constraint pk_user_logs_id primary key (id) using index tablespace tbs_userlog_ind
);
复制总结:
通过以上面个方面,共节省40+64+10+16=130字节,每行最可以节省130字节,按1亿记录来算,光数据存储空间就节省了12g以上,如果再加上索引节省的空间,大概节省15g左右,每年下来,节省的空间就不止15g,节省的存储也值一大笔钱啊.
再加上做成分区或者历史表,把数据到了生命周期的迁移到压缩表空间里去,又节约了大笔存储空间!
很多时候节约存储空间,并不能得到上级领导的赞许,说白了没有成就感,成绩!
但是通过节约方式,可以降低对存储的要求,也就是降低了IO的要求,必然带来降低了内存的要求,也降低了CPU工作时间.
