关于java:10万条数据批量插入到底怎么做才快

6次阅读

共计 5722 个字符，预计需要花费 15 分钟才能阅读完成。

@[toc]
上周松哥转载了一个数据批量插入的文章，里边和大家聊了一下数据批量插入的问题，批量插入到底怎么做才快。

有个小伙伴看了文章后提出了不同的意见：

松哥认真和 BUG 同学聊了下，基本上明确了这个小伙伴的意思，于是我本人也写了个测试案例，重新整理了明天这篇文章，心愿和小伙伴们一起探讨这个问题，也欢送小伙伴们提出更好的计划。

批量插入这个问题，咱们用 JDBC 操作，其实就是两种思路吧：

用一个 for 循环，把数据一条一条的插入（这种须要开启批处理）。
生成一条插入 sql，相似这种 insert into user(username,address) values('aa','bb'),('cc','dd')...。

到底哪种快呢？

咱们从两方面来思考这个问题：

插入 SQL 自身执行的效率。
网络 I/O。

先说第一种计划，就是用 for 循环循环插入：

这种计划的劣势在于，JDBC 中的 PreparedStatement 有预编译性能，预编译之后会缓存起来，前面的 SQL 执行会比拟快并且 JDBC 能够开启批处理，这个批处理执行十分给力。
劣势在于，很多时候咱们的 SQL 服务器和应用服务器可能并不是同一台，所以必须要思考网络 IO，如果网络 IO 比拟费时间的话，那么可能会拖慢 SQL 执行的速度。

再来说第二种计划，就是生成一条 SQL 插入：

这种计划的劣势在于只有一次网络 IO，即便分片解决也只是数次网络 IO，所以这种计划不会在网络 IO 上破费太多工夫。
当然这种计划有好几个劣势，一是 SQL 太长了，甚至可能须要分片后批量解决；二是无奈充分发挥 PreparedStatement 预编译的劣势，SQL 要从新解析且无奈复用；三是最终生成的 SQL 太长了，数据库管理器解析这么长的 SQL 也须要工夫。

所以咱们最终要思考的就是咱们在网络 IO 上破费的工夫，是否超过了 SQL 插入的工夫？这是咱们要思考的外围问题。

接下来咱们来做一个简略的测试，批量插入 5 万条数据看下。

首先筹备一个简略的测试表：

CREATE TABLE `user` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

接下来创立一个 Spring Boot 工程，引入 MyBatis 依赖和 MySQL 驱动，而后 application.properties 中配置一下数据库连贯信息：

spring.datasource.username=root
spring.datasource.password=123
spring.datasource.url=jdbc:mysql:///batch_insert?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true

大家须要留神，这个数据库连贯 URL 地址中多了一个参数 rewriteBatchedStatements，这是外围。

MySQL JDBC 驱动在默认状况下会忽视 executeBatch() 语句，把咱们冀望批量执行的一组 sql 语句拆散，一条一条地发给 MySQL 数据库，批量插入实际上是单条插入，间接造成较低的性能。将 rewriteBatchedStatements 参数置为 true, 数据库驱动才会帮咱们批量执行 SQL。

OK，这样筹备工作就做好了。

首先咱们来看计划一的测试，即一条一条的插入（实际上是批处理）。

首先创立相应的 mapper，如下：

@Mapper
public interface UserMapper {Integer addUserOneByOne(User user);
}

对应的 XML 文件如下：

