1亿条数据批量插入 MySQL，哪种方式最快？
利用JAVA向Mysql插入一亿数量级数据—效率测评
这几天研究mysql优化中查询效率时，发现测试的数据太少（10万级别），利用EXPLAIN比较不同的SQL语句，不能够得到比较有效的测评数据，大多模棱两可，不敢通过这些数据下定论。
所以通过随机生成人的姓名、年龄、性别、电话、email、地址，向mysql数据库大量插入数据，便于用大量的数据测试SQL语句优化效率。、在生成过程中发现使用不同的方法，效率天差万别。
1、先上Mysql数据库，随机生成的人员数据图。分别是ID、姓名、性别、年龄、Email、电话、住址。
下图一共三千三百万数据：
在数据量在亿级别时，别点下面按钮，会导致Navicat持续加载这亿级别的数据，导致电脑死机。~觉着自己电脑配置不错的可以去试试，可能会有惊喜
2、本次测评一共通过三种策略，五种情况，进行大批量数据插入测试
策略分别是：
测试结果：
Mybatis轻量级插入->JDBC直接处理->JDBC批处理。 JDBC批处理，效率最高 第一种策略测试： 2.1Mybatis轻量级框架插入（无事务）Mybatis是一个轻量级框架，它比hibernate轻便、效率高。
但是处理大批量的数据插入操作时，需要过程中实现一个ORM的转换，本次测试存在实例，以及未开启事务，导致mybatis效率很一般。
这里实验内容是：
本想测试插入五百万条数据，但是实际运行过程中太慢，中途不得不终止程序。最后得到52W数据，大约耗时两首歌的时间（7~9分钟）。随后，利用mybatis向mysql插入
结果如下：
利用mybatis插入一万条数据耗时：28613，即28.6秒
第二种策略测试： 2.2采用JDBC直接处理（开启事务、关闭事务）采用JDBC直接处理的策略，这里的实验内容分为开启事务、未开启事务是两种，过程均如下：
事务对于插入数据有多大的影响呢？ 看下面的实验结果:
1、我们首先利用上述代码测试无事务状态下，插入10W条数据需要耗时多少。
如图：
实验结论如下：
在未开启事务的情况下，平均每21.2秒插入一万数据。
接着我们测试开启事务后，插入十万条数据耗时，如图：
实验结论如下：
开启事务后，平均每3.9秒插入一万数据 第三种策略测试： 2.3采用JDBC批处理（开启事务、无事务）采用JDBC批处理时需要注意一下几点：
1、在URL连接时需要开启批处理、以及预编译
2、PreparedStatement预处理sql语句必须放在循环体外
代码如下：
实验结果：
使用JDBC批处理，未开启事务下，平均每2.1秒插入十万条数据
接着测试
开启事务，每次循环插入10W条数据，循环10次，一共100W条数据。
结果如下图：
实验结果：
使用JDBC批处理，开启事务，平均每1.9秒插入十万条数据 3
这几天研究mysql优化中查询效率时，发现测试的数据太少（10万级别），利用EXPLAIN比较不同的SQL语句，不能够得到比较有效的测评数据，大多模棱两可，不敢通过这些数据下定论。
所以通过随机生成人的姓名、年龄、性别、电话、email、地址，向mysql数据库大量插入数据，便于用大量的数据测试SQL语句优化效率。、在生成过程中发现使用不同的方法，效率天差万别。
1、先上Mysql数据库，随机生成的人员数据图。分别是ID、姓名、性别、年龄、Email、电话、住址。
下图一共三千三百万数据：
在数据量在亿级别时，别点下面按钮，会导致Navicat持续加载这亿级别的数据，导致电脑死机。~觉着自己电脑配置不错的可以去试试，可能会有惊喜
2、本次测评一共通过三种策略，五种情况，进行大批量数据插入测试
策略分别是：
- Mybatis轻量级框架插入（无事务）
- 采用JDBC直接处理（开启事务、无事务）
- 采用JDBC批处理（开启事务、无事务）
测试结果：
Mybatis轻量级插入->JDBC直接处理->JDBC批处理。 JDBC批处理，效率最高 第一种策略测试： 2.1Mybatis轻量级框架插入（无事务）Mybatis是一个轻量级框架，它比hibernate轻便、效率高。
但是处理大批量的数据插入操作时，需要过程中实现一个ORM的转换，本次测试存在实例，以及未开启事务，导致mybatis效率很一般。
这里实验内容是：
- 利用Spring框架生成mapper实例、创建人物实例对象
- 循环更改该实例对象属性、并插入。
//代码内无事务
privatelongbegin=33112001;//起始id
privatelongend=begin+100000;//每次循环插入的数据量
privateStringurl="jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
privateStringuser="root";
privateStringpassword="0203";
@org.junit.Test
publicvoidinsertBigData2()
{
//加载Spring，以及得到PersonMapper实例对象。这里创建的时间并不对最后结果产生很大的影响
ApplicationContextcontext=newClassPathXmlApplicationContext("applicationContext.xml");
PersonMapperpMapper=(PersonMapper)context.getBean("personMapper");
//创建一个人实例
Personperson=newPerson();
//计开始时间
longbTime=System.currentTimeMillis();
//开始循环，循环次数500W次。
for(inti=0;i<5000000;i++)
{
//为person赋值
person.setId(i);
person.setName(RandomValue.getChineseName());
person.setSex(RandomValue.name_sex);
person.setAge(RandomValue.getNum(1,100));
person.setEmail(RandomValue.getEmail(4,15));
person.setTel(RandomValue.getTel());
person.setAddress(RandomValue.getRoad());
//执行插入语句
pMapper.insert(person);
begin++;
}
//计结束时间
longeTime=System.currentTimeMillis();
System.out.println("插入500W条数据耗时："+(eTime-bTime));
}
本想测试插入五百万条数据，但是实际运行过程中太慢，中途不得不终止程序。最后得到52W数据，大约耗时两首歌的时间（7~9分钟）。随后，利用mybatis向mysql插入
10000数据。
结果如下：
利用mybatis插入一万条数据耗时：28613，即28.6秒
第二种策略测试： 2.2采用JDBC直接处理（开启事务、关闭事务）采用JDBC直接处理的策略，这里的实验内容分为开启事务、未开启事务是两种，过程均如下：
- 利用PreparedStatment预编译
- 循环，插入对应数据，并存入
事务对于插入数据有多大的影响呢？ 看下面的实验结果:
//该代码为开启事务
privatelongbegin=33112001;//起始id
privatelongend=begin+100000;//每次循环插入的数据量
privateStringurl="jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
privateStringuser="root";
privateStringpassword="0203";
@org.junit.Test
publicvoidinsertBigData3(){
//定义连接、statement对象
Connectionconn=null;
PreparedStatementpstm=null;
try{
//加载jdbc驱动
Class.forName("com.mysql.jdbc.Driver");
//连接mysql
conn=DriverManager.getConnection(url,user,password);
//将自动提交关闭
conn.setAutoCommit(false);
//编写sql
Stringsql="INSERTINTOpersonVALUES(?,?,?,?,?,?,?)";
//预编译sql
pstm=conn.prepareStatement(sql);
//开始总计时
longbTime1=System.currentTimeMillis();
//循环10次，每次一万数据，一共10万
for(inti=0;i<10;i++){
//开启分段计时，计1W数据耗时
longbTime=System.currentTimeMillis();
//开始循环
while(begin<end){
//赋值
pstm.setLong(1,begin);
pstm.setString(2,RandomValue.getChineseName());
pstm.setString(3,RandomValue.name_sex);
pstm.setInt(4,RandomValue.getNum(1,100));
pstm.setString(5,RandomValue.getEmail(4,15));
pstm.setString(6,RandomValue.getTel());
pstm.setString(7,RandomValue.getRoad());
//执行sql
pstm.execute();
begin++;
}
//提交事务
conn.commit();
//边界值自增10W
end+=10000;
//关闭分段计时
longeTime=System.currentTimeMillis();
//输出
System.out.println("成功插入1W条数据耗时："+(eTime-bTime));
}
//关闭总计时
longeTime1=System.currentTimeMillis();
//输出
System.out.println("插入10W数据共耗时："+(eTime1-bTime1));
}catch(SQLExceptione){
e.printStackTrace();
}catch(ClassNotFoundExceptione1){
e1.printStackTrace();
}
}
1、我们首先利用上述代码测试无事务状态下，插入10W条数据需要耗时多少。
如图：
成功插入1W条数据耗时：21603
成功插入1W条数据耗时：20537
成功插入1W条数据耗时：20470
成功插入1W条数据耗时：21160
成功插入1W条数据耗时：23270
成功插入1W条数据耗时：21230
成功插入1W条数据耗时：20372
成功插入1W条数据耗时：22608
成功插入1W条数据耗时：20361
成功插入1W条数据耗时：20494
插入10W数据共耗时：212106
实验结论如下：
在未开启事务的情况下，平均每21.2秒插入一万数据。
接着我们测试开启事务后，插入十万条数据耗时，如图：
成功插入1W条数据耗时：4938
成功插入1W条数据耗时：3518
成功插入1W条数据耗时：3713
成功插入1W条数据耗时：3883
成功插入1W条数据耗时：3872
成功插入1W条数据耗时：3873
成功插入1W条数据耗时：3863
成功插入1W条数据耗时：3819
成功插入1W条数据耗时：3933
成功插入1W条数据耗时：3811
插入10W数据共耗时：39255
实验结论如下：
开启事务后，平均每3.9秒插入一万数据 第三种策略测试： 2.3采用JDBC批处理（开启事务、无事务）采用JDBC批处理时需要注意一下几点：
1、在URL连接时需要开启批处理、以及预编译
Stringurl=“jdbc:mysql://localhost:3306/User?rewriteBatched-Statements=true&useServerPrepStmts=false”;
2、PreparedStatement预处理sql语句必须放在循环体外
代码如下：
privatelongbegin=33112001;//起始id
privatelongend=begin+100000;//每次循环插入的数据量
privateStringurl="jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
privateStringuser="root";
privateStringpassword="0203";
@org.junit.Test
publicvoidinsertBigData(){
//定义连接、statement对象
Connectionconn=null;
PreparedStatementpstm=null;
try{
//加载jdbc驱动
Class.forName("com.mysql.jdbc.Driver");
//连接mysql
conn=DriverManager.getConnection(url,user,password);
//将自动提交关闭
//conn.setAutoCommit(false);
//编写sql
Stringsql="INSERTINTOpersonVALUES(?,?,?,?,?,?,?)";
//预编译sql
pstm=conn.prepareStatement(sql);
//开始总计时
longbTime1=System.currentTimeMillis();
//循环10次，每次十万数据，一共1000万
for(inti=0;i<10;i++){
//开启分段计时，计1W数据耗时
longbTime=System.currentTimeMillis();
//开始循环
while(begin<end){
//赋值
pstm.setLong(1,begin);
pstm.setString(2,RandomValue.getChineseName());
pstm.setString(3,RandomValue.name_sex);
pstm.setInt(4,RandomValue.getNum(1,100));
pstm.setString(5,RandomValue.getEmail(4,15));
pstm.setString(6,RandomValue.getTel());
pstm.setString(7,RandomValue.getRoad());
//添加到同一个批处理中
pstm.addBatch();
begin++;
}
//执行批处理
pstm.executeBatch();
//提交事务
//conn.commit();
//边界值自增10W
end+=100000;
//关闭分段计时
longeTime=System.currentTimeMillis();
//输出
System.out.println("成功插入10W条数据耗时："+(eTime-bTime));
}
//关闭总计时
longeTime1=System.currentTimeMillis();
//输出
System.out.println("插入100W数据共耗时："+(eTime1-bTime1));
}catch(SQLExceptione){
e.printStackTrace();
}catch(ClassNotFoundExceptione1){
e1.printStackTrace();
}
}
成功插入10W条数据耗时：3832
成功插入10W条数据耗时：1770
成功插入10W条数据耗时：2628
成功插入10W条数据耗时：2140
成功插入10W条数据耗时：2148
成功插入10W条数据耗时：1757
成功插入10W条数据耗时：1767
成功插入10W条数据耗时：1832
成功插入10W条数据耗时：1830
成功插入10W条数据耗时：2031
插入100W数据共耗时：21737
实验结果：
使用JDBC批处理，未开启事务下，平均每2.1秒插入十万条数据
接着测试
开启事务，每次循环插入10W条数据，循环10次，一共100W条数据。
结果如下图：
成功插入10W条数据耗时：3482
成功插入10W条数据耗时：1776
成功插入10W条数据耗时：1979
成功插入10W条数据耗时：1730
成功插入10W条数据耗时：1643
成功插入10W条数据耗时：1665
成功插入10W条数据耗时：1622
成功插入10W条数据耗时：1624
成功插入10W条数据耗时：1779
成功插入10W条数据耗时：1698
插入100W数据共耗时：19003
实验结果：
使用JDBC批处理，开启事务，平均每1.9秒插入十万条数据 3
你可能想看：