高性能 MySQL 之 schema 设计与管理

选择更合适的数据类型

在表的设计之初，数据类型的选择对于性能的影响至关重要，书中给了三点建议：

更小：尽量使用能够正确存储和表示数据的最小数据类型；

简单：例如，整型数据比字符型数据的比较操作代价更低，将日期和时间存储为MySQL的内置类型而不是字符串类型，用整型数据存储IP地址，都能降低操作代价；

尽量避免存储NULL：如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。当然这是视情况而定的，通常把可为NULL的列改为NOT NULL带来的性能提升比较小。

整数类型

有两种类型的数字：整数（whole number）和实数（real number，带有小数部分的数字）。先来看整数类型。

如果存储整数，可以使用这几种整数类型：TINYINT、SMALLINT、MEDIUMINT、INT或BIGINT。分别使用8、16、24、32和64位存储空间。可以存储的值的范围从到，其中N是存储空间的位数。整数类型有可选的UNSIGNED属性，表示不允许负值，可以使正数的上限提高一倍。例如，TINYINT UNSIGNED可以存储的值的范围是0～255。

需要明白的是：MySQL的存储和计算是不绑定的，你只能选择存储类型，计算时为了方便统一，会使用不一定和存储类型一样的类型。例如，整数计算通常使用64位的BIGINT整数，一些聚合函数是例外，它们使用DECIMAL或DOUBLE进行计算。

实数类型

实数是带有小数部分的数字。一般使用FLOAT和DOUBLE，这两种类型用于存储近似数值，适用于不需要绝对精度的场景。分别使用32和64位存储空间。MySQL 也支持精确类型，DECIMAL用于存储定点数（精确的十进制数），特别适用于需要高精度的场景，例如，DECIMAL(10, 2)表示最多10位数字，其中2位是小数。还可以使用DECIMAL存储比BIGINT还大的整数。

与整数类型一样，你只能选择存储类型；MySQL会使用DOUBLE进行浮点类型的内部计算。

实数类型的优化建议：由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才使用DECIMAL，例如存储财务数据。但在一些大容量的场景，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据并精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

MySQL支持多种字符串数据类型，每种类型还有许多变体。每个字符串列可以有自己的字符集和该字符集的排序规则集。

VARCHAR 和 CHAR

先来看看VARCHAR和CHAR值通常是如何存储在磁盘上的（基于InnoDB存储引擎）。下面是关于两种类型的一些比较。

VARCHAR用于存储可变长度的字符串，是最常见的字符串数据类型。它比固定长度的类型更节省空间，因为它仅使用必要的空间。VARCHAR需要额外使用1或2字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1字节表示，否则使用2字节。假设采用latin1字符集，一个VARCHAR(10)的列需要11字节的存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2字节存储长度信息。

VARCHAR节省了存储空间，所以对性能也有帮助，但是在某些情况下也会影响性能：由于行是可变长度的，在更新时可能会增长，这可能会导致额外的工作，如果行的增长使得原来的页无法容纳更多内容，InnoDB可能需要分割页面来容纳行，即创建一个新的页，并将原页中的部分数据行移动到新页中，页分割后，相关的索引也需要更新，以反映数据行的新位置。

这带来潜在的性能开销：页分割在读写时需要涉及多个页，这会导致额外的磁盘I/O操作，还可能导致数据在物理上不连续存储，增加了磁盘的碎片化程度，从而影响查询性能。页分割过程中可能会加剧锁竞争，影响并发性能。

相比之下，CHAR则更加简单，因为它是固定长度的：MySQL总是为定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL删除所有尾随空格。如果需要进行比较，值会用空格填充。CHAR适合存储非常短的字符串，或者适用于所有值的长度都几乎相同的情况。例如，对于用户密码的MD5值，CHAR是一个很好的选择，它们的长度总是相同的。

相比之下，VARCHAR不会去掉尾随空格：

对于经常修改的数据，CHAR也比VARCHAR更好，因为固定长度的行不容易出现碎片。对于非常短的列，CHAR也比VARCHAR更高效；设计为只保存Y和N的值的CHAR(1)在只使用1字节，但VARCHAR(1)需要2字节，因为还有一个记录长度的额外字节。

BLOB 和 TEXT

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，它们之间的区别是：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通常存储很长的数据，排序时消耗资源较大，MySQL对它们的排序与其他类型不同：它只对这些列的最前max_sort_length字节而不是整个字符串做排序。如果只需要按前面少数几个字符排序，可以减小max_sort_length服务器变量的值。此外，MySQL不能将BLOB和TEXT数据类型的完整字符串放入索引，也不能使用索引进行排序。

在优化上，可以使用ENUM（枚举）列代替常规的字符串类型。ENUM列可以存储一组预定义的不同字符串值。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到1或者2字节中。在内部会将每个值在列表中的位置保存为整数。这里有一个例子：

上面三行实际上存储的是整数，而不是字符串。可以通过在数值上下文中检索看到这个双重属性：

另一个令人惊讶的事情是，ENUM字段是根据内部整数值排序的，而不是根据字符串本身：

由于MySQL将每个枚举值存储为整数，并且必须进行查找以将其转换为字符串表示，因此ENUM列有一些开销。这些开销通常可以被ENUM列的小尺寸所抵消。

