《高性能Mysql》读书笔记之Schema与数据类型优化

一、选择优化的数据类型

原则

• 使用可以正确存储的最小数据类型

  小的数据类型占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少

• 选择简单的数据类型

  简单的数据类型通常需要更少的CPU周期，整数比字符操作代价更低

• 尽量避免NULL

  通常情况下最好设置指定列为NOT NULL，除非真的需要存储NULL值

  1. NULL会使用更多的存储空间，再MYSQL中需要特殊处理
  2. 当可为NULL的列被索引时，每个索引记录需要一个额外的字节
  3. 在MYSQL中还可能导致固定大小的索引变成可变大小的索引

1. 整数类型

基本属性

类型	占用存储空间	存储范围
TINYINT	8	-27~27
SMALLINT	16	-215~215
MEDUIMINT	24	-223~223
INT	32	-231~231
BIGINT	64	-263~263

注意点

1. 整数类型有可选的 UNSIGNED 属性，表示不允许负值，这样可以使正数的上限提高一倍

   TINYINT 存储范围是 -128~127 ，TINYINT UNSIGNED 存储范围是 0 ~128

2. 有符号和无符号类型使用相同的存储空间，并具有相同的性能
3. MYSQL可以位整数指定宽度，例如INT(11)，对大多数应用是没有意义的：它不会限制值的合法范围，只是规定了MYSQL的一些交互工具用来显示的字符个数。
4. 对于存储和计算来说，INT(11)和INT(20)是相同的

2. 实数类型

DECIMAL

1. DECIMAL类型用于存储精确的小数
2. CPU不支持对DECIMAL的直接计算，CPU直接支持原生浮点计算，所以浮点运算更快
3. MYSQL会将数字打包到一个二进制字符串中（每4个字节存储9个数字）；如DECIMAL(18,9)小数两边各存储9个数字，一共使用9个字节
4. MYSQL的DECIMAL类型支持最多65个数字
5. 在计算中DECIMAL会转换位DOUBLE

选择

1. FLOAT使用4字节存储，DOUBLE使用8字节存储，相比DECIMAL占用更少存储空间
2. 在要求确保数据精确情况下，数据量小使用DECIMAL，数量量大使用BIGINT存储

3. 字符串类型

比较	VARCHAR	CHAR
存储内容	存储可变长字符串	存储定长字符串
存储空间	列的最大长度小于255字节，则使用1个字节记录字符串长度，否则使用2个字节	根据字符串长度分配足够空间
适合场景	字符串列的最大长度比平均长度大得多 ; 列的更新很少，没有碎片问题；使用了像UTF-8这样复杂的字符集，每个字符集都是用不同字节存储	适合存储很短的字符串，或者所有值都接近同一个长度
注意点	UPDATE比原来更长时，数据库会做额外工作CHAR会根据需要采用空格进行填充以方便比较

比较	BLOG	TEXT
区别	存储二进制字符；无字符集和排序规则	有字符集和排序规则
相同点	MYSQL把BLOG和TEXT当作单独对象处理，当BLOG和TEXT值太大时，Innodb会使用专门外部区域存储，每个值在行内用1~4个字节存储一个指针，然后再外部存储实际的值

使用枚举代替字符串类型

1. 枚举使用整数存储而不是字符串
2. MYSQL存储的是每个值在列表中的位置，而不是实际的值，实际的值存在 .frm 文件中保存"数字 - 字符串"映射关系
3. 枚举按照内部存储的整数而不是定义的字符串进行排序，所以创建枚举的时候得按顺序存放
4. 可以在查询中使用FIELD()显示指定排序顺序，但会导致无法利用索引消除排序
5. 尽量不要用枚举存储整数，容易导致混乱
6. 枚举不适合未来可改变的字符串，除非能接受只在列表末尾添加元素

4. 日期和时间类型

比较	DATETIME	TIMESTAMP
范围	1001~9999年	保存1970年1月1日午夜以来的秒数，和Unix时间戳相同，只能表示1970~2038年
精度	秒	秒
格式	将日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关	默认NOT NULL，默认为当前时间
存储空间	8个字节	4个字节

5. 位数据类型

BIT

1. 存储一个或多个true/false值
2. 最大存储64位
3. Memory和Innodb，为每个BIT使用一个足够存储的最小整数类型存放，所以不能节省存储空间
4. MYSQL把BIT当作字符串类型而不是整数类型
5. 谨慎使用BIT类型，大部分应用避免使用此类型

SET

1. 在MYSQL内部是一个打包的位的集合表示
2. 改变列的定义的代价很高，需要alter table 和 枚举增长一样一样的

二、MySQL Schema 设计中的陷阱

1. 太多的列
2. 太多的关联

   单个查询最好在12个表以内做关联

3. 全能的枚举即过度使用枚举
4. 变相的枚举即避免滥用SET，考虑使用枚举代替集合

三、范式和反范式

范式的优点

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

范式的缺点

• 范式之间的关联比较复杂

反范式的优点

• 数据都在一张表中，避免了关联
• 避免随机IO，当整表查询的时候，基本上是顺序IO
• 冗余的表处理某些业务时候非常方便

混用范式化和反范式化

• 实际环境中不可能完全范式化或反范式化

四、缓存表和汇总表

缓存表 | 表示存储那些可以比较简单地从schema其它表获取（但获取速度比较慢）数据的表
汇总表 | 保存使用 GROUP BY 语句聚合数据的表

1.物化视图

物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新
Mysql不支持原生物化视图
这里推荐开源工具 Flexviews

组成

• 变更数据抓取功能，可以读取服务器的二进制日志并且解析相关行的变更
• 一系列可以帮助创建和管理视图的定义的存储过程
• 一些可以应用变更到数据库中的物化视图的工具

2.计数器表

• 使用单一的字段，会有全局锁
• 在表中建多个例如100个技术行，每次随机更新其中的某一行，可以减少并发

五、加快ALTER TABLE操作的速度

ALTER TABLE操作是新建一张表，将旧表所需数据查出插入到新表中，然后删除旧表，这种操作有时会持续数小时

优化

1. 现在一台不常用的服务器上进行ALTER TABLE 操作，然后和提供服务的主库进行切换
2. 用要求的表结构创建一张和原表无关的新表，然后通过重命名和删表方式交换量表数据
3. 直接修改 .frm 表而不涉及表数据

可能不需要重建表的一些操作

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

实现方式

原则是创建一个新的frm文件替换原来的frm文件

1. 创建一张有相同结构的表，并进行所需要的修改（例如增加ENUM常量）
2. 执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且进制任何表被打开
3. 交换.frm文件
4. 执行UNLOCK TABLES 来释放第二步的锁

快速创建MyISAM索引

TIP：会有很大风险，不是官方操作，做之前先备份数据

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

总结

1. 避免过度设计
2. 避免NULL值
3. 使用小而简单的适合的数据类型
4. 尽量使用相同的数据类型存储相似或相关的值，尤其是在关联条件中使用的列
5. 注意可变长字符串，其在临表和排序时可能导致最大长度分配内存
6. 尽量使用整型定义标识列
7. 避免使用MYSQL已经遗弃的特性
8. 小心使用ENUM和SET
9. 范式是好的，但反范式有时也是必须的，并且能带来好处
   10.ALTER TABLE是痛苦的操作，因为大部分情况下会锁表并重建整张表

---

参考书目

《高性能MySQL》