前车之鉴：MySQL 设计规范中的一些常见错误示例

terry 2年前 (2023-09-26) 阅读数 42 #数据库

为了避免给后面学习的人造成误解，今天我们来看看 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 字符串 23ebaa88-ce89-11eb-b431-0242ac110002 通过 UUID_TO_BIN 函数进行转换，得到二进制值如下：

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 8.0 还提供了 BIN_TO_UUID 函数，支持将二进制值转换为 UUID 字符串。

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

当然，很多同学也关心UUID效率和内存占用。我这里也进行了相关的插入性能测试，结果如下表所示：

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

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

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

金融领域设计

不正确的设计规范：金融相关金额数据必须使用十进制类型因为float和double都是非精确浮点类型，而decimal是精度浮点类型a逗号。点型。因此，规划用户余额和商品价格等财务领域通常使用可以精确到分钟的小数类型。

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

以下是bigint类型的优点：

十进制是用二进制实现的编码方式，计算效率不如bigint
如果使用bigint，field是一个固定长度的字段，存储效率高，而decimal则基于定义的宽度。设计数据时就决定定长存储有更好的性能
使用bigint来存储单位分割的量，但也可以存储GB级的量，完全够用了

使用编号字段

不正确设计规范：避免使用ENUM类型

在之前的开发项目中，当我们遇到用户性别、产品是否上架、评论是否隐藏等字段时，我们简单地将字段格式化为tinyint 然后添加到字段注意：0 是州，1 是国家。

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

表达不清楚：这张桌子可能是其他同事设计的。如果你不是特别热心的话，每次都得看字段注释，甚至有时候在编码的时候你还得去数据库确认字段的含义
脏数据：虽然插入的值可以受应用层代码限制，仍然可以使用SQL和可视化工具更改值

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

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

而且我们一般在定义编号值时使用“Y”、“N”等单个字符，占用空间不大。但如果选项的值不固定，并且会随着公司的发展而增加，则不建议使用编号字段。

限制索引数量

不正确的设计规范：限制数量每个表上的索引数量一个表中的索引不得超过 5

MySQL 对每个表上的索引数量没有限制。业务如果查询有特殊需求，只需创建一个即可。不要迷信数量限制

使用子查询

不正确的设计规范：避免使用子查询

其实这个规范是针对老版本的MySQL的。是的，因为之前版本的MySQL数据库对子查询的优化有限，所以在很多OLTP业务场景中我们要求web公司尽可能的使用子查询。

不过，在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 AND 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的执行计划，发现是一样的