JdbcTemplate讓我眼前一亮

接下來使用Spring框架的JdbcTemplate來測(cè)試一下。

實(shí)現(xiàn)步驟：

1. 同樣的我們新建一個(gè)測(cè)試類，并用JdbcTemplate實(shí)現(xiàn)一個(gè)批量插入方法；

接下來就可以開始測(cè)試了，果然中間又出現(xiàn)了了問題。

問題一：調(diào)用JdbcTemplate的batchUpdate批量操作方法，結(jié)果卻一條條的插數(shù)據(jù)？

首先看下控制臺(tái)輸出日志：

可以看到JdbcTemplate是將我們的數(shù)據(jù)一條條的發(fā)送到MySQL服務(wù)器的，每個(gè)插入耗時(shí)1毫秒，那么1秒鐘可以插入1000條記錄，那么1000萬條數(shù)據(jù)就需要10000秒，大約需要2.78個(gè)小時(shí)。

原因分析：JDBC驅(qū)動(dòng)默認(rèn)不支持批量操作，會(huì)將SQL語句分拆再一條條發(fā)往MySQL服務(wù)器執(zhí)行，打斷點(diǎn)跟蹤一下代碼，看看是不是這樣：

分析一下代碼：

• 首先執(zhí)行步驟1判斷rewriteBatchedStatements屬性，為false的話直接執(zhí)行步驟5的邏輯：串行執(zhí)行SQL語句，也就是一條條順序執(zhí)行；

• 如果rewriteBatchedStatements為true，那么首先會(huì)執(zhí)行步驟2：判斷是否為insert語句，結(jié)果為true則會(huì)改寫SQL執(zhí)行批量插入操作；
• 如果不是insert語句，再繼續(xù)根據(jù)JDBC驅(qū)動(dòng)版本以及數(shù)據(jù)量大小判斷是否需要執(zhí)行批量操作；

Tip：對(duì)于非insert的批量操作語句，如果數(shù)據(jù)量小于3條，那也只會(huì)一條條順序執(zhí)行，不會(huì)進(jìn)行合并批量執(zhí)行。

附上rewriteBatchedStatements官方文檔：

大概看了一下，跟我們上面分析的一樣，標(biāo)示是否讓JDBC驅(qū)動(dòng)使用批量模式去改寫SQL語句。

解決方案：數(shù)據(jù)庫連接上加上rewriteBatchedStatements=true屬性，開啟批量操作支持。

再次看下執(zhí)行結(jié)果：

結(jié)果：

使用JdbcTemplate插入1000萬條數(shù)據(jù)到MySQL數(shù)據(jù)庫共花費(fèi)了80.1秒！

分析：

一開始由于沒有開啟批量操作支持，所以導(dǎo)致MySQL只能一條條插入數(shù)據(jù)，原因在于我對(duì)JDBC驅(qū)動(dòng)不夠了解，看來以后還得加強(qiáng)學(xué)習(xí)。

開啟批量操作支持后，通過日志可以觀察到真正的SQL執(zhí)行時(shí)間只有67.9秒，但整個(gè)插入操作用了80.1秒，中間差的10多秒中應(yīng)該就是消耗在了改寫SQL語句上了。

總得來說，JdbcTemplate批量插入大量數(shù)據(jù)的效率還不錯(cuò)，讓我有眼前一亮的感覺。

原生JDBC就是快??！

早有耳聞批量插入大量數(shù)據(jù)必須使用原生JDBC，百聞不如一見，接下來我就使用原生JDBC的方式來測(cè)試一下。

實(shí)現(xiàn)步驟：

1. 同樣的，我們新建一個(gè)測(cè)試類，并使用原生JDBC的方式實(shí)現(xiàn)一個(gè)批量插入方法；

接下來就可以測(cè)試了，別擔(dān)心，這次肯定不會(huì)再出問題了。

來看下執(zhí)行結(jié)果：

結(jié)果：使用原生JDBC的方式插入1000萬條數(shù)據(jù)到MySQL數(shù)據(jù)庫共花費(fèi)了58.9秒！

分析：

原生JDBC寫起來還是既簡(jiǎn)單又舒適啊，都多少年沒寫過了，但是越是簡(jiǎn)單的東西它越好用。

通過輸出日志，我們可以看到整個(gè)方法執(zhí)行時(shí)間為58.9秒，而SQL真正的執(zhí)行時(shí)間為46.8秒，中間同樣相差了10多秒，同樣也是花在了改寫SQL語句上，這一結(jié)果正好與上面JdbcTemplate的執(zhí)行結(jié)果互相佐證，證明了我們的分析是正確的。

存儲(chǔ)過程行不行？

最后使用存儲(chǔ)過程的方式來試一下，說實(shí)話工作以來很少寫存儲(chǔ)過程，只好臨時(shí)惡補(bǔ)了一波知識(shí)。

實(shí)現(xiàn)步驟：

1. 首先編寫批量插入的存儲(chǔ)過程，功能其實(shí)很簡(jiǎn)單，接收一個(gè)外部傳入的表示循環(huán)次數(shù)的參數(shù)，進(jìn)行循環(huán)插入數(shù)據(jù)；

