JDBC批量Insert深度优化（有事务）（转）

最新推荐文章于 2023-03-31 15:35:58 发布
最新推荐文章于 2023-03-31 15:35:58 发布
阅读量77 收藏
点赞数
分类专栏： MySQL 文章标签： JDBC SQL MySQL C C++
环境：
MySQL 5.1
RedHat Linux AS 5
JavaSE 1.5
DbConnectionBroker 微型数据库连接池
 
测试的方案：
执行10万次Insert语句，使用不同方式。
 
A组：静态SQL，自动提交，没事务控制（MyISAM引擎）
1、逐条执行10万次
2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。
 
B组：预编译模式SQL，自动提交，没事务控制（MyISAM引擎）
1、逐条执行10万次
2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。
-------------------------------------------------------------------------------------------
C组：静态SQL，不自动提交，有事务控制（InnoDB引擎）
1、逐条执行10万次
2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。
 
D组：预编译模式SQL，不自动提交，有事务控制（InnoDB引擎）
1、逐条执行10万次
2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。
 
本次主要测试C、D组，并得出测试结果。
 
SQL代码
DROP   TABLE   IF   EXISTS  tuser;  

CREATE   TABLE  tuser (  
    id   bigint(20)   NOT   NULL  AUTO_INCREMENT,  
     name   varchar(12)   DEFAULT   NULL,  
    remark   varchar(24)   DEFAULT   NULL,  
    createtime   datetime   DEFAULT   NULL,  
    updatetime   datetime   DEFAULT   NULL,  
     PRIMARY   KEY  (id)  
) ENGINE=InnoDB AUTO_INCREMENT=1   DEFAULT  CHARSET=utf8;
 
C、D组测试代码：
package  testbatch;  

import  java.io.IOException;  
import  java.sql.*;  

/** 
* JDBC批量Insert优化（下） 
* 
* @author leizhimin 2009-7-29 10:03:10 
*/  
public   class  TestBatch {  
         public   static  DbConnectionBroker myBroker =   null;  

         static  {  
                 try  {  
                        myBroker =   new  DbConnectionBroker( "com.mysql.jdbc.Driver",  
                                         "jdbc:mysql://192.168.104.163:3306/testdb", 
                                        "vcom", "vcom", 2, 4, 
                                        "c:\\testdb.log", 0.01); 
                } catch (IOException e) { 
                        e.printStackTrace(); 
                } 
        } 

