高性能的Mysql读书笔记系列之四（Schema与数据类型优化）

前言：

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schema，这往往需要权衡各种因素。例如，反范式的设计可以加快某些类型的查询，但同时可能使另一些类型的查询变慢。

所以在进行库表设计的时候，需要根据具体的情况在范式和非范式之间进行取舍。

正文：

一、如何选择优化的数据类型

1.更小的通常更好

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和cpu缓存，并且处理时需要的cpu周期更少。

但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。（如果系统不是很忙或者存储的数据量不多，或者是在可以轻易修改设计的早期阶段，那之后修改数据类型也比较容易）。

2.尽量避免null

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

3.整数类型指定长度意义不大

MySQL可以为整数类型指定宽度，例如INT（11），对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT（1）和INT（20）是相同的。

4.实数类型建议只在对小数精确计算使用（Decimal）

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型；MySQL使用DOUBLE作为内部浮点计算的类型。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

5.字符串类型选择

•  char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。
•  varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
•  text不设置长度， 当不知道属性的最大长度时，适合用text。

6.使用枚举（ENUM）代替字符串类型 

不推荐在mysql中设置某一字段类型为enum，因为枚举的作用主要是为了限定值得取值范围，比如性别（男，女，未知），这个可以在入参时，从前端和参数校验来做。

7.日期和时间类型

• 两者的存储方式不一样，TIMESTAMP涉及时区的转化问题，DATETIME是原样输入和输出
• 两者所能存储的时间范围不一样，DATETIME的范围比TIMESTAMP的范围更大些

建议使用DATETIME类型来存储时间 

二、MySQL schema设计中的陷阱

太多的列，会使cpu的占用非常高，从而降低查询效率。

太多的关联，一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12张表以内做关联，当然随着mysql的版本不断优化，可能这个限制的阈值会变高，但是还是尽量关联少量的表。

三、范式和反范式

范式的优点和缺点：

• 范式化的更新操作通常比反范式化要快。
• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。
• 范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。

 反范式的优点和缺点：

•  反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。
• 如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O(14)。
• 单独的表也能使用更有效的索引策略。

范式化和反范式化的schema各有优劣，怎么选择最佳的设计？
事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧。 

总结：

今日分享一句土味情话：

虽然今天天气很冷，但你让我心里暖。

我是阿达，一名喜欢分享知识的程序员，时不时的也会荒腔走板的聊一聊电影、电视剧、音乐、漫画，这里已经有11359位小伙伴在等你们啦，感兴趣的就赶紧来点击关注我把，哪里有不明白或有不同观点的地方欢迎留言！