关于范式:第33期MySQL-表标准化设计

5次阅读

共计 11637 个字符，预计需要花费 30 分钟才能阅读完成。

关系表设计正当与否是影响关系型数据库性能的外围因素之一。

谈到关系型数据库表设计问题，首先想到的是范式实践。也就是说一张表的设计首先要满足肯定的范式，完了后再依据肯定的需要来反范式设计，也即冗余备用设计。

数据库范式个别蕴含 6 个，别离为 1NF、2NF、3NF、BCNF、4NF、5NF。这 6 个范式级别别离从数据是否容许肯定范畴的冗余、数据是否更加精细化的治理这两个关键点登程，越往后，数据越标准，冗余越小，可读性越强。

一般来说，达到 3NF 或者 BCNF 即可，或者更进一步，达到 4NF 即可，5NF 更加偏差学术。

再次假如咱们对各种依赖关系十分明确（局部函数依赖，齐全函数依赖，传递函数依赖，多值依赖，连贯依赖等）。上面我用经典的员工表与学生表来举例说明每个范式的逐级优化。

1NF：也即属性具备原子性，不可拆分。

对数据如何寄存要求最低，目标是让关系表的属性（字段或列）放弃原子性，不可再次拆分。

1NF 用员工表来做示例，表构造如下：

(debian-ytt1:3500)|(ytt)>desc employee;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | varchar(64) | YES  |     | NULL    |       |
| employee_name   | varchar(64) | YES  |     | NULL    |       |
| salary          | json        | YES  |     | NULL    |       |
| dept            | varchar(64) | YES  |     | NULL    |       |
| dept_desc       | text        | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

