《高性能MySQL》阅读-范式和反范式

【卧佛一枕眠】诗号：
苦海无边，欲醒还眠，千程如梦，一枕悠然。

MySQL schema 设计中的陷阱

1. 设计中的陷阱
   1. 太多的列
   2. 太多的关联
   3. 全能的枚举：注意防止过度使用枚举
   4. 变相的枚举
   5. 非此发明的NULL：建议尽量避免使用NULL，可以使用0、某个特殊值、或者空字符串作为代替

范式和反范式

1. 三范式：
   ① 第一范式：强调的是列的原子性，即列不能够再分成其他的几列
   ② 第二范式：首先要满足第一范式，另外包含两部分，一是表必须有一个主键；二是没有包含在主键中的列必须依赖于主键，而不能只依赖于主键的一部分
   ③ 第三范式：首先满足第二范式，另非主键列必须直接依赖主键，不能存在传递依赖。即不能存在：非主键列A依赖于非主键B，非主键B依赖于主键的情况
2. 反范式：违反范式规则的数据库设计理念
3. 范式的优点和缺点：
   ① 范式化的更新操作通常比反范式化要快
   ② 当数据较好的范式化时，就只有很少或没有重复数据，所有只需要修改更少的数据
   ③ 范式化的表通常更小，可以更好的存放在内存中，所以执行操作会更快
   ③ 很少有多余的数据意味着检索列表数据时更少需要distinct或者group by 语句
   ④ 范式化的缺点是通常表需要关联，稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次的关联，可能会更多。这不但代价昂贵，也可能是一些索引策略无效。
4. 反范式的优点和缺点：
   ① 反范式化的表因为所有的数据都在一张表中，可以很好的避免关联
   ② 如果不需要关联表，则对大部分查询最差的情况－即使表没有使用索引－是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O。
   ③ 关联会使的，在执行数据库语句时，使得需要在一个索引中又排序又过滤。如果采用反范式化组织数据将两张表的字段合并一下，并且增加一个索引，就可以不通过关联写出这个查询。
   ④ 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失。
5. 混用范式化和反范式化
   ① 完全范式和完全反范式在实际的开发中几乎不存在。
   ② 最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在MySQL5.0和更新版中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。
   ③ 另一个从父表冗余一些数据到子表的理由是排序的需要
   ④ 缓存衍生值也是有用的；就是说对于一些数量统计显示，可以单独设定一个子查询来计算并显示或者在主表中建立一个统计字段保存和更新这个值
6. 缓存表和汇总表
   ① 有时候提升性能最好的办法就是在同一张表中保存衍生的冗余数据；并且，有时可能需要创建一张完全独立的汇总表或缓存表
   ② 不严格的计数或者通过小范围查询填满间隙的严格计数-都是比统计表中所有的行要有效的多，这就是建立汇总表的关键
   ③ 缓存表其对优化搜索和检查查询语句很有效。这些查询语句经常需要特殊的表和索引结构，跟普通OLTP操作用的表有区别
   ④ 例如，可能会需要很多不同的索引组合起来加速各种类型的查询。这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表。一个有用的技巧是对缓存表使用不同的存储引擎
   ⑤ 在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多的碎片，以及有完全顺序组织的索引
   ⑥ 当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用