《高性能MySQL》读书笔记-2-Schema与数据类型优化1

良好的逻辑设计和物理设计是高性能的基石，系统需根据要执行的查询语句来设计schema，其中反范式的设计可以加快某些类型的查询，但是可能导致一些类型的查询变慢。

我之所谓生存，并不是苟活，所谓温饱，不是奢侈，所谓发展，也不是放纵。
— 鲁迅

1. 选择优化的数据类型

选择合适数据类型的原则：

更小的通常更好
一般情形下，尽量使用可以正确存储数据的最小数据类型，更小的数据类型占用更少的磁盘、内存、CPU缓存、更少的CPU周期。
简单就好
简单数据类型的操作通常需要更少的CPU周期。整型比字符串操作代价更低，字符串的字符集和校对规则要比整型的复杂。例如：使用内建的类型来存储时间；使用整型来存储IP值。
尽量避免NULL
通常情况最好指定列为not null，除非真的需要存储null值。可以为null的列使得索引、索引统计、值比较都变得更复杂。

timestamp和datetime都可以存储时间和日期，并且精确到秒：其中timestamp只需要datetime一半的空间，但是timestamp允许的时间范围要小得多。

1.1 整数类型

整数存储可以使用：tinyint,smallint,mediumint,int,bigint，他们分别使用8，16，24，32，64位存储空间。同时整数可选unsigned属性，表示正整数。这可以使得该类型的上限范围大概提高一倍。整数计算一般使用64位的bigint整数。虽然MySQL可以为整数类型指定宽度，例如int(11)，但一般来说没有意义，他不会限制值的合法范围，只是规定了MySQL的一些交互工具显示字符的个数，对于存储和计算来说，int(1)和int(20)是相同的。

1.2 实数类型

实数是带有小数的数字。float(4字节)和double(8字节)类型支持标准的浮点数运算，同时decimal支持更高精度的计算。需要指出，由于CPU不支持decimal的直接计算，decimal类型的计算是由MySQL核心服务实现的。因此，如非必要，使用原生浮点计算类型float和double会比decimal更快。使用浮点数时，建议只指定数据类型，不指定精度。MySQL使用double作为内部浮点计算的类型。

由于浮点数计算需要额外的空间和计算开销，所以尽量只在对小数进行计算时才使用decimal。数据量比较大的情况下，可以根据小数位数乘以相应倍数，使用bigint来存储，可以避免浮点数计算不精确和decimal计算代价高的问题。

1.3 字符串类型

1.3.1 varchar

varchar用于存储可变长字符串。他会使用额外的1到2个字节来存储字符串长度。varchar节省了空间，对性能有帮助，但是变长也带来了一些问题：update时字符串可能变长，这就需要额外的工作，比如，InnoDB可能会需要分裂页来让行可以放进页内。

合适的用varchar情况：

字符串列的最大长度比平均值大的多，
列的更新少，碎片化不是问题；
使用了UTF-8这样复杂字符集的，每个字符都用不同的字节存储

1.3.2 char

char是定长的，MySQL会根据定义的长度分配足够的空间，存储char值时，MySQL会删除字符串末尾空格，同时根据需要填充空格以方便比较。

适合char的情况：

短的字符串，或者所有值都接近一个长度
经常变更的数据，char也比varchar更好，char不容易产生碎片

比较短的列，char比varchar在存储空间上更有效率，比如char(1)存储Y和N，如果采用单字符集只需要一个字节，而varchar(1)需要2个字节，一个存值一个存长度。

1.3.3 blob和text类型

很大数据数据可以使用blob或者text类型，blob类型使用二进制存储，他没有排序规则或字符集，而text使用字符方式，需要字符集和排序规则。

blob类型：tinyblob，smallblob，blob，mediumblob，longblob
text类型：tinytext，smalltext，text，mediumtext，longtext

MySQL把blob和text当作一个独立对象来存储，当值比较大时，InnoDB会使用外部区域来存储，同时每个值在行内需要1-4个字节存储一个指针，指向外部区域实际的值。

