Mybatis插入大量數(shù)據效率對比：foreach插入、SqlSession批量插入、sql插入

點擊關注公眾號，Java干貨及時送達??

使用mybatis插入數(shù)據執(zhí)行效率對比，對比三種方式（測試數(shù)據庫為MySQL），

1. 使用 SqlSessionFactory，每一批數(shù)據執(zhí)行一次提交
2. 使用mybatis-plus框架的insert方法，for循環(huán)，每次執(zhí)行一次插入
3. 使用ibatis，純sql插入

新增xml執(zhí)行效率測試：xml執(zhí)行時間比sql稍慢一些，50000條數(shù)據插入時間約為2000毫秒左右，平均時間是sql的2倍左右。

先貼出執(zhí)行效果（數(shù)字代表執(zhí)行的時間，單位毫秒）：

測試代碼：

//測試類
@RunWith(SpringRunner.class)
@SpringBootTest(classes?=?DemoApplication.class)
public?class?Test1?{
?
????@Autowired
????UsersMapper?usersMapper;
?
????@Autowired
????SqlSessionFactory?sqlSessionFactory;
????public?List?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("拼裝數(shù)據?耗時："+(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?????????????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?????????????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?list);
?
????public?void?xmlBatchInsert(@Param("list")?List?list);
}
?
public?class?UsersProvider?{
?
????public?String?insertListSql(List?list)?{
????????StringBuffer?sqlList?=?new?StringBuffer();
?
????????sqlList.append("?INSERT?INTO?users(id,name,age,manager_id)??VALUES?");
????????for?(int?i?=?0;?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?1)?{
????????????????sqlList.append(",");
????????????}
????????}
????????return?sqlList.toString();
????}
}

xml 插入mapper文件

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插入效率最低。

執(zhí)行效率echarts圖：

來源：blog.csdn.net/chenping1993/

article/details/106116000

1.?Java中clone( )和new效率哪個更高？
2.?Linux 打包及壓縮命令使用方法總結
3.?千萬不要這樣使用 @Async 注解 !
4.?手把手10分鐘實現(xiàn) Spring Boot 發(fā)送郵件功能
最近面試BAT，整理一份面試資料《Java面試BATJ通關手冊》，覆蓋了Java核心技術、JVM、Java并發(fā)、SSM、微服務、數(shù)據庫、數(shù)據結構等等。
獲取方式：點“在看”，關注公眾號并回復?Java?領取，更多內容陸續(xù)奉上。
文章有幫助的話，在看，轉發(fā)吧。
謝謝支持喲 (*^__^*)