浅谈数据库设计

 内容大多源于慕课网的一篇课程，算是做一个笔记并加上自己的一些理解与补充。原链接:https://www.imooc.com/video/1903

数据库设计

 1.什么是数据库设计？

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

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

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

 3.数据库设计一般步骤：需求分析–>逻辑设计–>物理设计–>维护优化，下面来展开说下每一个步骤都做了些什么

一、需求分析

 1.为什么要进行需求分析？

 需求分析主要来了解系统所要存储的数据；了解数据的存储特点、关系；了解数据的生命周期。对需求的理解与掌握是我们在进行数据库设计必须要进行的环节

 2.需求分析要搞清楚的问题？

1.实体与实体之间的关系(1对1,1对多,多对多)
2.实体所包含的属性
3.哪些属性或属性的组合可以唯一标识一个实体

1.1 需求分析示例

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

 各模块分析：

模块名称	包含属性	唯一标识属性	存储特点
用户模块	用户名、密码、电话、邮箱、手机号、身份证号、姓名…	用户名、身份证、电话	随时间逐渐增加，永久存储
商品模块	商品编码、商品名称、商品描述、商品品类、供应商名称、重量、有效期、价格…	(商品编码)、(商品名称，供应商名称)	对于下线商品可以归档
订单模块	订单号、用户姓名、用户电话、收货地址、商品编码、商品名称、数量、价格、订单状态、支付状态、订单类型…	订单号	永久存储
购物车模块	用户名、商品编号、商品名称、商品价格、商品描述、加入时间、数量…	(购物车编号)、(用户名、商品编号、加入时间)	不用永久存储(设置归档、清理规则)
供应商模块	供应商编号、供应商名称、联系人、电话、营业执照号、地址…	(供应商编号)、(营业执照号)	永久存储

 各模块关系图：

二、逻辑设计

 逻辑设计是做什么的？

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

 ER图名词解释

关系：一张表
元组：表中的一行
属性：表中的一个列
候选码：表中的某个属性组，它可以唯一确定一个元组
域：属性的取值范围

 ER图例说明

 示例演示

2.1 数据操作异常及数据冗余

 操作异常

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

 数据冗余：是指相同的数据在多个地方存在，或者说表中的某个列可以由其它列计算得到，这样就说表中存在着数据冗余

2.2 逻辑设计规范

1. 第一范式：数据库表中的所有字段都是单一属，不可再分的，这个单一属性是由基本的数据类型所构成的。要求数据库中的表都是二维表，都是由行列组成。
   如下图所示，我们常规设计都是上面表的形式，下面表中用户信息出现了组合列的形式。
   

2. 第二范式：数据库的表不存在非关键字段对任一候选关键字段的部分函数依赖，部分函数依赖是指存在着组合关键字的某一关键字决定非关键字的情况。所有单关键字段的表都符合第二范式，不要存在组合关键字。
   
    如上图示例，由于供应商和商品之间是多对多的关系，所以只有使用商品名称和供应商名称是一组组合关键字。那么它存在以下的部分函数依赖关系：
     (商品名称)->(价格、描述、重量、商品有效期)
     (供应商名称)->(供应商电话)

    这样的设计会出现下面的异常：
     1) 插入异常:如果供应商没有提供商品，那么就意味着我们无法找到供应商信息
     2) 删除异常:如果上图中饮料一厂只提供了可乐这一种商品，那么删除掉可乐这种商品，意味着我们将无法找到饮料一厂的信息
     3) 更新异常:更新供应商信息会将其所有关联的商品信息一同更新
     4) 数据冗余：供应商没提供一种商品，供应商信息就要被多存一份

    遵循第二范式，我们可以对上面的结构进行如下改进：
   
    如上，我们将供应商信息、商品信息拆成两个表，并通过一张中间表存两个的关联关系，也就符合我们第二范式的要求，解决了上述中出现的问题。

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

   示例
   
   分析
   上面的结构存在以下传递函数依赖关系：
    (商品名称)–>(分类)–>(分类描述)
   就是说存在非关键字段“分类描述”对关键字段“商品名称”传递函数依赖

   存在问题
    1) 数据冗余：(分类、分类描述)对于每一个商品都会进行记录
    2) 插入、删除、更新异常：如果没有商品信息则将查不到分类的信息

   解决方式
   

4. BC范式：数据库表中如果不存在任何字段对任意候选关键字段的传递函数依赖则符合BC范式。就是说如果是复合关键字，则符合关键字之间也不能存在函数依赖关系。

三、物理设计

物理设计需要做的事

1. 选择合适的数据库管理系统
2. 定义数据库表以及字段的命名规范
3. 选择合适的字段类型
4. 反范式化设计：违反范式以空间换时间

常见的DBMS系统
 Oracle、SQLServer(微软)：商用数据库，适用于企业级项目
 MySQL、PgSQL：开源数据库，适用于互联网项目

MySQL常用的存储引擎

3.1 表及字段的命名规则

命名原则

1. 可读性原则：大小写、驼峰式
2. 表意性原则：表的名称应该体现出表中存储的数据内容
3. 长名性原则：尽量不要使用缩写

3.2 字段类型选择

字段类型的选择原则
优先级：数字类型>日期>char>varchar

1. 同样的数据，字符处理比数字更慢
2. 数据处理以页为单位，列的长度越小，利于性能提升
   

char与varchar如何选择

1. 如果列中要存储的数据长度差不多是一致的，考虑用char，否则考虑用varchar
2. 最大数据长度小于50Byte，考虑用char，但如果列很少用，则基于节省空间和减少I/O的考虑，还是可以选择用vatchar
3. 一般不已定义大于50Byte的char类型列

decimal与float

1. decimal用于存储精确数据，float只用于存储非精确
2. float的存储空间比decimal小(精确到7位消暑只需要4个字节，而精确到15位小数只需要8字节)

时间类型选择

1. 使用int来存储时间字段的优缺点
   优点：字段长度比datetime小
   缺点：使用不方便，需要进行函数转换
   限制：只能存储到2038-1-19 11:13:07即2^32
2. 需要存储的时间粒度需求判断：是存年月日时分秒，还是只需要存储年月日

3.3 表设计关键点

如何选择主键

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

避免使用外键约束

1. 降低数据导入的效率
2. 增加维护成本
3. 相关联的列上一定要建立索引

避免使用触发器

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

关于预留字段

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

反范式化设计
 为了性能考虑适当的对第三范式的要求进行违反，允许少量的数据冗余，用空间来换时间

反范式化示例
 符合反范式化的设计

 上述设计如果要查询订单信息，则需要关联三张表：订单表、用户表、订单商品表进行查询，如果我们进行一点反范式化的设计，就会使这个查询变得简单且高效

 如上图我们如果再想查询订单信息，那么只需要查询订单表就可以了，sql会变得简单、效率也肯定会比三表联查高。

为什么反范式化设计

1. 减少表的关联数量
2. 增加数据的读取效率
3. 要适度反范式化

四、维护优化

4.1维护数据字典

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

4.2 维护索引

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

1. 出现在Where从句，Group By ，Order by
2. 可选择性高的列要放到索引的前面
3. 索引中不要包括太长的数据类型

注意事项

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

4.3 维护表结构

1. 禁止使用Selet *这样的查询
2. 控制使用用户自定义函数：影响索引的使用
3. 不要使用数据库中的全文索引

4.4 水平拆分与垂直拆分

1. 垂直拆分：经常查询的列放到一起，text、blob等大字段拆分出到附加表中
2. 水平拆分：将一张表拆分成多张一样的小表中，通过hashkey来区分数据