乐趣区

关于前端:MySQL-那些常见的错误设计规范

jiezi

依靠于互联网的发达，咱们能够随时随地利用一些等车或坐地铁的碎片工夫学习以及理解资讯。同时发达的互联网也不便人们可能疾速分享本人的常识，与雷同喜好和需要的敌人们一起独特探讨。

然而过于不便的分享也让常识变得形形色色，很容易让人接管到谬误的信息。这些谬误最多的都是因为技术倒退迅速，而且没有闲暇工夫去及时更新曾经公布的内容所导致。为了防止给前面学习的人造成误会，咱们明天来看一看 MySQL 设计规范中几个常见的谬误例子。

主键的设计

谬误的设计规范： 主键倡议应用自增 ID 值，不要应用 UUID，MD5，HASH，字符串作为主键

这个设计规范在很多文章中都能看到，自增主键的长处有占用空间小，有序，应用起来简略等长处。

上面先来看看自增主键的毛病：

  • 自增值因为在服务器端产生，须要有一把自增的 AI 锁爱护，若这时有大量的插入申请，就可能存在自增引起的性能瓶颈，所以存在并发性能问题；
  • 自增值做主键，只能在以后实例中保障惟一， 不能保障全局惟一，这就导致无奈在分布式架构中应用；
  • 公开数据值，容易引发平安问题，如果咱们的商品 ID 是自增主键的话，用户能够通过批改 ID 值来获取商品，重大的状况下能够晓得咱们数据库中一共存了多少商品。
  • MGR（MySQL Group Replication）可能引起的性能问题；

因为自增值是在 MySQL 服务端产生的值，须要有一把自增的 AI 锁爱护，若这时有大量的插入申请，就可能存在自增引起的性能瓶颈。比方在 MySQL 数据库中，参数 innodb_autoinc_lock_mode 用于管制自增锁持有的工夫。尽管，咱们能够调整参数 innodb_autoinc_lock_mode 取得自增的最大性能，然而因为其还存在其它问题。因而， 在并发场景中，更举荐 UUID 做主键或业务自定义生成主键。

咱们能够间接在 MySQ L 应用 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 提供的排序 UUID 性能最好，甚至比自增 ID 还要好。此外，因为 UUID_TO_BIN 转换为的后果是 16 字节，仅比自增 ID 减少 8 个字节，最初存储占用的空间也仅比自增大了 3G。

而且因为 UUID 能保障全局惟一，因而应用 UUID 的收益远远大于自增 ID。可能你曾经习惯了用自增做主键，然而在并发场景下，更举荐 UUID 这样的全局惟一值做主键。

当然了，UUID 虽好，然而在分布式场景下，主键还须要退出一些额定的信息，这样能力保障后续二级索引的查问效率，举荐依据业务自定义生成主键。然而在并发量和数据量没那么大的状况下，还是举荐应用自增 UUID 的。大家更不要认为 UUID 不能当主键了。

金融字段的设计

谬误的设计规范： 同财务相干的金额类数据必须应用 decimal 类型 因为 float 和 double 都是非精准的浮点数类型，而 decimal 是精准的浮点数类型。所以个别在设计用户余额，商品价格等金融类字段个别都是应用 decimal 类型，能够准确到分。

然而在海量互联网业务的设计标准中，并不举荐用 DECIMAL 类型，而是更举荐将 DECIMAL 转化为整型类型。 也就是说，金融类型更举荐应用用分单位存储，而不是用元单位存储。如 1 元在数据库中用整型类型 100 存储。

上面是 bigint 类型的长处：

  • decimal 是通过二进制实现的一种编码方式，计算效率不如 bigint
  • 应用 bigint 的话，字段是定长字段，存储高效，而 decimal 依据定义的宽度决定，在数据设计中，定长存储性能更好
  • 应用 bigint 存储分为单位的金额，也能够存储千兆级别的金额，齐全够用

枚举字段的应用

谬误的设计规范： 防止应用 ENUM 类型

在以前开发我的项目中，遇到用户性别，商品是否上架，评论是否暗藏等字段的时候，都是简略的将字段设计为 tinyint，而后在字段里备注 0 为什么状态，1 为什么状态。

这样设计的问题也比拟显著：

  • 表白不清：这个表可能是其余共事设计的，你印象不是特地深的话，每次都须要去看字段正文，甚至有时候在编码的时候须要去数据库确认字段含意
  • 脏数据：尽管在应用层能够通过代码限度插入的数值，然而还是能够通过 sql 和可视化工具批改值

这种固定选项值的字段，举荐应用 ENUM 枚举字符串类型，外加 SQL_MODE 的严格模式

在 MySQL 8.0.16 当前的版本，能够间接应用 check 束缚机制，不须要应用 enum 枚举字段类型

而且咱们个别在定义枚举值的时候应用 ”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 查问文章表的用户有哪些。

如果用 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 也能实现上述需要，但不容易了解。

咱们应用 explain 查看两条 sql 的执行打算，发现都是一样的

通过上图能够很显著看到，不论是子查问还是 LEFT JOIN，最终都被转换成了 left hash Join，所以上述两条 SQL 的执行工夫是一样的。即，在 MySQL 8.0 中，优化器会主动地将 IN 子查问优化，优化为最佳的 JOIN 执行打算，这样一来，会显著的晋升性能。

总结

浏览完后面的内容置信大家对 MySQL 曾经有了新的认知，这些常见的谬误能够总结为以下几点：

  • UUID 也能够当主键，自增 UUID 比自增主键性能更好，多占用的空间也可忽略不计
  • 金融字段除了 decimal，也能够试试 bigint，存储分为单位的数据
  • 对于固定选项值的字段，MySQL8 以前举荐应用枚举字段，MySQL8 当前应用 check 函数束缚，不要应用 0，1，2 示意
  • 一张表的索引个数并没有限度不能超过 5 个，能够依据业务状况增加和删除
  • MySQL8 对子查问有了优化，能够放心使用。

举荐浏览

实操笔记：为 NSQ 配置监控服务的心路历程

go-zero：开箱即用的微服务框架

退出移动版