数据库批量插入这么讲究的么？

首先，多条数据的插入，可选的方案：

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

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 update
   apt-get install vim

4. 修改my.cnf

   vim my.cnf

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

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

6. 退出容器

   # exit

7. 重启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

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

插入方式	10	100	1000	1w	10w	100w	1000w
for循环插入	387	1150	7907	70026	635984	太久了...	太久了...
拼接sql插入	308	320	392	838	3156	24948（很可能超过sql长度限制）	OutOfMemoryError: 堆内存溢出
批处理(重点)	333	323	362	636	1638	8978	OutOfMemoryError: 堆内存溢出
批处理 + 分批提交	359	313	394	630	2907	18631	OutOfMemoryError: 堆内存溢出

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

一开始发现批处理比较慢的时候，真的挺怀疑自己，后面发现是有一个参数，有一种拨开云雾的感觉，知道得越多，不知道的越多。