首页 / Mysql / MySQL中主键与rowid的使用陷阱总结

MySQL中主键与rowid的使用陷阱总结

前言 大家在MySQL中我们可能听到过rowid的概念,但是却很难去测试实践,不可避免会有一些疑惑,比如:

前言

大家在MySQL中我们可能听到过rowid的概念,但是却很难去测试实践,不可避免会有一些疑惑,比如:

  • 如何感受到rowid的存在;
  • rowid和主键有什么关联关系;
  • 在主键的使用中存在哪些隐患;
  • 如何来理解rowid的潜在瓶颈并调试验证。

本文要和大家一起讨论这几个问题,测试的环境基于MySQL 5.7.19版本。

问题1、如何感受到rowid的存在

我们不妨通过一个案例来进行说明。

记得有一天统计备份数据的时候,写了一条SQL,当看到执行结果时才发现SQL语句没有写完整,在完成统计工作之后,我准备分析下这条SQL语句。

根据业务特点,一天之内肯定没有这么多的记录,明显不对,到底是哪里出了问题呢。

自己仔细看了下SQL,发现是没有加group by,我们随机查出10条数据。

在早期的版本中数据库参数sql_mode默认为空,不会校验这个部分,从语法角度来说,是允许的;但是到了高版本,比如5.7版本之后是不支持的,所以解决方案很简单,在添加group by之后,结果就符合预期了。

但是比较好奇这个解析的逻辑,看起来是SQL解析了第一行,然后输出了count(*)的操作,显然这是从执行计划中无法得到的信息。

我们换个思路,可以看到这个表有4万多条的记录。

为了验证,我们可以使用_rowid的方式来做初步的验证。

InnoDB表中在没有默认主键的情况下会生成一个6字节空间的自动增长主键,可以用select _rowid from table来查询,如下:

再可以实现一个初步的思路。

然后继续升华一些,借助rownum来实现,当然在MySQL中原生不支持这个特性,需要间接实现。

写一个完整的语句,如下:

通过这个案例,可以很明显发现是第1行的记录,然后做了count(*)的操作。

当然我们的目标是要掌握rowid和主键的一些关联关系,所以我们也复盘一下主键使用中的隐患问题。

问题2、rowid和主键有什么关联关系

在学习MySQL开发规范之索引规范的时候,强调过一个要点:每张表都建议有主键。我们在这里来简单分析一下为什么?

除了规范,从存储方式上来说,在InnoDB存储引擎中,表都是按照主键的顺序进行存放的,我们叫做聚簇索引表或者索引组织表(IOT),表中主键的参考依据如下:

  • 显式的创建主键Primary key。
  • 判断表中是否有非空唯一索引,如果有,则为主键。
  • 如果都不符合上述条件,则会生成6个字节的bigint unsigned值。

从以上可以看到,MySQL对于主键有一套维护机制,而一些常见的索引也会产生相应的影响,比如唯一性索引、非唯一性索引、覆盖索引等都是辅助索引(secondary index,也叫二级索引),从存储的角度来说,二级索引列中默认包含主键列,如果主键太长,也会使得二级索引很占空间。

问题3、在主键的使用中存在哪些隐患

这就引出行业里非常普遍的主键性能问题,这不是一个单一的问题,需要MySQL方向持续改造的,将技术价值和业务价值结合起来。我看到很多业务中设置了自增列,但是大多数情况下,这种自增列却没有实际的业务含义,尽管是主键列保证了ID的唯一性,但是业务开发无法直接根据主键自增列来进行查询,于是他们需要寻找新的业务属性,添加一系列的唯一性索引,非唯一性索引等等,这样一来我们坚持的规范和业务使用的方式就存在了偏差。

从另外一个维度来说,我们对于主键的理解是有偏差的,我们不能单一的认为主键就一定是从1开始的整数类型,我们需要结合业务场景来看待,比如我们的身份证其实就是一个不错的例子,把证号分成了几个区段,偏于检索和维护;或者是外出就餐时得到的流水单号,它都有一定的业务属性在里面,对于我们去理解业务的使用是一种不错的借鉴。

问题4、如何来理解rowid的潜在瓶颈并进行调试验证

我们知道rowid只有6个字节,因此最大值是2^48,所以一旦 row_id超过这个值还是会递增,这种情况下是否存在隐患。

光说不练假把式,我们可以做一个测试来说明。

1)我们创建一张表test_inc,不包含任何索引。

 2)通过ps -ef|grep mysql得到对应的进程号,使用gdb来开始做下调试配置,切记!此处应该是自己的测试环境。

3)我们做下基本检验,得到建表语句,保证测试是预期的样子。

4)插入一些数据,使得rowid持续自增。

5)我们对rowid进行重置,调整为2^48

6)继续写入一些数据,比如我们写入4,5,6三行数据。

7)查看数据结果,发现1,2两行已经被覆盖了。

由此,我们可以看到rowid自增后,还是存在使用瓶颈,当然这个概率是很低的,需要自增列的值到281万亿,这是一个相当庞大的数值了,从功能上来说,应该抛出写入重复值的错误更为合理。

而有了主键之后,上面这个瓶颈似乎就不存在了。

>>>> 参考资料

rowid调试参考了丁奇的博客

https://www.jb51.net/article/172262.htm

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对我是攻城狮的支持。

本文来自网络,不代表我是攻城狮立场,转载请注明出处:https://www.54gcs.cn/sql/mysql/5479.html

helwo作者

上一篇
下一篇

为您推荐

发表评论

联系我们

联系我们

在线咨询: QQ交谈

邮箱: web@5vo.cn

工作时间:周一至周五,9:00-17:30,节假日休息
关注微信
微信扫一扫关注我们

微信扫一扫关注我们

手机访问
手机扫一扫打开网站

手机扫一扫打开网站

返回顶部
跳至工具栏