MySQL INSERT ... ON DUPLICATE KEY UPDATE ...对自增主键的影响

MySQL INSERT … ON DUPLICATE KEY UPDATE …对自增主键的影响

背景

生产告警，数据库自增主键(int无符号)增长很快，溢出程序中Integer类型的最大值。

org.springframework.dao.DataIntegrityViolationException:
Error attempting to get column 'match_id' from result set.  Cause:
com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: '2.395907755E9' in column
'1' is outside valid range for the datatype INTEGER.

数据库的主键ID为什么增长这么快？已经达到将近24亿。由此排查代码中可能存在的问题。

排查过程

发现程序中在插入该表时采用的INSERT … ON DUPLICATE KEY UPDATE …的方式，我们推测是否是由这种插入更新方式引起的。

这种方式在向数据库插入一条记录时，若该数据的主键值/UNIQUE KEY在表中存在时，则执行更新操作，否则插入一条新的记录。

MySQL官方文档对该语法解释

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. 

如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2；如果原有的记录存在且更新前后值一样，则受影响的行数为0。

验证过程

MySQL版本：5.7.26

Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper

事务隔离级别：读提交

mysql> mysql> SHOW VARIABLES LIKE "transaction_isolation";
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

自增主键的锁模式：1

mysql> SHOW VARIABLES LIKE "innodb_autoinc_lock_mode";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

建表语句

mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(10) unsigned NOT NULL,
  `f` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

唯一索引u

测试步骤

1. 先用insert…on duplicate key插入一条数据，此时主键id为1

   mysql> insert into t2 (u,f) values (1,100) on duplicate key update f=values(f);
   Query OK, 1 row affected (0.04 sec)
   
   mysql> select * from t2;
   +----+---+-----+
   | id | u | f   |
   +----+---+-----+
   |  1 | 1 | 100 |
   +----+---+-----+
   1 row in set (0.00 sec)
   

2. 再用insert…on duplicate key更新这条数据

   mysql> insert into t2 (u,f) values (1,101) on duplicate key update f=values(f);
   Query OK, 2 rows affected (0.06 sec)
   
   mysql> select * from t2;
   +----+---+-----+
   | id | u | f   |
   +----+---+-----+
   |  1 | 1 | 101 |
   +----+---+-----+
   1 row in set (0.00 sec)
   

3. 再插入另外一条数据，发现此时第2条数据的id变为3，而不是2

   mysql> insert into t2 (u,f) values (2,200) on duplicate key update f=values(f);
   Query OK, 1 row affected (0.04 sec)
   
   mysql> select * from t2;
   +----+---+-----+
   | id | u | f   |
   +----+---+-----+
   |  1 | 1 | 101 |
   |  3 | 2 | 200 |
   +----+---+-----+
   2 rows in set (0.00 sec)
   

4. 同理再持续更新第1条数据，更新3次

   mysql> insert into t2 (u,f) values (1,102) on duplicate key update f=values(f);
   Query OK, 2 rows affected (0.03 sec)
   
   mysql> insert into t2 (u,f) values (1,103) on duplicate key update f=values(f);
   Query OK, 2 rows affected (0.03 sec)
   
   mysql> insert into t2 (u,f) values (1,104) on duplicate key update f=values(f);
   Query OK, 2 rows affected (0.04 sec)
   
   mysql> select * from t2;
   +----+---+-----+
   | id | u | f   |
   +----+---+-----+
   |  1 | 1 | 104 |
   |  3 | 2 | 200 |
   +----+---+-----+
   2 rows in set (0.00 sec)
   

5. 再插入第3条数据，发现此时id为7

   mysql> insert into t2 (u,f) values (3,300) on duplicate key update f=values(f);
   Query OK, 1 row affected (0.15 sec)
   
   mysql> select * from t2;
   +----+---+-----+
   | id | u | f   |
   +----+---+-----+
   |  1 | 1 | 104 |
   |  3 | 2 | 200 |
   |  7 | 3 | 300 |
   +----+---+-----+
   3 rows in set (0.00 sec)
   

结论

由上面测试可以发现，INSERT … ON DUPLICATE KEY UPDATE这种方式在更新数据时，也将auto_increment的值加1，这样持续更新并插入时就会导致数据库自增主键id不连续，并且增长很快。

MySQL中有个参数innodb_autoinc_lock_mode，有3种模式,0,1,2，数据库默认是1的情况下，就会发生上面的那种现象,每次使用INSERT … ON DUPLICATE KEY UPDATE的时候都会把简单自增id增加,不管是发生了insert还是update。当做简单插入（可以确定插入行数）的时候，直接将auto_increment加1，而不会去锁表，这也就提高了性能。

模式0的话不管什么情况都是加上表锁，等语句执行完成的时候再释放，将auto_increment加1。

模式2，什么情况都不加AUTO_INC锁，存在安全问题，当binlog格式设置为Statement模式的时候，从库同步的时候，执行结果可能跟主库不一致，问题很大。因为可能有一个复杂插入，还在执行呢，另外一个插入就来了，恢复的时候是一条条来执行的，就不能重现这种并发问题，导致记录id可能对不上。

解决方案

1. 修改字段类型为BigInteger（临时方案）。
2. 改业务逻辑，将INSERT … ON DUPLICATE KEY UPDATE语句拆开，先查询，后更新。
3. 不使用自增主键，让唯一索引来做主键，只要确定目前的自增主键没有实际的用处，但插入删除的时候可能会影响效率。
4. 将innodb_autoinc_lock_mode设置为0，但这样的话，插入的并发性可能会受很大影响（影响多大待测试）。

参考资料

1. https://segmentfault.com/a/1190000017268633
2. https://blog.csdn.net/eleanoryss/article/details/82997899
3. https://www.cnblogs.com/zhanzhijie/p/10916424.html