第四章 Schema与数据类型优化

1、数据类型的选择

  • 变小的通常更好

例如varchar,占用空间小,不过最好要确定值的范围

  • 简单就好

例如通常数字比字符简单,日期用内置的日期类型

  • 尽量避免NULL

对于MySQL而言更难优化,无法使用索引,更多的储存空间

1.1 整数类型

能确定是正整数的话,尽量用unsigned,整数类型的宽度,如INT(11)是没有意义的,只是给交互工具显示的

1.2 实数

如果需要对小数进行精确计算就使用DECIMAL
场景:财务数据;数据量大时,可以考虑使用BIGINT代替DECIMAL

1.3 字符串类型

VARCHAR

可变长的字符串,节省空间,太长容易产生碎片
场景:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字符数进行储存
策略:只分配真正需要的空间

CHAR

定长字符串,储存短并且固定的字符串效率非常高,而且不会产生碎片
场景:固定长度的列,比如CHAR(1)储存Y、N

BLOB和TEXT

BLOB和TEXT都是为储存很大的数据而设计的字符串类型,分别采用二进制和字符方式储存;当BLOB和TEXT值太大时,InnoDB会使用专门的“外部”储存区域来进行储存,需要1~4个字节储存一个指针

BLOB储存的是二进制数据,没有排序规则和字符集,而TEXT类型有字符集和排序规则,MySQL不能将BLOB和TEXT列全部长度的字符串进行索引;如果对BLOB和TEXT类型字段进行排序会非常慢,最好是别用,如果一定要用那就使用substring后再排序;

ENUM

放弃它吧!

1.4 日期和时间类型

日期时间类型没有代替品,使用看场景
DATETIME

保存范围大,1001年到9999年,精度为秒,以整数储存,格式为YYYYMMDDHHMMSS,与时区无关

TIMESTAMP

范围是1970年到2038年,从“1970-1-1 00:00:00”开始,它和UNIX时间戳相同;默认是NOT NULL;通常应该尽量使用TIMESTAMP空间效率比DATETIME高;与时区有关;尽量在MySQL连接串上定义时区参数;

1.5 位数据类型

也放弃它吧!

1.6 选择标识符(ID)

优先使用整数

字符串类型
避免使用MD5、SHA1、UUID生成的字符串作为主键,不然INSERT语句会变慢,页分裂、磁盘随机访问;SELECT也会变慢;如果一定要出差UUID,应移除“-”,更好的做法是,用UNHEX()函数转换UUID值为16字节的数字

2、MySQL schema设计中的陷阱

  • 太多的列

MySQL储存引擎API工作时需要在服务器层和储存引擎之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列

  • 太多的关联

MySQL限制每个关联操作最多只能有61张表,最好是12个表以内做关联

  • 使用枚举

放弃它吧!

3、范式和反范式

!!!极端的做法只有在实验室中存在!!!

多使用缓存表汇总表

推荐阅读更多精彩内容