关系型数据库表结构的两个设计技巧

谁践踏了优雅 2022-01-12 03:21 194阅读 0赞

关系型数据库表结构的设计，有下面两个设计技巧：

物理主键作为关联的外键

关系型数据库，由多个数据表构成。每一个数据表的结构是相同的，不同表之间可能存在关联关系。表之间的关联关系，正是关系型数据库得名的原因。

一个表由多个字段构成。其中可能有多个字段适合作为主键。主键字段，就是表中每一行都不会有重复数据的字段。

主键，可以分为两种：物理主键和逻辑主键。

每一张数据库的表，都使用自增长的id字段作为物理主键。

多表之间的外键关联，都关联其他表的物理主键，也就是关联其他表的id字段。

逻辑主键，就是除了id字段外的不重复的字段。我们设计数据库的外键关联时，不使用逻辑主键，而是使用物理主键。

这是因为，物理主键肯定是主键，因为它是自增的。（对于不支持自增字段的关系数据库，可以使用uuid保证物理主键的唯一性）

而逻辑主键，则可能随着业务的发展，成为可重复的字段。一旦这种情况发生，关系数据库的外键关系就被破坏了。如，可能你觉得姓名是逻辑主键，但可能后面发现有人重名，那么这个字段就不再是主键了。而如果之前使用这个字段作为外键，那么查询返回的数据就不是一一对应的了。

这就是为什么要使用物理主键，以及用物理主键作为关联外键的原因。

使用乐观锁更新依赖之前状态的记录

考虑这样一种数据库的应用场景：

任务单的接管：多个操作人员从一个任务池中接管一个任务。只有第一个获取任务的操作人员才能成功接管该任务，后续操作人员的接管操作都必须失败。

这时，就需要一种数据库记录的锁定机制。只有第一个事务才能更新记录。

数据库可以使用悲观锁和乐观锁来锁定数据库记录。

悲观锁是如下sql语句实现的：

SELECT \* FROM t FOR UPDATE

这条语句会在其他修改内容的事务提交后返回最新的数据。

一旦执行这条语句，这些记录就被锁住了，不能被其他sql事务修改。直到本事务提交。

乐观锁，是应用程序实现的，不是数据库实现的机制。乐观锁，对于数据库来说，就是没有上锁。事务可以select其他事务已经提交的数据。更新数据时，数据库保证多个事务的更新是原子的。

悲观锁，会导致事务等待其他事务完成。乐观锁，只会等待其他事务的更新语句的完成，不会等待整个事务完成，因此效率较高。

实现乐观锁的方法：

给数据库表添加一个version字段。version是一个数字类型的字段，每次更新都加1。每次更新时都要检测version字段是否和当前事务的值相同。如果version字段不同，那么就表明在查询数据之后，有其他事务已经更新了该记录，就会导致此次更新失败。应用必须重新载入最新的数据，然后重新更新数据。

如果使用乐观锁，那么如果数据库中version和应用中version相同，则用version+1的版本值更新version字段。 SQL语句如下：

update studentVersion set ver=?, name=? where id=? and ver=?

不使用锁更新独立状态的记录

考虑这样一种数据库的应用场景：

需要更新虚拟机的状态。多个事务可能会同时更新虚拟机的状态为start或者stop。这种状态的更新和前一个阶段的状态是无关的，因此不需要锁定记录。直接更新即可。此时不需要使用悲观锁或者乐观锁。

如果这个表添加了version字段，直接忽略对version字段的比较和更新即可。

SQL语句如下：

update studentVersion set name=? where id=?

总结

设计关系型数据库的表时，需要给表添加一个ID字段（自增字段，或者uuid字段）和一个version字段（数值类型）。ID字段作为物理主键，用于保证记录的不可重复性和用作外键关联。

version字段用于实现乐观锁，提供比悲观锁更好的性能。特别是对于UI显示并可能出现并发更新的数据，更需要使用乐观锁来提升数据库访问性能。

对于后台自动更新的任务，可以使用乐观锁实现。但需要在冲突发生时实现自动退让。也可以使用悲观锁在数据库上对事务进行排队来解决更新冲突问题。

对于不关心记录的状态之间关系的场景，可以直接更新记录，忽略掉version字段的检测和更新。

转载于:https://juejin.im/post/5d09de17f265da1ba84a947e

发表评论取消回复

表情：

评论列表（有 0 条评论，194人围观）

还没有评论，来说两句吧...

相关阅读

相关关系型数据库表之间的联系[关系]详解

关系型数据库表之间的联系\[关系\]详解在表中，行和列的逻辑顺序无关紧要。每个表至少包含一列，该列被称为主键，它唯一地标识了表中的每一行。键是表中扮演特殊角色的列，

拼搏现实的明天。/ 2023年01月17日 13:59/ 0 赞/ 203 阅读

相关 Oracle数据库同时查询两个数据库的两个表

本地数据库分别访问服务器上的两个数据库，对两个数据库的数据分别进行比较。命令创建 create database link 随便起个名字 connect to 用

Myth丶恋晨/ 2022年10月01日 12:50/ 0 赞/ 28 阅读

相关架构师：关系型数据库设计技巧？

![53e5be8535522ea4ee49e65e46a895fe.png][] 摘要本文讨论关系数据库设计相关的一些内容，涉及关系模型，表结构设计等内容，以学生选

小鱼儿/ 2022年08月30日 11:27/ 0 赞/ 208 阅读

相关数据库技术：关系型数据库设计总结

关系型数据库简介关系数据库由由埃德加·科德（IBM）在1969年左右提出。自推出后就成为商业应用的主要数据库模型（与其他数据库模型，如分级、网络或对象模型相比）。如今已

约定不等于承诺〃/ 2022年05月23日 03:52/ 0 赞/ 255 阅读

相关比较两个数据库的表结构差异

/\ \--比较两个数据库的表结构差异 \--邹建 2003.9-- \/ /\ \--调用示例 exec p\_comparestructure

ゝ一世哀愁。/ 2022年04月01日 11:24/ 0 赞/ 290 阅读

相关关系型数据库设计范式

一、关系型数据库设计范式 > 范式：Normal Format，符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度范式是离散数

我就是我/ 2022年03月09日 10:10/ 0 赞/ 387 阅读

相关关系型数据库表结构的两个设计技巧

关系型数据库表结构的设计，有下面两个设计技巧：物理主键作为关联的外键关系型数据库，由多个数据表构成。每一个数据表的结构是相同的，不同表之间可能存在关联关系。表之间的

谁践踏了优雅/ 2022年01月12日 03:21/ 0 赞/ 195 阅读

相关如何设计关系型数据库？

本文讨论关系数据库设计相关的一些内容，涉及关系模型，表结构设计等内容，以学生选修课程讲述设计过程，在尽量讲清楚设计要领的前提下，简化设计内容。本文基于MySQL数据库为基础

快来打我*/ 2021年09月08日 23:58/ 0 赞/ 490 阅读

相关数据库设计60个技巧

数据库设计指南如果把企业的数据比做生命所必需的血液，那么数据库的设计就是应用中最重要的一部分。有关数据库设计的材料汗牛充栋，大学学位课程里也有专门的讲述。不过，就如我们

墨蓝/ 2021年06月26日 16:06/ 0 赞/ 399 阅读

相关如何设计一个关系型数据库

存储管理逻辑数据和物理存储的映射。缓存机制加载的页，块会缓存，根据计算机理论里面的局部性原理，当访问一条数据的时候，接下来它周围的数据也会大概率访问到，所以

àì夳堔傛蜴生んèń/ 2021年06月10日 20:39/ 0 赞/ 593 阅读