MySQL进阶实战1，数据类型与三范式

一、选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。

1、更小的

一般情况下，应该尽量使用较小的数据类型，更小的数据类型通常更快，因为占用更少的磁盘、内存和CPU缓存，处理时需要的CPU周期更短。

2、更简单的

简单的数据类型通常需要更少的CPU周期，整形比字符串类型代价更低，因为字符集和校验规则使字符比较比整形比较更复杂。

3、尽量避免NULL

很多表都包含可为NULL的列，即使应用程序并不需要保存NULL也是如此，因为可为NULL是列的默认属性，通常情况下，最好指定列为NOT NULL。

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

4、datetime和timestamp datetime和timestamp都可以存储时间和日期，都精确到秒，但是timestamp只是用datetime一半的存储空间，并且timestamp会根据时区变化，具有特殊的自动更新能力。

二、整形类型

1、整形

如果存储整形，可以使用tinyint、smallint、mediumint、int、bigint。分别使用8、16、24、32、64位存储空间。它们可以存储的值的范围从-2^(n-1)到2^(n-1)-1，其中N是存储空间的位数。

整形类型有可选的unsigned属性，表示不允许负值，这大致可以使正数的上限提升一倍。例如tinyint unsigned可以存储的范围是0~255，而tinyint的存储范围是-128 ~ 127。

有符号和无符号类型使用的是相同的存储空间，具有相同的性能，因此可以根据实际情况选择合适的类型。

2、实数

实数是带有小数部分的数字，然而，它们不只是为了存储小数部分，还可以使用decimal存储比bigint还大的数字。

float和double类型支持使用标准的浮点计算进行近似计算。float使用4个字节存储，double使用8个字节存储，相比float有更高的精度和更大的范围。

因为需要额外的空间和计算开销，应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据，但在数据量比较大的时候，可以使用bigint代替decimal，将需要存储的货币单位根据小数的位数乘以相应的倍数即可，这样可以避免浮点数存储计算不精确和decimal精确计算代价高的问题。

三、字符串类型

1、varchar

varchar是可变长的字符串，是最常见的字符串数据类型。它比定长类型char更省空间，因为它仅适用必要的空间。varchar需要使用1~2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节，使用1个字节表示，否则使用2个字节。

2、char

char类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间，char值会根据需要采用空格进行长度填充。

1. char适合存储很短的字符串或者都是一个长度的字符串，比如MD5值；
2. 对于经常变更的数据，char也比varchar更友好，定长的char类型不容易产生碎片；
3. 对于较短的列，char也比varchar更有效率，因为不用额外的1字节记录长度。

3、blob和text类型

• blob采用二进制存储，没有排序规则和字符集；
• text采用字符方式存储，有排序规则和字符集；

MySQL会把每个blob和text值当做一个独立的对象处理。存储引擎在存储是通常会做特殊处理。当blob或text的值太大时，InnoDB会使用专门的外部存储空间进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

尽量避免对blob或text字段进行排序，可以通过substring()方法截取一小部分字符进行排序，也不能将blob或text列全部长度的字符串进行索引。

四、三范式

第一范式：每个表的每一列都要保持它的原子性，也就是表的每一列是不可分割的； 第二范式：在满足第一范式的基础上，每个表都要保持唯一性，也就是表的非主键字段完全依赖于主键字段； 第三范式：在满足第一范式和第二范式的基础上，表中不能产生传递关系，要消除表中的冗余性；

五、范式的优缺点

1、优点

1. 范式化的更新操作通常比反范式化要快；
2. 当数据较好地范式化后，就只有很少或者没有重复数据，所以只需要修改更少的数据；
3. 范式化的表通常更小，可以更好地存放在内存中，执行操作更快；
4. 很少有冗余的数据，意味着检索数据时更少使用distinct或group by语句；

2、缺点

1. 查询时，由于分表的原因，常常需要表关联；
2. 可能无法触发索引；

六、反范式的优缺点

1、优点

1. 由于都在一个表里，可以减少表关联查询；
2. 可能同属于一个索引的字段，不用分表，触发索引更方便；

2、缺点

1. 存储过多的冗余数据
2. 查询时可能需要distinct或group by的情况更多；

在实际的开发中，都是混用范式化和反范式化，有的时候必要的冗余，有益而无害。