seo发帖网站,国内著名网站建设公司,上海建设人才网站,吕梁购物网站开发设计文章目录 简介数据库的设计范式有哪些数据库中的几种键从1NF到3NF1NF2NF3NFBCNF#xff08;巴斯范式#xff09; 反范式设计反范式的适用场景总结参考文献 简介
本小节主要内容#xff1a;
数据库的设计范式都有哪些数据库的键都有哪些1NF、2NF和3NF都是指什么#xff1f… 文章目录 简介数据库的设计范式有哪些数据库中的几种键从1NF到3NF1NF2NF3NFBCNF巴斯范式 反范式设计反范式的适用场景总结参考文献 简介
本小节主要内容
数据库的设计范式都有哪些数据库的键都有哪些1NF、2NF和3NF都是指什么什么是BCNF什么是反范式设计它有什么适用场景
数据库的设计范式有哪些
范式NF可以理解成是一张数据表在设计时需要满足的某种设计标准的级别。
目前关系型数据库一共有六种范式按照范式级别从低到高依次是
1NF即第一范式2NF即第二范式3NF即第三范式BCNF即巴斯-科德范式4NF第四范式5NF第五范式也叫做完美范式。
数据库的范式设计越高阶冗余度就越低 同时高阶范式一定满足低阶范式的要求比如满足2NF的一定满足1NF。
一般来讲在关系型数据库里数据表的设计应该尽量满足3NF但是并不绝对很多时候出于提升查询性能的需要我们会反范式设计主动冗余一些字段。 数据库中的几种键
超键只要是能唯一标识元组的属性集都叫做超键。
候选键是不含多余属性的超键可以理解成是最小超键从它的集合里找不到一个子集也是超键
主键从候选键中选择一个作为主键
外键不介绍了
主属性包含在任一候选键里的属性称为主属性
非主属性与主属性相对不包含在任何一个候选键里的属性。
举个例子以球员表为例一个球员表包含球员的编号、姓名、证件号、年龄和球队编号。
而超键就是包含球员编号或者证件号的任意组合比如说球员编号、证件号、证件号、姓名、证件号、姓名、年龄等。
而候选键就是最小超键即球员编号或者证件号。我们可以从它俩里选一个做主键。
外键就是球队编号。
主属性就是球员编号、证件号剩下的都是非主属性。
从1NF到3NF
1NF
1NF是指数据库表中的任何属性都是原子性的不可再分。事实上任何DBMS都会满足第一范式的要求不会将字段进行拆分。
2NF
2NF指数据库表中的非主属性都要和这个数据表的候选键有完全依赖关系。这里的完全依赖是指依赖候选键的全部属性。
简单的说呢就是针对概念上的联合主键非主属性依赖于联合主键的每一个字段而非部分字段。
接下来我们介绍一个没有满足2NF的例子。
我们设计了一张球员比赛表其包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地这些属性字段。
按照上一小节介绍的这张表的候选键和主键都是球员编号比赛编号因此我们可以得到以下关系
球员编号比赛编号决定了姓名、年龄、比赛时间、比赛场地这个是不满足2NF的。
为啥这么说呢
因为非主属性姓名、年龄、比赛时间、比赛场地并没有完全依赖主属性。
这张表里还存在以下关系
球员编号 决定了 姓名年龄
比赛编号 决定了 比赛时间比赛场地候选键的某个字段就可以决定非主属性。
这会产生什么问题呢
数据冗余如果一个球员参加了m场比赛那么球员的年龄和姓名数据就重复了m-1次。如果一场比赛有n个球员参加那么比赛的时间和场地信息就重复了n-1次。插入异常如果我们想添加一场新的比赛但是还没有决定哪些球员能参加这个数据是没法插入的删除异常如果我想删除某场比赛的信息但是有球员只参加了这一场比赛的话就会把这个球员的信息也给删掉更新异常如果一场比赛调整了比赛时间那么我需要把这个表里这场比赛的全部记录都update时间。
为了避免以上情况我们可以按照2NF将球员比赛表设计为三张表
球员表包含球员编号姓名年龄比赛表包含比赛编号、比赛时间、比赛场地球员比赛表包含比赛编号、球员编号和个人得分等各种字段。
所以从某种程度上来讲2NF是1NF的升级。1NF只告诉我们字段属性是独立的而2NF告诉我们一张表就是一个独立的对象即一张表只表达一个意思。
3NF
3NF在满足2NF的同时要求任何非主属性都不传递依赖于候选键。也就是说不能存在非主属性A依赖于非主属性B而B依赖于候选键的情况。
在3NF里每个非主属性必须且只能直接依赖于全部候选键。可以理解成非主属性只能直接依赖于全部主键字段。
再举一个例子假设我们有这么一张球员表其包含了球员编号、球员姓名、球队名称和球队教练。
这些字段的依赖关系如下图 可以看到球队教练这个属性是传递依赖于球员编号的这个就不符合3NF的要求。
如果要按照3NF来设计的话上面应该分成两个表
球员表球员编号、姓名和球队编号球队表球队编号、球队名称和球队教练
BCNF巴斯范式
举一个例子假设我们有一张仓库管理表包含以下字段 在这个表里我们指定一个仓库只能有一个管理员且一个管理员只能管理一个仓库即仓库和管理员严格一对一。
这样的话仓库名决定了管理员而管理员也决定了仓库名同时仓库名物品名或者是管理员物品名又决定了数量这个属性。
所以这张表的候选键就是仓库名物品名、管理员物品名。我们随便挑一个候选键作为主键比如仓库名物品名。
于是主属性就是仓库名、物品名、管理员非主属性就是数量。
接下来我们判断一下这张表的所属范式。
数据表每个属性都是原子性的符合1NF的要求
非主属性数量与候选键全部依赖不存在仅使用候选键的某个子集就可以决定数量的情况。比如说仓库名或者物品名都无法决定数量。所以符合2NF的要求
非主属性数量不传递依赖于候选键而且也没有其他非主属性让它去传递依赖。所以符合3NF的要求。
综上数据表符合3NF的要求那是不是在设计上就没什么问题了呢
不是还是有很多问题
增加一个新仓库但是没有存放任何物品。由于物品名是联合主键不能为空因此插入异常仓库要更换管理员需要修改这个仓库下所有物品的记录仓库的商品卖空了没有物品了。由于主键不能为空这个仓库的信息只能删除那么这个仓库的信息和对应的管理员信息也会被删除掉。
可以看到即使一张表满足了3NF的要求同样还是存在插入、更新和删除的问题。
这是为什么呢
归根结底的原因是主属性仓库名对候选键管理员物品名是部分依赖的关系。“管理员可以单独决定仓库名”。
于是人们在3NF的基础上进行了改进提出了BCNF。
BCNF在3NF的基础上进一步要求主属性也不能对候选键有部分依赖或者传递依赖。
因此根据BCNF我们可以将上面的仓库管理表进一步细分成两张表
仓库表仓库名管理员库存表仓库名物品名数量
反范式设计
越高阶的范式设计出来的表数量就越多数据冗余度就越低。但是这其实并不完全是好事因为分化出来的表越多意味着我们要做一个业务查询的时候需要关联的表就越多。
因此我们在实际生产中经常为了性能和读取效率而做反范式的设计。即允许少量冗余字段的存在以空间来换时间。
反范式也是一种对查询效率的优化思路。
接下来举一下教程里的例子通过实验来模拟一下反范式的优化效果。
我们需要两张表
商品评论表product_comment对应字段如下 用户表user对应的字段如下 然后我们分别给两张表模拟出百万量级的数据可以通过存储过程来实现。
给用户表随机生成100w用户
CREATE DEFINERrootlocalhost PROCEDURE insert_many_user(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT (2017-01-01 00:00:00);
DECLARE date_temp DATETIME;
SET date_temp date_start;
SET autocommit0;
REPEAT
SET ii1;
SET date_temp date_add(date_temp, interval RAND()*60 second);
INSERT INTO user(user_id, user_name, create_time)
VALUES((starti), CONCAT(user_,i), date_temp);
UNTIL i max_num
END REPEAT;
COMMIT;
END在循环前我们将 autocommit 设置为 0这样等计算完成再统一插入执行效率更高。
然后调用call insert_many_user(10000, 1000000);生成编号从10000开始的百万用户数据教程里是消耗了1分37秒。
接着给商品评论表随机生成100w条评论内容为20个随机字母存储过程如下
CREATE DEFINERrootlocalhost PROCEDURE insert_many_product_comments(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT (2018-01-01 00:00:00);
DECLARE date_temp DATETIME;
DECLARE comment_text VARCHAR(25);
DECLARE user_id INT;
SET date_temp date_start;
SET autocommit0;
REPEAT
SET ii1;
SET date_temp date_add(date_temp, INTERVAL RAND()*60 SECOND);
SET comment_text substr(MD5(RAND()),1, 20);
SET user_id FLOOR(RAND()*1000000);
INSERT INTO product_comment(comment_id, product_id, comment_text, comment_time, user_id)
VALUES((STARTi), 10001, comment_text, date_temp, user_id);
UNTIL i max_num
END REPEAT;
COMMIT;
END然后调用call insert_many_product_comments(10000, 1000000)教程里是花了2分7秒。
如果我们现在接到一个需求需要查询产品10001的前1000条评论并且需要显示出对应的评论人姓名需要写成
SELECT p.comment_text, p.comment_time, u.user_name FROM product_comment AS p
LEFT JOIN user AS u
ON p.user_id u.user_id
WHERE p.product_id 10001
ORDER BY p.comment_id DESC LIMIT 1000运行时长为0.395s。
效率不高。这是因为两个表都是百万量级的大表关联的时候要做嵌套查询消耗自然就上去了。
如果我们想提升查询的效率就可以允许适当的字段冗余比如说在商品评论表里增加用户名这个字段这样一来只需要单表查询就可以完成预定的需求如
SELECT comment_text, comment_time, user_name FROM product_comment2 WHERE product_id 10001 ORDER BY comment_id DESC LIMIT 1000消耗时间仅为0.039s。
反范式的适用场景
综上反范式可以通过空间来换时间从而提升查询效率。
但是在数据量小的情况下反范式并体现不出优势反而会把数据库的搞得更加复杂比如说增加了更新的难度可能要单独编写触发器之类的自动更新。
反范式经常被用在数据仓库的设计之中。因为数仓通常是存储历史数据以OLAP为主对增删改的实时要求不高反而是对历史数据的分析需求强。这时候适当增加数据的冗余度更方便进行数据分析。
教程里对下数据仓库和数据库在使用上的区别是这么总结的 数据库设计的目的在于捕获数据而数据仓库设计的目的在于分析数据 数据库对数据的增删改实时性要求强需要存储在线的用户数据而数据仓库存储的一般是历史数据 数据库设计需要尽量避免冗余但为了提高查询效率也允许一定的冗余度而数据仓库在设计上更偏向采用反范式设计。
其实上面的总结里还是保守了数仓对反范式的应用是很广泛的。比如说数仓中为了方便分析经常会加工一些宽表冗余大量字段来实现单表支撑分析的功能。另外在数仓的分层里从ODS到ADS上层其实都是对下层的冗余。
总结
可以看到从1NF到BCNF突出的都是一个去冗余化主打的就是一个精简就如同相同的代码不能出现两次在范式设计的思想下会重复出现的字段都应该抽离出来单独成表。
或者可以换个角度去思考以BCNF一节中的例子来说这个表里其实是有3个实体的分别是仓库、管理员和物品。其中仓库管理员是1:1而仓库物品是N:1管理员:物品同样是N:1。
三个实体且三个实体间是1:1:N。这就不符合范式设计的要求了在范式设计下单表里最好只保留两个及以下的实体不同实体之间的关系最好是1:1或者1:N。因此优化后的例子里把1:1:N拆分成了1:1和1:N两张表。
当然实际设计数据表的时候未必要符合这些原则尤其是是在分布式的OLAP应用场景下。
一方面是这些范式本身就存在一些问题在插入、更新和删除的时候可能会带来一些异常。另外它们也会降低查询的效率这是因为范式等级越高设计出来的数据表就越多实际做查询的时候就需要关联很多很多张表从而降低查询效率。
说句题外话我在教程的下面评论里看到一条极其有才的评论
范式与反范式正如传统与解构规则与务实稳定与突破守成与创新是阴阳动静的矛盾关系两者一而二二而一即和而不同、求同存异落脚点是务实也就是应用场景和业务需求。
所以说这已经不单是数据库设计的问题而中国哲学体系在互联网商业中实践指导。
数据库设计提出范式的同时存在反范式的要求符合否定之否定的螺旋上升轨迹是数据库也是SQL语言保持强壮生命力而经久不衰的重要原因是现实生存逻辑的映射。看完当场下跪这就是互联网的发言吗。
参考文献
21丨范式设计数据表的范式有哪些3NF指的是什么22丨反范式设计3NF有什么不足为什么有时候需要反范式设计
