一.环境
- 数据库MySql 8.0.2
- Maven+IDEA
二.方法配置
- 目录结构
- POM.xml依赖
<dependencies>
<!--
https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<!--
https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.13</version>
</dependency>
<!--
https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
</dependencies>
- Mybatis配置mybati-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config
3.0//EN"
"<http://mybatis.org/dtd/mybatis-3-config.dtd>">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://192.168.20.10:3306/SCTest?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&rewriteBatchedStatements=true"/>
<property name="username" value="root"/>
<property name="password" value="17klop"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.sc.common.IInsertMapper"/>
</mappers>
</configuration>
- Mybatis Mapper代码(IInsertMapper)
package com.sc.common;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
@Mapper
public interface
IInsertMapper {
@Select("insert into
t_inserttest(IndexV)value(#{index})")
void insertBatch(int index);
}
- 实现类源代码
package com.sc.common;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Random;
public class MySqlHelper {
public MySqlHelper() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
}
//&rewriteBatchedStatements=true连接字符串必须添加这个配置,否则无法实现批量提交
private static final String DB_URL = "jdbc:mysql://192.168.20.10:3306/SCTest?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&rewriteBatchedStatements=true";
private static final String uid = "root";
private static final String pwd = "17klop";
private static final String insertSql="insert into
t_inserttest(IndexV)value(?)";
private static Connection connection = null;
//1.测试连接
public Boolean isConnect() throws Exception {
try {
connection = DriverManager.getConnection(DB_URL,uid,pwd);
return true;
} catch (Exception ex) {
System.out.println(ex.getMessage());
return false;
} finally {
if (connection != null) {
connection.close();
}
}
}
//2.测试AutoCommit=true;
//此次测试耗时11411ms
public void BatchByAutoCommit()throws Exception {
long start = System.currentTimeMillis();
try{
connection = DriverManager.getConnection(DB_URL,uid,pwd);
PreparedStatement ps = null;
ps=connection.prepareStatement(insertSql);
Random random = new Random();
for(int i=0;i<1000000;i++){
ps.setObject(1,new Integer(random.nextInt(1000000)));
ps.addBatch();
//每1000条数据提交一次,防止出现内存溢出的情况;
if (i % 999 == 0) {
ps.executeBatch();
ps.clearBatch();
}
}
//最后的一次也需要执行提交
ps.executeBatch();
ps.clearBatch();
}catch (Exception ex) {
System.out.println(ex.getMessage());
} finally {
if (connection != null) {
connection.close();
}
}
System.out.println("百万条数据插入用时:"
+ (System.currentTimeMillis()
- start)+"【单位:毫秒】");
}
//3.测试AutoCommit=false;
//此次耗时14084ms,根据测试,未发现AutoCommit设置与否对测试插入耗时结果的影响
public void BatchNoAutoCommit()throws Exception {
long start = System.currentTimeMillis();
try{
connection = DriverManager.getConnection(DB_URL,uid,pwd);
connection.setAutoCommit(false);
PreparedStatement ps = null;
ps=connection.prepareStatement(insertSql);
Random random = new Random();
for(int i=0;i<1000000;i++){
ps.setObject(1,new Integer(random.nextInt(1000000)));
ps.addBatch();
//每1000条数据提交一次,防止出现内存溢出的情况;
if (i % 999 == 0) {
ps.executeBatch();
ps.clearBatch();
}
}
//最后的一次也需要执行提交
ps.executeBatch();
ps.clearBatch();
}catch (Exception ex) {
System.out.println(ex.getMessage());
} finally {
if (connection != null) {
connection.close();
}
}
System.out.println("百万条数据插入用时:"
+ (System.currentTimeMillis()
- start)+"【单位:毫秒】");
}
//4.测试使用Mybatis批量提交
//此方法耗时1202058ms合计20分钟(setAutoCommit(true))
//此方法耗时422900ms合计7分钟(setAutoCommit(false)),速度提升了3倍
public void BatchByMybatis() throws Exception{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 创建SqlSession对象
SqlSession sqlSession =
sqlSessionFactory.openSession();
sqlSession.getConnection().setAutoCommit(false);
// 获取UserMapper对象
IInsertMapper iInsertMapper =
sqlSession.getMapper(IInsertMapper.class);
Random random = new Random();
long start = System.currentTimeMillis();
try {
for (int i = 0; i < 1000000; i++) {
iInsertMapper.insertBatch(new Integer(random.nextInt(1000000)));
//每1000条数据提交一次,防止出现内存溢出的情况;
if (i % 999 == 0) {
sqlSession.commit();
sqlSession.clearCache();
}
}
sqlSession.commit();
sqlSession.clearCache();
}catch (Exception ex){
sqlSession.rollback();
}
finally {
sqlSession.close();
}
System.out.println("百万条数据插入用时:"
+ (System.currentTimeMillis()
- start)+"【单位:毫秒】");
}
//5.使用Mybatis实现原生的批量提交
//此方法耗时14044ms
public void BatchByMyBatisUseJDBC()throws Exception {
long start = System.currentTimeMillis();
try{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 创建SqlSession对象
SqlSession sqlSession =
sqlSessionFactory.openSession();
connection=sqlSession.getConnection();
connection.setAutoCommit(false);
PreparedStatement ps = null;
ps=connection.prepareStatement(insertSql);
Random random = new Random();
for(int i=0;i<1000000;i++){
ps.setObject(1,new Integer(random.nextInt(1000000)));
ps.addBatch();
//每1000条数据提交一次,防止出现内存溢出的情况;
if (i % 999 == 0) {
ps.executeBatch();
ps.clearBatch();
}
}
//最后的一次也需要执行提交
ps.executeBatch();
ps.clearBatch();
sqlSession.close();
}catch (Exception ex) {
System.out.println(ex.getMessage());
} finally {
if (connection != null) {
connection.close();
}
}
System.out.println("百万条数据插入用时:"
+ (System.currentTimeMillis()
- start)+"【单位:毫秒】");
}
//6.使用Mybatis实现原生的批量提交多个字段
//此方法第一次测试耗时27782ms,第二次测试耗时18376ms
//测试结果得出,字段越多,插入的时间越长,相较于2个字段而言,10个字段的时间时1个字段的2倍
public void BatchByMyBatisUseJDBCManyField()throws Exception {
long start = System.currentTimeMillis();
try{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 创建SqlSession对象
SqlSession sqlSession =
sqlSessionFactory.openSession();
connection=sqlSession.getConnection();
connection.setAutoCommit(false);
PreparedStatement ps = null;
ps=connection.prepareStatement("insert
into
t_inserttest(field1,field2,field3,field4,field5,field6,field7,field8,field9,field10)value(?,?,?,?,?,?,?,?,?,?)");
Random random = new Random();
for(int i=0;i<1000000;i++){
ps.setObject(1,new Integer(random.nextInt(1000000)));
ps.setObject(2,new Integer(random.nextInt(1000000)));
ps.setObject(3,new Integer(random.nextInt(1000000)));
ps.setObject(4,new Integer(random.nextInt(1000000)));
ps.setObject(5,new Integer(random.nextInt(1000000)));
ps.setObject(6,new Integer(random.nextInt(1000000)));
ps.setObject(7,new Integer(random.nextInt(1000000)));
ps.setObject(8,new Integer(random.nextInt(1000000)));
ps.setObject(9,new Integer(random.nextInt(1000000)));
ps.setObject(10,new Integer(random.nextInt(1000000)));
ps.addBatch();
//每1000条数据提交一次,防止出现内存溢出的情况;
if (i % 999 == 0) {
ps.executeBatch();
ps.clearBatch();
}
}
//最后的一次也需要执行提交
ps.executeBatch();
ps.clearBatch();
sqlSession.close();
}catch (Exception ex) {
System.out.println(ex.getMessage());
} finally {
if (connection != null) {
connection.close();
}
}
System.out.println("百万条数据插入用时:"
+ (System.currentTimeMillis()
- start)+"【单位:毫秒】");
}
}
- 测试类源代码
package com.sc.test;
import com.sc.common.MySqlHelper;
import org.junit.Test;
public class TestMain {
@Test
public void testConnection(){
MySqlHelper msh=new MySqlHelper();
try {
if (msh.isConnect()) {
System.out.println("连接成功!");
}else{
System.out.println("连接失败!");
}
}catch (Exception ex){
System.out.println(ex.getMessage());
}
}
@Test
public void testByAutoCommit(){
MySqlHelper msh=new MySqlHelper();
try {
msh.BatchByAutoCommit();
}catch (Exception ex){
System.out.println(ex.getMessage());
}
}
@Test
public void testNoAutoCommit(){
MySqlHelper msh=new MySqlHelper();
try {
msh.BatchNoAutoCommit();
}catch (Exception ex){
System.out.println(ex.getMessage());
}
}
@Test
public void testMybatis(){
MySqlHelper msh=new MySqlHelper();
try {
msh.BatchByMybatis();
}catch (Exception ex){
System.out.println(ex.getMessage());
}
}
@Test
public void testMyBatisUseJDBC(){
MySqlHelper msh=new MySqlHelper();
try {
msh.BatchByMyBatisUseJDBC();
}catch (Exception ex){
System.out.println(ex.getMessage());
}
}
@Test
public void testMyBatisUserJDBCManyField(){
MySqlHelper msh=new MySqlHelper();
try {
msh.BatchByMyBatisUseJDBCManyField();
}catch (Exception ex){
System.out.println(ex.getMessage());
}
}
}
- 测试结果说明
a) 方法2、3使用JDBC直接实现批量插入,区别在于3中setAutoCommit=false;
根据实际测试情况,两者在插入1000W条数据的时候速度相差不是特别大。
b) 方法4使用Mybatis的批处理方法实现批量插入,分别进行了两种测试,即setAutoCommit=true和false的情况下,两者速度差距达到了3倍,但与直接使用JDBC进行批量插入速度相差依然很大
c) 方法5测试使用Mybatis直接调用JDBC进行批量插入,测试情况显示,与直接使用JDBC的速度差距不大。
d) 方法6测试在插入多个字段与单个字段的情况下两者的速度差距,经过两次测试,第一个10个字段的速度是单个字段速度的2倍;第二次测试速度相差不多,猜测是因为数据库缓存的原因
e) 总体测试过程中,数据库CPU使用率大幅度提升,因为使用1000条数据提交一次的方法,内存使用率有提升,但变化不是特别明显。实际开发过程中不建议大量使用批量插入的方法,避免对数据库造成过大的压力