Wangxiong's Tech

「Stay hungry. Stay foolish.」

嗨,我是王雄 (@Outlier),一名职业PHP开发者,业余Go/Linux爱好者。现居北京,专注金融。我深信,所有的非常,都源于不懈的日常。


这里是我的Jekyll博客,关于Hexo博客,也欢迎前往了解

【MySQL】范式设计和反范式设计

摘要:所谓的范式,是指关系型数据库设计中所需要满足的规范格式(Normal Format),大多数情况下我们需要遵循相应的规则和指导方法,以便我们设计出高效率和优雅的数据库。对应的,任何事物都有两面性,遵循范式设计的数据库在更新操作带来高效率(尤其是在密集的写场景下)和保持数据一致性有一定的优势,但却在查询操作的时候降低了效率。这个时候,适当的反范式设计反而能提高查询效率,(尤其是在读密集的查询场景下)。普遍认为,范式设计中基本满足第一范式(1NF)、第二范式(2NF)、第三范式(3NF)便足够能很好的对数据关系进行约束。第一范式,是指字段具有不可拆分的原子性;第二范式在满足第一范式的基础上,消除部分依赖,要求必须有主键并且非主键字段完全依赖主键(不能存在部分依赖);第三范式在第二范式的基础上,消除传递依赖,要求非主键字段不能相互依赖。关于范式设计和反范式设计的选择,需要根据实际业务场景进行选择,完全的范式化和反范式化设计都是实验室里才会出现的。简单来讲,符合实际业务场景的设计便是好设计。

范式设计和反范式设计

<The rest of contents | 余下全文>

前言

所谓的范式,就是指关系型数据库设计中所需要满足的规范格式(Normal Format)。范式是关系数据库理论的基础,也是我们在设计数据库结构过程中所要遵循的规则和指导方法。只有正确理解数据库的设计范式,才能设计出高效率、优雅的数据库,否则可能会设计出错误的数据库、导致出现数据冗余、难于维护等问题。

不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。但是有些时候一昧的追求范式减少冗余,反而会降低数据读写的效率,这个时候就要反范式,利用空间来换时间。

范式设计

目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。现在数据库设计最多满足3NF,普遍认为范式过高,虽然具有对数据关系更好的约束性,但也导致数据关系表增加而令数据库IO更易繁忙,原来交由数据库处理的关系约束现更多在数据库使用更多的程序来完成。因此本文重点分析范式设计中的1NF2NF3NF

第一范式,字段的原子性

所谓的第一范式是指字段具有不可拆分的原子性,强调的是列的原子性,即列不能够再分成其他几列,不能把两个字段或者多个字段的内容放在一个字段中。例如如下的数据表中,课程时间字段包括开课时间字段和结课时间两个字段。

老师姓名 老师性别 班级名称 上课教室 课程时间
王老师 001 101 2019-09-01至2020-09-01
张老师 002 102 2019-10-01至2020-10-01
高老师 003 103 2019-12-01至2020-12-01
聂老师 004 104 2019-11-01至2020-11-01

对于此不满足第一范式的设计,应该进行字段的拆分。

老师姓名 老师性别 班级名称 上课教室 开课时间 结课时间
王老师 001 101 2019-09-01 2020-09-01
张老师 002 102 2019-10-01 2020-10-01
高老师 003 103 2019-12-01 2020-12-01
聂老师 004 104 2019-11-01 2020-11-01

经过拆分后,以上数据表就满足第一范式的设计。

在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。

第二范式,消除部分依赖

所谓的第二范式,首先要满足它是1NF,另外还需要包含两部分内容:一是表必须有一个主键;二是非主键字段必须完全依赖于主键,而不能只依赖于主键的一部分。

上面改良后的数据表因为没有一个字段可以用来代表整条记录,可以说它不满足第二范式要求。通过增加唯一编号作为主键字段后如下:

ID 老师姓名 老师性别 班级名称 上课教室 开课时间 结课时间
1 王老师 001 101 2019-09-01 2020-09-01
2 张老师 002 102 2019-10-01 2020-10-01
3 高老师 003 103 2019-12-01 2020-12-01
4 聂老师 004 104 2019-11-01 2020-11-01

