1. ON DUPLICATE KEY UPDATE语法

duplicate：美 [ˈduːplɪkeɪt , ˈduːplɪkət] 完全一样的。

mysql表结构：

CREATE TABLE `t_school` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  `stu_num` int(11) DEFAULT NULL COMMENT '学生数量',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_index` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

其中：id是逻辑主键、name是唯一索引。

业务场景中：若某条记录存在，那么更新，否则插入。

mysql语法：

INSERT INTO t_school(`name`,address,stu_num) VALUES('海淀中学','北京市',2000) ON DUPLICATE KEY UPDATE address = '北京市海淀区';

ON DUPLICATE KEY UPDATE语法的目的是为了解决重复性，当数据库存在某个记录时，执行这条语句会更新它，而不存在这条记录时，会插入它。

如何判断记录是否存在

如果插入的记录存在主键或唯一索引（例如：上例中name便是唯一索引），且表中存在该记录，那么就会认为该条记录存在，则便是更新语句。

2. ON DUPLICATE KEY UPDATE语法的坑

2.1 导致主键不连续自增

ON DUPLICATE KEY UPDATE可能会导致主键不连续自增。

MySQL性能（4）—AUTO_INCRMENT锁机制（innodb_autoinc_lock_mode参数配置）。

在Mysql5.1.2-Mysql8.0中innodb_autoinc_lock_mode的默认配置。即对于Mixed inserts(混合插入)：直接分析语句，获取最坏情况下需要插入的数量，然后一次性分配足够的auto increment id，只会将整个分配过程锁住。

INSERT … ON DUPLICATE KEY UPDATE属于Mixed inserts。该语句插入时并不会锁表操作，而是会分配足够的自增主键。但若是最终触发了UPDATE操作，那么便会导致主键的跳跃。

2.2 binlog主从复制不一致

影响的mysql的版本：5.0.91,5.1.50,5.6.0

相关bug地址：https://bugs.mysql.com/bug.php?id=50413

原因：当存在多个唯一键时，binlog选择statement模式，会导致主从不一致。

复现：

create table t1(
  f1 int auto_increment primary key,
  f2 int unique key,
  f3 int
);

表中有一条记录：(1, 10, 100)

con1	con2
begin;	begin;
insert into t1 values(2, 10, 200) on duplicate key update f3 = 120;
	insert into t1 values(2, 20, 300) on duplicate key update f3 = 500;
	commit;
commit;

1. con1在con2开始之前执行了整个插入，则con1会在f2=10上看到冲突，因此将唯一行更新为（1，10，120）；
2. con2看不到冲突，于是插入（2，20，300）；
3. 最终主库的状态为：

f1	f2	f3
1	10	120
2	20	300

因为binlog若是statement模式的场景下：语句执行的顺序是commit的先后顺序，而con2是con1之前进行commit的，于是从库顺序为：

1. con2插入不会看到冲突，然后插入（2，20，300）；
2. con1会看见冲突，但这次是在f1=2上（与master相反，主库是在f2=10上看到的），因此它将冲突行改为（2，20，120）；
3. 最终从库的状态为：

f1	f2	f3
1	10	100
2	20	120

出现bug了...

mysql做出的改变是加更多的锁（gap lock），来避免这种场景。但是加锁又会引发死锁，造成性能损耗。详见下文。

2.3 存在间隙锁，影响性能

加了间隙锁，导致死锁，影响性能。

影响的mysql版本：5,7,26 8.0.15

问题来源：https://bugs.mysql.com/bug.php?id=98324

在2.2中我们说到了ON DUPLICATE KEY UPDATE加了更多的锁，来解决多个唯一索引场景下主从不一致的情况。

那么为什么mysql会选择间隙锁作为处理方案？

image.png

我们对这里处理的困难在于：如果有多个约束，那么违反哪个特定的约束（f1 或 f2）非常重要，因为这会影响ON DUPLICATE KEY UPDATE字句将更新哪一行（f1有冲突还是f2有冲突？）

为了提供可序列化性（binlog主从一致性），我们需要“锁定我们看到的一切以做出决定”【要加间隙锁】

ON DUPLICATE KEY UPDATE的流程：
在主库中，con1通过首先观察到没有f1=2的行，做出冲突发生在f2（而不是f1）上的决定。
它是如何观察到的？通过主索引临时创建一条f1=2的行，做出了冲突发生在f2（而不是f1）上的决定。
直到后来，当它发现f2上的冲突并决定删除该记录时。
通过删除记录，con1删除了它在f1上没有看到冲突的“证明”的一部分。
这就是con2能够插入f1=2行的原因，这在以后会导致复制问题。

“保留证据”的正确做法是确保f1=2的间隙保持锁定，直到con1提交。

这样可以通过在临时行上创建显式锁来实现，然后让它在删除时被继承。
这种显式锁可以通过所谓的隐式到显式转换来创建。
con1 已经对记录进行了隐式锁定，因为它的 TRX_ID 作为写入它的 trx 的 id 在行的标题中。
但是这个隐式锁会在我们物理删除记录后立即消失，因此需要将其作为显式锁保存在内存中。

3. 优化方案：

分析系统插入操作多还是更新操作多。若是更新操作多，可以先进行update操作，若更新失败（影响的行数为0），那么在进行插入操作。

推荐阅读

关于MYSQL 的 AUTO-INC Locks

mysql官网升级信息文档