MySQL读书学习笔记（二）——schema与数据类型优化

2.1 选择优化的数据类型

更小

应尽量使用可以存储数据的最小数据类型。更小的数据通常更快，因为它们占更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。但要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。

简单

简单数据类型的操作通常需要更少的CPU周期。

避免NULL

很多表都包含可为null的列，即使应用程序不需要保存null时也是如此，这是因为可为null是列的默认属性。通常情况下最好指定列为not null，除非真的需要存储null。

如果查询中包含可为null的列，对MySQL来说更难优化，因为可为null的列使索引，索引统计和值比较更复杂。可为null的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为null的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至很可能导致固定大小的索引变成可变大小的索引。

2.1.1 整数类型

整数有以下几种类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8，16，32，64位存储空间。它们可以存储的范围从-2^（n-1）到2^（n-1）-1，其中N是存储空间的位数。

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提供一倍。

有符号与无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际的情况选择合适的类型。

2.1.2 实数类型

实数是带小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

2.1.3 字符串类型

VARCHAR和CHAR类型

VARCHAR类型用于存储可变字符串，是最常见的字符串类型。它比定长类型更节省空间，因为它仅使用必要的空间。有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都使用定长存储，这样很浪费空间。

VARCHAR需要使用一至两个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只能使用一个字节表示，否则使用两个字节。

VARCHAR节省了存储空间，所以对性能也有帮助。但是由于行是变长的，在update时可能变得比原来更长，这就需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据的位置上更新数据。

以下情况适合使用VARVHAR：字符串列的最大长度比平均长度大很多；列的更新很少，碎片不是问题；使用了像UTF-8这样的字符集，每个字符都使用不同的字节数进行存储。

CHAR

CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。

BOLB和TEXT类型

都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

使用枚举代替字符串类型

可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时会非常紧凑，会根据列表值的数量压缩到一个或两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

2.1.4 日期和时间类型

DATETIME

这个类型能保存大范围的值。从1001年到9999年，精度为s。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。

默认情况下，MySQL以一种可排序、无歧义的格式显示DATATIME值，例如“2018-2-16 22：55：08”。这是ANSI标准定义的日期和时间表示法。

TIMESTAMP

保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同。使用4个字节的存储空间，因为它的范围比DATETIME小得多：只能表示从1970-2038年。MySQL提供了FROM_UNIXTIME（）函数把Unix时间戳转换为日期，并提供UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。

TIMESTAMP显示的值也依赖于时区。

除非特殊要求，通常应该尽量使用TIMESTAMP，因为它比DATATIME空间效率更高。有时候回将Unix时间截存储为整数值，但这不会带来任何收益。用整数保存时间截的方式不方便处理，不推荐这样。

要存储比秒更小粒度的日期和时间值，MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式：可以使用BIGINT类型存储微秒级别的时间截，或者使用double存储秒之后的小数。

2.1.5 位数据类型

BIT

可以使用BIT列在一列中存储一个或多个true/false值。BIT（1）定义一个包含单个位的字段，BIT列的最大长度是64个位。

MySQL把BIT当字符串类型，而不是数字类型。

SET

如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。这样就有效利用了存储空间，并且MySQL有像FIND_IN_SET()和FIELD（）这样的函数，方便在查询中使用。它的主要缺点是改变列的定义的代价较高：需要ALTER TABLE，这对大表来说是非常昂贵的操作。一般来说，也无法在SET列上通过索引进行查找。

在整数列上进行按位操作

一种替代SET的方式是使用一个整数包装一系列的位。

比起SET，这种办法的主要好处在于可以不使用ALTER TABLE改变字段代表的“枚举”值，缺点是查询语句更难写，并更难理解。

2.1.6 选择标识符

为标识列选择选择合适的数据类型非常重要。一般来说更有可能用标识列与其他值进行比较，或者通过标识列寻找其他列。标识列有可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中对应列一样的类型。

当选择标识列的类型时，不仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较。

一旦选择了一种类型，要确保在所有关联表中都使用同样的类型。类型之间需要精确匹配，包括像UNSIGNED的属性。混用不同数据类型可能会导致性能问题，即使没有性能影响，在比较操作时隐式类型可能导致很难发现的错误。这种错误可能会很久以后才突然出现，那时候可能已忘记在比较不同数据类型。

在可以满足值的范围需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。

整数类型

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

ENUM和SET类型

它们是糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能没问题。ENUM和SET列适合存储固定信息。

字符串类型

应尽量避免使用，因为它很消耗空间，并且比整数慢。

2.2 Schema设计中的陷阱

太多的列

MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。

太多的关联

“实体-属性-值”（EAV）设计模式是一个常见的糟糕设计模式，尤其在MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表，但EAV数据库需要许多自关联，不少EAV数据库都超过了这个限制。事实上在许多关联少于61张表的情况下，解析和优化查询的代价也会成为MySQL的问题。

全能的枚举

防止过度使用枚举，比如应该用整数作为外键关联到字典表或查找表来查找具体值。

变相的枚举

ENUM列运行在列中存储一组定义值中的单个值，SET列则允许在列中存储一种定义值中的一个值或多个值。有时候这可能比较容易导致混乱。

非此发明（Not Invent Here）的NULL

之前提到尽量避免使用NULL，可以使用0，某个特殊值，或者空字符串代替。

但确实要表示未知值时也可以使用，在一些场景中，使用NULL会比某些常数好。

2.3 范式与反范式

2.3.1 范式的优缺点

建议对schema进行范式化设计，尤其是写密集的场景。范式有以下好处：

更新更快。

只有很少或没有重复数据，只需要修改更少的数据。

表通常更小，可以更好地放在内存里，执行操作会更快。

检索列表数据时更少需要DISTINCT或GROUP BY语句。

缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。

2.3.2 反范式的优点与缺点

反范式的schema因为所以的数据都在一张表中，可以很好地避免关联。

如果不需要关联表，则对大部分查询最差的情况（即使表没有使用索引）是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O。

2.3.3 混用

在实际应用中经常需要混用，可能使用部分范式化的schema，缓存表以及其他技巧。

2.4 缓存表和汇总表

有时提升性能最好的方法时在同一张表里保存衍生的冗余数据。有时也需要创建一张完全独立的汇总表或缓存表。如果能容许少量的脏数据，这是非常好的方法，但有时没有选择的余地。

“缓存表”表示可以存储那些比较简单从schema其他表获取数据的表。“汇总表”则使用的是group by语句聚合数据的表。也叫累积表。

2.4.1 物化视图

许多数据库管理系统都提供一个物化视图的功能。物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。

2.4.2 计数器表

如果应用在表里保存计数器，则在更新计数器时可能遇到并发问题。计数器表在Web应用中很常见，可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张独立的表存储计数器较好，这样可使计数器表小且快。

2.5 加快ALTER TABLE

ALTER TABLE对大表来说性能是个问题。MySQL执行大部分修改表结构操作的方法是用新结构创建一个空表，从旧表中查出所有数据插入新表。然后删除旧表。这个操作可能花费很长时间，如果内存不足而表又很大，索引很多的时候尤其如此。

2.5.1 只修改.frm文件

修改.frm文件的方法很快，但MySQL有时候也会在没必要的时候也重建表。如果可以有风险，可以让MySQL做一些其他类型的修改而不用重写表。

下列操作不用重写表：

移除一个列的AUTO_INCREMENT属性。

增加、移除或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字符串。

2.5.2 快速创建MyISAM表

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引。