批量插入大量数据

使用手动编写SQL、 MyBatis-Plus IService 接口的 saveBatch 方法和insertBatchSomeColumn 方法实现批量插入

项目地址：https://gitee.com/libeimaningg/beizhilib

（一）介绍

（1）手动编写SQL

优势

高效：一次性执行批量插入，减少数据库交互次数。通过拼接VALUES子句，一次性提交多条数据，所有记录在一个INSERT语句中插入。减少了网络I/O和数据库事务开销，性能通常优于逐条插入。
灵活：可以根据需要选择性插入部分字段，减少不必要的数据传输。

缺点

维护成本高：每个表都需要手动编写对应的 XML SQL。
不支持自动生成主键：如果表中有自增主键，需额外处理。

（2）saveBatch方法

MyBatis-Plus 提供的 saveBatch 方法简化了批量插入的操作，但其底层实际上是逐条插入，因此在处理大量数据时性能不佳。
优势

简便易用：无需手动编写 SQL，直接调用接口方法即可。
自动事务管理：内置事务支持，确保数据一致性。

缺点

性能有限：底层逐条插入，面对大数据量时效率较低。
批量大小受限：默认批量大小可能不适用于所有场景，需要手动调整。

（3）insertBatchSomeColumn 方法

其实mybatis-plus给我们预留了一个真正批量插入的扩展插件InsertBatchSomeColumn，为了实现真正高效的批量插入，可以使用 insertBatchSomeColumn 方法，实现一次性批量插入。
优势

高效：一次性执行批量插入，减少数据库交互次数。
灵活：可选择性插入部分字段，优化数据传输。(通过实体类属性)

（二）前提配置

1、配置文件添加：insert-ignore-auto-increment-column: true

mybatis-plus:
      insert-ignore-auto-increment-column: true # 插入数据时，如果主键是自增的，则忽略该字段

2、在datasource的url添加rewriteBatchedStatements=true

url: jdbc:mysql://localhost:3306/ry-cloud?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8

当将rewriteBatchedStatements设置为true后，它会启用批量重写语句的功能。在执行批量插入或更新操作时，数据库驱动程序会将多个单独的语句组合成一个批处理语句，从而提高数据库操作的效率。
通常情况下，每次插入或更新一行数据都会触发一次数据库的网络通信和磁盘写入操作，这会带来一定的性能开销。但是，当你启用rewriteBatchedStatements后，驱动程序会将多个单独的语句组合成一个批处理语句，并将其发送到数据库执行，从而减少了网络通信和磁盘写入的次数，提高了插入或更新操作的效率。

（三）StopWatch计时器

StopWatch类是Spring框架中用于测量代码执行时间的工具类，它提供了一系列属性来记录监测信息。

使用示例：

    public static void main(String[] args) throws InterruptedException {
        StopWatch stopWatch = new StopWatch();

        stopWatch.start("task1");
        System.out.println("当前任务名称："+stopWatch.currentTaskName());
        Thread.sleep(1000);
        stopWatch.stop();
        System.out.println("task1耗时毫秒："+stopWatch.getLastTaskTimeMillis());

        stopWatch.start("task2");
        System.out.println("当前任务名称："+stopWatch.currentTaskName());
        Thread.sleep(2000);
        stopWatch.stop();
        System.out.println("task2耗时毫秒："+stopWatch.getLastTaskTimeMillis());

        System.out.println("总任务数："+stopWatch.getTaskCount());
        System.out.println("总耗时毫秒："+stopWatch.getTotalTimeMillis());
        System.out.println("所有任务简要信息：\n"+stopWatch.shortSummary());
        System.out.println("所有任务详细信息：\n"+stopWatch.prettyPrint());
    }

（四）实现示例：

（1）使用手动编写SQL:

//实现层
sysUserMapper.insertList(list);
//接口层
void insertList(@Param("list") List<SysUser> list);
//XML
<insert id="insertList"
            parameterType="java.util.List">
        insert into sys_user (<include refid="Base_Column_List"/>) values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.userId},#{item.deptId},#{item.userName},#{item.nickName},#{item.userType},#{item.email},#{item.phonenumber},#{item.sex},#{item.avatar},#{item.password},#{item.status},#{item.delFlag},#{item.loginIp},#{item.loginDate},#{item.createBy},#{item.createTime},#{item.updateBy},#{item.updateTime},#{item.remark})
        </foreach>
    </insert>

