『慕课网 imooc』
『数据库设计那些事』

第一章 需求分析

数据库设计简介

什么是数据库设计？

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

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

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

数据库设计的步骤

  • 需求分析
  • 数据是什么
  • 数据有哪些属性
  • 数据和属性各自的特点有哪些
  • 逻辑设计
  • 使用ER图对数据库进行逻辑建模
  • 物理设计
  • 根据数据库自身的特点把逻辑设计转换为物理设计
  • 维护优化
  • 新的需求进行建表
  • 索引优化
  • 大表拆分

需求分析重要性简介

为什么要进行需求分析

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

要搞清楚的一些问题

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

需求分析案例

实例演示

  • 以一个小型的电子商务网站为例，在这个电子商务网站的系统中包括了几个核心模块：用户模块，商品模块，订单模块，购物车模块，供应商模块。

实例演示 - 用户模块

  • 用于记录注册用户信息
  • 包括属性：
  • 用户名
  • 密码
  • 电话
  • 邮箱
  • 身份证号码
  • 地址
  • 姓名
  • 昵称
  • …
  • 可选唯一标识属性：
  • 用户名
  • 身份证号码
  • 电话
  • 存储特点：
  • 随系统上线时间逐渐增加，需要永久存储

实例演示 - 商品模块

  • 用于记录网站中所销售的商品信息
  • 包括属性：
  • 商品编码
  • 商品名称
  • 商品描述
  • 商品品类
  • 供应商名称
  • 重量
  • 有效期
  • 价格
  • …
  • 可选唯一标识属性：
  • （商品名称，供应商名称）组合
  • 商品编码
  • 存储特点：
  • 对于下线商品可以归档存储

实例演示 - 订单模块

  • 用于用户订购商品的信息
  • 包括属性：
  • 订单号
  • 用户姓名
  • 用户电话
  • 收货地址
  • 商品编号
  • 商品名称
  • 数量
  • 价格
  • 订单状态
  • 支付状态
  • 订单类型
  • …
  • 可选唯一标识属性：
  • 订单号
  • （用户姓名、商品名称、下单时间）组合
  • 存储特点：
  • 永久存储（分表、分库存储）

实例演示 - 购物车模块

  • 用于保存用户购物时选对的商品
  • 包含属性：
  • 用户名
  • 商品编号
  • 商品名称
  • 商品何价格
  • 商品描述
  • 商品分类
  • 加入时间
  • 商品数量
  • …
  • 可选唯一标识：
  • （用户名、商品编号、加入时间）组合
  • 购物车编号
  • 存储特点：
  • 不用永久存储（设置归档、清理规则）

实例演示 - 供应商模块

  • 用于保存所销售商品的供应商信息
  • 包括属性：
  • 供应商编号
  • 供应商名称
  • 联系人
  • 电话
  • 营业执照号
  • 地址
  • 法人
  • 可选唯一标识：
  • 供应商编号
  • 营业执照号
  • 存储特点：
  • 永久存储

第二章 逻辑设计

ER图

逻辑设计师做什么的

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

名词解释

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

ER图例说明

  • 矩形：表示实体集，矩形内写实体集的名字
  • 菱形：表示联系集
  • 椭圆：表示实体的属性
  • 线段：将属性连接到实体集，或将实体集连接到联系集

设计范式概要

什么是数据库设计范式

  • 常见数据库设计范式包括：
  • 第一范式
  • 第二范式
  • 第三范式
  • BC范式
  • 当然还有第四及第五范式不过这里我们会把重点放到前三个范式上，这也是目前我们大多数数据库设计所要遵循的范式。

数据操作异常及数据冗余

  • 操作异常
  • 插入异常：如果某实体随着另一个实体的存在而存在，即缺少某个实体时无法表示这个实体，那么这个表就存在插入异常。
  • 更新异常：如果要更改表所对应的某个实体实例的单独属性时，需要将多行更新，那么就说这个表存在更新异常。
  • 删除异常：如果删除表的的某一行来反映某实体实例失效时导致另一个不同实体实例信息丢失，那么这个表中就存在删除异常。
  • 数据冗余：
  • 是指相同的数据在多个地方存在，或者说表中的某个列可以由其他列计算得到，这样就说表中存在着数据冗余。

