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