基于此类问题,笔者以自己日常的开发手段作为依据演示一下MySQL批量插入的技巧。
CREATE TABLE `batch_insert_test` ( `id` int NOT NULL AUTO_INCREMENT, `fileid_1` varchar(100) DEFAULT NULL, `fileid_2` varchar(100) DEFAULT NULL, `fileid_3` varchar(100) DEFAULT NULL, `fileid_4` varchar(100) DEFAULT NULL, `fileid_5` varchar(100) DEFAULT NULL, `fileid_6` varchar(100) DEFAULT NULL, `fileid_7` varchar(100) DEFAULT NULL, `fileid_8` varchar(100) DEFAULT NULL, `fileid_9` varchar(100) DEFAULT NULL, `fileid_10` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COMMENT='测试批量插入,一行数据1k左右';使用逐行插入
/** * 逐行插入 */ @Test void rowByRowInsert() { //预热先插入5条数据 performCodeWarmUp(5); //生成10w条数据 List<BatchInsertTest> testList = generateBatchInsertTestData(); // 堆代码 duidaima.com long start = System.currentTimeMillis(); for (BatchInsertTest test : testList) { batchInsertTestMapper.insert(test); } long end = System.currentTimeMillis(); log.info("逐行插入{}条数据耗时:{}", BATCH_INSERT_SIZE, end - start); }输出结果如下,可以看到当进行3000条数据的逐条插入时耗时在3s左右:
05.988 INFO c.s.w.WebTemplateApplicationTests:55 main
逐行插入100000条数据耗时:119678
insert into batch_insert_test (id, fileid_1, fileid_2, fileid_3, fileid_4, fileid_5, fileid_6, fileid_7, fileid_8, fileid_9, fileid_10) values (1, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'), (2, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'), (3, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10');批量插入代码如下所示:
/** * foreach插入 */ @Test void forEachInsert() { /** * 代码预热 */ performCodeWarmUp(5); List<BatchInsertTest> testList = generateBatchInsertTestData(); long start = System.currentTimeMillis(); batchInsertTestMapper.batchInsertTest(testList); long end = System.currentTimeMillis(); log.info("foreach{}条数据耗时:{}", BATCH_INSERT_SIZE, end - start); }对应xml配置如下:
<!-- 插入数据 --> <insert id="batchInsertTest" parameterType="java.util.List"> INSERT INTO batch_insert_test (fileid_1, fileid_2, fileid_3, fileid_4, fileid_5, fileid_6, fileid_7, fileid_8, fileid_9, fileid_10) VALUES <foreach collection="list" item="item" separator=","> (#{item.fileid1}, #{item.fileid2}, #{item.fileid3}, #{item.fileid4}, #{item.fileid5}, #{item.fileid6}, #{item.fileid7}, #{item.fileid8}, #{item.fileid9}, #{item.fileid10}) </foreach> </insert>实验结果如下,使用foreach进行插入3000条的数据耗时不到1s:
10.496 INFO c.s.w.WebTemplateApplicationTests:79 main
foreach3000条数据耗时:403
Error updating database. Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (106,100,142 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.使用批处理完成插入
rewriteBatchedStatements=true完成连接配置后,我们还需要对于批量插入的编码进行一定调整,Mybatis默认情况下执行器为Simple,这种执行器每次执行创建的都是一个全新的语句,也就是创建一个全新的PreparedStatement对象,这也就意味着每次提交的SQL语句的插入请求都无法缓存,每次调用时都需要重新解析SQL语句。
@Autowired private SqlSessionFactory sqlSessionFactory; /** * session插入 */ @Test void batchInsert() { /** * 代码预热 */ performCodeWarmUp(5); List<BatchInsertTest> testList = generateBatchInsertTestData(); SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH); BatchInsertTestMapper sqlSessionMapper = sqlSession.getMapper(BatchInsertTestMapper.class); long start = System.currentTimeMillis(); for (BatchInsertTest batchInsertTest : testList) { sqlSessionMapper.insert(batchInsertTest); } sqlSession.commit(); long end = System.currentTimeMillis(); log.info("批处理插入{}条数据耗时:{}", BATCH_INSERT_SIZE, end - start); }可以看到进行3000条数据插入时,耗时也只需只需2ms左右:
05.226 INFO c.s.w.WebTemplateApplicationTests:108 main
批处理插入3000条数据耗时:179
04.771 INFO c.s.w.WebTemplateApplicationTests:108 main
批处理插入100000条数据耗时:4635
// 创建Statement对象 PreparedStatement statement = connection.createStatement(); // 批量插入的数据 String[] names = {"John Doe", "Jane Smith", "Mike Johnson"}; int[] ages = {30, 25, 35}; String[] cities = {"New York", "London", "Paris"}; // 构建批量插入的SQL语句 StringBuilder insertQuery = new StringBuilder("INSERT INTO mytable (name, age, city) VALUES "); for (int i = 0; i < names.length; i++) { insertQuery.append("('").append(names[i]).append("', ").append(ages[i]).append(", '").append(cities[i]).append("')"); if (i < names.length - 1) { insertQuery.append(", "); } } // 执行批量插入操作 statement.executeUpdate(insertQuery.toString()); // 关闭连接和Statement statement.close(); connection.close();可以看到在每一次使用foreach进行插入操作时,都需要重新创建一个PreparedStatement构建出一个SQL语句,每次提交时MySQL都需要进行一次预编译,这意味着用户每次使用foreach插入时,都需要进行一次预编译的网络IO,也正是这个原因使得其性能相较于批处理会逊色一些。
因为Mybatis对于原生批处理操作做了很多的封装,其中涉及很多校验检查和解析等繁琐的流程,所以通过使用原生JDBC Batch来避免这些繁琐的解析、动态拦截等操作,对于MySQL批量插入也会有显著的提升。感兴趣的读者可以自行尝试,笔者这里就不多做演示了。
show variables like 'max_allowed_packet%';当然并不一定只有上述条件影响批量插入的性能,影响批量插入的性能原因还有:
show variables like 'innodb_buffer_pool_size';索引的维护:这点相信读者比较熟悉,如果每次插入涉及大量无序且多个索引的维护,导致B+tree进行节点分裂合并等处理,则会消耗大量的计算资源,从而间接影响插入效率。