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

來源:互聯網
上載者:User

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

單位IM改版了
使用者聊天內容要存放在資料庫.

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

類比表結構如下

create table chat_message(
 
    id bigint primary key auto_increment,

    src_userid bigint not null,

    target_userid bigint not null,

    message varchar(200),

    ts timestamp not null default current_timestamp,

    s1 int,

    s2 int,

    s3 int,

    s4 int

);

下面代碼,分別使用四種方式,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

 

更多詳情見請繼續閱讀下一頁的精彩內容:

  • 1
  • 2
  • 下一頁

相關文章

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。