如何进行数据库设计

淩亂°似流年 2022-05-11 05:54 209阅读 0赞

关系数据理论（包含什么是数据依赖、函数依赖）

https://www.cnblogs.com/JCSU/articles/1324032.html

1.  **需求分析**

数据库需求的作用点：

1.  数据是什么
2.  数据有哪些属性
3.  数据和属性各自的特点有哪些

## **为什么要进行数据分析** ##

1.  了解系统中所要存储的数据
2.  了解数据存储的特点。如，一些时效性的数据，可以定义规则过期自动清理
3.  了解数据的生命周期。

有的数据增长量很大，但它们不是核心数据，对这些数据可采用分库分表存储。如，开发人员希望把日志类数据存储在数据库中，从数据库管理员的角度来看，这种数据是不适合存储在数据库中的。因为日志这种数据增长量极大，并且不属于核心数据。如果非要存储在数据库中的话，可在数据库存储之前定义好这类数据的归档或清理规则。随着数据库的上限，就会进行数据库的归档。如果当这个表增长量很大的时候，再进行归档，工作量会很大还会影响线上的使用。

## **搞清楚一些问题** ##

1.  实体与实体之间的关系（如1对1，1对多，多对多）
2.  实体所包含的属性有哪些
3.  哪些属性或属性的组合可以唯一标识一个实体

# **逻辑设计** #

使用ER图进行数据库逻辑建模

## **逻辑设计是做什么的** ##

1、将需求转为数据库的逻辑模型

2、通过ER图的形式对逻辑模型进行展示

![70][]

![70 1][]

3、同所选用的具体的DBMS系统无关

## **设计范式概要** ##

![70 2][]

**操作异常**

![70 3][]

**数据冗余**

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

### **各个范式的特点** ###

![70 4][]

**第一范式(1NF)**

数据库表中的所有字段都是单一属性，不可再分的，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。这个单一属性是由基本的数据类型所构成的，如整数、浮点数、字符串等；总而言之第一范式要求数据库中的表都是二维表（二维表：由行和列所组成的表）。’

![70 5][]

下图是一张不符合1NF的表：

![70 6][]

**第二范式(2NF)**

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。例如，员工信息表中加上了员工编号（emp\_id）列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码。

若关系模式R∈[1NF][]（即R符合[第一范式][Link 1]），并且每一个非主属性都完全依赖于R的[主码][Link 2]，则R∈2NF（即R符合第二范式）。

所谓完全依赖是指不能存在仅依赖[主关键字][Link 3]一部分的属性，理解了完全依赖和部分依赖，就很容易理解第二范式，下面举例说明一下。

1、不符合2NF的例子

![70 7][]

该表中主键为（货物类型、货物id），货物名称字段完全依赖于这个主键，换句话说货物的名称是完全取决于这个主键的值。但“注意事项”这一列，仅依赖于“货物类型”这一个属性。简单的说，2NF要求每个非主属性完全依赖于主键，而不是仅依赖于其中一部分属性。那么，既然表中存在一个对主键不是完全依赖的属性，就可以确定此表是不符合2NF的。

2、符合2NF的例子

![70 8][]

该表中的主键依然是（货物类型，货物id），非主键字段（即其它属性）“货物名称”完全依赖于此主键，那么这张表就是符合2NF的。

**第二范式的优缺点**

![70 9][]

**一个关系模式的分级例子**

![70 10][]

**第三范式**

第三范式(Third Normal Form,3rd NF)就是指表中的所有数据元素不但要能惟一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。也就是说，对于一个满足2nd NF 的数据结构来说，表中有可能存在某些数据元素依赖于其他非关键字数据元素的现象,必须消除。

采用投影分解法将一个2NF的关系分解为多个3NF的关系，可以在一定程度上解决原2NF关系中存在的插入异常、删除异常、数据冗余度大、修改复杂等问题。

将一个2NF关系分解为多个3NF的关系后，并不能完全消除[关系模式][Link 4]中的各种异常情况和数据冗余。

例：如S1（SNO，SNAME，DNO，DNAME，LOCATION） 各属性分别代表学号，姓名，所在系，系名称，系地址。

