Mysql replace into 与 insert into on duplicate key update 死锁和性能测试

最新推荐文章于 2025-10-18 12:20:09 发布

原创最新推荐文章于 2025-10-18 12:20:09 发布 · 置顶 · 1.2w 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #性能 #性能测试 #并发 #数据库

mysql 专栏收录该内容

1 篇文章

订阅专栏

该博客通过性能测试和并发测试比较了MySQL中的REPLACE INTO和INSERT INTO ON DUPLICATE KEY UPDATE在不同场景下的死锁发生概率和性能。测试发现，当涉及自增ID主键时，REPLACE INTO可能导致更高的死锁概率，而性能表现相近。在数据量达到100W时，两者都可能出现慢日志，但并发的改查操作会导致慢日志明显增加。建议在高并发环境下避免使用REPLACE INTO。

1 编写目的

1. 测试 replace into 引发死锁

2. 测试 replace 和INSET INTO *** ON DUPLICATE KEY UPDATE *** 性能差

2 数据库环境说明

1、数据库系统：

名称：Mysql 5.5.31

引擎：innodb

字符集：UTF8

安装方式：源码编译安装

2、数据库部署环境：

软硬件环境：Linux（CentOS-6.2-x86_64）

3 测试工具

Mysql 自带 Mysqlslap

4 测试过程

所有操作前重启mysql，避免缓存。

测试表：

CREATE TABLE`test_replace_into` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`wechat_init_code` int(11) NOT NULL,

`used` varchar(50) DEFAULT '0',

`expire` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

UNIQUE KEY `ii` (`wechat_init_code`)

) ENGINE=InnoDBDEFAULT CHARSET=utf8;

insert_on_duplicate.sql：

SET@mm=ROUND(RAND()*100000);

SET@nn=ROUND(RAND()*100000);

INSERT INTOtest.`test_replace_into`(wechat_init_code,used,expire) VALUES (@mm,@nn,@nn) ONDUPLICATE KEY UPDATE used =@nn,expire=@nn ;

replace_into.sql:

SET@mm=ROUND(RAND()*100000);

SET @nn=ROUND(RAND()*100000);

REPLACE INTOtest.`test_replace_into` (`wechat_init_code`,`used`,`expire`) VALUES(@mm,@nn,@nn);

insert_on_duplicate_with_select.sql:

SET@mm=ROUND(RAND()*100000);

SET@nn=ROUND(RAND()*100000);

INSERT INTOtest.`test_replace_into`(wechat_init_code,used,expire) VALUES (@mm,@nn,@nn) ONDUPLICATE KEY UPDATE used =@nn,expire=@nn ;

CALLtest.`proc_test_replace_int`();

replace_into_with_select.sql:

SET@mm=ROUND(RAND()*100000);

SET@nn=ROUND(RAND()*100000);

REPLACE INTOtest.`test_replace_into` (`wechat_init_code`,`used`,`expire`) VALUES(@mm,@nn,@nn);

CALLtest.`proc_test_replace_int`();

test.`proc_test_replace_int`()：

DELIMITER $$

USE `test`$$

DROP PROCEDURE IFEXISTS `proc_test_replace_int`$$

CREATEDEFINER=`root`@`%` PROCEDURE `proc_test_replace_int`()

BEGIN

SET @mm=ROUND(RAND()*100000);

PREPARE sql2 FROM 'SELECT * FROMtest.`test_replace_into` LIMIT ?,10 ;';

EXECUTE sql2 USING @mm;

END$$

DELIMITER ;

4.1 全更新操作，含自增ID主键，唯一索引

前置操作A:建表：

前置操作B:使用文件（2.test_data.sql）填充数据。

测试语句：

servicemysql restart

mysqlslap -uroot -proot --delimiter=";" --concurrency=1000 --number-of-queries=10000 --iterations=10--query="insert_on_duplicate.sql"

结果：

servicemysql restart

mysqlslap -uroot -proot --delimiter=";" --concurrency=1000 --number-of-queries=10000 --iterations=10--query="replace_into.sql"

结果：

4.1.1 结论：在这个情况下， replace into 的死锁概率高, 效率略高

4.2 全更新操作，不含ID，唯一索引为主键

前置操作A:删除表中的自增ID，进行测试

测试语句：

servicemysql restart

mysqlslap -uroot -proot --delimiter=";" --concurrency=1000 --number-of-queries=10000 --iterations=10--query="insert_on_duplicate.sql"

servicemysql restart

mysqlslap -uroot -proot --delimiter=";" --concurrency=1000 --number-of-queries=10000 --iterations=10--query="replace_into.sql"

4.2.1 结论：这个情况性能接近，数据增加到100W是出现少量慢日志

4.3 初始空数据，增加修改，含自增ID主键，唯一索引

mysql-uroot -proot -e 'TRUNCATE TABLE test.`test_replace_into`;'

mysqlslap -uroot -proot --delimiter=";" --concurrency=1000 --number-of-queries=10000 --iterations=10--query="replace_into.sql"

mysql -uroot-proot -e 'TRUNCATE TABLE test.`test_replace_into`;'

mysqlslap -uroot -proot --delimiter=";" --concurrency=1000 --number-of-queries=10000 --iterations=10--query="insert_on_duplicate.sql"

结论： replace into容易死锁

4.4 初始空数据，增加修改，不含ID，唯一索引为主键

mysql-uroot -proot -e 'TRUNCATE TABLE test.`test_replace_into`;'

mysqlslap -uroot -proot --delimiter=";" --concurrency=1000 --number-of-queries=10000 --iterations=10--query="replace_into.sql"

mysql-uroot -proot -e 'TRUNCATE TABLE test.`test_replace_into`;'

mysqlslap -uroot -proot --delimiter=";" --concurrency=1000 --number-of-queries=10000 --iterations=10--query="insert_on_duplicate.sql"

结论：接近

4.5 全更新操作，不含ID，唯一索引为主键,增加查询

前置操作：数据扩充100W

service mysql restart

mysqlslap -uroot -proot --delimiter=";" --concurrency=1000 --number-of-queries=10000 --iterations=5--query="replace_into_with_select.sql"

service mysql restart

mysqlslap -uroot -proot --delimiter=";" --concurrency=1000 --number-of-queries=10000 --iterations=5--query="insert_on_duplicate_with_select.sql"

4.5.1 结论：接近，在数据库数据量达到100W时，慢日志明显暴增. 15000的1秒慢日志，两个语句慢日志数量接近

5 结论

带自增ID，使用replace 容易死锁，不要使用replace into，效率接近。

当数据量达到100W基本是，单纯修改，少量慢日志，同时的改查，慢日志明显。两者性能接近。

慢日志频繁时应该考虑降低写入并发。