MySQL对blob和text列的排序：它只对每个列的最前max_sort_length个字节做排序，如果只需要排序一小部分可以减少max_sort_length的值，或者使用order by sustring(cloumn,length)。同时MySQL不会对blob和text全部长度的字符串做索引，也不能使用这些索引消除排序。

如果Explain执行计划中的Extra列包含“Using temporary”,说明这个查询使用了隐式临时表

1.3.4 使用枚举代替字符串

枚举列可以把以一些不重复的字符串存储成一个预定义集合，MySQL会根据列表值的胡数量压缩到一个或者两个字节中，MySQL在内部会将值在列表中的位置保存为整数，在表的frm文件中保存"数字-字符串"映射关系的查找表：

mysql> create teable enum_test(
	->  e ENUM('fish','appale',"dog") NOT NULL
	->);
mysql> insert into enum_tes(e) values('fish','dog','appale');

实际存储的时整数，可以在数字上下文环境检索：

mysql> select e + 0 from enum_test;
+-----+
|e + 0|
|    1|
|    3|
|    2|
+-----+

同时，枚举字段是按照内部存的整数而不是定义的字符串来排序的

mysql> select e  from enum_test;
+-------+
| e     |
| fish  |
| appale|
| dog   |
+-------+

1.4 日期和时间类型

MySQL提供两种日期类型：Datetime和Timestamp：

1.4.1 Datetime

Datetime可以保存从1001到9999年，精度为秒，封装到格式为yyyyMMddHHssmm的整数中，无时区，占用8个字节存储空间，MySQL以一种可排序的，无歧义的格式显示为“2021-07-30 14:58:15”。 datetime默认是not null。

1.4.2 timestamp

timestamp保存了自1970年1月1的0点到现在的秒数，使用4个字节存储时间，只能表示1970年到2038年的时间。timestamp存储的时间与时区相关。默认值为当前时间。

原书作者建议：通常使用timestamp，他的空间效率更高，datetime用整数保存的时间不方便处理，所以推荐使用timestamp存储时间。

1.5 位数据类型

从技术上来说，位数据类型都是存储的字符串类型，非常迷惑的类型。

1.5.1 Bit

可以用bit列存储一个或多个ture/false的值，bit(n)表示定义一个n个位的字段，n最大64。InnoBD给每个bit列使用一个足够存储的最小整数类型来存放，比如8位及以下就是1个字节，无法节省空间。

同时MySQL吧bit当作字符串类型，当然在数字计算时，结果时将字符串转为数据，相当迷惑，谨慎使用。

1.5.2 Set

需要保存多个true/false值时可以考虑合并到一个set数据类型，缺点是修改时需要alter table来实现。一般来说，set列无法使用索引。

一种代替set列的方式时使用一个整数包装一些列位，例如可以把8个位包装到一个tinyint中，并按位操作，可以弥补set列的修改问题，但是异常难以查询和理解，谨慎使用。

1.6 选择标识符

给标识列选择合适的数据类型，关联时标识列需要用来比较，也可能在其他表中作为外键使用。

整数通常是标识列最好的选择，速度块，并且可以使用auto_increment。尽量避免使用字符串类型，比较消耗空间，并且不如整数快。同时更要避免随机字符串，随机的字符串会导致数据任意分布在很大空间内，导致insert和select语句变慢：

insert变慢：插入值会随机的写到索引的不同位置出，同时会导致页分裂，磁盘随机访问，已经对于聚簇索引产生碎片。
select语句变慢：逻辑相邻的两条记录被分布在不同的磁盘和索引处。不利于查询。
随机值导致访问的局部性原理失效，整个数据集都是热点数据，那么缓存将无意义。

如果使用UUID时应该去掉‘-’符号，更好的做法是，用unhex()函数将uuid转为16字节的数据，并存储在binary(16)列中。检索时可以用hex()函数转为数字在检索。即便如此，还是不如递增的整数好用。

1.7 特殊类型

一个重要例子是很多人使用varchar(15)列来存储IP值。实际上IP值是32位无符号整数，不是字符串。建议使用无符号整数存储IP值，MySQL提供INET_ATON()和INET_NTOA()函数在数值IP和点分IP之间转换。