第四章 Schema与数据类型优化
以下简单的几个原则有助于做出更好的选择:
- 更小的通常更好
- 更小的数据类型通常更快,因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期更少
- 简单就好
- 简单类型的操作通常需要更少的CPU周期
- 尽量避免NULL
- 查询中有NULL的列,对MySQL来说更难优化
4.1 选择优化的数据类型
4.1.1 整数类型
4.1.1 实数类型
4.1.1 字符串类型
4.1.1 BLOB和TEXT类型
4.1.1 日期和时间类型
4.1.1 位数据类型
4.1.1 选择标志符(identifier)
4.1.1 特殊类型数据
- IP_V4地址 实际上是32位无符号整数,所以应该用无符号整数来存储IP地址。
- MySQL提供了INET_ATON() 和 INET_NTOA() 函数在这两种表示方法之间转化
4.2 MySQL schema中的陷阱
- 太多的列
- 太多的关联
- 一个粗略的经验法则,如果希望查询执行得快速且并发性好,单个查询最好在12个表以内做关联
- 全能的枚举
- 注意防止过多的使用枚举(ENUM)
- 变相的枚举
- 非此发明的NULL
- 可以用0,其他值代替,但是不要走极端
4.3 范式与反范式
- 第一范式:当关系模式R的所有属性都不能在分解为更基本的数据单位时,称R是满足第一范式的,简记为1NF。
每一列都是不可分割的原子属性
- 第二范式:如果关系模式R满足第一范式,并且R得所有非主属性都完全依赖于R的每一个候选关键属性,称R满足第二范式,简记为2NF。
在1NF的基础上,数据表里的每一条数据记录,都是可唯一标识的,而且所有的非主键字段,都必须完全依赖主键,不能只依赖主键的一部分。
- 第三范式:设R是一个满足第一范式条件的关系模式,X是R的任意属性集,如果X非传递依赖于R的任意一个候选关键字,称R满足第三范式,简记为3NF。
在2NF的基础上,非主键列必须唯一主键相关,非主属性之间不能有依赖关系,它们是互相独立的
4.3.1 范式的优点和缺点
- 优点
- 更少的表
- 冗余数据少
- 表更小
- 更新操作比反范式快
- 缺点
- 通常需要关联查询数据,可能会使得一些索引无效
- 可能会拆分更多的表
4.3.2 反范式的优点和缺点
-
优点
- 很好的避免了关联,可以提高查询效率
-
缺点
- 需要修改更多的表,可能会导致数据不一致问题
- 存储空间变大了
- 在数据量小的情况下,反范式不能体现性能的优势,可能还会让数据库的设计更加复杂。
4.3.3 混用范式和反范式
- 当冗余信息能大幅度提高查询效率的时候,我们才会采取反范式的优化。
- 增加冗余字段的建议
- 增加冗余冗余字段一定要符合下面的两个条件,满足下面的两个条件才可以考虑增加冗余字段
- 这个冗余字段不需要经常进行修改
- 这个冗余字段查询的时候不可或缺
4.4 缓存表和汇总表
- 将复杂的关联查询的SQL,设计为视图,并以物理表的形式保存,方便添加索引,以便提高查询性能
- 将有大量聚合查询的SQL,设计为视图,并以物理表的形式保存,减少数据检索表/行数,方便添加索引,以便提高查询性能
4.4.1 物化视图
- mysql并不原生支持物化视图
- 使用开源工具Fleviews,也可以实现物化视图。
4.4.2 计数器表
如果应用在表中保存计数器,则在更新计数器时可能碰到并发问题。
- 拆分表或数据
- 将一个表或一行计数器拆分为多表或者行,例如行,1行分成100行。在查询的时候可以聚合查询100条数据,统计总数。写入的时候可以多线程同时写入多行。
- 更快的度,更慢的写
4.5 加快alter table操作的速度
- ALTER COLUMN
- 大部分操作会导致MySQL服务中断
- 大部分ALTER COLUMN都将导致表重建
- MODIFY COLUMN
- 所有的MODIFY COLUMN都将导致表重建
- 修改列的默认值比 ALTER COLUMN慢
- CHANGE COLUMN
4.5.1 只修改.frm文件
下面这些操作是有可能不重建表的:
- 移除一个列的AUTO_INCREMENT
- 增加、移除或更改ENUM和SET常量
基本的技术是:新建空表>进行修改>替换已经存在的.frm文件
- 创建与原表一样的空表
- 执行
FLUSH tables with read lock
- 交换.frm文件
- mv schema.old schema.tmp
- mv schema.new schema.old
- mv schema.tmp schema.new
- 执行UNLOCK TABLES,释放第2步的锁
- 删除第3步的临时表
drop table tmp
4.5.2 快速创建MyISAM 索引
为了高效的载入数据到MyISAM 表中,有一个常用的技巧是先禁用索引、载入数据、然后重启索引
这个办法对唯一索引无效