《高性能 MySQL》 — 第四章 Schema 与数据类型优化

选择优化的数据类型

选择数据类型的一般原则:

  • 更小的通常更好:尽量使用可以正确存储数据的最小数据类型。

  • 简单就好:简单数据类型的操作通常需要更少的 CPU 周期。例如整型比字符操作代价更低,以为字符集和校对规则(排序规则)使字符比较比整型更复杂。举例,应该使用 MySQL 内建的类型(date, time, datetime) 而不是字符串来存储日期和时间,应该使用整型来存储 IP 地址。

  • 尽量避免 NULL:如果查询中包含可为 NULL 的列,对 MySQL 来说更难优化,因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间,在 MySQL 里也需要特殊处理。当可为 NULL 的列被索引时,每个索引记录需要一个额外的字节,在 MyISAM 里甚至还可能导致固定大小的索引变成可变大小的索引。例外,对于稀疏数据(很多列的值为 NULL),InnoDB 使用单独的位(bit)存储 NULL 值,有很好的空间效率。

在为列选择数据类型时,第一步需要确定合适的大类型:数字、字符串、时间等。下一步是选择具体类型。

整数类型

TINYINT(8), SMALLINT(16), MEDIUMINT(24), INT(32),BIGINT(64),后面的数字表示存储空间的位数 N,可以存储的值的范围从 -2^(N-1)2^(N-1) - 1

整数类型还有可选的 UNSIGNED 属性,表示不允许负值,可以使整数的上限提高一倍。有符号和无符号类型使用相同的存储空间,并具有相同的性能。

数据类型决定了 MySQL 怎么在内存和磁盘中保存数据,但整数计算一般使用 64 位的 BIGINT 整数,即使在 32 位环境也是。(一些聚合函数是例外,使用 DECIMAL/DOUBLE 进行计算。)

MySQL 可以为整数类型指定宽度,它只是规定了一些交互工具用来显示字符的个数。对于存储和计算,INT(1)INT(20) 是相同的。

实数类型

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

DECIMAL 类型用于存储精确的小数,可以指定小数点前后所允许的最大位数。

字符串类型

存储引擎存储 varchar 和 char 值的方式在内存中和在磁盘上可能不一样,所以 MySQL 服务器从存储引擎读出的值可能需要转换为另一种存储格式。

  • varchar:仅使用必要的空间,但需要额外的 1 或 2 个字节来记录字符串的长度。适合使用 varchar 的场景:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了像 UTF8 这样复杂的字符集,每个字符都使用不同的字节进行存储。

  • char:定长的,不容易产生碎片。存储 char 值时,MySQL 会删除所有的末尾空格,进行比较时会用空格进行填充。

枚举(enum)

使用枚举(enum)代替字符串类型:MySQL 在内部会将每个值在列表中的位置保存为整数,并且在表的 .frm 文件中保存 ”数字 – 字符串“映射关系的”查找表“。存储空间一般只需要一个或两个字节。

create table enum_test(
     e enum('fish',  'apple',  'dog) not null
);

insert into enum_test(e) values('fish'), ('dog'), ('apple');

枚举的问题:

  1. 枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。绕过限制的方式是按照需要的顺序来定义枚举;或者在查询中使用 field 函数显式地指定排序顺序,但这会导致 MySQL 无法利用索引消除排序。
  2. 字符串列表是固定的,添加或删除字符串必须使用 alter table,如果能接受只在列表的末尾添加元素,那么 MySQL 就可以不用重建整个表来完成修改。
    3 MySQL 把每个枚举值保存为整数,并且必须进行查找才能转换为字符串,这会带来一定的开销。

日期和时间类型

MySQL 能存储的最小时间粒度为秒。

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

  • timestamp:保存了从 1970 年 1 月 1 日午夜以来的秒数,和 UNIX 时间戳相同。只使用 4 个字节的存储空间,只能表示从 1970 年到 2038 年。

选择标识符(identifier)

为标识列(identifier column)选择合适的数据类型非常重要,下面是一些小技巧:

  • 整数类型:整数通常是标识列最好的选择,因为它们很快并且可以使用 AUTO_INCREMENT。
  • ENUM 和 SET 类型:通常不是标识列的好选择。MySQL 在内部使用整数存储 ENUM 和 SET 类型,然后在做比较时转换为字符串。
  • 字符串类型:尽量避免,耗空间,比数字类型慢。对于完全 “随机” 的字符串,会分布在很大的空间内,这会导致 insert 以及一些 select 语句变得很慢:
    > * 因为插入值会随机地写到索引的不同位置,使得 insert 语句更慢。这会导致 页分裂、磁盘随机访问,以及对于聚族存储引擎产生聚族索引碎片。
    > * select 语句会变得更慢,因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
    > * 随机值会导致缓存对于所有类型的查询语句效果都很差,因为会使得缓存赖以工作的访问局部性原理失效。
    > * 另一方面,对一些有很多写的特别大的表,这种伪随机值实际上可以帮助消除热点。

MySQL schema 设计中的陷阱

  • 太多的列:MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的,转换的代价依赖于列的数量。
  • 太多的关联:MySQL 限制了每个关联操作最多只能有 61 张表。
  • 全能的枚举:
  • 变相的枚举:枚举列允许在列中存储一组定义值中的单个值,集合列则允许在列中存储一组定义值中的一个多多个值。
  • 非此发明的 NULL:MySQL 会在索引中存储 NULL 值,而 Oracle 则不会。

范式和反范式

范式化的好处:

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

范式化设计的缺点是通常需要关联。

反范式化使数据存放在尽可能少的表里,很好地避免关联。

最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列。

缓存表和汇总表

ON DUPLICATE KEY UPDATE 可以实现不存在则插入、存在则更新的逻辑:

insert into daily_hit_counter(day, slot, cnt)
values (current_date, rand() * 100, 1)
on duplicate key update cnt = cnt + 1;

加快 alter table 操作的速度

MySQL 执行大部分修改表结构操作的方法是用新的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表。

快速创建 MyISAM 索引

为了高效地载入数据,常用的技巧是先禁用索引(disable keys)、载入数据,然后重新启动索引。但 disable keys 只对非唯一索引有效。

MyISAM 为在内存中构造唯一索引,并且为载入的每一行检查唯一性。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据