Schema与数据类型优化

《高性能 MySQL》读书笔记二

选择合适的数据类型

简单的几个原则：

• 更小的更好：一般情况下，应该尽量使用可以存储数据的最小数据类型。更小的数据类型通常更快，占用更少的磁盘、内存和 CPU 缓存。schema 设计之初，应该估算出字段的数据能用到的最大值，然后在满足要求的前提下使用最小数据类型。

• 简单：简单数据类型的操作通常需要更少的 CPU 周期。例如：整型比字符型操作代价更低。使用整型相对于字符型可以带来更好的性能，这包括 数据的查询和排序。整型只需要比较一次，字符型需要比较每一个字符，系统开销更大。尽量使用 MySQL 内置类型，例如：不要使用字符串去保存时间。使用整型存储 IP 地址。

• 尽量避免 NULL

列值可为 NULL 是列的默认属性，即使应用程序不需要保存 NULL 也是如此。通常情况下，应该尽量指定列为 NOT NULL，除非真的需要保存 NULL 值。

查询中包含 NULL 的列，对 MySQL 来说更难优化，NULL 值使得索引，索引统计和值的比较都更为复杂。为 NULL 的列会使用更多的存储空间，MySQL 需要特殊处理。也有例外：InnoDB 使用单独的位（bit）来保存 NULL，对于稀疏数据（大多数为 NULL）有很好的空间效率，但这点不适用于 MyISAM。

整数类型

有两种类型的数字：整数和实数。

整数类型：

• TINYINT 1 字节、【-128，127】、【0，255】

• SMALLINT 2 字节、【-32768，32767】、【0，65535】

• MEDIUMINT 3 字节、【-2147483648，2147483647】、【0，4294967295】

• INT 4 字节、【-2147483648，2147483647】、【0，4294967295】

• BIGINT 8 字节、【-2⁶³，2⁶³-1】、【0，2⁶⁴-1】

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

有符号和无符号具有相同的存储空间和性能，根据实际情况选择合适的类型。

长度（宽度）有什么用？

使用图形界面如 navicat 建表时，有一个“长度”设置。千万别以为这个“长度”会限制整型存储的数据范围，例如 INT(11)，它对大多数应用程序没有意义，它不会限制 INT 的存储范围，存储范围只和数据类型密切相关。

那长度有什么用？

只有当列设置为“ZEROFILL”时，查询数据显示时，MySQL 会在左侧自动补零以显示最小长度。例如：INT（3）保存 1 时，查询出来会显示为：001。

实数类型

实数是带有小数部分的数字。

• FLOAT 单精度，4 字节
• DOUBLE 双精度，8 字节
• DECIMAL 存储精确的小数

FLOAT 和 DOUBLE 使用标准的浮点运算进行 近似运算，运算结果和平台相关。

DECIMAL 支持精确计算，但 CPU 不支持对 DECIMAL 的直接计算，MySQL 自己实现了 DECIMAL 的高精度计算，所以 DECIMAL 在性能上要弱一些。

Tips

DECIMAL 需要额外的空间和计算消耗，当数据量比较大时，可以考虑使用 BITINT 来代替，将存储的数据根据小数的位数乘以相应的倍数即可。这样可以解决浮点类型计算不准确，DECIMAL 计算开销太大的问题。

字符串类型

VARCHAR

VARCHAR 用于存储可变长的字符，比定长更节省空间，越短的字符占用空间越少。但有一种例外：当表使用“ROW_FORMAT=FIXED”创建时，每一行都会使用定长存储，浪费空间。

VARCHAR 需要额外使用 1 或 2 个字节来记录字符长度，如果长度小于等于 255，就用 1 字节保存长度，否则使用 2 字节。例如：VARCHAR(10)需要额外的 1 字节，VARCHAR(1000)需要额外的 2 字节。

VARCHAR 节省了存储空间，对性能也有帮助。但是由于行是变长的，所以 UPDATE 时可能比原来更长，这就需要 MySQL 为其额外再分配存储空间，导致 UPDATE 时开销比定长类型要大。

CHAR

CHAR 类型是定长的，MySQL 会根据定义的长度去分配存储空间，所以不会有 VARCHAR 进行 UPDATE 时的额外开销。CHAR 类型存储时，会自动去除末尾的空格，这一点需要注意。

CHAR 适合存储短的，长度固定的字符，例如 MD5 值，UUID 等…

由于 UPDATE 时没有额外的开销，对于经常变更的数据，CHAR 的性能也比 VARCHAR 更好。

BLOB 和 TEXT 类型

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

它们分别属于不同的数据类型家族：

字符类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。二进制类型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

与其他类型不同，MySQL 会将 BLOB 和 TEXT 当做单独的对象处理。

当值太大时，MySQL 会使用专门的存储区域来存储数据，行内使用 1~4 字节来存储一个指针，指向对应的大文本字符。

BLOB 和 TEXT 的不同之处在于：由于 BLOB 是二进制，所以没有字符集和排序规则，但是 TEXT 有。

即使 TEXT 有排序规则，MySQL 对其进行排序时，也不会对整个文本进行排序，只会对前 max_sort_length 字节进行排序，可以通过修改 max_sort_length 进行配置。

MySQL 不能将 BLOB 和 TEXT 全部长度的字符进行索引。

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

枚举可以把一些不重复的字符串存储成一个预定义的集合，MySQL 在存储枚举时非常紧凑，会根据列表值压缩到 1 到 2 个字节中。

