数据库批量数据插入问题分析

最新推荐文章于 2025-09-11 14:55:07 发布
转载 最新推荐文章于 2025-09-11 14:55:07 发布 · 843 阅读 · 1
标签
#mysql #MySQL #Mysql #MYSQL #MySql
#批量 #插入 #数据
本文探讨了在MySQL数据库中进行大规模数据插入的各种方法及其性能表现，包括普通插入、批量插入及多线程插入等，并对比了不同存储引擎的效果。

【转载地址】http://www.blogjava.net/hankchen/archive/2009/09/01/293430.html

在数据库的相关开发中，经常会遇到数据的批量插入问题。本文主要是通过实验的方式探讨批量数据插入的瓶颈，以及优化建议。


以10w条记录的插入作为实验对象，采用下面的几种方法插入：

1.       普通插入：普通的一条条插入

2.       普通插入+手动提交：setAutoCommit(false)、commit（）

3.       普通插入+手动提交+ prepareStatement方式

4.       批量插入：addBatch、executeBatch

5.       批量插入：insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,

6.       多线程插入。

7.       InnoDB引擎和MyISAM引擎的比较。


实验环境：

数据库：MySQL 5.0

机器硬件：

内存 3G 

CPU AMD双核4400+ 2.3G

首先建立一个简单的user表：

CREATE TABLE `user` (                                         

          `id` varchar(50) NOT NULL,                                  

          `seqid` bigint(20) NOT NULL auto_increment,                 

          `name` varchar(50) NOT NULL,                                 

          PRIMARY KEY (`seqid`)

        ) ENGINE=MyISAM DEFAULT CHARSET=utf8

一、普通插入
代码：

 1 Connection conn = source.getConnection();
  2 Statement s = conn.createStatement();
  3 String sql = "" ;
  4 long  start = System.currentTimeMillis();
  5 for ( int  i = 0 ;i < 100000 ;i ++ )
  6 {
 7    sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";
 8    s.execute(sql);
 9}
10 s.close();
 11 conn.close();
 12 long  end = System.currentTimeMillis();
 13 System.out.println( " commonInsert()执行时间为： " + (end - start) + " ms " );
输出结果：
commonInsert()执行时间为：13828ms

二、普通插入+手动提交：setAutoCommit(false)、commit()
代码：
 1 Connection conn = source.getConnection();
  2 conn.setAutoCommit( false );
  3 Statement s = conn.createStatement();
  4 String sql = "" ;
  5 long  start = System.currentTimeMillis();
  6 for ( int  i = 0 ;i < 100000 ;i ++ )
  7 {
 8   sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";
 9   s.execute(sql);
10}
11 conn.commit();
 12 s.close();
 13 conn.close();
 14 long  end = System.currentTimeMillis();
 15 System.out.println( " commonInsert()执行时间为： " + (end - start) + " ms " );
输出结果：
commonInsert()执行时间为：13813ms
对比分析：
可以看出，仅仅是这种方式的设置，对性能的影响并不大。

三、 普通插入 + 手动提交 +  prepareStatement 方式
代码：
 1 Connection conn = source.getConnection();
  2         conn.setAutoCommit( false );
  3         PreparedStatement ps = conn.prepareStatement( " insert into user(id,name) value(?,?) " );
  4          long  start = System.currentTimeMillis();
  5          for ( int  i = 0 ;i < 100000 ;i ++ )
  6          {
 7            ps.setString(1,(i+1)+"");
 8            ps.setString(2,"chenxinhan");
 9            ps.execute();
10        }
11         conn.commit();
 12         ps.close();
 13         conn.close();
 14          long  end = System.currentTimeMillis();
 15         System.out.println( " prepareStatementInsert()执行时间为： " + (end - start) + " ms " );
输出结果：
prepareStatementInsert()执行时间为：12797ms
对比分析：
采用prepareStatement的方式确实可以提高一点性能，因为减少了数据库引擎解析优化SQL语句的时间，但是由于现在的插入语句太简单，所以性能提升不明显。

四、 批量插入： addBatch 、 executeBatch
代码：
 1         Connection conn = source.getConnection();
  2         conn.setAutoCommit( false );
  3         Statement s = conn.createStatement();
  4         String sql = "" ;
  5          long  start = System.currentTimeMillis();
  6          for ( int  j = 0 ;j < 100 ;j ++ )
  7          {
 8            for(int i=0;i<1000;i++)
 9            {
10                sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";
11                s.addBatch(sql);
12            }
13            s.executeBatch();
14            conn.commit();
15            s.clearBatch();
16        }
17         s.close();
 18         conn.close();
 19          long  end = System.currentTimeMillis();
 20         System.out.println( " batchInsert()执行时间为： " + (end - start) + " ms " );