<insert id="addUserOneByOne">
    insert into user (username,address,password) values (#{username},#{address},#{password})
</insert>

service 如下：

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IUserService {private static final Logger logger = LoggerFactory.getLogger(UserService.class);
    @Autowired
    UserMapper userMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;

    @Transactional(rollbackFor = Exception.class)
    public void addUserOneByOne(List<User> users) {SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
        UserMapper um = session.getMapper(UserMapper.class);
        long startTime = System.currentTimeMillis();
        for (User user : users) {um.addUserOneByOne(user);
        }
        session.commit();
        long endTime = System.currentTimeMillis();
        logger.info("一条条插入 SQL 消耗工夫 {}", (endTime - startTime));
    }
}

这里我要说一下：

尽管是一条一条的插入，然而咱们要开启批处理模式（BATCH），这样前前后后就只用这一个 SqlSession，如果不采纳批处理模式，反反复复的获取 Connection 以及开释 Connection 会消耗大量工夫，效率奇低，这种效率奇低的形式松哥就不给大家测试了。

接下来写一个简略的测试接口看下：

@RestController
public class HelloController {private static final Logger logger = getLogger(HelloController.class);
    @Autowired
    UserService userService;
    /**
     * 一条一条插入
     */
    @GetMapping("/user2")
    public void user2() {List<User> users = new ArrayList<>();
        for (int i = 0; i < 50000; i++) {User u = new User();
            u.setAddress("广州：" + i);
            u.setUsername("张三：" + i);
            u.setPassword("123：" + i);
            users.add(u);
        }
        userService.addUserOneByOne(users);
    }
}

写个简略的单元测试：

/**
 * 
 * 单元测试加事务的目标是为了插入之后主动回滚，防止影响下一次测试后果
 * 一条一条插入
 */
@Test
@Transactional
void addUserOneByOne() {List<User> users = new ArrayList<>();
    for (int i = 0; i < 50000; i++) {User u = new User();
        u.setAddress("广州：" + i);
        u.setUsername("张三：" + i);
        u.setPassword("123：" + i);
        users.add(u);
    }
    userService.addUserOneByOne(users);
}

能够看到，耗时 901 毫秒，5w 条数据插入不到 1 秒。

计划二是生成一条 SQL 而后插入。

mapper 如下：

@Mapper
public interface UserMapper {void addByOneSQL(@Param("users") List<User> users);
}

对应的 SQL 如下：

<insert id="addByOneSQL">
    insert into user (username,address,password) values
    <foreach collection="users" item="user" separator=",">
        (#{user.username},#{user.address},#{user.password})
    </foreach>
</insert>

service 如下：

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IUserService {private static final Logger logger = LoggerFactory.getLogger(UserService.class);
    @Autowired
    UserMapper userMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;
    @Transactional(rollbackFor = Exception.class)
    public void addByOneSQL(List<User> users) {long startTime = System.currentTimeMillis();
        userMapper.addByOneSQL(users);
        long endTime = System.currentTimeMillis();
        logger.info("合并成一条 SQL 插入消耗工夫 {}", (endTime - startTime));
    }
}

而后在单元测试中调一下这个办法：

/**
 * 合并成一条 SQL 插入
 */
@Test
@Transactional
void addByOneSQL() {List<User> users = new ArrayList<>();
    for (int i = 0; i < 50000; i++) {User u = new User();
        u.setAddress("广州：" + i);
        u.setUsername("张三：" + i);
        u.setPassword("123：" + i);
        users.add(u);
    }
    userService.addByOneSQL(users);
}

能够看到插入 5 万条数据耗时 1805 毫秒。

能够看到，生成一条 SQL 的执行效率还是要差一点。

另外还须要留神，第二种计划还有一个问题，就是当数据量大的时候，生成的 SQL 将特地的长，MySQL 可能一次性解决不了这么大的 SQL，这个时候就须要批改 MySQL 的配置或者看待插入的数据进行分片解决了，这些操作又会导致插入工夫更长。

很显著，计划一更具劣势。当批量插入十万、二十万数据的时候，计划一的劣势会更加显著（计划二则须要批改 MySQL 配置或者看待插入数据进行分片）。

小伙伴们晓得，其实 MyBatis Plus 里边也有一个批量插入的办法 saveBatch，咱们来看看它的实现源码：

@Transactional(rollbackFor = Exception.class)
@Override
public boolean saveBatch(Collection<T> entityList, int batchSize) {String sqlStatement = getSqlStatement(SqlMethod.INSERT_ONE);
    return executeBatch(entityList, batchSize, (sqlSession, entity) -> sqlSession.insert(sqlStatement, entity));
}

能够看到，这里拿到的 sqlStatement 就是一个 INSERT_ONE，即一条一条插入。

再来看 executeBatch 办法，如下：

public static <E> boolean executeBatch(Class<?> entityClass, Log log, Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {Assert.isFalse(batchSize < 1, "batchSize must not be less than one");
    return !CollectionUtils.isEmpty(list) && executeBatch(entityClass, log, sqlSession -> {int size = list.size();
        int i = 1;
        for (E element : list) {consumer.accept(sqlSession, element);
            if ((i % batchSize == 0) || i == size) {sqlSession.flushStatements();
            }
            i++;
        }
    });
}

这里留神 return 中的第三个参数，是一个 lambda 表达式，这也是 MP 中批量插入的外围逻辑，能够看到，MP 先对数据进行分片（默认分片大小是 1000），分片实现之后，也是一条一条的插入。持续查看 executeBatch 办法，就会发现这里的 sqlSession 其实也是一个批处理的 sqlSession，并非一般的 sqlSession。

综上，MP 中的批量插入计划跟咱们 2.1 大节的批量插入思路其实是一样的。

好啦，通过下面的剖析，当初小伙伴们晓得了批量插入该怎么做了吧？

松哥提供了一个测试案例，公众号后盾回复 批量插入测试 获取案例地址，案例中有三个单元测试办法，间接运行，就能够看到批量插入的工夫差别（数据库脚本在 resources 目录下）。

感兴趣的小伙伴无妨试试~

最初再次感激 BUG 童鞋提出的意见~

正文完

java

发表至： java

2021-11-02

0

关于java:1Spring-基础系列之-IoC

关于java:Java工具类-将List转换为树状结构

关于java:面试官问线程池-几个参数-拒绝策略

3分钟为女同事解决Maven依赖冲突不香吗

关于python:Python代码阅读第28篇计算列表中位数

关于java:10万条数据批量插入到底怎么做才快

1. 思路剖析

2. 数据测试

2.1 计划一测试

2.2 计划二测试

2.3 比照剖析

3. MP 怎么做的？

4. 小结

站内搜索