mybatis批量插入mysql性能优化_mybatis三种批量插入方式性能对比

mybatis三种批量插入方式性能对比

发布时间：2018-08-03 13:58,

浏览次数：1523

, 标签：

mybatis

准备：

1.表结构

CREATE TABLE `t_user` (

`id` varchar(32) CHARACTER SET utf8 NOT NULL COMMENT '主键',

`name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '用户名',

`del_flag` char(1) CHARACTER SET utf8 DEFAULT NULL COMMENT '删除标示',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2.1 jdbc.properties配置

mysql.driver=com.mysql.jdbc.Driver mysql.url=jdbc:mysql://127.0.0.1:3306/ssm

mysql.username=root mysql.password=admin #定义初始连接数 mysql.initialSize=1 #定义最大连接数

mysql.maxActive=20 #定义最大空闲 mysql.maxIdle=20 #定义最小空闲 mysql.minIdle=1 #定义最长等待时间

mysql.maxWait=60000

2.2 spring-mybatis.xml配置

id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"

destroy-method="close">

value="${mysql.driver}"/>

name="username" value="${mysql.username}"/>

value="${mysql.password}"/>

value="${mysql.initialSize}"/>

value="${mysql.maxActive}"/>

value="${mysql.maxIdle}"/>

value="${mysql.minIdle}">

value="${mysql.maxWait}"/>

id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

name="typeAliasesPackage" value="com.win"/>

name="mapperLocations" value="classpath:/mapping/*.xml"/>

id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">

id="mapperScannerConfigurer"

class="org.mybatis.spring.mapper.MapperScannerConfigurer">

name="basePackage" value="com.win.ssm.dao"/>

name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>

class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

第一种：普通for循环插入

①junit类

@Test public void testInsertBatch2() throws Exception { long start =

System.currentTimeMillis(); User user; SqlSession sqlSession =

sqlSessionTemplate.getSqlSessionFactory().openSession(false); UserDao mapper =

sqlSession.getMapper(UserDao.class); for (int i = 0; i < 500; i++) { user = new

User(); user.setId("test" + i); user.setName("name" + i); user.setDelFlag("0");

mapper.insert(user); } sqlSession.commit(); long end =

System.currentTimeMillis(); System.out.println("---------------" + (start -

end) + "---------------"); }

②xml配置

INSERT INTO t_user (id, name, del_flag) VALUES(#{id},

#{name}, #{delFlag})

第二种：mybatis BATCH模式插入

①junit类

@Test public void testInsertBatch2() throws Exception { long start =

System.currentTimeMillis(); User user; SqlSession sqlSession =

sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH,

false);//跟上述sql区别 UserDao mapper = sqlSession.getMapper(UserDao.class); for

(int i = 0; i < 500; i++) { user = new User(); user.setId("test" + i);

user.setName("name" + i); user.setDelFlag("0"); mapper.insert(user); }

sqlSession.commit(); long end = System.currentTimeMillis(); System.out

.println("---------------" + (start - end) + "---------------"); }

②xml配置与第一种②中使用相同

第三种：foreach方式插入

①junit类

@Test public void testInsertBatch() throws Exception { long start =

System.currentTimeMillis(); List list = new ArrayList<>(); User user; for

(int i = 0; i < 10000; i++) { user = new User(); user.setId("test" + i);

user.setName("name" + i); user.setDelFlag("0"); list.add(user); }

userService.insertBatch(list); long end = System.currentTimeMillis(); System.out

.println("---------------" + (start - end) + "---------------"); }

②xml配置

INSERT INTO t_user (id, name, del_flag) VALUES

(#{user.id},

#{user.name}, #{user.delFlag})

特别注意：mysql默认接受sql的大小是1048576(1M)，即第三种方式若数据量超过1M会报如下异常：(可通过调整MySQL安装目录下的my.ini文件中[mysqld]段的＂max_allowed_packet

= 1M＂)

nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is

too large (5677854 > 1048576).

You can change this value on the server by setting the max_allowed_packet'

variable.

结果对比：

第一种 第二种 第三种

500条 7742 7388 622

1000条 15290 15078 746

5000条 78011 177350 1172

10000条 397472 201180 1205

时间有限测试数据较少，有兴趣可以自己测试以下。(不清楚为什么BATCH有时候比单条循环插入还耗时间)