MySQL之 为列选择合适的数据类型

本节内容总结自《High Performance MySQL》

选择优化的数据类型

选择数据类型的方法

1. 更小的通常更好：一般情况下尽量使用可以正确存储数据的最小数据类型。更小的通常更快，因为他们占用更少的磁盘，内存和CPU缓存。
2. 简单就好：简单数据类型的操作通常需要更少的CPU周期。例如，整形比字符操作带价更低，因为字符集和校对规则使字符比较比整形比较更复杂。应该使用MySQL内建的类型而不是字符串来存储日期和时间，应该使用整形存储IP地址。
3. 尽量避免NULL：通常情况下最好指定列属性为NOT NULL，除非真的需要存储NULL值。NULL对于MySQL来说更难优化，因为可为NULL的列使得索引，索引统计和值比较都更加复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。

整数类型

1. 分类
   1. TINYINT（8位）
   2. SMALLINT（16位）
   3. MEDIUMINT（24位）
   4. INT（32位）
   5. BIGINT（64位）
2. UNSIGNED：整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。
3. MySQL也可以为整形指定宽度，例如INT(11)，他不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数。对于存储和运算来说，INT(1)和INT(20)是相同的。

实数类型

1. 概述：实数是带有小数部分的数字。然后他们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，有支持不精确类型。
2. 分类
   1. DECIMAL：用于存储精确的小数。支持精确计算。DECIMAL只是一种存储格式，计算的时候会转为DOUBLE类型。尽量只在对小数进行精确计算时才使用DECIMAL存储数据。
   2. FLOAT：4个字节。
   3. DOUBLE：8个字节，作为MySQL内部浮点计算的类型。

字符串类型

1. VARCHAR：VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。他比定长类型更节省空间，因为他仅使用必要的空间。VARCHAR需要使用1或2个额外字节记录字符串的长度。VARCHAR节省了存储空间，对性能也有帮助。但由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致了需要做额外的工作。有可能原来存放字符串的页没有更多的存储空间。这种情况下不同存储引擎处理方式不一样。例如MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。以下三种情况推荐使用VARCHAR：①：字符串列的最大长度比平均长度大很多。②：列的更新很少，所以碎片不是问题。③：使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
2. CHAR：CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。CHAR适合存储很短的字符串。或者所有值都接近同一个长度的字符串。对于经常变更的值，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。
3. BINARY和VARBINARY：他们和上述两种类型一样，他们用来存储二进制字符串。MySQL填充BINARY采用'\0'而不是空格，检索时也不会去掉填充值。
4. BLOB和TEXT类型：都是为存储很大数据而设计的字符串数据类型，分别采用二进制和字符方式存储。实际上他们分别属于两组不同的数据类型家族：字符类型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT；对应的二进制类型是TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT也是SMALLTEXT的同义词。MySQL把每个BLOB和TEXT类型当作一个独立的对象处理。当BLOB和TEXT太大时，InnoDB会使用专门的外部存储区域来进行存储，此时每个值在行内需要1~4字节存储一个指针，然后在外部存储区域存储实际的值。
   • BLOB和TEXT类型的区别：BLOB类型没有排序规则会字符集，而TEXT有。而对TEXT类型列排序只会对每个列的最前max_sort_length字节而不是整个字符串做排序。同理，MySQL也不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

使用ENUM类型代替字符串类型

1. ENUM定义：枚举列可以把一些不重复的字符串存储成一个预定义的集合。
2. MySQL对于ENUM类型的存储：非常紧凑，会根据列表值的数量压缩到一个字节或两个字节中。MySQL内部会将每个值在列表中的位置保存为整数，并且在表.frm文件中保存"数字-字符串"映射关系的查找表。
3. 枚举的排序规则：枚举是按照内部存储的整数而不是定义的字符串进行排序的。可以使用FIELD()函数显示的指定排序规则，但这会导致MySQL无法利用索引消除排序。
4. 如果需要在字符串建立主键索引，最好考虑将字符串封装到枚举当中，这样会使得表中的其他非主键索引也会更小。

日期和时间类型

1. DATETIME：这个类型可以保存大范围的值，从1001年到9999年，精度为秒。他把日期和时间封装到格式YYYYMMDDHHMMSS的整数中，和时区无关。
2. TIMESTAMP：保存了从1970年1月1日午夜以来的秒数，他和UNIX时间戳相同。TIMESTAMP只使用4个字节，因此它的范围比DATETIME小得多，只能表示从1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为UNIX时间戳。TIMESTAMP显示的值依赖于时区设置。

位数据类型

1. 简述：MySQL有少数几种存储类型使用紧凑的位存储数据，从技术上来说都是字符串类型。
2. BIT：可以使用BIT列在一列中存储一个或多个true/false值。BIT(n)表示存储n个位，n最大为64。MyISAM会打包存储所有的BIT列，所有17个单独的BIT列只需要17个位存储，这样MyISAM只要3个字节就可以存储这17个列。其他存储引擎例如Memory和InnoDB，为每个BIT列使用一个足够存储的最小整数类型存放。MyISAM把BIT当作字符串类型而不是数字类型。当检索BIT(1)的值时，结果是一个包含二进制0或1值的字符串，而不是ASCII码的"0"或"1"。然而，在数字上下文的场景中检索时，结果将是位字符串转换成的数字。
3. SET：如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，他在MySQL内部是以一系列打包的位的集合来表示的。有效地利用的存储空间。MySQL有像FIND_IN_SET()和FIELD()这样的函数可以方便查询。它主要的缺点是改变列的定义的代价较高，需要ALTER TABLE，也无法在SET列上通过索引进行查找。
4. 使用整数按位操作代替SET：一种代替SET的方式是使用一个整数类型包装要存储的一系列位。例如，可以把8个位包装到一个TINYINT中，并且按位操作来使用。可以在应用中位每个位定义名称常量来简化这个工作。

选择标识符（identifier）

1. 简述：为标识列选择合适的数据类型非常重要，一般来说更有可能用标识列与其它值进行比较（例如在关联操作中），或者通过标识列寻找其他列。标识列也可能在其它另外的表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型。一旦选定了一种类型，要确保在所有关联表中使用同样的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性。在可以精确存储值的情况下选择最小的数据类型。
2. 关于标识列数据类型的选择问题
   1. 整数类型：整数通常是标识列最好的选择，因为他们很快而且可以使用AUTO_INCREMENT。
   2. ENUM和SET类型：ENUM和SET类型通常是一个糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题大。ENUM和SET列适合存储固定信息，这样就相对限制了可扩展性。
   3. 字符串类型：应避免使用字符串类型作为标识列，因为他们很耗空间，并且通常比数字慢。MyISAM默认对字符串是用压缩索引，这会导致查询慢得多。
   4. 伪随机字符串：对于完全伪随机的字符串例如MD5()，SHA1()或者UUID()，产生的字符串，这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢。因为插入值会随机的写到索引的不同位置使得INSERT变得更慢。这会导致页分裂，磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。SELECT语句变慢是因为逻辑上相邻的行会分布在磁盘和内存的不同地方。随机值会导致缓存对所有类型的查询语句效果都很差，因为这会使得缓存赖以工作的访问局部性原理失效。

特殊数据类型

• 使用32位无符号整数存储IPv4的地址而不是字符串。MySQL提供了INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。