我们理解了一个表必须有一个主键,那怎么来理解非主键字段必须完全依赖于主键,而不能只依赖于主键的一部分?

实际场景中我们会创建一个订单明细表,包含如下字段:订单编号(OrderID)、商品编号(ProductID)、商品名称(ProductName)、商品价格(UnitPrice)、商品数量(Quantity)。

订单编号 商品编号 商品名称 商品价格 商品数量
1001 01 商品01 99 2
1001 02 商品02 199 6
1001 03 商品03 299 5
1002 04 商品04 399 3

我们知道,在一个订单中可以订购多个商品,所以单单一个OrderID 是不足以成为主键的,主键应该是(OrderIDProductID)。从上面的数据表中我们可以看出,商品数量完全依赖(取决)于主键(OderIDProductID)。而商品的价格和商品的名称只依赖于商品编号(ProductID),此时我们就说该表设计不满足第二范式。因为非主键字段必须完全依赖于主键,而不能只依赖于主键的一部分。

要满足第二范式,我们必须将表作如下处理:

订单编号 商品编号 商品数量
1001 01 2
1001 02 6
1001 03 5
1002 04 3
商品编号 商品名称 商品价格
01 商品01 99
02 商品02 199
03 商品03 299
04 商品04 399

注:判断是否依赖于主键的一部分,说明第二规则的主键是由2个或者2个以上的字段构成的。

第三范式,消除传递依赖

所谓的第三范式,是在满足第一范式(字段的原子性)和第二范式(消除部分依赖)的基础上,再消除传递依赖,也就是非主键字段不能相互依赖。

有如下表:

订单编号 商品编号 客户编号 客户名称 商品数量
1001 商品01 客户01 wx01 7
1002 商品02 客户02 wx02 4
1003 商品03 客户03 wx03 8

从表中我们可以发现,非主键列客户名称依赖于非主键列客户编号,非主键列客户编号依赖于主键的情况,这种情况我们称为传递依赖,客户名称依赖客户编号传递依赖主键。因为第三范式要求非主键列必须直接依赖于主键,不能存在传递依赖,即非主键列不能存在依赖主键,所以以上数据表不满足第三范式。

要满足第三范式,我们做如下处理:

订单编号 商品编号 客户编号 商品数量
1001 商品01 客户01 7
1002 商品02 客户02 4
1003 商品03 客户03 8

|客户编号|客户名称| |–|–| |客户01|wx01| |客户02|wx02| |客户03|wx03|

注:理解2NF3NF的关键点在于,2NF-某字段依赖于主键的一部分,3NF-某字段依赖于某个非主键字段。

小结: 1NF:字段不可分。 2NF:必须有主键,非主键字段完全依赖主键(不能存在部分依赖)。 3NF:非主键字段不能相互依赖。

范式的优点和缺点

优点

当考虑到性能问题,遇到写密集型的场景,建议对表使用范式化设计,因为范式化可以带来如下好处:

  • 范式化的更新操作通常比反范式化要快。
  • 当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据。
  • 范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快。
  • 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

简而言之,范式化的设计避免了大量的数据冗余,使得更新速度更快,同时也节省了存储空间,保持了数据的一致性(不必在超过两个以上的地方更改同一个值)。

缺点

范式化设计的缺点通常是需要关联许多表,这会导致在复杂查询的时候需要多次关联,降低查询效率。

范式越高意味着表的划分更细,一个数据库中需要的表也就越多,用户不得不将原本相关联的数据分摊到多个表中。当用户同时需要这些数据时只能采用连接表的形式将数据重新合并在一起。同时把多个表联接在一起的花费是巨大的,尤其是当需要连接的两张或者多张表数据非常庞大的时候,表连接操作几乎是一个噩梦,这严重降低了系统运行性能。

因此我们引入反范式的设计模式,可以允许适当的数据冗余,用这个冗余去缩短查询数据的时间。实质上是利用空间来换取时间,把数据冗余在多个表中,当查询时可以减少或者是避免表之间的关联。

反范式设计

