MySQL中级优化教程（六）——表的范式化和反范式化

青旅半醒 2022-01-13 13:59 164阅读 0赞

*  您可以到这里下载本篇博文所使用的数据库以及优化工具mysqldumpslow和pt-query-digest：
 *  [https://blog.csdn.net/IT\_TIfarmer/article/details/92224646][https_blog.csdn.net_IT_TIfarmer_article_details_92224646]
 *  关于explain语句的结果参数详解也可以参考如上博文。

> ###  前言： ###

范式化是指数据库设计的规范，目前说范式化，一般是指第三设计范式，也就是要求数据表中不存在非关键字段对任意关键字段的传递依赖和部分依赖。

> ### 范式化： ###

**下面我们从一个例子出发简单的介绍一下第三范式，如下图：**

![20190616100911170.png][]

我们现在已经有了一张表和上述两条数据，首先解释什么是关键字：

关键字就是一张表的一列或几列组合在一起，能够唯一确定该表每一行数据。

比如，我们的商品名称，就可以唯一确定该表的每一条数据，因为如果我们知道了商品名称是可乐，我们也就可以唯一确定剩下所有字段的值了（价格、重量、有效期、分类、分类描述），在这里，商品名称这一列就是主键，而剩下的字段都是非主键。而什么是传递依赖呢？

我们容易看出分类描述是依赖于分类的，而分类又是依赖于主键商品名称的（即商品名称确定了分类，分类又确定了分类描述），所以，分类描述与主键商品名称之间就产生了传递依赖。

**依赖传递会导致哪些问题呢？**

*  数据冗余：（分类，分类描述）对于每一个商品都会进行记录；
 *  数据的删除异常；
 *  数据的更新异常；
 *  数据的插入异常。

我们说它数据冗余是因为，分类和分类描述这两个字段其实逻辑意义上是等同的，确定了二者的任意一个，也就确定了另一个，所以二者没有必要出现在同一张表中，重复的出现只会使对该字段相关的查询操作的资源开销增大。

删除异常：当我们删除了所有饮料类的商品时，饮料的分类描述也都将会消失，不可见。

更新异常：如果要更新一个分类描述，就要对所有相同分类的数据的分类描述全都进行更新。

插入异常：如果“饮料”分类描述下没有对应的商品，则无法看到碳酸饮料的分类描述。

**那么我们如何使之满足第三范式？**

答曰：拆婊！

如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70][]

我们将之拆成三个表，这样就可以达到最佳的优化效果，也不会再出现上述问题了。

> ### 反范式化： ###

**范式化我们应该理解的差不多了，也知道了它的好处。那么什么是反范式化呢？为嘛要反范式化？**

为了实现范式化，我们可能会把一张表拆分成几张，这样在查询主表信息的时候，就要去关联查询很多张表。反范式化是指为了查询效率的考虑，把原来符合第三范式的表适当增加冗余，以达到优化查询效率的目的，反范式化是一种以空间换取时间的操作。

且看下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 1][]

例子虽有些不恰当，但也能说明问题（细心的小伙伴会发现第一张和第四张表有传递依赖）。

对于上述几张表结构，如果我们想要查询订单信息，我们要这么去查：

SELECT b.用户名,b.电话,b.地址,a.订单ID,SUM(c.商品价格, * c.商品数量) as 订单价格
    FROM 订单表 a
    JOIN 用户表 b ON a.用户ID = b.用户ID
    JOIN 订单商品表 c ON c.订单ID = b.订单ID
    GROUP BY b.用户化名, b.电话, b.地址, a.订单ID

上边的语句无疑是相对复杂的，而且还用到了group by，要使用临时表来对数据进行处理，效率显然不高，而这时为了达到相同的查询业务需求，我们对sql语句优化的空间非常小，这时我们就要考虑在表结构的设计上增加冗余数据了：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 2][]

如图，我们在订单表中又添加了几个冗余字段，这是我们的查询语句就变成了：

SELECT a.用户名, a.电话, a.地址, a.订单ID, a.订单价格 FROM 订单表 a

虽然每张表所占的空间更大了，但省去了多个连接查询和group by语句，我们的sql效率还是提升了很多。

PS：关于第二范式、第三范式的详细说明，大家感兴趣的话可以参考这篇博文：

[https://www.cnblogs.com/knowledgesea/p/3667395.html。 ][https_www.cnblogs.com_knowledgesea_p_3667395.html]

[https_blog.csdn.net_IT_TIfarmer_article_details_92224646]: https://blog.csdn.net/IT_TIfarmer/article/details/92224646
[20190616100911170.png]: /images/20220113/4195085d04ba48f9ab04c2a6e68e0194.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70]: /images/20220113/76fa93fc844d43faa5e09932f02bc96e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190616101740939.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190616101907325.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy,size_16,color_FFFFFF,t_70
[https_www.cnblogs.com_knowledgesea_p_3667395.html]: https://www.cnblogs.com/knowledgesea/p/3667395.html%E3%80%82%C2%A0