引入

最近一直在筹划重构我在2021年完成的一个项目。不仅是因为该项目在设计时存在很多不足之处，也是为了复习知识点。今天，从数据库层面对项目进行了优化。

项目旧版数据库存在的缺陷

旧版的数据库设计可以参考下图所示。

在后续的开发过程中，逐渐发现数据库存在下面的问题。

  1. 用户表使用了email作为主键，而其它一些表采用了自增主键。

    这样的设计完全可以实现后续的业务，但是存在一些问题。

    email并不是一个理想的主键。应该避免使用这种对用户有意义的信息作为主键，因为随着后续业务逻辑的复杂度上升，它是完全可能改变，或者重复的。

    自增主键在服务器端产生，需要有一把自增的 AI 锁保护，若这时有大量的插入请求，就可能存在自增引起的性能瓶颈，所以存在并发性能问题；

  2. 数据库创建了三个完全不同的用户表，而三个表的大部分字段意义是相同的。

    这会导致数据库的设计显得冗余，不够灵活。

  3. 对城市这个关系的处理存在问题。

    在招聘网站的业务逻辑中，岗位/企业所在的城市是一个很重要的考量因素。在上面的数据库设计中并没有考虑到这一点，导致后续业务的实现存在一定被动。

  4. 岗位与企业是一对多关系，不需要单独设立关系表。

    在设计数据库时出现了思维惯性，设立了岗位-企业关系表。

  5. 消息-用户表的主键不合理，会导致同一个用户，只能接收同一个企业的同一种消息一次。

    这限制了业务的实现，并且不符合逻辑。

针对性解决方案

1.UUID_TO_BIN优化的主键设计方案

正如上面提到的，直接采用邮箱或者自增主键都存在一些问题。理想的主键设计方式要满足下面的这些要素：

  1. 占用空间小

    主键不宜过长，这会导致空间的浪费。

  2. 有序生成

    在MySQL中，使用的存储引擎默认是InnoDB，数据实际上是按主键的顺序存储的。这种存储方式称为聚簇索引（Clustered Index）。这种方式的主要优点是，当我们按主键顺序访问数据时，性能会非常高，因为可以利用主键的有序性二分查找。然而，这种方式的主要缺点是，当我们插入新的数据时，如果新的主键值不是在当前最大主键值的后面，那么数据库可能需要移动一部分已有的数据来为新数据腾出空间。

    因此，我们要尽可能保证主键生成的值是有序的，从而避免上述的问题。

  3. 存在唯一性

    毫无疑问，主键必须是唯一的。

除了我的项目旧版数据库中采用的两种主键，使用32位(实际上是36位)的UUID作为主键是另一种方式。然而，它既不满足“占用空间小”这个要素，也不满足“有序生成”这个要素，并不理想。

但是在使用MySQL的UUID_TO_BIN函数优化UUID以后，它就成为了一种理想的主键设计方案。这个函数对UUID字符串进行了以下处理：

  • 通过参数将时间高位放在最前，解决了 UUID 插入时乱序问题；

  • 去掉了无用的字符串 “-“，精简存储空间；

  • 将字符串其转换为二进制值存储，空间最终从之前的 36 个字节缩短为了 16 字节。

因此，可以将主键设计为BINARY(16)类型：

1
2
3
4
5
CREATE TABLE my_table (
  id BINARY(16) PRIMARY KEY,
  other_column VARCHAR(100),
  ...
);

在插入行时，使用UUID_TO_BIN()函数生成新的UUID值，并设置第二个参数为1，以改善行的物理存储顺序。

1
INSERT INTO my_table (id, other_column) VALUES (UUID_TO_BIN(UUID(), 1), 'other value');

这种主键的设计方案是我目前已知比较优秀的。

2.用户表设计优化

我决定将应聘者、企业、管理员三种用户的基本信息统一存放在一个表单中，并分别使用额外的表单存放不同用户类型特有的字段。在公共的表单中，采用一个枚举类型的字段来标记用户属于哪种类型。

这种设计方案的优点在于，避免了用户表单中相同含义字段的冗余出现，让数据库的设计更加统一。缺点在于，很多用户基本信息的查询可能需要联表进行。在后续MyBatis的使用中，也需要进行额外的配置来装配数据。当然，这是MyBatis的灵活性完全可以做到的。

3.对数据库其它细节进行调整

  • 创建city关系表存储城市，以及城市-用户关系表、城市-岗位关系表，来处理城市这个要素。

  • 删除岗位-企业关系表，而是在岗位表中添加外键存储发布它的用户id

  • 消息-用户关系表调整主键设计

    主键中添加岗位id，成为消息-用户-岗位关系表，逻辑上合理了。

设计结果

下面是经过调整后的数据库模型：

其中蓝色的是实体表，红色的是关系表。

值得一提的是，上面的模型是在Navicat中设计的，不仅可以详细设计表单的字段，主键外键，索引等，而且可以自动生成.sql文件，直接创建设计的数据库。这让我得以摆脱在抽象的符号化环境里设计数据库的状况，摆脱语法的约束，专注于对数据库本身的思考。以后我会关注这个工具。

参考资料

MySQL 那些常见的错误设计规范