数据类型的优化

数据类型的优化

通用规则

1. 选择更小的数据类型。更小的类型会占用更少的磁盘、内存和cpu缓存，并且处理时需要的CPU周期更短。但是要确保没有低估储存值得范围。因为在schema中多个地方添加数据类型的范围是一个痛苦耗时的工作。
2. 简单地数据类型。整数比字符串好。MySql中内建的类型储存日期更好，而不是使用字符串。
3. 避免Null值。Null值无法被简单地索引在MyISAM中。但是在InnoDB中Null是以单独的bit存储的，反而对稀疏数据有更好的空间效率。

整数类型

1. 整数计算一般使用的是64位的BIGINT，即使是在32位的环境中亦是如此。
2. MySQL可以指定整数的宽度，例如INT(11)。但是一般这是没有意义的，宽度只是规定了客户端用来显示的字符个数。对于储存和计算来说INT(1)和INT(20)是相同的。

实数类型

1. DECIMAL。在MySQL5.0和更高的版本中，DECIMAL支持精确计算。在MySQL4.1以及更早的版本DECIMAL只是一个存储类型，计算会出现一些奇怪的精度损失。
2. CPU自身不支持DECIMAL计算，所以MySQL5.0中自己实现了DECIMAL计算。因此，CPU支持的原生浮点运算的速度会更快。
3. 浮点和DECIMAL都可以指定精度。但是这会消耗更多的空间。
4. 浮点类型在存储同样范围的值时，比DECIMAL使用更少的空间。FLOAT使用4个字节，DOUBLE使用8个字节。
5. 尽量在只对小数进行精确计算时才使用DECIMAL，例如存储财务数据。但是在数据量较大的情况下，可以考虑使用BIGINT代替。将存储货币单位根据小数位乘以相应的倍数即可。

VARCHAR&CHAR

1. 不同类型的存储引擎之间，VARCHAR和CHAR在磁盘和内存中的存储方式存在很大差异。同时存储引擎在内存和磁盘中存储字符窜的方式可能不一样，这就会涉及到从存储引擎中的值的格式转换。
2. VARCHAR比CHAR更节省空间，因为VARCHAR只使用必要的空间，除非ROW_FORMAT=FIXED
3. VARCHAR需要一个或者2个字节记录字符串长度。如果长度大于255使用2个字节，小于或者等于1个字节。例如VARCHAR(10)是11个字节，VARCHAR(1000)1002个字节。
4. VARCHAR的长度是可变的。在UPDATE时如果字符串变长，就会导致额外的工作。MyISAM会拆成不同的片段储存。InnoDB需要分裂页使行可以放进业内。InnoDB还会把过长的VARCHAR储存为BLOB。
5. VARCHAR适用于：
   • 字符串最大长度比平均长度大很多
   • 列的更新很少
   • 使用了UTF-8这样复杂的字符集
6. CHAR长度定值。储存CHAR值时，MySQL会删除所有的末尾空格，以方便之后补充比较。
7. CHAR适用于：
   • 长度相近的字符串，如md5值
   • 经常变更的数据，定长的CHAR不易产生碎片
   • 非常短的值。CHAR(1)1个字节，VARCHAR(1)需要2个字节，因为还需要一个记录长度的字节

BLOB&TEXT

1. BLOB和TEXT都是用来储存大的字符串类型的数据的。BLOB以二进制的方式储存，TEXT以字符串的方式储存。
2. MySQL只会对每列最前max_sort_length的字节而不是整个字符串进行排序

日期和时间类型

1. DATETIME。可以保存大范围的值，从1001年到9999年，精度为秒。它会把日期封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间。
2. TIMESTAMP。和UNIX时间戳相同。保存了自1970年1月1日午夜以来的秒数。使用4个字节的存储空间。范围只能从1970-2038年。
3. TIMESTAMP依赖于时区，因此如果存储的值为0，在美国东部时区显示的就是1969-12-31 19：00：00
4. TIMESTAMP比DATETIME效率更高，占用的空间更少