第四章 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、范式和反范式

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

多使用缓存表和汇总表