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

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

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

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

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

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

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

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

批量插入篇

1. 多條insert語句(快)

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

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

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

特別注意：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 語句

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, '王五', '會看鬼子進(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. 多個values語句(快)

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

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

（1）sql 語句

insert into 
	table_name(id, name, title) 
values
		(1, '張三', '如何抵擋美食的誘惑?'),
		(2, '李四', '批判張三的《如何抵擋美食的誘惑?》'),
		(3, '王五', '會看鬼子進(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)建臨時表(快)

實測：50*6500行數(shù)據(jù)耗時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)建臨時表的原理，先創(chuàng)建整張臨時表到內(nèi)存中，然后將整張臨時表導(dǎo)入數(shù)據(jù)庫，連接關(guān)閉時即銷毀臨時表，其他的不多說，可自行了解。

（1）sql 語句

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

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

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

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, '王五', '會看鬼子進(jìn)村的那些不堪入目的事兒' 
union all
select 
	4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)';

b. 創(chuàng)建臨時表方式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, '王五', '會看鬼子進(jìn)村的那些不堪入目的事兒' 
union all
select 
	4, '趙柳', 'Java該怎樣高效率學(xué)習(xí)';

c. 創(chuàng)建臨時表方式3 - 使用 temporary + 多個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, '王五', '會看鬼子進(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語句批量更新(快)

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

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

由于批量更新存在條件判斷，所以整體上時效上沒有批量插入那么高（下面是手動提交事務(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 語句

start transaction;
update table_name set name = '張三', title = 'springboot如何入門' where id = 1;
update table_name set name = '李四', title = 'JVM到底是怎樣運行的' where id = 2;
update table_name set name = '王五', title = '并發(fā)編程你需要注意什么' where id = 3;
update table_name set name = '趙柳', title = '別讓一時的貪成為你不努力的理由' 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)建臨時表批量更新(快)

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

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

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

（A）sql 語句

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

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, '王五', '會看鬼子進(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)建臨時表)

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

（A）sql 語句

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

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, '王五', '會看鬼子進(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)建臨時表)

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

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

（A）sql 語句

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, '王五', '會看鬼子進(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 …批量更新(快)

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

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

（1）sql 語句

replace into table_name(id, name, title)
values
		(1, '張三', '如何抵擋美食的誘惑?'),
		(2, '李四', '批判張三的《如何抵擋美食的誘惑?》'),
		(3, '王五', '會看鬼子進(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 …批量更新(快)

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

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

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

（1）sql 語句

insert into table_name(id, name, title)
values
		(1, '張三', '如何抵擋美食的誘惑?'),
		(2, '李四', '批判張三的《如何抵擋美食的誘惑?》'),
		(3, '王五', '會看鬼子進(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>