接下來我們調(diào)用存儲(chǔ)過程，執(zhí)行命令：

CALL batchInsert(10000000);
 
 

等待執(zhí)行完成，看下耗時(shí)情況：

結(jié)果：

調(diào)用存儲(chǔ)過程向MySQL數(shù)據(jù)庫中批量插入1000萬數(shù)據(jù)需要141.1秒。

分析：

存儲(chǔ)過程需要141.1秒的時(shí)間我還是比較驚訝的，本來我對(duì)存儲(chǔ)過程還是比較期待的。

仔細(xì)想想，其實(shí)存儲(chǔ)過程用在這個(gè)場(chǎng)景并沒有發(fā)揮出它的優(yōu)勢(shì)，時(shí)間長(zhǎng)點(diǎn)也不奇怪。

為什么這么說？

首先我們來看看存儲(chǔ)過程的一些特點(diǎn)：

1. 可以封裝一些復(fù)雜的業(yè)務(wù)邏輯，外部直接調(diào)用存儲(chǔ)過程即可；
2. 存儲(chǔ)過程只在創(chuàng)建時(shí)編譯一次，以后每次執(zhí)行存儲(chǔ)過程都不需再重新編譯，使用存儲(chǔ)過程可提高執(zhí)行速度；
3. 將操作直接放到數(shù)據(jù)庫端執(zhí)行，可以減少客戶端與服務(wù)端進(jìn)行網(wǎng)絡(luò)通訊開銷，提高通信效率；

其次我們?cè)賮砜纯创鎯?chǔ)過程用在我們場(chǎng)景是否合適：

1. 我們使一次性提交所有數(shù)據(jù)，所以不存在多次通信增加耗時(shí)的操作，在這里存儲(chǔ)過程的優(yōu)勢(shì)沒有發(fā)揮出來；
2. 在存儲(chǔ)過程的insert語句中，我們使用了concat函數(shù)來拼接字符串，函數(shù)運(yùn)算會(huì)降低SQL執(zhí)行效率；

所以說存儲(chǔ)過程在我們這個(gè)業(yè)務(wù)場(chǎng)景并沒有發(fā)揮出它的優(yōu)勢(shì)。

越簡(jiǎn)單越快

面對(duì)快速往MySQL數(shù)據(jù)庫中導(dǎo)入1000萬數(shù)據(jù)這個(gè)問題，我們通過Mybatis、JdbcTemplate、原生JDBC以及存儲(chǔ)過程4種方式分別進(jìn)行測(cè)試，得出了最終結(jié)果：

插入速度：原生JDBC > JdbcTemplate > 存儲(chǔ)過程 > Mybatis

結(jié)果分析：

Mybatis由于封裝程度較高，底層有一個(gè)SQL模版解析和SQL拼接的過程，所以導(dǎo)致速度較慢；

存儲(chǔ)過程一來由于本應(yīng)用場(chǎng)景不太適合沒有發(fā)揮出優(yōu)勢(shì)，二來由于SQL語句中加入了函數(shù)運(yùn)算拖累了執(zhí)行效率；

JdbcTemplate是Spring框架為了方便開發(fā)者調(diào)用對(duì)原生JDBC的一個(gè)輕度封裝，雖然有點(diǎn)小插曲，但整體來看插入效率還可以；

轉(zhuǎn)載：https://blog.csdn.net/zl1zl2zl3/article/details/105007492

場(chǎng)景介紹

再實(shí)際開發(fā)應(yīng)用中總會(huì)面臨導(dǎo)入大批量數(shù)據(jù)插入數(shù)據(jù)庫、數(shù)據(jù)遷移、同步等操作在java 后臺(tái)執(zhí)行，執(zhí)行效率的優(yōu)化問題隨之而來！比如如何快速往MySQL數(shù)據(jù)庫中導(dǎo)入1000萬數(shù)據(jù)

mybatis

2、MySQL中新建一張user表，為了方便演示只保留id、昵稱、年齡3個(gè)字段，建表語句；

3、再次打開pom.xml文件，添加mybatis generator插件用于自動(dòng)生成mapper映射文件；

4、上一步添加了mybatis generator插件之后還不能直接使用，還需要在項(xiàng)目resources目錄下新建一個(gè)配置文件generatorConfig.xml，里面主要需要配置數(shù)據(jù)庫連接信息、Model文件生成目錄、Mapper文件生成目錄、以及xml文件生成目錄；

5、打開resources目錄下的application.properties（或是application.yml）文件，添加一下mybatis相關(guān)配置和項(xiàng)目數(shù)據(jù)庫連接配置；

6、展示一下項(xiàng)目的完整結(jié)構(gòu)

Mybatis為什么慢？

首先我們用Mybatis來測(cè)試一下，看看插入1000萬條數(shù)據(jù)需要多長(zhǎng)時(shí)間。

實(shí)現(xiàn)步驟：

1、在UserMapper.java和UserMapper.xml文件中實(shí)現(xiàn)批量插入方法；

