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

小鱼儿 2024-04-20 12:17 8阅读 0赞

#### 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批量插入的效率，特别是在处理大量数据时，这些优化策略将发挥出更为明显的作用。在实际应用中，根据具体场景选择合适的优化方法，将会为系统性能的提升带来显著的帮助。同时，也要注意在优化过程中保证数据的一致性和完整性。  
![在这里插入图片描述][e692474f049146f099e089dbbfd3cc0b.png]

[e692474f049146f099e089dbbfd3cc0b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/567905feaec045619d485fd7494649be.png