MyBatis/JDBC批量插入数据 23 秒插入 100 万条数据
MyBatis/JDBC批量插入数据 23 秒插入 100 万条数据，批量插入正确的姿势！
源码：https://gitee.com/little_lunatic/advance/tree/main/spring-transactional
数据库表结构如下：
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1000001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
实体类、mapper和配置文件定义
@TableName("t_user")
@Data
public class User implements Serializable {
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
private String username;
private Integer age;
}
public interface UserMapper extends BaseMapper<User> {
void batchInsertUser(@Param("userList") List<User> userList);
}
<insert id="batchInsertUser" parameterType="java.util.List">
insert into t_user(username,age) values
<foreach collection="userList" item="item" index="index" separator=",">
( #{item.username}, #{item.age} )
</foreach>
</insert>
MyBatis实现插入100万条数据
先清理表数据，然后优化批处理执行插入：
TRUNCATE table t_user;
以下是通过 MyBatis 实现 100 万条数据插入代码实现：
@Slf4j
@Service
public class UserServiceImpl {
@Autowired
UserMapper userMapper;
/**
* mybatis 批量插入
*
* @param count
*/
@Transactional
public void testBatchInsertUser(int count) {
try {
List<User> userList = new ArrayList<>();
for (int i = 1; i <= count; i++) {
User user = new User();
user.setId(i);
user.setUsername("须知少时凌云志, 曾许人间第一流 " + i);
user.setAge((int) (Math.random() * 100));
userList.add(user);
if (i % 5000 == 0) {
userMapper.batchInsertUser(userList);
// 每 1000 条数据提交一次事务
userList.clear();
}
}
// 最后插入剩余的数据
if(!CollectionUtils.isEmpty(userList)) {
userMapper.batchInsertUser(userList);
}
log.info("成功插入{}条数据", count);
} catch (Exception e) {
log.error("MSG={}", e.getMessage(), e);
}
}
}
24秒可以完成数据插入操作
JDBC实现插入100万条数据
JDBC循环插入的话跟上面的mybatis逐条插入类似。
设置参数，否则 JDBC 批量插入仍然缓慢
MySQL 需要 rewriteBatchedStatements=true，如： jdbc:mysql://localhost:3306/db?rewriteBatchedStatements=true&allowMultiQueries=true
@Slf4j
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> {
@Autowired
UserMapper userMapper;
@Autowired
private DataSource dataSource;
@TimeCost
@Transactional
public void jdbcBatchInsert(int count) throws SQLException {
try (Connection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement(
"INSERT INTO t_user (username, age) VALUES (?, ?)")) {
connection.setAutoCommit(false);
for (int i = 1; i <= count; i++) {
ps.setString(1, "须知少时凌云志, 曾许人间第一流 " + i);
ps.setInt(2, (int) (Math.random() * 100));
ps.addBatch();
if (i % 5000 == 0) {
log.info("开始插入{}条数据。。。", i);
ps.executeBatch();
connection.commit();
}
}
// 最后插入剩余的数据
ps.executeBatch();
connection.commit();
log.info("成功插入{}条数据", count);
} catch (Exception e) {
log.error("msg={}", e.getMessage(), e);
}
}
}
22秒可以完成数据插入操作
通过 JDBC 连接 MySQL 数据库，并执行批处理操作插入数据。具体实现步骤如下：
1、** 定义SQL语句，使用PreparedStatement对象预编译SQL语句并设置参数；
2、 执行批处理操作；
3、 处理剩余的数据；
4、 关闭Statement和Connection对象；
使用setAutoCommit(false) 来禁止自动提交事务，然后在每次批量插入之后手动提交事务。每次插入数据时都新建一个 PreparedStatement 对象以避免状态不一致问题。在插入数据的循环中，每 10000 条数据就执行一次 executeBatch() 插入数据。
另外，需要根据实际情况优化连接池和数据库的相关配置，以防止连接超时等问题。
总结
实现高效的大量数据插入需要结合以下优化策略（建议综合使用）：
批处理：批量提交SQL语句可以降低网络传输和处理开销，减少与数据库交互的次数在Java中可以使用Statement或者PreparedStatement的addBatch()方法来添加多个SQL语句，然后一次性执行executeBatch()方法提交批处理的SQL语句；
在循环插入时带有适当的等待时间和批处理大小，从而避免内存占用过高等问题：
设置适当的批处理大小：批处理大小指在一次插入操作中插入多少行数据。如果批处理大小太小，插入操作的频率将很高，而如果批处理大小太大，可能会导致内存占用过高。通常，建议将批处理大小设置为1000-5000行，这将减少插入操作的频率并降低内存占用。
采用适当的等待时间：等待时间指在批处理操作之间等待的时间量。等待时间过短可能会导致内存占用过高，而等待时间过长则可能会延迟插入操作的速度。通常，建议将等待时间设置为几秒钟到几十秒钟之间，这将使操作变得平滑且避免出现内存占用过高等问题。
可以考虑使用一些内存优化的技巧，例如使用内存数据库或使用游标方式插入数据，以减少内存占用。
总的来说，选择适当的批处理大小和等待时间可以平稳地进行插入操作，避免出现内存占用过高等问题。
1、 索引:在大量数据插入前暂时去掉索引，最后再打上，这样可以大大减少写入时候的更新索引的时间；
2、 数据库连接池：使用数据库连接池可以减少数据库连接建立和关闭的开销，提高性能在没有使用数据库连接池的情况，记得在finally中关闭相关连接；
3、 数据库参数调整：增加MySQL数据库缓冲区大小、配置高性能的磁盘和I/O等；
浙公网安备 33010602011771号