MySQL的批量更新和批量新增優(yōu)化方式

更新時(shí)間：2025年03月14日 08:40:45 作者：唯荒

這篇文章主要介紹了MySQL的批量更新和批量新增優(yōu)化方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

MySQL的批量更新和批量插入優(yōu)化

如果需要批量插入和批量更新操作就需要進(jìn)行sql 的優(yōu)化，否則近30萬(wàn)條數(shù)據(jù)的插入或更新就會(huì)耗費(fèi)幾分鐘甚至更多的時(shí)間，此文僅批量插入和批量更新的幾種優(yōu)化。

批量插入篇（使用多條insert語(yǔ)句、使用union all創(chuàng)建臨時(shí)表、使用多個(gè)values）；
批量更新篇（使用多條update語(yǔ)句、使用union all創(chuàng)建臨時(shí)表創(chuàng)建臨時(shí)表、使用replace into、使用insert ... on duplicate key ... update...）。

如果有需要的同僚可根據(jù)下列內(nèi)容使用jdbcTemplate和Java反射技術(shù)將其封裝。

特別提示：做批量操作時(shí)，請(qǐng)限制每次1000-2000條數(shù)據(jù)，以避免GC和OOM。后期也會(huì)貼出相關(guān)代碼，歡迎指正優(yōu)化或提供其它更好的方法。

批量插入篇

1. 多條insert語(yǔ)句(快)

實(shí)測(cè)：50*6500行數(shù)據(jù)耗時(shí)8-12秒，如果不是手動(dòng)提交事務(wù)，耗時(shí)約70-180秒

類型：
	insert into table_name(id,name,title) values(?, ?, ?);

常用的插入操作就是批量執(zhí)行1條insert類型的SQL語(yǔ)句，這樣的語(yǔ)句在執(zhí)行大量的插入數(shù)據(jù)時(shí)，其效率低下就暴露出來(lái)了。

特別注意：jdbc.url需要加上：allowMultiQueries=true

jdbc.url = jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true

（1）sql 語(yǔ)句

start transaction;
insert into table_name(id, name, title) values(1, '張三', '如何抵擋美食的誘惑?');
insert into table_name(id, name, title) values(2, '李四', '批判張三的《如何抵擋美食的誘惑?》');
insert into table_name(id, name, title) values(3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒');
insert into table_name(id, name, title) values(4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)');
commit;

（2）mapper 文件的 sql

<insert id="batchSave"  parameterType="java.util.List">
	start transaction;
	<foreach collection="list" index="index" item="item">
		insert into table_name(id, name, title) values(#{item.id}, #{item.name}, #{item.title});
		
	</foreach>
	commit;
</insert>

2. 多個(gè)values語(yǔ)句(快)

實(shí)測(cè)：50*6500行數(shù)據(jù)耗時(shí)6至10秒（與服務(wù)器的有關(guān)）

類型：
	insert into table_name(id, name, title) values(?, ?, ?), ..., (?, ?, ?);

（1）sql 語(yǔ)句

insert into 
	table_name(id, name, title) 
values
		(1, '張三', '如何抵擋美食的誘惑?'),
		(2, '李四', '批判張三的《如何抵擋美食的誘惑?》'),
		(3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒'),
		(4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)');

（2）mapper 文件的 sql

<insert id="batchSave"  parameterType="java.util.List">
	insert into table_name(id, name, title) values
	<foreach collection="list" index="index" item="item" separator=", ">
	    (#{item.id}, #{item.name}, #{item.title})
	</foreach>
</insert>

3. 使用union all 創(chuàng)建臨時(shí)表(快)

實(shí)測(cè)：50*6500行數(shù)據(jù)耗時(shí)6至10秒（與服務(wù)器的有關(guān)）

類型：
	insert into table_name(id,name,title) select ?, ?, ? union all select ?, ?, ? union all ...

union all 在這里select ?, ?, ? union all select ?, ?, ? union all ...是創(chuàng)建臨時(shí)表的原理，先創(chuàng)建整張臨時(shí)表到內(nèi)存中，然后將整張臨時(shí)表導(dǎo)入數(shù)據(jù)庫(kù)，連接關(guān)閉時(shí)即銷毀臨時(shí)表，其他的不多說(shuō)，可自行了解。

（1）sql 語(yǔ)句

insert into  
	table_name(id, name, title) 
select 
	1, '張三', '如何抵擋美食的誘惑?' 
union all
select 
	2, '李四', '批判張三的《如何抵擋美食的誘惑?》' 
union all
select 
	3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒' 
union all
select 
	4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)';

a. 創(chuàng)建臨時(shí)表方式1 - 使用 temporary + union all

簡(jiǎn)單列舉三種創(chuàng)建臨時(shí)表的方式, 這里熟悉了temporary 、 select ?, ? ,? union all select ?, ?, ? 和、insert into ... values(?, ?, ?), (?, ?, ?), (?, ?, ?)...之后，都可以組合創(chuàng)建臨時(shí)表，效率幾乎差不多。個(gè)人更加偏向第二種，因?yàn)楹?jiǎn)單方便。

create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
SELECT id, name, title FROM tmp
union all 
select 
	1, '張三', '如何抵擋美食的誘惑?' 
union all
select 
	2, '李四', '批判張三的《如何抵擋美食的誘惑?》' 
union all
select 
	3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒' 
union all
select 
	4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)';

