java 批量插入千万条数据优化方案【值得收藏】

布满荆棘的人生 2024-03-26 16:01 9阅读 0赞

## 场景介绍 ##

再实际开发应用中总会面临导入大批量数据插入数据库、数据迁移、同步等操作在java 后台执行，执行效率的优化问题随之而来！比如如何**快速往MySQL数据库中导入1000万数据**

## mybatis ##

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4]

2、MySQL中新建一张user表，为了方便演示只保留id、昵称、年龄3个字段，建表语句；

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 1]

3、再次打开pom.xml文件，添加mybatis generator插件用于自动生成mapper映射文件；

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 2]

4、上一步添加了mybatis generator插件之后还不能直接使用，还需要在项目resources目录下新建一个配置文件generatorConfig.xml，里面主要需要配置数据库连接信息、Model文件生成目录、Mapper文件生成目录、以及xml文件生成目录；

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 3]

5、打开resources目录下的application.properties（或是application.yml）文件，添加一下mybatis相关配置和项目数据库连接配置；

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 4]

6、展示一下项目的完整结构

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 5]

## Mybatis为什么慢？ ##

首先我们用Mybatis来测试一下，看看插入1000万条数据需要多长时间。

**实现步骤：**

1、在UserMapper.java和UserMapper.xml文件中实现批量插入方法；

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 6]

2、新建一个Test测试类实现随机生成1000万用户记录，并调用上步已经实现的批量插入方法将数据插入到MySQL数据库；

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 7]

接下来就是正式测试了，没想到中间出了不少问题，在这里说明一下并附上解决方案。

> 问题一：Java堆内存爆了！

**原因分析：**由于要生成1000万条用户记录，需要申请大量Java的堆内存，已经超出JVM设置的最大堆内存大小，导致**OutOfMemoryError**报错：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 8]

**解决办法：**增加堆内存。

JVM设置堆内存有两个参数：

1.  **-Xms** 用于设置堆内存初始值，一般建议设置为和最大值一样；
2.  **-Xmx** 用于设置堆内存最大值，默认值为物理内存的1/4；

因为我的电脑是32G内存，也就是说默认最大堆内存有8G，8G都不够的话，那我直接来个20G试试，IDEA菜单栏依次打开Run -> Edit Configurations：

**修改步骤：**

1.  选中我们的测试类
2.  在右边找到VM options选项，输入-Xms20480m -Xmx20480m

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 9]

> 问题二：MySQL报错：Packet for query is too large

**原因分析：**因为发送到MySQL的数据量过大，超出了设置的最大值，导致报错：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 10]

**解决方案：**修改MySQL服务器max\_allowed\_packet属性。

**修改步骤：**

1.  直接在MySQL安装目录下找到my.ini文件，在\[mysqld\]下面添加一行**max\_allowed\_packet = 4G**

1.  通过MySQL客户端工具修改，这里以我常用的MySQL Workbench客户端来修改，菜单栏找到Server -> Options File，点击切换Networking标签：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 11]

> Tip：不论哪种方式，修改完都要记得重启MySQL，否则修改不生效哦。

我们来看下执行结果：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 12]

**结果：**

使用Mybatis插入1000万条数据到MySQL数据库共花费了**199.8秒**！

这结果是快还是慢？我们来具体分析一下耗时分布情况。

**分析：**

方法：

JDBC驱动中有一个profileSQL属性，可以跟踪记录SQL执行时间，附上官方文档介绍：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 13]

所以我们需要将数据库连接加上profileSQL=true属性。

再次看下执行结果：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 14]

其中，duration指的是SQL执行的时间，也就是说**MySQL服务器执行具体SQL语句的时间其实只有82.3秒，**我们上面统计到Mybatis插入1000万条数据花了近200秒的时间，那么**这中间的100多秒都干嘛去了？**

分析控制台输出的日志之后我发现了蹊跷所在：从程序调用Mybatis的批量插入方法开始，到MySQL服务器执行SQL，这中间正好差了100多秒，会是巧合吗？

打断点Debug追踪到Mybatis解析SQL语句的方法：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 15]

这parse方法首先会读取我们xml文件里的SQL模版拿到参数及参数类型信息，拼接生成SQL语句。

