Performance Evaluation of Large-Scale Data Insertion into MySQL Using MyBatis, JDBC, and Batch Processing

The author investigates the efficiency of inserting large volumes of data into a MySQL database by generating random person records (ID, name, gender, age, email, phone, address) and measuring insertion times under different approaches.

Test Strategies

MyBatis lightweight framework insertion (no transaction)

Direct JDBC handling (with and without transaction)

JDBC batch processing (with and without transaction)

1. MyBatis Lightweight Insertion (No Transaction)

MyBatis, being a lightweight ORM, was used to insert records in a loop of 5,000,000 entries. The code (no transaction) is shown below:

private long begin = 33112001; // start id
private long end = begin + 100000; // batch size
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";

@org.junit.Test
public void insertBigData2() {
    ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
    PersonMapper pMapper = (PersonMapper) context.getBean("personMapper");
    Person person = new Person();
    long bTime = System.currentTimeMillis();
    for (int i = 0; i < 5000000; i++) {
        person.setId(i);
        person.setName(RandomValue.getChineseName());
        person.setSex(RandomValue.name_sex);
        person.setAge(RandomValue.getNum(1, 100));
        person.setEmail(RandomValue.getEmail(4, 15));
        person.setTel(RandomValue.getTel());
        person.setAddress(RandomValue.getRoad());
        pMapper.insert(person);
        begin++;
    }
    long eTime = System.currentTimeMillis();
    System.out.println("Inserted 5M rows in: " + (eTime - bTime));
}

The test stopped after inserting ~520,000 rows, taking about 7–9 minutes. Inserting 10,000 rows with MyBatis took 28.6 seconds.

2. Direct JDBC Handling

Two sub‑tests were performed: without transaction and with transaction. The core code for the transactional version is:

private long begin = 33112001;
private long end = begin + 100000;
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";

@org.junit.Test
public void insertBigData3() {
    Connection conn = null;
    PreparedStatement pstm = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url, user, password);
        conn.setAutoCommit(false);
        String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
        pstm = conn.prepareStatement(sql);
        long bTime1 = System.currentTimeMillis();
        for (int i = 0; i < 10; i++) { // 10 batches of 10k rows = 100k rows
            long bTime = System.currentTimeMillis();
            while (begin < end) {
                pstm.setLong(1, begin);
                pstm.setString(2, RandomValue.getChineseName());
                pstm.setString(3, RandomValue.name_sex);
                pstm.setInt(4, RandomValue.getNum(1, 100));
                pstm.setString(5, RandomValue.getEmail(4, 15));
                pstm.setString(6, RandomValue.getTel());
                pstm.setString(7, RandomValue.getRoad());
                pstm.execute();
                begin++;
            }
            conn.commit();
            end += 10000;
            long eTime = System.currentTimeMillis();
            System.out.println("Inserted 10k rows in: " + (eTime - bTime));
        }
        long eTime1 = System.currentTimeMillis();
        System.out.println("Inserted 100k rows total: " + (eTime1 - bTime1));
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Results: without transaction, inserting 10,000 rows averaged ~21.2 seconds; with transaction, the average dropped to ~3.9 seconds.

3. JDBC Batch Processing

Batch processing required enabling rewriteBatchedStatements in the URL and preparing the statement outside the loop. Sample code:

private long begin = 33112001;
private long end = begin + 100000;
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";

@org.junit.Test
public void insertBigData() {
    Connection conn = null;
    PreparedStatement pstm = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url, user, password);
        String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
        pstm = conn.prepareStatement(sql);
        long bTime1 = System.currentTimeMillis();
        for (int i = 0; i < 10; i++) { // 10 batches of 100k rows = 1M rows
            long bTime = System.currentTimeMillis();
            while (begin < end) {
                pstm.setLong(1, begin);
                pstm.setString(2, RandomValue.getChineseName());
                pstm.setString(3, RandomValue.name_sex);
                pstm.setInt(4, RandomValue.getNum(1, 100));
                pstm.setString(5, RandomValue.getEmail(4, 15));
                pstm.setString(6, RandomValue.getTel());
                pstm.setString(7, RandomValue.getRoad());
                pstm.addBatch();
                begin++;
            }
            pstm.executeBatch();
            end += 100000;
            long eTime = System.currentTimeMillis();
            System.out.println("Inserted 100k rows in: " + (eTime - bTime));
        }
        long eTime1 = System.currentTimeMillis();
        System.out.println("Inserted 1M rows total: " + (eTime1 - bTime1));
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Results: without transaction, average time per 100k rows was ~2.1 seconds; with transaction, average time per 100k rows was ~1.9 seconds.

Overall Conclusions

Across all tests, enabling transactions consistently reduced insertion time, and JDBC batch processing combined with transactions delivered the best performance for massive single‑row inserts. The author reports that inserting 100 million rows using batch processing with transactions took about 174.8 seconds.

Supplementary Observations

MyBatis performed poorly for bulk inserts unless transactions were used.

Direct JDBC without transactions required a database round‑trip for each row, leading to a 5‑fold slowdown.

Batch processing with transactions showed only a slight advantage over batch without transactions, but the speed gain was still noticeable.

Four configurations were compared: no batch/no transaction, batch only, transaction only, and both batch and transaction (the fastest).

Source: blog.csdn.net/q6834850/article/details/73726707