高性能MySQL-笔记总结（1）-Schema与数据型优化

第四章 Schema与数据型优化

4.1选择优化的数据类型

1. 更小的通常更好
   更小的数据类型通常更快，占用更少的资源。
2. 简单就好
   简单数据类型和的操作通常需要更少的CPU周期。推荐使用后MYSQL内建的类型来存储时间和日期而不是字符串
3. 尽量避免null
   通常情况下最好指定列为NOT NULL。 如果查询中包含可为null的列对MySQL来说更难优化，会使索引、索引统计和值比较都更复杂。同时将可为null的列修改为not null 带来的性能提升比较小，该问题没有必要首先解决。

4.1.1 整数类型

整数计算一般使用64位BIGINT整数，即使在32位环境。

4.1.2 实数类型

他们不仅可以存储小数部分，也可以使用DECIMAL存储比BIOGINT还大的整数。

DECIMAL类型用于存储精确的小鼠，支持精确计算。

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。5.0和更高的版本中的CDECIMAL类型允许最多65个数字，早期的限制是254个数字，并且保存为未压缩的字符串。DECIMAL只是一种存储格式，在计算DECIMAL会转化为DOUBNLE类型。

因为需要额外的空间和计算开销，所以应该尽量直在对小数进行精确计算时才使用DECIMAL。

4.1.3 字符串类型

VARCHAR 用于存储可变长字符串。用额外的一到两个字节记录字符串的长度。

CHAR 是定长的，且CHAR类型自动删除所有的末尾空格。

VARCHAR和CHAR类型：存储引擎存储这两种类型的方式在内存中和磁盘上可能不一样，所以MySQL服务器从存储引擎读出的值可能需要转化为另一种存储格式。



BLOB和TEXT类型，都是存储很大的数据的字符串数据类型，分别是二进制和字符方式存储。MySQL会使用专门的“外部”存储区域来进行存储，内部使用1~4个字节存储一个指针，外部区域存储实际的值。

BLOB：二进制数据，没有排序规则或字符集。

TEXT：有字符集和排序规则。



使用枚举（ENUM）代替字符串类型。ENUM在表中存储的是枚举类种每个值得下标整数，以 “数字-字符串”映射。同时尽量避免使用数字作为ENUM枚举常量，这种双重性很容易造成混乱。枚举得比较排序是使用内部存储得整数做比较而不是定义得字符串。同时可以使用FIELD() 函数显示的制定排序顺序。对于一系列未来可能会改变的字符串，避免使用枚举类。

同时在使用ENUM和VARCHAR进行关联的时候注意他们的速度。

4.1.4 日期和时间类型

DATETIME

精度为秒，封装格式YYYYMMDDHHMMSS的整数种，与时区无关，使用8个字节的存储空间。

TIMESTAMP

只使用4个字节的存储空间，从1970年到2038年。TIMESTAMP显示的值依赖时区。TIMESTAMP列可以配置插入和更新行为。默认为not null。

除了特殊行为外，尽量使用TIMESTAMP，他的空间利用率更高。若需要存储比秒更小粒度的日期和时间值，可以使用BIGINT类型存储微妙级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。

4.1.5 位数据类型

BIT

使用BIT可以存储一个或多个TRUE/FALSE值，但是并不能减少存储空间。MySQL吧BIT当作字符串类型。间缩BIT(1)时结果时一个包含二进制0或1的字符串，而不是ASCII码的 ‘0’ 或 ‘1’ 。对于大部分应用我们应该避免使用bit类型，他的比较转化成了字符码等值的字符串。

SET

如果需要保存很多true/false，使用一个SET数据类型进行包装。有FIND_IN_SET() 函数方便查询。缺点是改变列定义的代价比较高。

在整数列上进行按位操作

把8位包装到一个TINYINT中，并且按位操作来使用。

4.1.6 选择标识符

标识列的数据类型要适合比较，例如在关联操作中。

整数类型

整数通常是标识列最好的选择。他们很快并且可以使用AUTO_INCREMENT。

ENUM 和 SET 类型

这两种类型并不是一个好的选择。适合在有序状态，产品类型，性别等列。

字符串类型

应该避免使用字符串类型作为标识符，他们很消耗空间，比较也比数字型的慢。最多可能导致6倍的性能下降。

• 插入值随机写到索引的不同位置，使得INSERT语句很慢

• SELECT会变得更慢，逻辑上相邻得行会分布在磁盘上得各个地方

  如果存储UUID，应该去除 ‘ - ’符号，用UNHEX() 函数转换UUID值为16个字节的数字。

4.2 MySQL schema 设计中的陷阱

1. 不要有太多的列，非常宽的表中，只有一小部分列会实际用到，在服务层和存储引擎层之间将编码列转化为行数据结构的时候代价非常高。
2. 不要有太多的关联，关联操作最多只能有61张表，如果希望查询执行的块且并发性好，单个查询最好在12个表以内做关联。
3. 防止过度枚举，导致频繁ALTER TABLE
4. 变相的枚举，在一个集合中，如果不会同时出现集合中的两个元素的值，那么应该替换成枚举列。
5. 尽可能不要使列可为NULL

4.3 范式和反范式

4.3.1 范式的优点与缺点

优点：

• 范式化的更新操作通常比反范式化的快
• 很少或者没有数据重复
• 表通常更小
• 更少使用DISTINCT或者GROUP BY

缺点：

通常需要联表查询，代价昂贵，可能使一些索引策略无效。

4.3.2 反范式化的优点和缺点

可以很好的避免关联，将多个表的字段合并，再增加一个索引，就可以避免关联，这非常高效。

4.3.3 混用范式化和反范式化

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。（可以使用触发器更新缓存值）

冗余数据可能会导致更新时要更新多张表，但是相对于select的次数而言，这样的开销是比较小的。

4.4 缓存表和汇总表

计数器表

如果应用在表中保存计数器，则计数器更新时很有可能碰到并发的问题。

假设建立一张表作为计数器表，可以在表中插入100条计数器，然后每次增加都随机选择一条进行增加，要获得统计结果的时候可以对列进行sum聚合查询。

4.5 加快ALTER TABLE操作的速度

4.5.1只修改.frm文件

以下这些操作不需要重建表：

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

也可以创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件：

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