MySQL + JDBC 实现批量插入（千万数据）

1. 配置my.ini文件

bulk_insert_buffer_size=120M 或者更大

将insert语句的长度设为最大。

Max_allowed_packet=1M

Net_buffer_length=8k

可以通过navicat直接查询修改：

 SHOW VARIABLES where Variable_name = 'bulk_insert_buffer_size'
 
 set bulk_insert_buffer_size = 104857600

2. 查看设置的参选有没有生效

SHOW VARIABLES;

bulk_insert_buffer_size默认是8M，我们要把它调成100M或百兆以上，也不要太大。下面看调整好的，如下图：

3. 数据插入

表结构：

建议多线程+批量插入执行，否则耗时太长，单线程插入100W跳数据插入了半小时…

开启十个线程，每个线程插入100W数据：

可以看出这个实际还是可以接受的！

代码如下：

public class BatchMain {

    public static void main(String[] args) throws Exception {
        //嘎嘎快
        ExecutorService executorService = Executors.newFixedThreadPool(10);
        for (int i = 0; i < 10; i++) {
            executorService.execute(new BatchThread());
        }

        //batchInsert();
    }

    static class BatchThread implements Runnable {

        @SneakyThrows
        @Override
        public void run() {
            long start = System.currentTimeMillis();

            Connection conn;
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/demo?useTimezone=true&serverTimezone=GMT%2B8&characterEncoding=utf-8";
            String user = "root";
            String password = "123456";
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);

            conn.setAutoCommit(false);
            PreparedStatement ps = null;
            String sql = "insert into test(name,addr,age) values (?,?,?)";
            ps = conn.prepareStatement(sql); // 批量插入时ps对象必须放到for循环外面
            for (int i = 0; i < 1000000; i++) {
                ps.setString(1, "mary_" + i);
                ps.setString(2, "西城区" + i + "号路");
                ps.setInt(3, (i + 1));

                ps.addBatch();
                // 每1000条记录插入一次
                if (i % 10000 == 0) {
                    ps.executeBatch();
                    conn.commit();
                    ps.clearBatch();
                }
            }
            // 剩余数量不足10000
            ps.executeBatch();
            conn.commit();
            ps.clearBatch();
            long end = System.currentTimeMillis();
            System.out.println(end - start);
        }
    }

    //批量插入
    public static void batchInsert() throws Exception {
        long start = System.currentTimeMillis();

        Connection conn;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/demo?useTimezone=true&serverTimezone=GMT%2B8&characterEncoding=utf-8";
        String user = "root";
        String password = "123456";
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);

        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        String sql = "insert into test(name,addr,age) values (?,?,?)";
        ps = conn.prepareStatement(sql); // 批量插入时ps对象必须放到for循环外面
        for (int i = 0; i < 1000; i++) {
            ps.setString(1, "mary_" + i);
            ps.setString(2, "西城区" + i + "号路");
            ps.setInt(3, (i + 1));

            ps.addBatch();
            // 每1000条记录插入一次
            if (i % 1000 == 0) {
                ps.executeBatch();
                conn.commit();
                ps.clearBatch();
            }
        }
        // 剩余数量不足10000
        ps.executeBatch();
        conn.commit();
        ps.clearBatch();
        long end = System.currentTimeMillis();
        System.out.println(end - start);
    }

    //巨慢无比 100w数据需要30分钟
    public static void nomarl() throws Exception {
        Connection conn;
        PreparedStatement stmt;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/demo?useTimezone=true&serverTimezone=GMT%2B8&characterEncoding=utf-8";
        String user = "root";
        String password = "123456";
        //String sql = "insert into test values (?,?,?)";
        String sql = "insert into test(name,addr,age) values (?,?,?)";
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);
        stmt = (PreparedStatement) conn.prepareStatement(sql);
        System.out.println(new Date());
        /* —————————————————————————— */
        for (int i = 1; i < 10000000; i++) {
            stmt.setString(1, "mary_" + i);
            stmt.setString(2, "西城区" + i + "号路");
            stmt.setInt(3, (i + 1));
            stmt.executeUpdate();
        }
        System.out.println(new Date());

        try {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}