java中JDBC實(shí)現(xiàn)往MySQL插入百萬(wàn)級(jí)數(shù)據(jù)的實(shí)例代碼
想往某個(gè)表中插入幾百萬(wàn)條數(shù)據(jù)做下測(cè)試，原先的想法，直接寫(xiě)個(gè)循環(huán)10W次隨便插入點(diǎn)數(shù)據(jù)試試吧，好吧，我真的很天真....
DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存儲(chǔ)過(guò)程則刪掉
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=100000 DO
INSERT INTO text VALUES(i,CONCAT('姓名',i),'XXXXXXXXX');
SET i = i+1;
END WHILE;
END $
CALL proc_initData();
執(zhí)行CALL proc_initData()后，本來(lái)想想，再慢10W條數(shù)據(jù)頂多30分鐘能搞定吧，結(jié)果我打了2把LOL后，回頭一看，還在執(zhí)行，此時(shí)心里是徹底懵逼的....待我打完第三把結(jié)束后，終于執(zhí)行完了，這種方法若是讓我等上幾百萬(wàn)條數(shù)據(jù)，是不是早上去上班，下午下班回來(lái)還沒(méi)結(jié)束呢？10W條數(shù)據(jù)，有圖有真相
JDBC往數(shù)據(jù)庫(kù)中普通插入方式
后面查了一下，使用JDBC批量操作往數(shù)據(jù)庫(kù)插入100W+的數(shù)據(jù)貌似也挺快的，
先來(lái)說(shuō)說(shuō)JDBC往數(shù)據(jù)庫(kù)中普通插入方式，簡(jiǎn)單的代碼大致如下，循環(huán)了1000條，中間加點(diǎn)隨機(jī)的數(shù)值，畢竟自己要拿數(shù)據(jù)測(cè)試，數(shù)據(jù)全都一樣也不好區(qū)分
private String url = "jdbc:mysql://localhost:3306/test01";
private String user = "root";
private String password = "123456";
@Test
public void Test(){
Connection conn = null;
PreparedStatement pstm =null;
ResultSet rt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
pstm = conn.prepareStatement(sql);
Long startTime = System.currentTimeMillis();
Random rand = new Random();
int a,b,c,d;
for (int i = 1; i <= 1000; i++) {
pstm.setInt(1, i);
pstm.setInt(2, i);
a = rand.nextInt(10);
b = rand.nextInt(10);
c = rand.nextInt(10);
d = rand.nextInt(10);
pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);27 pstm.executeUpdate();
}
Long endTime = System.currentTimeMillis();
System.out.println("OK,用時(shí)：" + (endTime - startTime));
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
if(pstm!=null){
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
輸出結(jié)果：OK,用時(shí)：738199，單位毫秒，也就是說(shuō)這種方式與直接數(shù)據(jù)庫(kù)中循環(huán)是差不多的。
在討論批量處理之前，先說(shuō)說(shuō)遇到的坑，首先，JDBC連接的url中要加rewriteBatchedStatements參數(shù)設(shè)為true是批量操作的前提，其次就是檢查mysql驅(qū)動(dòng)包時(shí)候是5.1.13以上版本（低于該版本不支持），因網(wǎng)上隨便下載了5.1.7版本的，然后執(zhí)行批量操作（100W條插入），結(jié)果因?yàn)轵?qū)動(dòng)器版本太低緣故并不支持，導(dǎo)致停止掉java程序后，mysql還在不斷的往數(shù)據(jù)庫(kù)中插入數(shù)據(jù)，最后不得不停止掉數(shù)據(jù)庫(kù)服務(wù)才停下來(lái)...
那么低版本的驅(qū)動(dòng)包是否對(duì)100W+數(shù)據(jù)插入就無(wú)力了呢？實(shí)際還有另外一種方式，效率相比來(lái)說(shuō)還是可以接受的。
使用事務(wù)提交方式
先將命令的提交方式設(shè)為false，即手動(dòng)提交conn.setAutoCommit(false);最后在所有命令執(zhí)行完之后再提交事務(wù)conn.commit();
private String url = "jdbc:mysql://localhost:3306/test01";
private String user = "root";
private String password = "123456";
@Test
public void Test(){
Connection conn = null;
PreparedStatement pstm =null;
ResultSet rt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
pstm = conn.prepareStatement(sql);
conn.setAutoCommit(false);
Long startTime = System.currentTimeMillis();
Random rand = new Random();
int a,b,c,d;
for (int i = 1; i <= 100000; i++) {
pstm.setInt(1, i);
pstm.setInt(2, i);
a = rand.nextInt(10);
b = rand.nextInt(10);
c = rand.nextInt(10);
d = rand.nextInt(10);
pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
pstm.executeUpdate();
}
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println("OK,用時(shí)：" + (endTime - startTime));
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
if(pstm!=null){
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
以上代碼插入10W條數(shù)據(jù)，輸出結(jié)果：OK,用時(shí)：18086，也就十八秒左右的時(shí)間，理論上100W也就是3分鐘這樣，勉強(qiáng)還可以接受。
批量處理
接下來(lái)就是批量處理了，注意，一定要5.1.13以上版本的驅(qū)動(dòng)包。
private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
private String user = "root";
private String password = "123456";
@Test
public void Test(){
Connection conn = null;
PreparedStatement pstm =null;
ResultSet rt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
pstm = conn.prepareStatement(sql);
Long startTime = System.currentTimeMillis();
Random rand = new Random();
int a,b,c,d;
for (int i = 1; i <= 100000; i++) {
pstm.setInt(1, i);
pstm.setInt(2, i);
a = rand.nextInt(10);
b = rand.nextInt(10);
c = rand.nextInt(10);
d = rand.nextInt(10);
pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
pstm.addBatch();
}
pstm.executeBatch();
Long endTime = System.currentTimeMillis();
System.out.println("OK,用時(shí)：" + (endTime - startTime));
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
if(pstm!=null){
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
10W輸出結(jié)果：OK,用時(shí)：3386，才3秒鐘.
批量操作+事務(wù)
然后我就想，要是批量操作+事務(wù)提交呢？會(huì)不會(huì)有神器的效果？
private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
private String user = "root";
private String password = "123456";
@Test
public void Test(){
Connection conn = null;
PreparedStatement pstm =null;
ResultSet rt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
pstm = conn.prepareStatement(sql);
conn.setAutoCommit(false);
Long startTime = System.currentTimeMillis();
Random rand = new Random();
int a,b,c,d;
for (int i = 1; i <= 100000; i++) {
pstm.setInt(1, i);
pstm.setInt(2, i);
a = rand.nextInt(10);
b = rand.nextInt(10);
c = rand.nextInt(10);
d = rand.nextInt(10);
pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
pstm.addBatch();
}
pstm.executeBatch();
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println("OK,用時(shí)：" + (endTime - startTime));
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
if(pstm!=null){
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
以下是100W數(shù)據(jù)輸出對(duì)比：（5.1.17版本MySql驅(qū)動(dòng)包下測(cè)試，交替兩種方式下的數(shù)據(jù)測(cè)試結(jié)果對(duì)比）
| 批量操作(10W) | 批量操作+事務(wù)提交(10W) | 批量操作(100W) | 批量錯(cuò)作+事務(wù)提交(100W) |
|
OK,用時(shí)：3901 |
OK,用時(shí)：3343 |
OK,用時(shí)：44242 |
OK,用時(shí)：39798 |
|
OK,用時(shí)：4142 |
OK,用時(shí)：2949 |
OK,用時(shí)：44248 |
OK,用時(shí)：39959 |
|
OK,用時(shí)：3664 |
OK,用時(shí)：2689 |
OK,用時(shí)：44389 |
OK,用時(shí)：39367 |
可見(jiàn)有一定的效率提升，但是并不是太明顯，當(dāng)然因?yàn)閿?shù)據(jù)差不算太大，也有可能存在偶然因數(shù)，畢竟每項(xiàng)只測(cè)3次。
預(yù)編譯+批量操作
網(wǎng)上還有人說(shuō)使用預(yù)編譯+批量操作的方式能夠提高效率更明顯，但是本人親測(cè)，效率不高反降，可能跟測(cè)試的數(shù)據(jù)有關(guān)吧。
預(yù)編譯的寫(xiě)法，只需在JDBC的連接url中將寫(xiě)入useServerPrepStmts=true即可，
如：
private String url = "jdbc:mysql://localhost:3306/test01?useServerPrepStmts=true&rewriteBatchedStatements=true"
好了，先到這里...
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
- 一步步教你利用Mysql存儲(chǔ)過(guò)程造百萬(wàn)級(jí)數(shù)據(jù)
- MySQL數(shù)據(jù)庫(kù)10秒內(nèi)插入百萬(wàn)條數(shù)據(jù)的實(shí)現(xiàn)
- MySQL 百萬(wàn)級(jí)數(shù)據(jù)的4種查詢優(yōu)化方式
- MySQL百萬(wàn)級(jí)數(shù)據(jù)量分頁(yè)查詢方法及其優(yōu)化建議
- MySQL百萬(wàn)級(jí)數(shù)據(jù)分頁(yè)查詢優(yōu)化方案
- MySQL單表百萬(wàn)數(shù)據(jù)記錄分頁(yè)性能優(yōu)化技巧
- MySQL使用MyFlash快速恢復(fù)誤刪除和修改的數(shù)據(jù)
- MySQL數(shù)據(jù)庫(kù)刪除數(shù)據(jù)后自增ID不連續(xù)的問(wèn)題及解決
- MySQL BinLog如何恢復(fù)誤更新刪除數(shù)據(jù)
- 使用 SQL 快速刪除數(shù)百萬(wàn)行數(shù)據(jù)的實(shí)踐記錄
相關(guān)文章
java如何獲取兩個(gè)List集合之間的交集、差集、并集
在日常開(kāi)發(fā)中經(jīng)常會(huì)遇到對(duì)2個(gè)集合的操作,例如2個(gè)集合之間取相同的元素（交集）,2個(gè)集合之間取不相同的元素（差集）等等,這篇文章主要給大家介紹了關(guān)于java如何獲取兩個(gè)List集合之間的交集、差集、并集的相關(guān)資料,需要的朋友可以參考下2024-02-02
SpringBoot中@Insert、@Update實(shí)現(xiàn)批量新增更新的使用示例
本文主要介紹了SpringBoot中@Insert、@Update實(shí)現(xiàn)批量新增更新的使用,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-10-10
Spring?中的?Service?有多個(gè)實(shí)現(xiàn)類(lèi)如何注入(多種方式)
這篇文章主要介紹了Spring?中的?Service?有多個(gè)實(shí)現(xiàn)類(lèi)如何注入，本文通過(guò)多種方式結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-07-07
java程序員必須要學(xué)會(huì)的linux命令總結(jié)(推薦)
下面小編就為大家分享一篇java程序員必須要學(xué)會(huì)的linux命令總結(jié)(推薦)。具有很好的參考價(jià)值。希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2017-11-11
Java算法之最長(zhǎng)公共子序列問(wèn)題(LCS)實(shí)例分析
這篇文章主要介紹了Java算法之最長(zhǎng)公共子序列問(wèn)題(LCS),結(jié)合實(shí)例形式分析了最長(zhǎng)公共子序列的原理及問(wèn)題解決方法,需要的朋友可以參考下2017-11-11
在java開(kāi)發(fā)中，HashMap是最常用、最常見(jiàn)的集合容器類(lèi)之一，文中通過(guò)示例代碼介紹HashMap為啥要二次Hash，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01
這篇文章主要為大家詳細(xì)介紹了基于UDP實(shí)現(xiàn)聊天室功能，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-04-04
SSM使用mybatis分頁(yè)插件pagehepler實(shí)現(xiàn)分頁(yè)示例
本篇文章主要介紹了SSM使用mybatis分頁(yè)插件pagehepler實(shí)現(xiàn)分頁(yè)示例，使用分頁(yè)插件的原因，簡(jiǎn)化了sql代碼的寫(xiě)法，實(shí)現(xiàn)較好的物理分頁(yè)，非常具有實(shí)用價(jià)值，需要的朋友可以參考下2018-03-03
SpringBoot JWT實(shí)現(xiàn)token登錄刷新功能
JWT本身是無(wú)狀態(tài)的，這點(diǎn)有別于傳統(tǒng)的session，不在服務(wù)端存儲(chǔ)憑證。這種特性使其在分布式場(chǎng)景，更便于擴(kuò)展使用。接下來(lái)通過(guò)本文給大家分享SpringBoot JWT實(shí)現(xiàn)token登錄刷新功能，感興趣的朋友一起看看吧2021-09-09
如何解決SpringBoot定時(shí)任務(wù)報(bào)錯(cuò)Unexpected error occurred
這篇文章主要介紹了如何解決SpringBoot定時(shí)任務(wù)報(bào)錯(cuò)Unexpected error occurred in scheduled task問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-08-08