**每条数据循环一次，那1000W条数据就要循环解析1000万次，不慢才怪！**

**注意： MySQL的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入 MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组SQL语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能 url: jdbc:mysql://127.0.0.1:3306/test1?allowMultiQueries=true&rewriteBatchedStatements=true //allowMultiQueries=true，允许一次性执行多条SQL,批量插入时必须在连接地址后面加allowMultiQueries=true这个参数 //rewriteBatchedStatements=true，批量将数据传给MySQL，数据库会更高性能的执行批量处理，MySQL数据库版本在5.1.13以上，才能实现高性能的批量插入**

## JdbcTemplate让我眼前一亮 ##

接下来使用Spring框架的JdbcTemplate来测试一下。

**实现步骤：**

1.  同样的我们新建一个测试类，并用JdbcTemplate实现一个批量插入方法；

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 16]

接下来就可以开始测试了，果然中间又出现了了问题。

> 问题一：调用JdbcTemplate的batchUpdate批量操作方法，结果却一条条的插数据？

首先看下控制台输出日志：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 17]

可以看到JdbcTemplate是将我们的数据一条条的发送到MySQL服务器的，每个插入耗时1毫秒，那么1秒钟可以插入1000条记录，那么1000万条数据就需要10000秒，大约需要2.78个小时。

**原因分析：**JDBC驱动默认不支持批量操作，会将SQL语句分拆再一条条发往MySQL服务器执行，打断点跟踪一下代码，看看是不是这样：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 18]

分析一下代码：

*  首先执行步骤1判断rewriteBatchedStatements属性，为false的话直接执行步骤5的逻辑：串行执行SQL语句，也就是一条条顺序执行；

*  如果rewriteBatchedStatements为true，那么首先会执行步骤2：判断是否为insert语句，结果为true则会改写SQL执行批量插入操作；
 *  如果不是insert语句，再继续根据JDBC驱动版本以及数据量大小判断是否需要执行批量操作；

> Tip：对于非insert的批量操作语句，如果数据量小于3条，那也只会一条条顺序执行，不会进行合并批量执行。

附上rewriteBatchedStatements官方文档：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 19]

大概看了一下，跟我们上面分析的一样，标示是否让JDBC驱动使用批量模式去改写SQL语句。

**解决方案：**数据库连接上加上rewriteBatchedStatements=true属性，开启批量操作支持。

再次看下执行结果：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 20]

**结果：**

使用JdbcTemplate插入1000万条数据到MySQL数据库共花费了**80.1秒**！

**分析：**

一开始由于没有开启批量操作支持，所以导致MySQL只能一条条插入数据，原因在于我对JDBC驱动不够了解，看来以后还得加强学习。

开启批量操作支持后，通过日志可以观察到真正的SQL执行时间只有67.9秒，但整个插入操作用了80.1秒，中间差的10多秒中应该就是消耗在了改写SQL语句上了。

总得来说，JdbcTemplate批量插入大量数据的效率还不错，让我有眼前一亮的感觉。

## 原生JDBC就是快啊！ ##

早有耳闻批量插入大量数据必须使用原生JDBC，百闻不如一见，接下来我就使用原生JDBC的方式来测试一下。

**实现步骤：**

1.  同样的，我们新建一个测试类，并使用原生JDBC的方式实现一个批量插入方法；

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 21]

接下来就可以测试了，别担心，这次肯定不会再出问题了。

来看下执行结果：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 22]

**结果：**使用原生JDBC的方式插入1000万条数据到MySQL数据库共花费了**58.9秒**！

**分析：**

原生JDBC写起来还是既简单又舒适啊，都多少年没写过了，但是**越是简单的东西它越好用。**

通过输出日志，我们可以看到整个方法执行时间为58.9秒，而SQL真正的执行时间为46.8秒，中间同样相差了10多秒，同样也是花在了改写SQL语句上，这一结果正好与上面JdbcTemplate的执行结果互相佐证，证明了我们的分析是正确的。

## 存储过程行不行？ ##

最后使用存储过程的方式来试一下，说实话工作以来很少写存储过程，只好临时恶补了一波知识。

**实现步骤：**

1.  首先编写批量插入的存储过程，功能其实很简单，接收一个外部传入的表示循环次数的参数，进行循环插入数据；

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 23]

