Replace into 带来的主从自增主键差异

环境

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into t (age, msg) values (1,'aaa'),(2,'bbb'),(3,'ccc');
select * from t;
+----+------+------+
| id | age  | msg  |
+----+------+------+
|  1 |    1 | aaa  |
|  2 |    2 | bbb  |
|  3 |    3 | ccc  |
+----+------+------+
3 rows in set (0.01 sec)

Master-Slave(Master)

下面我们在主库进行replace操作

katyusha@127.0.0.1:3306:test 16:51:31>replace into t (age, msg) values (1, '111');
Query OK, 2 rows affected (0.01 sec)
【这里影响2条记录，即delete+insert】
katyusha@127.0.0.1:3306:test 16:51:41>select * from t;
+----+------+------+
| id | age  | msg  |
+----+------+------+
|  2 |    2 | bbb  |
|  3 |    3 | ccc  |
|  4 |    1 | 111  |
+----+------+------+
3 rows in set (0.00 sec)
【自增id=4】
katyusha@127.0.0.1:3306:test 16:51:44>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
【AUTO_INCREMENT+1=5】

我们看到replace有2行被影响，id=4，insert操作做了一次auto_increment+1

歪个楼，replace操作对于已经存在的数据进行update操作，而update实际是delete+insert两个连续操作，binlog产生一条update语句，这里binlog_format=ROW

# at 10806
#160722 16:51:41 server id 202  end_log_pos 10868 CRC32 0x6ecd5fa9      Update_rows: table id 125 flags: STMT_END_F
BINLOG '
nd6RVxPKAAAAMAAAADYqAAAAAH0AAAAAAAEABHRlc3QAAXQAAwMDDwIeAAY0H6oy
nd6RVx/KAAAAPgAAAHQqAAAAAH0AAAAAAAEAAgAD///4AQAAAAEAAAADYWFh+AQAAAABAAAAAzEx
MalfzW4=
'/*!*/;
### UPDATE `test`.`t`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3='aaa' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3='111' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
【binlog为update语句】

来看看从库

katyusha@127.0.0.1:3306:test 16:51:57>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
【AUTO_INCREMENT=4，即未增加】

我们看到replace有2行被影响，id=4，而表没有做auto_increment+1，因为对于从库来说，只做了一次update操作，影响1行

问题1: DB出现切换,新主写入冲突

新数据写进来，报错了，主键冲突

katyusha@127.0.0.1:3306:test 16:34:10>insert into t (age, msg) values (4, 'ddd');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
【这里虽然报主键冲突，但是AUTO_INCREMENT+1】
(PS:在正常的insert语句出现主键冲突的时候，AUTO_INCREMENT值不变)

主键冲突就不说啥了，刚才id没有auto_increment+1，这是必然。但是，insert操作却出发了一次auto_increment+1，为什么？

二次insert，成功

katyusha@127.0.0.1:3306:test 16:34:56>insert into t (age, msg) values (4, 'ddd');
Query OK, 1 row affected (0.00 sec)
katyusha@127.0.0.1:3306:test 16:37:03>select * from t;
+----+------+------+
| id | age  | msg  |
+----+------+------+
|  2 |    2 | bbb  |
|  3 |    3 | ccc  |
|  4 |    1 | 111  |
|  5 |    4 | ddd  |
+----+------+------+
4 rows in set (0.00 sec)

那么，通过问题3的方法，可以解决主从id一致的问题，对于操作频繁的DB，只要replace后面有insert操作，即可追平，所以如果业务是读写分离的话，应该没啥影响。不过，这是侥幸心理，如果正当replace后出现宕机切换，那么一样会在切换后有二次主键冲突的情况发生。安全的做法是重做主从

问题2: 重启Slave后,自增值追平

从库重启

1
2
3

[root@n-op-210 ~]# mysql_server -P 3306 restart
Shutting down MySQL (Percona Server)... SUCCESS!
Starting MySQL (Percona Server)... SUCCESS!

从库状态

katyusha@127.0.0.1:3306:test 16:52:34>select * from t;
+----+------+------+
| id | age  | msg  |
+----+------+------+
|  1 |    1 | aaa  |
|  2 |    2 | bbb  |
|  3 |    3 | ccc  |
+----+------+------+
3 rows in set (0.00 sec)
katyusha@127.0.0.1:3306:test 16:52:41>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

我们可以看到，start slave，写入同步正常，mysql会记录auto_increment偏移量，这里是步长是1

# at 4
#160722 16:56:07 server id 210  end_log_pos 120 CRC32 0x71861b3a 	Start: binlog v 4, server v 5.6.27-76.0-log created 160722 16:56:07 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
p9+RVw/SAAAAdAAAAHgAAAABAAQANS42LjI3LTc2LjAtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACn35FXEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAATob
hnE=
'/*!*/;
# at 120
#160722 17:05:29 server id 202  end_log_pos 183 CRC32 0xec8db911 	Query	thread_id=31	exec_time=0	error_code=0
SET TIMESTAMP=1469178329/*!*/;
SET @@session.pseudo_thread_id=31/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=524288/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 183
#160722 17:05:29 server id 202  end_log_pos 231 CRC32 0xcff7f2d6 	Table_map: `test`.`t` mapped to number 70
# at 231
#160722 17:05:29 server id 202  end_log_pos 279 CRC32 0x8b9bfdd7 	Write_rows: table id 70 flags: STMT_END_F
BINLOG '
2eGRVxPKAAAAMAAAAOcAAAAAAEYAAAAAAAEABHRlc3QAAXQAAwMDDwIeAAbW8vfP
2eGRVx7KAAAAMAAAABcBAAAAAEYAAAAAAAEAAgAD//gFAAAABAAAAANkZGTX/ZuL
'/*!*/;
### INSERT INTO `test`.`t`
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
###   @3='ddd' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */

