【学习笔记】《数据库设计那些事 - 慕课课程》
link: https://www.imooc.com/video/1903
这门课程是陈皓老师（左耳朵耗子）在他的极客时间专栏《左耳听风》专栏中推荐的，推荐详情如下：
你需要系统地了解一下数据库设计中的那些东西，这里推荐慕课网的一个在线课程：数据库设计的那些事。每个小课程不过 5-6 分钟，全部不到 2 个小时，我相信你一定能跟下来。你需要搞清楚数据的那几个范式，还有 SQL 语句的一些用法。
这个课程比较简洁干练，适合入门和作为复习提纲。我用了大概四小时学完了该课程，并跟着敲完了课堂笔记。
1-1 数据库设计简介
什么是数据库设计？
数据库设计就是根据业务系统的具体需要，结合我们所选用的DBMS(数据库管理系统)，为这个业务系统构造出最优的数据存储模型，并建立好数据库中的表结构及表与表之间的关联结构的过程。使之能有效的对应应用系统中的数据进行存储，并可以高效的对已经存储的数据进行访问（目的）。
为什么要进行数据库设计？
优良的设计：
- 减少数据冗余
- 避免数据维护异常
- 节约存储空间
- 高效的访问
糟糕的设计：
- 存在大量数据冗余
- 存在数据插入，更新，删除异常
- 浪费大量存储空间
- 访问数据低效。
1-2 数据库设计的步骤
需求分析 -> 逻辑设计 -> 物理设计 -> 维护优化
需求分析
数据库需求的作用点：
- 数据是什么？
- 数据库有哪些属性？
- 数据和属性各自的特点有哪些？
逻辑设计
使用ER图对数据库进行逻辑建模
物理设计
根据不同数据库自身的特点把逻辑设计转换为物理设计
维护优化
- 对新的需求进行建表
- 索引优化
- 大表拆分
1-3 需求分析重要性简介
为什么要进行需求分析？
- 了解系统中所要存储的数据
- 了解数据的存储特点
- 了解数据的生命周期
要搞清楚的一些问题
- 实体及实体之间的关系（1对1，1对多，多对多）
- 实体所包含的属性有什么？
- 哪些属性或属性的组合可以唯一标识一个实体？
1-4 需求分析实例演示
实例演示
以一个小型的电子商务网站为例，在这个电子商务网站的系统中包括了几个核心模块：用户模块，商品模块，订单模块，购物车模块，供应商模块。
用户模块
用于记录注册用户信息
包括属性：用户名、密码、电话、邮箱、身份证号、地址、姓名、昵称。。。。
可选唯一标识属性：用户名、身份证、电话、
存储特点： 随系统上线时间逐渐增加，需要永久存储
商品模块
用于记录网站中所销售的商品信息
包括属性：商品编码、商品名称、商品描述、商品品类、供应商名称、重量、有效期、价格。。。。
可选唯一标识：（商品名称、供应商名称)、（商品编码）
存储特点：对于下线商品可以归档存储
订单模块
用于用户订购商品的信息
包括属性：订单号、用户姓名、用户电话、收货地址、商品编号、商品名称、数量、 价格、 订单状态、支付状态、 订单类型。。。
可选唯一表示属性：（订单号）
存储特点：永久存储（分表、分库存储）
购物车模块
用于保存用户购物时选择的商品
包括属性：用户名、 商品编号、商品名称、商品价格、商品描述、商品分类、加入时间、商品数量
可选唯一标识：（用户名、商品编号、加入时间）、（购物车编号）
存储特点：不用永久存储（设置归档、清理规则）
供应商模块
用于保存所销售商品的供应商信息
包括属性：供应商编号、 供应商名称 、 联系人、 电话、 营业执照号、 地址、 法人、
可选唯一标识：供应商编号、营业执照号
存储特点：永久存储
电子商务网站
用户 <---->订单：一对多
用户 <---->购物车：一对多
订单 <----> 商品 ： 多对多
购物车 <----> 商品 ：多对多
商品 <----> 供应商 ： 多对多
2-1 ER图
逻辑设计时做什么的？
- 将需求转化为数据库的逻辑模型
- 通过ER图的形式对逻辑模型进行展示
- 与所选用的具体的DBMS系统无关
名词解释
关系：一个关系对应通常所说的一张表
元组：表中的一行即为一个元组（一个实体）
属性： 表中的一列即为一个属性；每一个属性都有一个名称，称为属性名。
候选码：表中的某个属性组，它可以唯一确定一个元组。
主码：一个关系有多个候选码，选定其中一个为主码。（主键）
域：属性的取值范围。
分量：元组中的一个属性值。
ER图例说明
矩形：表示实体集，矩形内写实体集的名字
菱形：表示联系集
椭圆：表示实体的属性
线段：将属性连接到实体集，或将实体集连接到联系集
2-2 设计范式概要
什么是数据库设计范式？
常见的数据库设计范式包括：
第一范式，第二范式，第三范式、及BC范式
当然还有第四范式和第五范式，不过因为目前大多数数据库设计所遵循的不包括上述两种范式，所以重点放在第一范式，第二范式，第三范式上。
数据操作异常及数据冗余
操作异常：
数据冗余：
是指相同数据再多个地方存在，或者说表中的某个列可以由其他列计算得到，这样就说表中存在着数据冗余。
2-3 第一范式（1NF)
定义：
数据库表中的所有字段都是单一属性，不可再分。
这个单一属性是由基本的数据类型所构成的，如整数、浮点数、字符串等。简言之，第一范式要求数据库中的表都是二维表。
2-4 第二范式（2NF)
定义：
数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖。
部分函数依赖是指存在着组合关键字中的某一关键字决定非关键字的情况。
简言之，所有单关键字段的表都符合第二范式。
案例：
由于供应商和商品之间时多对多的关系，所以只有使用商品名称和供应商名称才可以唯一标识出一件商品，即商品名称和供应商名称是一组组合关键字。 解决方式：拆分表。
2-5 第三范式
定义：
第三范式是在第二范式的基础之上定义的。如果数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。
案例：
存在以下传递函数依赖关系：
商品分类 --->分类 ---> 分类描述
即存在非关键字段“分类描述”对关键字段“商品名称”的传递函数依赖、
存在问题
（分类，分类描述）对于每一个商品都会进行记录，所以存在着数据冗余。同时也存在着数据的插入，更新及删除异常。 解决方式：拆分表。
2-6 BC范式
定义：
在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式。即，如果是复合关键字，则复合关键字之间也不能存在函数依赖关系。
案例：
假定：供应商联系人只能受雇于一家供应商，每家供应商可以供应多个商品，则存在以下决定关系：
（供应商，商品ID) ---> （联系人，商品数量）
（联系人，商品ID) ----> (供应商，商品数量)
存在以下关系不符合BCNF要求：
（供应商） -> （供应商联系人）
（供应商联系人）-> （供应商）
并且存在数据操作异常和数据冗余解决方式：拆分表
3-1数据库物理设计要做什么？
- 选择合适的数据库管理系统
- 定义数据库、表以及字段的命名规范
- 根据所选的DBMS系统选择合适的字段类型
- 反范式化设计（以空间换时间）
3-2 选择哪种数据库？
常见的DBMS系统：
Oracle, SQLServer (商业数据库) -》大的事务性操作
MySQL , PgSQL(开源数据库)
3-3 MySQL 常用的存储引擎
MyISAM : 不支持事务，主要应用于SELECT,INSERT
MRG_MYISAM: 不支持事务，主要应用于分段归档，数据仓库
Innodb: 支持事务，支持MVCC的行级锁（阻塞更少），主要应用于事务处理
Archive: 不支持事务，行级锁，主要应用于日志记录，只支持INSERT,SELECT
3-4 数据库表及字段的命名规则
所有对象命名应该遵循以下原则：
- 可读性原则
使用大写和小写来格式化数据库对象名以获得更好的可读性。
例如，使用CustAddress而不是custaddress(有些DBMS系统对表名的大小是敏感的)
- 表意性原则
对象的名字应该能够描述它所标识的对象。例如，对于表，表的名称能够体现表中存储的数据内容。对于存储过程， 存储过程名称应该能够体现存储过程的功能。
- 长名原则
尽可能少使用或者不使用缩写，适用于数据库（DATABASE)名之外的任一对象。
3-5 数据库字段类型选择原则
字段类型的选择原则
列的数据类型一方面影响数据存储空间，另一方面也会影响数据查询性能。当一列可以选择多种数据类型时，应该优先考虑数字类型
，其次是日期或者二进制类型，最后是字符类型。对于相同级别的数，应该优先选择占用空间小的数据类型。INT - 4 字节、DATE - 3字节、DATETIME - 8字节、TIMESTAMP - 4字节
为什么是这样的选择原则？
主要是从两个角度考虑：
- 在对数据进行比较时（查询条件，JOIN条件及排序）操作时：
同样的数据，字符处理往往比数字处理慢。
- 在数据库中，数据处理以页为单位，列的长度越长，利于性能提升。
3-6 数据库如何具体选择字段类型
char 与 varchar 如何选择？
原则：
- 如果列中要存储的数据长度差不多是一致的，则应该考虑用char,否则应该考虑用varchar。
- 如果列中的最大数据长度小于50Byte,则一般也考虑用char。
- 一般不宜定义大于50Byte的char类型列。
decimal 和float 如何选择？
原则：
- decimal 用于存储精确数据，而float只能用于存储非精确数据。所以精确数据只能选择decimal类型。
- 由于float的存储空间开销一般比decimal小（精确到7位小数只需要4个字节，而精确到15位小数只需要8字节）所以非精确数据优先选择float类型。
时间类型如何存储？
- 使用int来存储时间字段的优缺点
- 优点：字段长度比DATETIME小
- 缺点：使用不方便，需要进行函数转换
- 限制：只能存储2038-1-19 11:14:07 即2的32次方（2147483648）
- 如果经常被使用到，选择DATETIME类型来存储。如订单的下单时间或支付时间等
- 如果主要用来存储，而很少被查询到，选择INT类型来存储。如生日等
- 需要存储的时间粒度
年 月 日 小时 分 秒 周
3-7 数据库设计其他注意事项
如何选择主键？
1. 区分业务主键和数据库主键
业务主键用于标识业务数据，进行表与表之间的关联；数据库主键为了优化数据存储（Innodb会生成6个字节的隐含主键）
一篇博客有介绍："使用逻辑主键的主要原因是，业务主键一旦改变则系统中关联该主键的部分的修改将会是不可避免的，并且引用越多改动越大。而使用逻辑主键则只需要修改相应的业务主键相关的业务逻辑即可，减少了因为业务主键相关改变对系统的影响范围。业务逻辑的改变是不可避免的，因为“永远不变的是变化”，没有任何一个公司是一成不变的，没有任何一个业务是永远不变的。最典型的例子就是身份证升位和驾驶执照号换用身份证号的业务变更"
2. 根据数据库的类型，考虑主键是否要顺序增长
有些数据库是按主键的逻辑顺序存储的
3. 主键字段类型所占空间要尽可能的小
对于使用聚集索引方式储存的表，每个索引后都会附加主键信息。
避免使用外键约束
- 降低数据导入效率
- 增加维护成本
- 虽然不建议使用外键约束，但是相关联的列上一定要建立索引
避免使用触发器
触发器常见于在日志中插入数据
- 降低数据导入效率
- 可能会出现意想不到的数据异常
- 使得业务逻辑变的复杂
关于预留字段
- 无法准确的知道预留字段的类型
- 无法准确的知道预留字段中所存储的内容
- 后期维护预留字段的成本与增加一个字段的成本是相同的
- 严禁使用预留字段
3-8 反范式化表设计
什么是反范式化？
反范式化是针对范式化而言的，即为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余。换言之，以空间换时间。
案例：
符合范式化的设计
用户表：用户ID、姓名、电话、地址、邮编
订单表：订单ID、用户ID、下单时间、支付类型、订单状态
订单商品表：订单ID、商品ID、商品数量、商品价格
商品表：商品ID、名称、描述、过期时间
反范式化的设计
用户表：用户ID、姓名、电话、地址、邮编
订单表：订单ID、用户ID、下单时间、支付类型、订单状态、订单价格、姓名、地址、电话
订单商品表：订单ID、商品ID、商品数量、商品价格、商品名称、过期时间
商品表：商品ID、名称、描述、过期时间
查询订单详情或订单信息效率会更高。
为什么要反范式化？（反范式化的好处）
- 减少表的关联数量
- 增加数据的读取效率
- 反范式化一定要适度
4-1 数据库维护和优化要做什么？
维护和优化中要做什么？
- 维护数据字典
- 维护索引
- 维护表结构
- 在适当的时候对表进行水平拆分或垂直拆分
4-2 如何维护数据字典？
- 使用第三方工具对数据字典进行维护
- 利用数据库本身的备注字段来维护数据字典。以MySQL为例
- CREATE TABLE customer(
- cust_id INT AUTO_INCREMENT NOT NULL COMMENT '自增ID',cust_name VARCHAR(10) NOT NULL COMMENT'客户姓名', PRIMARY KEY (cust_id)
- ) COMMENT '客户表'
- 导出数据字典
4-3 如何维护索引？
如何选择合适的列建立索引？
- 出现在WHERE从句、GROUP BY 从句、ORDER BY 从句 中的列
- 可选择性高的列要放在索引的前面
- 索引中不要包括太长的数据类型
注意事项
- 索引并不是越多越好，过多的索引不但会降低写效率而且会降低读的效率
- 定期维护索引碎片
- 在SQL语句总不要使用强制索引关键字
4-4 数据库中适合的操作
如何维护表结构？
注意事项：
- 使用在线变更表结构的工具
- MySQL5.5 之前可以使用pt-online-schema-change
- MySQL5.6 之后本身支持在线表结构的变更
- 同时对数据字典进行维护
- 控制表的宽度和大小
数据库中适合的操作
- 批量操作（√）vs 逐条操作
- 禁止使用Select * 这样的操作（因为会导致IO浪费，造成程序出错）
- 控制使用用户自定义函数（低效）
- 不要使用数据库中的全文索引（需要建立另外的索引文件，对中文不友好）
4-5 数据库表的垂直和水平拆分
表的垂直拆分
为了控制表的宽度可以进行表的垂直拆分
- 经常一起查询的列放到一起
- text，blob 等大字段拆分到附加表中
