数库据设计最佳实践-CSDN博客

本文链接：https://blog.csdn.net/cooper_lyt/article/details/136807083

本文分享了一位经验丰富的程序员在设计和维护数据库过程中的思考，涉及字段选择、事务处理、乐观锁策略、主键和外键的使用，以及微服务分库中的数据冗余问题。作者强调了数据库设计的动态性和测试的重要性。

摘要由CSDN通过智能技术生成

中老年程序员，从业生涯设计过很多数据库，有用上的也有没用上的，有精心设计花无数心思更改了无数次的也有敷衍了事能用就行的，有最糟糕的设计也有感觉还不错的。在设计和修改过程中有很多疑问和感悟，在此记录一下以方便自己查阅如果还能给后来人一些帮助和启发那就十分容幸了。

本文主要是以程序员角度讨论关系型数据库的设计思路及对程序代码编写造成的影响，以随想方式发布，想起来的就写一点，想不起来就算了。

可统计字段

在设计库的时候总有一些字段是可加可不加，是可以通过其它数据统计出来的，到底要不要加上这个字段呢，最典型的例子就是“余额”字段，这个值是可以通过明细表用SQL统计出来的，那要不要加上这个字段呢，这个答案很明显，肯定是要加上的，不仅要加，还要通过结算表把每期的余额都记录下来，以便以需要重新算。那么这是为什么呢？其它不那么明显的字段要不要加上呢？加不加的考虑因素有以下几点：

事务隔离级别：以“余额”为例，如果没有这个字段，那么每次事务都要把整个明细表锁住来操作，而有这个字段的话，那么一个行锁就解决了，也就是说加和不加这个字段在程序开发时需要的事务隔离级别是不同的，要尽量使用低级的隔离级别，事务隔离级别越低性能越好，而且可以有效防止死锁的产生，所以我推荐程序开发时以较低的隔离级别做为默认值，在有需要的操作时提高隔离级别，如果这个字段存在可以有效降低所需事务的隔离级别，那么加上吧。
是否要频繁读取：主要出于性能考虑，这个很容理解可以提高查询性能，如果只是月报上需要的一个字段，果断不用加。
使用时的实时性和准确性：主是这个字段数据的实时性和准确性要求有多高，如果这个字段实时性要求很高，在读取它的时候需要加锁才能进行读取那么就加上它，反之可以不加。这是出于对以上两点的综合考虑。
一致性：如果这个字段会频繁变化，且对偏差容忍度很低。在这里“余额”是一个反例，它确实会频繁，且对偏差容忍度也不高。这个时候还加上了这个字段，那么就要做一些补偿性设计了。以“余额”为例：
- 在所有会产生此字段变化的操作适当提高事务的隔离级别。
- 增加结算表来记录每期余额。
- 最终解释权，余额与明细不符时以余额为准，这不是个笑话，说的是在程序开发时要优先保证“余额”数据的准确性，明细表什么的可以延迟变动或通过重试机制来处理明细操作时发生的错误。

快照表

有一些数据是以快照的形式存在的，也就是一但完成操作成为历史那么是不应该也不可以进行更改的，例如帐户的明细表，非要更改也是另加一条冲帐的明细来进行另类的更改，在设计的时候区分出这些快照表那么表中需不需要加乐观锁，建立时间，更新时间等这种字段就不言自明了。

对历史信息特别敏感且有多种数据来源时我建议加一张快照索引表，以房屋为例：由测绘信息快照，登记信息快照，规化信息快照等多张表组合连接到一张房屋信息快照索引表中，然后由房屋表记录快照索引表的ID，在每次信息发生变化时，由业务生成新的快照索引重新连接发生变化的快照表索引ID并在完成时更新房屋表的索引ID，这样就能保证房屋的历史档案信息在任何时候查看都不会发生变化，要不要加快照索引表就看业务的数据来源吧，像前面帐户的例子就不需要，因为明细数据来源比较单一。

这么设计需要在查询时增加很多LEFT JOIN，造成需要很长的SQL才能查全信息，那么就需要一些补偿设计：

