数据库设计概述

什么是数据库设计？

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

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

只有我们设计了才能出现优良的数据库设计才能提高我们系统的性能，提供更好的服务。否则就是糟糕的数据库设计会出现很多问题，影响我们的工作效率，服务的效率和用户的使用效率。

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

数据库设计的步骤有哪些？

需求分析：需要哪些数据，这些数据有哪些属性，数据和属性的各自特点有哪些 。

逻辑设计：使用ER图对数据库进行逻辑建模。

物理设计：根据数据库的自身特点将逻辑设计转化为物理设计。

维护优化：新需求新表新属性的添加，索引的优化，大表的拆分。

一、需求分析

为什么要进行需求分析？

1. 了解系统中所要存储的数据
2. 了解数据的存储特点
3. 了解数据的生命周期
4. 搞清楚实体与实体之间的关系
5. 搞清楚实体所包含的属性有哪些
6. 搞清楚哪些属性或属性的组合可以唯一标识一个实体

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

                                                                                    各个模块之间的关系

• 用户模块

包括属性：用户名、密码、电话、邮箱、身份证号、地址、姓名、昵称等。

可选唯一标识属性：用户名、电话、身份证号

存储特点：随系统上线时间逐渐增加，需要永久存储。

• 商品模块

包括属性：商品编码、商品名称、商品描述、商品品类、供应商名称、重量、有效期、价格等。

可选唯一标识属性：（商品编码）、（商品名称和供应商名称）

存储特点：对于下线商品可以归档存储

• 订单模块

包括属性：订单号、用户姓名、用户电话、收货地址、商品编号、商品名称、数量、价额、订单状态、支付状态、din

可选唯一标识属性：订单号

存储特点：永久存储、分库分表存储

• 购物车模块

包括属性：购物车编号、用户名、商品编号、商品名称、商品价格、商品描述、商品分类、加入购物车时间、商品数据等。

可选唯一标识属性：（用户名、加入时间、商品编号）、（购物车编号）

存储特点：不永久存储、设置归档清理、规则

• 供应商模块

包括属性：供应商编号、供应商名称、联系人、电话、营业执照号、地址、法人等。

可选唯一标识属性：供应商编号、营业执照编号

存储特点：永久存储

二、逻辑设计

逻辑设计是要做什么？

1. 将需求转化为数据库的逻辑模型
2. 通过ER图的形式对逻辑模型进行展示
3. 与所选用的具体的数据库模型无关

名词解释

关系：一个关系对应通常所说的一张表

元组：表中一行即为一个元组

属性：表中的一列即为一个属性，每一个属性都有一个名称，称为属性名

候选码：表中的某个属性组，它可以唯一确定一个元组

主码：一个关系有多个候选码，选定其中一个为主码

域：属性的取值范围

分量：元组中的一个属性值

                                                              ER图例说明

                                                               实例演示

数据操作异常及冗余是什么？

操作异常

• 插入异常：某个实体随着另一个实体的存在而存在，若缺少这个实体时另一个实体无法表示。
• 更新异常：更改表所对应的某个实体实例的单独属性时，需要将多行更新。
• 删除异常：若删除表的某一个实体实例的属性时导致另一个实体实例的属性丢失。

数据冗余

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

什么是数据库设计范式？

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

1. 第一范式要求数据库中表都是二维表。所有字段都是单一属性，不可再分的，这个单一属性是由基本的数据类型所组成的。
2. 第二范式要求要求实体的属性完全依赖于主关键字，所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。
3. 第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。主要是为了减少数据的冗余。

4. 

5. BCNF范式要是如果是符合关键字，则复合关键字之间也不能不能存在函数依赖关系

三、物理设计

物理设计要做什么？

1. 选择合适的数据库管理系统。
2. 定义数据库、表及字段的命名规范。
3. 根据所选的DBMS系统选择合适的。
4. 反范式化设计。

表及字段的命名规则：

• 可读行原则：使用小写和下划线来格式化对象名字以获得良好的可读性。
• 表意性原则：对象的名字应该能够描述它所标识的对象的。
• 长名原则：尽可能少使用或者不使用缩写，适用于数据库名之外的任一对象。

字段类型的选择原则：

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

1. 在对数据进行比较(查询条件、JOIN条件及排序)操作时，同样的数据，数字往往比字符处理快。
2. 在数据库中，数据处理以页为单位，列的长度越小，越利于性能提升。

char 和 varchar如何选择？

• 如果列中要存储的数据长度差不多是一致的，应该考虑用char,否则应该考虑用varchar。
• 如果列中最大数据长度小于50Byte,则也考虑用char。
• 一般不定义数据长度大于50Byte的char类型列。

decimal和float如何选择？

• decimal用于存储精确数据，而float只能用于存储非精确的数据，所以精确数据只能用decmal存储。
• 由于 flaot的存储空间开销一般比decimal小，所以非精确数据优先选择float。

时间类型如何存储？

根据需要存储时间的粒度。int  datetime timestamp

int和timestamp比datetime占用的长度小，但是int和timestamp存储的时间有限制，只能存储到2038-1-19 11：14:07，int使用不方便要使用函数转换。

如何选择主键？

• 区分业务主键和数据库主键
• 根据数据库类型，考虑主键是否要顺序增长
• 主键的数据类型所占空间要尽可能的小

为甚么要避免使用外检约束？

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

为什么避免使用触发器？

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

为什么严禁使用预留字段？

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

什么是反范式化？

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

为什么反范式化？

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

                                                                        符合范式化要求的设计

                                                                                              反范式化要求的设计

                                                                                         查询订单详情

                                                                              查询订单信息

四、维护优化

维护和优化中要做什么？

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

如何维护数据字典？

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

如何维护索引？

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

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

• 注意事项

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

如何维护表结构？

1. 使用在线变更表结构的工具
2. 同时对数据字典进行维护
3. 控制表的宽度和大小

数据库中适合的操作

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

• 表的垂直拆分

 

• 表的水平拆分