《高性能MySQL》（三）Schema与数据类型优化

一. 选择优化的数据类型

• 尽量使用能正确存储数据的最小数据类型；
• 整型比字符串操作代价更低，字符比较的字符集和校对规则更复杂，如时间等应该用数字存储；
• 尽量避免NULL，可为NULL的列需要更多的空间，索引构建更复杂，所以需要创建索引的列尽量是NOT NULL；
• 相同类型的数据，但长度范围不同、精度不同、需要的物理空间也不同，例如 DATETIME 和 TIMESTAMP 都存储时间和日期，精确到秒，TIMESTAMP只会用DATETIME一半的空间
• MySQL的数字类型分整数类型和实数类型，其中 DECIMAL 支持高精度计算，如 DECIMAL(18, 9) 小数点两边将各存9个数字，小数点占一个字节，总共占9个字节。内部浮点计算统一使用 DOUBLE ，DECIMAL 只是存储类型。DECIMAL 会需要额外的空间和计算开销
• 字符串类型：
  • VARCHAR：可变长字符串，仅使用必要空间（除非ROW_FORMAT=FIXED），使用一个或两个字节存储字符串长度。没有银弹，节省空间的带来的是UPDATE需要额外的开销（页已满InnoDB会分裂页使新行可以放入页内）
  • CHAR：定长字符串，根据定义的长度分配足够的空间。适合存储定长或经常修改的字符串，因为定长类型不容易产生碎片；短列如CHAR(1)只需一个字节，而VARCHAR(2)需要两个字节，一个字节存放长度。
• BLOB和TEXT：分别采用二进制和字符串存储较大数据。
  • 不同类型家族：TINYBLOB、SMALLBLOB / BLOB、MEDIUMBLOB、LONGBLOB 和 TINYTEXT、SMALLTEXT / TEXT、MEDIUMTEXT、LONGTEXT
  • 当数据较大时，MySQL会使用外部区域存放值，行内需要为每个值放1~4个字节的指针。
  • 排序时只会对前 max_sort_length 个字节排序，也可以使用 ORDER BY SUSTRING(column, length) 。
• 日期和时间类型：
  • DATETIME：1001年到9999年，精度为秒，格式为YYYYMMDDHHMMSS的整数，与时区无关，占用8个字节的存储空间，时间值的显式会因为时区不同而不同。
  • TIMESTAMP：1970年到2038年，保存从1970年1月1日午夜以来的秒数（格林尼治时间），只使用4个字节的存储空间，时间值的显式会因为时区不同而不同。
• 位数据类型：
  • BIT：旧版本BIT等价于TINYINT，BIT(1)存放一个位，最大为64位。BIT属于字符串类型，不建议使用。
  • SET
• 特殊类型：
  • 低于秒精度的时间戳
  • IPv4地址，经常用 VARCHAR(15) 存放IP地址，实际上本质是32位无符号整数，小数点只是方便阅读，使用无符号整数存放，MySQL还提供了 INET_ATON() 和 INET_NTOA() 来在二者间转换。

二. 选择合适的标识列

标识列要常用于比较（如关联操作）、作为查询条件、外键等。不同表的相同标识列要保持数据类型一致，并且在选择数据类型时考虑MySQL如何执行计算和比较（如ENUM和SET使用整数存储，比较时转换为字符串）。

• 整数类型：最适合的标识列类型，效率高并且可以 AUTO_INCREMENT 。
• ENUM和SET类型：糟糕的选择。
• 字符串类型：消耗空间，并且效率差于数字，特别是一些随机生成的字符串会任意分布在很大的空间内，导致INSERT或SELECT执行很慢。存储UUID应该去掉 - ，直接用 UNHEX() 函数转换为16字节数字存放在 BINARY(16) 列中，检索时使用 HEX() 格式化为十六进制格式。
  • 插入值随机写到索引的不同位置，使INSERT变慢，会导致页分裂、磁盘随机访问、对于聚簇存储引擎产生聚簇索引碎片。
  • 逻辑上相邻的行分布在磁盘和内存的不同地方，使SELECT变慢。
  • 随机值导致缓存赖以工作的访问局部性原理失效，整个数据集都一样“热”，缓存带来的好处就消失了。

三. 常见设计陷阱

1. 太多的列：MySQL存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，在服务器层将缓冲转码为各个列，将编码后的内容转码为各个行的数据格式操作代价较高，所以尽量避免上千个属性这种极端情况。
2. 太多的关联：期望查询能够快速的执行且并发性好，单个查询不要超过12个表关联。
3. 避免滥用枚举：修改枚举中值会进行一次 ALTER TABLE 操作。