程序中使用缓存，由于快照记录一但建立就不会发生变化，也不会被删除，这简直是最理想的缓存对象。使用Spring boot cache 配合 Caffeine 可以使得这种操作简单到只需加一个注解就完成了，由于不用担心缓存数据不一致问题, 根本不需要考虑什么时候要更新缓存。（注：Spring boot 3.2 以前的版本对Mono 和 Flux 的缓存有点问题，它缓存的是Mono对象本身而不是Mono中的内容，所以在缓存方法返回时要加一下.cache()防止读缓存时重复执行响影链，3.2 及以后的版开始支持Mono 和Flux，不需要再加cache()方法，加了有时反尔会有问题）
服务前加一级Redis，由redis来组合各个快照分片并扁平化对象提供实时信息，并提高访问性能。
数据库后加一级Elasticsearch同步，由于redis是目录型数据库，对于拉列表和查询无能为力，所以后端我使用了canal 将数据推送到es中提供查询和拉列表及统计的功能，canal这货的坑很多，参见我另一篇文章吧。

以上几点对于由于微服务分库造成的数据存储分散也是一个不错的解决方案。

乐观锁

乐观锁的原理很简，实现更简单Spring boot data 一个注解的事，这里不作讨论，我总结加乐观锁有以下几点需要注意一下：

保证一次原子操作只有一个乐观锁，可以减少不必要的版本检查和发生不必要的异常。
最后更新有乐观锁的表，这主要是基于如果有后台数据推送的情况时，因为一般情况下有乐观锁的表也就是推送数据时被监听的表，最后更新可时使得推送被监听到时就可以取得所有操作的相关数据，同时还可以根据数据版本号来过滤重复和多余的推送，如果只有子表发生变化包含乐观锁的表没有变化，最好也更新一下这张表，这样才能触发后面的数据推送。
不要使用Update语句直接更新带有乐观锁的表，这样会造成乐观锁的版本号不正确。

关于乐观锁要不要传递给前端，总觉得把这个锁传递给前端再由前端传递回来感觉不太好，虽然叫乐观锁，传递给前端的话有点过于乐观了吧，传递链太长，时间也太长，想来想去还是看具体情况吧，我认为仅量还是不要传给前端，如果要传递，那么前端就要做好错误处理和页面超时。例如，两个同时发起请求，由于前端用户操作时长的原因，两个更改的到达时间是不确定的，如果把锁传递给了前端，那么其中一个肯定会得到一个错误，而如果不传递那么两个都会成功，只是修改完成后最终的结果是不确定的，但如果操作有完善的操作记录也给以给用户一个合理的解释，可以减少前端的错误处理，这不仅仅是为了减少前端的开发难度，而是因为在前端不管你错误处理的多么优雅，对最终用户来说都感觉像是要出大事了，它们会立刻变的警觉起来，大声宣布系统出问题了，根本不会去读给出的错误提示，不敢再进行任何操作，并且把以前和以后出现的所有问题都归咎于系统又双叕出错了，所以不把锁传到前端可以有效减少程序开发人员对用户的打骂次数。

主键的选择

主键的选择我知道的有以下几种，各有优缺点：

UUID
数据库自增
有意义的编号
雪花ID
机器ID加序号

每种的优缺点网上说的很多，我简单说下，

UUID使用上最简单，一个注解就行，而且是无限的，其它总有用尽的时候虽然时间长到可以忽略不计，问题是它是无序的。
数据库自增和雪花ID都有序的，而且自增ID还是连续的但使用时需要先存储后才能获得
雪花ID和机器ID加序号都需要几位机器ID，使用起来相比其它要麻烦一些，但是这两个都有开源的实现，百度有一个分布式的雪花ID的实现，但是机器ID的获取有问题（太过于浪费而且不能重复利用），我根据它的代码重写一份通过spring cloud的注册服务来获取机器ID减少浪费，并支持Mono和Spring autoconfig,已在github上开源发布。美团也开源了一份同时支持这两种类型的实现，没用过不知到怎么样。
有意义的编号最后都需要有几位序号，最终还是要靠一个单一的源来实现不够分布式，而且有意义的编号最终都会变的没有意义，接触过编码规范做的最好的就是身份证号了，包含了很多信息，而且可以自校验，但里面的信息最后都会变成错的，例如，出生地区划代码，很多地方的区划代码会因行政级别的变化而改变，更别说里面还有性别信息了，当你看到一个男性大美女你会在惊呆的同时怀疑自己的程序出Bug了还是怀疑自已眼睛出现问题了呢？

