1億條數據批次插入 MySQL，哪種方式最快？

2024-04-29碼農

大家好，我是磊哥。

利用JAVA向Mysql插入一億數量級數據—效率測評

這幾天研究mysql最佳化中查詢效率時，發現測試的數據太少（10萬級別），利用 EXPLAIN 比較不同的 SQL 語句，不能夠得到比較有效的測評數據，大多模棱兩可，不敢透過這些數據下定論。

所以透過隨機生成人的姓名、年齡、性別、電話、email、地址，向mysql資料庫大量插入數據，便於用大量的數據測試 SQL 語句最佳化效率。、在生成過程中發現使用不同的方法，效率天差萬別。

1、先上Mysql資料庫，隨機生成的人員數據圖分別是ID、姓名、性別、年齡、Email、電話、住址；

下圖一共三千三百萬數據：

在數據量在億級別時，別點下面按鈕，會導致Navicat持續載入這億級別的數據，導致電腦當機。~覺著自己電腦配置不錯的可以去試試，可能會有驚喜

2、本次測評一共透過三種策略，五種情況，進行大批次數據插入測試；

策略分別是：

Mybatis 輕量級框架插入（無事務）

采用JDBC直接處理（開啟事務、無事務）

采用JDBC批次處理（開啟事務、無事務）

測試結果：

Mybatis輕量級插入 -> JDBC直接處理 -> JDBC 批次處理。

JDBC 批次處理，效率最高

第一種策略測試：

2.1 Mybatis 輕量級框架插入（無事務）

Mybatis是一個輕量級框架，它比hibernate輕便、效率高。

但是處理大批次的數據插入操作時，需要過程中實作一個ORM的轉換，本次測試存在例項，以及未開啟事務，導致mybatis效率很一般。

這裏實驗內容是：

利用Spring框架生成mapper例項、建立人物例項物件

迴圈更改該例項物件內容、並插入。

//程式碼內無事務 privatelong begin = 33112001;//起始id privatelong end = begin+100000;//每次迴圈插入的數據量 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 publicvoidinsertBigData2() { //載入Spring，以及得到PersonMapper例項物件。這裏建立的時間並不對最後結果產生很大的影響 ApplicationContext context = new classPathXmlApplicationContext("applicationContext.xml"); PersonMapper pMapper = (PersonMapper) context.getBean("personMapper"); //建立一個人例項 Person person = new Person(); //計開始時間 long bTime = System.currentTimeMillis(); //開始迴圈，迴圈次數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()); //執行插入語句 pMapper.insert(person); begin++; } //計結束時間 long eTime = System.currentTimeMillis(); System.out.println("插入500W條數據耗時："+(eTime-bTime)); }

本想測試插入五百萬條數據，但是實際執行過程中太慢，中途不得不終止程式。最後得到52W數據，大約耗時兩首歌的時間（7~9分鐘）。隨後，利用mybatis向mysql插入 10000 數據。

結果如下：

利用mybatis插入一萬條數據耗時：28613，即28.6秒

第二種策略測試：

2.2 采用JDBC直接處理（開啟事務、關閉事務）

采用JDBC直接處理的策略，這裏的實驗內容分為開啟事務、未開啟事務是兩種，過程均如下：

利用PreparedStatment預編譯

迴圈，插入對應數據，並存入

事務對於插入數據有多大的影響呢？ 看下面的實驗結果:

//該程式碼為開啟事務 privatelong begin = 33112001;//起始id privatelong end = begin+100000;//每次迴圈插入的數據量 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 publicvoidinsertBigData3(){ //定義連線、statement物件 Connection conn = null; PreparedStatement pstm = null; try { //載入jdbc驅動 class.forName("com.mysql.jdbc.Driver"); //連線mysql conn = DriverManager.getConnection(url, user, password); //將自動送出關閉 conn.setAutoCommit(false); //編寫sql String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)"; //預編譯sql pstm = conn.prepareStatement(sql); //開始總計時 long bTime1 = System.currentTimeMillis(); //迴圈10次，每次一萬數據，一共10萬 for(int i=0;i<10;i++) { //開啟分段計時，計1W數據耗時 long bTime = System.currentTimeMillis(); //開始迴圈 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()); //執行sql pstm.execute(); begin++; } //送出事務 conn.commit(); //邊界值自增10W end += 10000; //關閉分段計時 long eTime = System.currentTimeMillis(); //輸出 System.out.println("成功插入1W條數據耗時："+(eTime-bTime)); } //關閉總計時 long eTime1 = System.currentTimeMillis(); //輸出 System.out.println("插入10W數據共耗時："+(eTime1-bTime1)); } catch (SQLException e) { e.printStackTrace(); } catch ( classNotFoundException e1) { e1.printStackTrace(); } }

1、我們首先利用上述程式碼測試無事務狀態下，插入10W條數據需要耗時多少；

如圖：

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

實驗結論如下：

在未開啟事務的情況下，平均每 21.2 秒插入一萬數據。

接著我們測試開啟事務後，插入十萬條數據耗時，如圖：

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

實驗結論如下：

開啟事務後，平均每 3.9 秒插入一萬數據

第三種策略測試：

2.3 采用JDBC批次處理（開啟事務、無事務）

采用JDBC批次處理時需要註意一下幾點：

1、在URL連線時需要開啟批次處理、以及預編譯；

String url = 「jdbc:mysql://localhost:3306/User?rewriteBatched -Statements=true&useServerPrepStmts=false」;

2、 PreparedStatement預處理sql語句必須放在迴圈體外；

程式碼如下：

privatelong begin = 33112001;//起始id privatelong end = begin+100000;//每次迴圈插入的數據量 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 publicvoidinsertBigData(){ //定義連線、statement物件 Connection conn = null; PreparedStatement pstm = null; try { //載入jdbc驅動 class.forName("com.mysql.jdbc.Driver"); //連線mysql conn = DriverManager.getConnection(url, user, password); //將自動送出關閉 // conn.setAutoCommit(false); //編寫sql String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)"; //預編譯sql pstm = conn.prepareStatement(sql); //開始總計時 long bTime1 = System.currentTimeMillis(); //迴圈10次，每次十萬數據，一共1000萬 for(int i=0;i<10;i++) { //開啟分段計時，計1W數據耗時 long bTime = System.currentTimeMillis(); //開始迴圈 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()); //添加到同一個批次處理中 pstm.addBatch(); begin++; } //執行批次處理 pstm.executeBatch(); //送出事務 // conn.commit(); //邊界值自增10W end += 100000; //關閉分段計時 long eTime = System.currentTimeMillis(); //輸出 System.out.println("成功插入10W條數據耗時："+(eTime-bTime)); } //關閉總計時 long eTime1 = System.currentTimeMillis(); //輸出 System.out.println("插入100W數據共耗時："+(eTime1-bTime1)); } catch (SQLException e) { e.printStackTrace(); } catch ( classNotFoundException e1) { e1.printStackTrace(); } }

首先開始測試

無事務，每次迴圈插入10W條數據，迴圈10次，一共100W條數據。

結果如下圖：

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

實驗結果：

使用JDBC批次處理，未開啟事務下，平均每 2.1 秒插入十萬條數據

接著測試

開啟事務，每次迴圈插入10W條數據，迴圈10次，一共100W條數據。

結果如下圖：

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