利用JAVA向Mysql插入一億數(shù)量級(jí)數(shù)據(jù)—效率測(cè)評(píng)

這幾天研究mysql優(yōu)化中查詢效率時(shí)，發(fā)現(xiàn)測(cè)試的數(shù)據(jù)太少（10萬(wàn)級(jí)別），利用 EXPLAIN 比較不同的 SQL 語(yǔ)句，不能夠得到比較有效的測(cè)評(píng)數(shù)據(jù)，大多模棱兩可，不敢通過(guò)這些數(shù)據(jù)下定論。

所以通過(guò)隨機(jī)生成人的姓名、年齡、性別、電話、email、地址 ，向mysql數(shù)據(jù)庫(kù)大量插入數(shù)據(jù)，便于用大量的數(shù)據(jù)測(cè)試 SQL 語(yǔ)句優(yōu)化效率。、在生成過(guò)程中發(fā)現(xiàn)使用不同的方法，效率天差萬(wàn)別。

1、先上Mysql數(shù)據(jù)庫(kù)，隨機(jī)生成的人員數(shù)據(jù)圖。分別是ID、姓名、性別、年齡、Email、電話、住址。

下圖一共三千三百萬(wàn)數(shù)據(jù)：

在數(shù)據(jù)量在億級(jí)別時(shí)，別點(diǎn)下面按鈕，會(huì)導(dǎo)致Navicat持續(xù)加載這億級(jí)別的數(shù)據(jù)，導(dǎo)致電腦死機(jī)。~覺(jué)著自己電腦配置不錯(cuò)的可以去試試，可能會(huì)有驚喜




2、本次測(cè)評(píng)一共通過(guò)三種策略，五種情況，進(jìn)行大批量數(shù)據(jù)插入測(cè)試

策略分別是：

• Mybatis 輕量級(jí)框架插入（無(wú)事務(wù)）
• 采用JDBC直接處理（開(kāi)啟事務(wù)、無(wú)事務(wù)）
• 采用JDBC批處理（開(kāi)啟事務(wù)、無(wú)事務(wù)）

測(cè)試結(jié)果：

Mybatis輕量級(jí)插入 -> JDBC直接處理 -> JDBC 批處理。

JDBC 批處理，效率最高

第一種策略測(cè)試：

2.1 Mybatis 輕量級(jí)框架插入（無(wú)事務(wù)）

Mybatis是一個(gè)輕量級(jí)框架，它比hibernate輕便、效率高。

但是處理大批量的數(shù)據(jù)插入操作時(shí)，需要過(guò)程中實(shí)現(xiàn)一個(gè)ORM的轉(zhuǎn)換，本次測(cè)試存在實(shí)例，以及未開(kāi)啟事務(wù)，導(dǎo)致mybatis效率很一般。

這里實(shí)驗(yàn)內(nèi)容是：

• 利用Spring框架生成mapper實(shí)例、創(chuàng)建人物實(shí)例對(duì)象
• 循環(huán)更改該實(shí)例對(duì)象屬性、并插入。

//代碼內(nèi)無(wú)事務(wù)

privatelongbegin=33112001;//起始id
privatelongend=begin+100000;//每次循環(huán)插入的數(shù)據(jù)量
privateStringurl="jdbc//localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
privateStringuser="root";
privateStringpassword="0203";


@org.junit.Test
publicvoidinsertBigData2()
{
//加載Spring，以及得到PersonMapper實(shí)例對(duì)象。這里創(chuàng)建的時(shí)間并不對(duì)最后結(jié)果產(chǎn)生很大的影響
ApplicationContextcontext=newClassPathXmlApplicationContext("applicationContext.xml");
PersonMapperpMapper=(PersonMapper)context.getBean("personMapper");
//創(chuàng)建一個(gè)人實(shí)例
Personperson=newPerson();
//計(jì)開(kāi)始時(shí)間
longbTime=System.currentTimeMillis();
//開(kāi)始循環(huán)，循環(huán)次數(shù)500W次。
for(inti=0;i<5000000;i++)
{
//為person賦值
person.setId(i);
person.setName(RandomValue.getChineseName());
person.setSex(RandomValue.name_sex);
person.setAge(RandomValue.getNum(1,100));
person.setEmail(RandomValue.getEmail(4,15));
person.setTel(RandomValue.getTel());
person.setAddress(RandomValue.getRoad());
//執(zhí)行插入語(yǔ)句
pMapper.insert(person);
begin++;
}
//計(jì)結(jié)束時(shí)間
longeTime=System.currentTimeMillis();
System.out.println("插入500W條數(shù)據(jù)耗時(shí)："+(eTime-bTime));
}

