MyBatis高效插入MySQL方案

方案一：

我们可以使用mybatis批量新增

xml：

<insert id="addBatch" useGeneratedKeys="true" keyColumn="id" parameterType="list">
      INSERT INTO user_test(userName,age,info)
      VALUES 
    <foreach collection="list" separator="," item="user" index="index">
        (
         #{user.userName},
         #{user.age},
         #{user.info}
        )
    </foreach>

如果数量过大，建议在mysql侧执行一下接收数据大小的命令

show variables like ‘%max_allowed_packet%’
set global max_allowed_packet = 10010241024

调整接收数据大小,防止因为数据量过大报错

Packet for query is too large (1706 > 1024). You can change this value on the server by setting the max_allowed_packet' variable. 

方案二：

使用多线程批量插入：

 private void submitInsertTask(List<UserTest> userTestList) {
        ExecutorService executor = Executors.newFixedThreadPool(5); // 根据实际情况调整线程池大小
        Future<?> future = executor.submit(() -> userTestMapper.addBatch(userTestList));
        // 如果需要等待所有任务完成（可选）
        try {
            future.get();
        } catch (InterruptedException | ExecutionException e) {
            // 处理异常
            e.printStackTrace();
        } finally {
            executor.shutdown();
        }
    }

示例代码：

sql脚本：

CREATE TABLE `user_test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `age` int(11) NOT NULL COMMENT '1男  2女',
  `info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

user实体：

@Data
public class UserTest {
    private Integer id;
    private String userName;
    private Integer age;
    private String info;
}

mapper：

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface UserTestMapper {
    void addBatch(@Param("list") List<UserTest> userTestList);

    
}

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.rh.serviceproduct.test.UserTestMapper">

  <insert id="addBatch" useGeneratedKeys="true" keyColumn="id" parameterType="list">
      INSERT INTO user_test(userName,age,info)
      VALUES 
    <foreach collection="list" separator="," item="user" index="index">
        (
         #{user.userName},
         #{user.age},
         #{user.info}
        )
    </foreach>
  </insert>

 

</mapper>

service：

public interface UserTestService {

    void addBatch();

    void addBatch2();

}

service实现类：

@Service
public class UserTestServiceImpl implements UserTestService{
    @Autowired
    private UserTestMapper userTestMapper;
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;
    @Override
    public void addBatch() {
        List<UserTest> userTestList=new ArrayList<>();
        String userName="";
        for (int i = 0; i < 50000; i++) {
            userName="test"+i;
            Integer age= new Random().nextInt(3-1)+1;
            UserTest userTest = new UserTest();
            userTest.setUserName(userName);
            userTest.setAge(age);
            userTestList.add(userTest);
        }
        userTestMapper.addBatch(userTestList);
    }
    //这里还可以优化成定时任务插入，切割一下userTestList，分批次插入
    @Override
    public void addBatch2() {
        List<UserTest> userTestList = new ArrayList<>();
        String userName = "";
        for (int i = 0; i < 50000; i++) {
            userName = "test" + i;
            Integer age = new Random().nextInt(3 - 1) + 1;
            UserTest userTest = new UserTest();
            userTest.setUserName(userName);
            userTest.setAge(age);
            userTestList.add(userTest);
        }
        // 使用线程池执行批量插入
        submitInsertTask(userTestList);
    }
    //多线程操作
    private void submitInsertTask(List<UserTest> userTestList) {
        ExecutorService executor = Executors.newFixedThreadPool(5); // 根据实际情况调整线程池大小
        Future<?> future = executor.submit(() -> userTestMapper.addBatch(userTestList));
        // 如果需要等待所有任务完成（可选）
        try {
            future.get();
        } catch (InterruptedException | ExecutionException e) {
            // 处理异常
            e.printStackTrace();
        } finally {
            executor.shutdown();
        }
    }
}

controller：

@RestController
public class UserTestController {
    @Autowired
    private UserTestService userTestService;

    @RequestMapping("/addBatch")
    public String addBatch(){
        long start = System.currentTimeMillis();
        userTestService.addBatch();
        long end = System.currentTimeMillis();
        return "success耗时"+(end-start)/1000+"秒"; //经测试耗时2秒
    }
    @RequestMapping("/addBatch2")
    public String addBatch2(){
        long start = System.currentTimeMillis();
        userTestService.addBatch2();
        long end = System.currentTimeMillis();
        return "success耗时"+(end-start)/1000+"秒"; //经测试耗时1秒
    }
}