MySQL大量数据入库的性能比较

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. } 

测试结果:

更多详情见请继续阅读下一页的精彩内容：