003 mysql之数据类型优化

原则

1. 更小的通常更好
2. 简单更好
3. 尽量避免NULL

数字

整数

TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间。它们可以存储的值得范围从
其中N是存储空间的位数。
类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0-255，而TINYINT可以存储的范围是-128-+127。有符号和无符号使用相同的存储空间，并且具有相同的性能。整数计算一般使用64位的BIGINT整数，即使在32位环境也是如此（一些聚合函数是例外，它们使用DECIMAL或DOUBLE进行计算），所以能选择的只有存储类型，计算时的类型有MySQL决定。

实数

实数是带有小数部分的数字。FLOAT[（M,D）]，DOUBLE[（M,D）]，DECIMAL[（M,D）]，M是总位数，D是小数点后面的位数，如果M,D省略，则根据硬件允许的限制来保存值。
例如：DECIMAL（18，9）小数点两边各存储9个数字，小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身用1个字节，一共9个字节。MySQL 5.0以及以上版本将数字打包保存到一个二进制字符串中（每4个字节存储9个数字）。因为DECIMAL需要额外的空间和计算开销，所以应该尽量只在对小数进行精度计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数乘以相应的倍数即可。例如如果数据精确到万分之一，可以把数据乘以一百万（参考微信的账务）。

字符串

VARCHAR

• a.VARCHAR可变长字符串，比定长类型更节省空间，因为它仅使用必要的空间。（但是如果表使用FOW_FORMAT=FIXED创建话，每行都是定长）。
• b.VARCHAR需要1到2个额外字节记录字符串的长度：如果列的最长长度<=255，则使用1个字节表示，否则使用两个字节。假设采用Latin1字符集，一个VARCHAR（10）列需要11个字节的存储空间。VARCHAR（1000）的列则需要1002个字节，因为需要两个字节来表示长度。VARCHAR节省了存储空间，性能更好。但是由于行是变长的，在update的时候很可能行变大，这时候可能需要分裂新的页来存储。

CHAR

• a.CHAR是定长的：MYSQL总是分配定义的长度的空间。当存储类型是CHAR的时候，MYSQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。
• b.CHAR很适合存储很短的字符串，或者所有值都接近同一个长度。例如，存储密码的MD5，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR在空间上也更有效率。例如CHAR（1）来存储Y和N的值，如果采用单字节字符集（一个字节来表示一个字符）只需要一个字节，但是VARCHAR（1）缺需要两个字节，因为还有一个额外记录长度的字节。
  
  

BLOB和TEXT类型

• a.都是存储很大的数据而设计的字符串类型，分别采用二进制和字符方式存储。当BLOB和TEXT的值太大时，InnoDB会使用专门外部存储区域来进行存储，此时每个值在行内只保留个1-4个字节的指针。BLOB和TEXT仅有的不同是BLOB使用的二进制，没有排序规则和字符集，而TEXT有排序规则和字符集。
• b.MYSQL对BLOB和TEXT列进行排序与其他类型存储不同，它只对每个列钱max_sort_length字节而不是对真个字符串做排序。如果只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或者使用ORDER BY SUSTRING(column length)
  ## ENUM
  可以把一些不重复的字符串存储成一个预定义的集合，MYSQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或两个字节中。
  

日期和时间

DATETIME

范围从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的正数中，例如2017-09-09 18:24:15

DATE

范围从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMM的正数中，例如2017-09-09

TIMESTAMP

保存了从1970年1月1日以来的秒数，它和UNIX时间戳相同。TIMESTAMP只是用4个字节的存储空间，因此只能表示从1970年到2038年。MYSQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转化为Unix时间戳。