设为首页收藏本站
网站公告 | 这是第一条公告
     

 找回密码
 立即注册
缓存时间22 现在时间22 缓存数据 一个女孩所能发的最大的脾气就是如同哑巴不开口说话。晚安！

一个女孩所能发的最大的脾气就是如同哑巴不开口说话。晚安！

晓枫资讯--科技资讯社区»论坛 › 数据库，网络编程。 › 数据库。›快速切换版块 › MySQL的批量更新和批量新增优化方式
返回列表 发新帖
查看: 291|回复: 0

MySQL的批量更新和批量新增优化方式

[复制链接]
聂梓菱

  离线 

TA的专栏

  • 打卡等级：即来则安
  • 打卡总天数：20
  • 打卡月天数：0
  • 打卡总奖励：224
  • 最近打卡：2025-04-19 16:18:25
等级头衔

等級：晓枫资讯-上等兵

在线时间
0 小时

积分成就
威望
0 点
贡献
315 次
主题
265次
精华
0 篇
金钱
1101 枚
积分
620枚
、
注册时间
2023-2-11
最后登录
2025-6-4
发表于 2025-5-31 06:41:37 | 显示全部楼层 |阅读模式
MySQL的批量更新和批量插入优化

如果需要批量插入和批量更新操作就需要进行sql 的优化，否则近30万条数据的插入或更新就会耗费几分钟甚至更多的时间， 此文仅批量插入和批量更新的几种优化。

  • 批量插入篇（使用多条
    1. insert
    复制代码
    语句、使用
    1. union all
    复制代码
    创建临时表、使用多个values）；
  • 批量更新篇（使用多条
    1. update
    复制代码
    语句、使用
    1. union all
    复制代码
    创建临时表创建临时表、使用
    1. replace into
    复制代码
    、使用
    1. insert ... on duplicate key ... update...
    复制代码
    ）。
如果有需要的同僚可根据下列内容使用jdbcTemplate和Java反射技术将其封装。
特别提示：做批量操作时，请限制每次1000-2000条数据，以避免GC和OOM。后期也会贴出相关代码，欢迎指正优化或提供其它更好的方法。

批量插入篇


1. 多条insert语句(快)

实测：50*6500行数据耗时8-12秒，如果不是手动提交事务，耗时约70-180秒
  1. 类型：
  2.         insert into table_name(id,name,title) values(?, ?, ?);
复制代码
常用的插入操作就是批量执行1条insert类型的SQL语句，这样的语句在执行大量的插入数据时， 其效率低下就暴露出来了。
特别注意：jdbc.url需要加上：
  1. allowMultiQueries=true
复制代码
  1. jdbc.url = jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true
复制代码
（1）sql 语句
  1. start transaction;
  2. insert into table_name(id, name, title) values(1, '张三', '如何抵挡美食的诱惑?');
  3. insert into table_name(id, name, title) values(2, '李四', '批判张三的《如何抵挡美食的诱惑?》');
  4. insert into table_name(id, name, title) values(3, '王五', '会看鬼子进村的那些不堪入目的事儿');
  5. insert into table_name(id, name, title) values(4, '赵柳', 'Java该怎样高效率学习');
  6. commit;
