关于java:数据库批量插入这么讲究的么

最近新的我的项目写了不少各种 insertBatch 的代码，始终有人说，批量插入比循环插入效率高很多，那本文就来试验一下,到底是不是真的？

测试环境：

• SpringBoot 2.5
• Mysql 8
• JDK 8
• Docker

首先，多条数据的插入，可选的计划：

• foreach循环插入
• 拼接sql，一次执行
• 应用批处理性能插入

搭建测试环境`

sql文件：

drop database IF EXISTS test;CREATE DATABASE test;use test;DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `id` int(11) NOT NULL,  `name` varchar(255) DEFAULT "",  `age` int(11) DEFAULT 0,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

利用的配置文件：

server:  port: 8081spring:  #数据库连贯配置  datasource:    driver-class-name: com.mysql.cj.jdbc.Driver    url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&&serverTimezone=UTC&setUnicode=true&characterEncoding=utf8&&nullCatalogMeansCurrent=true&&autoReconnect=true&&allowMultiQueries=true    username: root    password: 123456#mybatis的相干配置mybatis:  #mapper配置文件  mapper-locations: classpath:mapper/*.xml  type-aliases-package: com.aphysia.spingbootdemo.model  #开启驼峰命名  configuration:    map-underscore-to-camel-case: truelogging:  level:    root: error

启动文件，配置了Mapper文件扫描的门路：

import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.EnableAutoConfiguration;import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication@MapperScan("com.aphysia.springdemo.mapper")public class SpringdemoApplication {    public static void main(String[] args) {        SpringApplication.run(SpringdemoApplication.class, args);    }}

Mapper文件一共筹备了几个办法，插入单个对象，删除所有对象，拼接插入多个对象：

import com.aphysia.springdemo.model.User;import org.apache.ibatis.annotations.Param;import java.util.List;public interface UserMapper {    int insertUser(User user);    int deleteAllUsers();    int insertBatch(@Param("users") List<User>users);}

Mapper.xml文件如下：

<?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.aphysia.springdemo.mapper.UserMapper">    <insert id="insertUser" parameterType="com.aphysia.springdemo.model.User">        insert  into user(id,age) values(#{id},#{age})    </insert>    <delete id="deleteAllUsers">        delete from user where id>0;    </delete>    <insert id="insertBatch" parameterType="java.util.List">        insert into user(id,age) VALUES        <foreach collection="users" item="model" index="index" separator=",">            (#{model.id}, #{model.age})        </foreach>    </insert></mapper>

测试的时候，每次操作咱们都删除掉所有的数据，保障测试的主观，不受之前的数据影响。

不同的测试

1. foreach 插入

先获取列表，而后每一条数据都执行一次数据库操作，插入数据：

@SpringBootTest@MapperScan("com.aphysia.springdemo.mapper")class SpringdemoApplicationTests {    @Autowired    SqlSessionFactory sqlSessionFactory;    @Resource    UserMapper userMapper;    static int num = 100000;    static int id = 1;    @Test    void insertForEachTest() {        List<User> users = getRandomUsers();        long start = System.currentTimeMillis();        for (int i = 0; i < users.size(); i++) {            userMapper.insertUser(users.get(i));        }        long end = System.currentTimeMillis();        System.out.println("time:" + (end - start));    }}

2. 拼接sql插入

其实就是用以下的形式插入数据：

INSERT INTO `user` (`id`, `age`) VALUES (1, 11),(2, 12),(3, 13),(4, 14),(5, 15);

    @Test    void insertSplicingTest() {        List<User> users = getRandomUsers();        long start = System.currentTimeMillis();        userMapper.insertBatch(users);        long end = System.currentTimeMillis();        System.out.println("time:" + (end - start));    }

3. 应用Batch批量插入

将MyBatis session 的 executor type 设为 Batch ，应用sqlSessionFactory将执行形式置为批量，主动提交置为false,全副插入之后，再一次性提交：

    @Test    public void insertBatch(){        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);        UserMapper mapper = sqlSession.getMapper(UserMapper.class);        List<User> users = getRandomUsers();        long start = System.currentTimeMillis();        for(int i=0;i<users.size();i++){            mapper.insertUser(users.get(i));        }        sqlSession.commit();        sqlSession.close();        long end = System.currentTimeMillis();        System.out.println("time:" + (end - start));    }

4. 批量解决+分批提交

在批处理的根底上，每1000条数据，先提交一下，也就是分批提交。

    @Test    public void insertBatchForEachTest(){        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);        UserMapper mapper = sqlSession.getMapper(UserMapper.class);        List<User> users = getRandomUsers();        long start = System.currentTimeMillis();        for(int i=0;i<users.size();i++){            mapper.insertUser(users.get(i));            if (i % 1000 == 0 || i == num - 1) {                sqlSession.commit();                sqlSession.clearCache();            }        }        sqlSession.close();        long end = System.currentTimeMillis();        System.out.println("time:" + (end - start));    }

首次后果，显著不对？

运行下面的代码，咱们能够失去上面的后果，for循环插入的效率的确很差，拼接的sql效率绝对高一点，看到有些材料说拼接sql可能会被mysql限度，然而我执行到1000w的时候，才看到堆内存溢出。

上面是不正确的后果！！！

拼接sql并没有超过内存

咱们看一下mysql的限度:

mysql> show VARIABLES like '%max_allowed_packet%';+---------------------------+------------+| Variable_name             | Value      |+---------------------------+------------+| max_allowed_packet        | 67108864   || mysqlx_max_allowed_packet | 67108864   || slave_max_allowed_packet  | 1073741824 |+---------------------------+------------+3 rows in set (0.12 sec)

这67108864足足600多M，太大了，怪不得不会报错，那咱们去改改一下它吧，改完从新测试：

1. 首先在启动mysql的状况下，进入容器内，也能够间接在Docker桌面版间接点Cli图标进入：

docker exec -it mysql bash

2. 进入 /etc/mysql 目录，去批改my.cnf文件：

cd /etc/mysql

3. 先依照vim，要不编辑不了文件：

apt-get updateapt-get install vim

4. 批改my.cnf

vim my.cnf

5. 在最初一行增加max_allowed_packet=20M(按i编辑，编辑完按esc，输出:wq退出)

 [mysqld]pid-file        = /var/run/mysqld/mysqld.pidsocket          = /var/run/mysqld/mysqld.sockdatadir         = /var/lib/mysqlsecure-file-priv= NULL# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0 # Custom config should go here!includedir /etc/mysql/conf.d/max_allowed_packet=2M

6. 退出容器

# exit

7. 查看mysql容器id

docker ps -a

8. 重启mysql

docker restart c178e8998e68

重启胜利后查看最大的max_allowed_pactet，发现曾经批改胜利：

mysql> show VARIABLES like '%max_allowed_packet%';+---------------------------+------------+| Variable_name             | Value      |+---------------------------+------------+| max_allowed_packet        | 2097152    || mysqlx_max_allowed_packet | 67108864   || slave_max_allowed_packet  | 1073741824 |+---------------------------+------------+

咱们再次执行拼接sql，发现100w的时候，sql就达到了3.6M左右，超过了咱们设置的2M，胜利的演示抛出了谬误：

org.springframework.dao.TransientDataAccessResourceException: ### Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.; Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.; nested exception is com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (36,788,583 > 2,097,152). You can change this value on the server by setting the 'max_allowed_packet' variable.

批量解决为什么这么慢？

然而，认真一看就会发现，下面的形式，怎么批处理的时候，并没有展现出劣势了，和for循环没有什么区别？这是对的么？

这必定是不对的，从官网文档中，咱们能够看到它会批量更新，不会每次去创立预处理语句，实践是更快的。

而后我发现我的一个最重要的问题：数据库连贯 URL 地址少了rewriteBatchedStatements=true

如果咱们不写，MySQL JDBC 驱动在默认状况下会漠视 executeBatch() 语句，咱们冀望批量执行的一组 sql 语句拆散，然而执行的时候是一条一条地发给 MySQL 数据库，实际上是单条插入，间接造成较低的性能。我说怎么性能和循环去插入数据差不多。

只有将 rewriteBatchedStatements 参数置为 true, 数据库驱动才会帮咱们批量执行 SQL。

正确的数据库连贯：

jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&&serverTimezone=UTC&setUnicode=true&characterEncoding=utf8&&nullCatalogMeansCurrent=true&&autoReconnect=true&&allowMultiQueries=true&&&rewriteBatchedStatements=true

找到问题之后，咱们从新测试批量测试,最终的后果如下：

从下面的后果来看，的确批处理是要快很多的，当数量级太大的时候，其实都会超过内存溢出的，批处理加上分批提交并没有变快，和批处理差不多，反而变慢了，提交太屡次了，拼接sql的计划在数量比拟少的时候其实和批处理相差不大，最差的计划就是for循环插入数据，这真的特地的耗时。100条的时候就曾经须要1s了，不能抉择这种计划。

一开始发现批处理比较慢的时候，真的挺狐疑本人，前面发现是有一个参数，有一种拨开云雾的感觉，晓得得越多，不晓得的越多。

【作者简介】：
秦怀，公众号【秦怀杂货店】作者，技术之路不在一时，山高水长，纵使迟缓，驰而不息。

剑指Offer全副题解PDF

2020年我写了什么？

开源编程笔记