高性能MySQL（第3版）读书笔记-第四章Schema与数据类型优化

选择优化的数据类型

三原则：
1.正确的最小大小的数据类型
2.最简单的数据类型，如整数
3.尽量避免NULL

确定合适的大类型（数字、时间、字符串等）之后选择合适的子类型

整数类型

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT分别使用8,16,24,32,64字节存储空间
整数类型有UNSIGNED选项，表示不允许负数，开启后会将正数上限提高一倍，如TINYINT会从-128~127变为0到255。
整数计算一般采用BIGINT（一些聚合函数例外，他们使用DECAIMAL或DOUBLE计算）
Myql可以为整数指定宽度，例如INT(11)。这个只规定了Mysql的交互工具显示的字符个数，对于存储和计算来说没有意义。

实数类型

实数是带有小数部分的数字
Mysql支持精确类型或者非精确类型。

不止为了存储小数部分，DECIMAL可以用于存储BIGINT无法存储的整数。

FLOAT和DOUBLE支持标准的浮点数计算。由于CPU不支持对DECIMAL直接计算，在4.1或更早版本使用浮点计算来实现DECIMAL计算（转换为DOUBLE来浮点计算），可能会出现精度损失。在5.0后自身实现了DECIMAL的高精确计算。

DECIMAL

DECIMAL从MySQL 5.1引入，列的声明语法是DECIMAL(M,D)。在MySQL 5.1中，参量的取值范围如下：
M是数字的最大数（精度）。其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254），M 的默认值是10。
D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。

浮点和DECIMAL都可以指定精度。对于DECIMAL列，可以指定小数点前后的最大位数，会影响列的空间消耗。在5.0版本以后DECIMAL允许最多65个数字，将数字打包保存到二进制字符串中（每4个字节存储9个数字），例如DECIMAL（19，8）总共占用9个字节。小数点左侧占4个字节，小数点占一个字节，小数点右侧占一个字节。

由于需要额外空间和计算开销，尽量只在对小数进行精确计算时使用，例如存储财务数据。但在数据量大时可以考虑采用BIGINT代替，将货币单位乘以相应的倍数即可。同时避免浮点存储计算不准确和DECIMAL精确计算代价高的问题。

字符串类型

VARCHAR与CHAR

VARCHAR用于存储可变长字符串，CHAR用于存储定长字符串。

假设存储引擎为InnoDB

VARCHAR比定长类型更节省空间，仅使用必要的空间（使用ROW_FORMAT=FIXED创建表除外），当最大长度小于或等于255字节，使用一个字节记录长度，否则使用两个字节记录长度。
VARCHAR节省存储空间，对性能有所帮助。但是在不断update下，行所占空间持续增长，页的剩余空间持续缩小。当页内没有更多空间时，不同存储引擎会做不同的处理，InnoDB需要分裂页来使行可以放进页内。

VARCHAR在5.0版本后存储和检索会保留末尾空格，但在4.1或更老版本会删除末尾空格。
CHAR存储时，会删除末尾空格。（会根据需要采用空格填充以方便比较）

以下情况使用VARCHAR是合适的：
a) 字符串列的最大长度比平均长度大。
b) 列的更新很少，所以碎片不是问题。
c) 除了向UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

以下情况使用CHAR是合适的：
a) 很短的字符串，因为varchar需要一个字节或两个字节存储字符串长度。
b) 所有值都接近同一长度，长度是定长的。
c) 经常变更的数据，不容易产生碎片。

注：虽然VARCHAR为可变长类型，但分配时仍需按需分配。（更长的列会消耗更多内存，影响对内存临时表，磁盘临时表的操作和排序）

BINARY与VARBINARY

存储的为二进制数据。
填充采用的\0进行填充，而不是空格，检索时也不会省去（检索时需要保持值不变时需要注意）
当需要存储二进制数据，并且希望mysql采用字节码不是字符比较时，使用BINARY与VARBINARY不仅会大小写敏感，而且会更方便更快的比较：逐个字节进行该字节的数值比较。

BLOB和TEXT

存储很大的字符数据而设计的字符串数据类型，分别采用二进制和字符串方式存储。
mysql会把BLOB和TEXT作为一个独立对象处理。存储引擎在存储时会做特殊处理，当值过大时，InooDb会使用专门的外部存储区域，每个值在行内需要一个1~4字节存储一个指针，在外部存储区域存储具体的值。

