百度360必应搜狗淘宝本站头条
当前位置:网站首页 > 技术文章 > 正文

10万条数据批量插入,到底怎么做才快?

haoteby 2025-01-23 17:20 4 浏览

基本上明白了这个小伙伴的意思,于是我自己也写了个测试案例,重新整理了今天这篇文章,希望和小伙伴们一起探讨这个问题,也欢迎小伙伴们提出更好的方案。

1. 思路分析

批量插入这个问题,我们用 JDBC 操作,其实就是两种思路吧:

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

到底哪种快呢?

我们从两方面来考虑这个问题:

  1. 插入 SQL 本身执行的效率。
  2. 网络 I/O。

先说第一种方案,就是用 for 循环循环插入:

  • 这种方案的优势在于,JDBC 中的 PreparedStatement 有预编译功能,预编译之后会缓存起来,后面的 SQL 执行会比较快并且 JDBC 可以开启批处理,这个批处理执行非常给力。
  • 劣势在于,很多时候我们的 SQL 服务器和应用服务器可能并不是同一台,所以必须要考虑网络 IO,如果网络 IO 比较费时间的话,那么可能会拖慢 SQL 执行的速度。

再来说第二种方案,就是生成一条 SQL 插入:

  • 这种方案的优势在于只有一次网络 IO,即使分片处理也只是数次网络 IO,所以这种方案不会在网络 IO 上花费太多时间。
  • 当然这种方案有好几个劣势,一是 SQL 太长了,甚至可能需要分片后批量处理;二是无法充分发挥 PreparedStatement 预编译的优势,SQL 要重新解析且无法复用;三是最终生成的 SQL 太长了,数据库管理器解析这么长的 SQL 也需要时间。

所以我们最终要考虑的就是我们在网络 IO 上花费的时间,是否超过了 SQL 插入的时间?这是我们要考虑的核心问题。

2. 数据测试

接下来我们来做一个简单的测试,批量插入 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,这样准备工作就做好了。

2.1 方案一测试

首先我们来看方案一的测试,即一条一条的插入(实际上是批处理)。

首先创建相应的 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 秒。

2.2 方案二测试

方案二是生成一条 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 的配置或者对待插入的数据进行分片处理了,这些操作又会导致插入时间更长。

2.3 对比分析

很明显,方案一更具优势。当批量插入十万、二十万数据的时候,方案一的优势会更加明显(方案二则需要修改 MySQL 配置或者对待插入数据进行分片)。

3. MP 怎么做的?

小伙伴们知道,其实 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 小节的批量插入思路其实是一样的。

4. 小结

好啦,经过上面的分析,现在小伙伴们知道了批量插入该怎么做了吧?

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

最后再次感谢 BUG 童鞋提出的意见~

相关推荐

Python的RSA操作(私钥与公钥)(python rsa 公钥解密)

RSA是1977年由罗纳德·李维斯特(RonRivest)、阿迪·萨莫尔(AdiShamir)和伦纳德·阿德曼(LeonardAdleman)一起提出的。当时他们三人都在麻省理工学院工作。RSA...

RSA在日益互联的世界网络中安全性能如何?

KeyFactor公司(美国一家领先的安全数字身份管理解决方案提供商及网络安全行业权威机构)研究表明,许多物联网设备制造商正在生成不安全的RSA密钥,182个RSA证书里就有一个可能会被破解,由于不正...

让频谱分析更高效,澄清RSA使用中的一些误解

从事射频应用的研究人员、工程师和技术人员通常都能充分理解频谱分析仪的用途和优点,无论是传统的扫频分析仪(TSA)还是更现代的矢量信号分析仪(VSA)。他们熟练掌握这些重要射频仪器的关键规范和工作...

微软公告:Win10/Win11将不再支持短于2048位的RSA密钥证书

IT之家3月16日消息,微软近日发布公告,表示即将放弃短于2048位的RSA密钥证书。在公告中微软并未明确弃用时间,对于用户来说,这其实有利于构建更安全的上网环境。IT之家翻译微软公告...

目前已知的最强加密算法RSA(rsa加密算法的优点)

前面有人让我讲解一下RSA算法,今天我就用我所学的知识讲解一下,首先我们先了解一下RSARSA是一种非对称加密算法,1977年由罗纳德·李维斯特(RonRivest)、阿迪·萨莫尔(AdiSha...

韩国 CryptoLab 将在 2025年 RSA 大会发布加密人脸识别解决方案

据美通社4月23日报道,韩国同态加密网络安全企业CryptoLab宣布,将于4月24日在2025年RSA大会上,首次发布加密人脸识别(EFR)方案,为生物识别安全难题提供创新解法。当前,人脸识...

应对变化!盘点RSA2015十大热门产品

4月20日-24日,全球知名信息安全峰会RSAConference2015在美国旧金山召开。作为IT安全领域的权威科技大会,RSA大会不仅会邀请各地区著名安全专家出席与分享,更吸引汇集了全球众多顶...

RSA 2015主题:变化挑战当今的安全理念

1“变化”成为RSA2015主题4月20日-24日,全球知名信息安全峰会RSAConference2015在美国旧金山召开。作为IT安全领域的权威科技大会,RSA大会不仅会邀请各地区著名安全专家出...

非对称加密——一文看懂RSA(非对称加密详解)

非对称加密----RSA的使用"非对称加密也叫公钥密码:使用公钥加密,使用私钥解密"在对称密码中,由于加密和解密的密钥是相同的,因此必须向接收者配送密钥。用于解密的密钥必须被配送给...

RSA算法详解(rsa算法图解)

什么是RSA前面文章我们讲了AES算法,AES算法是一种是对称加密算法,本文我们来介绍一个十分常用的非对称加密算法RSA。非对称加密算法也叫公钥密码算法,通过生成的公私钥来对明文密文进行加密解密。R...

升级SSH后ssh-rsa失效?一文带你轻松解决!

背景今天刚给Linux桌面系统完成升级,结果SSH连接突然“罢工”了,还弹出了这个报错信息:...

历史回顾RSA大会:25年,十个瞬间(rsa conference)

国家安全局、Clipper芯片、苹果对决FBI、禁止ShowGirl——RSA大会都经历过。RSA需要你RSA这个词代表一家密码及安全厂商,也代表着世界上最大的网络安全展会,它今年在旧...

RSA 加密技术详解(rsa的加密原理是什么)

RSA的安全性基于数学难题的理论安全:RSA的安全性主要基于大质数分解和离散对数问题这两个数学难题。在RSA加密算法中,公钥包含一个大整数N,它是两个大质数p和q的乘积。攻击者如果想要破解RSA加密,...

「游戏开发」请别再说Unity不如Unreal:Unity室内场景 + 光照练习 3

关注“indienova”,挖掘独立游戏的更多乐趣引言上两节慢吞吞的补了很多技术实现的细节,感觉要是把用到的所有技术细节都过一遍可能还需要若干篇文章。所以决定先把整体的流程这篇好玩的写了,以后再慢慢补...

再做一个Android!Google发布第二代VR眼镜Cardboard

在去年的GoogleI/O上,Google向所有与会者发放了一款名为Cardboard的纸盒版虚拟现实眼镜,相比OculusRift等颇为酷炫的VR头盔,第一代Cardboard着实糙得很。不过,...