4 Schema 与数据类型优化

1 概述

 略。

2 选择优化的数据类型

 MySQL 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。下面是几个原则。

• 1 更小的通常更好。因为他们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少。
• 2 简单就好。简单数据类型的操作通常需要更少的 CPU 周期。例如，整形比字符操作代价更低，因为字符集和校对规则使字符比较比整形比较更复杂；以及应该使用 MySQL 内建的类型而不是字符串来存储日期和时间、应该用整形存储 IP 地址。
• 3 尽量避免 NULL。MySQL 中，可为 NULL 的列使得索引、索引统计和值比较都更复杂。例外的情况是，InnoDB 使用单独的位（bit）存储 NULL 值，所以对于稀疏数据有很好的空间效率，但这一点不适用于 MyISAM。

 在为列选择数据类型时，首先需要确定合适的大类型：数字、字符串、时间等。然后再选择具体的类型，相同大类型的不同子类型数据有时也有一些特殊的行为和属性。如下。

• DATETIME 和 TIMESTAMP 列都可以存储相同类型的数据：时间和日期，精确到秒。然而 TIMESTAMP 只使用 DATETIME 一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESTAMP 允许的时间范围要小很多，有时候它的特殊能力会称为障碍。

 不讨论数据类型的别名，如 INTEGER、BOOL、以及 NUMERIC 都只是别名。

