关于mysql:第34期MySQL-表冗余设计

4次阅读

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

引言：

上一篇我介绍了 MySQL 范式标准化表设计，范式设计具备以下长处：

  1. 把如何打消数据冗余做到极致，从而缩小关系表对磁盘的额定占用。
  2. 各个表之间的关系体现十分清晰，可读性十分强。

注释：

然而范式设计同样也有毛病：
  1. 表范式标准化，等级越高，表数量就越多。比方 2NF 比 1NF 可能要多几张表，3NF 比 2NF 可能又要多几张表等等。
  2. 表数量越多，查问时可能须要关联的表就越多。咱们晓得，检索多表关联的开销比检索单表的开销要大的多。

综上，咱们须要联合范式设计的长处，并且想方法去解决范式设计的毛病，由此带来的思路就是容许数据有肯定水平的冗余，用空间换工夫。比方当初微服务设计、NOSQL 数据库等基本不会思考范式规范实践。

这样的思路也就是明天要讲的重点，简称反范式。

反范式也即通过肯定的冗余把原先高级别的范式设计升高为低级别的范式设计来缩小范式设计带来的表数量增多的毛病。比方满足 BCNF 的表，通过冗余肯定字段，升高为 3NF，甚至升高到 2NF，始终到 1NF。有的场景为了查问性能甚至不须要满足 1NF。比方表 t1, 原本字段有 100 个，其中 5 个罕用，剩下 95 个都不罕用，那能够把这 95 个字段集成到一个大对象字段即可，比方 JSON 类型的字段。

接下来咱们用简略的示例看看反范式如何精简查问语句并且晋升效率。

以下 5 张关系表别离代表员工表，部门表，薪水表，以及员工与部门关系表，员工与薪水关系表。

员工表：

(debian-ytt1:3500)|(ytt)>desc employee;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| employee_number | varchar(64)      | NO   | PRI | NULL    |       |
| employee_name   | varchar(64)      | YES  |     | NULL    |       |
| gender          | char(1)          | YES  |     | NULL    |       |
| age             | tinyint unsigned | YES  |     | NULL    |       |
| register_date   | date             | YES  |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

部门表：


(debian-ytt1:3500)|(ytt)>desc dept;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| dept_id    | tinyint unsigned | NO   | PRI | NULL    |       |
| dept_name  | varchar(64)      | YES  |     | NULL    |       |
| found_date | datetime         | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

薪水表：


(debian-ytt1:3500)|(ytt)>desc salary;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| salary_level | tinyint unsigned | NO   | PRI | NULL    |       |
| base_salary  | decimal(10,2)    | YES  |     | NULL    |       |
| extra_salary | decimal(10,2)    | YES  |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

员工与薪水关系表：

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

员工与部门关系表：


(debian-ytt1:3500)|(ytt)>desc employee_vs_dept;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| employee_number | varchar(64)      | NO   | PRI | NULL    |       |
| dept_id         | tinyint unsigned | NO   | PRI | NULL    |       |
| is_manager      | char(1)          | YES  | MUL | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
当初有以下几种查问需要：
  1. 查问每个部门的部门管理者名字以及对应的部门名字。
  2. 查问每个部门的第一位员工名字。
  3. 列出“服务部”工资最高的员工名字。
  4. 计算每个部门的男女数量。
接下来，来别离实现上述几种查问需要。

第一个需要须要表 employee、dept、employee_vs_dept 三表关联能力失去后果。

(debian-ytt1:3500)|(ytt)>select
    ->   a.employee_name as '管理者名字',
    ->   b.dept_name as '所属部门'
    -> from
    ->   employee as a,
    ->   dept as b,
    ->   employee_vs_dept as c
    -> where
    ->   a.employee_number = c.employee_number
    ->   and b.dept_id = c.dept_id
    ->   and c.is_manager = '是';
+-----------------+-----------------+
| 管理者名字      | 所属部门        |
+-----------------+-----------------+
| 小张            | 研发部          |
| 小徐            | 服务部          |
| 小李            | 材料部          |
| 小倩            | 财务部          |
| 小娜            | 人力资源部      |
| 小聂            | 市场部          |
| 小婷            | 公关部          |
| 小北            | 销售部          |
| 小婉            | 行政部          |
+-----------------+-----------------+
9 rows in set (0.00 sec)

对于第二个需要，假如到目前为止还没有员工到职，那么同样须要 employee、dept、employee_vs_dept 三张表做关联能力拿到后果。能够看到，每个部门的第一位员工都曾经降职到了部门负责人。