第一范式（1NF）

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

第二范式（2NF）

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

第三范式（3NF）

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

Boyce.Codd范式（BCNF）

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

第三章 物理设计

数据库物理设计要做什么

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

选择哪种数据库

  • 常见的DBMS系统
  • 开源数据库（适合于互联网项目）
    • MySQL
    • PgSQL
  • 商业数据库（更适合企业级项目）
    • Oracle
    • SQLServer（Windows、.Net）

MySQL常用存储引擎

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

数据库表及字段的命名规则

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

数据库字段类型选择原则

  • 例子：birthday
  • char(10)：’1978-03-01’
  • varchar(20)：’1978-03-01’
  • datatime：1978-03-01
  • int：257529600
  • 列的数据类型一方面影响数据存储空间的开销，另一方面也会影响数据查询性能。当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。
  • 以上选择原则主要是从下面两个角度考虑：
  • 在对数据进行比较（查询条件、JOIN条件及排序）操作时：同样的数据，字符处理往往比数字处理慢。
  • 在数据库中，数据处理以页为单位，列的长度越小，利于性能提升。

数据类型占用字节表

『更多Mysql数据类型』

列类型 存储空间
TINYINT 1字节
SMALLINT 2个字节
MEDIUMINT 3个字节
INT 4个字节
BINGINT 8个字节
DATE 3个字节
DATETIME 8个字节
TIMESTAMP 4个字节
CHAR(M) M字节，1 <= M <= 255
VARCHAR(M) L+1字节，在此L <= M和1 <= M <=255

数据库如何具体选择字段类型

char与varchar 如何选择

  • 原则：
  • 如果列中要存储的数据长度差不多是一致的，则应该考虑用char；否则应该考虑用varchar。
  • 如果列中的最大数据长度小于50Byte，则一般也考虑用char。（当然，如果这个列很少用，则基于节省空间和减少I/O的考虑，还是可以选择用varchar）
  • 一般不宜定义大于50Byte的char类型列。

decimal与float 如何选择

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

时间类型如何存储

  • 使用int来存储时间字段的优缺点
  • 优点：
    • 字段长度比datatime小
  • 缺点：
    • 使用不方便，要进行函数转换
  • 限制：
    • 只能存储到2038-1-19 11:14:07即2^32 为2147483648
  • 需要存储的时间粒度
  • 年 月 日 小时 分 秒 周

数据库设计其它注意事项

如何选择主键

  • 区分业务主键和数据库主键
  • 业务主键用于标识业务数据，进行表与表之间的关联；
  • 数据库主键为了优化数据存储（Innodb会生成6个字节的隐含主键）
  • 根据数据库的类型，考虑主键是否要顺序增长
  • 有些数据库是按主键的顺序逻辑存储的
  • 主键的字段类型所占空间要尽可能的小
  • 对于使用聚集索引方式存储的表，每个索引后都会附加主键信息

避免使用外键约束

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

避免使用触发器

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

关于预留字段

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

反范式化表设计

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

为什么反范式化

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

第四章 维护优化

数据库维护和优化要做什么

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

数据库如何维护数据字典

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

数据库如何维护索引

  • 如何选择合适的列建立索引？
  • 出现在WHERE从句，GROUP BY 从句，ORDER BY 从句中的列
  • 可选择性高的列要放到索引的前面
  • 索引中不要包括太长的数据类型

数据库如何维护表结构

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

数据库中适合的操作

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

数据库表的垂直和水平拆分

表的垂直拆分

  • 为了控制表的宽度可以进行表的垂直拆分
  • 经常一起查询的列放到一起
  • text，blob等大字段拆分出到附加表中
  • 为了控制表的大小可以进行表的水平拆分