关键字SNO决定各个属性。由于是单个关键字，没有部分依赖的问题，肯定是2NF。但这关系肯定有大量的冗余，有关学生所在的几个属性DNO，DNAME，LOCATION将重复存储，插入，删除和修改时也将产生类似以上例的情况。原因：**关系中存在传递依赖造成的。即SNO -> DNO。 而DNO -> SNO却不存在，DNO -> LOCATION, 因此关键字 SNO 对 LOCATION 函数决定是通过传递依赖 DNO -> LOCATION 实现的。也就是说，SNO不直接决定非主属性LOCATION。**

解决目地：每个[关系模式][Link 4]中不能留有传递依赖。

解决方法：分为两个关系 S（SNO，SNAME，DNO），D（DNO，DNAME，LOCATION）

注意：关系S中不能没有[外关键字][Link 5]DNO。否则两个关系之间失去联系。

将[第一范式][Link 1]，[第二范式][Link 6]化为第三范式的步骤：

1.  1.  求出R的[最小函数依赖集][Link 7]Fmin
    2.  找出不在Fmin中出现的属性，并将这些属性从R中去掉，构成一个关系模式
    3.  若Fmin中有一个函数依赖涉及R的全部属性，则R不能分解
    4.  否则，若Fmin中有X->A,则分解应包含\{XA\};若有X->A1,X->A2....X->An均属于Fmin，则分解应包含\{XA1A2...An\}

**BC范式**

BC范式一定满足第三范式，简单来说所有的非主码属性都需要消除传递函数依赖，包括候选码，则称为BC范式。

例子1：

比如我们有一个学生导师表，其中包含字段：学生ID，专业，导师，专业GPA，这其中学生ID和专业是联合主键。

![70 11][]

这个表的设计满足三范式，有主键，不存在主键的部分依赖，不存在非主键的传递依赖。但是这里存在另一个依赖关系，“专业”函数依赖于“导师”，也就是说每个导师只做一个专业方面的导师，只要知道了是哪个导师，我们自然就知道是哪个专业的了。

所以这个表的部分主键依赖于非主键部分，那么我们可以进行以下的调整，拆分成2个表：

![70 12][]

例子2：

![70 13][]

**物理设计（下面是以mysql为例）**

根据数据库自身的特点把逻辑设计转换为物理设计（涉及到选用哪种数据库）

## **数据库设计需要做什么** ##

1.  选择合适的数据库管理系统
2.  定义数据库、表及字段的命名规范
3.  根据所选的dbms系统选择合适的字段类型
4.  反范式化设计（如，增加一些冗余，来达到效率的提升）

## **选用数据库管理系统** ##

使用什么数据库管理系统，要考虑用途、开发语言、安全性，，，，等等因素

![70 14][]

**MySQL的存储引擎**

![70 15][]

**表及字段的命名规则**

1、可读性原则：

使用大写和小写来格式化的库对象的名字以获得良好的可读性。（如，使用CustAddress而不是custaddress来提高可读性；这里要注意有的DBMS是对大小写敏感的）

2、表意性原则

对象的名字应该能够描述它所标识的对象。例如，对于表，表的名字应该能够体现表中存储的数据内容；对于存储过程，存储过程应该能够体现存储过程的功能。

3、长名原则

尽可能少使用或不适用缩写，这样会降低可读性及它实际表达的功能

### **字段类型选择原则** ###

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

以上选择原则又从两个方面进行考虑：

1.  在对数据进行比较（查询条件、join条件及排序）操作时：
2.  在数据库中，数据处理以页为单位，列的长度越小，越利用提升性能（Mysql的InnoDB存储页的大小为16k）；一个列的长度越小，在一个页中存储的数据量越大，这样在加载相同数据量时，加载的页就会越少，io的性能就会提升。

如，一个birthday字段有以下数据类型可选择：

![70 16][]

MySql中数据库类型所占空间介绍：

![70 17][]

**数据如何具体选择数据类型**

**Char****和varchar如何选择：**

1.  如果类中要存储的数据长度差不多是一致的，则应该考虑char，否则考虑varchar
2.  如果列中的最大长度小于50Byte，一般也考虑用char。如果这个列很少使用，则基于节省空间和io的考虑，还是可以选择varchar
3.  一般不宜定义大于50Byte的char类型