(debian-ytt1:3500)|(ytt)>select
    ->   a.employee_name as '员工姓名',
    ->   b.dept_name as '所属部门'
    -> from
    ->   employee as a,
    ->   dept as b,
    ->   (
    ->     select
    ->       min(employee_number) as employee_number,
    ->       dept_id
    ->     from
    ->       employee_vs_dept
    ->     group by
    ->       dept_id
    ->   ) c
    -> where
    ->   a.employee_number = c.employee_number
    ->   and b.dept_id = c.dept_id;
+---------------+-----------------+
| 员工姓名       | 所属部门       |
+---------------+-----------------+
| 小娜          | 人力资源部      |
| 小聂          | 市场部          |
| 小北          | 销售部          |
| 小婷          | 公关部          |
| 小徐          | 服务部          |
| 小婉          | 行政部          |
| 小李          | 材料部          |
| 小倩          | 财务部          |
| 小张          | 研发部          |
+---------------+-----------------+
9 rows in set (0.02 sec)

对于第三个需要，须要联结查问 employee、dept、employee_vs_dept、salary、employee_vs_salary 五张表能力求出后果。

(debian-ytt1:3500)|(ytt)>select
    ->   a.employee_name as '员工姓名',
    ->   b.dept_name as '所属部门'
    -> from
    ->   employee as a,
    ->   dept as b,
    ->   employee_vs_dept as c,
    ->   (
    ->     select
    ->       b.employee_number
    ->     from
    ->       (
    ->         select
    ->           max(salary_level) as salary_level
    ->         from
    ->           salary
    ->       ) as a,
    ->       employee_vs_salary as b
    ->     where
    ->       a.salary_level = b.salary_level
    ->   ) as d
    -> where
    ->   a.employee_number = c.employee_number
    ->   and b.dept_id = c.dept_id
    ->   and d.employee_number = a.employee_number
    ->   and b.dept_name = '服务部';
+---------------+-----------+
| 员工姓名       | 所属部门 |
+---------------+-----------+
| 小郑 4826      | 服务部    |
...
| 小王 2381      | 服务部    |
+---------------+-----------+
93 rows in set (0.01 sec)

对于第四个需要，须要联结查问表 dept、employee、employee_vs_dept 三张表能力出后果 

(debian-ytt1:3500)|(ytt)>select
    ->   a.dept_name as '部门名称',
    ->   sum(case b.gender when '男' then 1 end) as '男',
    ->   sum(case b.gender when '女' then 1 end) as '女'
    -> from
    ->   dept as a,
    ->   employee as b,
    ->   employee_vs_dept as c
    -> where
    ->   a.dept_id = c.dept_id
    ->   and b.employee_number = c.employee_number
    -> group by
    ->   a.dept_id;
+-----------------+------+------+
| 部门名称        | 男   | 女   |
+-----------------+------+------+
| 人力资源部      |  982 | 1179 |
| 市场部          | 1036 | 1163 |
| 销售部          | 1078 | 1200 |
| 公关部          | 1031 | 1190 |
| 服务部          | 1043 | 1199 |
| 行政部          | 1017 | 1224 |
| 材料部          |  956 | 1180 |
| 财务部          | 1076 | 1219 |
| 研发部          | 1037 | 1190 |
+-----------------+------+------+
9 rows in set (0.05 sec)
以上四种需要，想要查出来后果，就必须对多张表做联结查问。

联结查问的开销十分大，为了打消不必要的联结查问，此时就不能齐全依照范式理念来设计表，须要肯定的反范式思维，针对每个需要，增加必要的冗余列方可达到简化查问。

接下来看看对以上这几种需要，如何针对每张表来增加必要的冗余列来晋升查问性能。

针对第一种，能够把部门名称，是否为部门负责人两个字段，冗余到员工表。

(debian-ytt1:3500)|(ytt)>create table employee2 like employee;
Query OK, 0 rows affected (0.07 sec)

(debian-ytt1:3500)|(ytt)>insert into employee2 select * from employee;
Query OK, 20000 rows affected (1.69 sec)
Records: 20000  Duplicates: 0  Warnings: 0

(debian-ytt1:3500)|(ytt)>alter table employee2 add dept_name varchar(64), add is_manager char(1);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

