mysql auto 修复_MySQL auto_increment的坑

清疚 2022-10-25 00:56 179阅读 0赞

背景：

Innodb引擎使用B\_tree结构保存表数据，这样就需要一个唯一键表示每一行记录(比如二级索引记录引用)。

Innodb表定义中处理主键的逻辑是：

1.如果表定义了主键，就使用主键唯一定位一条记录

2.如果没有定义主键，Innodb就生成一个全局唯一的rowid来定位一条记录

auto\_increment的由来:

1.Innodb强烈推荐在设计表中自定义一个主键，因为rowid是全局唯一的，所以如果有很多表没有定义主键，就会在生成rowid上产生争用。

/\* Dictionary system struct \*/

struct dict\_sys\_struct\{

mutex\_tmutex;

row\_id\_trow\_id;

......

\}

row\_id由mutex保护，并在每次checkpoint的时候，写入到数据字典的文件头。

2.当用户自定义了主键后，由于大部分实际应用部署的分布式，所以主键值的生成上，采用集中式的方式，更容易实现唯一性，所以auto\_increment非常合适。

auto\_increment也带来两个好处：

1. auto\_increment的值是表级别的，不会在db级别上产生争用

2. 由于auto\_increment的顺序性，减少了随机读的可能，保证了写入的page的缓冲命中。(不可否认，写入的并发足够大时，会产生热点块的争用)

auto\_increment引起的bug：

环境：MySQL 5.6.16版本, binlog\_format=row

case复现：

create table test.kkk ( c int(11) default null, id int(11) not null auto\_increment, d int(11) default null, primary key (id), unique key d (d) )

engine=innodb default charset=latin1;

insert into test.kkk values(5, 27,4);

replace into test.kkk(c, id, d) values(6, 35, 4);

commit;

show create table时：

主库：auto\_increment=36

备库：auto\_increment=28

当进行主备切换后，导致主键冲突，slave恢复异常。

同样insert on duplication update 语句同样存在这样的问题。

aliyun rds分支bug修复

问题的原因：Innodb对于auto\_increment的处理，当语句是insert时，会进行递增，而update，delete语句则不更新。

当replace语句在主库的执行时：

1. 先按照insert语句执行，发现uk冲突。

2. 演变成update语句进行更新。

这样在主库，虽然insert失败，但auto\_increment也递增上去了。但到备库，row格式下，只产生了一个update row event，

备库无法知道主库是一个replace语句，而且insert还失败了， 所以auto\_increment在备库没有递增。

修复方式：在备库，对于update进行auto\_increment递增，可能会产生副作用，即auto\_increment的浪费，但不会产生主键冲突。

那些年经历的auto\_increment坑：

1. 实例重启，主键冲突：

内存中的autoinc值，在系统重启后，使用select max(id) from table来初始化。所以，如果你设计的业务表，存在delete操作，那么一旦你的实例crash过，重启后，可能会复用以前使用过的id值。如果你需要持续对这个表进行逻辑备份，那么就可能会碰到主键冲突的问题。

2. load file阻塞：

在设置innodb\_autoinc\_lock\_mode=1的时候，MySQL为了维护单个statement语句的id连续性，当不确定插入条数的时候，会在语句整个执行过程中

持有LOCK\_AUTO\_INC, /\* locks the auto-inc counter of a table in an exclusive mode \*/

这个锁是表级别的，使用互斥模式。

所以，在繁忙的表上，如果要导入数据，小心可能阻塞正常的业务写入，并发写入在这个时候也会阻塞的。

发表评论取消回复

表情：

评论列表（有 0 条评论，179人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL登录的bug修复

在输入mysql -u root -p登录MySQL时，报错：mysql: Character set 'utf8</pre>' is not a compiled chara

た入场券/ 2023年01月13日 10:13/ 0 赞/ 22 阅读

相关 mysql修复库_mysql数据库恢复

1.数据库恢复基本事项 MySQL命令以及source命令恢复数据库的原理就是在数据库里重新执行文件的SQL语句的过程。数据恢复和字符集的关联很大，如果字符集不正确则会导致恢

矫情吗；*/ 2023年01月11日 03:37/ 0 赞/ 182 阅读

相关 mysql获取autoincrement_MySQL获取Auto_increment字段刚插入的值 | 学步园

MySQL获取Auto\_increment字段刚插入的值不能使用select max(id) from testnotnull; 这样来获取刚插入的那个递增字段的值，

港控/mmm°/ 2022年10月25日 05:48/ 0 赞/ 183 阅读

相关 mysql auto 修复_MySQL auto_increment的坑

背景： Innodb引擎使用B\_tree结构保存表数据，这样就需要一个唯一键表示每一行记录(比如二级索引记录引用)。 Innodb表定义中处理主键的逻辑是： 1.如果表

清疚/ 2022年10月25日 00:56/ 0 赞/ 180 阅读

相关对MYSQL AUTO_INCREMENT 的初始化

<table style="table-layout: fixed;"> <tbody> <tr> <td> <div> <p>MyIS

àì夳堔傛蜴生んèń/ 2022年08月07日 01:52/ 0 赞/ 130 阅读

相关 mysql的auto_incremnet操作

在堆mysql数据库创建表时，如果我们把某个属性设置为auto\_increment，也就是自动增长，我们在不要某行数数据时，当然是要删除该数据了，但是，有一个问题，摆在我们面

谁借莪１个温暖的怀抱￠/ 2022年06月04日 08:24/ 0 赞/ 158 阅读

相关 mysql修复过程

1基础知识 1.1默认安装路径 1）windows 安装目录：C:\\ProgramFiles\\MySQL\\MySQL Server x.x 数据目录：在C:/D

深碍√TFBOYSˉ_/ 2022年05月20日 04:43/ 0 赞/ 185 阅读

相关 MYSQL AUTO_INCREMENT常识

参考：[https://blog.csdn.net/iamczb/article/details/43112689][https_blog.csdn.net_iamczb_ar

傷城~/ 2022年04月17日 02:10/ 0 赞/ 184 阅读

相关 mysql auto_increment_increment和auto_increment_offset

关于 mysql自增列的有2个参数： auto\_increment\_increment auto\_increment\_offset auto\_increm

逃离我推掉我的手/ 2022年04月01日 16:58/ 0 赞/ 253 阅读

相关 MySQL Auto-Failover 功能测试

[为什么80%的码农都做不了架构师？>>> ][80_] ![hot3.png][] 参考资料： http://www.clusterdb.com/mysql/replic

╰半橙微兮°/ 2022年01月16日 23:21/ 0 赞/ 203 阅读