Mysql_inserOrUpdate_解决方法和存在问题_insert into on duplicate key update

 

解决问题：Mysql_inserOrUpdate_解决方法

http://www.jb51.net/article/39255.htm

mysql "ON DUPLICATE KEY UPDATE" 语法
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。 
例如，如果列 a 为 主键 或 拥有UNIQUE索引，并且包含值1，则以下两个语句具有相同的效果：

复制代码代码如下:

INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

如果行作为新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2。 
这个语法还可以这样用: 
如果INSERT多行记录(假设 a 为主键或 a 是一个 UNIQUE索引列):

复制代码代码如下:

INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;

执行后, c 的值会变为 4 (第二条与第一条重复, c 在原值上+1).

复制代码代码如下:

INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);

执行后, c 的值会变为 7 (第二条与第一条重复, c 在直接取重复的值7). 
注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！ 
这个语法和适合用在需要 判断记录是否存在,不存在则插入存在则更新的场景.

INSERT INTO .. ON DUPLICATE KEY更新多行记录
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：

复制代码代码如下:

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

如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。
如果你想了解更多关于INSERT INTO .. ON DUPLICATE KEY的功能说明，详见MySQL参考文档：13.2.4. INSERT语法

现在问题来了，如果INSERT多行记录， ON DUPLICATE KEY UPDATE后面字段的值怎么指定？要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE，到底他会更新一行记录，还是更新所有需要更新的行。这个问题困扰了我很久了，其实使用VALUES()函数一切问题都解决了。

举个例子，字段a被定义为UNIQUE，并且原数据库表table中已存在记录(2,2,9)和(3,2,1)，如果插入记录的a值与原有记录重复，则更新原有记录，否则插入新行：

复制代码代码如下:

INSERT INTO TABLE (a,b,c) VALUES 
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);

以上SQL语句的执行，发现(2,5,7)中的a与原有记录(2,2,9)发生唯一值冲突，则执行ON DUPLICATE KEY UPDATE，将原有记录(2,2,9)更新成(2,5,9)，将(3,2,1)更新成(3,3,1)，插入新记录(1,2,3)和(4,8,2)

 

mysql DUPLICATE KEY UPDATE 死锁问题

DUPLICATE KEY UPDATE batch执行时出死锁错误

 

背景知识

一、 mysql  insert 与 duplicate key：

典型的插入语句：

多条：INSERT INTO tablename (columnA, columnB, columnC)

VALUES ('a', 1, 2), ('b', 7, 5)

单条：INSERT INTO tablename SET columnA='a', columnB=1, columnC=2

复制：INSERT [options1] [INTO] tablename [ (columnlist) ]
SELECT …

若表已设置主键如columnA，重复的插入无效

ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'

如果数据库中已有某条数据，以下的两条语句可等同：

INSERT INTO tablename (id, data) VALUES (1, 10)
ON DUPLICATE KEY UPDATE data=data+10;

UPDATE tablename SET data=data+10 WHERE id=1;

duplicate key语句一般应用在 格式化多条更新语句：

INSERT INTO tablename (id, data) VALUES (1, 10), (2, 15)
ON DUPLICATE KEY UPDATE data=data+VALUE(data)

 

二、innodb表提高插入效率

查询表使用的引擎：  show create table tablename;

innodb 的存储引擎提供行级锁，支持共享锁和排他锁两种锁定模式，以及四种不同的隔离级别。

对于Innodb 类型的表，我们有以下几种方式可以提高导入的效率：
a. 因为Innodb 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺
序排列，可以有效的提高导入数据的效率。如果Innodb 表没有主键，那么系统会默认
创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高
导入数据的效率。
b. 在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET
UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
c. 如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动
提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

如果如果你同时从同一客户插入很多行，使用多个值表的INSERT 语句。这比使用分开INSERT 语句快(在一些情况中几倍)。

你从不同客户插入很多行，能通过使用INSERT DELAYED 语句得到更高的速度。Delayed 的含义是让insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；

 

在项目中遇到的问题时，使用了这种insert处理，但是分库分表，数据表类型为innodb，  tablename各不相同，duplicate key只是用于合并update和insert语句。

 "java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction" 错误状况为 第一个插入即出错，或一个batch中重复一个key，插入多个值。 貌似mysql有这个bug（http://bugs.mysql.com/bug.php?id=52020）

使用threadlocal去获取操作数据库的对象，static对象，获取pool的连接并执行批处理方法

 

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

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

 

+

+

+

-

-

-