第34期：MySQL 表冗余设计

2021-08-19 本文已影响0人爱可生开源社区

image

引言：

上一篇我介绍了 MySQL 范式标准化表设计，范式设计具有以下优点：

把如何消除数据冗余做到极致，从而减少关系表对磁盘的额外占用。

各个表之间的关系表现非常清晰，可读性非常强。

正文：

但是范式设计同样也有缺点：

表范式标准化，等级越高，表数量就越多。比如 2NF 比 1NF 可能要多几张表，3NF 比 2NF 可能又要多几张表等等。
表数量越多，查询时可能需要关联的表就越多。我们知道，检索多表关联的开销比检索单表的开销要大的多。

综上，我们需要结合范式设计的优点，并且想办法去解决范式设计的缺点，由此带来的思路就是允许数据有一定程度的冗余，用空间换时间。比如现在微服务设计、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)

现在有以下几种查询需求：

查询每个部门的部门管理者名字以及对应的部门名字。
查询每个部门的第一位员工名字。
列出“服务部”工资最高的员工名字。
计算每个部门的男女数量。

接下来，来分别实现上述几种查询需求。

第一个需求需要表 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 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！