mybatis-plus 批量插入/新增

yelrihsss

已于 2023-07-24 11:02:30 修改

阅读量2.7k

点赞数 1

分类专栏：数据库文章标签： mybatis

于 2023-06-01 10:03:00 首次发布

本文链接：https://blog.csdn.net/m0_63297646/article/details/131843517

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

文章比较了三种MySQL插入数据的方法：单条插入、使用foreach和自定义批量插入的性能，并分析了各自的优缺点。通过MyBatis-Plus实现了自定义的批量插入和更新方法，以提高效率。同时，提到了SQL语句长度限制、数据库连接限制和内存消耗等问题，以及分批插入和调整数据库配置的优化策略。

摘要由CSDN通过智能技术生成

建表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for rewrite_sql
-- ----------------------------
DROP TABLE IF EXISTS `rewrite_sql`;
CREATE TABLE `rewrite_sql`  (
  `id` int NOT NULL DEFAULT -1,
  `stu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int NULL DEFAULT -1,
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

搭建项目

application.properties:

server.port=1222

#数据库
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3308/mysql?useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=root

#nocas
spring.cloud.nacos.discovery.server-addr=127.0.0.1:8848

#mapper
mybatis.mapper-locations=classpath:mapper/*.xml

#log
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

RewriteSqlDO:

@Data
@TableName("rewrite_sql")
public class RewriteSqlDO implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.INPUT)
    private Integer id;

    private String stuName;

    private Integer age;

    @TableField(value = "create_time", fill = FieldFill.INSERT)
    private Date createTime;
}

1、一条条插入

@Test
    void test() {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        for (int i = 10000; i < 20000; i++) {        //测试1w条数据耗时
            RewriteSqlDO rewriteSqlDO = new RewriteSqlDO();
            rewriteSqlDO.setId(i);
            rewriteSqlDO.setAge(i);
            rewriteSqlDO.setStuName(String.valueOf(1));
            rewriteSqlMapper.insert(rewriteSqlDO);
        }
        stopWatch.stop();
        System.out.println(stopWatch.getLastTaskTimeMillis());
    }

【注】

优点：1.spring自带工具类，可直接使用且简单2.性能消耗小，展示清晰，start和stop之间时间的误差更小

缺点：

一个stopWatch只能开启一个task，必须在stop之后才能开启新的，如果需要开启多个，则需要创建多个stopWatch实例

结果耗时：

2、使用foreach

RewriteSqlMapper.java：

public interface RewriteSqlMapper extends BaseMapper<RewriteSqlDO> {            //注意：这里用的是BaseMapper
    int insertBatch(@Param("list") List<RewriteSqlDO> list);
}

RewriteSqlMapper.xml：

<insert id="insertBatch" parameterType="java.util.List">
        insert into rewrite_sql(id, stu_name, age, create_time) values
        <foreach collection="list" item="item" separator=",">
            (#{item.id}, #{item.stuName}, #{item.age}, #{item.createTime})
        </foreach>
    </insert>

在配置文件数据库配置中加入：&rewriteBatchedStatements=true

@Test
void test1() {
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    List<RewriteSqlDO> rewriteSqlDOList = new ArrayList<RewriteSqlDO>();
    for (int i = 1; i < 10000; i++) {
        RewriteSqlDO rewriteSqlDO = new RewriteSqlDO();
        rewriteSqlDO.setId(i);
        rewriteSqlDO.setAge(i);
        rewriteSqlDO.setStuName(String.valueOf(i));
        rewriteSqlDOList.add(rewriteSqlDO);
    }
    rewriteSqlMapper.insertBatch(rewriteSqlDOList);
    stopWatch.stop();
    System.out.println(stopWatch.getLastTaskTimeMillis());
}

结果耗时：

3、自定义插入/更新

简述就是，封装好的foreach方法，性能耗时与第二种接近

CommonMapper：

public interface CommonMapper<T> extends BaseMapper<T> {

    /**
     * 自定义批量插入
     * 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一
     */
    int insertBatch(@Param("list") List<T> list);

    /**
     * 自定义批量更新，条件为主键
     * 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一
     */
    int updateBatch(@Param("list") List<T> list);
    
}

InsertBatchMethod.java：

/**
 * 批量新增
 */
@Slf4j
public class InsertBatchMethod extends AbstractMethod {

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        final String sql = "<script>insert into %s %s values %s</script>";
        final String fieldSql = prepareFieldSql(tableInfo);
        final String valueSql = prepareValuesSql(tableInfo);
        final String sqlResult = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);
        log.debug("sqlResult----->{}", sqlResult);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        // 第三个参数必须和RootMapper的自定义方法名一致
        return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), null, null);
    }
  
    private String prepareFieldSql(TableInfo tableInfo) {
        StringBuilder fieldSql = new StringBuilder();
        fieldSql.append(tableInfo.getKeyColumn()).append(",");
        tableInfo.getFieldList().forEach(x -> {
            //新增时修改字段不填充
            if (!("update_time".equals(x.getColumn()))
                    &&!("update_user_id".equals(x.getColumn()))
                    &&!("update_user_name".equals(x.getColumn()))){
                fieldSql.append(x.getColumn()).append(",");
            }
        });
        fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
        fieldSql.insert(0, "(");
        fieldSql.append(")");
        return fieldSql.toString();
    }
  
    private String prepareValuesSql(TableInfo tableInfo) {
        final StringBuilder valueSql = new StringBuilder();
        valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
        valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
        tableInfo.getFieldList().forEach(x -> {
            if (!("updateTime".equals(x.getProperty()))
                    &&!("updateUserId".equals(x.getProperty()))
                    &&!("updateUserName".equals(x.getProperty()))){
                valueSql.append("#{item.").append(x.getProperty()).append("},");
            }
        });
        valueSql.delete(valueSql.length() - 1, valueSql.length());
        valueSql.append("</foreach>");
        return valueSql.toString();
    }
}

UpdateBatchMethod：

/**
 * 批量更新方法实现，条件为主键，选择性更新
 */
@Slf4j
public class UpdateBatchMethod extends AbstractMethod {

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sql = "<script>\n<foreach collection=\"list\" item=\"item\" separator=\";\">\nupdate %s %s where %s=#{%s} %s\n</foreach>\n</script>";
        String additional = tableInfo.isWithVersion() ? tableInfo.getVersionFieldInfo().getVersionOli("item", "item.") : "" + tableInfo.getLogicDeleteSql(true, true);
        String setSql = sqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, "item", "item.");
        String sqlResult = String.format(sql, tableInfo.getTableName(), setSql, tableInfo.getKeyColumn(), "item." + tableInfo.getKeyProperty(), additional);
        log.debug("sqlResult----->{}", sqlResult);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        // 第三个参数必须和RootMapper的自定义方法名一致
        return this.addUpdateMappedStatement(mapperClass, modelClass, "updateBatch", sqlSource);
    }
  
}

自定义sql注入器MyInjector：

@Slf4j
public class MyInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
//        log.info("==============methodList===================");
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new InsertBatchMethod());
        methodList.add(new UpdateBatchMethod());
        return methodList;
    }

}

注入容器MybatisPlusConfig：

@Slf4j
@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
//        log.info("===================mybatisPlusInterceptor====================");
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return mybatisPlusInterceptor;
    }


    @Bean
    public MyInjector myInjector(){
//        log.info("========================myInjector===================================");
        return new MyInjector();
    }

}

测试：

@Test
void test2() {
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    List<RewriteSqlDO> rewriteSqlDOList = new ArrayList<RewriteSqlDO>();
    for (int i = 100000; i < 200000; i++) {
        RewriteSqlDO rewriteSqlDO = new RewriteSqlDO();
        rewriteSqlDO.setId(i);
        rewriteSqlDO.setAge(i);
        rewriteSqlDO.setStuName(String.valueOf(i));
        rewriteSqlDOList.add(rewriteSqlDO);
    }
    rewriteSqlMapper.insertBatch(rewriteSqlDOList);
    stopWatch.stop();
    System.out.println(stopWatch.getLastTaskTimeMillis());
}

【注意】

在配置文件数据库配置中加入：&allowMultiQueries=true

结果耗时：

对比

这里说明一下，另外一种jdbc加参数的情况，参考地址：

https://mp.weixin.qq.com/s/5FeCet4chv5L-q5WVXZsng

一条一条插入，接近于第一种方法的耗时，于是在配置中加入rewriteBatchedStatements这个参数，效率大幅度提升。

1、rewriteBatchedStatements默认是false，mysql连接驱动器版本3.1.13以后支持该配置。
2、底层原理：通过executeBatch方法批量提交到mysql服务端的sql重写为insert多值插入再执行。

缺点：

1、如果某条语句失败，则默认所有的都失败。

2、某些语句参数不一样，会出现查询缓存未命中。

问题：

在 MyBatis-Plus 中使用 foreach 进行批量插入操作时，可能会出现以下问题：

1、SQL语句长度限制：某些数据库对于单个 SQL 语句的长度有限制，如果插入的数据过多，可能会导致 SQL 语句超过数据库的限制而导致失败。这通常是由于数据库的配置或限制引起的。

2、数据库连接限制：数据库可能有并发连接数的限制，如果批量插入的数据量过大，可能会占用过多的数据库连接资源，导致其他请求无法正常访问数据库。

3、内存消耗：批量插入大量数据时，需要将数据一次性加载到内存中进行处理，如果数据量过大，可能会导致内存消耗过高，甚至触发内存溢出错误。

4、事务处理：批量插入的数据通常需要在一个事务中进行处理，如果在批量插入过程中出现异常，可能会导致部分数据插入成功，部分数据插入失败，造成数据不一致的情况。

对于sql语句限制：

MySQL: MySQL 5.7之前的版本对于单个SQL语句的长度限制为64KB，MySQL 5.7及以后的版本默认限制为4MB，可通过配置参数max_allowed_packet进行调整。
PostgreSQL: PostgreSQL没有固定的SQL语句长度限制，但通常会受到操作系统或网络传输的限制，一般可以处理较大的SQL语句。
Oracle: Oracle数据库对于单个SQL语句的长度限制为约2GB，但在实际应用中，一般不会达到这个限制。
SQL Server: SQL Server对于单个SQL语句的长度限制为65,536字符(65KB)。

考虑解决方案：

分批插入：将大批量数据拆分为较小的批次进行插入，以避免单个 SQL 语句过长和内存消耗过大的问题。
使用批量插入方法：MyBatis-Plus 提供了批量插入的方法，例如 insertBatch，它会自动将数据拆分为多个批次进行插入，并处理事务等问题。
调整数据库配置：根据数据库的实际情况，适时调整数据库的配置参数，如增加 SQL 语句长度限制、调整连接数等。
监控和优化：对于大批量插入操作，建议进行性能监控和优化，包括监控数据库的资源使用情况、优化 SQL 语句、调整数据库连接池等。

自定义SQL注入器失效

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.example.mapper.xxxxMapper.insertBatch

1、检查properties文件是否加了注解，参考上文

2、检查项目是否重写了SqlSessionFactory，需要加入一行配置

@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(
        @Qualifier("mybatisPlusInterceptor") MybatisPlusInterceptor mybatisPlusInterceptor,
        CustomMetaObjectHandler customMetaObjectHandler
) throws Exception {
    MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
    //其他配置
    //......
    GlobalConfig globalConfig = new GlobalConfig();
    globalConfig.setMetaObjectHandler(customMetaObjectHandler);
    //注入sqlInject
    globalConfig.setSqlInjector(new MyInjector());
    sessionFactoryBean.setGlobalConfig(globalConfig);
    return sessionFactoryBean.getObject();
}