        /** 
         * 初始化测试环境 
         * 
         * @throws SQLException 异常时抛出 
         */ 
        public static void init() throws SQLException { 
                Connection conn = myBroker.getConnection(); 
                conn.setAutoCommit(false); 
                Statement stmt = conn.createStatement(); 
                stmt.addBatch("DROP TABLE IF EXISTS tuser"); 
                stmt.addBatch("CREATE TABLE tuser (\n" + 
                                "    id bigint(20) NOT NULL AUTO_INCREMENT,\n" + 
                                "    name varchar(12) DEFAULT NULL,\n" + 
                                "    remark varchar(24) DEFAULT NULL,\n" + 
                                "    createtime datetime DEFAULT NULL,\n" + 
                                "    updatetime datetime DEFAULT NULL,\n" + 
                                "    PRIMARY KEY (id)\n" + 
                                ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8"); 
                stmt.executeBatch(); 
                conn.commit(); 
                myBroker.freeConnection(conn); 
        } 

        /** 
         * 100000条静态SQL插入 
         * 
         * @throws Exception 异常时抛出 
         */ 
        public static void testInsert() throws Exception { 
                init();         //初始化环境 
                Long start = System.currentTimeMillis(); 
                for (int i = 0; i < 100000; i++) { 
                        String sql = "\n" + 
                                        "insert into testdb.tuser \n" + 
                                        "\t(name, \n" + 
                                        "\tremark, \n" + 
                                        "\tcreatetime, \n" + 
                                        "\tupdatetime\n" + 
                                        "\t)\n" + 
                                        "\tvalues\n" + 
                                        "\t('" + RandomToolkit.generateString(12) + "', \n" + 
                                        "\t'" + RandomToolkit.generateString(24) + "', \n" + 
                                        "\tnow(), \n" + 
                                        "\tnow()\n" + 
                                        ")"; 
                        Connection conn = myBroker.getConnection(); 
                        conn.setAutoCommit(false); 
                        Statement stmt = conn.createStatement(); 
                        stmt.execute(sql); 
                        conn.commit(); 
                        myBroker.freeConnection(conn); 
                } 
                Long end = System.currentTimeMillis(); 
                System.out.println("单条执行100000条Insert操作，共耗时：" + (end - start) / 1000f + "秒！"); 
        } 

        /** 
         * 批处理执行静态SQL测试 
         * 
         * @param m 批次 
         * @param n 每批数量 
         * @throws Exception 异常时抛出 
         */ 
        public static void testInsertBatch(int m, int n) throws Exception { 
                init();             //初始化环境 
                Long start = System.currentTimeMillis(); 
                for (int i = 0; i < m; i++) { 
                        //从池中获取连接 
                        Connection conn = myBroker.getConnection(); 
                        conn.setAutoCommit(false); 
                        Statement stmt = conn.createStatement(); 
                        for (int k = 0; k < n; k++) { 
                                String sql = "\n" + 
                                                "insert into testdb.tuser \n" + 
                                                "\t(name, \n" + 
                                                "\tremark, \n" + 
                                                "\tcreatetime, \n" + 
                                                "\tupdatetime\n" + 
                                                "\t)\n" + 
                                                "\tvalues\n" + 
                                                "\t('" + RandomToolkit.generateString(12) + "', \n" + 
                                                "\t'" + RandomToolkit.generateString(24) + "', \n" + 
                                                "\tnow(), \n" + 
                                                "\tnow()\n" + 
                                                ")"; 
                                //加入批处理 
                                stmt.addBatch(sql); 
                        } 
                        stmt.executeBatch();    //执行批处理 
                        conn.commit(); 
//                        stmt.clearBatch();        //清理批处理 
                        stmt.close(); 
                        myBroker.freeConnection(conn); //连接归池 
                } 
                Long end = System.currentTimeMillis(); 
                System.out.println("批量执行" + m + "*" + n + "=" + m * n + "条Insert操作，共耗时：" + (end - start) / 1000f + "秒！"); 
        } 

        /** 
         * 100000条预定义SQL插入 
         * 
         * @throws Exception 异常时抛出 
         */ 
        public static void testInsert2() throws Exception {     //单条执行100000条Insert操作，共耗时：40.422秒！ 
                init();         //初始化环境 
                Long start = System.currentTimeMillis(); 
                String sql = "" + 
                                "insert into testdb.tuser\n" + 
                                "    (name, remark, createtime, updatetime)\n" + 
                                "values\n" + 
                                "    (?, ?, ?, ?)"; 
                for (int i = 0; i < 100000; i++) { 
                        Connection conn = myBroker.getConnection(); 
                        conn.setAutoCommit(false); 
                        PreparedStatement pstmt = conn.prepareStatement(sql); 
                        pstmt.setString(1, RandomToolkit.generateString(12)); 
                        pstmt.setString(2, RandomToolkit.generateString(24)); 
                        pstmt.setDate(3, new Date(System.currentTimeMillis())); 
                        pstmt.setDate(4, new Date(System.currentTimeMillis())); 
                        pstmt.executeUpdate(); 
                        conn.commit(); 
                        pstmt.close(); 
                        myBroker.freeConnection(conn); 
                } 
                Long end = System.currentTimeMillis(); 
                System.out.println("单条执行100000条Insert操作，共耗时：" + (end - start) / 1000f + "秒！"); 
        } 

        /** 
         * 批处理执行预处理SQL测试 
         * 
         * @param m 批次 
         * @param n 每批数量 
         * @throws Exception 异常时抛出 
         */ 
        public static void testInsertBatch2(int m, int n) throws Exception { 
                init();             //初始化环境 
                Long start = System.currentTimeMillis(); 
                String sql = "" + 
                                "insert into testdb.tuser\n" + 
                                "    (name, remark, createtime, updatetime)\n" + 
                                "values\n" + 
                                "    (?, ?, ?, ?)"; 
                for (int i = 0; i < m; i++) { 
                        //从池中获取连接 
                        Connection conn = myBroker.getConnection(); 
                        conn.setAutoCommit(false); 
                        PreparedStatement pstmt = conn.prepareStatement(sql); 
                        for (int k = 0; k < n; k++) { 
                                pstmt.setString(1, RandomToolkit.generateString(12)); 
                                pstmt.setString(2, RandomToolkit.generateString(24)); 
                                pstmt.setDate(3, new Date(System.currentTimeMillis())); 
                                pstmt.setDate(4, new Date(System.currentTimeMillis())); 
                                //加入批处理 
                                pstmt.addBatch(); 
                        } 
                        pstmt.executeBatch();    //执行批处理 
                        conn.commit(); 
//                        pstmt.clearBatch();        //清理批处理 
                        pstmt.close(); 
                        myBroker.freeConnection(conn); //连接归池 
                } 
                Long end = System.currentTimeMillis(); 
                System.out.println("批量执行" + m + "*" + n + "=" + m * n + "条Insert操作，共耗时：" + (end - start) / 1000f + "秒！"); 
        } 

        public static void main(String[] args) throws Exception { 
                init(); 
                Long start = System.currentTimeMillis(); 
                System.out.println("--------C组测试----------"); 
                testInsert(); 
                testInsertBatch(100, 1000); 
                testInsertBatch(250, 400); 
                testInsertBatch(400, 250); 
                testInsertBatch(500, 200); 
                testInsertBatch(1000, 100); 
                testInsertBatch(2000, 50); 
                testInsertBatch(2500, 40); 
                testInsertBatch(5000, 20); 
                Long end1 = System.currentTimeMillis(); 
                System.out.println("C组测试过程结束，全部测试耗时：" + (end1 - start) / 1000f + "秒！"); 

                System.out.println("--------D组测试----------"); 
                testInsert2(); 
                testInsertBatch2(100, 1000); 
                testInsertBatch2(250, 400); 
                testInsertBatch2(400, 250); 
                testInsertBatch2(500, 200); 
                testInsertBatch2(1000, 100); 
                testInsertBatch2(2000, 50); 
                testInsertBatch2(2500, 40); 
                testInsertBatch2(5000, 20); 

                Long end2 = System.currentTimeMillis(); 
                System.out.println("D组测试过程结束，全部测试耗时：" + (end2 - end1) / 1000f + "秒！"); 
        } 
}
 
执行结果：
--------C组测试----------  
单条执行100000条Insert操作，共耗时：103.656秒！  
批量执行100*1000=100000条Insert操作，共耗时：31.328秒！  
批量执行250*400=100000条Insert操作，共耗时：31.406秒！  
批量执行400*250=100000条Insert操作，共耗时：31.75秒！  
批量执行500*200=100000条Insert操作，共耗时：31.438秒！  
批量执行1000*100=100000条Insert操作，共耗时：31.968秒！  
批量执行2000*50=100000条Insert操作，共耗时：32.938秒！  
批量执行2500*40=100000条Insert操作，共耗时：33.141秒！  
批量执行5000*20=100000条Insert操作，共耗时：35.265秒！  
C组测试过程结束，全部测试耗时：363.656秒！  
--------D组测试----------  
单条执行100000条Insert操作，共耗时：107.61秒！  
批量执行100*1000=100000条Insert操作，共耗时：32.64秒！  
批量执行250*400=100000条Insert操作，共耗时：32.641秒！  
批量执行400*250=100000条Insert操作，共耗时：33.109秒！  
批量执行500*200=100000条Insert操作，共耗时：32.859秒！  
批量执行1000*100=100000条Insert操作，共耗时：33.547秒！  
批量执行2000*50=100000条Insert操作，共耗时：34.312秒！  
批量执行2500*40=100000条Insert操作，共耗时：34.672秒！  
批量执行5000*20=100000条Insert操作，共耗时：36.672秒！  
D组测试过程结束，全部测试耗时：378.922秒！  
 
 
测试结果意想不到吧，最短时间竟然超过上篇。观察整个测试结果，发现总时间很长，原因是逐条执行的效率太低了。
 
结论：
 
在本测试条件下，得出结论：
 
数据库连接池控制下，不自动提交，事务控制（InnoDB引擎）
 
1、逐条执行的效率很低很低，尽可能避免逐条执行。
2、事务控制下，静态SQL的效率超过预处理SQL。
3、分批的大小对效率影响挺大的，一般来说，事务控制下，分批大小在100-1000之间比较合适。
4、谈到优化方式，上面的批处理就是很好的优化策略。
 
 
大总结：
 
对比上篇没事务的测试结果，得出一个全面的结论：
 
1、连接池最基本的也是最重要的优化策略，总能大幅提高性能。
 
2、批处理在效率上总是比逐条处理有优势，要处理的数据的记录条数越大，批处理的优势越明显，批处理还有一个好处就是减少了对数据库的链接次数，从而减轻数据库的压力。
 
3、批处理执行SQL的时候，批处理的分批的大小与数据库的吞吐量以及硬件配置有很大关系，需要通过测试找到最佳的分批大小，一般在50-1000之间。
 
4、预处理SQL在没事务的表上效率较高，在有实物的情况下比静态SQL稍有不及。但预定义SQL还有个好处就是消耗的内存较少，静态SQL串会占用大量的内存资源，容易导致内存溢出的问题。因此批量执行时候可以优先选择预定义SQL。
 
5、在批处理执行的时候，每批执行完成后，最好显式的调用pstmt.close()或stmt.close()方法，以便尽快释放执行过的SQL语句，提高内存利用率。
 
6、对于有大量SELECT操作，MyISAM是更好的选择；对于有大量INSERT和UPDATE操作的表，InnoDB效率更好。
 
7、虽然测试结果只能反映特定情况下的一些事实，以上的优化策略是普遍策略，可以明显缩短寻找最优策略的时间，对于效率要求很高的程序，还应该做并发性等测试。
 
8、测试是件很辛苦的事情，你需要有大量的事实来证明你的优化是有效的，而不能单单凭经验，因为每个机器的环境都不一样，使用的方式也不同。
 

本文出自 “熔 岩” 博客，转载请与作者联系！

eoasis
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySql批量插入性能优化(多值插入)【通用实体服务类TCommonService】
iOS逆向与安全
02-15 1188
mybatis-plus内置的批量插入方法并不是真正的批量写入，而是通过executeBatch分批提交。 所以想提升批量插入的性能可以采用多值插入。多值插入的方式1: JDBC URL批量处理参数配置， 在mysql连接信息中添加rewriteBatchedStatements=true使得执行效率大幅提升。多值插入的方式2: 通过sql注入器注入InsertBatchSomeColumn方法实现了insert的多值插入，提升了批量插入的性能。通过sql注入器注入InsertBatchSomeColumn
logstash-output-jdbc插件
09-28
5. **连接池**：可以通过`jdbc_pool_timeout`、`jdbc_validate_connection`等设置，优化数据库连接的管理和性能。 **应用场景** - 日志分析：将日志数据存储到数据库，便于进行复杂SQL查询和报表生成。 - 实时监控...
JDBC批量Insert深度优化（有事务）
weixin_34379433的博客
07-29 226
JDBC批量Insert深度优化（有事务）   环境： MySQL 5.1 RedHat Linux AS 5 JavaSE 1.5 DbConnectionBroker 微型数据库连接池   测试的方案： 执行10万次Insert语句，使用不同方式。   A组：静态SQL，自动提交，没事务控制（MyISAM引擎） 1、逐条执行10万次 2、分批执行将10万分成m批，每批...
数据库插入或者更新大批量数据的性能优化
weixin_49071459的博客
12-29 634
对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此，优化数据库插入性能是很有意义的。 经过对MySQL InnoDB的一些性能测试，发现一些可以提高insert效率的方法，供大家参考参考。 1、一条SQL语句插入多条数据 常用的插入语句如： INSERTI...
JDBC批量插入数据优化,使用addBatch和executeBatch
rentian1的博客
09-01 1186
转载请注明出处：http://blog.csdn.net/linglongxin24/article/details/53769957 本文出自【DylanAndroid的博客】 一.用 preparedStatement.addBatch()配合preparedStatement.executeBatch()去批量插入 /** * 执行数据库插入操作 *
JDBC批量插入优化
chengstudying的博客
03-31 269
【代码】JDBC批量插入优化。
SQLite_Test_Tutorial
03-15
《SQLite在Java中的应用深度解析》 SQLite是一个轻量级的、开源的、自包含的数据库引擎，广泛应用于移动设备、嵌入式系统以及桌面应用程序。在Java开发中，SQLite作为一个方便的数据存储解决方案，尤其适合那些对...
精通 Hibernate：Java 对象持久化技术详解(第2版).part4
06-12
　20.4 Java应用通过JDBC API声明JDBC事务 　20.5 Java应用通过Hibernate API声明JDBC事务 　20.5.1 处理异常 　20.5.2 Session与事务的关系 　20.5.3 设定事务超时 　20.6 Java应用通过Hibernate API声明JTA事务 　...
精通 Hibernate：Java 对象持久化技术详解(第2版).part3
06-12
　20.4 Java应用通过JDBC API声明JDBC事务 　20.5 Java应用通过Hibernate API声明JDBC事务 　20.5.1 处理异常 　20.5.2 Session与事务的关系 　20.5.3 设定事务超时 　20.6 Java应用通过Hibernate API声明JTA事务 　...
精通 Hibernate：Java 对象持久化技术详解(第2版).part2
06-12
　20.4 Java应用通过JDBC API声明JDBC事务 　20.5 Java应用通过Hibernate API声明JDBC事务 　20.5.1 处理异常 　20.5.2 Session与事务的关系 　20.5.3 设定事务超时 　20.6 Java应用通过Hibernate API声明JTA事务 　...
Spring JdbcDaoSupport 批量插入数据
05-28
NULL 博文链接：https://huibin.iteye.com/blog/618316
jdbc 批量插入优化
放下渴望收获的心
03-27 287
项目中有一个大数据插入的功能是通过jdbc的批处理实现的，但效率一直不理想。 最近研究了一下，做如下总结： 1) 如果id使用的是uuid，一定要保证有序。 因为通常数据库会为主键创建聚集索引。而聚集索引是用来指明数据排序规则的。所以，对于非有序的id，插入的同时会做大量的排序操作，很影响效率。 另外，我们在尝试的过程中有使用apache axis.jar 下的SimpleUUIDGen...
JDBC大批量插入数据性能优化
weixin_34392435的博客
01-22 514
2019独角兽企业重金招聘Python工程师标准>>> ...
JDBC批量Insert深度优化
awen7916的专栏
01-12 145
JDBC批量Insert深度优化（有事务）   环境： MySQL 5.1 RedHat Linux AS 5 JavaSE 1.5 DbConnectionBroker 微型数据库连接池   测试的方案： 执行10万次Insert语句，使用不同方式。   A组：静态SQL，自动提交，没事务控制（MyISAM引擎） 1、逐条执行10万次 2、分批执行将10万分成m批，每...
【MySQL】insert批量插入优化方案
骏马逸动，心随你动的博客
08-27 4914
对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此，优化数据库插入性能是很有意义的。 经过对MySQL innodb的一些性能测试，发现一些可以提高insert效率的方法，供大家参考参考。 一条SQL语句插入多条数据 常用的插入语句如： INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VAL
JDBC批量插入
Luck_ZZ的博客
09-11 546
获得Connection连接后，默认是自动提交的 要设置不手动提交，批量插入才有效果 preparedStatement.addBatch();积攒SQL preparedStatement.executeBatch();批量执行 preparedStatement.clearBatch();清空积攒的SQL public void testAddBatch() throws ...
jdbc批量插入
zona
12-01 1576
java jdbc批量插入
JDBC 批量插入数据
qq_57389269的博客
08-03 1496
当需要批量插入数据到数据库时，我们可以利用基础的Statement或PreparedStatement外加for循环来实现操作，但这种方式效率奇低。 因此，当需要成批插入或者更新记录时，我们可以利用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理，通常情况下比单独提交处理更有效率。 JDBC的批量处理语句包括下面三个方法： addBatch(String)：添加需要批量处理的SQL语句或是参数； executeBatch()：执行批量...
jdbc批量insert数据
最新发布
09-14
要使用JDBC进行批量插入数据，你可以按照以下步骤操作： 1. 获取数据库连接：使用JDBC驱动程序获取与数据库的连接。例如，使用`DriverManager.getConnection()`方法。 2. 创建PreparedStatement对象：使用连接对象创建`PreparedStatement`对象，并指定插入语句，但是不指定参数值。例如，`connection.prepareStatement("INSERT INTO table_name (column1, column2) VALUES (?, ?)")`。 3. 设置参数值：使用`PreparedStatement`对象的`setXxx()`方法为每个参数设置值。例如，`preparedStatement.setInt(1, value1)`和`preparedStatement.setString(2, value2)`。 4. 添加批量操作：使用`addBatch()`方法将每个`PreparedStatement`对象添加到批处理中。例如，`preparedStatement.addBatch()`。 5. 执行批处理：使用`executeBatch()`方法执行批处理操作。例如，`preparedStatement.executeBatch()`。 6. 关闭连接和声明对象：在操作完成后，关闭连接和所有相关的声明对象。例如，`preparedStatement.close()`和`connection.close()`。 以下是一个示例代码片段，演示如何使用JDBC进行批量插入数据： ```java Connection connection = null; PreparedStatement preparedStatement = null; try { connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name", "username", "password"); String insertQuery = "INSERT INTO table_name (column1, column2) VALUES (?, ?)"; preparedStatement = connection.prepareStatement(insertQuery); // 设置参数值并添加到批处理 preparedStatement.setInt(1, value1); preparedStatement.setString(2, value2); preparedStatement.addBatch(); // 可以继续添加更多批处理操作 // 执行批处理 int[] batchResult = preparedStatement.executeBatch(); // 处理批处理结果 } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭连接和声明对象 if (preparedStatement != null) { try { preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } ``` 请注意，以上代码只是一个示例，你需要根据实际的表结构和数据来修改插入语句和参数设置。确保在使用JDBC进行数据库操作时，遵循相关的安全和最佳实践。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值