数据库设计规范：MySQL 中常见错误的一些示例

让我们看一下 MySQL 设计规范中一些常见错误的示例。

主键设计

错误的设计规范：建议主键使用自动递增的ID值。不要使用UUID、MD5、HASH、字符串作为主键

这个设计规范在很多文章中都可以看到，因为添加主键的优点是占用空间小、清晰、易用。

我们看一下自增主键的缺点：

• 由于自增值是在服务器端生成的，所以必须有自增AI锁来保护。如果此时有大量插入请求，可能会出现性能自动提升的问题，从而出现并发性能问题；
• 使用自增值作为主键，只能保证当前实例的唯一性，无法保证全局唯一性，无法在分布式架构中使用；
• 公开的数据值很容易造成安全问题。如果我们的产品ID是自增主键，用户可以通过编辑ID值来检索产品。在严重的情况下，他们可以知道我们的数据库中存储了多少产品。
• MGR（MySQL Group Replication）可能引起的性能问题；

由于自增值是在MySQL服务器上生成的，因此必须使用自增AI锁进行保护。如果此时有大量的插入请求，可能会出现自增带来的性能瓶颈。例如，在MySQL数据库中，innodb_autoinc_lock_mode参数用于控制自动增量锁定的时间。虽然我们可以调整 innodb_autoinc_lock_mode 参数来获得最大的自动增量性能，但还存在其他问题。因此，在并发场景下，更建议使用UUID作为主键或者生成企业定义的主键。

我们可以直接在MySQL中使用UUID()函数来获取UUID值。

MySQL> select UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 23ebaa88-ce89-11eb-b431-0242ac110002 |
+--------------------------------------+
1 row in set (0.00 sec)

需要注意的是，存储时间时，UUID按照时间位的相反顺序存储，即最低时间位存储在前面，最高时间位存储在后面结尾。 ，即 UUID 的前 4 个字节随时间“随机”变化，并且不会单调增加。非随机值在插入时会产生离散IO，造成性能瓶颈。这也是UUID相比自增值最大的缺点。

为了解决这个问题，MySQL 8.0引入了UUID_TO_BIN函数，可以插入UUID字符串：

• 将高位时间位放在参数中，解决插入UUID时乱序的问题；
• 删除不必要的“-”字符串，减少存储空间；
• 将字符串转换为存储二进制值，空间最终从之前的36字节减少到16字节。

下面，我们通过 UUID_TO_BIN 函数将之前的 UUID 字符串 23ebaa88-ce89-11eb-b431-0242ac110002 进行转换，得到二进制值如下：

MySQL> SELECT UUID_TO_BIN('23ebaa88-ce89-11eb-b431-0242ac110002',TRUE) as UUID_BIN;
+------------------------------------+
| UUID_BIN                           |
+------------------------------------+
| 0x11EBCE8923EBAA88B4310242AC110002 |
+------------------------------------+
1 row in set (0.01 sec)

另外还提供了 MySQL_ID Convert BIN0 函数。二进制值转换为 UUID 字符串。

虽然MySQL 8.0之前没有UUID_TO_BIN/BIN_TO_UUID函数，但是仍然可以通过自定义函数来解决。对于应用层，可以根据自己的编程语言编写相应的功能。

当然，很多同学也关心UUID的性能以及它在存储中占用的空间。我这里也做了相关的性能测试。结果如下表所示：

，MySQL 8.0提供的UUID排序具有最好的性能，甚至比自增ID还要好。另外，由于UUID_TO_BIN转换结果为16字节，因此只比自增ID多了8字节，最终占用的存储空间也只比自增多了3G。

而且由于UUID可以保证全局唯一性，因此使用UUID的好处比自增ID大得多。您可能习惯使用自增作为主键，但在并发场景下，建议使用全局唯一值（例如UUID）作为主键。

当然，UUID虽然好，但是在分布式场景下，需要在主键上添加一些额外的信息，以保证后续二级索引的高效查询。建议根据业务自定义主键。但如果并发量和数据量不是很大，建议使用UUID自动递增。不要以为UUID不能作为主键。

