Batch rewriteBatchedStatements=true

Recently, we are optimizing the performance of mass data insertion.
The original mass data insertion method used in the project is the foreach splicing SQL method of Mybatis.
I found that no matter the method changed to Mybatis Batch submission or native JDBC Batch does not work. In fact, when inserting, it is still inserting one record after another, which is far slower than the original method of joining SQL by foreach of Mybatis. This is very unscientific to the common sense.

Here are three insertion methods:

 

public class NotifyRecordDaoTest extends BaseTest {

    @Resource(name = "masterDataSource")
    private DataSource dataSource;


    @Test
    public void insert() throws Exception {

        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        String sql = "insert into notify_record(" +
                "        partner_no," +
                "        trade_no, loan_no, notify_times," +
                "        limit_notify_times, notify_url, notify_type,notify_content," +
                "        notify_status)" +
                "        values(?,?,?,?,?,?,?,?,?) ";

        PreparedStatement statement = connection.prepareStatement(sql);

        for (int i = 0; i < 10000; i++) {
            statement.setString(1, "1");
            statement.setString(2, i + "");
            statement.setInt(3, 1);
            statement.setInt(4, 1);
            statement.setString(5, "1");
            statement.setString(6, "1");
            statement.setString(7, "1");
            statement.setString(8, "1");
            statement.setString(9, "1");
            statement.addBatch();
        }
        long start = System.currentTimeMillis();

        statement.executeBatch();
        connection.commit();
        connection.close();
        statement.close();
        System.out.println(System.currentTimeMillis() - start);


    }

    @Test
    public void insertB() {

        List<NotifyRecordEntity> notifyRecordEntityList = Lists.newArrayList();
        for (int i = 0; i < 10000; i++) {
            NotifyRecordEntity record = new NotifyRecordEntity();
            record.setLastNotifyTime(new Date());
            record.setPartnerNo("1");
            record.setLimitNotifyTimes(1);
            record.setNotifyUrl("1");
            record.setLoanNo("1");
            record.setNotifyContent("1");
            record.setTradeNo("" + i);
            record.setNotifyTimes(1);
            record.setNotifyType(EnumNotifyType.DAIFU);
            record.setNotifyStatus(EnumNotifyStatus.FAIL);
            notifyRecordEntityList.add(record);
        }
        long start = System.currentTimeMillis();
        Map<String, Object> params = Maps.newHashMap();
        params.put("notifyRecordEntityList", notifyRecordEntityList);
        DaoFactory.notifyRecordDao.insertSelectiveList(params);
        System.out.println(System.currentTimeMillis() - start);

    }


    @Resource
    SqlSessionFactory sqlSessionFactory;

    @Test
    public void insertC() {

        SqlSession sqlsession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        NotifyRecordDao notifyRecordDao = sqlsession.getMapper(NotifyRecordDao.class);
        int num = 0;

        for (int i = 0; i < 10000; i++) {
            NotifyRecordEntity record = new NotifyRecordEntity();
            record.setLastNotifyTime(new Date());
            record.setPartnerNo("1");
            record.setLimitNotifyTimes(1);
            record.setNotifyUrl("1");
            record.setLoanNo("1");
            record.setNotifyContent("1");
            record.setTradeNo("s" + i);
            record.setNotifyTimes(1);
            record.setNotifyType(EnumNotifyType.DAIFU);
            record.setNotifyStatus(EnumNotifyStatus.FAIL);
            notifyRecordDao.insert(record);
            num++;
//            if(num>=1000){
//                sqlsession.commit();
//                sqlsession.clearCache();
//                num=0;
//            }
        }
        long start = System.currentTimeMillis();
        sqlsession.commit();
        sqlsession.clearCache();
        sqlsession.close();
        System.out.println(System.currentTimeMillis() - start);


    }
}

It takes more than 5 seconds for the test to insert 10000 pieces of data except for the way of splicing SQL. It takes more than 50 seconds for both Mybatis Batch and native JDBC Batch. It's impossible to think about it. If there's no problem with the writing method, there must be a problem with the database or database connection configuration.

Later, it was found that for batch execution, a new parameter needs to be added to the JDBC connection URL string: rewriteBatchedStatements=true

 

master.jdbc.url=jdbc:mysql://112.126.84.3:3306/outreach_platform?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&rewriteBatchedStatements=true

About rewriteBatchedStatements:

In order to achieve high-performance bulk insertion, the rewriteBatchedStatements parameter should be added to the url of MySQL JDBC connection and the driver of version 5.1.13 or above should be guaranteed.
By default, the MySQL JDBC driver ignores the executeBatch() statement and breaks up a group of sql statements that we expect to execute in batch, and sends them to the MySQL database one by one. In fact, the batch insert is a single insert, which directly results in lower performance.
Only when the rewriteBatchedStatements parameter is set to true can the driver help you execute SQL in batches
In addition, this option is valid for INSERT/UPDATE/DELETE

Comparison of execution speed after adding the parameter rewriteBatchedStatements=true:
The approximate time of inserting 10000 pieces of data in the same table:
JDBC batch 1.1s or so > mybatis batch 2.2s or so > splicing SQL 4.5 s or so

It can be seen that the speed of batch processing is still very powerful.

Let's also talk about the parameter allowMultiQueries=true.
This parameter allows JDBC connections to perform multiple addition, deletion, modification and query at one time. If this parameter is not configured, all batch operations will report errors.



Author: late Song
Link: https://www.jianshu.com/p/0f4b7bc4d22c
Source: Jianshu
The copyright belongs to the author. For commercial reprint, please contact the author for authorization. For non-commercial reprint, please indicate the source.

Published 117 original articles, won praise 31, with 360000 visitors+
His message board follow

Tags: JDBC SQL Mybatis MySQL

Posted on Sun, 12 Jan 2020 23:34:34 -0800 by Hyperjase