insert into xxx on duplicate key update xxx=xxx语句可以优秀地解决插入数据时产生的重复主键问题,前提是设置了正确的unique key。
但在大数据量情况下(超过1w条),在duplicate key较少的情况下仍表现良好,遇到duplicate key较多的场景下,执行效率出现断崖式降低。
为了解决此问题,
优化点1:可采用先update再进行insert ignore的方式替代原逻辑。
实际场景如下:A表中数据(每日更新)需要插入B表,且B表中数据状态会根据A表的数据进行更新。
优化点2:对数据按照分页思想进行分批插入。
运用原逻辑
insert into B (id, name, age, sex)
select A.id, A.name, A.age, A.sex from A left join B
on A.id = B.id and A.name=B.name
on duplicate key update age=values(age), sex=values(sex)
优化点1
运用替换后的逻辑分两步走
step 1,更新B表中存在于A表中记录的age和sex
update B join A on A.id = B.id and A.name=B.name
set age=A.age, sex=A.sex
step 2, 将A表中B表不存在的记录,插入B表
insert ignore into B (id, name, age) from A
优化点2
例,15w条数据,每次插入1w条
step1
update B join
(select id,name,age,sex from A limit pageNumber,pageSize) aa
on aa.id = B.id and aa.name=B.name
set age=aa.age, sex=aa.sex
step 2, 将A表中B表不存在的记录,插入B表
insert ignore into B (id, name, age, sex) from (
select id,name,age,sex from A limit pageNumber,pageSize
) aa
注意:update 和insert执行时间较长时,连续执行会造成mysql死锁,做完update 操作后,根据实际数据量,应进行sleep,避免产生死锁
Thread.sleep(1000)
15w万条数据,实测有效。