记一次auto_increment引发的悲剧

实战

先来看看架构图吧

1. x表将数据写入A，这时候A的id是自增长的
2. A再将自己的自增长的id（发号器），写入到 B, 注意是指定id写入。insert into B(id) values(xx);
3. 同时也将数据写入到C

最终的想法是这样的

1. 主表A将发号器id写入C (id 在一个小区间增长: 1 ~ 100000)
2. 主表B将发号器id也写入C (id 在一个大区间增长: 10000000 ~ ? )
3. 很多人疑惑的是：为什么一开始id不区分开来呢？ 因为一开始业务没有拆分，需要保证A,B一致。直到某个阶段，才将B设置alter table B auto_increment = 10000000
4. 这个时候，由于指定id写入的原因，A，B的id还是一致的，直到X表分发数据到不同表。如：xx写入A，yy写入B，这个时候由于A，B的auto_increment不一样，所以id就会有所区分(A的id自增长在小的区间，B的id自增长在大的区间)。

错误场景重现

那么问题就来了，如果这时候B的auto_incrememt再次变回到小区间1 ~ 100000，会导致什么问题呢？
问题严重啦： 这时候由于A，B 都往C表写同一个区间的数据，会导致很多脏数据，结果就悲剧了咯。。。
真实场景重现如下

分析

问题的关键处在auto_increment变小

那么什么场景下回导致auto_increment变小呢？

1. 参考： http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html
2. alter table xx auto_increment = yy;
3. truncate table
4. restart mysql

* If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

* A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, which you can use with InnoDB tables to set the initial counter value or alter the current counter value.

解决方案

1. 手动插入一条数据到B，让其最大值在10000000+1, 这样就不会出问题。

第二种奇葩问题

1. 一张刚创建的innoDB表，目前自增是1.
2. 插入3条记录后，auto_increment=4.
3. 然后再删除掉这三条记录,auto_increment=4 没变
4. 关闭MySQL，当MySQL再次起来的时候，会发现auto_increment值从4，变成1

总结

业务不要依赖auto_increment值，它并不是总是递增