数据库优化之结构设计

设计好处

• 良好的数据库逻辑设计和物理设计师数据库获得高性能的基础
• 范式化设计和反范式化设计（减少冗余、减少异常、让数据组织的更加和谐）

• 优化目的

  - 减少数据冗余（尽量）
  - 尽量避免数据维护中出现更新、插入和删除等异常
      - 插入：如果表中的某个实体随着另一个实体而存在
      - 更新：如果更改表中的某个实体的单独属性时，需要对多表进行更新
      - 删除：如果删除表中的某一时则会导致其他实体的消失

设计过程

• 需求分析

  • 全面了解产品设计的需求
  • 存储需求（比如一对多，多对一等）
  • 数据处理需求
  • 数据的安全性和完整性

• 逻辑分析

  • 设计数据的逻辑存储结构
  • 数据实体之前的逻辑关系，解决数据冗余和数据维护异常

• 物理设计

  • 根据所使用数据特点设计表结构

• 维护优化

  • 对索引、存储结构等进行优化

• 范式化

  • 设计没有数据冗余和数据维护异常的数据库结构

• 反范式化

  • 针对范式化而言的，在前面介绍了数据库设计的范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对数据库设计范式的要求进行违法，而允许存在少量的数据冗余，换句话来说反范式化就是使用空间来换取时间

本篇重点解释物理设计、范式化与反范式化化各自优缺点；其他将在文章《数据库优化》系列一一讲明；

范式化与反范式化

• 范式化设计的优缺点

  • 优点

    • 尽量减少数据冗余
    • 范式化的更新操作比反范式化更快
    • 范式化的表通常比反范式更小

  • 缺点

    • 对于查询需要对多个表进行关联（mysql限制不能超过10张表）
    • 更难进行索引优化

• 反范式化设计的优缺点

  • 优点

    • 减少表的关联
    • 更好的进行索引优化

  • 缺点

    • 存在数据冗余及数据库维护异常
    • 对数据修改需要更多的成本

设计范式化要求的三范式：
第一范式

• 数据库表中的所有字段都只具有单一属性
• 单一属性的列是由基本的数据类型所构成的
• 设计及出来的表都是简单的二维表

第二范式

• 要求一个表中只具有一个业务主键，也就是说符合第二范式的表中不能存在非主键列对只对部分主键的依赖关系

第三范式

• 数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系

物理设计

• 物理设计的内容

  • 定义数据库、表及字段的命名规范
  • 选择合适的存储引擎
  • 为表中的字段选择合适的数据类型
  • 建立数据库结构

• 定义数据库、表及字段的命名规范

  • 可读性原则
  • 表意行原则
  • 长名原则
• 选择合适的存储引擎

存储引擎	事务	锁粒度	主要应用	忌用
MyISAM	不支持	支持并发插入的表级锁	SELECT、INSERT	读写操作频繁
MRG_MYISAM	不支持	支持并发插入的表级锁	分段归档，数据仓库	全局查找过多的场景
Innodb	支持	支持MVCC的行级锁	事务处理	无
Archive	不支持	行级锁	日志记录，只支持insert，select	需要随机读取，更新，删除
Ndb cluster	支持	行级锁	高可用性	大部分应用

• 为表中的字段选择合适的数据类型（数据页）

  • 当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型

• 如何为Innodb选择主键

  • 主键应该尽可能的小
  • 主键应该是顺序增长的
  • Innodb的主键和业务主键可以不同；

---

相关链接
《数据库优化之实例和故事》、《 数据库优化之什么影响性能》

作者：不动峰
博客园：http://www.cnblogs.com/mylly/
版权所有，欢迎保留原文链接进行转载：)