单位IM改版了
用户聊天内容要存放在数据库.
一般JAVA Insert MySQL有如下几种方式
1.自动提交Insert
2.事务提交Insert
3.批量提交
4.使用Load File接口
模拟表结构如下
下面代码,分别使用四种方式,Insert 2w记录.记录执行时间.
依赖
commons-lang3-3.3.2.jar
mysql-connector-java-5.1.31-bin.jar(低版本驱动有性能影响)
测试结果:
用户聊天内容要存放在数据库.
一般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(低版本驱动有性能影响)
- import java.io.ByteArrayInputStream;
- import java.io.InputStream;
- import java.io.UnsupportedEncodingException;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.SQLException;
- import org.apache.commons.lang3.RandomStringUtils;
- public class Main {
- private static String URL = "jdbc:mysql://127.0.0.1:3306/mvbox";
- private static String USERNAME = "xx";
- private static String PWD = "xx";
- private static int MAX = 20000;
- private static String SQL = "insert into chat_message(src_userid,target_userid,message,s1,s2,s3,s4) values(?,?,?,?,?,?,?)";
- public static void main(String[] args) throws ClassNotFoundException, SQLException, UnsupportedEncodingException {
- long start = System.currentTimeMillis();
- testLoadFile(100);
- long end = System.currentTimeMillis();
- System.out.println((end - start));
- System.out.println(MAX / ((end - start) / 1000));
- }
- private static Connection getConnection() throws SQLException, ClassNotFoundException {
- Class.forName("com.mysql.jdbc.Driver");
- Connection con = DriverManager.getConnection(URL, USERNAME, PWD);
- return con;
- }
- private static void testInsert() throws ClassNotFoundException, SQLException {
- Connection con = getConnection();
- con.setAutoCommit(false);
- PreparedStatement pt = con.prepareStatement(SQL);
- int i = 0;
- while (i < MAX) {
- pt.setLong(1, 1 + (int) (Math.random() * 100000000));
- pt.setLong(2, 1 + (int) (Math.random() * 100000000));
- pt.setString(3, RandomStringUtils.randomAscii(200));
- pt.setInt(4, 1);
- pt.setInt(5, 1);
- pt.setInt(6, 1);
- pt.setInt(7, 1);
- pt.executeUpdate();
- con.commit();
- i++;
- }
- con.close();
- }
- private static void testInsertAutoCommit() throws ClassNotFoundException, SQLException {
- Connection con = getConnection();
- con.setAutoCommit(true);
- PreparedStatement pt = con.prepareStatement(SQL);
- int i = 0;
- while (i < MAX) {
- pt.setLong(1, 1 + (int) (Math.random() * 100000000));
- pt.setLong(2, 1 + (int) (Math.random() * 100000000));
- pt.setString(3, RandomStringUtils.randomAscii(200));
- pt.setInt(4, 1);
- pt.setInt(5, 1);
- pt.setInt(6, 1);
- pt.setInt(7, 1);
- pt.executeUpdate();
- i++;
- }
- con.close();
- }
- private static void testBatchInsert(int batchSize) throws ClassNotFoundException, SQLException {
- Connection con = getConnection();
- con.setAutoCommit(false);
- PreparedStatement pt = con.prepareStatement(SQL);
- int i = 0;
- while (i < MAX) {
- pt.setLong(1, 1 + (int) (Math.random() * 100000000));
- pt.setLong(2, 1 + (int) (Math.random() * 100000000));
- pt.setString(3, RandomStringUtils.randomAscii(200));
- pt.setInt(4, 1);
- pt.setInt(5, 1);
- pt.setInt(6, 1);
- pt.setInt(7, 1);
- pt.addBatch();
- if (i % batchSize == 1) {
- pt.executeBatch();
- con.commit();
- }
- i++;
- }
- pt.executeBatch();
- con.commit();
- con.close();
- }
- private static void testLoadFile(int batchSize)
- throws ClassNotFoundException, SQLException, UnsupportedEncodingException {
- String fieldsterminated = "\t\t";
- String linesterminated = "\t\r\n";
- String loadDataSql = "LOAD DATA LOCAL INFILE 'sql.csv' INTO TABLE chat_message FIELDS TERMINATED BY '"
- + fieldsterminated + "' LINES TERMINATED BY '" + linesterminated
- + "' (src_userid,target_userid,message,s1,s2,s3,s4) ";
- Connection con = getConnection();
- con.setAutoCommit(false);
- PreparedStatement pt = con.prepareStatement(loadDataSql);
- com.mysql.jdbc.PreparedStatement mysqlStatement = null;
- if (pt.isWrapperFor(com.mysql.jdbc.Statement.class)) {
- mysqlStatement = pt.unwrap(com.mysql.jdbc.PreparedStatement.class);
- }
- int i = 0;
- StringBuilder sb = new StringBuilder(10000);
- while (i < MAX) {
- sb.append(1 + (int) (Math.random() * 100000000));
- sb.append(fieldsterminated);
- sb.append(1 + (int) (Math.random() * 100000000));
- sb.append(fieldsterminated);
- sb.append(RandomStringUtils.randomAscii(200).replaceAll("\\\\", " "));
- sb.append(fieldsterminated);
- sb.append(1);
- sb.append(fieldsterminated);
- sb.append(1);
- sb.append(fieldsterminated);
- sb.append(1);
- sb.append(fieldsterminated);
- sb.append(1);
- sb.append(linesterminated);
- if (i % batchSize == 1) {
- byte[] bytes = sb.toString().getBytes();
- InputStream in = new ByteArrayInputStream(bytes);
- mysqlStatement.setLocalInfileInputStream(in);
- mysqlStatement.executeUpdate();
- con.commit();
- sb = new StringBuilder(10000);
- }
- i++;
- }
- byte[] bytes = sb.toString().getBytes();
- InputStream in = new ByteArrayInputStream(bytes);
- mysqlStatement.setLocalInfileInputStream(in);
- mysqlStatement.executeUpdate();
- con.commit();
- con.close();
- }
- }
测试结果:
执行方式 | 执行时间(毫秒) | 每秒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 |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-1841299/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-1841299/