使用JDBC的批量插入百万数据要多少秒

谁借莪１个温暖的怀抱￠ 2023-09-24 20:19 64阅读 0赞

package com.example.demo.sql;
    
    import org.junit.Test;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.Random;
    
            /*
             1.设置URL user name
             2. 加载驱动
             3. 建立连接
             4. 预编译sql
             5. 批量执行
            */
    public class JdbcBatchDemo {
        //1.设置URL user name
        private static String url = "jdbc:mysql://localhost:3308/largebatchdemo?rewriteBatchedStatements=true";
        private  String user = "root";
        private  String password = "123456";
    
        @Test
        public void TestBatch(){
            PreparedStatement ps = null;
            Connection connection = null;
            //2. 加载驱动
            try {
                Class.forName("com.mysql.jdbc.Driver");
                //   3. 建立连接
                connection = DriverManager.getConnection(url,user,password);
                //  4. 预编译sql
                String sql = "INSERT  INTO largebatchdemo(age,name,address) VALUES (CONCAT(id,?),?,?) ";
                ps = connection.prepareStatement(sql);
                int c,d;
                Random rand = new Random();
                //計時
                connection.setAutoCommit(false);
                Long beginTime = System.currentTimeMillis();
                //5. 批量执行
                for (int i = 1; i <= 100000; i++) {
      
                    ps.setInt(1, i);
                    //随意赋值
                    c = rand.nextInt(5);
                    d = rand.nextInt(5);
                    ps.setString(2, "c" + c + d);
                    ps.setString(3, "d" + d + c);
                    ps.addBatch();
                    //防数据过大，内存溢出，每20W/次插入
                    /*if (i % 200000 == 199999){
                        ps.executeBatch();
                        connection.commit();
                        ps.clearBatch();
                    }*/
                }
                ps.executeBatch();
                connection.commit();
                Long endTime = System.currentTimeMillis();
                System.out.println("SQL执行用时：" + (endTime - beginTime));
               
            } catch (ClassNotFoundException  | SQLException e) {
                e.printStackTrace();
                throw new RuntimeException();
            }
            finally {
                if (ps!=null){
                    try {
                        ps.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                        throw new RuntimeException();
                    }
                }
                if (connection!=null){
                    try {
                        ps.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                        throw new RuntimeException();
                    }
                }
            }
        }
    }

重点在于：url加上**rewriteBatchedStatements=true设置，将N条SQL语句的执行封装成单条语句的执行，可以考虑同时关闭事务的自动提交connection.setAutoCommit(false)来提高执行效率;**

来看执行效率结果（普通笔记本测试环境，插入10W条数据做测试）：

1.  不加**rewriteBatchedStatements=true时，只关闭事务提交**

![e3615c4da45840b9ae18e2f0ae286a36.png][]

1.  加**rewriteBatchedStatements=true时，同时关闭事务提交**

![0249b0df90d3431887dee2c715c96f07.png][]

提升效果还是很明显的，10W的数据量下差了几秒的速度。

来看执行效率结果（普通笔记本测试环境，插入100W条数据做测试）内存占用瞬间飙高：

1.  不加**rewriteBatchedStatements=true时，只关闭事务提交**

![4ebaba308c7a4fdca201b9e8b96aaa8f.png][]

1.  加**rewriteBatchedStatements=true时，同时关闭事务提交**

![2b5a0416e6384d9fb6600064daef08ad.png][]

100W的数据量下，效率差直接达到60S。。有点猛

来看执行效率结果（普通笔记本测试环境，插入100W条数据做测试）分20W/执行，批量执行5次，对比百万数据直接插入，分批执行内存占用一下就降低了：

1.  不加**rewriteBatchedStatements=true时，只关闭事务提交**

![45ef20e1827e416fa4065228d12dc002.png][]

1.  加**rewriteBatchedStatements=true时，同时关闭事务提交**

![6e9084c2953749e6b32850ea1ec0940d.png][]

速度是差不多的，主要是降低了内存的使用，防止内存溢出

[e3615c4da45840b9ae18e2f0ae286a36.png]: https://img-blog.csdnimg.cn/img_convert/e3615c4da45840b9ae18e2f0ae286a36.png
[0249b0df90d3431887dee2c715c96f07.png]: https://img-blog.csdnimg.cn/img_convert/0249b0df90d3431887dee2c715c96f07.png
[4ebaba308c7a4fdca201b9e8b96aaa8f.png]: https://img-blog.csdnimg.cn/img_convert/4ebaba308c7a4fdca201b9e8b96aaa8f.png
[2b5a0416e6384d9fb6600064daef08ad.png]: https://img-blog.csdnimg.cn/img_convert/2b5a0416e6384d9fb6600064daef08ad.png
[45ef20e1827e416fa4065228d12dc002.png]: https://img-blog.csdnimg.cn/img_convert/45ef20e1827e416fa4065228d12dc002.png
[6e9084c2953749e6b32850ea1ec0940d.png]: https://img-blog.csdnimg.cn/img_convert/6e9084c2953749e6b32850ea1ec0940d.png