复制代码
（2）mapper 文件的 sql
  1. <insert id="batchSave"  parameterType="java.util.List">
  2.         start transaction;
  3.         <foreach collection="list" index="index" item="item">
  4.                 insert into table_name(id, name, title) values(#{item.id}, #{item.name}, #{item.title});
  5.                
  6.         </foreach>
  7.         commit;
  8. </insert>
复制代码
2. 多个values语句(快)

实测：50*6500行数据耗时6至10秒（与服务器的有关）
  1. 类型：
  2.         insert into table_name(id, name, title) values(?, ?, ?), ..., (?, ?, ?);
复制代码
（1）sql 语句
  1. insert into
  2.         table_name(id, name, title)
  3. values
  4.                 (1, '张三', '如何抵挡美食的诱惑?'),
  5.                 (2, '李四', '批判张三的《如何抵挡美食的诱惑?》'),
  6.                 (3, '王五', '会看鬼子进村的那些不堪入目的事儿'),
  7.                 (4, '赵柳', 'Java该怎样高效率学习');
复制代码
（2）mapper 文件的 sql
  1. <insert id="batchSave"  parameterType="java.util.List">
  2.         insert into table_name(id, name, title) values
  3.         <foreach collection="list" index="index" item="item" separator=", ">
  4.             (#{item.id}, #{item.name}, #{item.title})
  5.         </foreach>
  6. </insert>
复制代码
3. 使用union all 创建临时表(快)

实测：50*6500行数据耗时6至10秒（与服务器的有关）
  1. 类型：
  2.         insert into table_name(id,name,title) select ?, ?, ? union all select ?, ?, ? union all ...
复制代码
union all 在这里
  1. select ?, ?, ? union all select ?, ?, ? union all ...
复制代码
是创建临时表的原理，先创建整张临时表到内存中，然后将整张临时表导入数据库，连接关闭时即销毁临时表，其他的不多说，可自行了解。
（1）sql 语句
  1. insert into  
  2.         table_name(id, name, title)
  3. select
  4.         1, '张三', '如何抵挡美食的诱惑?'
  5. union all
  6. select
  7.         2, '李四', '批判张三的《如何抵挡美食的诱惑?》'
  8. union all
  9. select
  10.         3, '王五', '会看鬼子进村的那些不堪入目的事儿'
  11. union all
  12. select
  13.         4, '赵柳', 'Java该怎样高效率学习';
复制代码
a. 创建临时表方式1 - 使用 temporary + union all
简单列举三种创建临时表的方式, 这里熟悉了
  1. temporary
复制代码
、
  1. select ?, ? ,? union all select ?, ?, ?
复制代码
和 、
  1. insert into ... values(?, ?, ?), (?, ?, ?), (?, ?, ?)...
复制代码
之后，都可以组合创建临时表， 效率几乎差不多。个人更加偏向第二种，因为简单方便。
  1. create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
  2. SELECT id, name, title FROM tmp
  3. union all
  4. select
  5.         1, '张三', '如何抵挡美食的诱惑?'
  6. union all
  7. select
  8.         2, '李四', '批判张三的《如何抵挡美食的诱惑?》'
  9. union all
  10. select
  11.         3, '王五', '会看鬼子进村的那些不堪入目的事儿'
  12. union all
  13. select
  14.         4, '赵柳', 'Java该怎样高效率学习';
复制代码
1.png

b. 创建临时表方式2 - 使用 select + union all
  1. select
  2.         id, name, title from table_name where id = -1
  3. union all
  4. select
  5.         1, '张三', '如何抵挡美食的诱惑?'
  6. union all
  7. select
  8.         2, '李四', '批判张三的《如何抵挡美食的诱惑?》'
  9. union all
  10. select
  11.         3, '王五', '会看鬼子进村的那些不堪入目的事儿'
  12. union all
  13. select
  14.         4, '赵柳', 'Java该怎样高效率学习';
复制代码
2.png

c. 创建临时表方式3 - 使用 temporary + 多个insert values
  1. create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
  2. insert into
  3.         tmp(id, name, title)
  4. values
  5.                 (1, '张三', '如何抵挡美食的诱惑?'),
  6.                 (2, '李四', '批判张三的《如何抵挡美食的诱惑?》'),
  7.                 (3, '王五', '会看鬼子进村的那些不堪入目的事儿'),
  8.                 (4, '赵柳', 'Java该怎样高效率学习');
复制代码
3.png

（2）mapper 文件的 sql
  1. <insert id="batchSave"  parameterType="java.util.List">
  2.         insert into table_name(id, name, title)
  3.         <foreach collection="list" index="index" item="item" separator=" union all ">
  4.             select #{item.id}, #{item.name}, #{item.title}
  5.         </foreach>
  6. </insert>
复制代码
批量更新篇


1. 多条update语句批量更新(快)

实测：50*6500行数据耗时26-30秒，如果不是手动提交事务，耗时约70-180秒
  1. 类型：
  2.         update table_name set name = ?, title = ? where id = ?;
复制代码
由于批量更新存在条件判断，所以整体上时效上没有批量插入那么高（下面是手动提交事务的代码）。
特别注意：jdbc.url需要加上：
  1. allowMultiQueries=true
复制代码
  1. jdbc.url = jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true
复制代码
（1）sql 语句
  1. start transaction;
  2. update table_name set name = '张三', title = 'springboot如何入门' where id = 1;
  3. update table_name set name = '李四', title = 'JVM到底是怎样运行的' where id = 2;
  4. update table_name set name = '王五', title = '并发编程你需要注意什么' where id = 3;
  5. update table_name set name = '赵柳', title = '别让一时的贪成为你不努力的理由' where id = 4;
  6. commit;
复制代码
（2）mapper 文件的 sql
  1. <update id="batchUpdate"  parameterType="java.util.List">
  2.         start transaction;
  3.         <foreach collection="list" index="index" item="item">
  4.                 update table_name set name = #{item.id}, title = #{item.title} where id = #{item.id};
  5.                
  6.         </foreach>
  7.         commit;
  8. </update >
复制代码
2. 创建临时表批量更新(快)

实测：50*6500行数据耗时26至28秒
（1）批量更新(使用 temporary + select … union all … select …创建临时表)
  1. 类型：
  2.         create temporary table 临时表;
  3.         select id, name, title FROM 临时表 union all select ... union all ... select ...
复制代码
（A）sql 语句
这里也可以使用 union all 加上 temporary 的方式创建临时表, 详情请看批量插入篇的创建临时表的两种方式
  1. create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
  2. select id, name, title from tmp
  3. union all
  4. select
  5.         1, '张三', '如何抵挡美食的诱惑?'
  6. union all
  7. select
  8.         2, '李四', '批判张三的《如何抵挡美食的诱惑?》'
  9. union all
  10. select
  11.         3, '王五', '会看鬼子进村的那些不堪入目的事儿'
  12. union all
  13. select
  14.         4, '赵柳', 'Java该怎样高效率学习';
  15. update table_name, tmp set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;
复制代码
（B）mapper 文件的 sql
  1. <update id="batchUpdate"  parameterType="java.util.List">
  2.         create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
  3.         update table_name, (SELECT id, name, title FROM tmp union all
  4.         <foreach collection="list" index="index" item="item" separator=" union all ">
  5.                 select #{item.id}, #{item.name}, #{item.title}
  6.         </foreach>) as tmp
  7.         set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;
  8. </insert>
复制代码
（2）批量更新(使用 temporary + insert into values(…), (…)… 创建临时表)
  1. 类型：
  2.         create temporary table 临时表;
  3.         insert into values(...), (...)...;
  4.         update ... set ... where ...;
复制代码
（A）sql 语句
这里也可以使用 union all 加上 temporary 的方式创建临时表, 详情请看批量插入篇的创建临时表的两种方式
  1. create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
  2. insert into
  3.         tmp(id, name, title)
  4. values
  5.                 (1, '张三', '如何抵挡美食的诱惑?'),
  6.                 (2, '李四', '批判张三的《如何抵挡美食的诱惑?》'),
  7.                 (3, '王五', '会看鬼子进村的那些不堪入目的事儿'),
  8.                 (4, '赵柳', 'Java该怎样高效率学习') ;
  9. update table_name, tmp set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;
复制代码
（B）mapper 文件的 sql
  1. <update id="batchUpdate"  parameterType="java.util.List">
  2.         create temporary table tmp(id int(4) primary key,name varchar(50),title varchar(50));
  3.         insert into tmp(id, name, title) values
  4.         <foreach collection="list" index="index" item="item" separator=",">
  5.                 (#{item.id}, #{item.name}, #{item.title})
  6.         </foreach>;
  7.         update table_name, tmp set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;
  8. </insert>
复制代码
（3）批量更新(使用 select … union all… 创建临时表)
  1. 类型：
  2.         update 表名, (select ... union all ...) as tmp set ... where ...
复制代码
注意： id=-1为数据库一个不存在的主键id
（A）sql 语句
  1. update table_name, (select id, name, title from table_name where id = -1 union all
  2. select 1, '张三', '如何抵挡美食的诱惑?' union all
  3. select 2, '李四', '批判张三的《如何抵挡美食的诱惑?》' union all
  4. select 3, '王五', '会看鬼子进村的那些不堪入目的事儿' union all
  5. select 4, '赵柳', 'Java该怎样高效率学习') as tmp
  6. set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;
复制代码
（B）mapper 文件的 sql
  1. <update id="batchUpdate"  parameterType="java.util.List">
  2.         update table_name, (select id, name, title from table_name where id = -1 union all
  3.         <foreach collection="list" index="index" item="item" separator=" union all ">
  4.                 select #{item.id}, #{item.name}, #{item.title}
  5.         </foreach>) as tmp
  6.         set table_name.name=tmp.name, table_name.title= tmp.title where table_name.id = tmp.id;
  7. </insert>
复制代码
3. replace into …批量更新(快)

实测：50*6500行数据耗时26至28秒
  1. 类型：
  2.          replace into ... values (...),(...),...
复制代码
（1）sql 语句
  1. replace into table_name(id, name, title)
  2. values
  3.                 (1, '张三', '如何抵挡美食的诱惑?'),
  4.                 (2, '李四', '批判张三的《如何抵挡美食的诱惑?》'),
  5.                 (3, '王五', '会看鬼子进村的那些不堪入目的事儿'),
  6.                 (4, '赵柳', 'Java该怎样高效率学习') ;
复制代码
（2）mapper 文件的 sql
  1. <update id="batchUpdate"  parameterType="java.util.List">
  2.         replace into table_name(id, name, title) values
  3.         <foreach collection="list" index="index" item="item" separator=",">
  4.             (#{item.id}, #{item.name}, #{item.title})
  5.         </foreach>
  6. </update>
复制代码
4. insert into … on duplicate key … update …批量更新(快)

实测：50*6500行数据批量更新耗时27-29秒, 批量插入耗时9-12秒
  1. 类型：
  2.          insert into ... values (...),(...),...on duplicate key ... update ...
复制代码
这句类型的SQL在遇到 duplicate key 时执行更新操作, 否则执行插入操作(时效略微慢一点)
（1）sql 语句
  1. insert into table_name(id, name, title)
  2. values
  3.                 (1, '张三', '如何抵挡美食的诱惑?'),
  4.                 (2, '李四', '批判张三的《如何抵挡美食的诱惑?》'),
  5.                 (3, '王五', '会看鬼子进村的那些不堪入目的事儿'),
  6.                 (4, '赵柳', 'Java该怎样高效率学习')
  7. on duplicate key update name=values(name), title=values(title);
复制代码
（2）mapper 文件的 sql
  1. <update id="batchUpdate"  parameterType="java.util.List">
  2.         replace into table_name(id, name, title) values
  3.         <foreach collection="list" index="index" item="item" separator=",">
  4.             (#{item.id}, #{item.name}, #{item.title})
  5.         </foreach>
  6.         on duplicate key update id= values(id);
  7. </update>
复制代码
总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持晓枫资讯。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
晓枫资讯-科技资讯社区-免责声明
免责声明：以上内容为本网站转自其它媒体，相关信息仅为传递更多信息之目的，不代表本网观点，亦不代表本网站赞同其观点或证实其内容的真实性。
      1、注册用户在本社区发表、转载的任何作品仅代表其个人观点，不代表本社区认同其观点。
      2、管理员及版主有权在不事先通知或不经作者准许的情况下删除其在本社区所发表的文章。
      3、本社区的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，举报反馈：点击这里给我发消息进行删除处理。
      4、本社区一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
      5、以上声明内容的最终解释权归《晓枫资讯-科技资讯社区》所有。
http://bbs.yzwlo.com 晓枫资讯--游戏IT新闻资讯~~~
回复

举报 送花
返回列表 发新帖
严禁发布广告，淫秽、色情、赌博、暴力、凶杀、恐怖、间谍及其他违反国家法律法规的内容。！晓枫资讯-社区
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

手机版|晓枫资讯--科技资讯社区 本站已运行天

CopyRight © 2022-2025 晓枫资讯--科技资讯社区 ( BBS.yzwlo.com ) . All Rights Reserved .

晓枫资讯--科技资讯社区

本站内容由用户自主分享和转载自互联网，转载目的在于传递更多信息，并不代表本网赞同其观点和对其真实性负责。

如有侵权、违反国家法律政策行为，请联系我们，我们会第一时间及时清除和处理! 举报反馈邮箱：点击这里给我发消息

Powered by Discuz! X3.5

快速回复 返回顶部 返回列表