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

前言

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

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

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

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

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

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

mysql> select backup_date ,count(*) piece_no from redis_backup_result;+-------------+----------+| backup_date | piece_no |+-------------+----------+| 2018-08-14 | 40906 |+-------------+----------+1 row in set (0.03 sec)

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

mysql> select backup_date from redis_backup_result limit 10;+-------------+| 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 |+-------------+10 rows in set (0.00 sec)

mysql> select backup_date ,count(*) piece_no from redis_backup_result group by backup_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 |+-------------+----------+9 rows in set (0.06 sec)

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

mysql> select count(*)from redis_backup_result;+----------+| count(*) |+----------+| 40944 |+----------+1 row in set (0.01 sec)

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

mysql> select _rowid from redis_backup_result limit 5;+--------+| _rowid |+--------+| 117 || 118 || 119 || 120 || 121 |+--------+5 rows in set (0.00 sec)

mysql> select _rowid,count(*)from redis_backup_result;+--------+----------+| _rowid | count(*) |+--------+----------+| 117 | 41036 |+--------+----------+1 row in set (0.03 sec)

mysql> SELECT @rowno:=@rowno+1 as rowno,r._rowid from redis_backup_resultr ,(select @rowno:=0) t limit 20;+-------+--------+| 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 |+-------+--------+20 rows in set (0.00 sec)

mysql> SELECT @rowno:=@rowno+1 as rowno,r._rowid ,backup_date,count(*)from redis_backup_result r ,(select @rowno:=0) t ;+-------+--------+-------------+----------+| rowno | _rowid | backup_date | count(*) |+-------+--------+-------------+----------+| 1 | 117 | 2018-08-14 | 41061 |+-------+--------+-------------+----------+1 row in set (0.02 sec)

当然我们的目标是要掌握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，不包含任何索引。

create table test_inc(id int) engine=innodb;

[root@dev01 mysql]# gdb -p 3132 -ex 'p dict_sys->row_id=1' -batch[New LWP 3192][New LWP 3160][New LWP 3159][New LWP 3158][New LWP 3157][New LWP 3156][New LWP 3155][New LWP 3154][New LWP 3153][New LWP 3152][New LWP 3151][New LWP 3150][New LWP 3149][New LWP 3148][New LWP 3147][New LWP 3144][New LWP 3143][New LWP 3142][New LWP 3141][New LWP 3140][New LWP 3139][New LWP 3138][New LWP 3137][New LWP 3136][New LWP 3135][New LWP 3134][New LWP 3133][Thread debugging using libthread_db enabled]0x00000031ed8df283 in poll () from /lib64/libc.so.6$1 = 1

mysql> show create table test_inc\G*************************** 1. row ***************************  Table: test_incCreate Table: CREATE TABLE `test_inc` ( `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

mysql> insert into test_inc values(1),(2),(3);Query OK, 3 rows affected (0.08 sec)Records: 3 Duplicates: 0 Warnings: 0

mysql> select power(2,48);+-----------------+| power(2,48)  |+-----------------+| 281474976710656 |+-----------------+1 row in set (0.00 sec) [root@dev01 mysql]# gdb -p 3132 -ex 'p dict_sys->row_id=281474976710656' -batch。。。。。。[Thread debugging using libthread_db enabled]0x00000031ed8df283 in poll () from /lib64/libc.so.6$1 = 281474976710656

mysql> insert into test_inc values(4),(5),(6); Query OK, 3 rows affected (0.07 sec)Records: 3 Duplicates: 0 Warnings: 0

mysql> select *from test_inc;+------+| id |+------+| 4 || 5 || 6 || 3 |+------+4 rows in set (0.00 sec)

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

>>>> 参考资料

rowid调试参考了丁奇的博客

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

总结

更多相关文章

随机推荐