• 售前

  • 售后

热门帖子
入门百科

MySQL 那些常见的错误设计规范,你都知道吗

[复制链接]
米老鼠和蓝精鼠v 显示全部楼层 发表于 2021-8-14 14:46:26 |阅读模式 打印 上一主题 下一主题
依托于互联网的发达,我们可以随时随地利用一些等车或坐地铁的碎片时间学习以及了解资讯。同时发达的互联网也方便人们可以或许快速分享自己的知识,与雷同爱好和需求的朋侪们一起共同讨论。
但是过于方便的分享也让知识变得五花八门,很容易让人吸收到错误的信息。这些错误最多的都是由于技能发展迅速,而且没有空闲时间去及时更新已经发布的内容所导致。为了制止给后面学习的人造成误解,我们今天来看一看 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 的值。
  1. MySQL> select UUID();
  2. +--------------------------------------+
  3. | UUID()                               |
  4. +--------------------------------------+
  5. | 23ebaa88-ce89-11eb-b431-0242ac110002 |
  6. +--------------------------------------+
  7. 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 举行转换,得到二进制值如下所示:
  1. MySQL> SELECT UUID_TO_BIN('23ebaa88-ce89-11eb-b431-0242ac110002',TRUE) as UUID_BIN;
  2. +------------------------------------+
  3. | UUID_BIN                           |
  4. +------------------------------------+
  5. | 0x11EBCE8923EBAA88B4310242AC110002 |
  6. +------------------------------------+
  7. 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年没有发过文章的同砚的数量
  1. SELECT COUNT(*)
  2. FROM user
  3. WHERE id not in (
  4.     SELECT user_id
  5.     from blog
  6.     where publish_time >= "2020-01-01" AND  publish_time <= "2020-12-31"
  7. )
复制代码
可以看到,子查询的逻辑非常清楚:通过 not IN 查询文章表的用户有哪些。
如果用 left join 写
  1. SELECT count(*)
  2. FROM user LEFT JOIN blog
  3. ON user.id = blog.user_id and blog.publish_time >= "2020-01-01" and blog.publish_time <= "2020-12-31"
  4. 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 对子查询有了优化,可以放心利用。
到此这篇关于MySQL 那些常见的错误计划规范的文章就介绍到这了,更多相干MySQL 错误计划规范内容请搜索草根技术分享从前的文章或继承浏览下面的相干文章希望大家以后多多支持草根技术分享!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作