表 employee 有五个字段，别离为 employee_number(员工号码)、employee_name(员工姓名)、salary(员工薪水)、dept(所属部门）、dept_desc(所属部门形容信息）。

员工薪水字段数据类型为 JSON，用来寄存数组或者字典数据，很显著员工薪水字段不具备原子性，员工表不合乎 1NF。插入一条记录，更加直观：

(debian-ytt1:3500)|(ytt)>select * from employee\G
*************************** 1. row ***************************
employee_number: 202010001
  employee_name: 小王
         salary: {"base_salary": 10000, "extra_salary": 100000}
           dept: 财务部
      dept_desc: NULL
1 row in set (0.00 sec)

以上后果中能够看出，员工薪水字段蕴含了两个属性：基本工资和额定工资。那革新下这个字段，一个变两个，革新后的员工表构造如下：


(debian-ytt1:3500)|(ytt)>desc employee;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | varchar(64) | YES  |     | NULL    |       |
| employee_name   | varchar(64) | YES  |     | NULL    |       |
| base_salary     | varchar(30) | YES  |     | NULL    |       |
| extra_salary    | varchar(30) | YES  |     | NULL    |       |
| dept            | varchar(64) | YES  |     | NULL    |       |
| dept_desc       | text        | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

员工薪水字段被拆分成两个：根本薪水和额定薪水。此时表 employee 每个字段都具备原子性，不可拆分，合乎 1NF。

然而合乎 1NF 的表冗余仍然太多，比方一个部门有 10 个员工，那 dept 和 dept_desc 两个字段就会有很多重复记录。能够这么了解，除了 1NF 用来保障列原子性外，前面的所有更高级的范式都是保障在特定场景下的表原子性。

2NF：非主键字段必须对主键字段齐全函数依赖，不存非主键字段对主键字段的局部函数依赖。

2NF 比 1NF 在数据标准上更进一步，缩小更多的冗余。

2NF 改用学生表来做示范，学生表构造如下：


(debian-ytt1:3500)|(ytt)>desc ` 学生表 ` ;
+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| 学号               | varchar(64)      | NO   | PRI | NULL    |       |
| 姓名               | varchar(64)      | YES  |     | NULL    |       |
| 年龄               | tinyint unsigned | YES  |     | NULL    |       |
| 性别               | char(1)          | YES  |     | NULL    |       |
| 课程               | varchar(64)      | NO   | PRI | NULL    |       |
| 课程学分           | tinyint unsigned | YES  |     | NULL    |       |
| 所属系名称         | varchar(64)      | YES  |     | NULL    |       |
| 课程问题           | varchar(10)      | YES  |     | NULL    |       |
| 系地址             | varchar(100)     | YES  |     | NULL    |       |
| 系电话             | varchar(20)      | YES  |     | NULL    |       |
+--------------------+------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

这里对于学生表，设计主键为（学号，课程），姓名、年龄、性别等字段依赖学号字段，然而不依赖课程字段；课程问题、课程学分依赖课程字段，不依赖学号字段。所以此表存在非主键局部依赖主键，不满足 2NF，须要做进一步的拆分。拆分后的表构造如下：

学生表：

(debian-ytt1:3500)|(ytt)>desc ` 学生表 `;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| 学号            | varchar(64)      | NO   | PRI | NULL    |       |
| 姓名            | varchar(64)      | YES  |     | NULL    |       |
| 年龄            | tinyint unsigned | YES  |     | NULL    |       |
| 性别            | char(1)          | YES  |     | NULL    |       |
| 所属系名称      | varchar(64)      | YES  |     | NULL    |       |
| 系地址          | varchar(100)     | YES  |     | NULL    |       |
| 系电话          | varchar(20)      | YES  |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

课程表：

(debian-ytt1:3500)|(ytt)>desc ` 课程表 `;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| 课程         | varchar(64)      | NO   | PRI | NULL    |       |
| 课程学分     | tinyint unsigned | YES  |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

选课表：

(debian-ytt1:3500)|(ytt)>desc ` 选课表 `;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 学号         | varchar(64) | NO   | PRI | NULL    |       |
| 课程         | varchar(64) | NO   | PRI | NULL    |       |
| 课程问题     | varchar(10) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

拆完后，学生表满足 2NF。大部分场景都够用了，然而拆分后的学生表在某些场景下仍然存在冗余问题。比方新开了几个冷门系，没有招到任何学生，那学生表里就会被插入一些除了 所属系名称 ， 系地址 ， 系电话 等的空记录。那针对这种场景，2NF 临时无奈满足要求，须要更高级别的标准设计。

3NF：不存在非主键字段对主键字段的传递依赖。

3NF 在 2NF 根底上拆的更细，打消了在满足 2NF 的表中非主键字段对主键字段的传递依赖关系。

拿拆分后的学生表来举例：非主键字段都依赖主键字段 学号 ，然而同时非主键字段 系地址 和系电话 还同时依赖字段 所属系名称。所以此时存在这样的关系：

系地址 和系电话 依赖 所属系名称 ，同时 所属系名称 在此表中又依赖 学号，所以此时学生表不满足 3nf。为了满足 3NF，咱们再次对学生表做更进一步的拆分：

(debian-ytt1:3500)|(ytt)>desc ` 学生表 `;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| 学号            | varchar(64)      | NO   | PRI | NULL    |       |
| 姓名            | varchar(64)      | YES  |     | NULL    |       |
| 年龄            | tinyint unsigned | YES  |     | NULL    |       |
| 性别            | char(1)          | YES  |     | NULL    |       |
| 所属系名称      | varchar(64)      | YES  |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

院系表

(debian-ytt1:3500)|(ytt)>desc ` 院系表 `;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| 系名称   | varchar(64)  | NO   | PRI | NULL    |       |
| 地址   | varchar(100) | YES  |     | NULL    |       |
| 电话   | varchar(20)  | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

拆分后，新学生表满足 3NF。接下来把之前的员工表 employee 也革新成 3NF，拆分成 employee 和 dept 两张表。


(debian-ytt1:3500)|(ytt)>desc employee;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | varchar(64) | NO   | PRI | NULL    |       |
| employee_name   | varchar(64) | NO   |     | NULL    |       |
| base_salary     | varchar(30) | YES  |     | NULL    |       |
| extra_salary    | varchar(30) | YES  |     | NULL    |       |
| dept            | varchar(64) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

(debian-ytt1:3500)|(ytt)>desc dept;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept      | varchar(64) | NO   | PRI | NULL    |       |
| dept_desc | text        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

从新拆分后，员工表也合乎 3NF。大部分场景来讲，3NF 足以。

我下面例子中的表都是繁多主键，如果是联结主键，则须要更细的拆分，这时候可能就得须要更高级别的 BCNF。

BCNF：在 3NF 根底上，打消联结主键中每个键之间的局部依赖与传递依赖关系。

BCNF，强调联结主键内的字段依赖关系。

比方下面曾经满足 3NF 的学生表和员工表，假如有这种场景：

有局部学生可能就读多个系，此时学生和系是多对对的关系，把表主键由 学号 改为联结主键（学号 , 所属系名称）

(debian-ytt1:3500)|(ytt)>alter table ` 学生表 ` drop primary key, add primary key (` 学号 `,` 所属系名 `);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

此时联结主键的两个字段相互依赖，合乎 3NF，然而不合乎 BCNF，有可能导致重复记录。

比方小李学习很好，又对很多其余的院系有趣味，同时攻读物理系、数学系、心理学系，相干记录如下：

(debian-ytt1:3500)|(ytt)>select * from ` 学生表 `;
+------------+--------+--------+--------+-----------------+
| 学号       | 姓名   | 年龄   | 性别   | 所属系名称      |
+------------+--------+--------+--------+-----------------+
| 2020100090 | 小李   |     21 | 男     | 心理学系        |
| 2020100090 | 小李   |     21 | 男     | 数学系          |
| 2020100090 | 小李   |     21 | 男     | 物理系          |
+------------+--------+--------+--------+-----------------+
3 rows in set (0.00 sec)

能够看到非主键字段有多个反复值！所以当初依据这样的场景来拆分学生表,

(debian-ytt1:3500)|(ytt)>desc ` 学生表 `
    -> ;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| 学号   | varchar(64)      | NO   | PRI | NULL    |       |
| 姓名   | varchar(64)      | YES  |     | NULL    |       |
| 年龄   | tinyint unsigned | YES  |     | NULL    |       |
| 性别   | char(1)          | YES  |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

(debian-ytt1:3500)|(ytt)>desc ` 学生_系_关系表 `;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int unsigned | NO   | PRI | NULL    | auto_increment |
| 学号            | varchar(64)  | YES  |     | NULL    |                |
| 所属系名称      | varchar(64)  | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

再看看革新后的两表记录

(debian-ytt1:3500)|(ytt)>select * from ` 学生表 `;
+------------+--------+--------+--------+
| 学号       | 姓名   | 年龄   | 性别   |
+------------+--------+--------+--------+
| 2020100090 | 小李   |     21 | 男     |
+------------+--------+--------+--------+
1 row in set (0.00 sec)

(debian-ytt1:3500)|(ytt)>select * from  ` 学生_系_关系表 `;
+----+------------+-----------------+
| id | 学号       | 所属系名称      |
+----+------------+-----------------+
|  1 | 2020100090 | 心理学系        |
|  2 | 2020100090 | 数学系          |
|  3 | 2020100090 | 物理系          |
+----+------------+-----------------+
3 rows in set (0.00 sec)

同样针对员工表，也存在这样的场景，比方有局部员工可能身兼数职，隶属于多个部门，革新办法相似。

(debian-ytt1:3500)|(ytt)>desc employee;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | varchar(64) | NO   | PRI | NULL    |       |
| employee_name   | varchar(64) | NO   |     | NULL    |       |
| base_salary     | varchar(30) | YES  |     | NULL    |       |
| extra_salary    | varchar(30) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

(debian-ytt1:3500)|(ytt)>desc employee_vs_dept;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int unsigned | NO   | PRI | NULL    | auto_increment |
| employee_number | varchar(64)  | YES  |     | NULL    |                |
| dept            | varchar(64)  | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

4NF: 在 3NF 根底上，打消多值依赖关系。

比 3NF 更加精密，仍然拿员工表来举例。比方一家外企，在中国有分公司，中国分公司的员工有中文名字，然而必须每天跟外国总部沟通，所以每个人取了一个英文名字，这时候员工表的主键就变为联结主键（employee_no,employee_name）一些示例数据如下：

(debian-ytt1:3500)|(ytt)>select * from employee;
+-----------------+---------------+-------------+--------------+
| employee_number | employee_name | base_salary | extra_salary |
+-----------------+---------------+-------------+--------------+
| 202010050       | lucy          | 50000       | 60000        |
| 202010050       | 小青          | 50000       | 60000        |
| 202010051       | simon         | 60000       | 20000        |
| 202010051       | 小张          | 60000       | 20000        |
+-----------------+---------------+-------------+--------------+
4 rows in set (0.00 sec)

从记录后果发现，其中薪水这两个字段有反复数据，员工表个别还有其余的很多字段，所以反复数据在这种场景下会更多。

那打消这种重复记录的办法就是再次拆分这张表，拆分为三张表，表构造如下：

(debian-ytt1:3500)|(ytt)>desc employee;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | varchar(64) | NO   | PRI | NULL    |       |
| base_salary     | varchar(30) | YES  |     | NULL    |       |
| extra_salary    | varchar(30) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)



(debian-ytt1:3500)|(ytt)>desc employee_zh;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | varchar(64) | NO   | PRI | NULL    |       |
| employee_name   | varchar(64) | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

(debian-ytt1:3500)|(ytt)>desc employee_en;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_number | varchar(64) | NO   | PRI | NULL    |       |
| employee_name   | varchar(64) | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


(debian-ytt1:3500)|(ytt)>select  * from employee;
+-----------------+-------------+--------------+
| employee_number | base_salary | extra_salary |
+-----------------+-------------+--------------+
| 202010050       | 50000       | 60000        |
| 202010051       | 60000       | 20000        |
+-----------------+-------------+--------------+
2 rows in set (0.00 sec)

(debian-ytt1:3500)|(ytt)>select  * from employee_zh;
+-----------------+---------------+
| employee_number | employee_name |
+-----------------+---------------+
| 202010050       | 小青          |
| 202010051       | 小张          |
+-----------------+---------------+
2 rows in set (0.00 sec)

(debian-ytt1:3500)|(ytt)>select  * from employee_en;
+-----------------+---------------+
| employee_number | employee_name |
+-----------------+---------------+
| 202010050       | lucy          |
| 202010051       | simon         |
+-----------------+---------------+
2 rows in set (0.00 sec)

此时这种场景下，冗余数据又被拆分掉了。

总结：

其实事实场景中，并不需要严格依照范式来设计表，有时候可能须要反着来。毕竟遵循的范式实践越高，表拆的就越细，检索表数据须要的表连贯就越多，表连贯可是开销很大的。

对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

正文完

范式

发表至：范式

2021-08-05

0

关于范式:第33期MySQL-表标准化设计

关于范式:打造面向未来的开发者服务新范式龙蜥社区开发者服务平台-devFree-MeetUp-硬核启动欢迎报名

关于tcaplusdb:运维必经操作TcaplusDB-tcapsvr扩缩容介绍

关于范式:第33期MySQL-表标准化设计

1NF：也即属性具备原子性，不可拆分。

对数据如何寄存要求最低，目标是让关系表的属性（字段或列）放弃原子性，不可再次拆分。

2NF：非主键字段必须对主键字段齐全函数依赖，不存非主键字段对主键字段的局部函数依赖。

2NF 比 1NF 在数据标准上更进一步，缩小更多的冗余。

3NF：不存在非主键字段对主键字段的传递依赖。

3NF 在 2NF 根底上拆的更细，打消了在满足 2NF 的表中非主键字段对主键字段的传递依赖关系。

BCNF：在 3NF 根底上，打消联结主键中每个键之间的局部依赖与传递依赖关系。

4NF: 在 3NF 根底上，打消多值依赖关系。

总结：

站内搜索