b. 創(chuàng)建臨時(shí)表方式2 - 使用 select + union all

select 
	id, name, title from table_name where id = -1 
union all
select 
	1, '張三', '如何抵擋美食的誘惑?' 
union all
select 
	2, '李四', '批判張三的《如何抵擋美食的誘惑?》' 
union all
select 
	3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒' 
union all
select 
	4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)';

c. 創(chuàng)建臨時(shí)表方式3 - 使用 temporary + 多個(gè)insert values

create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
insert into 
	tmp(id, name, title) 
values
		(1, '張三', '如何抵擋美食的誘惑?'),
		(2, '李四', '批判張三的《如何抵擋美食的誘惑?》'),
		(3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒'),
		(4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)');

（2）mapper 文件的 sql

<insert id="batchSave"  parameterType="java.util.List">
	insert into table_name(id, name, title) 
	<foreach collection="list" index="index" item="item" separator=" union all ">
	    select #{item.id}, #{item.name}, #{item.title}
	</foreach>
</insert>

批量更新篇

1. 多條update語(yǔ)句批量更新(快)

實(shí)測(cè)：50*6500行數(shù)據(jù)耗時(shí)26-30秒，如果不是手動(dòng)提交事務(wù)，耗時(shí)約70-180秒

類型：
	update table_name set name = ?, title = ? where id = ?;

由于批量更新存在條件判斷，所以整體上時(shí)效上沒(méi)有批量插入那么高（下面是手動(dòng)提交事務(wù)的代碼）。

特別注意：jdbc.url需要加上：allowMultiQueries=true

jdbc.url = jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true

（1）sql 語(yǔ)句

start transaction;
update table_name set name = '張三', title = 'springboot如何入門(mén)' where id = 1;
update table_name set name = '李四', title = 'JVM到底是怎樣運(yùn)行的' where id = 2;
update table_name set name = '王五', title = '并發(fā)編程你需要注意什么' where id = 3;
update table_name set name = '趙柳', title = '別讓一時(shí)的貪成為你不努力的理由' where id = 4;
commit;

（2）mapper 文件的 sql

<update id="batchUpdate"  parameterType="java.util.List">
	start transaction;
	<foreach collection="list" index="index" item="item">
		update table_name set name = #{item.id}, title = #{item.title} where id = #{item.id};
		
	</foreach>
	commit;
</update >

2. 創(chuàng)建臨時(shí)表批量更新(快)

實(shí)測(cè)：50*6500行數(shù)據(jù)耗時(shí)26至28秒

（1）批量更新(使用 temporary + select … union all … select …創(chuàng)建臨時(shí)表)

類型：
	create temporary table 臨時(shí)表;
	select id, name, title FROM 臨時(shí)表 union all select ... union all ... select ...

（A）sql 語(yǔ)句

這里也可以使用 union all 加上 temporary 的方式創(chuàng)建臨時(shí)表, 詳情請(qǐng)看批量插入篇的創(chuàng)建臨時(shí)表的兩種方式

create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
select id, name, title from tmp
union all 
select 
	1, '張三', '如何抵擋美食的誘惑?' 
union all
select 
	2, '李四', '批判張三的《如何抵擋美食的誘惑?》' 
union all
select 
	3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒' 
union all
select 
	4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)';
