高效TDDL批量插入

最新推荐文章于 2023-12-18 15:24:05 发布

jlb882800

最新推荐文章于 2023-12-18 15:24:05 发布

阅读量1.1k

点赞数 1

分类专栏： Ibatis

Ibatis 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

高效TDDL批量插入

使用TDDL客户端，向MySQL数据库插入1000条数据，插入时间10秒内。

常规方法

单条插入，代码如下：

 
          @Test 
         
          @Transactional 
          (TransactionMode.ROLLBACK) 
         
          public 
          void 
           testInsters2() { 
         
          for 
          ( 
          int 
          i =  
          0 
          ; i <  
          10000 
          ; i++) { 
         
          CheckItemDO checkItemDO = createCheckItem2(i); 
         
          checkItemDao.insertCheckItem(checkItemDO); 
         
          } 
         
          }

消费时间：

居然花了144秒，悲剧啊！

批量插入
第一种批量插入，ibatis批量插入数据-iterate

TDDL不支持SQL多条插入，INSERT INTO tableName (col_name,…) VALUES (expression,…),(…)；所以要添加备注，绕开语法校验，

/*+TDDL({type:executeByCondition,parameters:["seller_id=#sellerId#;l"],virtualTableName:jxc_check_item})*/

     
           <!-- 批量插入盘点明细  --> 
          
           < 
           insert 
           id 
           = 
           "insertCheckItems" 
           parameterClass 
           = 
           "java.util.Map" 
           > 
          
           /*+TDDL({type:executeByCondition,parameters:["seller_id=#sellerId#;l"],virtualTableName:jxc_check_item})*/ 
          
           insert into 
          
           jxc_check_item (id, check_item_id, check_id, from_site, seller_id, warehouse_id, product_id, before_check_num, after_check_num, creator, modifier, gmt_create, gmt_check, gmt_modified) 
          
           values 
          
           < 
           iterate 
           conjunction 
           = 
           "," 
           property 
           = 
           "checkItems" 
           > 
          
           <![CDATA[ 
          
           ( 
          
           #checkItems[].id#, #checkItems[].checkItemId#, #checkItems[].checkId#, #checkItems[].fromSite#, #checkItems[].sellerId#, #checkItems[].warehouseId#, #checkItems[].productId#, #checkItems[].beforeCheckNum#, #checkItems[].afterCheckNum#, #checkItems[].creator#, #checkItems[].modifier#, now(), now(), now() 
          
           ) 
          
           ]]> 
          
           </ 
           iterate 
           > 
          
           </ 
           insert 
           >

说白了这种方式就是拼接SQL，但是MySQL长度是有限制的，默认限制是1M，所以，如果一次性插入10000调数据，程序会出现假死状态。所有，要分批插入，一批插入1000调，插10次。Java代码如下：

     
           @Test 
          
           @Transactional 
           (TransactionMode.ROLLBACK) 
          
           public 
           void 
           testInsters() { 
          
           List<CheckItemDO> checkItemDOsTemp =  
           new 
           ArrayList<CheckItemDO>(); 
          
           for 
           ( 
           int 
           i =  
           0 
           ; i <  
           10000 
           ; i++) { 
          
           CheckItemDO checkItemDO = createCheckItem2(i); 
          
           checkItemDOsTemp.add(checkItemDO); 
          
           if 
           (i %  
           1000 
           ==  
           0 
           ) { 
          
           checkItemDao.insertCheckItems(sellerId, checkItemDOsTemp); 
          
           checkItemDOsTemp.clear(); 
          
           } 
          
           } 
          
           }

消费时间：只消耗了40秒不到，性能提升了3.5倍。

虽然时候性能已经提高了不少，但是，还有没有更给力的方法呢？答案是：Yes。

第二种批量插入，jdbc Rewrite Batched Statements批量插入

