【MySQL原理系列】- Schema与数据类型优化

【MySQL原理系列】- Schema与数据类型优化

---

在 MySQL 中，Schema（架构）和数据库（Database）基本是同义的概念。创建一个 Schema 实际上就是创建一个数据库，所有属于该数据库的表和其他对象都归属于这个 Schema

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schema：

• 比如反范式的设计可以加快某些类型的查询，但同时可能使另一些类型的查询变慢

• 比如添加计数表和汇总表是一种很好的优化查询的方式，但这些表的维护成本可能会很高

• MySQL独有的特性和实现细节对性能的影响也很大

一、选择优化的数据类型

选择正确数据类型的原则：

• 更小的通常更好

  • 尽量使用可以正确存储数据的最小数据类型
  • 更小的数据类型通常更快，因为占用更少的磁盘、内存和CPU缓存，处理时需的CPU周期也更少
  • 在schema中的增加数据类型的范围是一个非常耗时和痛苦的操作

• 简单类型更好

  • 简单数据类型的操作通常需要更少的CPU周期
  • 两个例子：一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间，另外一个是应该用整型存储IP地址，整型比字符操作代价更低

• 尽量避免NULL

  • 可为NULL是列的默认属性，通常最好指定列为NOT NULL，除非真的需要存储NULL值
  • 如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列会多用存储空间，同时使得索引、索引统计和值比较都更复杂
    • 当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引变成可变大小的索引
    • 传统存储模型中，每一列都要预留一定的空间，即使该列的数据为 NULL，也会占用一定的存储，而 InnoDB 通过使用单独的位（bit）表示该字段是否为 NULL，来存储 NULL 值，极大地提升了存储效率，特别是对于稀疏数据（含有大量 NULL 值的列）

• MySQL为了兼容性支持很多别名，例如INTEGER、BOOL ，以及NUMERIC ，都只是别名

二、MySQL schema设计中的陷阱

由MySQL的实现机制导致的问题

• 太多的列

  • MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列
  • 从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的
  • 转换的代价依赖于列的数量

• 太多的关联

  • “实体-属性-值”（EAV）设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱地工作
  • MySQL限制了每个关联操作最多只能有61张表，但是EAV数据库需要许多自关联
  • 因解析和优化查询代价，如果希望查询的快速且并发性好，单个查询最好在12个表以内做关联

• 太多的枚举

  • 注意防止过度使用枚举
  • 在枚举列表中更改表结构要做ALTER TABLE 操作，早期是阻塞操作，即使在5.1+版本中，如果不是在列表的末尾增加值也会一样需要ALTER TABLE 阻塞

• SET与枚举

  • 枚举（ENUM ）列允许在列中存储一组定义值中的单个值，集合（SET ）列则允许在列中存储一组定义值中的一个或多个值
  • 如果每次只有一个选项满足或不满足，则应使用枚举列代替集合列

• NULL

  • 即使需要存储一个事实上的“空值”到表中时，也不一定非得使用NULL ，也可用0、某个特殊值，或者空字符串代替
  • 但在一些场景中，使用NULL 可能会比用某个常数更好，因为选的值可能导致代码复杂很多

三、范式和反范式

• 在范式化的数据库中，每个事实数据会出现并且只出现一次

• 在反范式化的数据库中，信息是冗余的，可能会存储在多个地方

经典的“雇员，部门，部门领导”的例子：

这个schema的问题是修改数据时可能发生不一致，对这个表进行范式化，拆分成雇员表和部门表

1. 范式

• 优点：
  • 范式化的更新操作通常更快
  • 重复数据很少或者没有，只需要修改更少的数据
  • 表通常更小，可以更好地放在内存里，所以执行操作会更快
  • 很少有多余的数据意味着检索列表数据时更少需要DISTINCT 或者GROUP BY 语句，如在非范式化的结构中必须使用DISTINCT 或者GROUP BY 才能获得一份唯一的部门列表
• 缺点：
  • 关联代价昂贵，稍复杂一些的查询在符合范式的schema上都可能需要至少一次关联
  • 还可能使一些索引策略无效，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引，同时多个列在同一表中时还能创建复合索引

2. 反范式

• 优点：
  • 所有数据都在一张表中，可以很好地避免关联
  • 对大部分查询最差的情况，即表没用索引，是全表扫描，当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O
  • 单独的表也能使用更有效的索引策略，因为范式可能得跨表关联查询，非范式可单表复合索引

3. 混用范式化和反范式化

在实际应用中经常需要混用范式化和反范式化

• 最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列

• 这避免了完全反范式化的插入和删除繁琐问题，也不会把表搞得太大

• 另一个从父表冗余一些数据到子表的理由是排序的需要

四、缓存表和汇总表