**Decimal****和float类型如何选择：**

1.  Decimal用于存储精确数据，而float只能用于存储非精确数据
2.  由于float的存储空间开销一般比decimal小（精确到7位小数只需要4个字节，而精确到15位小数只需要8字节），所以非精确数据优先选择flaot

**时间类型如何选择**

1、使用int类型来存储时间字段的优缺点：

优点：字段长度比datetime小

缺点：使用不方便，要进行函数转换

限制：

![70 18][]

2、需要存储的时间粒度

年 月 日 小时 分 秒 周

## **如何选择主键** ##

![70 19][]

## **避免使用外键约束** ##

![70 20][]

## **避免使用触发器** ##

![70 21][]

## **关于预留字段** ##

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

## **反范式化** ##

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

**一个表的设计往往读操作和写操作的成本比例如果是3:1那还是比较划算的。**

**反范式化的有点：**

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

# **维护优化** #

1.  新的需求进行建表
2.  索引优化
3.  大表拆分

## **维护和优化需要做什么** ##

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

## **如何维护数据库字典** ##

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

## **如何维护索引（只是简单介绍）** ##

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

索引注意事项：

![70 22][]

**如何维护表结构**

1.  使用在线变更表结构的工具。Mysql5.5之间可以使用pt-online-schema-change（此工具的原理是建立一张临时表然后把原表的数据拷贝进去，之后对此临时表进行从命名）Mysql5.6之后本身支持在线表结构的变更
2.  同时对数据字典进行维护
3.  控制表的宽度和大小

## **数据库中适合的查询** ##

1.  批量操作VS逐条操作
2.  禁止使用Select \* 这样的查询
3.  控制使用用户自定义函数
4.  不要使用数据库中的全文索引（因为全文索引是需要另外建立索引文件来对全文索引进行管理，另外全文索引对中文支持不是很好）

## **表的垂直拆分** ##

当需求增加，表的列越来越多

![70 23][]

## **表的水平拆分** ##

![70 24][]

### **如何进行表的水平拆分** ###

对每条数据的主键进行hash操作；比如说对主键进行按模取值，将一张大表的数据平均的分到了多张小表中

![70 25][]

# **例子** #

一个电商系统为例它里面所包含了模块，用户模块、商品模块、订单模块、购物车模块、供应商模块（只是为了展示，真正系统中肯定不止这些模块）。

## **需求分析** ##

### **用户注册模块（记录用户注册信息）** ###

1.  包括属性：用户名、密码、电话号码、邮箱、身份证号、地址、姓名、昵称、收货地址…..
2.  可选唯一标识符：用户名、身份证、电话号码
3.  存储特点：随着系统上线时间组件增加，需要永久存储

### **商品模块（记录网站中所销售的商品的信息）** ###

1.  包括属性：商品编码、商品名称、商品描述、商品分类、供应商名称、重量、有效期、价格、…….
2.  可选唯一性属性：（商品名称、供应商名称），商品编码
3.  存储特点：对于下线商品可以归档存储

### **订单模块（记录用户在网站中订购商品的信息）** ###

1.  包括属性：订单号、用户姓名、用户电话、收货地址、商品标号、商品名称、价格、数量、订单状态、支付状态、订单类型…….
2.  可选唯一性属性：订单号
3.  存储特点：永久存储（分库、分表存储）

### **购物车模块（用于保存用户购物时选对的商品）** ###

1.  包括属性：用户名、商品编号、商品名称、商品价格、商品描述、商品分类、加入时间、商品数量…….
2.  可选唯一性属性：（用户名、商品编号、加入时间），（购物车编号）
3.  存储特点：不用就存储（设置归档、清理规则）

### **供应商模块（保存所销售商品的供应信息）** ###

1.  包括属性：供应商编号、供应商名称、联系人、电话、营业执照号、地址、法人….
2.  可选唯一性属性：供应商编号、营业执照号
3.  存储特点：永久存储

### **这几个模块实体与实体之间的关系** ###

![70 26][]