java代码如下：

 
           String url =  
           "jdbc:mysql://127.0.0.1:3306/dev?rewriteBatchedStatements=true" 
           ; 
          
           String userName =  
           "root" 
           ; 
          
           String password =  
           "1234" 
           ; 
          
           Connection conn =  
           null 
           ; 
          
           try 
           { 
          
           Class.forName( 
           "com.mysql.jdbc.Driver" 
           ); 
          
           conn = DriverManager.getConnection(url, userName, password); 
          
           conn.setAutoCommit( 
           false 
           ); 
          
           String sql =  
           "insert into t_user(id,uname) values(?,?)" 
           ; 
          
           PreparedStatement prest = conn.prepareStatement(sql); 
          
           long 
           a = System.currentTimeMillis(); 
          
           for 
           ( 
           int 
           x =  
           0 
           ; x <  
           100000 
           ; x++) { 
          
           prest.setInt( 
           1 
           , x); 
          
           prest.setString( 
           2 
           ,  
           "张三" 
           ); 
          
           prest.addBatch(); 
          
           } 
          
           prest.executeBatch(); 
          
           conn.commit(); 
          
           long 
           b = System.currentTimeMillis(); 
          
           System.out.println( 
           "MySql批量插入10万条记录用时" 
           + (b - a) +  
           " ms" 
           ); 
          
           }  
           catch 
           (Exception ex) { 
          
           ex.printStackTrace(); 
          
           }  
           finally 
           { 
          
           try 
           { 
          
           if 
           (conn !=  
           null 
           ) conn.close(); 
          
           }  
           catch 
           (SQLException e) { 
          
           e.printStackTrace(); 
          
           } 
          
           }

这里，要求mysql url必须带rewriteBatchedStatements=true。此处是jdbc的代码，而本人的开发环境是基于ibatis的，下面为ibatic批量插入的实现代码：

     
           @Test 
          
           @Transactional 
           (TransactionMode.DISABLED) 
          
           public 
           void 
           testBatch2() { 
          
           List<CheckItemDO> checkItemDOsTemp =  
           new 
           ArrayList<CheckItemDO>(); 
          
           for 
           ( 
           int 
           i =  
           0 
           ; i <  
           10000 
           ; i++) { 
          
           CheckItemDO checkItemDO = createCheckItem2(i); 
          
           checkItemDOsTemp.add(checkItemDO); 
          
           } 
          
           final 
           List<CheckItemDO> checkItemDOs =  
           new 
           ArrayList<CheckItemDO>(checkItemDOsTemp); 
          
           CheckItemDaoImpl checkItemDaoImpl = (CheckItemDaoImpl) checkItemDao; 
          
           SqlBaseExecutorImp sqlBaseExecutorImp = (SqlBaseExecutorImp)checkItemDaoImpl.getSqlBaseExecutor(); 
          
           sqlBaseExecutorImp.getSqlMapClientTemplate().execute( 
           new 
           SqlMapClientCallback() { 
          
           public 
           Object doInSqlMapClient(SqlMapExecutor executor) { 
          
           try 
           { 
          
           executor.startBatch(); 
          
           for 
           (CheckItemDO checkItemDO : checkItemDOs) { 
          
           executor.insert(CheckItemDao.SQL_MAPPING_NAME_SPACE +  
           ".insertCheckItem" 
           , checkItemDO); 
          
           } 
          
           executor.executeBatch(); 
          
           }  
           catch 
           (SQLException e) { 
          
           e.printStackTrace(); 
          
           return 
           false 
           ; 
          
           } 
          
           return 
           true 
           ; 
          
           }; 
          
           }); 
          
           }

消费时间：质的飞跃，3.384秒，性能提升了40倍，Perfect。

注意事项

第一，MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。

Mysql JDBC驱动，各个版本测试结果：

MySql JDBC 驱动版本号	插入10万条数据用时
5.0.8	加了rewriteBatchedStatements参数，没有提高还是17.4秒
5.1.7	加了rewriteBatchedStatements参数，没有提高还是17.4秒
5.1.13	加了rewriteBatchedStatements参数，插入速度提高到1.6秒

第二，保证表插入的顺序，如果要批量插入A和B两个表的数据，要保证先批量插入表A，再批量插入表B。原因在于磁盘IO，应为MySQL会保证SQL的顺序性，不断在A,B表之间切换，相当与磁盘寻址要不断改变，相当损耗性能，这无异于单条插入。

第三，小心事务嵌套，jdbc Rewrite Batched Statements批量插入要求在一个事务中进行批量插入，所以，外面不应该再嵌套事务，在使用TestNG事务的时候，要声明为无事务，即：@Transactional(TransactionMode.DISABLED)。