MySQL 在内部会将列中的枚举值保存为整数，在.frm 文件中保存一个“数字->字符串”的映射关系，通过数字快速的查找到具体的枚举值。

枚举字段排序时，并不会按照给定的字符串排序，而是根据内部的整数排序，所以建议列举枚举时按照预想的顺序给出。

日期和时间类型

MySQL 提供了多种类型来保存时间和日期，例如：YEAR、DATE、DATETIME。MySQL 能存储的最小时间粒度为秒（有的第三方存储引擎支持微秒）。

MySQL 提供了两种相似的事件类型：DATETIME 和 TIMESTAMP。

DATETIME

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

TIMESTAMP

保存了从 1970 年 1 月 1 日凌晨以来的秒数，和 UNIX 时间戳相同。使用 4 个字节来保存，比 DATETIME 节省空间，具有更高的性能。但是范围比 DATETIME 要小得多，只能存储 1970 年到 2038 年。

TIMESTAMP 显示的值依赖于时区，MySQL 服务器，操作系统以及客户端的连接都有时区的设置。

除了特殊行为之外，应该尽量使用 TIMESTAMP，它比 DATETIME 空间效率要高。

如果需要存储比秒更小粒度的时间，MySQL 目前没有提供合适的数据类型，可以考虑使用 BIGINT 来存储微秒级别的时间戳。

位数据类型

可以使用 BIT 列存储一个或多个 true/false 值，BIT(1)包含单个位的字段，最多可包含 64 个位。

MySQL 将 BIT 当做字符串类型，而不是数字类型。当查询 BIT(1)时，结果是一个包含二进制 0 或 1 的字符串，而不是 ASCII 码中的“0”或“1”。

BIT 列进行比较时，MySQL 会将位字符串转换为十进制数字进行比较。例如：’111′ = 7。

对于大部分应用，最好慎用 BIT 类型。

选择标识符

为标识列选择合适的数据类型十分重要。

一般来说标识列很可能用来在不同的表之间进行比较，甚至作为外键来使用。合适的数据类型可以提升系统的整体性能，减少数据比较的系统开销。

一旦选定了类型，一定要确保关联表中也是相同的数据类型，混用不同的数据类型会带来很多麻烦。例如：将字符串与整形做比较，会导致严重的性能问题。

一般来说，在没有特殊要求的情况下，整型 通常是标识列最好的选择，因为它很快，而且可以自动递增。

如果可以的话，应该尽量避免使用字符串当做标识列，它很消耗空间，而且比整型慢。

很多人喜欢用随机的字符串来作为标识列，例如：UUID。由于生成的字符没有规律，会导致 INSERT 和 SELECT 语句变得很慢：

• 插入的值会随机的写入到索引的不同位置，使得 INSERT 更慢。这会导致页分裂，磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
• SELECT 语句变慢，因为逻辑上相邻的数据会分布在磁盘的不同地方。
• 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问“局部性原理”失效。缓存无法命中，加载到内存中也是徒劳。

Tips

如果需要使用 UUID 当做标识列，那么应该移除“-”这种没有意义的字符。最好的解决方案是：用 UNHEX()将 UUID 转换为 16 位的二进制数据，没有字符集，没有排序，而且占用更少的磁盘空间，可以很好的提升性能。

INSERT INTO person  VALUES (UNHEX(REPLACE(uuid(),'-','')),'admin');
-- 4B676ECAE59D11E9A6B5F21CB433E1CE
SELECT * FROM person WHERE id = UNHEX('4B676ECAE59D11E9A6B5F21CB433E1CE')

特殊类型数据

有些类型的数据并不直接与 MySQL 的内置类型一致，微秒型的时间戳就是个例子。

还有例如：IPv4 地址，应该使用无符号的整数来保存，而非字符串。MySQL 内置的函数 INET_ATON 和 INET_NTOA 可以很好的转换。

SELECT INET_ATON('192.168.1.100');
-- 3232235876
SELECT INET_NTOA(3232235876);
-- 192.168.1.100

Schema 设计中的陷阱

• 太多的列 MySQL 存储引擎工作时，需要在服务器层和存储引擎层之间做行缓冲格式拷贝数据，然后在服务器层之间将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。如果单张表的列太多，就应该要考虑做表的拆分。

• 太多的关联 MySQL 限制了每个关联最多只能 61 张表，单个查询最好在 12 张表以内做关联。

• 滥用枚举

• 变相使用枚举

• 绝不用 NULL 不使用 NULL 有很多好处，应该尽量避免使用 NULL，如果要保存未知值时可以使用“0”或其他特殊字符代替，但是在遵循这个原则的前提下也不要走极端。当确实需要表示未知值时，大胆的使用 NULL。

加快 ALTER TABLE

MySQL 的 ALTER TABLE 操作的性能对大表来说性能是个大问题。

MySQL 修改表结构大部分是创建一个新表，然后将旧表的数据拷到新表，最后删除旧表。对于数据量很大的表来说，这通常很慢。

例如：修改一个列的默认值。

很慢的操作：

ALTER TABLE `test`.`person`
MODIFY COLUMN `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT admin AFTER `id`;

列的默认值，实际上保存在表的.frm 文件中，可以直接修改这个文件而不需要改动表本身。然而 MySQL 还没有采用这种优化的方案，所有的 MODIFY COLUMN 都会导致表重建。

另一种方式是通过 ALTER COLUMN 来修改列，这个语句会直接修改.frm 文件，所以速度很快。

ALTER TABLE `test`.`person`
ALTER COLUMN `name` SET DEFAULT 'admin';