数据库设计之范式与反范式

布满荆棘的人生 2023-01-23 01:05 143阅读 0赞

# 范式设计 #

## 什么是范式？ ##

范式来自英文Normal Form，简称NF。要想表之间设计—个好的关系，必须使关系  
满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求  
得严格。满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入  
(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟，不仅给数据  
库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需要的冗余信息。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三  
范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又  
称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上  
进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来  
说，数据库只需满足第三范式(3NF）就行了。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70]

## 数据库设计的第一范式 ##

定义： 属于第一范式关系的所有属性都不可再分，即数据项不可分。

理解： 第一范式强调数据表的原子性，是其他范式的基础。例如下表  
：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name-age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>morris-18</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>bob-19</td> 
  </tr> 
 </tbody> 
</table>

name-age列具有两个属性：name和age，不符合第一范式，把它拆  
分成两列  
：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>morris</td> 
   <td>18</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>bob</td> 
   <td>19</td> 
  </tr> 
 </tbody> 
</table>

上表就符合第一范式关系。但日常生活中仅用第一范式来规范表格是远远不  
够的，依然会存在数据冗余过大、删除异常、插入异常、修改异常的问题，此时  
就需要引入规范化概念，将其转化为更标准化的表格，减少数据依赖。

实际上，1NF是所有关系型数据库的最基本要求，你在关系型数据库管理系  
统（RDBMS），例如SQL Server，Oracle，MySQL中创建数据表的时候，如果数  
据表的设计不符合这个最基本的要求，那么操作一定是不能成功的。也就是说，  
只要在RDBMS中已经存在的数据表，一定是符合1NF的。

## 数据库设计的第二范式 ##

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二  
范式（2NF）必须先满足第一范式（1NF）。

第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一的区分。  
通常在实现来说，需要为表加上一个列，以存储各个实例的唯一标识。例如员工  
信息表中加上了员工编号（emp\_id）列，因为每个员工的员工编号是唯一的，因  
此每个员工可以被唯一区分。这个唯一属性列被称为主关键字或主键、主码。  
也就是说要求表中只具有一个业务主键，而且第二范式（2NF）要求实体的  
属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的  
属性。什么意思呢？

有两张表：订单表，产品表  
。

<table> 
 <thead> 
  <tr> 
   <th>订单表ID</th> 
   <th>订单时间</th> 
   <th>产品ID</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2021-04-16</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2021-05-16</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>产品表ID</th> 
   <th>产品名称</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>iphone</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>computer</td> 
  </tr> 
 </tbody> 
</table>

一个订单有多个产品，所以订单的主键为【订单ID】和【产品ID】组成的  
联合主键，这样2个组件不符合第二范式，而且产品ID和订单ID没有强关联，  
所以可以把订单表进行拆分为订单表与订单与商品的中间表  
。

<table> 
 <thead> 
  <tr> 
   <th>订单表ID</th> 
   <th>订单时间</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2021-04-16</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2021-05-16</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>订单-产品中间表ID</th> 
   <th>订单表ID</th> 
   <th>产品ID</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

## 数据库设计的第三范式 ##

第三范式指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第  
二范式的基础上消除了非主键对主键的传递依赖。例如，存在一个部门信息表，  
其中每个部门有部门编号（dept\_id）、部门名称、部门简介等信息。那么在员  
工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息  
再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该  
构建它，否则就会有大量的数据冗余。

<table> 
 <thead> 
  <tr> 
   <th>订单表ID</th> 
   <th>订单时间</th> 
   <th>产品ID</th> 
   <th>产品名称</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2021-04-16</td> 
   <td>1</td> 
   <td>iphone</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2021-05-16</td> 
   <td>2</td> 
   <td>computer</td> 
  </tr> 
 </tbody> 
</table>

其中  
：

*  产品ID与订单编号存在关联关系
 *  产品名称与订单编号存在关联关系
 *  产品ID与产品名称存在关联关系

如果产品ID发生改变，产品姓名也会改变；产品名称发生变化，这样不符  
合第三范式，应该把产品名称这一列从订单表中删除  
。

# 反范式设计 #

## 什么是反范式设计 ##

完全符合范式化的设计真的完美无缺吗？很明显在实际的业务查询中  
会大量存在着表的关联查询，而大量的表关联很多的时候非常影响查询的性能。  
所谓反范式化就是为了性能和读取效率得考虑而适当得对数据库设  
计范式得要求进行违反。允许存在少量得冗余，换句话来说反范式化就是使用空  
间来换取时间。

## 反范式设计-商品信息 ##

下面是范式设计的商品信息表  
，商品信息和分类信息经常一起查询，所以把分类信息也放到商品表里面，冗  
余存放。

商品信息表：

<table> 
 <thead> 
  <tr> 
   <th>商品信息表ID</th> 
   <th>商品名称</th> 
   <th>出版社名称</th> 
   <th>图书价格</th> 
   <th>图书描述</th> 
   <th>作者</th> 
  </tr> 
 </thead> 
 <tbody></tbody> 
</table>

分类信息表：

<table> 
 <thead> 
  <tr> 
   <th>分类信息表ID</th> 
   <th>分类名称</th> 
   <th>分类描述</th> 
  </tr> 
 </thead> 
 <tbody></tbody> 
</table>

商品分类对应关系表：

<table> 
 <thead> 
  <tr> 
   <th>商品分类对应关系表ID</th> 
   <th>商品信息表ID</th> 
   <th>分类信息表ID</th> 
  </tr> 
 </thead> 
 <tbody></tbody> 
</table>

商品信息和分类信息经常一起查询，所以把分类信息也放到商品表里面，冗  
余存放。

商品信息表反范式设计：

<table> 
 <thead> 
  <tr> 
   <th>商品信息表ID</th> 
   <th>商品名称</th> 
   <th>商品分类</th> 
   <th>出版社名称</th> 
   <th>图书价格</th> 
   <th>图书描述</th> 
   <th>作者</th> 
  </tr> 
 </thead> 
 <tbody></tbody> 
</table>

## 范式化和反范式总结 ##

范式化设计优缺点  
：

*  范式化的更新操作通常比反范式化要快。
 *  当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修  
    改更少的数据。
 *  范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
 *  很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP  
    BY语句。在非范式化的结构中必须使用DISTINCT或者GROUP BY才能获得一份唯  
    一的列表，但是如果是一张单独的表，很可能则只需要简单的查询这张表就行了。
 *  范式化设计的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的  
    表上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引  
    策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中  
    本可以属于同一个索引。

反范式化设计优缺点  
：

*  反范式设计可以减少表的关联
 *  可以更好的进行索引优化。
 *  反范式设计缺点也很明显，存在数据冗余及数据维护异常，对数据的修改  
    需要更多的成本。

## 实际工作中的反范式实现 ##

### 性能提升-缓存和汇总 ###

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

请记住：小孩子才做选择，我们全都要；小孩才分对错，大人只看利弊。

而现实也是，完全的范式化和完全的反范式化设计都是实验室里才有的东西，  
在真实世界中很少会这么极端地使用。在实际应用中经常需要混用。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特  
定列。

比如从父表冗余一些数据到子表的。前面我们看到的分类信息放到商品表里  
面进行冗余存放就是典型的例子。

缓存衍生值也是有用的。如果需要显示每个用户发了多少消息，可以每次执  
行一个对用户发送消息进行count的子查询来计算并显示它，也可以在user表用  
户中建一个消息发送数目的专门列，每当用户发新消息时更新这个值。

有需要时创建一张完全独立的汇总表或缓存表也是提升性能的好办法。“缓  
存表”来表示存储那些可以比较简单地从其他表获取（但是每次获取的速度比较  
慢）数据的表（例如，逻辑上冗余的数据)。而“汇总表”时,则保存的是使用 GROUP  
BY语句聚合数据的表。

