【MySQL】数据类型优化

表结构设计原则：

1. 尽量使用可以正确存储数据的最小数据类型。这样占用更少的磁盘、内存和CPU缓存，处理时需要的CPU周期也更少。
2. 选择更简单的数据类型。整型比字符操作代价更低，比如存储时间用date、time不用字符串，存储ip用整型不用字符串。
3. 尽量避免使用null，除非真的需要存储null值。因为可为null的列似的索引、索引统计和值比较都更复杂；可为null的列使用更多的存储空间。null改not null性能提升较小。如果计划建索引，尽量避免可为null

选择具体类型：
很多MySQL的数据类型可以存储想同类型的数据，只是存储的长度和范围不一样、允许的精度不一样、或者需要的物理空间不通，或者有一些特殊的属性。
数字分为整数和实数

整数类型

• 存储整数可以用：TINYINT（256），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。
• 可以为整数类型指定宽度，例如INT(11)，对大多数应用没有意义：它不会限制值得合法范围，只是规定了一些交互工具显示字符的个数。对于存储和计算来说，INT(3)和INT(20)是相同的

实数类型

实数是带有小数部分的数字。不止为了小数部分，也可使用DECIMAL存储比BIGINT还大的整数。
MySQL支持精确类型DECIMAL，也支持不精确类型FLOAT和DOUBLE。都可以指定精度。

• DECIMAL列可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如DECIMAL(18,9)小数点两边哥存储9个数字，一共使用9个字节：小数点前数字4个字节，小数点后数字4个字节，小数点本身1个字节。
  mysql5.0后DECIMAL类型最多允许65个数字。整数部分和小数部分的数字个数加起来最大可存65位，如果设计时指定了保留有效小数位数，小数部分的数字如果超出指定的有效小数位数就会四舍五入。
  DECIMAL只是一种存储格式，在计算中DECIMAL会转换为DOUBLE类型。

• 浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储，DOUBLE使用8个字节存储。不同的只是存储类型，MySQL内部使用DOUBLE作为内部浮点计算的类型。
  因为需要额外额空间和开销，索引尽量只对小数进行精确计算时才使用DECIMAL（比如财务数据）。数据量比较大的时候，可以考虑将数据乘以相应的倍数在进行存储，避免浮点存储计算不精确和DECIMAL精确计算代价高的问题

字符串类型

（假设存储引擎是InnoDB或者MyISAM，其他引擎参考各自文档）

VARCHAR

varchar存储的可变长字符串，是最常见的字符串类型。比定长类型更节省空间，因为它今使用必要的空间。（有一种例外：表使用ROW_FORMAT=FIXED创建，每一行都会定长存储）
varchar需要1或2个额外的字节记录字符串的长度：列长<=255字节，一个字节表示，否则需要两个字节。
varchar节省了存储空间，所以对性能也有所帮助。但是由于行是变长的，在update时可能会使行变得比原来更长，这就需要额外的工作。（MyISAM会将行拆成不通的片段存储，InnoDB则需要分裂页来使行可以放入页内。）

适合使用varchar的情况：字符串最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
varchar（200）和varchar（20）存储’mysql’空间开销是一样的。但前者需要消耗更多的内存。MySQL通常分配固定大小的内存快保存内存值，尤其是使用内存临时表进行排序或者操作时，或者用磁盘临时表排序时都很浪费空间。

CHAR

char类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。char值会根据需要采用空格进行填充以方便比较。
存储char值时，MySQL会删除所有的末尾空格。
同时char值会根据需要采用空格填充以方便比较。
适合使用char的情况：很短的字符串，或者所有值都接近同一个长度（比如密码的MD5值）。对于经常变更的数据，char也比varchar更好，因为定长的char类型不易产生碎片。非常短的列，比如CHAR(1)只需要1个字节，但是varchar（1）需要两个字节，因为还有一个记录长度的字节

BINARY和VARBINARY

存储的事二进制字符串。和常规字符串相似，但二进制字符串存储的事字节码而不是字符，填充BINARY采用的事\0（零字节）而不是空格，在检索时也不会去掉填充值。
二进制比较的优势：大小写敏感；二进制比较比字符比较更简单更快。

BLOB和TEXT

都是为了存储很大的数据而设计的字符串数据类型，BLOB采用二进制方式存储，TEXT采用字符串方式存储。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。
与其他类型不用，MySQL把每个BLOB和TEXT值当做单独的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后再外部存储区域存储实际的值。
BLOB类型存储的事二进制数据，没有排序规则或者字符集
TEXT类型存储的是字符串类型，有字符集和排序规则。
排序规则与其他类型不同，只对每列最前max_sort_length字节而不是整个字符串进行排序。可以更改max_sort_length或者使用order by substring(column,length)来更改排序字符数。
MySQL不能将BLOB和TEXT全场的字符串进行索引。

ENUM