BLOB中采用二进制方式存储，没有字符集，排序和比较基于数值值。
TEXT采用字符串方式存储，存在一个字符集，根据该字符集进行排序。
mysql对BLOB与TEXT排序不是对整个字符串进行排序，只针对于最前面的字节（设置的max_sort_length长度或者使用ORDER BY SUBSTRING(column, length)）

使用枚举代替字符串类型

mysql将一些不重复的字符串存储到一个预定义的集合中，在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或两个字节中。会将字符串所在顺序（创建表定义枚举时）保存为整数，在表的.frm文件中保存成“数字-字符串”映射关系的查找表。
尽量不要将枚举的常量设置成数字，容易混淆。
枚举字段是按照查找表中存储的整数排序，不按照插入时的字符串顺序排序。（需在创建表定义枚举时定义成自己所需顺序）

枚举的字符串列表是固定的，添加删除必须使用ALTER TABLE。

mysql将枚举值保存为整数，必须进行查找才能转换为字符串，所以枚举列存在一定的开销。

查找表时采用整数键进行关联，避免使用字符串键。将字符串列转换为枚举列有利于查找表时列关联的速度（字符串列与枚举列关联可能比字符串列直接关联更慢，两个枚举列关联最快），并且会缩小表，主键所占用的空间。

日期和时间类型

Mysql的事件类型最小时间粒度为秒，如果要微秒级，需要自己使用BIGINT存储微妙级的时间戳或者使用DOUBLE存储秒之后的小数部分。

时间的子类型TIMESTAMP与DATETIME的区别

a）DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。
b）DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
c）两者的存储方式不一样 ，对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。
d）两者所能存储的时间范围不一样
timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’；
datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。

位数据类型

少数几种类型使用位存储，为存储在Mysql中技术上是字符串实现。

BIT

MySQL5.0之前BIT与TINYINT相同，5.0之后的版本变为特性完全不同的数据类型。
BIT可以在列中存储一个或多个true/false，最大长度为64位。
BIT的行为根据存储引擎决定，InnoDB会位每个BIT列使用一个足够存储的最小整数类型来存放，无法节省存储空间。

Mysql把BIT当作字符串类型，不是数字类型。检索BIT值时，获得是字符串的字符码值而不是ASCII码值。
当存储一个8位的二进制00111001(二进制等于57)到BIT(8)中，字符码为57，对应的ASCII码值应为9，但是在数字上下文场景中得到的数字为57。

谨慎使用BIT，如果想在一个bit的存储空间中存储一个true/false值，可以使用为空的char(0)列,该列可以保存空值（NULL）或者长度为零的字符串（空字符串）。

SET

SET类型是将多个true/false储存在一个列中，有效节省了空间。
缺点仍是需要使用ALTER TABLE来更改表中列的数据，修改代价过高。

可考虑使用整数来代替SET，使用一个整数包装一系列的位。
优点是修改数据时不再需要ALTER 来更改数据，缺点是查询语句更难写和理解（当第五位被更改时是什么含义）。

如果使用SET列，可以让MySQL 在列定义里存储位到值的映射关系,内部存储的是整数列；如果使用整数列，则可以在应用代码里存储这个对应关系。

选择标识符

一旦选择一种类型，要确保在所有的关联表中使用相同的类型。

整数类型

通常是标识列的最好选择。因为很快并且可以使用AUTO_INCREMENT。

ENUM和SET

通常是糟糕的选择。由于ENUM和SET无法轻易修改，只适合存储固定信息。只适用于一些描述固定状态或类型的静态定义表。

字符串类型

应避免使用字符串类型作为标识列。由于使用字符串会占用更多的空间并且通常比整数类型更慢。
对于完全随机的字符也需要多加注意，如MD5(),SHA1(),UUID()产生的字符串，这些函数生成的新值会分布在很大的空间，对于插入，查询会更耗费时间：
a) 因为插入值会随机地写到索引的不同位置，所以使得INSERT语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
b) SELECT语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
c) 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。如果这个数据集都一样的“热”，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。

如果存储UUID值，则应该移除"-"符号；或者更好的做法是，用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。

UUID()生成的值与加密散列函数例如SHA1()生成的值不同的特征：UUID值虽然分布也不均匀，但还是有一定的顺序的。尽管如此，但还是不如递增的整数好用。

特殊类型数据

某些类型的数据并不直接与内置类型一致。例子：
a) 低于秒级精度的时间戳就是一个例子；
b) IPv4地址。人们通常使用VARCHAR(15)来存储IP地址。然而，它们实际是32位无符号整数，不是字符串。用小数点将字段分割成四段是为了阅读方便。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。