但在涉及表连接时，却有不同的情况。为了说明这一点，我们对表联接的速度进行了基准测试。该表有一个相当大的主键：

service列包含5个不同的值，平均长度为4字符，method列包含71个值，平均长度为20字符。

由于这两个列的值是固定范围内的，可以考虑设计成枚举类型。我们复制一下这个表，并将service列和method列转换为ENUM类型，如下所示：

然后我们测试用主键列进行联接操作的性能，下面是所使用的查询语句：

我们只需将这两个表进行不同排列组合的联接，得到如下测试结果：

从上面的结果可以看到，当把列都转换成ENUM以后，联接变得很快（2.6→3.5）。

但是当VARCHAR列和ENUM列进行联接时则慢很多（2.6→1.7、1.8）。这部分性能的损失就是来自两种类型之间的转换。

在本例中，只要不是必须让ENUM和VARCHAR列进行联接，那么将这些列转换为ENUM是一个好主意。

若涉及到联接场景，通常的设计实践是使用带有整数主键的“查找表”，以避免在联接中使用字符串。

所谓“查找表”，就是将枚举类型抽离出来独自成表，通常是（id，value）的结构，这样做的好处是同样可以使service列仍可以保存INT类型减小空间，而且查找表还确保了数据的一致性，避免了在多个地方重复存储相同的字符串值，也易于维护。

然而，将列转换为ENUM类型还有另一个好处：根据SHOW TABLE STATUS输出结果中的Data_length列，发现将这两列转换为ENUM会使表变小约三分之一。在某些情况下，即使必须将ENUM列联接到VARCHAR列，这也可能是有益的。而且，转换后主键也只有原来的一半大小了，因为这是InnoDB表，如果表中有其他索引，减少主键大小也会使这些非主键索引小得多。

日期和时间类型

MySQL中有很多数据类型用以支持各种各样的日期和时间值，比如YEAR和DATE。MySQL可以存储的最小时间粒度是微秒。不存在哪一种是最佳选择的问题，唯一的问题是，当需要同时存储日期和时间时该怎么做。

MySQL提供了两种非常相似的数据类型来实现这一需求：DATETIME和TIMESTAMP。对于许多应用程序来说，两者都可以，但在某些场景，它们各有优点。

DATETIME用8字节保存从1000年到9999年的大范围数值。它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无关。MySQL以可排序、无歧义的格式显示DATETIME值（2008-01-16 22：37：08）。

TIMESTAMP用4字节存储自1970年1月1日格林尼治标准时间（GMT）以来经过的秒数——与UNIX时间戳相同。所以它的范围比DATETIME小得多：只能表示从1970年到2038年1月19日。MySQL提供FROM_UNIXTIME()函数来将UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数将日期转换为UNIX时间戳。时间戳显示的值依赖于时区。MySQL服务器、操作系统和客户端连接都有时区设置。

此外，TIMESTAMP列在默认情况下为NOT NULL，这也和其他的数据类型不一样。当插入或更新一行记录时没有指定第一个TIMESTAMP列的值，MySQL会将该列的值设置为当前时间。

位压缩数据类型

位压缩类型，不管底层存储和处理方式如何，从技术上来说都是字符串类型。

可以使用BIT列存储一个或多个true/false值。BIT（1）定义一个包含1位的字段，BIT（2）存储2位的字段，依此类推；BIT列的最大长度为64位。InnoDB将每一列存储为足够容纳这些位的最小整数类型，所以使用BIT列不会节省任何存储空间。

MySQL在处理时会将BIT视为字符串类型，而不是数字类型。当检索BIT（1）的值时，结果是一个包含二进制值0或1的字符串，而不是ASCII码的“0”或“1”。但是，如果在数字上下文中检索该值，则会将BIT字符串转换为数字。如果需要将结果与另外的值进行比较，一定要记得这一点。

例如，如果将值b'00111001'（二进制数相当于57）存储到BIT（8）列中并检索它，则将得到包含字符码为57的字符串。这恰好是“9”的ASCII字符代码。但在数字上下文场景中，得到的将会是数字57：

这可能会让人非常困惑，因此我们建议谨慎使用BIT类型。对于大多数应用来说，最好避免使用这种类型。

如果想在1位的存储空间中存储true/false值，另一个方法是创建一个可为空的CHAR（0）列。该列可以存储空值（NULL）或长度为零的值（空字符串）来代表true/false值。这在实践中是可行的，但可能对使用数据库中该数据的其他人来说是难以理解的，并且使编写查询变得困难。除非你非常注重节省空间，否则建议使用TINYINT。

如果需要存储多个true/false值，可以考虑使用MySQL原生的SET数据类型，可以将多列组合成一列，这在MySQL内部是以一组打包的位的集合来表示的。这样可以更有效地利用存储空间，MySQL具有FIND_IN_SET()和FIELD()等函数，使其易于在查询中使用。

一个封装位的应用示例是保存权限的访问控制列表（ACL）。每个位或SET元素代表一个值，例如CAN_READ、CAN_WRITE或CAN_DELETE。如果使用SET列，可以让MySQL在列定义中存储位到值的映射：