枚举列可以把一些不重复的字符串存储成一个预定义的集合。在存储枚举时会分厂紧凑，根据值得数量压缩到1~2个字节中。MySQL会在内部将每个值在列表中国的位置保存为整数，并在表的文件中保存数字-字符串映射关系的查找表。
枚举字段按照内部存储的整数进行排序。
关联速度：ENUM关联ENUM(最快)>varchar关联varchar>enum关联varchar=varchar关联enum
枚举缺点：字符串列表是固定的，增删字符串必须使用ALTER TABLE。会变的字符串不宜使用枚举。
枚举优点：表空间变小，主键大小变小。某些情况下，导致较慢的enum和varchar关联也值得

日期和时间类型

DATETIME

这个类型能保存大范围的值，从1001到9999年，精度为秒。datetime把日期和时间封装到格式为YYYYMMDDHHmmss的整数中，与时区无关。使用8个字节的存储空间。
默认情况下，MySQL以一种可排序，无歧义的格式显示DATETIME值。这是ANSI标准定义的日期和时间表示方法。

TIMESTAMP

• timestamp类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，和UNIX时间戳相同。
• timestamp只用4个字节的存储空间，因此范围比datetime小得多：只能表示从1970年到2038年。
• timestamp显示的值依赖于时区，MySQL服务器、操作系统、客户端连接都有时区设置。因此存储值为0的timestamp在美国东部时区显示为"1969-12-31 19:00:00"，与格林尼治时间差5个小时。
• 可以配置任何timestamp的插入和更新行为。
• timestamp默认NOT NULL,与其他数据类型不一样。

尽量使用timestamp，它比datetime效率更高。
如果需要比秒更小粒度的日期和时间值，可以用BIGINT类型存储微妙级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。

位数据类型

MySQL有几种存储类型使用紧凑的为存储数据。这些类型底层存储格式和处理方式不同，技术上都是字符串。

BIT

• mysql5.0之前，BIT是tinyint的同义词。之后是一个新的数据类型。
• 可以用bit列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段，BIT(2)存储2个位，以此类推。BIT列最大长度是64个位。
• BIT列的行为因存储引擎而异。MyISAM会打包存储所有的BIT列，所以17个单独的BIT列只需要17个为存储（假设没有可为NULL的列），这样MyISAM只使用3个字节就能存储这17个列。其他存储引擎为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。
• mysql把BIT当做字符串类型，而不是数字类型。当检索BIT(1)的值时，结果是一个包含二进制0或1值的字符串，而不是ACII码的“0”或“1”。在数字上下文的场景中检索时，结果是字符串转成的数字。如存储一个值b’00111001’到BIT(8)的列并检索它，得到的内容是字符码为57的字符串（也就是ASCII码为57的字符“9”），但在数字上下文场景中，得到的是数字57.
  鉴于转换比较复杂，建议慎用BIT类型。

SET

• 如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。这样就有效的利用了存储空间，并且MySQL有FIND_IN_SET()和FIELD()这样的函数方便在查询中使用。
• 缺点是改变列的代价较高：需要ALTER TABLE。一般来说也无法在SET列上通过索引查找。一种替代SET的方式是使用一个整数包装一系列的位。例如可以把8个为包装到一个TINYINT中，并且按位操作来使用。可以在应用中为每个位定义名称敞亮来简化这个操作。也有缺点：查询语句更难写更难理解。

选择标志符（identifier）

• 为标识列（identifier column）选择合适的数据类型很重要。一半来说更有可能用标识列与其他值比较，或通过标识列寻找其他列。标识列也可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应当选择跟关联表中对应列一样的类型。
• 除了考虑存储类型，还要考虑MySQL中对这种类型怎么执行计算和比较。（比如在内部使用整数存储ENUM和SET类型，然后再作比较操作是转换为字符串）。
• 一旦选定一种类型，要确保所有关联表中都是用同样的类型。类型质检要精确匹配，包括向UNSIGNED这样的属性。混用不同的数据类型可能导致性能问题
• 整数类型：整数类型通常是标识列最好的选择，很快，并且可以使用AUTO_INCREMENT。
• 字符串类型：如果可能避免使用字符串类型作为标识列，以为很消耗空间，并且通常比数字类型慢。随机字符串也会导致insert以及一些select会很慢。（可以用UNHEX（）函数转换UUID值为16字节的数字）
• ENUM和SET类型：对于标识列来说，ENUM和SET类型通常也是一个糟糕的选择。
  ENUM和SET列适合存储固定信息，例如有序的状态、产品类型、人的性别。

特殊类型数据

某些类型数据并不直接与内置类型一致。

• 低于秒级精度的时间戳，可以用BIGINT类型存储微妙级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。
• IPv4地址，人们经常用varchar(15)列存储。然而是32位无符号整数，不是字符串。用小数点将地址分成四段的表示方法只是为了阅读容易。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()
  和INET_NTOA()函数在这两种表示方法之间转换。