共计 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 的技术内容,你们还有什么想晓得的吗?连忙留言通知小编吧!