在使用缓存表和汇总表时，有个关键点是如何维护缓存表和汇总表中的数据，  
常用的有两种方式，实时维护数据和定期重建，这个取决于应用程序，不过一般  
来说，缓存表用实时维护数据更多点，往往在一个事务中同时更新数据本表和缓  
存表，汇总表则用定期重建更多，使用定时任务对汇总表进行更新。

### 性能提升-计数器表 ###

计数器表在Web应用中很常见。比如网站点击数、用户的朋友数、文件下  
载次数等。对于高并发下的处理，首先可以创建一张独立的表存储计数器，这样  
可使计数器表小且快，并且可以使用一些更高级的技巧。

比如假设有一个计数器表，只有一行数据，记录网站的点击次数，网站的每  
次点击都会导致对计数器进行更新，问题在于，对于任何想要更新这一行的事务  
来说，这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行  
执行，会严重限制系统的并发能力。

怎么改进呢？可以将计数器保存在多行中，每次随机选择一行进行更新。在  
具体实现上，可以增加一个槽（slot)字段，然后预先在这张表增加 100 行或者更  
多数据，当对计数器更新时，选择一个随机的槽（slot)进行更新即可。  
这种解决思路其实就是写热点的分散，在 JDK 的 JDK1.8 中新的原子类  
LongAdder 也是这种处理方式，而我们在实际的缓冲中间件 Redis 等的使用、架  
构设计中，可以采用这种写热点的分散的方式，当然架构设计中对于写热点还有  
削峰填谷的处理方式，这种在MySQL的实现中也有体现。

### 反范式设计-分库分表中的查询 ###

例如，用户购买了商品，需要将交易记录保存下来，那么如果按照买家的维度分表，则每个买家的交易记录都被保存在同一表中，我们可以很快、很方便地査  
到某个买家的购买情况，但是某个商品被购买的交易数据很有可能分布在多张  
表中，査找起来比较麻烦 。反之，按照商品维度分表，则可以很方便地査找到  
该商品的购买情况，但若要査找到买家的交易记录，则会比较麻烦 。

所以常见的解决方式如下：

1.  在多个分片表查询后合并数据集，这种方式的效率很低。
2.  记录两份数据，一份按照买家维度分表，一份按照商品维度分表
3.  通过搜索引擎解决，但如果实时性要求很高，就需要实现实时搜索  
    在某电商交易平台下，可能有买家査询自己在某一时间段的订单，也可能有  
    卖家査询自已在某一时间段的订单，如果使用了分库分表方案，则这两个需求是  
    难以满足的，因此，通用的解决方案是，在交易生成时生成一份按照买家分片的  
    数据副本和一份按照卖家分片的数据副本，查询时分别满足之前的两个需求，因此，查询的数据和交易的数据可能是分别存储的，并从不同的系统提供接口。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70]: /images/20221020/553920a06fb242b3b5af16e3cb8f2c3e.png