2、新建一個(gè)Test測(cè)試類實(shí)現(xiàn)隨機(jī)生成1000萬用戶記錄，并調(diào)用上步已經(jīng)實(shí)現(xiàn)的批量插入方法將數(shù)據(jù)插入到MySQL數(shù)據(jù)庫；

接下來就是正式測(cè)試了，沒想到中間出了不少問題，在這里說明一下并附上解決方案。

問題一：Java堆內(nèi)存爆了！

原因分析：由于要生成1000萬條用戶記錄，需要申請(qǐng)大量Java的堆內(nèi)存，已經(jīng)超出JVM設(shè)置的最大堆內(nèi)存大小，導(dǎo)致OutOfMemoryError報(bào)錯(cuò)：

解決辦法：增加堆內(nèi)存。

JVM設(shè)置堆內(nèi)存有兩個(gè)參數(shù)：

1. -Xms?用于設(shè)置堆內(nèi)存初始值，一般建議設(shè)置為和最大值一樣；
2. -Xmx?用于設(shè)置堆內(nèi)存最大值，默認(rèn)值為物理內(nèi)存的1/4；

因?yàn)槲业碾娔X是32G內(nèi)存，也就是說默認(rèn)最大堆內(nèi)存有8G，8G都不夠的話，那我直接來個(gè)20G試試，IDEA菜單欄依次打開Run -> Edit Configurations：

修改步驟：

1. 選中我們的測(cè)試類
2. 在右邊找到VM options選項(xiàng)，輸入-Xms20480m -Xmx20480m

問題二：MySQL報(bào)錯(cuò)：Packet for query is too large

原因分析：因?yàn)榘l(fā)送到MySQL的數(shù)據(jù)量過大，超出了設(shè)置的最大值，導(dǎo)致報(bào)錯(cuò)：

解決方案：修改MySQL服務(wù)器max_allowed_packet屬性。

修改步驟：

1. 直接在MySQL安裝目錄下找到my.ini文件，在[mysqld]下面添加一行max_allowed_packet = 4G

1. 通過MySQL客戶端工具修改，這里以我常用的MySQL Workbench客戶端來修改，菜單欄找到Server -> Options File，點(diǎn)擊切換Networking標(biāo)簽：

Tip：不論哪種方式，修改完都要記得重啟MySQL，否則修改不生效哦。

我們來看下執(zhí)行結(jié)果：

結(jié)果：

使用Mybatis插入1000萬條數(shù)據(jù)到MySQL數(shù)據(jù)庫共花費(fèi)了199.8秒！

這結(jié)果是快還是慢？我們來具體分析一下耗時(shí)分布情況。

分析：

方法：

JDBC驅(qū)動(dòng)中有一個(gè)profileSQL屬性，可以跟蹤記錄SQL執(zhí)行時(shí)間，附上官方文檔介紹：

所以我們需要將數(shù)據(jù)庫連接加上profileSQL=true屬性。

再次看下執(zhí)行結(jié)果：

其中，duration指的是SQL執(zhí)行的時(shí)間，也就是說MySQL服務(wù)器執(zhí)行具體SQL語句的時(shí)間其實(shí)只有82.3秒，我們上面統(tǒng)計(jì)到Mybatis插入1000萬條數(shù)據(jù)花了近200秒的時(shí)間，那么這中間的100多秒都干嘛去了？

分析控制臺(tái)輸出的日志之后我發(fā)現(xiàn)了蹊蹺所在：從程序調(diào)用Mybatis的批量插入方法開始，到MySQL服務(wù)器執(zhí)行SQL，這中間正好差了100多秒，會(huì)是巧合嗎？

打斷點(diǎn)Debug追蹤到Mybatis解析SQL語句的方法：

這parse方法首先會(huì)讀取我們xml文件里的SQL模版拿到參數(shù)及參數(shù)類型信息，拼接生成SQL語句。

每條數(shù)據(jù)循環(huán)一次，那1000W條數(shù)據(jù)就要循環(huán)解析1000萬次，不慢才怪！

注意： MySQL的JDBC連接的url中要加rewriteBatchedStatements參數(shù)，并保證5.1.13以上版本的驅(qū)動(dòng)，才能實(shí)現(xiàn)高性能的批量插入 MySQL JDBC驅(qū)動(dòng)在默認(rèn)情況下會(huì)無視executeBatch()語句，把我們期望批量執(zhí)行的一組SQL語句拆散，一條一條地發(fā)給MySQL數(shù)據(jù)庫，批量插入實(shí)際上是單條插入，直接造成較低的性能 url: jdbc:mysql://127.0.0.1:3306/test1?allowMultiQueries=true&rewriteBatchedStatements=true //allowMultiQueries=true，允許一次性執(zhí)行多條SQL,批量插入時(shí)必須在連接地址后面加allowMultiQueries=true這個(gè)參數(shù) //rewriteBatchedStatements=true，批量將數(shù)據(jù)傳給MySQL，數(shù)據(jù)庫會(huì)更高性能的執(zhí)行批量處理，MySQL數(shù)據(jù)庫版本在5.1.13以上，才能實(shí)現(xiàn)高性能的批量插入

到了這里，關(guān)于java 批量插入千萬條數(shù)據(jù)優(yōu)化方案【值得收藏】的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！