【MySQL高级篇笔记-数据库的设计规范(中) 】

待我称王封你为后i 2023-10-12 13:01 14阅读 0赞

此笔记为[尚硅谷][Link 1][MySQL高级][MySQL]篇部分内容

**目录**

一、为什么要数据库设计

二、范式

1、范式简介

2、范式都包括哪些

3、键和相关属性的概念

4、第一范式(1st NF)

5、第二范式(2nd NF)

6、第三范式(3rd NF)

7、小结

三、反范式化

1、概述

2、 应用举例

3、反范式的新问题

4、反范式的适用场景

1. 增加冗余字段的建议

2. 历史快照、历史数据的需要

五、BCNF(巴斯范式)

六、第四范式（了解）

七、第五范式、域键范式（了解）

八、ER模型

1、ER模型包括哪些要素

2、关系的类型

九、数据表的设计原则

十、数据库对象编写建议

1、关于库

2、关于表、列

3、关于索引

4、SQL编写

--------------------

# 一、为什么要数据库设计 #

--------------------

# ![64bda7b0cca845aba40d7f58e261f06e.png][] #

![17a195bbc919421d8256a55cad71f4e6.png][]

# 二、范式 #

--------------------

## 1、范式简介 ##

> **在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。**可以理解为，一张数据表的设计结构需要满足的某种设计标准的 级别 。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

## 2、范式都包括哪些 ##

> 目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

![f04740a0972d4f20b255318b816fe2f9.png][]

## 3、键和相关属性的概念 ##

> 范式的定义会使用到主键和候选键，数据库中的键(Key)由一个或者多个属性组成。数据表中常用的几种键和属性的定义:
> 
>  *  `超键`︰能唯─标识元组的属性集叫做超键。
>  *  `候选键`︰如果超键不包括多余的属性，那么这个超键就是候选键。·主键:用户可以从候选键中选择一个作为主键。
>  *  `外键`∶如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性集就是数据表R1的外键。
>  *  `主属性`:包含在任一候选键中的属性称为主属性。
>  *  `非主属`性:与主属性相对，指的是不包含在任何一个候选键中的属性。
> 
> 通常，我们也将候选键称之为“`码`”，把主键也称为“`主码`”。因为键可能是由多个属性组成的，针对单个属性，我们还可以用主属性和非主属性来进行区分

## 4、第一范式(1st NF) ##

> 第一范式主要是确保数据表中每个字段的值必须具有**`原子性`**，也就是说数据表中每个字段的值为**`不可再次拆分`**的最小数据单元。

![47e7699422404156a4b11a9904d92aaf.png][]

## 5、第二范式(2nd NF) ##

> 第二范式要求，在满足第一范式的基础上，还要**满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分**。如果知道主键的所有属性的值，就可以检索到任何元组(行)的任何属性的任何值。(要求中的主键，其实可以拓展替换为候选键)。

![8858d1c9a9804b06ae6417bc71f1d400.png][]

## 6、第三范式(3rd NF) ##

