高性能MySQL 笔记 第四章 Schema与数据类型优化

Schema与数据类型优化

选择优化的数据类型

几个简单的原则：

• 更小的通常更好：应该尽量使用可以正确存储数据的最小数据类型
• 简单就好：简单数据类型的操作通常需要更少的CPU周期
• 尽量避免NULL

整数类型

有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍

有符号和无符号类型使用相同的存储空间，并具有相同的性能

MySQL可以为整数类型指定宽度，但对于存储和计算来说，INT(1)和INT(20)是相同的

实数类型

FLOAT使用4字节，DOUBLE使用8字节，但MySQL使用DOUBLE作为内部浮点计算的类型

FLOAT和DOUBLE用作浮点的近似运算

尽量只在对小数进行精确计算时才使用DECIMAL。

数据量较大时，考虑使用BIGINT代替DECIMAL，根据小数的位数乘以相应的倍数即可。

字符串类型

VARCHAR

用于存储可变长字符串，仅使用必要的空间。如果设置ROW_FORMAT=FIXED，每一行都会使用定长存储，浪费空间。

以下情况使用VARCHAR：字串列的最大长度比平均长度大很多；列的更新很少；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

CHAR

是定长的。

适合存储很短的字符串，或者所有值都接近同一个长度，比如，CHAR非常适合储存密码的MD5值，因为是一个定长的值。

也适用于经常变更的数据。

BINARY和VARBINARY

适用于用于需要储存二进制数据，并且希望MySQL使用字节码而不是字符进行比较时。

BLOB和TEXT

BLOB采用二进制储存，没有排序规则或者字符集

TEXT采用字符方式储存，有排序规则和字符集

MySQL只对这两种数据类型每个列的最前max_sort_length字节而不是整个字符串做排序。

MySQL不能将BOLB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

使用枚举代替字符串类型

枚举列可以把一些不重复的字符串存储成一个预定义的集合。

MySQL在内部会将每个值在列表中的位置保存为整数。

枚举缺点：添加或者删除字串必须使用ALTER TABLE。有一定的性能开销

日期和时间类型

DATETIME

能保存大范围的值

与时区无关

TIMESTAMP

和UNIX时间戳相同

表示范围小（1970年到2038年）

依赖于时区

应该尽量使用TIMESTAMP，因为空间效率更高

位数据类型

BIT

可以使用BIT列在一列中存储一个或者多个true/false值

BIT的行为因存储引擎而异。MyISAM会打包存储所有的BIT列，可节省空间，其他引擎不会。

MySQL把BIT当作字符串类型，检索BIT(1)的值时，结果是一个包含二进制0或1的字串，而不是ASCII码的"0"或者"1"。在数字上下文的场景中，结果是位字符串转换成的数字。

应该谨慎，最好避免使用BIT类型

SET

如果需要保存很多true/false值，可以考虑合并到一个SET数据类型。

缺点时改变列的定义的代价较高，一般来说，也无法在SET列上通过索引查找。

一种替代SET的方式是使用一个整数包装一系列的位。缺点是查询语句更难写，更难理解。

选择标识符

当选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较。

一旦选定了一种类型，要确保在所有关联表中都使用同样的类型，类型之间需要精确匹配。

在满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。

整数类型

整数通常是标识列最好的选择，因为速度快而且可以自增

ENUM和SET类型

一个糟糕的选择，可能对某些只包含固定状态或者类型的静态“定义表”较为适用。

字符串类型

一个糟糕的选择，应尽量避免。因为很消耗空间，通常比数字类型慢。尤其注意在MyISAM表里使用字串作为标识列，该类表默认对字符串使用压缩索引，会导致查询慢很多。

对完全随机的字串也要多注意，比如MD5()、UUID()产生的字串，这些函数生成的值会分布在很大的空间内，会导致INSERT以及一些SELECT语句很慢。

MySQL schema设计中的陷阱

太多的列

太多的关联

MySQL限制了每个关联操作最多只能有61张表。

过度使用枚举

枚举类型和集合类型混用

尽量不要使用NULL，但也不要怕使用NULL

范式与反范式

范式化模型要求满足：

1. 每个字段只包含最小的信息属性
2. 在满足1的基础上，模型含有主键，非主键字段依赖主键
3. 在满足2的基础上，模型非主键字段不能相互依赖

不满足范式化的模型即为反范式化模型

范式优点：

• 范式化的更新操作通常比反范式化要快
• 当数据较好范式化时，只需要修改更少的数据
• 表通常更小，执行操作会更快
• 更少的需要DISTINCT或者GROUP BY语句

缺点：

• 通常需要关联

反范式的优点：

• 避免了关联
• 可以使用更有效的索引策略

混用范式化与反范式化是最好的办法

缓存表和汇总表

如果需要计算24小时内的操作次数，可以替换成每小时生成一张汇总表或者以每个小时的汇总表为基础，把前23小时的数据相加，再加上实时查询的时间差内的数据。

加快ALTER TABLE操作的速度

MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出搜偶的数据插入新表，然后删除旧表，可能需要花费很长的时间。

加快ALTER TABLE操作的方法：

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

ALTER COLUMN会直接修改.frm文件而不会涉及表数据，速度很快。

.frm 表定义文件。.myd 数据文件， .myi 索引文件

只修改.frm文件

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

快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，可以先禁用索引、载入数据，然后重新启用索引，此办法对唯一索引无效：

ALTER TABLE test.load_data DISABLE KEYS
-- Load All Data
ALTER TABLE test.load_data ENABLE KEYS

对现代版本的InnDB，可以先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。

也可以用交换数据库文件的方法来加速上述操作：

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