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

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基本是，单纯修改，少量慢日志，同时的改查，慢日志明显。两者性能接近。

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