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

测试结果:

执行方式

执行时间(毫秒)

每秒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