有时为满足检索的需求，需要创建一张完全独立的汇总表或缓存表

• 缓存表用于临时存储频繁访问的数据，以减少对主表或数据源的直接访问

• 汇总表保存的是使用GROUP BY 语句聚合数据的表

• 假设需要计算之前24小时内发送的消息数，在一个很繁忙的网站不可能维护一个实时精确计数器

  • 作为替代方案，可以每小时生成一张汇总表，这样简单查询就可以做到，并且比实时维护计数器要高效得多，缺点是计数器并不100％精确
  • 如果必须获得准确的消息发送数量，可以以每小时汇总表为基础，把前23个完整的小时的统计表中的计数全部加起来，最后再加上开始阶段和结束阶段不完整的小时内的计数
  • 不严格的计数或小范围查询来填满间隙的严格计数，都比计算message 表的所有行要高效得多
  • 实时计算统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，故建立汇总表

• 缓存表对优化搜索和检索查询语句很有效，有时需要创建一张只含主表中部分列的缓存表

• 使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建

  • 定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引
  • 重建时，需保证数据在操作时依然可用，通过使用“影子表”来实现，建表并填充好数据后再通过一个原子的重命名操作切换影子表和原表

更快地读，更慢地写

• 为了提升读查询的速度，经常需要建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。
• 这些方法会增加写查询的负担，需要额外的维护任务
• 但是虽然写操作变得更慢了，但更显著地提高了读操作的性能

1. 物化试图

物化视图指预计算并且存储在磁盘上的表，可用基于物化视图的简单快速的查询替换原来复杂的查询

• 普通视图只是一种逻辑视图只保存查询的定义，物化视图存储了查询结果的实际数据

• 可以使用Justin Swanhart的开源工具 Flexviews 实现物化视图

• 对比传统的维护汇总表和缓存表的方法，Flexviews通过提取对源表的更改，可以增量地重新计算物化视图的内容，不需要通过查询原始数据来更新视图，效率要高得多

2. 计数器表

计数器表在Web应用中很常见，如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题

创建一张独立的表存储计数器通常是个好主意

• 这样可使计数器表小且快

• 可以帮助避免查询缓存失效

• 并且可以使用一些高级技巧

假设计数器表只有一行，对任何想要更新这一行的事务，这条记录上都有一个全局的互斥锁mutex

• 这会使得这些事务只能串行执行
• 要获得更高的并发更新性能，可以将计数器保存在多行中，每次随机选择一行进行更新，使用聚合查询 mysql> SELECT SUM(cnt) FROM hit_counter; 将每个计数值汇总得到总的计数

五、加快ALTER TABLE操作的速度

MySQL执行大部分ALTER TABLE操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表

• 如果内存不足而表又很大，而且还有很多索引，则花费时间很长

• MySQL 5.1+版本包含一些在线操作的支持，不需要在整个操作过程中锁表，一般而言，大部分ALTER TABLE 操作将导致MySQL服务中断

常见场景下能使用的技巧只有两种：

• 一种是先在一台不提供服务的机器上执行ALTER TABLE 操作，然后和提供服务的主库进行切换
• 另一种是影子拷贝，用要求的结构创建一张和源表无关的新表，再通过重命名交换两张表

不是所有的ALTER TABLE 操作都会引起表重建，例如在更改某列的默认值时，列的默认值实际上存在表的*.frm* 文件中，所以可以直接通过ALTER COLUMN操作修改这个文件，而不需要改动表本身，然而MySQL没采用这种优化，所有MODIFY COLUMN 操作都将导致表重建

1. 只修改 .frm文件

创建表时，MySQL会在数据库子目录下创建一个和表同名的 .frm文件保存表的定义

具体操作：

• 创建一张有相同结构的空表 mysql> CREATE TABLE s.film_new LIKE s.film;
• 对空表进行所需要的修改（例如增加ENUM常量，但插中间会导致已存在的数据含义被改变）
• 执行 mysql> FLUSH TABLES WITH READ LOCK;，这将会关闭所有正在使用的表，并且禁止任何表被打开
• 用操作系统命令实现，用空表的 .frm文件替换掉原表的 .frm文件
• 执行 mysql>UNLOCK TABLES释放读锁
• 最后删除创建的辅助表 mysql> DROP TABLE s.film_new;

2. 快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引

• 因为这样，构建索引会被延迟到数据完全载入以后，此时可以通过排序来构建索引，这样快很多，且索引树碎片更少更紧凑
• 不过这个方法对唯一索引无效
• 对于唯一索引，MyISAM会在内存中构造唯一索引，每插入一行都要检查唯一性；一旦索引数据的大小超过了有效内存大小，系统会将部分索引数据写回磁盘，从磁盘进行分页读取，导致磁盘 I/O 操作频率增加，载入操作会变得越来越慢