需求分析阶段

1. 数据库设计简介

1.1 什么是数据库设计？

简单来说，数据库设计就是根据业务系统的具体需要，结合我们所选用的DBMS（数据库管理系统），为这个业务体系构造出最优的数据存储模型，并建立好数据库中的表结构以及表与表之间的关联关系的过程，使其能够有效的对应用系统中的数据进行存储，并可以高效的对已经存储的数据进行访问。

1.2 为什么要进行数据库的设计？

优良的设计	糟糕的设计
减少数据冗余	存在大量的数据冗余
避免数据维护异常	存在数据插入、更新、删除异常
节约存储空间	浪费大量存储空间
高效的访问	访问数据低效

在数据库最初使用是，由于数据量较小，可能看不出数良好的数据库设计的必要。但当业务越来越多，越来越复杂，数据量不断增长时，糟糕的数据库设计便阻碍了公司正常业务的进行，而且对开发人员会造成很大的影响。所以，在系统设计开始就对数据库进行良好的设计是非常有必要的。

2. 数据库设计的步骤

数据库设计的步骤主要分为以下四个阶段：

2.1 需求分析

数据库设计的需求分析与应用程序的需求分析不同，这里它的主要作用点是：

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

2.2 逻辑设计

主要使用ER图对数据库进行逻辑建模

2.3 物理设计

根据数据库自身的特点把逻辑设计转化为物理设计，即用不同的DBMS建立相应的数据库和数据表等。

2.4 维护优化

为保持数据库高效而稳定，在本阶段中主要做的工作如下：

根据新的需求进行建表
索引拆分
大表拆分（水平拆分和垂直拆分）

3. 需求分析的重要性

3.1 为什么要进行需求分析

了解系统中所要存储的数据
了解数据的存储特点
了解数据的生命周期

通过进行需求分析，对于我们构建表的过程中具有非常重要的作用，更影响到了后期开发的难度。

3.2 要搞清楚的问题

实体和实体之间的关系（一对一，一对多，多对多）
实体包含的属性有什么
哪些属性或属性的组合可以唯一标识一个实体

逻辑设计阶段

1. 逻辑设计简介

1.1 逻辑设计用来做什么

见需求转化为数据库的逻辑模型
通过ER图的形式对逻辑模型进行展示
与将来选用的具体DBMS系统无关

1.2 名词解释

关系：一个关心对应通常诉说的一张表
元组：表中的一行即为一个元组
属性：表中的一列即为一个属性；每个属性都有一个名称，成为属性名
候选码：表中的某个属性组，它可以唯一确定一个元组
主码：一个关系有多个候选码，选定某一个作为主码，也称主键
域：属性的取值范围
分量：元组中的一个属性值

1.3 ER图例说明

矩形：表示实体集，矩形内部写实体集名字
菱形：表示联系集
- 椭圆：表示实体的属性（属性名加下划线表示主键）
线段：将属性连接到实体集，或将联系集连接到实体集

2. 设计范式概要

在进行数据库设计的时候，我们会碰到建立表的问题，例如：

用户信息和购物车信息 => 一张表

或

用户信息 => 一张表

购物车信息 => 一张表

到底应该怎样设计比较好呢？这里就引出了数据库设计范式的问题。

2.1 什么是数据库设计范式

常见的数据库设计范式包括：

第一范式，第二范式，第三范式以及BC范式

还有第四范式以及第五范式，不过我们重点是在前三个范式上，也是大多数数据库设计索要遵循的范式。

2.2 数据操作异常以及数据冗余

范式通常就是为了解决数据操作异常以及数据冗余而存在的，数据操作异常主要指以下三类：

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

注：一般一个表存在插入异常的同时，必定存在着更新异常和删除异常！

数据冗余是指相同的数据在多个地方存在，或者说表中的某个列可以由其他列计算得到。数据冗余会产生许多无用的不必要的数据，给数据库表带来更大的压力，不利于以后的开发和维护。一般设计时应避免出现数据冗余。

