数据选择
- 更小的通常更好:占用的磁盘、内存、CPU更少;
- 简单就好,如整型比字符操作代价更低;
- 尽量避免NULL
- 尽量使用相同的数据类型存储相似或相关的值,尤其是关联条件使用的列。
具体说明
整型:TINTINT SMALLINT MEDIUMINT INT BIGINT分别使用8,16,24,32,64位内存。对于存储和计算来说,指定大小是没有意义的。
实数类型:DECIMAL FLOAT DOUBLE :DECIMAL支持精确计算,占用空间较大,相对而言,FLOAT 和DOUBLE占用的空间较小,在对精度没有要求的时候建议使用浮点类型。可以使用BIGINT代替DECIMAL,例如一个数据要求精确到万分之一,可以将数字乘以10000,以BIGINT形式存储,既避免了浮点精度不高的缺点,也避免了DECIMAL精确计算大家大的问题。
VARCHAR CHAR类型:VARCHAR是可变长字符串,比定长节省空间。但需要额外的1个字节(长度小于255)或2个字节来记录字符串长度。且UPDATE时可能会有额外的工作;CHAR是定长,存储时会删除末尾空格,适合存储长度相似或较短的字符串。
BLOB TEXT 类型:两者之间的区别是BLOB存储的是二进制数据,而TEXT是以字符的方式存储。当数据较大时,InnoDB会使用专门的"外部"存储区域来存储,此时行内需要1-4个字节存储指针。
枚举ENUM:有时可以使用枚举列代替常用的字符串类型。枚举类在MYSQL内部以整数形式保存,并且在表的.frm文件中保存“数字-字符串”映射表供查询。
日期和时间类型: DATETIME精度为秒,格式YYYYMMDDHHmmss,用8个字节存储;TIMESTAMP保存以秒为精度的时间戳,占用4个字节存储。尽量使用TIMESTAMP。
位数据:BIT SET 都是字符串类型。SET可以保存很多个true/false值。如CREATE TABLE myset (col SET(‘a’, ‘b’,‘c’, ‘d’)); 则a = 0001=1 , b=0010=2,c=0100=4,d=1000=8; 查询语句SELECT * FROM tbl_name WHERE set_col =‘val1,val2’;可以使用整型TINYINT代替SET。BIT要尽量避免使用。
标识列的选择:尽量使用整数类型字段作为标识列。
Schame设计注意
- 不要使用过多的列
- 不要过多地关联表
- 枚举的使用不能过度
- 在必要时可以使用NULL而不是代替值。
范式和反范式
范式
每个事实数据会出现并且只出现一次
优点:
- 更新操作更快
- 因为范式化数据时,重复的数据很少甚至没有,所以只需要更少的数据
- 范式化表通常比较小,可以更好的放在内存中,且执行速度更快
- 很少的数据冗余意味着查询时只需要更少的DISTINCT或者GROUP BY
缺点 :通常需要关联
反范式
所有的数据集中在一张表中,可以很好地避免关联。
建议:混用范式化和反范式化