利用JAVA向Mysql插入一億數(shù)量級數(shù)據(jù)—效率測評
這幾天研究mysql優(yōu)化中查詢效率時,發(fā)現(xiàn)測試的數(shù)據(jù)太少(10萬級別),利用 EXPLAIN 比較不同的 SQL 語句,不能夠得到比較有效的測評數(shù)據(jù),大多模棱兩可,不敢通過這些數(shù)據(jù)下定論。
所以通過隨機(jī)生成人的姓名、年齡、性別、電話、email、地址 ,向mysql數(shù)據(jù)庫大量插入數(shù)據(jù),便于用大量的數(shù)據(jù)測試 SQL 語句優(yōu)化效率。、在生成過程中發(fā)現(xiàn)使用不同的方法,效率天差萬別。
1、先上Mysql數(shù)據(jù)庫,隨機(jī)生成的人員數(shù)據(jù)圖。分別是ID、姓名、性別、年齡、Email、電話、住址。
下圖一共三千三百萬數(shù)據(jù):
在數(shù)據(jù)量在億級別時,別點(diǎn)下面按鈕,會導(dǎo)致Navicat持續(xù)加載這億級別的數(shù)據(jù),導(dǎo)致電腦死機(jī)。~覺著自己電腦配置不錯的可以去試試,可能會有驚喜
2、本次測評一共通過三種策略,五種情況,進(jìn)行大批量數(shù)據(jù)插入測試
策略分別是:
Mybatis 輕量級框架插入(無事務(wù))
采用JDBC直接處理(開啟事務(wù)、無事務(wù))
采用JDBC批處理(開啟事務(wù)、無事務(wù))
測試結(jié)果:
Mybatis輕量級插入 -> JDBC直接處理 -> JDBC 批處理。
JDBC 批處理,效率最高
第一種策略測試:
2.1 Mybatis 輕量級框架插入(無事務(wù))
Mybatis是一個輕量級框架,它比hibernate輕便、效率高。
但是處理大批量的數(shù)據(jù)插入操作時,需要過程中實現(xiàn)一個ORM的轉(zhuǎn)換,本次測試存在實例,以及未開啟事務(wù),導(dǎo)致mybatis效率很一般。
這里實驗內(nèi)容是:
利用Spring框架生成mapper實例、創(chuàng)建人物實例對象
循環(huán)更改該實例對象屬性、并插入。
//代碼內(nè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 publicvoidinsertBigData2() { //加載Spring,以及得到PersonMapper實例對象。這里創(chuàng)建的時間并不對最后結(jié)果產(chǎn)生很大的影響 ApplicationContextcontext=newClassPathXmlApplicationContext("applicationContext.xml"); PersonMapperpMapper=(PersonMapper)context.getBean("personMapper"); //創(chuàng)建一個人實例 Personperson=newPerson(); //計開始時間 longbTime=System.currentTimeMillis(); //開始循環(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í)行插入語句 ????????????pMapper.insert(person); ????????????begin++; ????????} ????????//計結(jié)束時間 ????????long?eTime?=?System.currentTimeMillis(); ????????System.out.println("插入500W條數(shù)據(jù)耗時:"+(eTime-bTime)); ????}
本想測試插入五百萬條數(shù)據(jù),但是實際運(yùn)行過程中太慢,中途不得不終止程序。最后得到52W數(shù)據(jù),大約耗時兩首歌的時間(7~9分鐘)。隨后,利用mybatis向mysql插入10000數(shù)據(jù)。
結(jié)果如下:
利用mybatis插入 一萬 條數(shù)據(jù)耗時:28613,即28.6秒
第二種策略測試:
2.2 采用JDBC直接處理(開啟事務(wù)、關(guān)閉事務(wù))
采用JDBC直接處理的策略,這里的實驗內(nèi)容分為開啟事務(wù)、未開啟事務(wù)是兩種,過程均如下:
利用PreparedStatment預(yù)編譯
循環(huán),插入對應(yīng)數(shù)據(jù),并存入
事務(wù)對于插入數(shù)據(jù)有多大的影響呢? 看下面的實驗結(jié)果:
//該代碼為開啟事務(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對象 Connectionconn=null; PreparedStatementpstm=null; try{ //加載jdbc驅(qū)動 Class.forName("com.mysql.jdbc.Driver"); //連接mysql conn=DriverManager.getConnection(url,user,password); //將自動提交關(guān)閉 conn.setAutoCommit(false); //編寫sql Stringsql="INSERTINTOpersonVALUES(?,?,?,?,?,?,?)"; //預(yù)編譯sql pstm=conn.prepareStatement(sql); //開始總計時 longbTime1=System.currentTimeMillis(); //循環(huán)10次,每次一萬數(shù)據(jù),一共10萬 for(inti=0;i<10;i++)?{ ????????????????//開啟分段計時,計1W數(shù)據(jù)耗時 ????????????????long?bTime?=?System.currentTimeMillis(); ????????????????//開始循環(huán) ????????????????while?(begin?
1、我們首先利用上述代碼測試無事務(wù)狀態(tài)下,插入10W條數(shù)據(jù)需要耗時多少。
如圖:
成功插入1W條數(shù)據(jù)耗時:21603 成功插入1W條數(shù)據(jù)耗時:20537 成功插入1W條數(shù)據(jù)耗時:20470 成功插入1W條數(shù)據(jù)耗時:21160 成功插入1W條數(shù)據(jù)耗時:23270 成功插入1W條數(shù)據(jù)耗時:21230 成功插入1W條數(shù)據(jù)耗時:20372 成功插入1W條數(shù)據(jù)耗時:22608 成功插入1W條數(shù)據(jù)耗時:20361 成功插入1W條數(shù)據(jù)耗時:20494 插入10W數(shù)據(jù)共耗時:212106
實驗結(jié)論如下:
在未開啟事務(wù)的情況下,平均每 21.2 秒插入 一萬 數(shù)據(jù)。
接著我們測試開啟事務(wù)后,插入十萬條數(shù)據(jù)耗時,如圖:
成功插入1W條數(shù)據(jù)耗時:4938 成功插入1W條數(shù)據(jù)耗時:3518 成功插入1W條數(shù)據(jù)耗時:3713 成功插入1W條數(shù)據(jù)耗時:3883 成功插入1W條數(shù)據(jù)耗時:3872 成功插入1W條數(shù)據(jù)耗時:3873 成功插入1W條數(shù)據(jù)耗時:3863 成功插入1W條數(shù)據(jù)耗時:3819 成功插入1W條數(shù)據(jù)耗時:3933 成功插入1W條數(shù)據(jù)耗時:3811 插入10W數(shù)據(jù)共耗時:39255
實驗結(jié)論如下:
開啟事務(wù)后,平均每 3.9 秒插入 一萬 數(shù)據(jù)
第三種策略測試:
2.3 采用JDBC批處理(開啟事務(wù)、無事務(wù))
采用JDBC批處理時需要注意一下幾點(diǎn):
1、在URL連接時需要開啟批處理、以及預(yù)編譯
Stringurl=“jdbc//localhost:3306/User?rewriteBatched -Statements=true&useServerPrepStmts=false”;
2、PreparedStatement預(yù)處理sql語句必須放在循環(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對象 Connectionconn=null; PreparedStatementpstm=null; try{ //加載jdbc驅(qū)動 Class.forName("com.mysql.jdbc.Driver"); //連接mysql conn=DriverManager.getConnection(url,user,password); //將自動提交關(guān)閉 //conn.setAutoCommit(false); //編寫sql Stringsql="INSERTINTOpersonVALUES(?,?,?,?,?,?,?)"; //預(yù)編譯sql pstm=conn.prepareStatement(sql); //開始總計時 longbTime1=System.currentTimeMillis(); //循環(huán)10次,每次十萬數(shù)據(jù),一共1000萬 for(inti=0;i<10;i++)?{ ????????????//開啟分段計時,計1W數(shù)據(jù)耗時 ????????????long?bTime?=?System.currentTimeMillis(); ????????????//開始循環(huán) ????????????while?(begin?
首先開始測試
無事務(wù),每次循環(huán)插入10W條數(shù)據(jù),循環(huán)10次,一共100W條數(shù)據(jù)。
結(jié)果如下圖:
成功插入10W條數(shù)據(jù)耗時:3832 成功插入10W條數(shù)據(jù)耗時:1770 成功插入10W條數(shù)據(jù)耗時:2628 成功插入10W條數(shù)據(jù)耗時:2140 成功插入10W條數(shù)據(jù)耗時:2148 成功插入10W條數(shù)據(jù)耗時:1757 成功插入10W條數(shù)據(jù)耗時:1767 成功插入10W條數(shù)據(jù)耗時:1832 成功插入10W條數(shù)據(jù)耗時:1830 成功插入10W條數(shù)據(jù)耗時:2031 插入100W數(shù)據(jù)共耗時:21737
實驗結(jié)果:
使用JDBC批處理,未開啟事務(wù)下,平均每 2.1 秒插入 十萬 條數(shù)據(jù)
接著測試
開啟事務(wù),每次循環(huán)插入10W條數(shù)據(jù),循環(huán)10次,一共100W條數(shù)據(jù)。
結(jié)果如下圖:
成功插入10W條數(shù)據(jù)耗時:3482 成功插入10W條數(shù)據(jù)耗時:1776 成功插入10W條數(shù)據(jù)耗時:1979 成功插入10W條數(shù)據(jù)耗時:1730 成功插入10W條數(shù)據(jù)耗時:1643 成功插入10W條數(shù)據(jù)耗時:1665 成功插入10W條數(shù)據(jù)耗時:1622 成功插入10W條數(shù)據(jù)耗時:1624 成功插入10W條數(shù)據(jù)耗時:1779 成功插入10W條數(shù)據(jù)耗時:1698 插入100W數(shù)據(jù)共耗時:19003
實驗結(jié)果:
使用JDBC批處理,開啟事務(wù),平均每 1.9 秒插入 十萬 條數(shù)據(jù)
3 總結(jié)
能夠看到,在開啟事務(wù)下 JDBC直接處理 和 JDBC批處理 均耗時更短。
Mybatis 輕量級框架插入 , mybatis在我這次實驗被黑的可慘了,哈哈。實際開啟事務(wù)以后,差距不會這么大(差距10倍)。大家有興趣的可以接著去測試
JDBC直接處理,在本次實驗,開啟事務(wù)和關(guān)閉事務(wù),耗時差距5倍左右,并且這個倍數(shù)會隨著數(shù)據(jù)量的增大而增大。因為在未開啟事務(wù)時,更新10000條數(shù)據(jù),就得訪問數(shù)據(jù)庫10000次。導(dǎo)致每次操作都需要操作一次數(shù)據(jù)庫。
JDBC批處理,在本次實驗,開啟事務(wù)與關(guān)閉事務(wù),耗時差距很微?。ê竺鏁黾訙y試,加大這個數(shù)值的差距)。但是能夠看到開啟事務(wù)以后,速度還是有提升。
結(jié)論:設(shè)計到大量單條數(shù)據(jù)的插入,使用JDBC批處理和事務(wù)混合速度最快
實測使用批處理+事務(wù)混合插入1億條數(shù)據(jù)耗時:174756毫秒
4 補(bǔ)充
JDBC批處理事務(wù),開啟和關(guān)閉事務(wù),測評插入20次,一次50W數(shù)據(jù),一共一千萬數(shù)據(jù)耗時:
1、開啟事務(wù)(數(shù)據(jù)太長不全貼了)
插入1000W數(shù)據(jù)共耗時:197654
2、關(guān)閉事務(wù)(數(shù)據(jù)太長不全貼了)
插入1000W數(shù)據(jù)共耗時:200540
還是沒很大的差距~
借用:
分別是:
不用批處理,不用事務(wù);
只用批處理,不用事務(wù);
只用事務(wù),不用批處理;
既用事務(wù),也用批處理;(很明顯,這個最快,所以建議在處理大批量的數(shù)據(jù)時,同時使用批處理和事務(wù))
審核編輯:劉清
-
JAVA
+關(guān)注
關(guān)注
19文章
2948瀏覽量
104380 -
SQL
+關(guān)注
關(guān)注
1文章
751瀏覽量
43995 -
JDBC
+關(guān)注
關(guān)注
0文章
25瀏覽量
13382 -
MYSQL數(shù)據(jù)庫
+關(guān)注
關(guān)注
0文章
95瀏覽量
9372
原文標(biāo)題:1億條數(shù)據(jù)批量插入 MySQL,哪種方式最快?
文章出處:【微信號:AndroidPush,微信公眾號:Android編程精選】歡迎添加關(guān)注!文章轉(zhuǎn)載請注明出處。
發(fā)布評論請先 登錄
相關(guān)推薦
評論