1億條數(shù)據(jù)批量插入 MySQL，哪種方式最快？
利用JAVA向Mysql插入一億數(shù)量級(jí)數(shù)據(jù)—效率測(cè)評(píng)
這幾天研究mysql優(yōu)化中查詢效率時(shí)，發(fā)現(xiàn)測(cè)試的數(shù)據(jù)太少（10萬級(jí)別），利用 EXPLAIN 比較不同的 SQL 語句，不能夠得到比較有效的測(cè)評(píng)數(shù)據(jù)，大多模棱兩可，不敢通過這些數(shù)據(jù)下定論。
所以通過隨機(jī)生成人的姓名、年齡、性別、電話、email、地址 ，向mysql數(shù)據(jù)庫大量插入數(shù)據(jù)，便于用大量的數(shù)據(jù)測(cè)試 SQL 語句優(yōu)化效率。、在生成過程中發(fā)現(xiàn)使用不同的方法，效率天差萬別。
1、先上Mysql數(shù)據(jù)庫，隨機(jī)生成的人員數(shù)據(jù)圖。分別是ID、姓名、性別、年齡、Email、電話、住址。
下圖一共三千三百萬數(shù)據(jù)：
在數(shù)據(jù)量在億級(jí)別時(shí)，別點(diǎn)下面按鈕，會(huì)導(dǎo)致Navicat持續(xù)加載這億級(jí)別的數(shù)據(jù)，導(dǎo)致電腦死機(jī)。~覺著自己電腦配置不錯(cuò)的可以去試試，可能會(huì)有驚喜
2、本次測(cè)評(píng)一共通過三種策略，五種情況，進(jìn)行大批量數(shù)據(jù)插入測(cè)試
策略分別是：
Mybatis 輕量級(jí)框架插入（無事務(wù)） 采用JDBC直接處理（開啟事務(wù)、無事務(wù)） 采用JDBC批處理（開啟事務(wù)、無事務(wù)）
測(cè)試結(jié)果：
Mybatis輕量級(jí)插入 -> JDBC直接處理 -> JDBC 批處理。
JDBC 批處理，效率最高
第一種策略測(cè)試：
2.1 Mybatis 輕量級(jí)框架插入（無事務(wù)）
Mybatis是一個(gè)輕量級(jí)框架，它比hibernate輕便、效率高。
但是處理大批量的數(shù)據(jù)插入操作時(shí)，需要過程中實(shí)現(xiàn)一個(gè)ORM的轉(zhuǎn)換，本次測(cè)試存在實(shí)例，以及未開啟事務(wù)，導(dǎo)致mybatis效率很一般。
這里實(shí)驗(yàn)內(nèi)容是：
利用Spring框架生成mapper實(shí)例、創(chuàng)建人物實(shí)例對(duì)象 循環(huán)更改該實(shí)例對(duì)象屬性、并插入。
//代碼內(nèi)無事務(wù)
private long begin = 33112001;//起始id
private long end = begin+100000;//每次循環(huán)插入的數(shù)據(jù)量
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";
@org.junit.Test
public void insertBigData2()
{
//加載Spring，以及得到PersonMapper實(shí)例對(duì)象。這里創(chuàng)建的時(shí)間并不對(duì)最后結(jié)果產(chǎn)生很大的影響
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
PersonMapper pMapper = (PersonMapper) context.getBean("personMapper");
//創(chuàng)建一個(gè)人實(shí)例
Person person = new Person();
//計(jì)開始時(shí)間
long bTime = System.currentTimeMillis();
//開始循環(huán)，循環(huán)次數(shù)500W次。
for(int i=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++;
}
//計(jì)結(jié)束時(shí)間
long eTime = System.currentTimeMillis();
System.out.println("插入500W條數(shù)據(jù)耗時(shí)："+(eTime-bTime));
}
本想測(cè)試插入五百萬條數(shù)據(jù)，但是實(shí)際運(yùn)行過程中太慢，中途不得不終止程序。最后得到52W數(shù)據(jù)，大約耗時(shí)兩首歌的時(shí)間（7~9分鐘）。隨后，利用mybatis向mysql插入10000數(shù)據(jù)。
結(jié)果如下：
利用mybatis插入 一萬 條數(shù)據(jù)耗時(shí)：28613，即28.6秒
第二種策略測(cè)試：
2.2 采用JDBC直接處理（開啟事務(wù)、關(guān)閉事務(wù)）
采用JDBC直接處理的策略，這里的實(shí)驗(yàn)內(nèi)容分為開啟事務(wù)、未開啟事務(wù)是兩種，過程均如下：
利用PreparedStatment預(yù)編譯 循環(huán)，插入對(duì)應(yīng)數(shù)據(jù)，并存入
事務(wù)對(duì)于插入數(shù)據(jù)有多大的影響呢？ 看下面的實(shí)驗(yàn)結(jié)果:
//該代碼為開啟事務(wù)
private long begin = 33112001;//起始id
private long end = begin+100000;//每次循環(huán)插入的數(shù)據(jù)量
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";
@org.junit.Test
public void insertBigData3() {
//定義連接、statement對(duì)象
Connection conn = null;
PreparedStatement pstm = 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
String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
//預(yù)編譯sql
pstm = conn.prepareStatement(sql);
//開始總計(jì)時(shí)
long bTime1 = System.currentTimeMillis();
//循環(huán)10次，每次一萬數(shù)據(jù)，一共10萬
for(int i=0;i<10;i++) {
//開啟分段計(jì)時(shí)，計(jì)1W數(shù)據(jù)耗時(shí)
long bTime = System.currentTimeMillis();
//開始循環(huán)
while (begin < end) {
//賦值
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í)
long eTime = System.currentTimeMillis();
//輸出
System.out.println("成功插入1W條數(shù)據(jù)耗時(shí)："+(eTime-bTime));
}
//關(guān)閉總計(jì)時(shí)
long eTime1 = System.currentTimeMillis();
//輸出
System.out.println("插入10W數(shù)據(jù)共耗時(shí)："+(eTime1-bTime1));
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
}
1、我們首先利用上述代碼測(cè)試無事務(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é)論如下：
在未開啟事務(wù)的情況下，平均每 21.2 秒插入 一萬 數(shù)據(jù)。
接著我們測(cè)試開啟事務(wù)后，插入十萬條數(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é)論如下：
開啟事務(wù)后，平均每 3.9 秒插入 一萬 數(shù)據(jù)
第三種策略測(cè)試：
2.3 采用JDBC批處理（開啟事務(wù)、無事務(wù)）
采用JDBC批處理時(shí)需要注意一下幾點(diǎn)：
1、在URL連接時(shí)需要開啟批處理、以及預(yù)編譯
String url = “jdbc:mysql://localhost:3306/User?rewriteBatched
-Statements=true&useServerPrepStmts=false”;
2、PreparedStatement預(yù)處理sql語句必須放在循環(huán)體外
代碼如下：
private long begin = 33112001;//起始id
private long end = begin+100000;//每次循環(huán)插入的數(shù)據(jù)量
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";
@org.junit.Test
public void insertBigData() {
//定義連接、statement對(duì)象
Connection conn = null;
PreparedStatement pstm = 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
String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
//預(yù)編譯sql
pstm = conn.prepareStatement(sql);
//開始總計(jì)時(shí)
long bTime1 = System.currentTimeMillis();
//循環(huán)10次，每次十萬數(shù)據(jù)，一共1000萬
for(int i=0;i<10;i++) {
//開啟分段計(jì)時(shí)，計(jì)1W數(shù)據(jù)耗時(shí)
long bTime = System.currentTimeMillis();
//開始循環(huán)
while (begin < end) {
//賦值
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í)
long eTime = System.currentTimeMillis();
//輸出
System.out.println("成功插入10W條數(shù)據(jù)耗時(shí)："+(eTime-bTime));
}
//關(guān)閉總計(jì)時(shí)
long eTime1 = System.currentTimeMillis();
//輸出
System.out.println("插入100W數(shù)據(jù)共耗時(shí)："+(eTime1-bTime1));
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
}
首先開始測(cè)試
無事務(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批處理，未開啟事務(wù)下，平均每 2.1 秒插入 十萬 條數(shù)據(jù)
接著測(cè)試
開啟事務(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批處理，開啟事務(wù)，平均每 1.9 秒插入 十萬 條數(shù)據(jù)
3 總結(jié)
能夠看到，在開啟事務(wù)下 JDBC直接處理 和 JDBC批處理 均耗時(shí)更短。
Mybatis 輕量級(jí)框架插入 , mybatis在我這次實(shí)驗(yàn)被黑的可慘了，哈哈。實(shí)際開啟事務(wù)以后，差距不會(huì)這么大（差距10倍）。大家有興趣的可以接著去測(cè)試
JDBC直接處理，在本次實(shí)驗(yàn)，開啟事務(wù)和關(guān)閉事務(wù)，耗時(shí)差距5倍左右，并且這個(gè)倍數(shù)會(huì)隨著數(shù)據(jù)量的增大而增大。因?yàn)樵谖撮_啟事務(wù)時(shí)，更新10000條數(shù)據(jù)，就得訪問數(shù)據(jù)庫10000次。導(dǎo)致每次操作都需要操作一次數(shù)據(jù)庫。
JDBC批處理，在本次實(shí)驗(yàn)，開啟事務(wù)與關(guān)閉事務(wù)，耗時(shí)差距很微小（后面會(huì)增加測(cè)試，加大這個(gè)數(shù)值的差距）。但是能夠看到開啟事務(wù)以后，速度還是有提升。
結(jié)論：設(shè)計(jì)到大量單條數(shù)據(jù)的插入，使用JDBC批處理和事務(wù)混合速度最快
實(shí)測(cè)使用批處理+事務(wù)混合插入1億條數(shù)據(jù)耗時(shí)：174756毫秒
4 補(bǔ)充
JDBC批處理事務(wù)，開啟和關(guān)閉事務(wù)，測(cè)評(píng)插入20次，一次50W數(shù)據(jù)，一共一千萬數(shù)據(jù)耗時(shí)：
1、開啟事務(wù)(數(shù)據(jù)太長不全貼了)
插入1000W數(shù)據(jù)共耗時(shí)：197654
2、關(guān)閉事務(wù)(數(shù)據(jù)太長不全貼了)
插入1000W數(shù)據(jù)共耗時(shí)：200540
還是沒很大的差距~
借用：
分別是：
不用批處理，不用事務(wù)； 只用批處理，不用事務(wù)； 只用事務(wù)，不用批處理； 既用事務(wù)，也用批處理；（很明顯，這個(gè)最快，所以建議在處理大批量的數(shù)據(jù)時(shí)，同時(shí)使用批處理和事務(wù)）