ON DUPLICATE KEY UPDATE 使用的问题

ON DUPLICATE KEY UPDATE 主要实现了一个insert sql 的save or update。但是当是两个唯一主键的时候是有性能问题及隐患，这个下面会解释。

一：背景前要

首先看下面的sql 是一个mybatis xml中使用case when 函数，这个我着实第一次学习我(谁让本宝宝才疏学浅呢)。

 <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>

这个代码好好理解理解。

二：官方文档隐患解释

根据使用的 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.

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

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

五：总结

1. on duplicate key update 在 MyISAM 存储引擎下使用的是表锁，性能不好。
2. on duplicate key update 在 InnoDB 下并发事务情况下可能会存在锁表/死锁问题。
3. 应尽量避免在多唯一索引的情况下使用此语句。