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

第四章 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文件

1. 创建与原表一样的空表
2. 执行FLUSH tables with read lock
3. 交换.frm文件
   • mv schema.old schema.tmp
   • mv schema.new schema.old
   • mv schema.tmp schema.new
4. 执行UNLOCK TABLES,释放第2步的锁
5. 删除第3步的临时表 drop table tmp

4.5.2 快速创建MyISAM 索引

为了高效的载入数据到MyISAM 表中，有一个常用的技巧是先禁用索引、载入数据、然后重启索引
这个办法对唯一索引无效

4.5 总结