可是，这是由同步发起的auto_increment+1，从时间轴上看，在服务器被重启的时候就已经自动完成上一次落后的追平工作，从库接到的不是一个update操作么，从库重启为什么要进行auto_increment？

答：实际上mysql是通过select max(id)+1 from t的方法来拿到auto_inrement的值

1
2
3

以下这段文字摘自淘宝内部分享:
建表时可以指定 AUTO_INCREMENT值，不指定时默认为1，这个值表示当前自增列的起始值大小，如果新插入的数据没有指定自增列的值，那么自增列的值即为这个起始值。对于InnoDB表，这个值没有持久到文件中。而是存在内存中（dict_table_struct.autoinc）。那么又问，既然这个值没有持久下来，为什么我们每次插入新的值后， show create table t1看到AUTO_INCREMENT值是跟随变化的。其实show create table t1是直接从dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。

问题3，插入新数据后，自增值自动追平

主库replace后insert

katyusha@127.0.0.1:3306:test 13:57:40>replace into t (age, msg) values (6, '666');
Query OK, 2 rows affected (0.00 sec)
【这里影响2条记录，即delete+insert】
katyusha@127.0.0.1:3306:test 14:01:16>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
【AUTO_INCREMENT=14】
katyusha@127.0.0.1:3306:test 14:01:17>insert into t (age, msg) values (8, 'hhh');
Query OK, 1 row affected (0.01 sec)
【紧跟着插入一条新记录】
katyusha@127.0.0.1:3306:test 14:01:55>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
【AUTO_INCREMENT+1=15】

从库状态

katyusha@127.0.0.1:3306:test 14:01:31>select * from t;
+----+------+------+
| id | age  | msg  |
+----+------+------+
|  4 |    1 | 111  |
|  6 |    5 | 555  |
|  8 |    2 | 222  |
|  9 |    3 | 333  |
| 10 |    4 | 444  |
| 12 |    7 | ggg  |
| 13 |    6 | 666  |
| 14 |    8 | hhh  |
+----+------+------+
8 rows in set (0.00 sec)
katyusha@127.0.0.1:3306:test 14:02:17>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
【AUTO_INCREMENT=15，与主库追平】

实际上mysql也是通过select max(id)+1 from t的方法来拿到auto_inrement的值

以上测试对insert into .. on duplicate key update语句同样效果

katyusha@127.0.0.1:3306:test 14:18:45>insert into t (age, msg) values (10, 'jjj');
Query OK, 1 row affected (0.00 sec)
katyusha@127.0.0.1:3306:test 14:20:22>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
katyusha@127.0.0.1:3306:test 14:20:24>insert into t (age, msg) values (10, '1010') on duplicate key update msg='1010';
Query OK, 2 rows affected (0.01 sec)
【这里影响2条记录，也是delete+insert】
katyusha@127.0.0.1:3306:test 14:21:04>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
【AUTO_INCREMENT+1=22】

# at 14955
#160725 14:21:04 server id 202  end_log_pos 15018 CRC32 0x31f5b457 	Update_rows: table id 126 flags: STMT_END_F
BINLOG '
0K+VVxPKAAAAMAAAAGs6AAAAAH4AAAAAAAEABHRlc3QAAXQAAwMDDwIeAAafi1gY
0K+VVx/KAAAAPwAAAKo6AAAAAH4AAAAAAAEAAgAD///4FAAAAAoAAAADampq+BQAAAAKAAAABDEw
MTBXtPUx
'/*!*/;
### UPDATE `test`.`t`
### WHERE
###   @1=20 /* INT meta=0 nullable=0 is_null=0 */
###   @2=10 /* INT meta=0 nullable=1 is_null=0 */
###   @3='jjj' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### SET
###   @1=20 /* INT meta=0 nullable=0 is_null=0 */
###   @2=10 /* INT meta=0 nullable=1 is_null=0 */
###   @3='1010' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
【binlog为update语句】

来看看从库

katyusha@127.0.0.1:3306:test 14:22:18>select * from t;
+----+------+------+
| id | age  | msg  |
+----+------+------+
|  4 |    1 | 111  |
|  6 |    5 | 555  |
|  8 |    2 | 222  |
|  9 |    3 | 333  |
| 10 |    4 | 444  |
| 13 |    6 | 666  |
| 14 |    8 | 888  |
| 15 |    7 | 777  |
| 16 |    9 | 999  |
| 20 |   10 | 1010 |
+----+------+------+
10 rows in set (0.00 sec)
katyusha@127.0.0.1:3306:test 14:22:20>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
【AUTO_INCREMENT=21，即未改变】

竟然也是做了update操作

从这些问题可以看出，MySQL的很多东西，需要我们小白去翻源码。。。

看到资讯阿里和腾讯自己内部的分支都已经修复了这个bug，并将auto_increment值落地，不知道什么时候percona可以参考修正下