mysql批处理

在调用prepareStatement.executeBatch();代码时 开启rewriteBatchedStatements会成倍提高执行效率

不开启则是单条sql提交
开启则是多条sql提交

但 提交的sql不能超过max_allowed_packet的设置值 不然报错

jdbc:mysql://localhost:3306/shopxxb2b2c?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false&rewriteBatchedStatements=true

存入500w条数据 8min

max_allowed_packet查询方法

进入mysql容器 运行

show VARIABLES like 'max_allowed_packet';

mysql> show VARIABLES like 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set

max_allowed_packet的单位为字节:

-- 转化为Mb,就是1024Mb
mysql> select 1073741824/1024/1024;
+----------------------+
| 1073741824/1024/1024 |
+----------------------+
| 1024.00000000        |
+----------------------+
1 row in set

JdbcTemplate 插入100w条数据

import lombok.SneakyThrows;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.IntStream;

@Nonnull
private final JdbcTemplate jdbcTemplate;
    
    @SneakyThrows
    public void createProducts() {
        String sql = "INSERT INTO Student (id, version, introduction, name) VALUES (?,?,?,?)";
        DataSource dataSource = jdbcTemplate.getDataSource();

        try {
            Connection collection = null;
            try {
                collection = dataSource.getConnection();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            collection.setAutoCommit(true);
            PreparedStatement prepareStatement = collection.prepareStatement(sql);
            final int batchSize = 25000;
            AtomicInteger beginCount = new AtomicInteger();
            AtomicInteger endCount = new AtomicInteger();
            IntStream.rangeClosed(0, 39).forEach(index -> {
                beginCount.set(index * batchSize + 1);
                endCount.set(beginCount.get() + batchSize - 1);
                articleService.asyncInsertStudent(index, batchSize, prepareStatement, (long) beginCount.get(), (long) endCount.get());
            });
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

import org.springframework.scheduling.annotation.Async;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.stream.IntStream;
import java.util.stream.LongStream;

//    @Async
    @Override
    public void asyncInsertStudent(int index, int batchSize, PreparedStatement prepareStatement, Long begin, Long end) {
        System.out.println("线程" + Thread.currentThread().getName() + " 执行异步任务：" + index);
        LongStream.rangeClosed(begin, end).forEach(i -> {
            try {
                prepareStatement.setLong(1, i);
                prepareStatement.setLong(2, 0L);
                prepareStatement.setString(3, "这是第" + i + "条测试数据！！！！！！！！");
                prepareStatement.setString(4, "测试华为手机" + i);

                prepareStatement.addBatch();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            if (i % batchSize == 0) {
                try {
                    prepareStatement.executeBatch();
                    prepareStatement.clearBatch();
                    System.out.println(i + "条数据执行完成");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        });
    }