2.3 第一范式（1NF）

定义：数据库表中的所有字段都是单一属性，不可再分的。这个单一属性是由基本的数据类型所构成的，如整数，浮点数，字符串等。换句话说，第一范式要求数据库中的表都是二维表。

2.4 第二范式（2NF）

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

举个栗子：

商品名称	供应商名称	价格	描述	重量	供应商电话	有效期	分类
可乐	饮料一厂	3.00		500ml	1234567	2019.01	饮料
可乐	饮料二厂	3.00		500ml	7654321	2019.01	饮料

上表中由于供应商和商品之间是多对多的关系，所以只有使用商品名称和供应商名称组合才可以唯一标识出一件商品，换句话说，主键应为：（商品名称+供应商名称）。这里，表中存在着以下部分函数依赖：

（商品名称）决定（价格，描述，重量，有效期）

（供应商名称）决定（供应商电话）

这违反了第二范式的规则，所以存在着数据操作异常以及数据冗余。一个正确的修改例子如下：（将原来的一张表拆分）

商品ID	商品名称	价格	描述	重量	有效期	分类
1	可乐	3.00		500ml	2019.01	饮料

供应商ID	供应商名称	供应商电话
1	饮料一厂	1234567
2	饮料二厂	7654321

2.5 第三范式（3NF）

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

商品名称	价格	商品描述	重量	有效期	分类	分类描述
可乐	3.00		500ml	2019.01	酒水饮料	碳酸饮料
苹果	5.00		500g		生鲜食品	水果

上述表中存在着以下传递函数依赖关系：

（商品名称）决定（分类）决定（分类描述）

也就是说，存在非关键字段“分类描述”对关键字段“商品名称”的传递函数依赖。

这里，（分类，分类描述）对于每一个商品都会进行记录，存在着数据冗余，同时还存在着数据操作异常。一个正确的修改例子如下：（拆分表）

商品ID	商品名称	价格	商品描述	重量	有效期
1	可乐	3.00		500ml	2019.01

分类ID	分类	分类描述
1	酒水饮料	碳酸饮料

2.6 BC范式（BCNF）

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

举个栗子：

供应商	商品ID	供应商联系人	商品数量
饮料一厂	1	张三	10
饮料二厂	2	李四	20
饮料二厂	1	王五	20

这里我们假定，供应商联系人只能受雇于一家供应商，没加供应商可以供应多个商品。则存在着如下决定关系：

（供应商，商品ID）决定（联系人，商品数量）

（联系人，商品ID）决定（供应商，商品数量）

而由于下列关系的存在，则不符合BC范式的要求：

（供应商）决定（供应商联系人）

（供应商联系人）决定（供应商）

一种正确的修改方式如下：（拆分）

供应商	商品ID	商品数量
饮料一厂	1	10
饮料一厂	2	20
饮料二厂	1	20

供应商	供应商联系人
饮料一厂	张三
饮料一厂	李四
饮料二厂	王五

物理设计阶段

1. 物理设计简介

物理设计就是将逻辑设计转化为真正使用DBMS创建和管理数据库数据表的过程，在这个过程中同样存在着许多设计上的选择和问题。那么物理设计要做什么？

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

2. 选择合适的数据库管理系统（DBMS）

这里暂时不探讨NoSql数据库，只对传统关系型数据库进行一点简单的区分选择。以下是常见的关系型数据库

Oracle：属于商业数据库，在使用时不得不考虑版权问题。在业界中口碑较好，性能高，比较适合大型事务操作，因为在Oracle数据库中事务操作成本较低。可以运行在Windows、类Unix操作系统上，使用Java、PHP等语言较为适合
SQLServer：属于商业数据库，版权问题需考虑。事务操作成本较高，比较适合小型事务操作。由于是微软开发的，它只支持运行在Windows操作系统，并且与 .Net 语言契合度较高。
MySQL：开源数据库，只要符合社区协议，使用时不用支付任何费用。像SQLServer一样，事务操作成本较高，也是比较适合小型事务操作。可运行在Windows、类Unix操作系统上，同样适合Java、PHP等编程语言。

