mysql innodb auto_increment_MySQL innodb主键及auto_increment的坑

背景：

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 */

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

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

转载来源地址：http://mysql.taobao.org/monthly/2014/09/06/