MySQL数据类型优化

MySQL - Schema与数据类型优化

笔记摘自 <高性能MySQL 第三版> 第四章

一.选择优化的数据类型

1.数据类型的选择原则：

更小的通常更好
简单就好
尽量避免NULL

2.应该尽量只在对小数进行精确计算时才使用DECIMAL，使用int类型通过程序控制单位效果更好

3.使用VARCHAR合适的情况
字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

4.CHAR适合存储很短的字符串，或者所有值都接近同一个长度；不容易产生碎片，在存储空间上更有效率

5.Blob与Text的区别: Blob类型存储二进制数据, 没有排序规则或字符集, 而Text类型有字符集和排序规则.

6.通常应该尽量使用TIMESTAMP，它比DATETIME空间效率更高

7.可以使用枚举列代替常用的字符串类型, 会根据列表值的数量压缩到一个或者两个字节中.

二.Schema设计中的陷阱
不好的设计

太多的列
太多的关联
全能的枚举
变相的枚举
非此发明（Not Invent Here）的NULL

三.范式和反范式
1.范式的优点

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

2.范式化设计的缺点是通常需要关联
3.反范式的优点：避免关联，避免了随机I/O，能使用更有效的索引策略

四.缓存表和汇总表
1.有时提升性能最好的方法是同一张表中保存衍生的冗余数据，有时也需要创建一张完全独立的汇总表或缓存表
2.物化视图，MySQL并不原生支持
3.如果应用在表中保存计数器，则在更新计数器时可能踫到并发问题，创建一张独立的表存储计数器，可以帮助避免缓存失效

解决独立表并发问题可以建多行，根据id随机更新，然后统计时sum()
按天或小时可以单独建行，旧时间可定时任务合并到统一的一行

五.加快ALTER TABLE操作的速度
1.两种方式：

一是在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换
二是通过“影子拷贝”，创建一张新表，然后通过重命名和删表操作交换两张表及里面的数据

2.快速创建MyISAM索引，先禁用索引，导入数据，然后重新启用索引