(debian-ytt1:3500)|(ytt)>UPDATE employee2 AS a,
    ->          employee_vs_dept AS b,
    ->          dept AS c SET a.dept_name = c.dept_name,
    ->          a.is_manager = b.is_manager
    -> WHERE a.employee_number = b.employee_number
    ->         AND b.dept_id = c.dept_id;

Query OK, 20000 rows affected (4.40 sec)
Rows matched: 20000  Changed: 20000  Warnings: 0

(debian-ytt1:3500)|(ytt)>alter table employee2 add key idx_is_manager(is_manager);
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

增加完冗余列后，只须要查问员工表即可，不须要关联更多的表。


(debian-ytt1:3500)|(ytt)>select employee_name '管理者名字', dept_name '所属部门' from employee2 where is_manager = '是';
+-----------------+-----------------+
| 管理者名字      | 所属部门        |
+-----------------+-----------------+
| 小张            | 研发部          |
| 小徐            | 服务部          |
| 小李            | 材料部          |
| 小倩            | 财务部          |
| 小娜            | 人力资源部      |
| 小聂            | 市场部          |
| 小婷            | 公关部          |
| 小北            | 销售部          |
| 小婉            | 行政部          |
+-----------------+-----------------+
9 rows in set (0.00 sec)

那么此时针对第二种需要也只须要查问员工表即可。

(debian-ytt1:3500)|(ytt)>SELECT employee_name '员工姓名',dept_name '所属部门'
    -> FROM
    ->     (SELECT employee_name,
    ->         dept_name,
    ->          row_number() over(partition by dept_name
    ->     ORDER BY  employee_number) AS rn
    ->     FROM employee2 ) t
    -> WHERE rn = 1;
+--------------+-----------------+
| 员工姓名     | 所属部门        |
+--------------+-----------------+
| 小娜         | 人力资源部      |
| 小婷         | 公关部          |
| 小聂         | 市场部          |
| 小徐         | 服务部          |
| 小李         | 材料部          |
| 小张         | 研发部          |
| 小婉         | 行政部          |
| 小倩         | 财务部          |
| 小北         | 销售部          |
+--------------+-----------------+
9 rows in set (0.08 sec)

对于第三种需要，只须要往员工表再次退出一个工资等级字段。

(debian-ytt1:3500)|(ytt)>alter table employee2 add salary_level tinyint unsigned;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

(debian-ytt1:3500)|(ytt)>UPDATE employee2 AS a,
    ->          employee_vs_salary AS b SET a.salary_level = b.salary_level
    -> WHERE a.employee_number = b.employee_number;
Query OK, 20000 rows affected (1.77 sec)
Rows matched: 20000  Changed: 20000  Warnings: 0

(debian-ytt1:3500)|(ytt)>alter table employee2 add key idx_salary_level (salary_level);
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

此时查问简化为:

(debian-ytt1:3500)|(ytt)>SELECT employee_name '员工姓名',dept_name '所属部门'
    -> FROM employee2
    -> WHERE salary_level =
    ->     (SELECT max(salary_level)
    ->     FROM salary)
    ->         AND dept_name ='服务部';
+--------------+--------------+
| 员工姓名     | 所属部门     |
+--------------+--------------+
| 小郑 4826     | 服务部       |
...
+--------------+--------------+
93 rows in set (0.00 sec)

同样针对第四种需要，也只须要查问员工表：

(debian-ytt1:3500)|(ytt)>select dept_name '部门名称', sum(if(gender='男',1,0)) '男',sum(if(gender='女',1,0
+-----------------+------+------+
| 部门名称        | 男   | 女   |
+-----------------+------+------+
| 人力资源部      |  982 | 1179 |
| 公关部          | 1031 | 1190 |
| 市场部          | 1036 | 1163 |
| 服务部          | 1043 | 1199 |
| 材料部          |  956 | 1180 |
| 研发部          | 1037 | 1190 |
| 行政部          | 1017 | 1224 |
| 财务部          | 1076 | 1219 |
| 销售部          | 1078 | 1200 |
+-----------------+------+------+
9 rows in set (0.05 sec)

总结：

范式设计标准是关系数据库根底，反范式理念并非一种规范，而是一种简化查问语句并晋升查问性能而定制化表构造的一种办法，目标就是为了可能让查问语句更加简洁，并且更加高效。

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

正文完
mysql
发表至： mysql
2021-08-19
 0
关于mysql:技术分享-MySQL-用户密码过期那点事
关于mysql:MySQL存储引擎概述
关于mysql:MySQL锁等待与死锁问题分析
关于mysql:只要99元零基础学习MySQL

站内搜索

-「」