為什么不建議使用ON DUPLICATE KEY UPDATE？

昨天評審代碼時，一群大佬看到有同事的代碼里使用了mysql的on duplicate key update語法實現(xiàn)了對數(shù)據(jù)的save or update，說這個語法有嚴(yán)重的性能和其他隱患問題，必須改成先查詢一次分出新增集合和修改集合，再分別進(jìn)行批量新增和批量修改的方式進(jìn)行，并對批量修改時使用case when的方式實現(xiàn)。

對于批量修改，在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>

公司同事寫的批量修改 SQL。對于這種做法我也表示認(rèn)同，但同事追問了一句，很想了解一下on duplicate key update到底有什么問題，很多同事也說不出具體的性能和隱患原因在哪里，所以我就寫出了這篇文章。

官方資料

為了能更直接獲取出最權(quán)威的信息，直接上mysql的官方說明查看有無對應(yīng)的資料。根據(jù)使用的mysql版本查看對應(yīng)的說明，如我這里的mysql5.7為例，其官方說明地址如下：

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

其中對于「on duplicate key update」的使用方法也有非常詳細(xì)的說明。

這里對于它的使用方法不做介紹，感興趣的可以點開上面的鏈接進(jìn)行詳細(xì)的查看。

但為了對官方文檔中的說明進(jìn)行驗證，這里根據(jù)官方的說明進(jìn)行一個小實驗進(jìn)行驗證。

創(chuàng)建一個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='臨時測試表'

驗證主鍵插入并更新功能

空表創(chuàng)建好后，多次執(zhí)行如下sql。(此時只有自增主鍵a列)

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

執(zhí)行1次的結(jié)果:

執(zhí)行2次的結(jié)果:

執(zhí)行3次的結(jié)果:

執(zhí)行4次的結(jié)果:

執(zhí)行5次的結(jié)果:

通過觀察可知，上面的 sql 在主鍵已經(jīng)存在時相當(dāng)于如下 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);

然后執(zhí)行如下 sql:

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

其 t1 表結(jié)果如下：

從上面的結(jié)果可以看出，其只執(zhí)行了 update 的操作，從而告訴了我們在使用 on duplicate key update 語句時，應(yīng)當(dāng)避免多個唯一索引的場景

當(dāng)a是一個唯一索引(unique index)時,并且t1表中已經(jīng)存在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存儲類型的表中，當(dāng)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.

也就是如果只有一個主鍵，則會執(zhí)行新增操作

但當(dāng)b也是一個唯一索引時，就會執(zhí)行更新操作
上面的語句就會變成這樣的：

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.

因此應(yīng)當(dāng)避免多唯一索引用on deplicate key update語法

涉及到的鎖說明

同時，在查看官網(wǎng)資料中底部對于此語法的說明，從中看到如下描述:

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”https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-locking.html。

主要是說在MyISAM的存儲引擎中,on duplicate key update使用的是表級鎖來進(jìn)行實現(xiàn)的，那么就可以存在表級鎖時的事務(wù)并發(fā)性能問題。

但是innoDB引擎中，on duplicate key update是用的行級鎖進(jìn)行實現(xiàn)的。

但同時查看了官方的bug列表，發(fā)現(xiàn)如下記錄：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.

主要是說在并發(fā)事務(wù)的情況下，可能會導(dǎo)致死鎖。

為了對此進(jìn)行驗證，我使用連接工具進(jìn)行了驗證，但可能是因為并發(fā)不夠的原因，并沒有產(chǎn)生死鎖。

總結(jié)

1. on duplicate key update在MyISAM存儲引擎下使用的是表鎖，性能不好

2. on duplicate key update在InnoDB下并發(fā)事務(wù)情況下可能會存在鎖表/死鎖問題

3. 應(yīng)盡量避免在多唯一索引的情況下使用此語句