输出结果：
batchInsert()执行时间为：13625ms
对比分析：
按道理，这种批处理的方式是要快些的，但是测试结果却不尽人意，有点不解，请高人拍砖。

五、 批量插入： insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,
代码：
 1 Connection conn = source.getConnection();
  2         conn.setAutoCommit( false );
  3         Statement s = conn.createStatement();
  4         StringBuilder sql = new  StringBuilder( "" );
  5          long  start = System.currentTimeMillis();
  6          for ( int  j = 0 ;j < 100 ;j ++ )
  7          {
 8            sql=new StringBuilder("");
 9            sql.append("insert into user(id,name) values ");
10            for(int i=0;i<1000;i++)
11            {
12                if(i==999)
13                    sql.append("('").append(i+1).append("',").append("'cxh')");
14                else
15                    sql.append("('").append(i+1).append("',").append("'cxh'),");
16            }
17            s.execute(sql.toString());
18            conn.commit();
19        }
20         s.close();
 21         conn.close();
 22          long  end = System.currentTimeMillis();
 23         System.out.println( " manyInsert()执行时间为： " + (end - start) + " ms " );
输出结果：
manyInsert()执行时间为：937ms
对比分析：
发现采用这种方式的批量插入性能提升最明显，有10倍以上的性能提升。所以这种方式是我推荐的批量插入方式！
六、 多线程插入
在第五种方式的基础上采用多线程插入。
代码：
 1 final  Connection conn = source.getConnection();
  2          for ( int  j = 0 ;j < 3 ;j ++ )
  3          {
 4            Thread t=new Thread(){
 5                @Override
 6                public void run() {
 7                    try
 8                    {
 9                        long start=System.currentTimeMillis();
10                        Statement s=conn.createStatement();
11                        StringBuilder sql=new StringBuilder("");
12                        for(int j=0;j<100;j++)
13                        {
14                            conn.setAutoCommit(false);
15                            sql=new StringBuilder("");
16                            sql.append("insert into user (id,name) values ");
17                            for(int i=0;i<1000;i++)
18                            {
19                                if(i==999)
20                                    sql.append("('").append(i+1).append("',").append("'cxh')");
21                                else
22                                    sql.append("('").append(i+1).append("',").append("'cxh'),");
23                            }
24                            s.execute(sql.toString());
25                            conn.commit();
26                        }
27                        s.close();
28                        long end=System.currentTimeMillis();
29                        System.out.println("multiThreadBatchInsert()执行时间为："+(end-start)+"ms");
30                    }
31                    catch(Exception e)
32                    {
33                        e.printStackTrace();
34                    }
35                }
36            };
37            t.start();
38            //t.join();
39        }
输出结果：
multiThreadBatchInsert()执行时间为：2437ms
multiThreadBatchInsert()执行时间为：2625ms
multiThreadBatchInsert()执行时间为：2703ms

注意：上面我采用的是三个线程插入30w条数据。
取最大时间为2703ms，较上面的937ms，基本还是三倍的时间。
所以发现此时多线程也解决不了批量数据插入问题。原因就是，这时候的性能瓶颈不是CPU，而是数据库！

七、 InnoDB 引擎和 MyISAM 引擎的比较
最后，分析一下，这两个引擎对批量数据插入的影响。
先建立user2数据表：

CREATE TABLE `user2` (                                         

          `id` varchar(50) NOT NULL,                                  

          `seqid` bigint(20) NOT NULL auto_increment,                 

          `name` varchar(50) NOT NULL,                                 

          PRIMARY KEY (`seqid`)

        ) ENGINE=InnoDB DEFAULT CHARSET=utf8

代码：
 1 Connection conn = source.getConnection();
  2         conn.setAutoCommit( false );
  3         Statement s = conn.createStatement();
  4         StringBuilder sql = new  StringBuilder( "" );
  5          long  start = System.currentTimeMillis();
  6          for ( int  j = 0 ;j < 100 ;j ++ )
  7          {
 8            sql=new StringBuilder("");
 9            sql.append("insert into user2 (id,name) values ");
10            for(int i=0;i<1000;i++)
11            {
12                if(i==999)
13                    sql.append("('").append(i+1).append("',").append("'cxh')");
14                else
15                    sql.append("('").append(i+1).append("',").append("'cxh'),");
16            }
17            s.execute(sql.toString());
18            conn.commit();
19        }
20         s.close();
 21         conn.close();
 22          long  end = System.currentTimeMillis();
 23         System.out.println( " manyInsert2()执行时间为： " + (end - start) + " ms " );
