第一部分 设计库设计流程

一个完整的数据库设计流程包含以下四个组成部分：需求分析、逻辑设计、物理设计和维护优化

需求分析

需求分析主要的任务是分析：

• 数据是什么
• 数据有哪些属性
• 数据和属性各自的特点有哪些

逻辑设计

逻辑设计就是使用ER图对数据库进行逻辑建模

一个简单的例子如下：

\1.png

图中：矩形表示实体集，菱形表示联系集，椭圆表示实体的属性，线段将属性连接到实体集或将实体集连接到联系集

物理设计

根据数据库自身的特点将逻辑设计转换为物理设计

维护优化

主要任务包括：

• 新的需求进行建表
• 索引优化
• 大表拆分

第二部分 数据库设计范式

常见的数据库设计范式包括：第一范式，第二范式，第三范式及BC范式，这是目前我们大多数数据库设计所要遵循的范式。

在了解范式之前，我们有必要了解一下数据操作异常及数据冗余的定义。

• 插入异常：如果某实体随着另一个实体的存在而存在，即缺少某个实体时无法表示这个实体，那么这个表就存在插入异常
• 更新异常：如果更改表所对应的某个实体市里的单独属性时，需要将多行更新，那么就说这个表存在更新异常
• 删除异常：如果删除表的某一行来反映某实体实例失效时导致另一个不同实体实例信息丢失，那么这个表就存在删除异常

第一范式（1NF）

定义：数据库表中的所有字段都是单一属性，不可再分的。
1NF是所有关系型数据库的最基本要求，你在关系型数据库管理系统（RDBMS），例如SQL Server，Oracle，MySQL中创建数据表的时候，如果数据表的设计不符合这个最基本的要求，那么操作一定是不能成功的。

第二范式（2NF）

定义：数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖。
部分函数依赖是指存在着组合关键字中的某一关键字决定非关键字的情况。
换句话说：所有单关键字段的表都符合第二范式。

举一个简单的例子：

\2.png

第三范式（3NF）

定义：第三范式是在第二范式的基础上定义的，如果数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。

举一个简单的例子：

\3.png

Boyce.Codd范式（BCNF）

定义：在第三范式的基础之上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式。
也就是说如果是复合关键字，则复合关键字之间也不能存在函数依赖关系

举一个简单的例子：

\4.png

第三部分 物理设计细节（以MySql为例）

物理设计要做什么

• 选择合适的数据库管理系统
• 定义数据库、表及字段的命名规范
• 根据所选的DBMS系统选择合适的字段类型
• 反范式设计

MySql 物理设计

1、MySql常用的存储引擎

\5.png

2、字段类型的选择原则

常见类型存储占用空间：

\6.png

通用原则：

• 在对数据进行比较（查询条件、JOIN条件及排序）操作时，同样的数据，字符处理往往比数字处理慢。

• 在数据库中，数据处理以页为单位，列的长度越小，利于性能提升。

char与varchar如何选择：

• 如果列中存储的数据长度差不多是一致的，则应该考虑用char；否则应该考虑用varchar
• 如果列中最大的数据长度小于50字节，则一般也考虑用char
• 一般不宜定义大于50字节的char类型列

decimal与float如何选择：

• decimal用于存储精确数据，而float只能用于存储非精确数据。故精确数据只能选择用decimal类型。
• 由于float的存储空间开销一般比decimal小（精确到7位小数只需要4字节，精确到15位小数只需要8字节），故非精确数据优先选择float类型

时间类型的选择：
使用int来存储时间字段的优点是比datetime小，但使用不方便要进行转换，限制是只能存储到2038-1-19 11:14:07 即2^32

3、其它注意事项

如何选择主键：

• 区分业务主键和数据库主键：业务主键用于标识业务数据，进行表与表之间的关联；数据库主键为了优化数据存储（没有的话，Innodb会生成6个字节的隐含主键）
• 跟踪数据库的类型，考虑主键是否要顺序增长
• 主键的字段类型所占空间要尽可能的小

避免使用外键约束：

• 降低数据导入效率
• 增加维护成本
• 虽然不建议使用外键约束，但是相关联的列上一定要建立索引

避免使用触发器：

• 降低数据导入的效率
• 可能会出现意想不到的数据异常
• 使业务逻辑变得复杂

关于预留字段：

• 无法准确的指导字段的类型
• 无法准确的指导预留字段中所存储的内容
• 后期维护预留字段所要的成本，同增加一个字段所需要的成本是相同的
• 严禁使用预留字段

4、反范式设计

反范式设计的原因：

• 减少表的关联数量
• 增加数据的读取数量
• 反范式一定要适度

第四部分 维护优化细节

1、维护与优化要做什么

• 维护数据字典
• 维护索引
• 维护表结构
• 在适当的时候对表进行水平拆分或垂直拆分

2、如何维护数据字典

• 利用第三方工具对数据字典进行维护
• 利用数据库本身的备注字段来维护数据字典

3、如何维护索引

如何选择合适的列建立索引：

• 出现在WHERE从句，GROUP BY从句中的列
• 可选择性高的列要放到索引的前面
• 索引中不要包含太长的数据类型

注意事项：

• 索引并不是越多越好，过多的索引不但会降低写效率而且会降低读的效率
• 定期维护索引碎片
• 在SQL语句中不要使用强制索引关键字

4、如何维护表结构

• 使用在线变更表结构的工具：MySQL5.5之前可以使用pt-online-schema-change，MySQL5.6之后本身支持在线表结构的变更
• 同时对数据字典进行维护
• 控制表的宽度和大小

5、数据库中适合的操作

• 批量操作VS逐条操作
• 禁止使用SELECT * 这样的查询
• 控制使用用户自定义函数
• 不要使用数据库中全文索引

6、表的垂直拆分

\7.png

7、表的水平拆分

\8.png

\9.png

参考网站：

• 数据库设计那些事