使用mybatis插入数据执行效率对比，对比三种方式（测试数据库为MySQL），
- 使用 SqlSessionFactory，每一批数据执行一次提交
- 使用mybatis-plus框架的insert方法，for循环，每次执行一次插入
- 使用ibatis，纯sql插入
新增xml执行效率测试：xml执行时间比sql稍慢一些，50000条数据插入时间约为2000毫秒左右，平均时间是sql的2倍左右。
先贴出执行效果（数字代表执行的时间，单位毫秒）：
测试代码：
//测试类 @RunWith(SpringRunner.class) @SpringBootTest(classes = DemoApplication.class) public class Test1 { @Autowired UsersMapper usersMapper; @Autowired SqlSessionFactory sqlSessionFactory; public List<Users> list = new ArrayList<>(); @Before public void getList() { long start = System.currentTimeMillis(); Users user; for (int i = 1; i <=50000 ; i++) { user = new Users(); user.setId(i); user.setName("java"); user.setAge(200); user.setManagerId(222); list.add(user); } System.out.println("拼装数据 耗时："+(System.currentTimeMillis()-start)); System.out.println(list.size()); } @Test public void batchInsert() { SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false); UsersMapper mapper = sqlSession.getMapper(UsersMapper.class); System.out.println("batchInsert 插入开始========"); long start = System.currentTimeMillis(); for (int i = 0; i < list.size(); i++) { mapper.insert(list.get(i)); if (i%5000==4999) { sqlSession.flushStatements(); // sqlSession.commit(); // sqlSession.clearCache(); } } // sqlSession.commit(); // sqlSession.clearCache(); sqlSession.flushStatements(); System.out.println("SqlSession 批量插入耗时："+(System.currentTimeMillis()-start)); } @Test public void forEachInsert() { System.out.println("forEachInsert 插入开始========"); long start = System.currentTimeMillis(); for (int i = 0; i < list.size(); i++) { usersMapper.insert(list.get(i)); } System.out.println("foreach 插入耗时："+(System.currentTimeMillis()-start)); } @Test public void sqlInsert() { System.out.println("sql 插入开始========"); long start = System.currentTimeMillis(); usersMapper.sqlInsert(list); System.out.println("sql 插入耗时："+(System.currentTimeMillis()-start)); } //xml批量插入 @Test public void xmlInsert() { System.out.println("xmlInsert 批量插入开始========"); long start = System.currentTimeMillis(); usersMapper.xmlBatchInsert(list); System.out.println("xmlInsert 批量插入耗时："+(System.currentTimeMillis()-start)); } } //sql插入相关类 @Repository public interface UsersMapper extends BaseMapper<Users> { @InsertProvider(type = UsersProvider.class, method = "insertListSql") public void sqlInsert(List<Users> list); public void xmlBatchInsert(@Param("list") List<Users> list); } public class UsersProvider { public String insertListSql(List<Users> list) { StringBuffer sqlList = new StringBuffer(); sqlList.append(" INSERT INTO users(id,name,age,manager_id) VALUES "); for (int i = 0; i < list.size() ; i++) { Users user = list.get(i); sqlList.append(" (").append(user.getId()).append(",").append("'").append(user.getName()).append("',").append(user.getAge()) .append(",").append(user.getManagerId()).append(")"); if (i < list.size()-1) { sqlList.append(","); } } return sqlList.toString(); } }
xml 插入mapper文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.demo.dao.UsersMapper"> <insert id="xmlBatchInsert"> INSERT INTO users(id,name,age,manager_id) VALUES <foreach collection="list" item="item" index="index" separator=","> (#{item.id}, #{item.name}, #{item.age}, #{item.managerId}) </foreach> </insert> </mapper>
总结
sql插入的效率最高，sqlsession次之，mybatis框架foreach插入效率最低。
执行效率echarts图：
本文就是愿天堂没有BUG给大家分享的内容，大家有收获的话可以分享下，想学习更多的话可以到微信公众号里找我，我等你哦。
