How 2 design a database.
数据库设计的基本步骤
需求分析 -> 逻辑设计 -> 物理设计 -> 维护优化
在进行需求分析时, 要明确的是:
- 数据是什么
- 数据有哪些属性
- 数据和属性各自的特点有哪些
从逻辑设计到物理设计, 根据选择数据库的特点不同实现起来也不一样.
一般维护优化, 譬如:
- 根据新的需求进行建表
- 索引优化
- 大表拆分
需求分析
- 系统中要存储的数据 ( 冷热分离? )
- 数据的存储特点
- 数据的生命周期
我们将数据库中存储的对象称之为实体.
而实体与实体间是存在不同的关系的( 一对一, 一对多, 多对多 ) 表 <–> 实体
实体中的属性 – 哪些属性或者属性的组合可以唯一标示一个实体.
譬如: 一个电商的用户表中, 可唯一表示一个用户的属性有, 用户名, 身份证, 电话等.
而这样的属性的存储是随着系统上线时间而逐渐增加的, 且需要永久存储.
而商品表中, 属性繁多(编码, 名称, 描述, 品类, 供应商, 重量, 有效期, 价格...), 但在这些属性中, 能够唯一标示的属性类如 ( 商品名称,商品编码等 ), 对于已经下线的商品, 我们可以进行归档存储, 或者进行表的迁移.
逻辑设计[ 数据库设计的范式 ]
在逻辑设计中, 我们将需求化成数据库的逻辑模型.
通过ER图的形式对逻辑模型进行展示.
在ER图中,矩形代表实体集,菱形代表联系集,椭圆代表实体的属性,线段将属性联系到实体集.
提到数据库的逻辑设计, 就不得不说一下数据库设计的范式.
设计范式用来使数据库变得简洁高效, 避免数据冗余和异常发生.
常见的数据库范式包括: 第一范式, 第二范式, 第三范式以及BC范式.
这些范式一个比一个复杂(或者说每一个都是以前一个作为基础的.)
目前的数据库设计大多遵循这些.
第一范式
第一范式的定义是:
数据库中的字段都是单一属性, 不可再分的
换句话说: 第一范式要求数据库中的表都是二维表.
| ID | 用户名 | 密码 | 姓名 | 电话 |
|---|---|---|---|---|
| 1 | Justin | ** | www | 0000000 |
很简单 对嘛, 基本上我们设计的表都是符合第一范式的.
第二范式
第二范式定义是:
数据库中的表中不存在非关键字对任一候选关键字段的部分函数依赖.
其中, 部分函数依赖指的是存在这组合关键字中的某一关键字决定非关键字的情况.
看不懂在说啥?
换句话说:所有的[!单!]关键字段的表都符合第二范式
看下面的例子:
| 商品名称 | 供应商名称 | 价格 | 描述 | 重量 | 电话 | 有效期 | 分类 |
|---|---|---|---|---|---|---|---|
| 可乐 | 一厂 | 3.00 | www | 300g | 88888888 | 2017.05 | 饮料 |
| 可乐 | 二厂 | 3.50 | www | 333g | 00000000 | 2017.05 | 饮料 |
注意这个表, 可乐这个商品并不具备唯一标示的能力(因为存在不同的生产商), 所以这是一组组合关键字, 而看一下函数依赖关系就会发现:
(商品名称) -> ( 价格, 描述, 重量, 有效期 )
(供应商名称) -> ( 供应商电话 )
对于这样的表, 进行优化后成为以下三个表:
|ID|商品名称|价格|描述|重量|有效期|分类|
|:—:|:—:|:—:|:—:|:—:|:—-:|
|1|可乐|3.00|www|300g|2017.05|饮料|
|2|可乐|3.50|www|333g|2017.05|饮料|
| ID | 供应商名称 | 供应商电话 |
|---|---|---|
| 1 | 一厂 | 88888888 |
| 2 | 二厂 | 00000000 |
| 供应商ID | 商品ID |
|---|---|
| 1 | 1 |
| 2 | 2 |
这样, 单个表中仅存在单关键字, 它就满足第二范式了, 之前存在的种种异常和数据冗余就不存在了.
第三范式
第三范式建立在第二范式的基础之上.
先不说定义, 请看表:
|商品名称|价格|描述|重量|有效期|分类|分类描述|
|:—:|:—:|:—:|:—:|:—-:|:—:|
|可乐|3.00|www|300ml|2017.05|酒水饮料|碳酸饮料|
|苹果|3.50|www|500g| |生鲜食品|水果|
该表中存在以下传递函数:
( 名称 ) -> ( 分类 ) -> ( 分类描述 )
非关键字段**’分类描述’, 对名称的传递**函数有依赖.
对于每一个商品我们都会进行记录, 所以存在着冗余. 同时还存在数据的插入, 更新及删除异常.
因此, 我们将其优化成如下的三张表:
|商品ID|商品名称|价格|描述|重量|有效期|
|:—:|:—:|:—:|:—:|:—:|
|1|可乐|3.00|www|300g|2017.05|
|2|可乐|3.50|www|333g|2017.05|
| 分类ID | 分类 | 分类描述 |
|---|---|---|
| 1 | 酒水饮料 | 碳酸饮料 |
| 2 | 生鲜食品 | 水果 |
| 分类ID | 商品ID |
|---|---|
| 1 | 1 |
| 2 | 2 |
对第三范式的定义是:
如果数据表中不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式.
BC范式
最后来看一下BC范式, 这是对于第三范式的扩展.
定义:
在第三范式的基础之上,数据库表中如果不存在任意字段对任一候选关键字段的传递函数依赖则符合BC范式.
如果是复合关键字, 则符合关键字之间也不能存在函数依赖关系.
来看这样一个表:
| 供应商 | 商品ID | 供应商联系人 | 商品数量 |
|---|---|---|---|
| 饮料一厂 | 1 | 张三 | 10 |
| 饮料二厂 | 2 | 李四 | 20 |
| 饮料三厂 | 1 | 王五 | 20 |
该表中存在不符合BCNF要求的以来依赖:
( 供应商, 商品ID ) -> ( 供应商联系人, 商品数量 )
( 联系人, 商品ID ) -> ( 供应商, 商品数量 )
该关系即为:
( 供应商 ) -> ( 供应商联系人 )
( 供应商联系人 ) -> ( 供应商 )
也就是说,关键字之间存在函数依赖关系.
那么如何更改优化呢?
| 供应商 | 商品ID | 商品数量 |
|---|---|---|
| 饮料一厂 | 1 | 10 |
| 饮料二厂 | 2 | 20 |
| 饮料一厂 | 1 | 20 |
| 供应商 | 供应商联系人 |
|---|---|
| 饮料一厂 | 张三 |
| 饮料一厂 | 李四 |
| 饮料二厂 | 王五 |
物理设计[ 以MySQL为例 ]
存储引擎的选择: InnoDB.(略)
命名
对于数据库的表和字段的命名应遵循下述原则:
可读性原则
表意性原则
- name NOT col_1
长名原则
- 例如: goods_id NOT id, student_name NOT name
类型选择
关于字段类型的选择,总的原则是对于相同级别的数据类型, 应该优先选择占用空间小的数据类型.
优先考虑数字类型, 其次是日期或二进制类型, 最后是字符类型
而对于选择这样的原则, 主要从下面两个角度考虑:
在对数据进行比较(查询条件, JOIN, 排序)操作时: 同样的数据, 字符常常处理的比数字慢.
数据库中, 数据处理以页为单位, 列的长度越小, 利于性能提升.
具体说来,有这些小Tips:
1.如果列中的数据长度长不多是一致的, 应该考虑使用
char, 否则考虑使用varchar.
2.如果列中的最大数据长度小于50Byte,则一般考虑使用char.( 当然, 如果这个列很常用, 则基于节省空间和减少I/O的考虑, 还是可以选择用varchar)
3.一般不宜定义大于50Byte的char类型
反范式化表设计
简单地说, 反范式化表设计是为了性能和读取效率的考虑(毕竟表的连接还是很消耗性能的), 因此, 允许存在少量的数据冗余或适当的对第三范式的要求进行违反.
也就是: 反范式化是使用空间换时间.
譬如之前的表, 在为了快速读取的业务场景下, 我们仍然可以考虑设计出这样的表.
| 订单ID | 商品ID | 商品数量 | 商品价格 | 商品名称 | 过期时间 |
|---|---|---|---|---|---|
在上面所说的逻辑设计部分, 这样的表是不合规范的, 因为我们完全可以通过商品ID来推出商品的名称和过期时间.
但是在实际的业务场景中, 我们经常需要获取名称和时间这样的信息.(热数据)
而且, SQL语句也会得到大量的缩减.
因此考虑到这个, 我们设计出这样的表.