MySQL中主键与rowid的使用陷阱总结
前言
大家在MySQL中我们可能听到过rowid的概念，但是却很难去测试实践，不可避免会有一些疑惑，比如：
- 如何感受到rowid的存在；
- rowid和主键有什么关联关系；
- 在主键的使用中存在哪些隐患；
- 如何来理解rowid的潜在瓶颈并调试验证。
本文要和大家一起讨论这几个问题，测试的环境基于MySQL5.7.19版本。
问题1、如何感受到rowid的存在
我们不妨通过一个案例来进行说明。
记得有一天统计备份数据的时候，写了一条SQL，当看到执行结果时才发现SQL语句没有写完整，在完成统计工作之后，我准备分析下这条SQL语句。
mysql>selectbackup_date,count(*)piece_nofromredis_backup_result; +-------------+----------+ |backup_date|piece_no| +-------------+----------+ |2018-08-14|40906| +-------------+----------+ 1rowinset(0.03sec)
根据业务特点，一天之内肯定没有这么多的记录，明显不对，到底是哪里出了问题呢。
自己仔细看了下SQL，发现是没有加groupby，我们随机查出10条数据。
mysql>selectbackup_datefromredis_backup_resultlimit10; +-------------+ |backup_date| +-------------+ |2018-08-14| |2018-08-14| |2018-08-14| |2018-08-15| |2018-08-15| |2018-08-15| |2018-08-15| |2018-08-15| |2018-08-15| |2018-08-15| +-------------+ 10rowsinset(0.00sec)
在早期的版本中数据库参数sql_mode默认为空，不会校验这个部分，从语法角度来说，是允许的；但是到了高版本，比如5.7版本之后是不支持的，所以解决方案很简单，在添加groupby之后，结果就符合预期了。
mysql>selectbackup_date,count(*)piece_nofromredis_backup_resultgroupbybackup_date; +-------------+----------+ |backup_date|piece_no| +-------------+----------+ |2018-08-14|3| |2018-08-15|121| |2018-08-16|184| |2018-08-17|3284| |2018-08-18|7272| |2018-08-19|7272| |2018-08-20|7272| |2018-08-21|7272| |2018-08-22|8226| +-------------+----------+ 9rowsinset(0.06sec)
但是比较好奇这个解析的逻辑，看起来是SQL解析了第一行，然后输出了count(*)的操作，显然这是从执行计划中无法得到的信息。
我们换个思路，可以看到这个表有4万多条的记录。
mysql>selectcount(*)fromredis_backup_result; +----------+ |count(*)| +----------+ |40944| +----------+ 1rowinset(0.01sec)
为了验证，我们可以使用_rowid的方式来做初步的验证。
InnoDB表中在没有默认主键的情况下会生成一个6字节空间的自动增长主键，可以用select_rowidfromtable来查询，如下：
mysql>select_rowidfromredis_backup_resultlimit5; +--------+ |_rowid| +--------+ |117| |118| |119| |120| |121| +--------+ 5rowsinset(0.00sec)
再可以实现一个初步的思路。
mysql>select_rowid,count(*)fromredis_backup_result; +--------+----------+ |_rowid|count(*)| +--------+----------+ |117|41036| +--------+----------+ 1rowinset(0.03sec)
然后继续升华一些，借助rownum来实现，当然在MySQL中原生不支持这个特性，需要间接实现。
mysql>SELECT@rowno:=@rowno+1asrowno,r._rowidfromredis_backup_result r,(select@rowno:=0)tlimit20; +-------+--------+ |rowno|_rowid| +-------+--------+ |1|117| |2|118| |3|119| |4|120| |5|121| |6|122| |7|123| |8|124| |9|125| |10|126| |11|127| |12|128| |13|129| |14|130| |15|131| |16|132| |17|133| |18|134| |19|135| |20|136| +-------+--------+ 20rowsinset(0.00sec)
写一个完整的语句，如下：
mysql>SELECT@rowno:=@rowno+1asrowno,r._rowid,backup_date,count(*) fromredis_backup_resultr,(select@rowno:=0)t; +-------+--------+-------------+----------+ |rowno|_rowid|backup_date|count(*)| +-------+--------+-------------+----------+ |1|117|2018-08-14|41061| +-------+--------+-------------+----------+ 1rowinset(0.02sec)
通过这个案例，可以很明显发现是第1行的记录，然后做了count(*)的操作。
当然我们的目标是要掌握rowid和主键的一些关联关系，所以我们也复盘一下主键使用中的隐患问题。
问题2、rowid和主键有什么关联关系
在学习MySQL开发规范之索引规范的时候，强调过一个要点：每张表都建议有主键。我们在这里来简单分析一下为什么？
除了规范，从存储方式上来说，在InnoDB存储引擎中，表都是按照主键的顺序进行存放的，我们叫做聚簇索引表或者索引组织表（IOT），表中主键的参考依据如下：
- 显式的创建主键Primarykey。
- 判断表中是否有非空唯一索引，如果有，则为主键。
- 如果都不符合上述条件，则会生成6个字节的bigintunsigned值。
从以上可以看到，MySQL对于主键有一套维护机制，而一些常见的索引也会产生相应的影响，比如唯一性索引、非唯一性索引、覆盖索引等都是辅助索引（secondaryindex，也叫二级索引），从存储的角度来说，二级索引列中默认包含主键列，如果主键太长，也会使得二级索引很占空间。
问题3、在主键的使用中存在哪些隐患
这就引出行业里非常普遍的主键性能问题，这不是一个单一的问题，需要MySQL方向持续改造的，将技术价值和业务价值结合起来。我看到很多业务中设置了自增列，但是大多数情况下，这种自增列却没有实际的业务含义，尽管是主键列保证了ID的唯一性，但是业务开发无法直接根据主键自增列来进行查询，于是他们需要寻找新的业务属性，添加一系列的唯一性索引，非唯一性索引等等，这样一来我们坚持的规范和业务使用的方式就存在了偏差。
从另外一个维度来说，我们对于主键的理解是有偏差的，我们不能单一的认为主键就一定是从1开始的整数类型，我们需要结合业务场景来看待，比如我们的身份证其实就是一个不错的例子，把证号分成了几个区段，偏于检索和维护；或者是外出就餐时得到的流水单号，它都有一定的业务属性在里面，对于我们去理解业务的使用是一种不错的借鉴。
问题4、如何来理解rowid的潜在瓶颈并进行调试验证
我们知道rowid只有6个字节，因此最大值是2^48,所以一旦row_id超过这个值还是会递增，这种情况下是否存在隐患。
光说不练假把式，我们可以做一个测试来说明。
1）我们创建一张表test_inc，不包含任何索引。
createtabletest_inc(idint)engine=innodb;
2）通过ps-ef|grepmysql得到对应的进程号，使用gdb来开始做下调试配置，切记！此处应该是自己的测试环境。
[root@dev01mysql]#gdb-p3132-ex'pdict_sys->row_id=1'-batch [NewLWP3192] [NewLWP3160] [NewLWP3159] [NewLWP3158] [NewLWP3157] [NewLWP3156] [NewLWP3155] [NewLWP3154] [NewLWP3153] [NewLWP3152] [NewLWP3151] [NewLWP3150] [NewLWP3149] [NewLWP3148] [NewLWP3147] [NewLWP3144] [NewLWP3143] [NewLWP3142] [NewLWP3141] [NewLWP3140] [NewLWP3139] [NewLWP3138] [NewLWP3137] [NewLWP3136] [NewLWP3135] [NewLWP3134] [NewLWP3133] [Threaddebuggingusinglibthread_dbenabled] 0x00000031ed8df283inpoll()from/lib64/libc.so.6 $1=1
3）我们做下基本检验，得到建表语句，保证测试是预期的样子。
mysql>showcreatetabletest_inc\G ***************************1.row*************************** Table:test_inc CreateTable:CREATETABLE`test_inc`( `id`int(11)DEFAULTNULL )ENGINE=InnoDBDEFAULTCHARSET=utf8 1rowinset(0.00sec)
4）插入一些数据，使得rowid持续自增。
mysql>insertintotest_incvalues(1),(2),(3); QueryOK,3rowsaffected(0.08sec) Records:3Duplicates:0Warnings:0
5）我们对rowid进行重置，调整为2^48
mysql>selectpower(2,48); +-----------------+ |power(2,48)| +-----------------+ |281474976710656| +-----------------+ 1rowinset(0.00sec) [root@dev01mysql]#gdb-p3132-ex'pdict_sys->row_id=281474976710656'-batch 。。。 。。。 [Threaddebuggingusinglibthread_dbenabled] 0x00000031ed8df283inpoll()from/lib64/libc.so.6 $1=281474976710656
6）继续写入一些数据，比如我们写入4,5,6三行数据。
mysql>insertintotest_incvalues(4),(5),(6); QueryOK,3rowsaffected(0.07sec) Records:3Duplicates:0Warnings:0
7）查看数据结果，发现1,2两行已经被覆盖了。
mysql>select*fromtest_inc; +------+ |id| +------+ |4| |5| |6| |3| +------+ 4rowsinset(0.00sec)
由此，我们可以看到rowid自增后，还是存在使用瓶颈，当然这个概率是很低的，需要自增列的值到281万亿，这是一个相当庞大的数值了，从功能上来说，应该抛出写入重复值的错误更为合理。
而有了主键之后，上面这个瓶颈似乎就不存在了。
>>>>参考资料
rowid调试参考了丁奇的博客
https://www.nhooo.com/article/172262.htm
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。