jdbc,大批量插入数据速度测试

本文对比了四种不同的MySQL批量插入方法的性能,包括一次性插入大量数据、每1000条commit一次、每次插入都commit以及使用addBatch和executeBatch方法。实验结果显示,一次性插入大量数据的方法性能最佳。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

方法1:insert into table(field1,field2) values(value1,value2),(value3,value3)。每次提交1000条,插入10次耗时140ms,每次插入10000,插入10次,耗时818ms

这种方式是最快的,

/**

 * 140ms,1w,818ms,10w

 */

public void test1(){

    final String driver = "com.mysql.jdbc.Driver";

    final String url = "jdbc:mysql://localhost:3306/XX?useUnicode=true&characterEncoding=UTF8";

    final String user = "XXXX";

    final String password = "XXXX";

 

    Connection con = null;

    PreparedStatement pstm = null;

    Statement statement=null;

    ResultSet rs = null;

    boolean flag = false;

    try {

        Class.forName(driver);

        con = DriverManager.getConnection(url, user, password);

        con.setAutoCommit(false);

        StringBuilder insertSql = new StringBuilder();

        insertSql.append("insert into  duidandcity_20190805_hdfs (duid,ip,city,day,year) values ('1','2','3','4','5')");

        int begin = insertSql.length();

        long beginTime = System.currentTimeMillis();

        for (int i = 0; i < 10; i++) {

            statement=con.createStatement();

            for (int j = 0; j < 10000; j++) {//每隔10000插入一次

                insertSql.append(",('1','2','3','4','5')");

            }

            statement.execute(insertSql.toString());

            con.commit();

            insertSql.delete(begin,insertSql.length());

        }

        con.commit();

        long end = System.currentTimeMillis();

        System.out.println((end-beginTime));

    } catch (ClassNotFoundException e) {

        e.printStackTrace();

    } catch (SQLException e) {

        e.printStackTrace();

    } finally {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        // 关闭执行通道

        if (pstm != null) {

            try {

                pstm.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        // 关闭连接通道

        try {

            if (con != null && (!con.isClosed())) {

                try {

                    con.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

方法2:每次插入execute执行一次,但是没1000条commit提交一次,1w条耗时3011。是方法1的30倍左右

/**

 * 3011,1w

 */

@Test

public void test2(){

    final String driver = "com.mysql.jdbc.Driver";

    final String url = "jdbc:mysql://localhost:3306/kpi?useUnicode=true&characterEncoding=UTF8";

    final String user = "XXX";

    final String password = "XXX";

 

    Connection con = null;

    PreparedStatement pstm = null;

    ResultSet rs = null;

    try {

        Class.forName(driver);

        con = DriverManager.getConnection(url, user, password);

        con.setAutoCommit(false);

        long beginTime = System.currentTimeMillis();

        for (int i = 0; i < 10; i++) {

 

            StringBuilder insertSql = new StringBuilder();

            insertSql.append("insert into  duidandcity_20190805_hdfs (duid,ip,city,day,year) values ('1','2','3','4','5')");

            pstm=con.prepareStatement(insertSql.toString());

            for (int j = 0; j < 1000; j++) {

                pstm.execute();

            }

            con.commit();

        }

        con.commit();

        long end = System.currentTimeMillis();

        System.out.println((end-beginTime));

    } catch (ClassNotFoundException e) {

        e.printStackTrace();

    } catch (SQLException e) {

        e.printStackTrace();

    } finally {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        // 关闭执行通道

        if (pstm != null) {

            try {

                pstm.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        // 关闭连接通道

        try {

            if (con != null && (!con.isClosed())) {

                try {

                    con.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

方法3,每次插入都execute和commit,插入1w条数据,耗时15秒,是方法2的5倍,方法1的150倍

/**

 * 15354,1w

 *

 */

@Test

public void test3(){

    final String driver = "com.mysql.jdbc.Driver";

    final String url = "jdbc:mysql://localhost:3306/kpi?useUnicode=true&characterEncoding=UTF8";

    final String user = "root";

    final String password = "135239";

 

    Connection con = null;

    PreparedStatement pstm = null;

    ResultSet rs = null;

    try {

        Class.forName(driver);

        con = DriverManager.getConnection(url, user, password);

        con.setAutoCommit(false);

        long beginTime = System.currentTimeMillis();

        for (int i = 0; i < 10; i++) {

 

            StringBuilder insertSql = new StringBuilder();

            insertSql.append("insert into  duidandcity_20190805_hdfs (duid,ip,city,day,year) values ('1','2','3','4','5')");

            pstm=con.prepareStatement(insertSql.toString());

            for (int j = 0; j < 1000; j++) {

                pstm.execute();

                con.commit();

            }

        }

        con.commit();

        long end = System.currentTimeMillis();

        System.out.println((end-beginTime));

    } catch (ClassNotFoundException e) {

        e.printStackTrace();

    } catch (SQLException e) {

        e.printStackTrace();

    } finally {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        // 关闭执行通道

        if (pstm != null) {

            try {

                pstm.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        // 关闭连接通道

        try {

            if (con != null && (!con.isClosed())) {

                try {

                    con.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

方法4,使用PreparedStatement的addbatch和executeBatch方法,1w条耗时1600,是方法2的一半左右

/**

 * 1w,1600

 */

@Test

public void test4(){

    final String driver = "com.mysql.jdbc.Driver";

    final String url = "jdbc:mysql://localhost:3306/kpi?useUnicode=true&characterEncoding=UTF8";

    final String user = "root";

    final String password = "135239";

 

    Connection con = null;

    PreparedStatement pstm = null;

    ResultSet rs = null;

    try {

        Class.forName(driver);

        con = DriverManager.getConnection(url, user, password);

        con.setAutoCommit(false);

        long beginTime = System.currentTimeMillis();

        StringBuilder insertSql = new StringBuilder();

        insertSql.append("insert into  duidandcity_20190805_hdfs (duid,ip,city,day,year) values ('1','2','3','4','5')");

        pstm=con.prepareStatement(insertSql.toString());

        for (int i = 0; i < 10; i++) {

            for (int j = 0; j < 1000; j++) {

                pstm.addBatch();

            }

            pstm.executeBatch();

            con.commit();

        }

        con.commit();

        long end = System.currentTimeMillis();

        System.out.println((end-beginTime));

    } catch (ClassNotFoundException e) {

        e.printStackTrace();

    } catch (SQLException e) {

        e.printStackTrace();

    } finally {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        // 关闭执行通道

        if (pstm != null) {

            try {

                pstm.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        // 关闭连接通道

        try {

            if (con != null && (!con.isClosed())) {

                try {

                    con.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}

通过以上4个方法的对比,我们可以发现,execute方法和commit方法都是很耗时的方法,应该尽量减少使用他们的次数。推荐使用方法1,速度最快。方法4也可以使用,方法2,3不推荐,他们使用的execute和commit次数都过多。

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值