IGNORE，REPLACE，ON DUPLICATE KEY UPDATE在避免重复插入记录时存在的问题及最佳实践

saintyyu

发布于 2021-11-22 09:53:09

2K1

发布于 2021-11-22 09:53:09

文章被收录于专栏：IT专栏

在实际业务场景中，经常会有这样的需求：插入一条记录，如果数据表中已经存在该条记录则更新它的部分字段，比如更新update_time或者在某些列上执行累加操作等。参考博客1中介绍了三种在MySQL中避免重复插入记录的方法，本文将在简单介绍这三种用法的基础上，深入分析这其各自存在的问题，最后给出在实际生产环境中对该业务场景的最佳实践。

为了便于后续描述，我们创建了如下的身份证记录表：

CREATE TABLE `identity_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `identity_id` varchar(30) NOT NULL COMMENT '身份证号',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_identity_id` (`identity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

该表有两个唯一键：主键和由identity_id字段构成的身份证号键唯一。创建该表时的AUTO_INCREMENT=0，表示主键id的自增起始值为0。此外，我们需要往该表中初始化一条数据：

insert into `identity_info`(`identity_id`,`name`) values('123456789012345678','张三');

此时如果执行如下查询语句：

select * from identity_info;

得到的结果如下：

为了后续比较，我们执行如下sql来查看该当前表的auto_increment值（其中的database_name需要替换为具体的数据库名）：

select auto_increment from information_schema.tables where table_schema='database_name' and table_name='identity_info';

查询结果如下：

该值为2，表示下次插入行记录的id字段值从2开始。

至此，前面描述的“数据表中已存在该条记录”的判断逻辑，在身份证记录表中的标准是指身份证号（identity_id字段的值）相同，因为我们认为用户在进行该操作时不会给某个身份证号指定分配主键（Id）值，所以以下讨论都不考虑主键（id）冲突的场景。

背景知识

为了更好地理解后续对INGORE、REPLACE和ON DUPLICATE KEY UPDATE实现机制的分析，我们需要先了解innodb_autoinc_lock_mode这参数。详细的介绍参见参考博客中的自增锁部分，本文简单介绍如下：自增锁是一种特殊的表级锁，主要用于获取事务中插入的自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以控制自增主键的生成策略，本质上就是控制auto_increment值的生成方式。通过执行如下sql可以看出当前使用的是哪种策略：

select @@innodb_autoinc_lock_mode;

本文执行结果如下：

实际上，innodb_autoinc_lock_mode值为1是数据库的默认策略。当然，除了1之外，该参数还有两种取值，分别是0和2，简单介绍如下：

当该值为0时，所有的插入语句都会获得一个特殊的表级AUTO-INC锁（即自增锁），用于插入具有AUTO_INCREMENT列的表。此锁定通常保持到语句执行结束（并非有某些博客中说的保持到事务结束），以确保为给定的INSERT语句序列以可预测和可重复的顺序分配自动递增值，并确保自动递增由任何给定语句分配的值是连续的。这意味着，当innodb_autoinc_lock_mode=0时，只有插入成功之后，auto_increment值才会递增，插入失败不会递增。

当该值为1时（默认值），对于“Simple inserts”（要插入的行数事先已知）通过在mutex（轻量锁）的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁，它只在分配过程的持续时间内保持，而不是直到语句完成。 不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。这意味着，当innodb_autoinc_lock_mode=1时，无论插入是否成功，auto_increment的值都会递增。这一点对于后面的分析非常关键。

当该值为2时，所有的插入语句都不会使用表级AUTO-INC lock，并且可以同时执行多个语句。

一、使用IGNORE关键字

1.1 使用示例

为了验证唯一键冲突，我们选择插入和数据表中已有记录完全相同的身份证号：

insert ignore into `identity_info`(`identity_id`,`name`) values('123456789012345678','李四');

在本示例中，执行该语句的结果为：

No error; 0 rows affected, taking 51 ms.

我们再查看auto_increment的值如下：

由此可知，使用ignore关键字，尽管待插入的记录因为唯一键冲突而没有插入成功，但auto_increment值却递增了。

1.2 实现机制及存在的问题（几乎没有实用场景和主从不一致的问题）

IGNORE的实现机制如下：

尝试把新行插入到表中；
如果插入成功，则返回正常的影响行数；如果报唯一键冲突（错误），则忽略该错误，返回影响行数为0 。

由此可知，在实际生产环境中，几乎不太有使用该关键字的场景，因为业务上是需要当出现唯一键冲突时更新某些字段的，而不是直接忽略。

此外，由上面的执行过程可知，我们期望插入的记录因为唯一键冲突而没有插入成功，但auto_increment字段值却递增了。因为插入语句并未执行成功，因而在binlog中并不会有执行记录。这意味着从库的auto_increment字段值不会递增，即主库和从库的auto_increment值出现了不一致。这带来的问题是，后续如果因为主库发生故障而发生主从切换，因为从库的auto_increment值落后于主库，就会导致一段时间内在原从库现主库插入的数据在原主库现从库上因为主键（id）冲突而导致插入失败。

二、使用REPLACE关键字

2.1 使用示例

和ignore示例一样，为了验证唯一键冲突，我们选择插入和数据表中已有记录完全相同的身份证号：

replace into `identity_info`(`identity_id`) values('123456789012345678');

该语句的执行结果为：

No error; 2 rows affected, taking 41 ms.

这里返回影响了2行记录，原因是replace是先删除了原有的重复记录，再插入一条新记录。

执行数据表检索得到的结果为：

对比初始化后的检索结果可知，id字段值又1变成了3，name字段由“张三”变成了默认值。同样的，auto_increment也发生了递增：

2.2 实现机制

REPLACE的运行与INSERT很相像，但当旧记录与新记录发生唯一键冲突时，会在新记录被插入之前，将旧记录被删除：

尝试把新行插入到表中；
当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时，从表中删除含有重复关键字值的（所有）冲突行；
再次尝试把新行插入到表中。

返回值：REPLACE语句会返回一个数值，用来指示受影响的行的数目。该数是被删除和被插入的行数的和。受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）。

2.3 存在的问题（数据字段丢失、主从不一致和主键消耗过快）

由其实现机制可知，对于发生唯一键（包括主键）冲突导致插入失败时，会先从表中删除原冲突行，再尝试把新行插入到表中。新插入的记录中除了指定的字段，其他字段都变成了默认值，即导致了数据字段丢失。因而如果要使用REPLACE，一定要保证sql语句中包含期望入库的所有字段。

此外，和IGNORE类似，REPLACE在主从机制下也会导致主从不一致的问题。导致主从不一致的原因由于以下两方面的原因导致：

Innodb对auto_increment的处理机制：当语句是insert时，Innodb会对auto_increment进行递增（不论是否insert成功），而对update，delete，select等语句则不更新；
当REPLACE语句在主库执行时，如果先按照insert将记录插入数据表成功，那么在主从同步的binlog日志（binlog_format=row）中，记录的就是insert row event；否则，在主库上“先执行delete后执行insert”这两步操作在binlog中会被记录成了一条update row event。

由以上原因可知，当在主库中执行REPLACE语句出现惟一键冲突时，主库虽然首先执行的insert操作是失败的，但auto_increment还是会递增；但到了备库，在row格式下，由于只产生了一条update row event，从库无法知道主库是一个replace语句，而且insert还失败了，所以auto_increment在备库上不会递增。从而同样出现主从切换后一段时间内新主库的插入操作在新的从库上因为主键（id）冲突而导致插入失败。

此外，由于REPLACE对于唯一键冲突都采用先删除再插入的方式，导致主键消耗过快且主键不连续。

三、使用INSERT...ON DUPLICATE KEY UPDATE

3.1 使用示例

类似地，为了验证唯一键冲突，我们选择插入和数据表中已有记录完全相同的身份证号：

insert into `identity_info`(`identity_id`,`name`) values('123456789012345678','王五') on duplicate key update `name` = '王五';

该语句的执行结果为：

No error; 2 rows affected, taking 52 ms.

这里发现返回的影响行数也是2，而不是预期的1。在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的话就是存在且更新前后值一样。即这里返回2只是为了区分到底是插入还是更新，而不是真正意义上的影响了两行。

执行数据表检索得到的结果为：

对比最后一次的操作结果可知，id字段值还是3，而name字段由默认值变成了“王五”。同样的，auto_increment也发生了递增：

3.2 实现机制

其实现运行步骤如下：

尝试把新行插入到表中；
当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时，则对现有的行加上S（共享）锁，然后返回该行数据给server层；
server在内存对该行执行update操作；
对该行记录加上X（排它）锁；
将update后的结果写入该行。

PS：这里为什么会先加S锁，再加X锁，其原因不得而知。姑且认为是为了提高读的并发度，但却带来了后面会分析到的死锁。

3.3 存在的问题（死锁、主从不一致、主键消耗过快和数据表存在多个唯一键时更新记录不确定）

首先，和REPLACE类似，由于是先执行insert操作，可能导致主从auto_increment不一致，并最终导致主从不一致。

其次，和REPLACE类似，也会导致主键消耗过快。

此外，由其实现机制不难发现，如果有两个事务并发对同一行记录执行操作，就有可能会产生死锁：

时间顺序	事务T1	事务T2
1	执行insert...on duplicate key update
2	insert失败	执行insert...on duplicate key update
3	获取重复记录的S锁，并获取该记录	insert失败
4	update该记录的值	获取重复记录的S锁，并获取该记录
5	申请该记录的X锁，由于T2有该记录的S锁，需要等待其释放	update该记录的值
6		申请该记录的X锁，由于T1有该记录的S锁，需要等待其释放
7	发生死锁	发生死锁

最后，关于“数据表存在多个唯一键时更新记录不确定”这个问题产生的原因是由于Innodb对重复key的检查顺序的不确定导致的。什么意思呢，假设我们一张表有两个唯一键（包括主键）A和B，现在我们使用insert...on duplicate key update语句插入一条记录，而我们插入的这条记录同时和数据表中的两条记录record1和record2出现唯一键冲突。其中和record1是在A键上冲突，和record2是在B键上冲突，那么Innodb最终只会返回这两条重复记录中的一条，并最终更新返回的这条记录。而且更重要的是，到底返回哪一条是不确定的。这就最终导致更新的记录也是不确定的。

到此，对比REPLACE可以发现，在这种场景下，REPLACE会先删除record1和record2记录，再重新插入新的记录，因而不存在上述问题。

当然，在实际的业务场景中，几乎不太可能出现待插入的数据和多条已有记录发生唯一键冲突，因而这个问题其实也无须太过关注。

四、三种方案小结

当innodb_autoinc_lock_mode等于1或2时，以上三个方案都存在主从不一致的问题。将innodb_autoinc_lock_mode设置为0（锁定保持到语句执行结束）可以解决这个问题，但这样的话，插入的并发度可能会受很大影响，这在生产环境中肯定是不允许的。

还有一种可行的方案，即不使用数据库的自增主键，转而使用自定义的唯一键来做主键。这种方案只适用于自定义主键具有和自增主键相类似优点的情形，这些优点包括：线性递增（避免插入时随机io）、占用空间小、速度快等。否则，该方案的插入和查询性能也会受到很大影响。这意味着，在大多数场景下，自增主键仍然是定义数据表的首选。

实际上，当innodb_autoinc_lock_mode等于1或2时，即使是普通的insert语句也可能会因为唯一键冲突导致插入失败进而最终导致主从不一致。从这个角度来说，主从不一致问题并非这三个方案所特有，需要一种统一的机制来解决（比如当主库的auto_increment字段变更时同步到从库，或者在主从切换时先手动同步一次auto_increment值），因而以上三个方案本身无须太过关注这个问题。

五、最佳实践

其中，IGNORE方案由于采用了冲突则不更新的机制，几乎不满足实际的业务场景，因而该方案实际用处并不大。因而最佳实践不考虑IGNORE方案。而REPLACE由于存在容易数据丢失和主键消耗过快等问题，也不作为最佳实践的首选。

对于insert...on duplicate key update，在使用时我们需要充分评估并发可能带来的死锁问题：如果业务场景中不太可能出现并发对同一条数据的操作，则优先选择该方案；否则优先考虑以下两种方案（以Java语言为例）：

开启事务，在事务中先执行普通的select语句，如果查询结果为空，则执行普通的insert语句，否则执行update语句。当然，在并发度非常高的场景，可能会出现select的结果为空，但insert却抛DuplicateKeyException异常（即出现幻读），或者在执行update时发现select的结果已经被其它事务删除的现象。对于这种情况，建议直接给上层返回操作失败，由上层业务决定是否重试。该方案适合并发度非常高的业务场景，通过先select再insert或update的方式来避免高频的唯一键冲突。
开启事务，在事务中先执行普通的insert语句，如果抛出重复键异常DuplicateKeyException（Java语言）时，在catch异常中先执行先执行select语句，再执行update语句的方式。当然这里又会引入新的并发问题，那就是当insert时抛出重复键异常，但在select时发现记录已经被其它线程删除（当隔离级别为RU或RC时），或者执行update时记录被其它线程删除。对于这种情况，建议直接给上层返回操作失败，而不要再考虑重新insert，以避免陷入死循环。该方案适合并发度不太高的场景，因而大多数情况下直接insert能执行成功，从而避免执行两次sql操作。

PS：上述两种方案都推荐使用普通的select语句，而不推荐使用select...for update语句，因为后者在隔离级别为RR（MySQL的默认级别）和Serializable两种隔离级别下使用的是临键锁，而这又可能导致另一种死锁，具体参见参考博客。

结尾彩蛋

本文一开始说明，我们不考虑指定主键（id）的场景：

insert ignore into `identity_info`(`id`,`identity_id`,`name`) values('2','123456789012345678','李四');
replace into `identity_info`(`id`,`identity_id`,`name`) values('2','123456789012345678','李四');
insert into `identity_info`(`id`,`identity_id`,`name`) values('2','123456789012345678','李四') on duplicate key update `name` = '王五';

一方面是因为在实际的业务场景中，几乎不会有指定主键（id）的情形；另一方面，当指定主键（id）时，无论insert语句执行成功或失败，auto_increment值都不会递增，因而不会出现主从不一致的问题。

此外，参考博客中提到，MySQL在指定主键（id ）进行插入的时候，如果这个id大于表的自增值，那么MySQL会把表的自增值修改为这个id值并加1，但是如果我们把主键更新成更大的值，MySQL并不会把表的自增值修改为更新后的值，会埋下一颗定时炸弹，在某些情况下，如DDL，重启等之后，业务开始报错，会误认为DDL或者重启导致业务表的插入故障。由此可知，在实际的业务操作中，也不建议在insert语句中指定主键（id）值。

参考博客：

1、https://blog.csdn.net/jbboy/article/details/46828917 MySql避免重复插入记录方法(ignore,Replace,ON DUPLICATE KEY UPDATE)

2、https://www.cnblogs.com/moss_tan_jun/p/6909565.html MySQL auto_increment的坑

3、https://blog.csdn.net/pml18710973036/article/details/78452688 INSERT ... ON DUPLICATE KEY UPDATE产生death lock死锁原理

4、https://www.jb51.net/article/169601.htm MySQL使用Replace操作时造成数据丢失的问题解决

5、https://blog.csdn.net/Saintyyu/article/details/91269087 MySQL常见的七种锁详细介绍

6、https://blog.csdn.net/Saintyyu/article/details/100810471 https://blog.csdn.net/Saintyyu/article/details/100810471

7、https://www.cnblogs.com/chenpingzhao/p/4956014.html 【MySQL】关于binlog格式

8、https://www.cnblogs.com/lin1/p/6269326.html MySQL三种binlog格式的理解

9、https://www.cnblogs.com/zhoujinyi/archive/2013/01/15/2836131.html MySQL Binlog 【ROW】和【STATEMENT】选择

10、https://blog.csdn.net/h2604396739/article/details/86680974 binlog的row\statement\mixed模式与具体内容

11、https://www.jianshu.com/p/a2fc6dde6b06 replace语句的binlog触发