本想測(cè)試插入五百萬(wàn)條數(shù)據(jù)，但是實(shí)際運(yùn)行過(guò)程中太慢，中途不得不終止程序。最后得到52W數(shù)據(jù)，大約耗時(shí)兩首歌的時(shí)間（7~9分鐘）。隨后，利用mybatis向mysql插入10000數(shù)據(jù)。

結(jié)果如下：

利用mybatis插入 一萬(wàn) 條數(shù)據(jù)耗時(shí)：28613，即28.6秒

第二種策略測(cè)試：

2.2 采用JDBC直接處理（開(kāi)啟事務(wù)、關(guān)閉事務(wù)）

采用JDBC直接處理的策略，這里的實(shí)驗(yàn)內(nèi)容分為開(kāi)啟事務(wù)、未開(kāi)啟事務(wù)是兩種，過(guò)程均如下：

• 利用PreparedStatment預(yù)編譯
• 循環(huán)，插入對(duì)應(yīng)數(shù)據(jù)，并存入

事務(wù)對(duì)于插入數(shù)據(jù)有多大的影響呢？ 看下面的實(shí)驗(yàn)結(jié)果:

//該代碼為開(kāi)啟事務(wù)
privatelongbegin=33112001;//起始id
privatelongend=begin+100000;//每次循環(huán)插入的數(shù)據(jù)量
privateStringurl="jdbc//localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
privateStringuser="root";
privateStringpassword="0203";


@org.junit.Test
publicvoidinsertBigData3(){
//定義連接、statement對(duì)象
Connectionconn=null;
PreparedStatementpstm=null;
try{
//加載jdbc驅(qū)動(dòng)
Class.forName("com.mysql.jdbc.Driver");
//連接mysql
conn=DriverManager.getConnection(url,user,password);
//將自動(dòng)提交關(guān)閉
conn.setAutoCommit(false);
//編寫sql
Stringsql="INSERTINTOpersonVALUES(?,?,?,?,?,?,?)";
//預(yù)編譯sql
pstm=conn.prepareStatement(sql);
//開(kāi)始總計(jì)時(shí)
longbTime1=System.currentTimeMillis();

//循環(huán)10次，每次一萬(wàn)數(shù)據(jù)，一共10萬(wàn)
for(inti=0;i<10;i++){
//開(kāi)啟分段計(jì)時(shí)，計(jì)1W數(shù)據(jù)耗時(shí)
longbTime=System.currentTimeMillis();
//開(kāi)始循環(huán)
while(begin//賦值
pstm.setLong(1,begin);
pstm.setString(2,RandomValue.getChineseName());
pstm.setString(3,RandomValue.name_sex);
pstm.setInt(4,RandomValue.getNum(1,100));
pstm.setString(5,RandomValue.getEmail(4,15));
pstm.setString(6,RandomValue.getTel());
pstm.setString(7,RandomValue.getRoad());
//執(zhí)行sql
pstm.execute();
begin++;
}
//提交事務(wù)
conn.commit();
//邊界值自增10W
end+=10000;
//關(guān)閉分段計(jì)時(shí)
longeTime=System.currentTimeMillis();
//輸出
System.out.println("成功插入1W條數(shù)據(jù)耗時(shí)："+(eTime-bTime));
}
//關(guān)閉總計(jì)時(shí)
longeTime1=System.currentTimeMillis();
//輸出
System.out.println("插入10W數(shù)據(jù)共耗時(shí)："+(eTime1-bTime1));
}catch(SQLExceptione){
e.printStackTrace();
}catch(ClassNotFoundExceptione1){
e1.printStackTrace();
}
}

1、我們首先利用上述代碼測(cè)試無(wú)事務(wù)狀態(tài)下，插入10W條數(shù)據(jù)需要耗時(shí)多少。

如圖：

成功插入1W條數(shù)據(jù)耗時(shí)：21603
成功插入1W條數(shù)據(jù)耗時(shí)：20537
成功插入1W條數(shù)據(jù)耗時(shí)：20470
成功插入1W條數(shù)據(jù)耗時(shí)：21160
成功插入1W條數(shù)據(jù)耗時(shí)：23270
成功插入1W條數(shù)據(jù)耗時(shí)：21230
成功插入1W條數(shù)據(jù)耗時(shí)：20372
成功插入1W條數(shù)據(jù)耗時(shí)：22608
成功插入1W條數(shù)據(jù)耗時(shí)：20361
成功插入1W條數(shù)據(jù)耗時(shí)：20494
插入10W數(shù)據(jù)共耗時(shí)：212106

實(shí)驗(yàn)結(jié)論如下：

