第34期:MySQL 表冗余设计

引言:

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

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

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

正文:

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

  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 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!

(0)

相关推荐

  • mysql常用语句——GROUP BY和HAVING

    创建表结构 create table `employ_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL DE ...

  • 【出版消息】《旅游规划与设计》第34期《体育旅游与户外游憩》出版啦!

    冬季滑雪活动      图片提供:万科松花湖滑雪场 随着国际化和全球化的发展,传统的旅游方式也发生了变化,诸如生态旅游.亲子旅游.家庭旅游.探险旅游等新的旅游方式在世界各地流行起来.在这些新旅游形式中 ...

  • 设计遇上智能,开启无界之旅 | 第34期 Mix+AI

    Mixlab成员周谷越博士作为导师的创业Demo: 方仔照相馆  Knowledge / def mix( ): 本期收录 新增19+ NO.34 #一阶运动模型# #opencv# #开源项目#  ...

  • 中国诗歌报诗词会员创作室第34期临屏集锦

    关注中国诗歌报(中诗报),让诗歌温暖人生! 中国诗歌报★有温暖有情怀 中诗报诗词会员创作室编辑部 主编:枫原 执行主编:乘物游心客 副主编:一阕清词 王者 编辑:华不注诗坛 朱兴鹏 本期主题:喜迎五一 ...

  • 抗压故事会第34期

    张爱玲曾经说过:于千万人之中,遇见你要遇见的人.于千万年之中,时间无涯的荒野里,没有早一步,也没有迟一步,遇上了也只能轻轻地说一句:"哦,你也在这里吗?" 珍惜生命中我们每一段遇见 ...

  • 栗江风韵第34期

    栗江风韵第34期

  • 人间词话每周一对第34期

    "每周一对"第三十四期 (02.24-03.02) 出句:红遍桃花,东风入牖催春梦:(草野) 优选对句: 对句:碧荣荷叶,上雨灌园滋夏苗.(孙斌) 对句:绿盈陌野,喜雨湿阶唤垄耕. ...

  • 第34期:在不断攀爬中,更多地向内看,稳住自己

    孩子会自己长大的 如果我等他长大,我就老了,我就死了 我就什么也做不了了 对不起,孩子并不比我更重要 正如我不比我父亲更重要 我们各人都是自己的 相互区别,相互爱着 --海桑<一个朋友说> ...

  • 第34期:20210408

    第34期:20210408 伙伴: 早,今天分享话题:[改变] 没有立刻马上的成功与获得,没有一成不变的稳定与安逸,因为外部环境在变化.在竞争,我们要进步.需要更多. 请改变我们的弱沟通.弱合作.弱承 ...

  • 【青青子衿诗苑微刊第34期】

       青青子衿诗苑一周岁生日! 猫咪副站长感言摘取: 二零一八年的四月一日,我们迎来青青子衿诗苑一周年诞辰.这是一个喜庆又富有浪漫色彩的日子.它就像一颗稚嫩的种子,在汲取了文字的营养和诗人的呵护,华丽 ...