10万条数据批量插入，到底怎么做才快？ _数据

文章插图

基本上明白了这个小伙伴的意思，于是我自己也写了个测试案例，重新整理了今天这篇文章，希望和小伙伴们一起探讨这个问题，也欢迎小伙伴们提出更好的方案。
1. 思路分析批量插入这个问题，我们用 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 插入的时间？这是我们要考虑的核心问题。
2. 数据测试【10万条数据批量插入，到底怎么做才快？】接下来我们来做一个简单的测试，批量插入 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=rootspring.datasource.password=123spring.datasource.url=jdbc:mysql:///batch_insert?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true

大家需要注意，这个数据库连接 URL 地址中多了一个参数 rewriteBatchedStatements ，这是核心。
MySQL JDBC 驱动在默认情况下会无视 executeBatch() 语句，把我们期望批量执行的一组 sql 语句拆散，一条一条地发给 MySQL 数据库，批量插入实际上是单条插入，直接造成较低的性能。将 rewriteBatchedStatements 参数值为 true , 数据库驱动才会帮我们批量执行 SQL 。
OK，这样准备工作就做好了。
2.1 方案一测试首先我们来看方案一的测试，即一条一条的插入（实际上是批处理）。
首先创建相应的 mapper，如下：
@Mapperpublic interface UserMapper {Integer addUserOneByOne(User user);}对应的 XML 文件如下：

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

service 如下：

@Servicepublic class UserService extends ServiceImpl<UserMapper, User> implements IUserService {private static final Logger logger = LoggerFactory.getLogger(UserService.class);@AutowiredUserMapper userMapper;@AutowiredSqlSessionFactory 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 会耗费大量时间，效率奇低，这种效率奇低的方式松哥就不给大家测试了。