如图，只有一条sql。

（2）使用MybatisPlus的saveBatch方法：

    @Override
    @Transactional
    public void insert(SysUser user) {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        List<SysUser> list = new ArrayList<>();
        for (int i = 0; i < 10000; i++) {
            list.add(user);
        }
        stopWatch.stop();
        log.info("for循环所需时间stopWatch.getTotalTimeMillis() = {}", stopWatch.getTotalTimeMillis());

        stopWatch.start();
        this.saveBatch(list);
        stopWatch.stop();
        log.info("插入所需时间stopWatch.getTotalTimeMillis() = {}", stopWatch.getTotalTimeMillis());
    }

如图，每条数据对应一条SQL。
合理设置批量大小，以平衡性能和资源消耗：
this.saveBatch(list,5):

（3）使用insertBatchSomeColumn方法

编写SQL注入器：

package org.yaoyao.yaoyaocommon.config;

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.AbstractSqlInjector;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;

import java.util.List;

/**
 * sql注入器
 *
 * @author Bryson
 * @date 2025/3/7
 */
public class EasySqlInjector extends DefaultSqlInjector {


    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
        // 注意：此SQL注入器继承了DefaultSqlInjector(默认注入器)，调用了DefaultSqlInjector的getMethodList方法，保留了mybatis-plus的自带方法
        List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
        methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
        return methodList;
    }

}

注入插件，把上面创建的类注入到spring容器：

package org.yaoyao.yaoyaocommon.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * mybatis plus 分页插件配置类
 *
 * @author Bryson
 * @date 2025/2/28
 */
@Configuration
public class MpConfig {
    @Bean
    public EasySqlInjector easySqlInjector() {
        return new EasySqlInjector();
    }
}

编写自己的mapper继承BaseMapper：

package org.yaoyao.yaoyaobiz.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import java.util.Collection;

/**
 * TODO 类作用描述
 *
 * @author Bryson
 * @date 2025/3/7
 */
public interface EasyBaseMapper<T> extends BaseMapper<T> {
    /**
     * 批量插入 仅适用于mysql
     *
     * @param entityList 实体列表
     * @return 影响行数
     */
    Integer insertBatchSomeColumn(Collection<T> entityList);
}

实体类的mapper继承自己写得mapper：

public interface SysUserMapper extends EasyBaseMapper<SysUser> {
}

测试：

sysUserMapper.insertBatchSomeColumn(list);

如图，只有一条SQL
根据具体业务场景和数据库性能，可以调整批量大小（如 1000 条一批），避免单次插入过多数据，SQL过长
优化后的EasyBaseMapper：

package org.yaoyao.yaoyaobiz.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import java.util.ArrayList;
import java.util.Collection;

/**
 * TODO 类作用描述
 *
 * @author Bryson
 * @date 2025/3/7
 */
public interface EasyBaseMapper<T> extends BaseMapper<T> {
    /**
     * 批量插入 仅适用于mysql
     *
     * @param entityList 实体列表
     * @return 影响行数
     */
    Integer insertBatchSomeColumn(Collection<T> entityList);

    int batchSize = 5;  // 应为mysql对于太长的sql语句是有限制的，所以我这里设置每1000条批量插入拼接sql


    default Integer batchInsert(Collection<T> entityList) {

        int result = 0;
        Collection<T> tempEntityList = new ArrayList<>();
        int i = 0;
        for (T entity : entityList) {
            tempEntityList.add(entity);
            if (i > 0 && (i % batchSize == 0)) {
                result += insertBatchSomeColumn(tempEntityList);
                tempEntityList.clear();
            }
            i++;
        }
        result += insertBatchSomeColumn(tempEntityList);
        return result;
    }
////    这样之后就可以用mapper中的batchInsert来批量插入了
}