## **逻辑设计** ##

ER图（只列出了每个实体中的关键属性；途中加了下滑线的属性表示主键）：

![70 27][]

## **逻辑设计到物理设计阶段需要考虑** ##

把哪些实体信息应该存储到用一张表中，哪些该单独存。如下图所示，用户信息和购物车信息是该一起存在一张表中还是单独存在一张表中：

![70 28][]

[70]: /images/20220511/fb3b731a46ad4f0d8f57c856d68c9465.png
[70 1]: /images/20220511/f619dba854db4f1c88000322f46aa3d7.png
[70 2]: /images/20220511/a94fb1b282a24ad7a110b325dae0ed4a.png
[70 3]: /images/20220511/4cad17ab17d14c28bc60db66ef085ec3.png
[70 4]: /images/20220511/1dad277a376f4a3cadec2fd828c21e75.png
[70 5]: /images/20220511/a1e8470666f24666a65a92fff2bbf4db.png
[70 6]: /images/20220511/4894d22c679f4829b32a10ba45edf72c.png
[1NF]: https://baike.baidu.com/item/1NF
[Link 1]: https://baike.baidu.com/item/%E7%AC%AC%E4%B8%80%E8%8C%83%E5%BC%8F
[Link 2]: https://baike.baidu.com/item/%E4%B8%BB%E7%A0%81
[Link 3]: https://baike.baidu.com/item/%E4%B8%BB%E5%85%B3%E9%94%AE%E5%AD%97
[70 7]: /images/20220511/725c2dc22288490486c2b2f9402fc4c8.png
[70 8]: /images/20220511/f9fae18e11a84062aaad57fa477954b7.png
[70 9]: /images/20220511/f4bd945ac8664862b3adcafe86fd090b.png
[70 10]: /images/20220511/4d1b2c17cdad444eb17bafe5aaa147a0.png
[Link 4]: https://baike.baidu.com/item/%E5%85%B3%E7%B3%BB%E6%A8%A1%E5%BC%8F
[Link 5]: https://baike.baidu.com/item/%E5%A4%96%E5%85%B3%E9%94%AE%E5%AD%97
[Link 6]: https://baike.baidu.com/item/%E7%AC%AC%E4%BA%8C%E8%8C%83%E5%BC%8F
[Link 7]: https://baike.baidu.com/item/%E6%9C%80%E5%B0%8F%E5%87%BD%E6%95%B0%E4%BE%9D%E8%B5%96%E9%9B%86
[70 11]: /images/20220511/822aed5681e24f5ea68c5444ab363618.png
[70 12]: /images/20220511/c59b235ff81e4da39993d6d5894b6ea0.png
[70 13]: /images/20220511/e8774cad8e4049bc99ea5207ea2fe9d2.png
[70 14]: /images/20220511/e5c81aef55054370961b2c12e69a1698.png
[70 15]: /images/20220511/a3f4fbd5f1ec4324967b107eb0dd2a48.png
[70 16]: /images/20220511/7f804428cf1a4df4b3af2dcb48bcdf68.png
[70 17]: /images/20220511/a23811a210f949cf81b88c2c1898f1a0.png
[70 18]: /images/20220511/9fe27b84eef6434b92df52464d1819fb.png
[70 19]: /images/20220511/02b164f0ca3b45b8a80579f56959dbef.png
[70 20]: /images/20220511/e87c677cf6c347beae392b78536091b7.png
[70 21]: /images/20220511/b2c8f4176ce0429f9d9724ccc49fd6da.png
[70 22]: /images/20220511/2e8f7a4c75ba4eb7b57d22439cc521c0.png
[70 23]: /images/20220511/514149a46d224cc5a516e091f55e55c8.png
[70 24]: /images/20220511/93b5fdc198ab40269477586140c896c5.png
[70 25]: /images/20220511/73dab3e89e3b40c0a0d1650ada5bb68a.png
[70 26]: /images/20220511/b9587281b9ab4d709f8cb38967ce1050.png
[70 27]: /images/20220511/0b8b35e143c242dfac259ebff6bad25c.png
[70 28]: /images/20220511/d57749999869470995e469d652305012.png