单位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 = c