第4章 Schema与数据类型优化

本章关注的是MySQL数据库的设计，主要介绍的是MySQL数据库设计与其他关系型数据库管理系统的区别

4.1 选择优化的数据类型

有以下几个简单的原则：

• 更小的通常更好，一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少。
• 简单就好，举两个栗子，一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间，另外一个是应该用整型存储IP地址
• 尽量避免NULL，如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂

4.1.1 整数类型

有两种类型的数字：整数（whole number）和实数（real number）。如果存储整数，可以使用这几种整数类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。他们可以存储的值得范围从-2^(N-1)到2^(N-1)-1，其中N是存储空间的位数。
整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍
然而，整数计算一般使用64位的BIGINT整数

4.1.2 实数类型

实数是带了小数点的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。
DECIMAL类型用于存储精确的小数。允许最多65个数字，因为DECIMAL只是一种存储格式，在计算中DECIMAL会转换为DOUBLE类型 尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据

4.1.3 字符串类型

VARCHAR

用于存储可变长字符串，它比定长类型更节省空间，因为它仅使用必要的空间。VARCHAR需要使用1或2个额外字节记录字符串的长度。UPDATE时可能使行变得比原来更长，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内

CHAR

CHAR是定长的：MySQL总是根据定义的字符串长度分配足够的空间。CHAR适合存储很短的字符串，或者所有值都接近同一个长度

BINARY和VARBINARY

它们存储的是二进制字符串。二进制比较的优势不仅仅体现在大小写敏感上。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符串比较简单很多，所以也就更快。

BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
与其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。 BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序或字符集，而TEXT类型有字符集和排序规则

使用枚举（ENUM）代替字符串类型

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

枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。VARCHAR 关联ENUM，会比VARCHAR关联VARCHAR，快很多。另一个，让表的大小缩小

4.1.4 日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。

DATETIME

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

TIMESTAMP

TIMESTAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同，只使用4个字节的存储空间，它的范围比DATETIME小得多：只能表示从1970年到2038年。MySQL提供FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。TIMESTAMP显示的值依赖于时区。MySQL服务器、操作系统、以及客户端连接都有时区设置。TIMESTAMP默认设置值为当前时间

除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高

4.1.5 位数据类型

BIT，最大长度是64个位。MySQL把BIT当作字符串类型，而不是数字类型。
SET，如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。

4.1.6 选择标识符

当选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较。例如，MySQL在内部使用整数存储ENMU和SET类型，然后在做比较操作时转换为字符串。

整数类型

整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT。
UUID()生成的值与加密散列函数例如SHA1()生成的值有不同的特征：UUID值虽然分布也不均匀，但还是有一定顺序的。尽管如此，还是不如递增的整数好用

4.1.7 特殊类型数据

某些类型的数据并不直接与内置类型一致。低于秒级精度的时间戳就是一个例子。另一个例子是一个IPv4递增。人们经常使用VARCHAR(15)列来存储IP地址。然而，它们实际上是32位无符号整数。应该用无符号整数存储IP地址。MySQL提供INET_ATON()和INET_NTOA（）函数在这两种表示方法之间转换

4.2 MySQL schema设计中的陷阱

• 太多的列，MySQL的存储引擎API工作时需要在服务层和存储引擎层之间通过行缓存格式拷贝数据，然后在服务层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。
• 太多的关联，所谓的“实体-属性-值”（EAV）设计模式是一个常见的糟糕的设计模式，尤其是在MySQL下不能靠谱地工作。如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联
• 全能的枚举，注意防止过度使用枚举（ENUM）
• 变相的枚举
• 非此发明的NULL，避免使用NULL，建议尽可能考虑替代方案

4.3 范式和反范式

4.3.1 范式的优点和缺点

• 范式化的更新操作通常比反范式化更快
• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句
  范式化设计的schema的缺点是通常需要关联。不但代价昂贵，也可能使一些所以策略无效。

4.3.2 反范式的优点和缺点

优点是所有数据都在一张表里，可以很好的避免关联。如果不需要关联表，对大部分查询最差的情况——即使没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O。

4.3.3 混用范式化和反范式化

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在MySQL5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。

4.4 缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表。

4.4.1 物化视图

物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略更新和刷新。MySQL并不原生支持物化视图，然而，使用Justin Swanhart的开源工具Flexviews，也可以实现物化视图。Flexviews比完全自己实现的解决方案要更精细，并且提供了很多不错的功能使得可以更简单地创建和维护物化视图。由下面这些部分组成：

• 变更数据抓取（Change Data Capture，CDC）功能，可以读取服务器的二进制日志并且解析相关行的变更
• 一系列可以帮助创建和管理视图的定义的存储过程
• 一系列可以应用变更到数据库中的物化视图的工具

先写出一个SELECT语句描述想从已经存在的数据库中得到的数据，这可能包含关联和聚合（GROUP BY）。Flexviews中有一个辅助工具可以转换SQL语句到Flexviews的API调用。Flexviews会做完所有的脏活、累活：监控数据库的变更并且转换后用于更新存储物化视图的表。现在应用可以简单的查询物化视图来提花查询需要检索的表

4.4.2 计数器表

创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小且快。独立的表可以帮助避免查询缓存失效

4.5 加快 ALTER TABLE 操作的速度

MySQL执行大部分修改表结构的操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此

一般而言，大部分 ALTER TABLE 操作将导致MySQL服务中断。对常见的场景，能使用的技巧只有两种：

• 一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；
• 另外一种技巧是“影子拷贝”。影子拷贝的技巧是用邀请的表结构创建一张与原表无关的新表，然后通过重命名和删表操作交换两张表。

不是所有的ALTER TABLE操作都会引起表重建有两种方法：

• 可以直接修改表的.frm文件而不需要改动表本身。然而MySQL还没有采用这种优化的方法，所有的MODIFY COLUMN操作都将导致表重建
• 另外一种方式是通过ALTER COLUMN操作来改变列的默认值：

4.5.1 只修改.frm文件

下面这些操作是有可能不需要重建表的：

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

基本的技术是为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那表的.frm文件

4.5.2 快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引
也可以使用ALTER TABLE的骇客方法来加速这个操作：

4.6 总结

• 尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计
• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值
• 尽量使用相同的数据类型存储相似或 相关的值，尤其是要在关联条件中使用的列
• 注意可变长字符串，其在临时表的排序时可能导致悲观的按最大长度分配内存
• 尽量使用整数定义标识列
• 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度
• 小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT
  ALTER TABLE在部分情况下，会锁表并且重建整张表。对于大部分场景，必须使用其他更常规的方法，例如在备机执行ALTER并在完成后把它切换为主库。