总体来说，Oracle、SQLServer数据库适合于大型企业级项目，而MySQL数据库更适合于互联网项目。

3. MySQL 常用的存储引擎

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

目前，我们常用的是 Innodb 引擎。

4. 表及字段的命名规则

所有对象命名应该遵循以下原则：

可读性原则：使用大写和小写来格式化的库对象名字，以获得良好的可读性。例如：使用UserAddress而不是useraddress来提高可读性（注意，有些DBMS系统对表名的大小写是敏感的）
表意性原则：对象的名字应该能够描述它所标识的对象。例如：对于表，表的名称应该能够体现表中存储的数据内容；对于存储过程，存储过程名称应该能够体现存储过程的功能。
长名原则：尽可能少使用或者不使用缩写，适用于数据库（DATABASE）名之外的任一对象

5. 字段类型的选择原则

列的数据类型一方面影响数据存储空间的开销，另一方面也会影响到数据查询性能。当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

以下是 MySQL 中每种数据类型占用空间大小的对比：

列类型	存储空间
TINYINT	1 字节
SMALLINT	2 字节
MEDIUMINT	3 字节
INT	4 字节
BIGINT	8 字节
DATE	3 字节
DATETIME	8 字节
TIMESTAMP	4 字节
CHAR（M）	M 字节，1 <= M <= 255
VARCHAR（M）	L + 1 字节，L <= M 且 1 <= M <= 255

以上选择原则主要是从下面两个角度考虑的：

在对数据进行比较（查询条件，JOIN条件以及排序）操作时，同样的数据，字符处理往往比数字处理慢。
在数据库中，数据处理以页为单位，列的长度越小，每页存储量越多，性能也就越好。

6. 字段类型的具体选择

6.1 char 与 varchar

如果列中要存储的数据长度差不多是一致的，则考虑用 char，否则考虑用 varchar。
如果列中的最大数据长度小于 50 Byte，则一般考虑用 char。（如果此列很少使用，出于节省空间和减少 I/O 的考虑，还是可以选择使用 varchar）
一般不宜定义大于 50 Byte 的 char 类型列。

6.2 decimal 与 float

decimal 用于存储精确数据，而 float 只能用于存储非精确数据。
由于 float 占用存储空间一般比 decimal 小，所以非精确数据优先选择 float 类型。

6.3 时间类型的存储

使用 INT 来存储时间字段（一般用于很少查询的情况）

优点：字段长度比 DATETIME 小

缺点：使用不方便，要进行函数转换才能获得具体表示的时间

限制：只能存储到 2038-01-19 11:14:07，即 2^32 为 2147483648
根据需要存储的时间粒度来选择存储类型

年，月，日，周，时，分，秒

7. 其他注意事项

7.1 如何选择主键

区分业务主键和数据库主键

业务主键用于标识业务数据，进行表与表之间的关联；

数据库主键为了优化数据存储（Innodb 会生成6个字节的隐含主键）
根据数据库的类型，考虑主键是否要顺序增长

有些数据库是按主键的顺序逻辑存储的
主键的字段类型所占空间要尽可能的小

对于使用聚集索引方式存储的表，每个索引后都会附加主键信息。

7.2 避免使用外键约束

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

7.3 避免使用触发器

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

7.4 关于预留字段

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

8. 反范式化表设计

什么是反范式化？反范式化是针对范式化而言的，它就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余。换句话说，反范式化就是使用空间来换取时间。

举个栗子：

以下是一个符合范式化的设计数据库表结构：