在反范式的设计模式中,可以允许适当的数据冗余,用这个冗余可以缩短取数据的时间。反范式其本质上就是用空间来换取时间,把数据冗余在多个表中,当查询时就可以减少或者是避免表之间的关联。

反范式的设计目的是利用空间换时间。

假设有一个网站,允许用户发送消息,并且一些用户是付费用户,现在想查看付费用户最近的10条信息。如果是范式化的结构并且索引了发送日期字段published,这个查询也许看起来像这样:

mysql > selct message_text, user_name
     -> from message
     -> inner join user on user.user_id = message.user_id
     -> where user.account_type = 'premiumv'
     -> order by message.published desc limit 10;

要更有效率地执行这个查询,MySQL需要扫描message表的published字段的索引。对于每一行找到的数据,将需要到user表里检查这个用户是不是付费用户。如果只有一小部分用户是付费账户,那么这是效率低下的做法。

另一种可能的执行计划是从user表开始,选择所有付费用户,获得他们所有的信息,并且排序。但这可能更加糟糕。

上面这个主要原因是因为范式设计中的关联,使得需要在一个索引中又排序又过滤。如果采用反范式化组织数据,将两张表的字段合并一下,并且增加一个索引(account_type,published),就可以不通过关联写出这个查询,这将非常高效:

mysql > select message_text , user_name
     -> form message_user
     -> where accout_type = 'premiumv'
     -> order by published desc
     -> limit 10;

通过上面这个示例,我们可以看出来范式设计因为关联导致查询效率低,而反范式设计虽然增加了冗余数据使得磁盘空间消耗较大,但查询效率却得到大幅度的提升,这就是反范式设计所带来的好处,用空间换取时间。

反范式的优点和缺点

优点

因为数据冗余,减少了表的连接甚至不连接,可以更好的利用索引筛选和排序,从而提高查询操作的性能。

数据冗余带来很好的读取性能,反范式适合读取操作密集的场景,不适合做写密集型的场景。

缺点

因为数据冗余,在更新操作或者删除操作时容易造成表中的信息不一致的问题。

对比

|设计|优点|缺点| |–|–|–| |范式设计| 数据没有冗余,更新操作容易,数据一致性高|关联表查询太多,查询性能下降| |反范式设计|数据大量冗余,查询性能较快|更新性能下降,磁盘空间消耗较大,数据一致性低|

范式化和反范式化的 schema 各有优劣,怎么选择最佳的设计?

关于范式设计和反范式设计,需要在数据一致性与查询之间找到平衡点,完全的范式化和反范式化 schema 都是实验室里才会出现的,在实际项目中,经常需要混用。简单来讲,符合实际业务场景的设计才是好设计。

最近

【网络连接】网络连接的全貌

摘要:本文主要回答在浏览器输入URL回车后发生了什么?实质上这个问题背后涉及了大部分计算机网络知识,如果能够站在更高的层面去思考这个问题,相信你的知识面会得到很大的扩充。我在网上参考很多资料,为了能够全面回答这个问题,最后参考了作者户根勤写的《网络是怎样连接的》这本书,本篇文章也是在学习完本书后自身所做的一些思考和整理。网络技术这么复杂,我为什么要去一点一点的去学习这些网络知识呢?用本书作者的话说就是:不理解网络的全貌,也就无法理解每一种网络技术背后的本质意义。而如果无法理解其本质意义, ...…

NetWorks继续阅读
更早

【Java基础】多线程(二)

摘要:本篇文章以电影院卖票为案例需求,主要阐述了多线程安全的问题是如何产生的,为了解决电影院售卖相同票的问题,引入Java中关键字synchronized同步代码块和同步方法的方式来解决数据安全问题,其中需要注意的是在同步方法中的静态同步方法的锁对象是当前函数所属类的字节码文件类名.class,非静态同步方法的锁对象是this,最后也简单介绍了JDK5以后提供的一个新的锁Lock机制来解决线程安全问题。线程同步的好处虽然帮我们更好的解决了多线程安全的问题,但当线程非常多时,为了同步上的锁也...…

Java继续阅读