为什么不建议使用ON DUPLICATE KEY UPDATE？

关注我，回复关键字“spring”，

免费领取Spring学习资料。

文章来源：https://c1n.cn/BeR1Q

背景

昨天评审代码时，大佬同事看到我代码里使用了 mysql 的 on duplicate key update 语法实现了对数据的 save or update，说这个语法有严重的性能和其他隐患问题，让我必须改成先查询一次分出新增集合和修改集合，再分别进行批量新增和批量修改的方式进行，并对批量修改时使用 case when 的方式实现。

对于批量修改，在 mybatis 中也就是类似这种的 xml：

    <update id="updateByIds">
        update tb_user
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="name = case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    when id= #{i.id,jdbcType=VARCHAR} then #{i.name,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="weight = case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    when id= #{i.id,jdbcType=VARCHAR} then #{i.weight,jdbcType=DECIMAL}
                </foreach>
            </trim>
            <trim prefix="high = case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    when id= #{i.id,jdbcType=VARCHAR} then #{i.high,jdbcType=DECIMAL}
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item.id,jdbcType=VARCHAR}
        </foreach>
    </update>

对于这种做法我也表示认同，但我还是很想了解一下 on duplicate key update 到底有什么问题，问大佬同事也说不出具体的性能和隐患原因在哪里，所以我决定还是靠自己研究一下。

官方资料

为了能更直接获取出最权威的信息，直接上 mysql 的官方说明查看有无对应的资料。

根据使用的 mysql 版本查看对应的说明，如我这里的 mysql5.7 为例，其官方说明地址如下：

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

其中对于 on duplicate key update 的使用方法也有非常详细的说明。

这里对于它的使用方法不做介绍，感兴趣的可以点开上面的链接进行详细的查看。

但为了对官方文档中的说明进行验证，这里根据官方的说明进行一个小实验进行验证。

进行验证

创建一个 t1 表：

CREATE TABLE `t1` (
  `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键ID',
  `b` int(11),
  `c` int(11),
  PRIMARY KEY (`a`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='临时测试表'

| 验证主键插入并更新功能

空表创建好后，多次执行如下 sql（此时只有自增主键 a 列）：

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

执行 1 次的结果：

执行 2 次的结果：

执行 3 次的结果：

执行 4 次的结果：

执行 5 次的结果：

通过观察可知，上面的 sql 在主键已经存在时相当于如下 sql：

UPDATE t1 SET c=c+1 WHERE a=1

再试下新增的 sql：

INSERT INTO t1 (b,c) VALUES (20,30)
ON DUPLICATE KEY UPDATE c=c+1;

新增记录成功，id 也自增正常。

| 验证多字段唯一索引问题

在官方资料中有这样的一句话：

If column b is also unique, the INSERT is equivalent to this UPDATE statement instead:

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

接下来实验一下，给 t1 加的 b 也加上唯一索引：

ALTER TABLE t1 ADD UNIQUE INDEX uniq_b (b ASC);

然后执行如下 sql：

INSERT INTO t1 (a,b,c) VALUES (3,20,30)
ON DUPLICATE KEY UPDATE c=c+1;

其 t1 表结果如下：

从上面的结果可以看出，其只执行了 update 的操作，从而告诉了我们在使用 on duplicate key update 语句时，应当避免多个唯一索引的场景。

当 a 是一个唯一索引（unique index）时，并且 t1 表中已经存在 a 为 1 的记录时，如下两个 sql 的效果是一样的。

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

ALTER TABLE t1 DROP INDEX uniq_b ;

ALTER TABLE ntocc_test.t1
ADD UNIQUE INDEX uniq_b (b ASC);
;

但在 innoBD 存储类型的表中，当 a 是一个自增主键时，其效果官方文档中的解释是这样的：

The effects are not quite identical: For an InnoDB table where a is an auto-increment column, the INSERT statement increases the auto-increment value but the UPDATE does not.

也就是如果只有一个主键，则会执行新增操作，但当 b 也是一个唯一索引时，就会执行更新操作。

上面的语句就会变成这样的：

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

因此应当避免多唯一索引用 on deplicate key update 语法。

| 涉及到的锁说明

同时，在查看官网资料中底部对于此语法的说明，从中看到如下描述：

An INSERT … ON DUPLICATE KEY UPDATE on a partitioned table using a storage engine such as MyISAM that employs table-level locks locks any partitions of the table in which a partitioning key column is updated. (This does not occur with tables using storage engines such as InnoDB that employ row-level locking.) For more information, see Section 22.6.4, “Partitioning and Locking”.

主要是说在 MyISAM 的存储引擎中，on duplicate key update 使用的是表级锁来进行实现的，那么就可以存在表级锁时的事务并发性能问题。

但是 innoDB 引擎中，on duplicate key update 是用的行级锁进行实现的。

但同时查看了官方的 bug 列表，发现如下记录：https://bugs.mysql.com/bug.php?id=52020

其中有如下记录：

Hi,

I am facing this same issue in version 5.7.18. Deadlock error when multiple threads execute INSERT… ON DUPLICATE KEY UPDATE for bulk insert/update.

How it can be fixed?

I am facing the same issue when multiple threads are trying to insert in same table with primary key and unique index. Records are being inserted are different. Also It seems to be taking next-key lock here.

主要是说在并发事务的情况下，可能会导致死锁。

为了对此进行验证，我使用连接工具进行了验证，但可能是因为并发不够的原因，并没有产生死锁。

总结

如下：

• on duplicate key update 在 MyISAM 存储引擎下使用的是表锁，性能不好。

• on duplicate key update 在 InnoDB 下并发事务情况下可能会存在锁表/死锁问题。

• 应尽量避免在多唯一索引的情况下使用此语句。

END

往期精彩程序员缺乏经验的 7 种表现
一款 IDEA 代码防沉迷插件
最新 955 不加班的公司名单
JetBrains 宣布涨价
如何写出令人惊叹的设计文档？

关注后端面试那些事，回复【2022面经】

获取最新大厂Java面经

最后重要提示：高质量的技术交流群，限时免费开放，今年抱团最重要。想进群的，关注SpringForAll社区，回复关键词：加群，拉你进群。