接下来我们调用存储过程，执行命令：

CALL batchInsert(10000000);

等待执行完成，看下耗时情况：

![如何快速向数据库插1000万数据？4种方法对比，它简单却速度最快][1000_4 24]

**结果：**

调用存储过程向MySQL数据库中批量插入1000万数据需要**141.1秒**。

**分析：**

存储过程需要141.1秒的时间我还是比较惊讶的，本来我对存储过程还是比较期待的。

仔细想想，其实存储过程用在这个场景并没有发挥出它的优势，时间长点也不奇怪。

为什么这么说？

首先我们来看看存储过程的一些特点：

1.  可以封装一些复杂的业务逻辑，外部直接调用存储过程即可；
2.  存储过程只在创建时编译一次，以后每次执行存储过程都不需再重新编译，使用存储过程可提高执行速度；
3.  将操作直接放到数据库端执行，可以减少客户端与服务端进行网络通讯开销，提高通信效率；

其次我们再来看看存储过程用在我们场景是否合适：

1.  我们使一次性提交所有数据，所以不存在多次通信增加耗时的操作，在这里存储过程的优势没有发挥出来；
2.  在存储过程的insert语句中，我们使用了concat函数来拼接字符串，函数运算会降低SQL执行效率；

所以说存储过程在我们这个业务场景并没有发挥出它的优势。

## 越简单越快 ##

面对**快速往MySQL数据库中导入1000万数据**这个问题，我们通过**Mybatis**、**JdbcTemplate**、**原生JDBC**以及**存储过程**4种方式分别进行测试，得出了最终结果：

插入速度：**原生JDBC > JdbcTemplate > 存储过程 > Mybatis**

**结果分析：**

Mybatis由于封装程度较高，底层有一个SQL模版解析和SQL拼接的过程，所以导致速度较慢；

存储过程一来由于本应用场景不太适合没有发挥出优势，二来由于SQL语句中加入了函数运算拖累了执行效率；

JdbcTemplate是Spring框架为了方便开发者调用对原生JDBC的一个轻度封装，虽然有点小插曲，但整体来看插入效率还可以；

转载：https://blog.csdn.net/zl1zl2zl3/article/details/105007492

[1000_4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c0dfbf4e8813498fa473793e007eabb4.png
[1000_4 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/503d2c2843e747ceb7d0ccd1ebea2163.png
[1000_4 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/1f21fb109e4244f9af81cee0a6a04788.png
[1000_4 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/7c89ac40b2414a07834196c077b84167.png
[1000_4 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c44d1619becf433ea89525870ccd5a6a.png
[1000_4 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/4efec2ce157549b885ce9c6b8df05591.png
[1000_4 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/43e3d251f7a447c2a403904b90ab8474.png
[1000_4 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/1e406210b9eb436f9577154ed8ad4099.png
[1000_4 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a3e1c6c40dc347a2a029424984ce8a1f.png
[1000_4 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/74ff1a2cc8354c7b8d09857271302baa.png
[1000_4 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/3264b285ad6d444ca2c76b650464de85.png
[1000_4 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/0196521a30cf438093de22da6b662139.png
[1000_4 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/1968f8b2a24a4944bdf979d6cd71dae7.png
[1000_4 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/cfe56f7304b844ed8ad30c12837bc796.png
[1000_4 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/fdc1281e66b043718e474256eacd2bb5.png
[1000_4 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/e823753ed7d940bf9c5a4a11dc06f8ca.png
[1000_4 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a2180e1186894dcc940fe948e2616247.png
[1000_4 17]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a29ce29270b343baa881583b9481c4d9.png
[1000_4 18]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ae9c0d99b3c04e0688b2f3883b75f1dd.png
[1000_4 19]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/0e25e5bd3cda4d58a69383436957a7d6.png
[1000_4 20]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/5849d5e28f1b4e9f8304a235b7134e3f.png
[1000_4 21]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/013517b9c9b94e55a8793df6218ad057.png
[1000_4 22]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/d5d6ceb1c19d41c2b618fe6868b2d06d.png
[1000_4 23]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c9b00631fd0c4fbb81f826500f90d2a8.png
[1000_4 24]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/d417e7535153476e93dc3d9590e6c71d.png