MySQL中的主键和rowid,看似简单,其实有一些使用陷阱需要注意

数据库 MySQL
大家在MySQL中我们可能听到过rowid的概念,但是却很难去测试实践,不可避免会有一些疑惑,今天要和大家一起讨论这几个问题,测试的环境基于MySQL 5.7.19版本。

MySQL中的主键和rowid,看似简单,其实有一些使用陷阱需要注意

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

1)如何感受到rowid的存在

2)rowid和主键有什么关联关系

3)在主键的使用中存在哪些隐患

4)如何来理解rowid的潜在瓶颈并调试验证

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

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

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

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

 

  1. mysql> select backup_date ,count(*) piece_no from redis_backup_result;  
  2. +-------------+----------+  
  3. | backup_date | piece_no |  
  4. +-------------+----------+  
  5. | 2018-08-14 | 40906 |  
  6. +-------------+----------+  
  7. 1 row in set (0.03 sec) 

 

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

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

 

  1. mysql> select backup_date from redis_backup_result limit 10;  
  2. +-------------+  
  3. | backup_date |  
  4. +-------------+  
  5. | 2018-08-14 |  
  6. | 2018-08-14 |  
  7. | 2018-08-14 |  
  8. | 2018-08-15 |  
  9. | 2018-08-15 |  
  10. | 2018-08-15 |  
  11. | 2018-08-15 |  
  12. | 2018-08-15 |  
  13. | 2018-08-15 |  
  14. | 2018-08-15 |  
  15. +-------------+  
  16. 10 rows in set (0.00 sec) 

 

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

 

  1. mysql> select backup_date ,count(*) piece_no from redis_backup_result group by backup_date;  
  2. +-------------+----------+  
  3. | backup_date | piece_no |  
  4. +-------------+----------+  
  5. | 2018-08-14 | 3 |  
  6. | 2018-08-15 | 121 |  
  7. | 2018-08-16 | 184 |  
  8. | 2018-08-17 | 3284 |  
  9. | 2018-08-18 | 7272 |  
  10. | 2018-08-19 | 7272 |  
  11. | 2018-08-20 | 7272 |  
  12. | 2018-08-21 | 7272 |  
  13. | 2018-08-22 | 8226 |  
  14. +-------------+----------+  
  15. rows in set (0.06 sec) 

 

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

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

 

  1. mysql> select count(*)from redis_backup_result;  
  2. +----------+  
  3. count(*) |  
  4. +----------+  
  5. | 40944 |  
  6. +----------+  
  7. 1 row in set (0.01 sec) 

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

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

 

  1. mysql> select _rowid from redis_backup_result limit 5;  
  2. +--------+  
  3. | _rowid |  
  4. +--------+  
  5. | 117 |  
  6. | 118 |  
  7. | 119 |  
  8. | 120 |  
  9. | 121 |  
  10. +--------+  
  11. rows in set (0.00 sec) 

 

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

 

  1. mysql> select _rowid,count(*)from redis_backup_result;  
  2. +--------+----------+  
  3. | _rowid | count(*) |  
  4. +--------+----------+  
  5. | 117 | 41036 |  
  6. +--------+----------+  
  7. 1 row in set (0.03 sec) 

 

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

 

  1. mysql> SELECT @rowno:=@rowno+1 as rowno,r._rowid from redis_backup_resultr ,(select @rowno:=0) t limit 20;  
  2. +-------+--------+  
  3. | rowno | _rowid |  
  4. +-------+--------+  
  5. | 1 | 117 |  
  6. | 2 | 118 |  
  7. | 3 | 119 |  
  8. | 4 | 120 |  
  9. | 5 | 121 |  
  10. | 6 | 122 |  
  11. | 7 | 123 |  
  12. | 8 | 124 |  
  13. | 9 | 125 |  
  14. | 10 | 126 |  
  15. | 11 | 127 |  
  16. | 12 | 128 |  
  17. | 13 | 129 |  
  18. | 14 | 130 |  
  19. | 15 | 131 |  
  20. | 16 | 132 |  
  21. | 17 | 133 |  
  22. | 18 | 134 |  
  23. | 19 | 135 |  
  24. | 20 | 136 |  
  25. +-------+--------+  
  26. 20 rows in set (0.00 sec) 

 

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

 

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

 

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

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

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

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

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

(1)显式的创建主键Primary key。

(2)判断表中是否有非空唯一索引,如果有,则为主键。

(3)如果都不符合上述条件,则会生成UUID的一个隐式主键(6字节大)。

从以上可以看到,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来开始做下调试配置切记!此处应该是自己的测试环境。

 

  1. [root@dev01 mysql]# gdb -p 3132 -ex 'p dict_sys->row_id=1' -batch  
  2. [New LWP 3192]  
  3. [New LWP 3160]  
  4. [New LWP 3159]  
  5. [New LWP 3158]  
  6. [New LWP 3157]  
  7. [New LWP 3156]  
  8. [New LWP 3155]  
  9. [New LWP 3154]  
  10. [New LWP 3153]  
  11. [New LWP 3152]  
  12. [New LWP 3151]  
  13. [New LWP 3150]  
  14. [New LWP 3149]  
  15. [New LWP 3148]  
  16. [New LWP 3147]  
  17. [New LWP 3144]  
  18. [New LWP 3143]  
  19. [New LWP 3142]  
  20. [New LWP 3141]  
  21. [New LWP 3140]  
  22. [New LWP 3139]  
  23. [New LWP 3138]  
  24. [New LWP 3137]  
  25. [New LWP 3136]  
  26. [New LWP 3135]  
  27. [New LWP 3134]  
  28. [New LWP 3133]  
  29. [Thread debugging using libthread_db enabled]  
  30. 0x00000031ed8df283 in poll from /lib64/libc.so.6  
  31. $1 = 1 

 

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

 

  1. mysql> show create table test_inc\G  
  2. *************************** 1. row ***************************  
  3. Table: test_inc  
  4. Create TableCREATE TABLE `test_inc` (  
  5. `id` int(11) DEFAULT  
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  7. 1 row in set (0.00 sec) 

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

  1. mysql> select *from test_inc;  
  2. +------+ 
  3. | id |  
  4. +------+  
  5. | 4 |  
  6. | 5 |  
  7. | 6 |  
  8. | 3 |  
  9. +------+  
  10. rows in set (0.00 sec) 

 

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

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

 

责任编辑:庞桂玉 来源: 杨建荣的学习笔记
相关推荐

2022-09-29 23:24:37

工具调试字段

2012-10-25 14:14:14

云计算架构师峰会

2015-04-10 13:27:44

微软Azure莱森米

2016-12-16 14:57:19

2017-10-16 14:40:50

数据库MySQL工具

2021-10-15 10:04:37

云计算安全云服务

2012-12-19 11:36:03

路由器MP

2020-12-02 18:57:00

Wi-FiMiMo无线

2017-01-03 15:23:56

Android动态加载SO库

2011-01-07 09:36:22

NullMySQL

2012-04-25 22:45:46

2016-12-26 18:51:34

AndroidJavascriptJSONObject

2009-07-21 09:29:27

iBATIS使用

2019-06-23 16:02:12

Kubernetes集群节点高并发

2010-08-31 14:33:46

DB2游标

2009-06-30 10:10:15

Namespace用法Flex

2012-07-04 14:40:37

Ajax

2023-07-27 18:39:20

低代码开发编码

2014-12-23 13:50:46

多播组播

2010-02-25 10:04:33

MySQL执行SQL语
点赞
收藏

51CTO技术栈公众号