> 第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，**要求数 据表中的所有非主键字段不能依赖于其他非主键字段。**(即，不能存在非主属性A依赖于非王属性B，非士属任B依赖于主键C的情况，即存在“A→B一C”"的决定关系）通俗地讲，该规则的意思是所有`非主键属性`之间不能有依 赖关系，必须`相互独立`。
> 
> 这里的主键可以拓展为候选键。

![a55bc8b74d874614a9c486f10fcce92b.png][]

## 7、小结 ##

> 关于数据表的设计，有三个范式要遵循。
> 
> (1）第一范式(1NF)，确保每列保持`原子性`
> 
> 数据库的每一列都是不可分割的原子数据项，不可再分的最小数据单元，而不能是集合、数组、记录等非原子数据项。
> 
> (2）第二范式(2NF)，确保每列都和主键`完全依赖`
> 
> 尤其在复合主键的情况下，非主键部分不应该依赖于部分主键。
> 
> (3）第三范式(3NF）确保每列都和主键列`直接相关`，而不是间接相关

**范式的优点:** 数据的标准化有助于消除数据库中的`数据冗余`，第三范式(3NF）通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。

**范式的缺点:** 范式的使用，可能`降低查询的效率`。因为范式等级越高，设计出来的数据表就越多、越精细，数据的冗余度就越低，进行数据查询的时候就可能需要`关联多张表`，这不但代价昂贵，也可能使一些索`引策略无效`。

范式只是提出了设计的标准，实际上设计数据表时，未必一定要符合这些标准。开发中，我们会出现为了性能和读取效率违反范式化的原则，通过`增加少量的冗余`或重复的数据来提高数据库的`读性能`，减少关联查询,join表的次数，实现空间换取时间的目的。因此在实际的设计过程中要理论结合实际，灵活运用。

> 范式本身没有优劣之分，只有适用场景不同。没有完美的设计，只有合适的设计，我们在数据表的设计中，还需要根据需求将范式和反范式混合使用。

# 三、反范式化 #

--------------------

## 1、概述 ##

> **规范化 vs 性能**
> 
>  *  **1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要**
>  *  **2. 在数据规范化的同时 , 要综合考虑数据库的性能**
>  *  **3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间**
>  *  **4. 通过在给定的表中插入计算列，以方便查询**

## 2、 应用举例 ##

![4197c3cdf88d4b9db88a717eb2289596.png][]

## 3、反范式的新问题 ##

>  *  存储 空间变大 了
>  *  一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则 数据不一致
>  *  若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常 消耗系统资源
>  *  在 数据量小 的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加 复杂

## 4、反范式的适用场景 ##

当冗余信息有价值或者能 大幅度提高查询效率 的时候，我们才会采取反范式的优化。

### 1. 增加冗余字段的建议 ###

### 2. 历史快照、历史数据的需要 ###

> 在现实生活中，我们经常需要一些冗余信息，比如订单中的收货人信息，包括姓名、电话和地址等。每次发生的 订单收货信息 都属于 历史快照 ，需要进行保存，但用户可以随时修改自己的信息，这时保存这些冗余信息是非常有必要的。  
> 反范式优化也常用在 数据仓库 的设计中，因为数据仓库通常 存储历史数据 ，对增删改的实时性要求不强，对历史数据的分析需求强。这时适当允许数据的冗余度，更方便进行数据分析。

# 五、BCNF(巴斯范式) #

--------------------

![04126e053e28464ead53226e9c5ddd5d.png][]

![c2fe1172eb744f3eab83e70e5e05268b.png][]

![efac076e4b054c469872f9dc9c97b250.png][]

![26b4dc8008d74f21a449d61af0ed1dcb.png][]

![d094134d774d45c7999d9f87141a5710.png][]

![403954598336455588b55463cb01b452.png][]

# 六、第四范式（了解） #

--------------------

![3b9395892d404f6db52283ddfc2d5327.png][]

![4da626c00319418c8533322d4e6aeb13.png][]

# 七、第五范式、域键范式（了解） #

--------------------

![91e5adcf27c542b9914686c9b3686691.png][]

# 八、ER模型 #

--------------------

## 1、ER模型包括哪些要素 ##

> **ER 模型中有三个要素，分别是实体、属性和关系。**
> 
>  *  **实体** ，可以看做是数据对象，往往对应于现实生活中的真实存在的个体。在 ER 模型中，用 矩形 来表示。实体分为两类，分别是 强实体 和 弱实体 。强实体是指不依赖于其他实体的实体；弱实体是指对另一个实体有很强的依赖关系的实体。
>  *  **属性** ，则是指实体的特性。比如超市的地址、联系电话、员工数等。在 ER 模型中用 椭圆形 来表示。
>  *  **关系** ，则是指实体之间的联系。比如超市把商品卖给顾客，就是一种超市与顾客之间的联系。在 ER 模型中用 菱形 来表示。

**注意：**实体和属性不容易区分。这里提供一个原则：我们要从系统整体的角度出发去看，可以独立存在的是实体，不可再分的是属性。也就是说，属性不能包含其他属性。

## 2、关系的类型 ##

![566327e2c6df429dbade500fb538d80c.png][]

# 九、数据表的设计原则 #

--------------------

> **综合以上内容，总结出数据表设计的一般原则："三少一多"**
> 
>  *  **1. 数据表的个数越少越好**
>  *  **2. 数据表中的字段个数越少越好**
>  *  **3. 数据表中联合主键的字段个数越少越好**
>  *  **4. 使用主键和外键越多越好**

**注意：**这个原则并不是绝对的，有时候我们需要牺牲数据的冗余度来换取数据处理的效率。

# 十、数据库对象编写建议 #

--------------------

## 1、关于库 ##

![ffe355dfd2b441d0b8b643dfab9441a6.png][]

## 2、关于表、列 ##

![61f8942099f146dd98a15b607d5acbe8.png][]

![40bcc20019524284b58248d8859abcce.png][]

## 3、关于索引 ##

![861a5f1b534c442a8cf91304cd7084d2.png][]

## 4、SQL编写 ##

![6b435dfb7b4443769d656e1014ca8e27.png][]

高级篇笔记PDF自取

链接：[https://pan.baidu.com/s/1pVqrTwIZFoED77i-EFmw6g?pwd=3333 ][https_pan.baidu.com_s_1pVqrTwIZFoED77i-EFmw6g_pwd_3333]  
提取码：3333

[Link 1]: https://so.csdn.net/so/search?q=%E5%B0%9A%E7%A1%85%E8%B0%B7&spm=1001.2101.3001.7020
[MySQL]: https://so.csdn.net/so/search?q=MySQL%E9%AB%98%E7%BA%A7&spm=1001.2101.3001.7020
[64bda7b0cca845aba40d7f58e261f06e.png]: https://img-blog.csdnimg.cn/64bda7b0cca845aba40d7f58e261f06e.png
[17a195bbc919421d8256a55cad71f4e6.png]: https://img-blog.csdnimg.cn/17a195bbc919421d8256a55cad71f4e6.png
[f04740a0972d4f20b255318b816fe2f9.png]: https://img-blog.csdnimg.cn/f04740a0972d4f20b255318b816fe2f9.png
[47e7699422404156a4b11a9904d92aaf.png]: https://img-blog.csdnimg.cn/47e7699422404156a4b11a9904d92aaf.png
[8858d1c9a9804b06ae6417bc71f1d400.png]: https://img-blog.csdnimg.cn/8858d1c9a9804b06ae6417bc71f1d400.png
[a55bc8b74d874614a9c486f10fcce92b.png]: https://img-blog.csdnimg.cn/a55bc8b74d874614a9c486f10fcce92b.png
[4197c3cdf88d4b9db88a717eb2289596.png]: https://img-blog.csdnimg.cn/4197c3cdf88d4b9db88a717eb2289596.png
[04126e053e28464ead53226e9c5ddd5d.png]: https://img-blog.csdnimg.cn/04126e053e28464ead53226e9c5ddd5d.png
[c2fe1172eb744f3eab83e70e5e05268b.png]: https://img-blog.csdnimg.cn/c2fe1172eb744f3eab83e70e5e05268b.png
[efac076e4b054c469872f9dc9c97b250.png]: https://img-blog.csdnimg.cn/efac076e4b054c469872f9dc9c97b250.png
[26b4dc8008d74f21a449d61af0ed1dcb.png]: https://img-blog.csdnimg.cn/26b4dc8008d74f21a449d61af0ed1dcb.png
[d094134d774d45c7999d9f87141a5710.png]: https://img-blog.csdnimg.cn/d094134d774d45c7999d9f87141a5710.png
[403954598336455588b55463cb01b452.png]: https://img-blog.csdnimg.cn/403954598336455588b55463cb01b452.png
[3b9395892d404f6db52283ddfc2d5327.png]: https://img-blog.csdnimg.cn/3b9395892d404f6db52283ddfc2d5327.png
[4da626c00319418c8533322d4e6aeb13.png]: https://img-blog.csdnimg.cn/4da626c00319418c8533322d4e6aeb13.png
[91e5adcf27c542b9914686c9b3686691.png]: https://img-blog.csdnimg.cn/91e5adcf27c542b9914686c9b3686691.png
[566327e2c6df429dbade500fb538d80c.png]: https://img-blog.csdnimg.cn/566327e2c6df429dbade500fb538d80c.png
[ffe355dfd2b441d0b8b643dfab9441a6.png]: https://img-blog.csdnimg.cn/ffe355dfd2b441d0b8b643dfab9441a6.png
[61f8942099f146dd98a15b607d5acbe8.png]: https://img-blog.csdnimg.cn/61f8942099f146dd98a15b607d5acbe8.png
[40bcc20019524284b58248d8859abcce.png]: https://img-blog.csdnimg.cn/40bcc20019524284b58248d8859abcce.png
[861a5f1b534c442a8cf91304cd7084d2.png]: https://img-blog.csdnimg.cn/861a5f1b534c442a8cf91304cd7084d2.png
[6b435dfb7b4443769d656e1014ca8e27.png]: https://img-blog.csdnimg.cn/6b435dfb7b4443769d656e1014ca8e27.png
[https_pan.baidu.com_s_1pVqrTwIZFoED77i-EFmw6g_pwd_3333]: https://pan.baidu.com/s/1pVqrTwIZFoED77i-EFmw6g?pwd=3333%C2%A0