输出结果：
manyInsert2()执行时间为：3484ms
注意：第七项的代码和第五是一样的，除了数据表名称不同（user、user2）
但是，
InnoDB ：3484ms
MyISAM：937ms
所以， MyISAM引擎对大数据量的插入性能较好。

总结：
对于大数据量的插入，建议使用insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,的方式，引擎建议使用MyISAM引擎。
友情提醒：本博文章欢迎转载，但请注明出处： 陈新汉
项目中数据库插入大批量数据遇到的问题
yang_leiting的博客
07-22 750
项目中数据库插入大批量数据遇到的问题
解决批量插入的时候数量过多引起的错误
chen's blog
07-16 969
传入一个list，对list进行分批插入 public void insertListNew(List list) { int insertLength = list.size(); int i = 0; while (insertLength > 1000) { zdyWechatMembersMapper.insertLiangxiangBatch(list.subList(i, i + 1000)); i = i + 1000; insertLength = insertLength - 100
INFORMIX数据库常用命令
lingyun1987的博客
03-24 4077
informix常用操作
INFORMIX SQL 介绍
我的夹子
08-30 1544
作者: springwind  一、 简介： 1）、INFORMIX主要产品分为三大部分： ?; 数据库服务器(数据库核心) ?; 应用开发工具 ?; 网络数据库互联产品 2）、数据库服务器有两种，作用都是提供数据操作和管理： ?; SE：完全基UNIX操作系统，主要针对非多媒体的较少用户数的应用 ?; ONLINE：针对大量用户的联机事务处理和多媒体应用环境 3）、应用开发工具是用以开发应用程序
mysql数据库批量插数死锁_MySQL 死锁套路：一次诡异的批量插入死锁问题分析
weixin_36222664的博客
01-26 1342
线上最近出现了批量insert的死锁，百思不得姐。死锁记录如下2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB:*** (1) TRANSACTION:TRANSACTION 1202026765, ACTIVE 0 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struc...
数据库批量插入和存在的问题
huhu123444的博客
07-10 1233
mysql批量操作和注意事项
mybatisPlus批量插入性能优化
少年Dream
08-15 8552
**//更新时自动填充的字段，不用插入值 methodList . add(new InsertBatchSomeColumn(i -> i . getFieldFill()!} }/**} }/**/*** 真正的批量插入* @return}}最后直接调用UserMapper的insertBatchSomeColumn()方法即可实现真正的批量插入。i < 5;i ++) {}查看日志输出信息，观察执行的sql语句，...
SqlServer数据库批量插入数据报错
最新发布
casually的博客
09-11 382
SqlServer数据库批量插入数据时，为什么会报错？本篇文章给出答案。
批量与分段插入insert数据到数据库中
weixin_42585386的博客
03-31 1563
问题现象： 项目中的一个程序，其中包括了插入数据库的逻辑，但由于是单条数据插入，所以入库就很慢，利用批量插入提高速度。 问题分析： 1. 插入慢 单数据插入数据库很慢，因为需要多次访问数据库，而批量插入则只需要访问一次数据库即可，所以会快很多。 2. 数量限制 所有的数据库（Oracle，Mysql，SQLServer，Postgresql，SQLite等等）都有自己的入库数量限制，这个就需要测试和验证得知了！ 解决方法： 应用： 1. 批量入库方法 First ： impor.
TDengine 如何批量插入？
慢慢来
07-13 6851
【本文正在参与 “拥抱开源 | 涛思数据 TDengine有奖征稿】拥抱开源 涛思数据 TDengine有奖征稿活动-CSDN 链接：https://marketing.csdn.net/p/0ada836ca30caa924b9baae0fd33857c 文章目录正常插入批量插入解决错误查询官方文档常见问题官方文档连接器官方文档高效写入数据官方文档最终结论修改后的代码       最近在使用涛思旗下tdengine数据库的时候，因为需求，需要
达梦数据库中jdbc接口的大批量插入数据的写法推荐
飞奔的屎壳郎
02-27 2932
通过查看sqllog日志，发现saveBatch接口使用PreparedStatement预编译多条insert语句 然后executeBatch提交。eg：在DM中没有rewriteBatchedStatements 参数，因为跟mysql的驱动内部的executeBatch()函数实现不同。使用mysql数据库的话通常使用的这种写法。这种写法会导致sql语句会非常大，不仅容易超出的sql长度限制，也会容易造成oom。使用预处理的方式进行批量插入是一种常见的优化方法，它可以显著提高插入操作的性能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值