jdbc批处理+手动事务实现170秒插入1000万数据（单线程版）

Dear 丶 2022-02-20 12:32 145阅读 0赞

*  jdbc批处理加上手动开启事务，能够实现170秒左右插入千万级别的数据

/**
     * @Author: guandezhi
     * @Date: 2019/4/13 15:35
     */
    public class JdbcUtils {
    
        private static String url = "jdbc:mysql://localhost:3306/user?useSSL=false&rewriteBatchedStatements=true";
        private static String user = "root";
        private static String password = "root";
    
    
        public static void executeBatch() {
            Connection conn = null;
            PreparedStatement ps = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(url, user, password);
                long beginTime = System.currentTimeMillis();
                String sql = "insert into user(name, mobile) values(?,?)";
                conn.setAutoCommit(false);
                ps = conn.prepareStatement(sql);
                for (int j = 0; j < 10000000; j++) {
                    String mobile = "13356544556";
                    Integer randNum = 0;
                    Random random = new Random();
                    for (int i = 0; i < 1000; i++) {
                        randNum = random.nextInt(10);
                    }
                    ps.setString(1, "官德志");
                    ps.setString(2, mobile + String.valueOf(randNum));
                    ps.addBatch();
                    //此处避免内存溢出
                    if (j % 1000000 == 0) {
                        ps.executeBatch();
                        conn.commit();
                        ps.clearBatch();
                    }
                }
                ps.executeBatch();
                conn.commit();
                long endTime = System.currentTimeMillis();
                System.out.println("插入一千万数据用时：" + (endTime - beginTime) / 1000 + " 秒");
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

*  来看一下测试结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1ODU5ODQ0_size_16_color_FFFFFF_t_70]: /images/20220220/f70cec37b3b44c30afff9c48d9aababf.png