update table_name, tmp set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;

（B）mapper 文件的 sql

<update id="batchUpdate"  parameterType="java.util.List">
	create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
	update table_name, (SELECT id, name, title FROM tmp union all 
	<foreach collection="list" index="index" item="item" separator=" union all ">
		select #{item.id}, #{item.name}, #{item.title}
	</foreach>) as tmp
	set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;
</insert>

（2）批量更新(使用 temporary + insert into values(…), (…)… 創(chuàng)建臨時(shí)表)

類型：
	create temporary table 臨時(shí)表;
	insert into values(...), (...)...;
	update ... set ... where ...;

（A）sql 語(yǔ)句

這里也可以使用 union all 加上 temporary 的方式創(chuàng)建臨時(shí)表, 詳情請(qǐng)看批量插入篇的創(chuàng)建臨時(shí)表的兩種方式

create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
insert into 
	tmp(id, name, title) 
values
		(1, '張三', '如何抵擋美食的誘惑?'),
		(2, '李四', '批判張三的《如何抵擋美食的誘惑?》'),
		(3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒'),
		(4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)') ;
update table_name, tmp set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;

（B）mapper 文件的 sql

<update id="batchUpdate"  parameterType="java.util.List">
	create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
	insert into tmp(id, name, title) values
	<foreach collection="list" index="index" item="item" separator=",">
		(#{item.id}, #{item.name}, #{item.title})
	</foreach>;
	update table_name, tmp set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;
</insert>

（3）批量更新(使用 select … union all… 創(chuàng)建臨時(shí)表)

類型：
	update 表名, (select ... union all ...) as tmp set ... where ...

注意： id=-1為數(shù)據(jù)庫(kù)一個(gè)不存在的主鍵id

（A）sql 語(yǔ)句

update table_name, (select id, name, title from table_name where id = -1 union all
select 1, '張三', '如何抵擋美食的誘惑?' union all
select 2, '李四', '批判張三的《如何抵擋美食的誘惑?》' union all
select 3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒' union all
select 4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)') as tmp 
set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;

（B）mapper 文件的 sql

<update id="batchUpdate"  parameterType="java.util.List">
	update table_name, (select id, name, title from table_name where id = -1 union all
	<foreach collection="list" index="index" item="item" separator=" union all ">
		select #{item.id}, #{item.name}, #{item.title}
	</foreach>) as tmp
	set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;
</insert>

3. replace into …批量更新(快)

實(shí)測(cè)：50*6500行數(shù)據(jù)耗時(shí)26至28秒

類型：
	 replace into ... values (...),(...),...

（1）sql 語(yǔ)句

replace into table_name(id, name, title)
values
		(1, '張三', '如何抵擋美食的誘惑?'),
		(2, '李四', '批判張三的《如何抵擋美食的誘惑?》'),
		(3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒'),
		(4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)') ;

（2）mapper 文件的 sql

<update id="batchUpdate"  parameterType="java.util.List">
	replace into table_name(id, name, title) values
	<foreach collection="list" index="index" item="item" separator=",">
	    (#{item.id}, #{item.name}, #{item.title})
	</foreach>
 </update>

4. insert into … on duplicate key … update …批量更新(快)

實(shí)測(cè)：50*6500行數(shù)據(jù)批量更新耗時(shí)27-29秒, 批量插入耗時(shí)9-12秒

類型：
	 insert into ... values (...),(...),...on duplicate key ... update ...

這句類型的SQL在遇到 duplicate key 時(shí)執(zhí)行更新操作, 否則執(zhí)行插入操作(時(shí)效略微慢一點(diǎn))

（1）sql 語(yǔ)句

insert into table_name(id, name, title)
values
		(1, '張三', '如何抵擋美食的誘惑?'),
		(2, '李四', '批判張三的《如何抵擋美食的誘惑?》'),
		(3, '王五', '會(huì)看鬼子進(jìn)村的那些不堪入目的事兒'),
		(4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)') 
on duplicate key update name=values(name), title=values(title);

（2）mapper 文件的 sql

<update id="batchUpdate"  parameterType="java.util.List">
	replace into table_name(id, name, title) values
	<foreach collection="list" index="index" item="item" separator=",">
	    (#{item.id}, #{item.name}, #{item.title})
	</foreach>
	on duplicate key update id= values(id);
 </update>