一、选择优化的数据类型
原则
- 使用可以正确存储的最小数据类型 小的数据类型占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少
- 选择简单的数据类型 简单的数据类型通常需要更少的CPU周期，整数比字符操作代价更低
- 尽量避免NULL 通常情况下最好设置指定列为NOT NULL，除非真的需要存储NULL值
- NULL会使用更多的存储空间，再MYSQL中需要特殊处理
- 当可为NULL的列被索引时，每个索引记录需要一个额外的字节
- 在MYSQL中还可能导致固定大小的索引变成可变大小的索引
1. 整数类型
基本属性
类型 | 占用存储空间 | 存储范围 |
|---|---|---|
TINYINT | 8 | -27~27 |
SMALLINT | 16 | -215~215 |
MEDUIMINT | 24 | -223~223 |
INT | 32 | -231~231 |
BIGINT | 64 | -263~263 |
注意点
- 整数类型有可选的 UNSIGNED 属性，表示不允许负值，这样可以使正数的上限提高一倍 TINYINT 存储范围是 -128~127 ，TINYINT UNSIGNED 存储范围是 0 ~128
- 有符号和无符号类型使用相同的存储空间，并具有相同的性能
- MYSQL可以位整数指定宽度，例如INT(11)，对大多数应用是没有意义的：它不会限制值的合法范围，只是规定了MYSQL的一些交互工具用来显示的字符个数。
- 对于存储和计算来说，INT(11)和INT(20)是相同的
2. 实数类型
DECIMAL
- DECIMAL类型用于存储精确的小数
- CPU不支持对DECIMAL的直接计算，CPU直接支持原生浮点计算，所以浮点运算更快
- MYSQL会将数字打包到一个二进制字符串中（每4个字节存储9个数字）；如DECIMAL(18,9)小数两边各存储9个数字，一共使用9个字节
- MYSQL的DECIMAL类型支持最多65个数字
- 在计算中DECIMAL会转换位DOUBLE
选择
- FLOAT使用4字节存储，DOUBLE使用8字节存储，相比DECIMAL占用更少存储空间
- 在要求确保数据精确情况下，数据量小使用DECIMAL，数量量大使用BIGINT存储
3. 字符串类型
比较 | VARCHAR | CHAR |
|---|---|---|
存储内容 | 存储可变长字符串 | 存储定长字符串 |
存储空间 | 列的最大长度小于255字节，则使用1个字节记录字符串长度，否则使用2个字节 | 根据字符串长度分配足够空间 |
适合场景 | 字符串列的最大长度比平均长度大得多 ; 列的更新很少，没有碎片问题；使用了像UTF-8这样复杂的字符集，每个字符集都是用不同字节存储 | 适合存储很短的字符串，或者所有值都接近同一个长度 |
注意点 | UPDATE比原来更长时，数据库会做额外工作CHAR会根据需要采用空格进行填充以方便比较 |
比较 | BLOG | TEXT |
|---|---|---|
区别 | 存储二进制字符；无字符集和排序规则 | 有字符集和排序规则 |
相同点 | MYSQL把BLOG和TEXT当作单独对象处理，当BLOG和TEXT值太大时，Innodb会使用专门外部区域存储，每个值在行内用1~4个字节存储一个指针，然后再外部存储实际的值 |
使用枚举代替字符串类型
- 枚举使用整数存储而不是字符串
- MYSQL存储的是每个值在列表中的位置，而不是实际的值，实际的值存在 .frm 文件中保存"数字 - 字符串"映射关系
- 枚举按照内部存储的整数而不是定义的字符串进行排序，所以创建枚举的时候得按顺序存放
- 可以在查询中使用FIELD()显示指定排序顺序，但会导致无法利用索引消除排序
- 尽量不要用枚举存储整数，容易导致混乱
- 枚举不适合未来可改变的字符串，除非能接受只在列表末尾添加元素
4. 日期和时间类型
比较 | DATETIME | TIMESTAMP |
|---|---|---|
范围 | 1001~9999年 | 保存1970年1月1日午夜以来的秒数，和Unix时间戳相同，只能表示1970~2038年 |
精度 | 秒 | 秒 |
格式 | 将日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关 | 默认NOT NULL，默认为当前时间 |
存储空间 | 8个字节 | 4个字节 |
5. 位数据类型
BIT
- 存储一个或多个true/false值
- 最大存储64位
- Memory和Innodb，为每个BIT使用一个足够存储的最小整数类型存放，所以不能节省存储空间
- MYSQL把BIT当作字符串类型而不是整数类型
- 谨慎使用BIT类型，大部分应用避免使用此类型
SET
- 在MYSQL内部是一个打包的位的集合表示
- 改变列的定义的代价很高，需要alter table 和 枚举增长一样一样的
二、MySQL Schema 设计中的陷阱
- 太多的列
- 太多的关联 单个查询最好在12个表以内做关联
- 全能的枚举即过度使用枚举
- 变相的枚举即避免滥用SET，考虑使用枚举代替集合
三、范式和反范式
范式的优点
- 范式化的更新操作通常比反范式化要快
- 当数据较好的范式化时，就只要很少或这没有重复数据，所以只需要修改更少的数据
- 范式化的表通常更小，可以更好的放在内存里，所以执行操作会更快
- 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句
范式的缺点
- 范式之间的关联比较复杂
反范式的优点
- 数据都在一张表中，避免了关联
- 避免随机IO，当整表查询的时候，基本上是顺序IO
- 冗余的表处理某些业务时候非常方便
混用范式化和反范式化
- 实际环境中不可能完全范式化或反范式化
四、缓存表和汇总表
缓存表 | 表示存储那些可以比较简单地从schema其它表获取（但获取速度比较慢）数据的表 汇总表 | 保存使用 GROUP BY 语句聚合数据的表
1.物化视图
物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新 Mysql不支持原生物化视图 这里推荐开源工具 Flexviews
组成
- 变更数据抓取功能，可以读取服务器的二进制日志并且解析相关行的变更
- 一系列可以帮助创建和管理视图的定义的存储过程
- 一些可以应用变更到数据库中的物化视图的工具
2.计数器表
- 使用单一的字段，会有全局锁
- 在表中建多个例如100个技术行，每次随机更新其中的某一行，可以减少并发
五、加快ALTER TABLE操作的速度
ALTER TABLE操作是新建一张表，将旧表所需数据查出插入到新表中，然后删除旧表，这种操作有时会持续数小时
优化
- 现在一台不常用的服务器上进行ALTER TABLE 操作，然后和提供服务的主库进行切换
- 用要求的表结构创建一张和原表无关的新表，然后通过重命名和删表方式交换量表数据
- 直接修改 .frm 表而不涉及表数据
可能不需要重建表的一些操作
- 移除（非增加）一个列的AUTO_INCREMENT属性
- 增加、移除或更改ENUM和SET常量。如果移除的是已经有行数据用到其他值的常量，查询将会返回一个空字符串
实现方式
原则是创建一个新的frm文件替换原来的frm文件
- 创建一张有相同结构的表，并进行所需要的修改（例如增加ENUM常量）
- 执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且进制任何表被打开
- 交换.frm文件
- 执行UNLOCK TABLES 来释放第二步的锁
快速创建MyISAM索引
TIP：会有很大风险，不是官方操作，做之前先备份数据
- 用需要的表结构创建一张表，但是不包括索引
- 载入数据到表中以构建.MYD文件
- 按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的 .frm 和 .MYI 文件
- 获取读锁并刷新表
- 重命名第二张表的 .frm 和 .MYI 文件，让MySQL认为是第一张表的文件
- 释放读锁
- 使用ALTER TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引
总结
- 避免过度设计
- 避免NULL值
- 使用小而简单的适合的数据类型
- 尽量使用相同的数据类型存储相似或相关的值，尤其是在关联条件中使用的列
- 注意可变长字符串，其在临表和排序时可能导致最大长度分配内存
- 尽量使用整型定义标识列
- 避免使用MYSQL已经遗弃的特性
- 小心使用ENUM和SET
- 范式是好的，但反范式有时也是必须的，并且能带来好处 10.ALTER TABLE是痛苦的操作，因为大部分情况下会锁表并重建整张表
