数据库：MySQL常见的设计规范误区

分手后的思念是犯贱 2022-08-28 03:50 165阅读 0赞

![84724e3e322db940fc4f7b082f7b5929.png][]

我们今天给大家分享MySQL 设计规范中几个常见的误区，希望以后的数据库设计可以规避掉。

# 1、主键的设计 #

**误区：主键建议使用自增 ID 值，尽量不要使用 UUID，MD5，HASH，字符串作为数据库主键**

自增主键的优点：占用空间小，有序，使用起来简单。

自增主键的不足：

*  自增值由于在服务器端产生，需要有一把自增的 锁保护，如果业务有大量的插入请求，就可能引起的性能瓶颈。
 *  自增值做数据库主键，只能在当前数据库实例中保证唯一，不能保证全局唯一，无法在分布式架构中使用。
 *  存在数据安全，如果我们的商品 ID 是采用自增主键的话，用户可以通过修改 ID 值来获取商品，甚至可以知道我们数据库中一共存了多少个商品。
 *  MGR（MySQL Group Replication） 可能引起的性能问题；

MySQL使用 UUID() 函数来获取 UUID 的值。

MySQL> select UUID();
    +--------------------------------------+
    | UUID()                               |
    +--------------------------------------+
    | 15ebaa88-ce00-11eb-b431-123ac110002 |
    +--------------------------------------+
    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-b123-123ac110002 通过函数 UUID\_TO\_BIN 进行转换，得到二进制值如下所示：

MySQL> SELECT UUID_TO_BIN('23ebaa88-ce89-11eb-b123-123ac110002',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 能保证全局唯一，因此使用 UUID 的好处远远大于自增 ID。可能你已经习惯了用自增做主键，但是在并发场景下，更推荐 UUID 这样的全局唯一值做主键。

但是在分布式场景下，主键还需要加入一些额外的信息，这样才能保证后续二级索引的查询效率，建议根据业务自定义生成主键。但是在并发量和数据量没那么大的情况下，还是建议使用自增 UUID 的。

# 2、子查询的使用 #

**错误的设计规范：避免使用子查询**

其实这个规范对老MySQL 8.0之前的版本来说是对的，因为之前版本的 MySQL 数据库对子查询优化存在很多不足，所以很多 OLTP 业务场合下，很多企业都要求在线业务避免使用子查询。

MySQL 8.0 版本中，子查询的优化得到大幅提升，所以在新版本的MySQL中可以放心的使用子查询。

采用子查询相比 JOIN 更易于阅读习惯。

# 3、枚举字段的使用 #

**误区：数据库字段设计避免使用 ENUM 类型**

在以前开发项目中，遇到用户性别，业务状态，星期等字段的时候，经常会简单的将字段设计为 tinyint，然后在字段注释里面说明 。

存在问题：

*  表达不清：数据表可能是其他同事设计的，你不可能每个都记得住，每次都可能需要去看字段注释，甚至有时候在编码的时候需要去数据库确认字段的具体含义
 *  脏数据：虽然在应用层可以通过代码限制插入的数值，但是还是可以通过sql和可视化工具修改值

固定选项值的字段，推荐使用 ENUM 枚举字符串类型，再加上SQL\_MODE 的严格模式

在MySQL 8.0.16 以后的版本，可以直接使用check约束机制，可以不使用enum枚举字段类型

而且我们一般在定义枚举值的时候使用"Y","N"等单个字符，并不会占用很多空间。针对选项值不固定的情况，随着业务发展可能会增加，才不推荐使用枚举字段。

# 3、索引个数限制 #

**误区：限制每张表上的索引数量，索引不能超过 5 个**

MySQL 单表的索引没有个数限制，要根据业务查询有具体需要，创建即可，不要太在意索引个数的限制。

# 4、金融字段的设计规范 #

**误区：同财务相关的金额类数据必须使用 decimal 类型**

由于 float 和 double 都是非精准的浮点数类型，而 decimal 是精准的浮点数类型。所以一般在设计用户余额，商品价格等金融类字段一般都是使用 decimal 类型，可以精确到分。

但是在海量数据互联网业务的设计规范中，不推荐用 DECIMAL 数据类型，而是推荐将 DECIMAL 转化为整型数据类型。 金融类型更适合使用用分单位存储，而不是用元单位存储。比如5元在数据库中用整型类型 500 存储。

bigint 类型的优点：

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

# 总结 #

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

IT技术分享社区

个人博客网站：https://programmerblog.xyz

![b5d699857851ccce3c633cc8836cf458.png][]

文章推荐[程序员效率：画流程图常用的工具][Link 1][程序员效率：整理常用的在线笔记软件][Link 2][远程办公：常用的远程协助软件，你都知道吗？][Link 3][51单片机程序下载、ISP及串口基础知识][51_ISP][硬件：断路器、接触器、继电器基础知识][Link 4]

[84724e3e322db940fc4f7b082f7b5929.png]: /images/20220828/0722553f6d674b6fb87afd2d8c925579.png
[b5d699857851ccce3c633cc8836cf458.png]: /images/20220828/73992515e4534031806aeef801e56450.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU5MjA5Njk0NQ==&mid=2247487684&idx=1&sn=008111bf1bcca6150ad6ddd48fd99711&chksm=fe25ab1ac952220cb0774f2a6da51141f901d18ca1ef6fde545376293b08dbaae45c6e671f2f&scene=21#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzU5MjA5Njk0NQ==&mid=2247487660&idx=1&sn=381729ac34ab1e36a2be82f84bb5e164&chksm=fe25ab72c952226479a3a6545a858f2918be750031b0f4bfbb6a2f308f1f736258528f57e625&scene=21#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzU5MjA5Njk0NQ==&mid=2247487655&idx=1&sn=40adeb081d70c7d656dc9dd793a32ed0&chksm=fe25ab79c952226f3f517ebac72a84c789bf4d1fdd352c0219e5f7c161cd2fd07de0994e1ec8&scene=21#wechat_redirect
[51_ISP]: http://mp.weixin.qq.com/s?__biz=MzU5MjA5Njk0NQ==&mid=2247487635&idx=1&sn=f19e9834a0a76efd52aa650c14c01ced&chksm=fe25ab4dc952225bd7976f2f77ef4162779b8a57bb7358c239357fddb1303c286b2f12d8c51f&scene=21#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzU5MjA5Njk0NQ==&mid=2247487633&idx=1&sn=c402ef612600d618281b5c9b4ca200c4&chksm=fe25ab4fc9522259bd0af7cf83eeb85ddc49e27be6db77fce74f8bb25cc7ba2b227e75bc0463&scene=21#wechat_redirect