在未開(kāi)啟事務(wù)的情況下，平均每 21.2 秒插入 一萬(wàn) 數(shù)據(jù)。

接著我們測(cè)試開(kāi)啟事務(wù)后，插入十萬(wàn)條數(shù)據(jù)耗時(shí)，如圖：

成功插入1W條數(shù)據(jù)耗時(shí)：4938
成功插入1W條數(shù)據(jù)耗時(shí)：3518
成功插入1W條數(shù)據(jù)耗時(shí)：3713
成功插入1W條數(shù)據(jù)耗時(shí)：3883
成功插入1W條數(shù)據(jù)耗時(shí)：3872
成功插入1W條數(shù)據(jù)耗時(shí)：3873
成功插入1W條數(shù)據(jù)耗時(shí)：3863
成功插入1W條數(shù)據(jù)耗時(shí)：3819
成功插入1W條數(shù)據(jù)耗時(shí)：3933
成功插入1W條數(shù)據(jù)耗時(shí)：3811
插入10W數(shù)據(jù)共耗時(shí)：39255

實(shí)驗(yàn)結(jié)論如下：

開(kāi)啟事務(wù)后，平均每 3.9 秒插入 一萬(wàn) 數(shù)據(jù)

第三種策略測(cè)試：

2.3 采用JDBC批處理（開(kāi)啟事務(wù)、無(wú)事務(wù)）

采用JDBC批處理時(shí)需要注意一下幾點(diǎn)：

1、在URL連接時(shí)需要開(kāi)啟批處理、以及預(yù)編譯

Stringurl=“jdbc//localhost:3306/User?rewriteBatched
-Statements=true&useServerPrepStmts=false”;

2、PreparedStatement預(yù)處理sql語(yǔ)句必須放在循環(huán)體外

代碼如下：

privatelongbegin=33112001;//起始id
privatelongend=begin+100000;//每次循環(huán)插入的數(shù)據(jù)量
privateStringurl="jdbc//localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
privateStringuser="root";
privateStringpassword="0203";


@org.junit.Test
publicvoidinsertBigData(){
//定義連接、statement對(duì)象
Connectionconn=null;
PreparedStatementpstm=null;
try{
//加載jdbc驅(qū)動(dòng)
Class.forName("com.mysql.jdbc.Driver");
//連接mysql
conn=DriverManager.getConnection(url,user,password);
//將自動(dòng)提交關(guān)閉
//conn.setAutoCommit(false);
//編寫sql
Stringsql="INSERTINTOpersonVALUES(?,?,?,?,?,?,?)";
//預(yù)編譯sql
pstm=conn.prepareStatement(sql);
//開(kāi)始總計(jì)時(shí)
longbTime1=System.currentTimeMillis();

//循環(huán)10次，每次十萬(wàn)數(shù)據(jù)，一共1000萬(wàn)
for(inti=0;i<10;i++){

//開(kāi)啟分段計(jì)時(shí)，計(jì)1W數(shù)據(jù)耗時(shí)
longbTime=System.currentTimeMillis();
//開(kāi)始循環(huán)
while(begin//賦值
pstm.setLong(1,begin);
pstm.setString(2,RandomValue.getChineseName());
pstm.setString(3,RandomValue.name_sex);
pstm.setInt(4,RandomValue.getNum(1,100));
pstm.setString(5,RandomValue.getEmail(4,15));
pstm.setString(6,RandomValue.getTel());
pstm.setString(7,RandomValue.getRoad());
//添加到同一個(gè)批處理中
pstm.addBatch();
begin++;
}
//執(zhí)行批處理
pstm.executeBatch();
//提交事務(wù)
//conn.commit();
//邊界值自增10W
end+=100000;
//關(guān)閉分段計(jì)時(shí)
longeTime=System.currentTimeMillis();
//輸出
System.out.println("成功插入10W條數(shù)據(jù)耗時(shí)："+(eTime-bTime));
}
//關(guān)閉總計(jì)時(shí)
longeTime1=System.currentTimeMillis();
//輸出
System.out.println("插入100W數(shù)據(jù)共耗時(shí)："+(eTime1-bTime1));
}catch(SQLExceptione){
e.printStackTrace();
}catch(ClassNotFoundExceptione1){
e1.printStackTrace();
}
}

首先開(kāi)始測(cè)試

無(wú)事務(wù)，每次循環(huán)插入10W條數(shù)據(jù)，循環(huán)10次，一共100W條數(shù)據(jù)。

結(jié)果如下圖：