这几种类型的主键我都用过，最终我认为最好的选择就是数据库自增ID和雪花ID组合起来使用，有一些没有关连表的主键使用自增ID，需要做关联的使用雪花ID。偶尔也可以使用有意义的编号。但要注意一点永远不要使用外部的编号来做主键，例如：社会信用统一代码（除非你就是颁发部门）因为使用之后你就会发现它所声称的唯一和不变在你的程序中就是个笑话。

外键和索引

索引和主键一样是一定要加的，不然性能和使用文本文件存数据区别不大，在数据库里加外键约束会自动根据外键字段生成索引，听说阿里是不允许在库里加外键约束，应该是出于对性能的考虑，不过我还是推荐加上外键约束的，一来大部分程序都不像阿里都有那么大的访问量和对性能极至的追求，二来如果有历史数据需要导入可以及时发现问题，不然程序会时不时的因为历史数据出一些莫名奇秒的问题，很让人头秃。如果真的不想要，可以在程序运行稳定很长时间后再全部移除。

关于索引的加法，我不是专业的DBA，给不出太专业的意见，我的习惯是前期先建立一些基本的索引，在程序开发时每写一条查询就根据SQL再建一个索引，最后在测试阶段开启慢查询再补一遍索引，索引真的很关键，我宁可多加也不想漏加，不要舍不得那点磁盘空间了，当然有条件也可以把这些工做都丢给DBA，毕竟人家是专业的。

字段类型的选择

Blob字段能不用就别用，见过很多次往数据库里存图片的，我认为这是个最糟糕的设计，索引时浪费磁盘空间，查询缓存时浪费内存，传输时浪费IO，及度影响性能。有很多开源好用的小文件存储引擎可以选用，而且只要是个云服务供应商都会提供这类存取服务。
时间类型，优先选择时区相关的时间类型，要知道中国的早8点和美国的早8点是完全不同的。以mysql为例有两种，datetime和timestamp，其中timestamp是时区相关的，但是需要注意的是timestamp 是会根据服务器所在时区变化的，而且早期版本这个类型默会自动更新为最后更新时间（不理解早期为什么要这么设置，当你在数据库上使用update时批量更新数据，你会惊喜的发现所有的时间字段内的数据都丢了），有的时候也应该选用datetime这种时区无关的类型来存储的,因为有些时间在语义上是自带时区信息的，例如身份证上的出生日期，无论是在哪里，意义都是中国时区上的时间。
字符类型的长度，这个要注意的是不仅要考虑内容所需长度，还要考虑最终在用户界面上的显示方式，不然最终用户界面是个什么样就很难估计了。
字典类型，尽量使用枚举以字符串的型式在数据库中映射存储而不是字典表，正常的ORM框架对枚举的映射都不是什么问题，这么做不仅可以减少关联表查询，还可以减少很多因为预计不到的字典项产生的错误，而且可以让枚举现实一个接口来减少代码中对IF的使用。使用字符串而不是数值映射是因为这会使数据内容更容易理解，防止别人看到库里的一个数值想知道是啥意思的时候忍不住问候你的亲人。
不要依赖数据库的默认值，如果要指定默认值，最好的选择就是 null 。例如：updateAt,createAt等字段，现在的ORM框架也能很好的实现默认值，例如Spring 的 @CreatedDate注解用起来也很方便，如果是依赖默值来实现，参考上面timestamp类型默认值问题，你可能会因为预计不到的惊喜而脱发，

存储过程

很多文章不推荐使用存储过程，因为会影响数据库迁移。但我认为有的功能的实现使用存储过程还是必要的，有些功能使用存储过程配合数据库的定时任务是会很方便，例如，月末结算,定期无用数据清理等。使用存储过程可以屏蔽不必要的外来影响，例如网络，IO等资源，除此之外性能优势也很明显因为自己在内部就解决了不用将数据传来传去，需要注意的是在调用存储过程和使用定任务的代码处一定要写好注释。另外想要为程序更换数据库的话可不是只要在ORM中更改个方言类型就完事了，别天真了。

触发器还是别用了，会使用的行为结果不太好预测，你会因为忘记某个触发器，而产生一些不可理解的结果。