实现类再调用batchInsert方法就行了sysUserMapper.batchInsert(list);
如图，可以看到sql按批次量被切分：

（五）性能优化

1、批量插入

数据批量插入相比数据逐条插入的运行效率得到极大提升。
当数据逐条插入时，每条插入操作都需要进行一次数据库连接和一次磁盘写入操作，这会导致频繁的网络通信和磁盘 I/O 开销。如果有大量的数据需要插入，这些额外的开销会导致插入速度变慢，降低整体的运行效率。
相比之下，批量插入将多条数据合并为一个批次进行插入。通过一次数据库连接和一次磁盘写入操作，可以将多条数据一次性插入到数据库中。这样可以减少网络通信次数和磁盘 I/O 操作次数，大大提高了数据插入的效率。
批量插入的效率提升主要有以下几个方面的原因：

减少网络通信开销：批量插入可以通过一次数据库连接和一次传输操作将多条数据发送给数据库，减少了网络通信的次数和开销。
减少磁盘 I/O 操作：批量插入将多条数据合并为一个写入操作，减少了磁盘的读写次数，降低了磁盘 I/O 的开销。
优化事务管理：批量插入可以将多条插入操作合并为一个事务，减少了事务的开启和提交次数，提高了事务管理的效率。

开启批量重写功能：在数据库的连接 URL 中添加 rewriteBatchedStatements=true，以优化批量插入的性能。

2、开启事务

数据逐条插入时，显示开启事务相比无事务的运行效率得到极大提升
MySQL 每条插入操作，都会在内部建立一个隐式事务，在这个事务内进行真正的插入操作，所以逐条插入需要不停的创建事务和提交事务，造成较大的开销；显示开启事务，将多条插入操作放在同一个事务内，等都执行完再提交事务，可以减少创建和提交事务的次数，从而降低消耗。
建议：添加注解@Transactional(rollbackFor = Exception.class)

3、合理设置批量大小

根据具体业务场景和数据库性能，调整批量大小（如 1000 条一批），避免单次插入过多数据。

绕过 max_allowed_packet 限制（MySQL）
MySQL 默认限制单个 SQL 包的大小（如 max_allowed_packet=4MB）。若批量插入的数据量过大，可能导致 SQL 包超限而报错。手动设置较小的 batchSize 可确保每次插入的数据量在安全范围内。
适配不同数据库的批量语法限制
不同数据库对单次批量插入的条目数有限制（如 PostgreSQL 默认最多 1000 条/次）。通过 batchSize 自动分块，避免因超限而报错。
控制内存峰值
大数据量插入时，一次性加载所有数据到内存可能导致 OOM（OutOfMemoryError）。分批次处理（batchSize）可降低单次内存占用，避免内存溢出。
缓解事务内存压力
若开启事务，批量提交会累积 Undo/Redo 日志，合理分块可减少单次事务的资源占用。

posted @ 2025-03-10 11:09 果咩那塞阅读(12) 评论(0) 收藏举报

刷新页面返回顶部

每一年都奔走在自己热爱里没有人是一座孤岛，总有谁爱着你

lbming

批量插入大量数据

（一）介绍

（1）手动编写SQL

（2）saveBatch方法

（3）insertBatchSomeColumn 方法

（二）前提配置

（三）StopWatch计时器

（四）实现示例：

（1）使用手动编写SQL:

（2）使用MybatisPlus的saveBatch方法：

（3）使用insertBatchSomeColumn方法

（五）性能优化

1、批量插入

2、开启事务

3、合理设置批量大小

公告

每一年都奔走在自己热爱里 没有人是一座孤岛，总有谁爱着你

lbming

批量插入大量数据

（一）介绍

（1）手动编写SQL

（2）saveBatch方法

（3）insertBatchSomeColumn 方法

（二）前提配置

（三）StopWatch计时器

（四）实现示例：

（1）使用手动编写SQL:

（2）使用MybatisPlus的saveBatch方法：

（3）使用insertBatchSomeColumn方法

（五）性能优化

1、批量插入

2、开启事务

3、合理设置批量大小

公告

每一年都奔走在自己热爱里没有人是一座孤岛，总有谁爱着你