MySql批量插入数据三种方式性能对比

一安未来 2022-09-01

158

大家好，我是一安，今天聊一下mysql的批量插入，实际开发中也经常使用，毕竟使用批量插入，只需开启一次事务，事务占用的开销比插入操作而言比例很小，资源实际利用率是很高的。

前言

Mysql批处理数据主要有三种方式：

Mybatis的foreach

Mybatis 是一个半 ORM（对象关系映射）框架，它内部封装了 JDBC，开发时只需要关注 SQL 语句本身，不需要花费精力去处理加载驱动、创建连接、创建statement 等繁杂的过程。程序员直接编写原生态 sql，可以严格控制 sql 执行性能，灵活度高。
MyBatis 可以使用 XML 或注解来配置和映射原生信息，将 POJO 映射成数据库中的记录，避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。

工作流程：组件介绍：

JdbcTemplate的batchUpdate
SqlSessionFactory的批处理

下面正式开始

准备工作

建表语句

比较简单

CREATE TABLE `demo` (
  `name` varchar(32) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

引入Mysql相关依赖

  <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.0.7</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.48</version>
    </dependency>

定义数据源配置信息

注意：url后面添加rewriteBatchedStatements=true
参数，不然批量无效

spring:
  application:
    name: test
  datasource:
    mysql:
      jdbcUrl: jdbc:mysql://127.0.0.1:3306/quartz?useUnicode=true&characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true
      username: root
      password: root
      driver-class-name: com.mysql.jdbc.Driver
      initialSize: 5
      minIdle: 2
      maxIdle: 5
      maxActive: 10
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20

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

初始化数据源

@Configuration
@MapperScan(basePackages = "com.test.dao.mapper.mysql",sqlSessionFactoryRef = "mysqlSessionFactory")
public class MysqlDataSourceConfig {

    @Bean(name = "mysqlDataSource")
    @Qualifier("mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mysqlJdbcTemplate")
    public JdbcTemplate primaryJdbcTemplate(
            @Qualifier("mysqlDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

@Configuration
public class MybatisPlusConfig {
    /**
     * 配置分页插件
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }

    /**
     * 配置公共库数据源
     * @return
     * @throws Exception
     */
    @Bean(name = "mysqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        //此处设置为了解决找不到mapper文件的问题
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/mysql/*.xml"));

        //添加分页功能
        sqlSessionFactoryBean.setPlugins(new Interceptor[]{
                paginationInterceptor()
        });
        //map接收返回值值为null的问题，默认是当值为null,将key返回
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setCallSettersOnNulls(true);
        sqlSessionFactoryBean.setConfiguration(configuration);
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("mySqlSessionTemplate")
    public SqlSessionTemplate mySqlSessionTemplate(
            @Qualifier("mysqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }
}

代码演示

1.定义mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.test.mapper.DemoMapper">
    <insert id="forEachInsert">
        INSERT INTO demo(name,age,time)  VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.name},#{item.age},#{item.time})
        </foreach>
    </insert>
    <insert id="insert">
        INSERT INTO demo(name,age,time)  VALUES (#{name},#{age},#{time})
    </insert>

</mapper>

2.持久层

public interface DemoMapper {
    void forEachInsert(@Param("list") List<Demo> list);

    void insert(Demo demo);
}

3.控制层

注意：
1.小编这里简化，直接在控制层调用了持久层，实际中间还有业务层
2.案例里包含了Mybatis，JdbcTemplate，SqlSessionFactory三种

    public static int count = 10000;
    @Autowired
    JdbcTemplate mysqlJdbcTemplate;
    @Autowired
    private DemoMapper mapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;


    @GetMapping("/jdbcTemplateInsert")
    public void jdbcTemplateInsert() throws Exception {
        List<Object[]> list = new ArrayList<>();
        for(int i=0;i<count;i++){
            list.add(new Object[]{"一安未来"+i,i,"2022-8-29 22:22:22"});
        }
        StopWatch sw = new StopWatch();
        sw.start();
        String sql = "insert into demo(name,age,time)  VALUES (?,?,?)";
        batchUpdate(sql,list);
        sw.stop();
        System.out.println(sw.getTotalTimeMillis());
    }

    private  void batchUpdate(String subscribeSQL, List<Object[]> batchArgs) throws Exception {
        int batchsix = 2000;
        if (batchArgs.size() > 0 && batchArgs.size() < batchsix) {
            mysqlJdbcTemplate.batchUpdate(subscribeSQL, batchArgs);
        } else if (batchArgs.size() > 0 && batchArgs.size() > batchsix) {
            int len = batchArgs.size() / batchsix;
            for (int i = 0; i <= len; i++) {
                List<Object[]> batchArgtemp = new ArrayList<Object[]>();
                if (i == len) {
                    batchArgtemp = batchArgs.subList((i) * batchsix,
                            batchArgs.size());
                } else {
                    batchArgtemp = batchArgs.subList((i) * batchsix, (i + 1)
                            * batchsix);
                }
                mysqlJdbcTemplate.batchUpdate(subscribeSQL, batchArgtemp);
            }
        }
    }

    @GetMapping("/forEachInsert")
    public void forEachInsert(){
        List<Demo> list = new ArrayList<>();
        for(int i=0;i<count;i++){
            Demo demo = new Demo();
            demo.setName("一安未来"+i);
            demo.setAge(i);
            demo.setTime("2022-8-29 22:22:22");
            list.add(demo);
        }
        StopWatch sw = new StopWatch();
        sw.start();
        mapper.forEachInsert(list);
        sw.stop();
        System.out.println(sw.getTotalTimeMillis());
    }

    @GetMapping("/sqlSessionFactoryInsert")
    public void sqlSessionFactoryInsert(){
        List<Demo> list = new ArrayList<>();
        for(int i=0;i<count;i++){
            Demo demo = new Demo();
            demo.setName("一安未来"+i);
            demo.setAge(i);
            demo.setTime("2022-8-29 22:22:22");
            list.add(demo);
        }
        StopWatch sw = new StopWatch();
        sw.start();
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        DemoMapper mapper = sqlSession.getMapper(DemoMapper.class);
        for(Demo demo:list){
            mapper.insert(demo);
        }
        sqlSession.flushStatements();
        sw.stop();
        System.out.println(sw.getTotalTimeMillis()); 

    }