我们今天给大家分享MySQL 设计规范中几个常见的误区&＃xff0c;希望以后的数据库设计可以规避掉。
1、主键的设计
误区&＃xff1a;主键建议使用自增 ID 值&＃xff0c;尽量不要使用 UUID&＃xff0c;MD5&＃xff0c;HASH&＃xff0c;字符串作为数据库主键
自增主键的优点&＃xff1a;占用空间小&＃xff0c;有序&＃xff0c;使用起来简单。
自增主键的不足&＃xff1a;
自增值由于在服务器端产生&＃xff0c;需要有一把自增的 锁保护&＃xff0c;如果业务有大量的插入请求&＃xff0c;就可能引起的性能瓶颈。
自增值做数据库主键&＃xff0c;只能在当前数据库实例中保证唯一&＃xff0c;不能保证全局唯一&＃xff0c;无法在分布式架构中使用。
存在数据安全&＃xff0c;如果我们的商品 ID 是采用自增主键的话&＃xff0c;用户可以通过修改 ID 值来获取商品&＃xff0c;甚至可以知道我们数据库中一共存了多少个商品。
MGR&＃xff08;MySQL Group Replication&＃xff09; 可能引起的性能问题&＃xff1b;
MySQL使用 UUID() 函数来获取 UUID 的值。
MySQL> select UUID();
&＃43;--------------------------------------&＃43;
| UUID() |
&＃43;--------------------------------------&＃43;
| 15ebaa88-ce00-11eb-b431-123ac110002 |
&＃43;--------------------------------------&＃43;
1 row in set (0.00 sec)
需要特别注意的是&＃xff0c;在存储时间类型时&＃xff0c;UUID 是根据时间位逆序存储&＃xff0c; 也就是低时间低位存放在最前面&＃xff0c;高时间位在最后&＃xff0c;即 UUID 的前 4 个字节会随着时间的变化而不断“随机”变化&＃xff0c;并非单调递增。而非随机值在插入时会产生离散 IO&＃xff0c;从而产生性能瓶颈。这也是 UUID 对比自增值最大的不足之处。
为了解决这个问题&＃xff0c;MySQL 8.0 推出了函数 UUID_TO_BIN&＃xff0c;它可以把 UUID 字符串&＃xff1a;
通过参数将时间高位放在最前&＃xff0c;解决了 UUID 插入时乱序问题&＃xff1b;
去掉了无用的字符串"-"&＃xff0c;精简存储空间&＃xff1b;
将字符串其转换为二进制值存储&＃xff0c;空间最终从之前的 36 个字节缩短为了 16 字节。
下面我们将之前的 UUID 字符串 23ebaa88-ce89-11eb-b123-123ac110002 通过函数 UUID_TO_BIN 进行转换&＃xff0c;得到二进制值如下所示&＃xff1a;
MySQL> SELECT UUID_TO_BIN(&＃39;23ebaa88-ce89-11eb-b123-123ac110002&＃39;,TRUE) as UUID_BIN;
&＃43;------------------------------------&＃43;
| UUID_BIN |
&＃43;------------------------------------&＃43;
| 0x11EBCE8923EBAA88B4310242AC110002 |
&＃43;------------------------------------&＃43;
1 row in set (0.01 sec)
MySQL 8.0 也提供了函数 BIN_TO_UUID&＃xff0c;可以支持将二进制值转化为 UUID 字符串。
MySQL 8.0 之前的版可以通过自定义函数的方式解决。业务层的话可以根据相应的编程语言编写相应的函数。
而且由于 UUID 能保证全局唯一&＃xff0c;因此使用 UUID 的好处远远大于自增 ID。可能你已经习惯了用自增做主键&＃xff0c;但是在并发场景下&＃xff0c;更推荐 UUID 这样的全局唯一值做主键。
但是在分布式场景下&＃xff0c;主键还需要加入一些额外的信息&＃xff0c;这样才能保证后续二级索引的查询效率&＃xff0c;建议根据业务自定义生成主键。但是在并发量和数据量没那么大的情况下&＃xff0c;还是建议使用自增 UUID 的。
2、子查询的使用
错误的设计规范&＃xff1a;避免使用子查询
其实这个规范对老MySQL 8.0之前的版本来说是对的&＃xff0c;因为之前版本的 MySQL 数据库对子查询优化存在很多不足&＃xff0c;所以很多 OLTP 业务场合下&＃xff0c;很多企业都要求在线业务避免使用子查询。
MySQL 8.0 版本中&＃xff0c;子查询的优化得到大幅提升&＃xff0c;所以在新版本的MySQL中可以放心的使用子查询。
采用子查询相比 JOIN 更易于阅读习惯。
3、枚举字段的使用
误区&＃xff1a;数据库字段设计避免使用 ENUM 类型
在以前开发项目中&＃xff0c;遇到用户性别&＃xff0c;业务状态&＃xff0c;星期等字段的时候&＃xff0c;经常会简单的将字段设计为 tinyint&＃xff0c;然后在字段注释里面说明 。
存在问题&＃xff1a;
固定选项值的字段&＃xff0c;推荐使用 ENUM 枚举字符串类型&＃xff0c;再加上SQL_MODE 的严格模式
在MySQL 8.0.16 以后的版本&＃xff0c;可以直接使用check约束机制&＃xff0c;可以不使用enum枚举字段类型
而且我们一般在定义枚举值的时候使用"Y","N"等单个字符&＃xff0c;并不会占用很多空间。针对选项值不固定的情况&＃xff0c;随着业务发展可能会增加&＃xff0c;才不推荐使用枚举字段。
3、索引个数限制
误区&＃xff1a;限制每张表上的索引数量&＃xff0c;索引不能超过 5 个
MySQL 单表的索引没有个数限制&＃xff0c;要根据业务查询有具体需要&＃xff0c;创建即可&＃xff0c;不要太在意索引个数的限制。
4、金融字段的设计规范
误区&＃xff1a;同财务相关的金额类数据必须使用 decimal 类型
由于 float 和 double 都是非精准的浮点数类型&＃xff0c;而 decimal 是精准的浮点数类型。所以一般在设计用户余额&＃xff0c;商品价格等金融类字段一般都是使用 decimal 类型&＃xff0c;可以精确到分。
但是在海量数据互联网业务的设计规范中&＃xff0c;不推荐用 DECIMAL 数据类型&＃xff0c;而是推荐将 DECIMAL 转化为整型数据类型。 金融类型更适合使用用分单位存储&＃xff0c;而不是用元单位存储。比如5元在数据库中用整型类型 500 存储。
bigint 类型的优点&＃xff1a;
decimal 是采用二进制实现的一种编码方式&＃xff0c;计算效率不如 bigint
使用 bigint 的话&＃xff0c;字段是定长字段&＃xff0c;存储高效&＃xff0c;而 decimal 根据定义的宽度决定&＃xff0c;在数据设计中&＃xff0c;定长存储性能更好
使用 bigint 存储分为单位的金额&＃xff0c;也可以存储千兆级别的金额数据&＃xff0c;完全够用。
总结
UUID 也可以当主键&＃xff0c;自增 UUID 比自增主键性能更好&＃xff0c;多占用的空间也可忽略不计
金融字段除了 decimal&＃xff0c;也可以试试 bigint&＃xff0c;存储分为单位的数据
对于固定选项值的字段&＃xff0c;MySQL8 以前推荐使用枚举字段&＃xff0c;MySQL8 以后使用check函数约束&＃xff0c;不要使用 0&＃xff0c;1&＃xff0c;2 表示
一张表的索引个数并没有限制不能超过5个&＃xff0c;可以根据业务情况
MySQL8 对子查询有了优化&＃xff0c;可以放心使用。
IT技术分享社区
个人博客网站&＃xff1a;https://programmerblog.xyz
文章推荐程序员效率&＃xff1a;画流程图常用的工具程序员效率&＃xff1a;整理常用的在线笔记软件远程办公&＃xff1a;常用的远程协助软件&＃xff0c;你都知道吗&＃xff1f;51单片机程序下载、ISP及串口基础知识硬件&＃xff1a;断路器、接触器、继电器基础知识
京公网安备 11010802041100号