用户表：	用户ID	姓名	电话	地址	邮编
订单表：	订单ID	用户ID	下单时间	支付类型	订单状态
订单商品表：	订单ID	商品ID	商品数量	商品价格
商品表：	商品ID	名称	描述	过期时间

对于上述设计，查询订单详情语句如下：

SELECT b.用户名, b.电话, b.地址, a.订单ID, SUM(c.商品价格*c.商品数量) as 订单价格, c.商品价格 \
  FROM "订单表" a \
  JOIN "用户表" b ON a.用户ID = b.用户ID \
  JOIN "订单商品表" c ON c.订单ID = b.订单ID \
  JOIN "商品表" d on d.商品ID = c.商品ID

而对于一个反范式化设计如下：

用户表：	用户ID	姓名	电话	地址	邮编
订单表：	订单ID	用户ID	下单时间	支付类型	订单状态	订单价格	姓名	地址	电话
订单商品表：	订单ID	商品ID	商品数量	商品价格	商品名称	过期时间
商品表：	商品ID	名称	描述	过期时间

此时，当我们想要查询订单详情时，查询语句将会变得非常简单：

1	SELECT a.用户名, a.电话, a.地址, a.订单ID, a.订单价格 FROM "订单表" a

由此可见，当我们读写比例比较大时，即读取数据次数比起写入次数总是要多很多时，使用反范式化构建表还是非常有必要的，这样可以加快我们的查询效率。

总结起来，反范式化的原因主要有以下几点：

减少表的关联数量
增加数据的读取效率

注意：反范式化一定要适度，不然可能造成数据的操作异常，得不偿失。

维护优化阶段

随着业务的增长，需求在不断的变化，此时对于数据库的维护和优化就显得尤为重要。在这个阶段中，我们主要做的事情有以下几方面：

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

1. 维护数据字典

维护数据字典的方式主要有以下几种：

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

如使用 MySQL 中的 COMMENT 关键字
导出数据字典

2. 维护索引

索引的选择一般有以下几种：

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

注意：

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

3. 维护表结构

3.1 常用的维护表结构的方法如下：

使用在线变更表结构的工具

在 MySQL 5.5 之前可以使用 pt-online-schema-change 来处理（此时涉及到之前提过的避免使用触发器，因为有的第三方工具在运行过程中，是会给数据库添加一些触发器来支持它们的操作，如果自己之前设置过触发器，很可能会损坏数据。）

在 MySQL 5.6 之后，数据库本身支持了在线表结构的变更
同时对数据字典进行维护

表结构更变的同时，一定要记得相应的更变数据字典
控制表的宽度和大小

3.2 数据库中适合的操作

批量操作 vs 逐条操作

显然，数据库适合批量操作，逐条操作适合在程序中完成
禁止使用 SELECT * 这样的语句

会造成 IO 的浪费，同时，如果表结构变更，可能会对程序造成影响
控制使用用户自定义函数

大量使用自定义函数会对索引产生不好的影响，自定义函数往往比较低效
不要使用数据库中的全文索引

需要这种功能，最好使用搜索引擎代替全文索引，提升效率

4. 数据表的拆分

当数据表的结构变化，如表越来越宽，或表越来越长，我们应该分别使用垂直拆分和水平拆分来控制表的大小和宽度，以保证数据库执行的效率

4.1 表的垂直拆分

通常以如下方法进行垂直拆分：

经常一起查询的列放到一起

这样不需要进行 JOIN 操作，减少了 I/O，同时还简化了查询语句
text, bolb 等大字段拆分出到附加表中

4.2 表的水平拆分

一般采用 hash 的方法，将一张很大的表拆分为几个拥有相同属性的小表

4.3 两种拆分方式总结

垂直拆分解决了表的宽度问题，水平拆分解决了表的数据量大小问题。两种拆分都可以优化数据库表的 I/O，进而提升了数据库的效率。

参考

慕课网 - 数据库设计那些事