2.1 整数类型

 有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别使用 8、16、24、32、64 位存储空间。整数有可选的 UNSIGNED 属性，表示不允许负值。
 整数计算一般使用 64 位的 BIGINT 整数，即使在 32 位环境也是如此。（一些聚合函数是例外，它们使用 DECIMAL 或 DOUBLE 进行计算）。  MySQL 可以为整数类型指定宽度，例如 INT(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了 MySQL 的一些交互工具用来显示字符的个数。对于存储和计算来说，INT(1) 和 INT(20) 是相同的。

2.2 实数类型

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

• FLOAD 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。
• DECIMAL 类型用于存储精确的小数。

2.3 字符串类型

 MySQL 支持多种字符串类型，每种类型还有很多变种。

2.3.1 VARCHAR 和 CHAR 类型

 VARCHAR 和 CHAR 是两种最主要的存储类型。每个存储引擎存储它们的实现可能不一样。我们按照 InnoDB 或者 MyISAM 来对比。

• 1 VARCHAR。
  • 1.1 用于存储可变长字符串。它比定长类型更节省空间。有一种情况例外是 MySQL 表使用 ROW_FORMAT=FIXED 创建的话，每一行都会使用定长存储，会很浪费空间。
  • 1.2 需要使用 1 或 2 个额外字节记录字符串的长度：如果列的最大长度小于或等于 255 字节，则只使用 1 个字节表示，否则使用 2 个字节。
• 2 CHAR。
  • 2.1 CHAR 是定长的。MySQL 会删除所有的末尾空格，再根据采用空格进行填充以方便比较。

 与 CHAR 和 VARCHAR 类似的类型还有 BINARY 和 VARBINARY，它们存储的是二进制字符串。它填充的是 \0 而不是空格。

2.3.2 BLOB 与 TEXT 类型

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

• 字符类型家族是：TYNYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT。TEXT 是 SMALLTEXT 的同义词。
• 二进制类型家族是：TYNYBOLB、SMALLBLOB、BOLB、MEDIUMBOLB、LONGBLOB。BLOB 是 SMALLBLOB 的同义词。

 MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理；MySQL 对 BLOB 和 TEXT 列进行排序与其他类型是不同：它只对每个列的最前 max_sort_length 字节而不是整个字符串做排序；MySQL 不能将 BLOB 和 TEXT 列全部长度的字符串进行索引，也不能使用这些索引消除排序。

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

 有时候可以使用枚举列代替常用的字符串类型。

• 枚举列实际存储为整数，而不是字符串。
• 枚举字段是按照内存存储的整数而不是定义的字符串进行排序的。枚举值排序是固定的，如过通过显示排序 order by field(x1, x2) 排序则无法利用索引消除排序。
• 枚举列进行查找时有一些开销。某些查询会变快，某些会变慢，这里不深入探讨了。

 转换列为枚举型还有下面两个好处。

1. 根据 SHOW TABLE STATUS 命令输出结果中 DATA_LENGTH 列的值，转换为 ENUM 可以缩小表的大小。
2. 如果是 InnoDB 的表，如果表上有其他索引，减少主键大小会使非主键索引也变得更小。

2.4 日期和时间类型

 MySQL 可以使用许多类型来保存日期和时间值，例如 YEAR 和 DATE。
 MySQL 提供两种相似的日期类型：DATETIME 和 TIMESTAMP。

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

2. TIMESTAMP。 保存了 1970 年 1 月 1 日午夜以来的秒数，它和 UNIX 时间戳相同。TIMESTAMP 只使用了 4 个字节的存储空间，只能表示从 1970 年到 2038 年，与时区有关。TIMESTAMP 也有 DATETIME 没有的特殊属性。默认情况下，如果插入时没有指定第一个 TIMESTAMP 列的值，MySQL 则设置这个列的值为当前时间。在插入一行记录时，MySQL 默认也会更新第一个 TIMESTAMP 列的值。TIMESTAMP 列默认为 NOT NULL，这也和其他的数据类型不一样。

 通常应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

2.5 位数据类型

 MySQL 有少数几种存储类型使用紧凑的位存储数据。

• 1 BIT。
  • 1.1 存储一个或多个 true/false 值。BIT 的行为因存储引擎而异。不深入探讨了，应当谨慎使用 BIT 类型。
• 2 SET。
  • 2.1 如果需要保存很多 true/false 值，可以考虑合并这些列到一个 SET 数据类型。
• 3 在整数列上进行按位操作。
  • 3.1 略。不建议使用。

2.6 选择标识符

 为标识列（如主键）选择合适的数据类型非常重要，通过需要考虑这些内容。

• 标识列于其他值进行比较。
• 通过标识列寻找其他列。
• 标识列也可能在另外的表中作为外键使用。

 以下是一些建议。

1. 整数类型。 整数通常是标识列最好的选择，因为它们很快并且可以使用 AUTO_INCREMENT。
2. ENUM 和 SET 类型。 对于标识列来说，ENUM 和 SET 类型通常是一个糟糕的选择。ENUM 和 SET 列适合存储固定信息，例如有序的状态、产品类型、人的类别。
3. 字符串类型。 应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。MyISAM 默认对字符串使用压缩索引，这会导致查询慢得多。

 对于完全“随机”的字符串也需要多加注意，例如 MD5()、SHA1()或者 UUID() 产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这回导致 INSERT 以及一些 SELECT 语句变得很慢。

• 因为插入值会随机地写到索引的不同位置，所以使得 INSERT 语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
• SELECT 语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
• 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。如果整个数据集都一样的“热”，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。

 如果存储 UUID 值，则应该移除 “-” 符合。或者更好的做法是，用 UNHEX() 函数转换 UUID 值为 16 字节的数字，并且存储在一个 BINARY(16) 列中。UUID 值虽然分布也不均匀，但还是有一定顺序。尽管如此，但还是不如递增的整数好用。

2.7 特殊类型数据

 某些类型的数据并不直接与内置类型一致。低于秒级精度的时间戳就是一个例子。
 另一个例子是一个 IPv4 地址。人们经常使用 VARCHAR(15) 列来存储 IP 地址。其实应该用无符号整数存储 IP 地址（IP 地址实际上是 32 位无符号整数，不是字符串）。

3 MySQL schema 设计中的陷阱

 简单列举下。

1. 太多的列。MySQL 的存储引擎 API 工作时需要在服务器和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。
2. 太多的关联。MySQL 限制了每个关联操作最多只能有 61 张表。应该避免“实体-属性-值”（EAV）设计模式，EAV 数据库需要许多自关联。
3. 全能的枚举。防止过度使用枚举。
4. 变相的枚举。变相使用枚举容易导致混乱。
5. 非此发明（NOT Invent Here）的 NULL。如避免使用 NULL，用 -1 代替，但是用 -1 代表一个未知的整数，可能导致代码复杂很多，并容易引入 BUG。

4 范式和反范式。

 概念略。

4.1 范式的优点和缺点

 范式的好处是。

• 范式化的更新操作通常比反范式化要快。
• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
• 很少有多余的数据意味着检索列数据时更少需要 DISTINCT 或者 GROUP BY 语句。

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

4.2 反范式化的优点和缺点

 反范式化的 schema 因为所有数据都在一张表中，可以很好地避免关联，即使表没有索引--是全表扫描。当数据比内存大时，这可能比关联要快得多，因为避免了随机 I/O。
 单独的表也能使用更有效的索引策略。

4.3 混用范式化和反范式化

 我们实际中一般是混用范式和反范式化。MySQL 5.0 和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。

5 缓存表和工作表

1. 缓存表。  表示存储那些可以比较简单地从 schema 其他表（但是每次获取的速度比较慢）获取数据的表（例如，逻辑上冗余的数据）。保存衍生的冗余数据，容许了少量的脏数据。
    其对优化搜索和检索查询语句很有效。比如将表导入 MyISAM （或 专门的搜索系统，例如 Lucene 或者 Sphinx 搜索引擎），来加速各种类型的查询。

2. 汇总表。  保存的是使用 GROUP BY 语句聚合数据的表。因为实时计算统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在某些特定的索引上才能有效允许，而这些特定索引一般会对 UPDATE 操作有影响，所以一般不希望创建这样的索引。  典型的是计算最活跃的用户，每小时聚合查询一次，缺点是计数器并不是 100% 准确。

5.1 物化视图

 许多数据库管理系统（例如 Oracle 或者微软 SQL Server）都提供了一个被称为物化视图的功能。
 物化视图是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。MySQL 并不原生支持物化视图。

5.2 计数器表

 针对缓存一个用户的朋友数，文件下载次数等，创建一张单独的表存储计数器通常是一个好主意，这个表就是一个计数器表。
 计数器表原生只有一个 count 字段。我们可以预处理为如 100 行，然后每次更新随机给计数器更新，这样可以提供并发量（提高了 100 倍）；如果需要每天重新计数，可以再添加时间维度；也可以将其他的行都汇总到第 0 行（或槽），再将其他行的数据都删除。

6 加快 ALTER TABLE 操作的速度

 MySQL 的 ALTER TABLE 操作的性能对大表来说是个大问题。MySQL 执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这个操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。
 比如修改一个表的默认值，下面是很慢的方式，它会拷贝到整张表到一张新表。

mysql> ALTER TABLE sakila.film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

 下面的两种方式是比较快的方式。

1. 只修改 .frm 文件。
2. 执行 ALTER COLUMN。

mysql> ALTER TABLE sakila.film ALTER COLUMN rental_duration DEFAULT 5;

6.1 只修改 .frm 文件

 如果愿意冒一些风险，可以让 MySQL 做一些其他类型的修改而不用重建表。
 下面这些操作是有可能不需要重建表的。

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

 具体操作步骤略。

6.2 快速创建 MyISAM 索引

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

mysql> ALTER TABLE test.load_data DISABLE KEYS;
-- load data
mysql> ALTER TABLE test.load_data ENABLE KEYS;

 但是这种方法对唯一索引无效，因为 DISABLE KEYS 只对非唯一索引有效。MyISAM 会在内存中构造唯一索引，并且为载入的每一行检查唯一性。一旦索引的大小超过了有效内存大小，载入操作就会变得越来越慢。
 具体操作步骤略。

参考文献

• [高性能 MySQL]