成功插入10W條數(shù)據(jù)耗時(shí)：3832
成功插入10W條數(shù)據(jù)耗時(shí)：1770
成功插入10W條數(shù)據(jù)耗時(shí)：2628
成功插入10W條數(shù)據(jù)耗時(shí)：2140
成功插入10W條數(shù)據(jù)耗時(shí)：2148
成功插入10W條數(shù)據(jù)耗時(shí)：1757
成功插入10W條數(shù)據(jù)耗時(shí)：1767
成功插入10W條數(shù)據(jù)耗時(shí)：1832
成功插入10W條數(shù)據(jù)耗時(shí)：1830
成功插入10W條數(shù)據(jù)耗時(shí)：2031
插入100W數(shù)據(jù)共耗時(shí)：21737

實(shí)驗(yàn)結(jié)果：

使用JDBC批處理，未開(kāi)啟事務(wù)下，平均每 2.1 秒插入 十萬(wàn) 條數(shù)據(jù)

接著測(cè)試

開(kāi)啟事務(wù)，每次循環(huán)插入10W條數(shù)據(jù)，循環(huán)10次，一共100W條數(shù)據(jù)。

結(jié)果如下圖：

成功插入10W條數(shù)據(jù)耗時(shí)：3482
成功插入10W條數(shù)據(jù)耗時(shí)：1776
成功插入10W條數(shù)據(jù)耗時(shí)：1979
成功插入10W條數(shù)據(jù)耗時(shí)：1730
成功插入10W條數(shù)據(jù)耗時(shí)：1643
成功插入10W條數(shù)據(jù)耗時(shí)：1665
成功插入10W條數(shù)據(jù)耗時(shí)：1622
成功插入10W條數(shù)據(jù)耗時(shí)：1624
成功插入10W條數(shù)據(jù)耗時(shí)：1779
成功插入10W條數(shù)據(jù)耗時(shí)：1698
插入100W數(shù)據(jù)共耗時(shí)：19003

實(shí)驗(yàn)結(jié)果：

使用JDBC批處理，開(kāi)啟事務(wù)，平均每 1.9 秒插入 十萬(wàn) 條數(shù)據(jù)

3 總結(jié)

能夠看到，在開(kāi)啟事務(wù)下 JDBC直接處理 和 JDBC批處理 均耗時(shí)更短。

• Mybatis 輕量級(jí)框架插入 , mybatis在我這次實(shí)驗(yàn)被黑的可慘了，哈哈。實(shí)際開(kāi)啟事務(wù)以后，差距不會(huì)這么大（差距10倍）。大家有興趣的可以接著去測(cè)試

• JDBC直接處理，在本次實(shí)驗(yàn)，開(kāi)啟事務(wù)和關(guān)閉事務(wù)，耗時(shí)差距5倍左右，并且這個(gè)倍數(shù)會(huì)隨著數(shù)據(jù)量的增大而增大。因?yàn)樵谖撮_(kāi)啟事務(wù)時(shí)，更新10000條數(shù)據(jù)，就得訪問(wèn)數(shù)據(jù)庫(kù)10000次。導(dǎo)致每次操作都需要操作一次數(shù)據(jù)庫(kù)。

• JDBC批處理，在本次實(shí)驗(yàn)，開(kāi)啟事務(wù)與關(guān)閉事務(wù)，耗時(shí)差距很微小（后面會(huì)增加測(cè)試，加大這個(gè)數(shù)值的差距）。但是能夠看到開(kāi)啟事務(wù)以后，速度還是有提升。

結(jié)論：設(shè)計(jì)到大量單條數(shù)據(jù)的插入，使用JDBC批處理和事務(wù)混合速度最快

實(shí)測(cè)使用批處理+事務(wù)混合插入1億條數(shù)據(jù)耗時(shí)：174756毫秒

4 補(bǔ)充

JDBC批處理事務(wù)，開(kāi)啟和關(guān)閉事務(wù)，測(cè)評(píng)插入20次，一次50W數(shù)據(jù)，一共一千萬(wàn)數(shù)據(jù)耗時(shí)：

1、開(kāi)啟事務(wù)(數(shù)據(jù)太長(zhǎng)不全貼了)

插入1000W數(shù)據(jù)共耗時(shí)：197654

2、關(guān)閉事務(wù)(數(shù)據(jù)太長(zhǎng)不全貼了)

插入1000W數(shù)據(jù)共耗時(shí)：200540

還是沒(méi)很大的差距~

借用：

分別是：

• 不用批處理，不用事務(wù)；
• 只用批處理，不用事務(wù)；
• 只用事務(wù)，不用批處理；
• 既用事務(wù)，也用批處理；（很明顯，這個(gè)最快，所以建議在處理大批量的數(shù)據(jù)時(shí)，同時(shí)使用批處理和事務(wù)）

審核編輯 ：李倩