云计算 Python linux Firefox Android Windows google mysql nginx 编程 apache 程序员 java php 开源 Ubuntu centos shell wordpress 微软

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

<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 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)。

延伸阅读

  • 抱歉,暂无相关内容!

评论