Mysql批量插入数据问题解决和优化_mysql 批量提交的时候耗时很长

Mysql批量插入数据问题解决和优化

一、问题描述

项目中mysql批量插入大概50000左右数据，使用事务和批量，但是速度依旧很慢，大约60s左右，迫切希望改进这个问题。

二、问题原因

尽管是批量addBatch()但是，mysql却默认没有执行批量，时间还是一条一条添加导致速度非常慢。解决办法：链接mysql字符串添加 rewriteBatchedStatements=true

三、解决和验证

1.测试代码

（1）新建测试表语句

create table mysqltest(
id INT,
name char(10)
)

create table mysqltest1(
id INT,
name char(10)
)
1
2
3
4
5
6
7
8
9

（2）Mysql链接工厂类

package bulk.mysql;
import java.sql.Connection;
import java.sql.DriverManager;

public class MySQLConnections {
    private String driver = "";
    private String dbURL = "";
    private String user = "";
    private String password = "";
    private static MySQLConnections connection = null;

    private MySQLConnections() throws Exception {
        driver = "com.mysql.jdbc.Driver";
        dbURL = "jdbc:mysql://ip:3306/test?rewriteBatchedStatements=true";
        user = "root";
        password = "passwd";
        System.out.println("dbURL:" + dbURL);
    }

    public static Connection getConnection() {
        Connection conn = null;
        if (connection == null) {
            try {
                connection = new MySQLConnections();
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
        try {
            Class.forName(connection.driver);
            conn = DriverManager.getConnection(connection.dbURL,
                    connection.user, connection.password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

（3）Mysql更新类

package bulk.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class MySQLUpdate {
    static Connection con = MySQLConnections.getConnection();
    static PreparedStatement stmt = null;
    public static int executeInsert() throws SQLException {
        int i = 0;
        //设置批量处理的数量
        int batchSize = 5000;
        stmt = con.prepareStatement("insert into mysqltest (id,name) "
                + "values (?,?)");
        // 关闭事务自动提交 ,这一行必须加上
        con.setAutoCommit(false);
        for (int j = 0; j < 50005; j++){
            ++i;
            stmt.setInt(1, j);
            stmt.setString(2, "name");
            stmt.addBatch();
            if ( i % batchSize == 0 ) {
                stmt.executeBatch();
                con.commit();
            }
        }
        if ( i % batchSize != 0 ) {
            stmt.executeBatch();
            con.commit();
        }
        return i;
    }

    public static  void executeInsert2() throws SQLException {
        // 关闭事务自动提交 ,这一行必须加上
        con.setAutoCommit(false);
        stmt = con.prepareStatement("insert into mysqltest (id,name) "
                + "values (?,?)");
        for (int j = 0; j < 50002; j++){
            stmt.setInt(1, j);
            stmt.setString(2, "name");
            stmt.addBatch();
        }
        stmt.executeBatch();
        con.commit();
        stmt.close();
        con.close();
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

（4）Mysql测试类

package bulk.mysql;

import java.sql.SQLException;

public class Test {

    public static void main(String[] args) throws SQLException {
        long begin1 = System.currentTimeMillis();
        MySQLUpdate.executeInsert();
        long end1 = System.currentTimeMillis();
        System.out.println("程序运行时间为：" + (end1 - begin1));

        long begin2 = System.currentTimeMillis();
        MySQLUpdate.executeInsert2();
        long end2 = System.currentTimeMillis();
        System.out.println("程序运行时间为：" + (end2 - begin2));
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.测试链接字符串不同配置

（1）为dbURL = “jdbc:mysql://ip:3306/test”;时

运行结果

程序测试插入时间
程序运行时间为：62095
程序运行时间为：60129
1
2
3

（2）为dbURL = “jdbc:mysql://ip:3306/test?rewriteBatchedStatements=true”;时

运行结果

程序运行时间为：2181
程序运行时间为：419
1
2

3.结论

优化了一百倍左右，这个真的很夸张了，而且确认最好一次性提交

4.注意JDBC批量操作的行数限制，如果stmt.executeBatch();执行过多而不提交，则会导致JDBC内存报错，建议60000条以内执行一次

四、参考

1.MySQL批量插入处理之提高速度

2. JDBC-批量操作的行数限制

3. execute(),executeQuery(),executeUpdate()和executeBatch()的使用与区别