选择优化的数据类型

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

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

• 简单就好：简单数据类型的操作通常需要更少的 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 为在内存中构造唯一索引，并且为载入的每一行检查唯一性。

---

欢迎关注我的微信公众号: coderbee笔记，可以更及时回复你的讨论。