四. 范式和反范式

范式化的数据库中，事实数据仅出现一次；反范式化的数据库中，信息是冗余的。

范式的优点：

• 更新操作更快；
• 很少有重复数据，需要修改的数据更少；
• 表空间更小，在内存中执行更快；
• 很少有多余数据，所以更少需要 DISTINCT 或 GROUP BY 语句。

范式的缺点：

• 常常需要关联，不但代价昂贵，也可能使索引策略无效。

反范式的优点：

• 数据都在一张表，所以很少关联；
• 全表扫描基本是顺序I/O，不需要关联表避免了随机I/O；
• 可以构建有效的索引策略；

真实的开发中很少会极端的遵循范式或反范式，而是混用二者；常见如复制或缓存，在不同的表存储相同的列，冗余的字段往往有其意义，比如排序需求、避免关联、避免多次查询等等。

五. 缓存表和汇总表

虽然冗余数据能够很好的提升性能，但有时还是需要创建一张完全独立的汇总表或缓存表。

一般缓存表用来存放那些可以简单获取但获取速度较慢的数据（逻辑上冗余的数据）；汇总表则存放使用 GROUP BY 语句聚合的数据。

例如，一个网站需要计算之前24小时发送的消息数，可以每小时生成一张汇总表（Redis应该是更好的解决方案），这样比实时维护计数器要高效的多，但并不精确。这种方案要远远快于统计message中所有行，实时计算统计值需要扫描表中的大部分数据，需要的索引也一般会影响到UPDATE操作（所以一般不希望创建这类索引）。

缓存表可以采用不同的存储引擎，比如主表是InnoDB，缓存表则使用MyISAM，这样可以得到更小的索引占用空间，并且可以做全文搜索。

使用缓存表和汇总表需要决定是实时维护数据还是定期重建，重建需要保证数据在操作时依然可用，所以需要“影子表”实现，即根据真实表创建一张影子表，完成建表后，通过一个原子性的重命名操作切换影子表和原表。

更快的读，更慢的写，为了增加读查询的速度，会经常建一些额外索引、增加冗余列，甚至创建缓存表和汇总表。这些都会导致写查询变慢，开发难度提升，但能显著提高读操作的性能。

六. 提高 ALTER TABLE 操作的速度

ALTER TABLE 对于大表会执行很慢，大部分修改表操作会按新结构创建一个新的空表，从旧表查出所有数据插入新表，最后删除旧表。大部分 ALTER TABLE 操作会导致MySQL服务中断。

常规场景的技巧：

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

不是所有 ALTER TABLE 操作都要重建表，如下修改字段默认值，前者创建新表，后者直接修改 .frm 文件。

ALTER TABLE XXX
MODIFY COLUMN xxx TINYINT(3)  NOT NULL DEFAULT 5;
ALTER TABLE XXX
ALTER COLUMN xxx SET DEFAULT 5;

6.1 只修改 .frm 文件，不重建表

只修改 .frm 文件要远远快于重建表，而MySQL有时会在没必要重建表的时候仍去重建。比如移除一个列的 AUTO_INCREMENT 属性；增加、移除、修改ENUM和SET常量。

这类情况可以创建新的 .frm 文件，替换旧的：

1. 创建一张有相同结构的空表，进行需要的修改；
2. 执行 FLUSH TABLES WITH READ LOCK ，会关闭所有正在使用的表，且禁止任何表被打开；
3. 交换 .frm 文件；
4. 执行 UNLOCK TABLES 释放第二步的读锁。

6.2 快速创建 MyISAM 索引

先禁用索引、载入数据，然后重新启用索引：因为构建索引的工作被延迟到数据完全载入后，这个时候可以利用排序来创建索引，相比会快很多，并且使索引树的碎片更少、更紧凑（只对非唯一索引有效）。

ALTER TABLE XXX DISABLE KEYS;
--LOAD THE DATA
ALTER TABLE XXX ENABLE KEYS;

InnoDB也可以先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。

操作步骤：

1. 用需要的表结构创建一张新表，但不包含索引；
2. 载入数据到表中以构建 .MYD 文件；
3. 按照需要的结构创建另一张空表，并包含索引，创建出 .frm 和 .MYI 文件；
4. 获取读锁并刷新表；
5. 重命名第二张表的 .frm 和 .MYI 文件，使MySQL认为是第一张表的文件；
6. 释放读锁；
7. 使用 REPAIR TABLE 重建表的索引，此操作通过排序来构建所有索引，包括唯一索引。

---

参考：

🔗 《高性能MySQL》