MySQL之SQL优化篇（四）：schema及数据类型优化

MySQL之SQL优化篇（四）：schema及数据类型优化

  高效的SQL背后离不开合理的数据库设计。

上一节：MySQL之SQL优化篇（三）：浅谈MySQL存储引擎

表结构设计

建议	说明
不要设计太多的列	MySQL存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，如果列太多，则会导致缓存和转换的代价变高，带来的是性能下降
不要过度设计，导致太多的关联	MySQL限制每个关联操作最多使用61张表，经验法则：单个查询最好在12个表以内做关联
范式化和反范式化的取舍	范式化意味着数据很少或者没有重复数据，对于修改和删除来说，通常只需要操作很少的数据，由于数据重复量少，很少或者不用使用group by或者distinct等语法，但是，范式化对于查询操作来说，通常需要关联多张表来查询，这相比于反范式化的数据来说，有时会更慢。反范式化存储的数据重复量大、需要group by或者distinct的操作多、在更新和删除时需要维护的表多，但是查询的时候很少需要关联，在一张表里设置索引之后可以写出比范式化的关联查询效率更高的SQL。因此，可以权衡二者的优缺点，混用范式化和反范式化，使用触发器可以很好地维护多张表的数据

表字段类型设计

MySQL字段类型介绍及其Java类型对照

参考链接

针对字段类型选择的一些有用的建议

建议	说明
更小的通常更好	在能满足功能需求的情况下，为未来可能的数据增长留足空间，在此基础上，字段类型越小越好；如果没有负数，最好使用 unsigned，如 int unsigned
简单就好	在很多数据库设计中，最常见的数据类型为 varchar ，只要能用 varchar 表示的为了省事基本上都是 varchar ，这也是本人最为反感的一种做法。殊不知，计算机对于数字类型的处理有着天然的优势，字符型与数字类型相比，不仅要处理字符校验规则，而且在存储上也不占优势。因此，不要为了省事而滥用 varchar ，应该根据数据具体情况进行分析，选取简单的类型
尽量避免null	在不指定列的 null 值情况时，默认是可为 null 。在一个可为 null 的列上建立索引，MySQL需要单独处理 null 值，增加索引的复杂性。在写SQL时需要单独考虑 null 值，可能需要增加or条件。有时候会把关于 null 的判断写成 = null，但这样无法匹配到为 null 值的数据，应该用 is null 或者 is not null 来对 null 做判断。因此，若一个列有可能建立索引，那么最好设置成 not null。然而，有一种情况使用 null 会比较好，对于未知值使用默认值来代替会出现不合理的现象，例如时间，存储一个不可能的时间(0000:00:00 00:00:00)不如使用 null
不用为整数类型指定宽度	例如，int(11) ，它不会限制值的合法范围，只是规定了MySQL一些交互工具用来显示字符的个数，对于存储和计算，int(8)和int(11)是一样的。在新版本MySQL中，指定整型宽度会提示 1681 Integer display width is deprecated and will be removed in a future release
选择合适的小数类型	浮点类型在存储同样范围的值时，通常比decimal使用更少的空间，应该尽量在需要对小数进行精确计算时才使用decimal。在数据量大时，可以使用bigint代替decimal，只需要换算一下即可。
对vachar不要过于慷慨	当使用varchar(5)和varchar(1000)来存储’hello’时，表面上效果是一样的，但是在查询过程中会有很大的不同。由于临时表可能使用的是Memory表，而Memory引擎不支持变长字符串，对于变长字符串会为其分配最大长度，varchar(1000)会占用更多不必要的空间。因此，长度预估到合适大小就可以了，不要过于慷慨。如果字符串长度大致相等，可以使用 char 代替 varchar.如果 varchar 长度超过5000，建议修改为text，并另外建表存储该字段，使用主键来进行对应，避免影响其他字段索引效率
避免使用blob和text	使用blob和text会让MySQL使用MyISAM磁盘临时表，而不是更快的Memory表，哪怕这张表数据量再小也是如此
合理使用enum	遇到固定属性列表时，可以考虑使用枚举类型，由于枚举类型采用数字作为枚举常量，在表关联时效率比字符串关联高（两张表关联字段类型一致），由于是数字，使用的存储空间也会更小。需要注意的点是，如果新增新的枚举类型，需要使用alter 添加枚举类型，这点需要特别注意
日期和时间类型选择	不要使用timestamp，建议使用datetime类型
存储UUID值	移除“-”号，或者使用UNHEX()将UUID值转换为16字节的数字，检索时用HEX()函数格式化为16进制格式
存储IP地址	使用无符号整数存储IP地址，使用INET_ATON()和INET_NTON()函数来互转

  在选择字段类型时，遵循两步：（1）选择大类型，数字、时间、字符串，初步确定数据类型的选择范围；（2）在大类型下，根据业务需求，选择最合适的类型，考虑精度、范围、长度等因素。

下一节：MySQL之SQL优化篇（五）：索引查询优化