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

程序員的成長之路

互聯(lián)網(wǎng)/程序員/技術(shù)/資料共享?

閱讀本文大概需要 2.8 分鐘。

來自：blog.csdn.net/chenping1993/article/details/106116000

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

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

新增xml執(zhí)行效率測試：xml執(zhí)行時間比sql稍慢一些，50000條數(shù)據(jù)插入時間約為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ù)據(jù)?耗時："+(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插入相關(guān)類
@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>

總結(jié)

sql插入的效率最高，sqlsession次之，mybatis框架foreach插入效率最低。

執(zhí)行效率echarts圖：

推薦閱讀：

留在一線，逃離一線？我從上海舉家回成都的生活經(jīng)歷告訴你

面試官問：Mybatis Plus 是如何實現(xiàn)動態(tài) SQL 語句的？原理你懂嗎？

最近面試BAT，整理一份面試資料《Java面試BATJ通關(guān)手冊》，覆蓋了Java核心技術(shù)、JVM、Java并發(fā)、SSM、微服務(wù)、數(shù)據(jù)庫、數(shù)據(jù)結(jié)構(gòu)等等。

獲取方式：點個「在看」，點擊上方小卡片，進(jìn)入公眾號后回復(fù)「面試題」領(lǐng)取，更多內(nèi)容陸續(xù)奉上。

朕已閱?