MySQL大量資料入庫的效能比較

壹頁書發表於2015-11-19
MySql
單位IM改版了
使用者聊天內容要存放在資料庫.

一般JAVA Insert MySQL有如下幾種方式
1.自動提交Insert
2.事務提交Insert
3.批次提交
4.使用Load File介面

模擬表結構如下
  1. create table chat_message(
  2.     id bigint primary key auto_increment,
  3.     src_userid bigint not null,
  4.     target_userid bigint not null,
  5.     message varchar(200),
  6.     ts timestamp not null default current_timestamp,
  7.     s1 int,
  8.     s2 int,
  9.     s3 int,
  10.     s4 int
  11. );

下面程式碼,分別使用四種方式,Insert 2w記錄.記錄執行時間.

依賴
commons-lang3-3.3.2.jar
mysql-connector-java-5.1.31-bin.jar(低版本驅動有效能影響)
  1. import java.io.ByteArrayInputStream;  
  2. import java.io.InputStream;  
  3. import java.io.UnsupportedEncodingException;  
  4. import java.sql.Connection;  
  5. import java.sql.DriverManager;  
  6. import java.sql.PreparedStatement;  
  7. import java.sql.SQLException;  
  8.   
  9. import org.apache.commons.lang3.RandomStringUtils;  
  10.   
  11. public class Main {  
  12.     private static String URL = "jdbc:mysql://127.0.0.1:3306/mvbox";  
  13.     private static String USERNAME = "xx";  
  14.     private static String PWD = "xx";  
  15.     private static int MAX = 20000;  
  16.     private static String SQL = "insert into chat_message(src_userid,target_userid,message,s1,s2,s3,s4) values(?,?,?,?,?,?,?)";  
  17.   
  18.     public static void main(String[] args) throws ClassNotFoundException, SQLException, UnsupportedEncodingException {  
  19.         long start = System.currentTimeMillis();  
  20.         testLoadFile(100);  
  21.         long end = System.currentTimeMillis();  
  22.         System.out.println((end - start));  
  23.         System.out.println(MAX / ((end - start) / 1000));  
  24.     }  
  25.   
  26.     private static Connection getConnection() throws SQLException, ClassNotFoundException {  
  27.         Class.forName("com.mysql.jdbc.Driver");  
  28.         Connection con = DriverManager.getConnection(URL, USERNAME, PWD);  
  29.         return con;  
  30.     }  
  31.   
  32.     private static void testInsert() throws ClassNotFoundException, SQLException {  
  33.         Connection con = getConnection();  
  34.         con.setAutoCommit(false);  
  35.         PreparedStatement pt = con.prepareStatement(SQL);  
  36.         int i = 0;  
  37.         while (i < MAX) {  
  38.             pt.setLong(1, 1 + (int) (Math.random() * 100000000));  
  39.             pt.setLong(2, 1 + (int) (Math.random() * 100000000));  
  40.             pt.setString(3, RandomStringUtils.randomAscii(200));  
  41.             pt.setInt(4, 1);  
  42.             pt.setInt(5, 1);  
  43.             pt.setInt(6, 1);  
  44.             pt.setInt(7, 1);  
  45.             pt.executeUpdate();  
  46.             con.commit();  
  47.             i++;  
  48.         }  
  49.         con.close();  
  50.     }  
  51.   
  52.     private static void testInsertAutoCommit() throws ClassNotFoundException, SQLException {  
  53.         Connection con = getConnection();  
  54.         con.setAutoCommit(true);  
  55.         PreparedStatement pt = con.prepareStatement(SQL);  
  56.         int i = 0;  
  57.         while (i < MAX) {  
  58.             pt.setLong(1, 1 + (int) (Math.random() * 100000000));  
  59.             pt.setLong(2, 1 + (int) (Math.random() * 100000000));  
  60.             pt.setString(3, RandomStringUtils.randomAscii(200));  
  61.             pt.setInt(4, 1);  
  62.             pt.setInt(5, 1);  
  63.             pt.setInt(6, 1);  
  64.             pt.setInt(7, 1);  
  65.             pt.executeUpdate();  
  66.             i++;  
  67.         }  
  68.         con.close();  
  69.     }  
  70.   
  71.     private static void testBatchInsert(int batchSize) throws ClassNotFoundException, SQLException {  
  72.         Connection con = getConnection();  
  73.         con.setAutoCommit(false);  
  74.         PreparedStatement pt = con.prepareStatement(SQL);  
  75.         int i = 0;  
  76.         while (i < MAX) {  
  77.             pt.setLong(1, 1 + (int) (Math.random() * 100000000));  
  78.             pt.setLong(2, 1 + (int) (Math.random() * 100000000));  
  79.             pt.setString(3, RandomStringUtils.randomAscii(200));  
  80.             pt.setInt(4, 1);  
  81.             pt.setInt(5, 1);  
  82.             pt.setInt(6, 1);  
  83.             pt.setInt(7, 1);  
  84.             pt.addBatch();  
  85.             if (i % batchSize == 1) {  
  86.                 pt.executeBatch();  
  87.                 con.commit();  
  88.             }  
  89.             i++;  
  90.         }  
  91.         pt.executeBatch();  
  92.         con.commit();  
  93.         con.close();  
  94.     }  
  95.   
  96.     private static void testLoadFile(int batchSize)  
  97.             throws ClassNotFoundException, SQLException, UnsupportedEncodingException {  
  98.         String fieldsterminated = "\t\t";  
  99.         String linesterminated = "\t\r\n";  
  100.         String loadDataSql = "LOAD DATA LOCAL INFILE 'sql.csv' INTO TABLE chat_message FIELDS TERMINATED BY '"  
  101.                 + fieldsterminated + "'  LINES TERMINATED BY '" + linesterminated  
  102.                 + "' (src_userid,target_userid,message,s1,s2,s3,s4) ";  
  103.         Connection con = getConnection();  
  104.         con.setAutoCommit(false);  
  105.         PreparedStatement pt = con.prepareStatement(loadDataSql);  
  106.         com.mysql.jdbc.PreparedStatement mysqlStatement = null;  
  107.         if (pt.isWrapperFor(com.mysql.jdbc.Statement.class)) {  
  108.             mysqlStatement = pt.unwrap(com.mysql.jdbc.PreparedStatement.class);  
  109.         }  
  110.   
  111.         int i = 0;  
  112.         StringBuilder sb = new StringBuilder(10000);  
  113.         while (i < MAX) {  
  114.             sb.append(1 + (int) (Math.random() * 100000000));  
  115.             sb.append(fieldsterminated);  
  116.             sb.append(1 + (int) (Math.random() * 100000000));  
  117.             sb.append(fieldsterminated);  
  118.             sb.append(RandomStringUtils.randomAscii(200).replaceAll("\\\\", " "));  
  119.             sb.append(fieldsterminated);  
  120.             sb.append(1);  
  121.             sb.append(fieldsterminated);  
  122.             sb.append(1);  
  123.             sb.append(fieldsterminated);  
  124.             sb.append(1);  
  125.             sb.append(fieldsterminated);  
  126.             sb.append(1);  
  127.             sb.append(linesterminated);  
  128.             if (i % batchSize == 1) {  
  129.                 byte[] bytes = sb.toString().getBytes();  
  130.                 InputStream in = new ByteArrayInputStream(bytes);  
  131.                 mysqlStatement.setLocalInfileInputStream(in);  
  132.                 mysqlStatement.executeUpdate();  
  133.                 con.commit();  
  134.                 sb = new StringBuilder(10000);  
  135.             }  
  136.   
  137.             i++;  
  138.         }  
  139.         byte[] bytes = sb.toString().getBytes();  
  140.         InputStream in = new ByteArrayInputStream(bytes);  
  141.         mysqlStatement.setLocalInfileInputStream(in);  
  142.         mysqlStatement.executeUpdate();  
  143.         con.commit();  
  144.   
  145.         con.close();  
  146.     }  
  147. }  

測試結果:
執行方式 執行時間(毫秒) 每秒Insert數量
自動提交
 17437
 1176
事務提交
 22990
 909
batchInsert 每10條提交
 12646
 1666
batchInsert 每50條提交
 13758
 1538
batchInsert 每100條提交
 15870
 1333
loadfile 每10條提交
 6973
 3333
loadfile 每50條提交
 5037
 4000
loadfile 每100條提交
 4175
 5000



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1841299/，如需轉載，請註明出處，否則將追究法律責任。

相關文章