MySQL中主键与rowid的使用陷阱有哪些

共计 5774 个字符，预计需要花费 15 分钟才能阅读完成。

这篇文章主要为大家展示了“MySQL 中主键与 rowid 的使用陷阱有哪些”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让丸趣 TV 小编带领大家一起研究并学习一下“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)

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

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)

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

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

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

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

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)

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

mysql  SELECT @rowno:=@rowno+1 as rowno,r._rowid from redis_backup_result
r ,(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)

通过这个案例，可以很明显发现是第 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，不包含任何索引。

create table test_inc(id int) engine=innodb;

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

[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

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

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

4）插入一些数据，使得 rowid 持续自增。

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

5）我们对 rowid 进行重置，调整为 2^48

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

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

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

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

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

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

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

以上是“MySQL 中主键与 rowid 的使用陷阱有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！