金融领域设计

糟糕的设计规范：金融金额数据必须使用十进制类型因为float和double是非精确浮点类型，而decimal是精确浮点类型。因此，在设计用户余额、商品价格等金融领域时，一般都会使用能够精确到分钟的小数类型。

但海量互联网服务的设计标准中并不推荐使用DECIMAL类型。建议将 DECIMAL 转换为整数类型。换句话说，金融类型更喜欢存储在子单元而不是元单元中。例如数据库中存储1元，整数类型为100。

bigint类型的优点如下：

• 十进制是在二进制中实现的编码方式，计算效率不如和bigint一样好
• 如果使用bigint，字段是定长数组，存储效率高，而decimal是基于定义的宽度决定定长存储在数据设计上有更好的性能
• 使用bigint来存储单位数量，可以存储千兆字节的数量，完全够用了

使用枚举字段

错误的设计规范：不要使用ENUM类型

在以前的开发项目中，遇到用户性别、产品是否是等字段时上架、评论是否隐藏等，我们简单地将数组设计为tinyint，然后注意数组中的0.why状态，1why状态。

这个设计的问题也比较明显：

• 表达不清楚：这个桌子可以由其他同事设计。如果你不是太印象深刻，你总是要阅读字段注释，甚至有时在编码时你必须去数据库确认字段的含义
• 脏数据：虽然可以限制插入的值通过应用层代码，仍然可以使用SQL和可视化工具修改值

对于固定选项值的字段，建议使用类型枚举字符串ENUM加严格模式SQL_MODE

版本中MySQL 8.0.16之后可以直接使用约束检查机制，不需要使用类型枚举字段

而且我们在定义枚举值时一般使用“Y”、“N”这样的单个字符不占用很大的空间。但如果选项的值不固定，可以随着业务的发展而增加，则不建议使用枚举字段。

限制索引数量

错误的设计规范：限制每个表上的索引数量。一张表的索引不能超过5个

MySQL对一张表的索引数量没有限制，业务查询有具体需要的话，创建一个即可，不要迷信数量限制

使用子查询

不好的设计规范：不要使用子查询

其实这个规范对于老版本的MySQL来说是正确的，因为之前版本的MySQL数据库对子查询的优化有限，所以在很多OLTP业务场景下，我们要求线上业务尽可能使用子查询。

不过在MySQL 8.0中，子查询的优化有了很大的提升，所以大家可以在新版本的MySQL中放心的使用子查询了。

子查询比 JOIN 更容易让人理解。比如现在我们要查询2020年没有发表文章的学生人数

SELECT COUNT(*)
FROM user
WHERE id not in (
    SELECT user_id
    from blog
    where publish_time >= "2020-01-01" AND  publish_time <= "2020-12-31"
)

可以看到，子查询的逻辑非常清晰：通过not IN What来查询articles表的users。

如果用LEFT JOIN来写

SELECT count(*)
FROM user LEFT JOIN blog
ON user.id = blog.user_id and blog.publish_time >= "2020-01-01" and blog.publish_time <= "2020-12-31"
where blog.user_id is NULL;

，你会发现LEFT JOIN虽然也能满足上面的要求，但是不太好理解。

我们通过解释查看了两条SQL的执行计划，发现是一样的

从上图可以明显看出，无论是子查询还是LEFT JOIN，最终都会被转换到左散列Join，因此上面两条SQL的执行时间是相同的。这意味着在MySQL 8.0中，优化器自动将IN子查询优化为最佳的JOIN执行计划，从而大大提高了性能。

总结

看完前面的内容，相信大家对MySQL都有了新的认识。这些常见错误可以总结为以下几点：

• UUID 也可以用作主键。自增UUID比自增要好。主键的性能比较好，占用的额外空间可以忽略不计，以后会使用控制特性限制。不要用0、1、2来表示
• 表中索引的数量不限制超过5个，可以根据业务情况增删
• MySQL8对子查询进行了优化，可以使用充满信心。