JAVA：优化MySQL批量插入提升数据写入效率

1、简述

在开发过程中，我们经常需要将大量数据批量插入到MySQL数据库中。但是，普通的单条插入操作效率较低，容易导致性能瓶颈。本文将介绍一些优化MySQL批量插入的方法，以提升数据写入效率。

2、 使用多行插入语句

普通的插入语句每次只能插入一行数据，而多行插入语句可以一次性插入多行数据，减少了通信和解析的开销。

INSERT INTO table_name (column1, column2, column3)
VALUES (value1_1, value2_1, value3_1),
       (value1_2, value2_2, value3_2),
       ...
       (value1_n, value2_n, value3_n);

我们可以使用Mybatis提供的foreach标签，在在对应的Mapper.xml中写好批量的insert语句：

<insert id="insertBankManagents">
 insert into bank_managent( bank_type, bank_no,user_id)
 VALUES
 <foreach collection = "list" item="item" index= "index" separator =",">
 (
 	#{item.bankType}, #{item.bankNo},#{item.userId}
 )
 </foreach>
</insert>

注意：

• 传入的collection属性，可以分为list,array,map要对应处理。
• 当前方法可以做到批量导入，但是不足的是不同的数据库可执行的SQL长度不同，处理大批量的数据时慎用当前方式。

3、 提高事务提交间隔

将多次插入操作分批提交事务，可以减少锁的竞争，提高并发性。

START TRANSACTION;
-- 批量插入语句1
-- 批量插入语句2
-- ...
COMMIT;

通过Mybatis内置的ExecutorType方式有3种，其中就有BATCH方式来处理批量的，重复使用已经预处理语句，并且批量执行所有更新的语句，通过对比foreach方式，性能更优：

3.1 Mybatis单个插入语句

<insert id="insertBankManagent">
 insert into bank_managent( bank_type, bank_no,user_id)
 VALUES
 (
 	#{bankType}, #{bankNo},#{userId}
 )
</insert>

3.2 通过BATCH批次插入

@Autowired
private SqlSessionTemplate sqlSessionTemplate;

private void batchInsertBankManagents(List<BankManagent>  bankManagents){
	SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
	SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
	BankManagentMapper mapper = sqlSession.getMapper(BankManagentMapper.class);
	try{
		//long sTime = System.currentTimeMillis();
		for(int i = 0; i< bankManagents.size(); i ++ ){
			BankManagent bankManagent= bankManagents.get(i);
			mapper.insertBankManagent(bankManagent);
		}
		sqlSession.commit();
	}finally {
		sqlSession.close();
	}
}

注意：
在没有提交Insert之前是无法获取自增ID的，顾要选择业务场景使用。

3、 关闭自动提交

在插入大量数据时，关闭自动提交模式可以将多个插入操作合并成一个事务，提高效率。

SET autocommit = 0;
-- 批量插入语句1
-- 批量插入语句2
-- ...
COMMIT;
SET autocommit = 1;

4、使用LOAD DATA INFILE

如果数据源是一个文件，可以使用LOAD DATA INFILE命令，直接将文件内容导入到表中，效率非常高。

LOAD DATA INFILE 'file_path' INTO TABLE table_name;

5、考虑使用批量插入工具或框架

许多编程语言和ORM框架提供了批量插入的工具或方法，可以更方便地进行大批量数据的插入。

6、优化索引和表结构

在进行批量插入前，考虑是否需要暂时移除索引、触发器等约束，以提高插入效率。

7、使用延迟索引

在插入完数据后再创建索引，可以减少插入时的索引维护开销。

8、分区表

如果数据量非常大，可以考虑将表进行分区，将数据分散到不同的物理存储上，提高查询和插入的效率。

9、结论：

通过以上优化方法，我们可以显著提升MySQL批量插入的效率，特别是在处理大量数据时，这些优化策略将发挥出更为明显的作用。在实际应用中，根据具体场景选择合适的优化方法，将会为系统性能的提升带来显著的帮助。同时，也要注意在优化过程中保证数据的一致性和完整性。