Performance Evaluation of Inserting Billion-Scale Data into MySQL Using MyBatis, JDBC, and Batch Processing

The author investigates efficient ways to insert massive amounts of data (up to billions of rows) into a MySQL database for performance testing, generating random person records (ID, name, gender, age, email, phone, address).

Test Strategies

Three strategies were evaluated, each in two transaction modes (with and without transaction):

MyBatis lightweight insertion (no transaction)

Direct JDBC handling (with and without transaction)

JDBC batch processing (with and without transaction)

MyBatis Lightweight Insertion

Using Spring to create a mapper and inserting 5,000,000 rows, the test was stopped after 520,000 rows due to speed; inserting 10,000 rows took 28.6 seconds.

//代码内无事务
private long begin = 33112001; //起始id
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 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("插入500W条数据耗时："+(eTime-bTime));
}

Result: inserting 10,000 rows took 28.6 seconds.

Direct JDBC Handling

Without transaction, inserting 100,000 rows averaged 21.2 seconds per 10,000 rows. With transaction, the average dropped to 3.9 seconds per 10,000 rows.

//该代码为开启事务
private long begin = 33112001; //起始id
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++) {
            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("成功插入1W条数据耗时："+(eTime-bTime));
        }
        long eTime1 = System.currentTimeMillis();
        System.out.println("插入10W数据共耗时："+(eTime1-bTime1));
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e1) {
        e1.printStackTrace();
    }
}

Results (no transaction): average 21.2 seconds per 10,000 rows; (with transaction): average 3.9 seconds per 10,000 rows.

JDBC Batch Processing

Batch processing without transaction averaged 2.1 seconds per 100,000 rows; with transaction it improved to 1.9 seconds per 100,000 rows.

private long begin = 33112001; //起始id
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);
        // conn.setAutoCommit(false); // optional
        String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
        pstm = conn.prepareStatement(sql);
        long bTime1 = System.currentTimeMillis();
        for (int i=0;i<10;i++) {
            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();
            // conn.commit(); // optional
            end += 100000;
            long eTime = System.currentTimeMillis();
            System.out.println("成功插入10W条数据耗时："+(eTime-bTime));
        }
        long eTime1 = System.currentTimeMillis();
        System.out.println("插入100W数据共耗时："+(eTime1-bTime1));
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e1) {
        e1.printStackTrace();
    }
}

Results: without transaction, average 2.1 seconds per 100,000 rows; with transaction, average 1.9 seconds per 100,000 rows.

Conclusion

Combining JDBC batch processing with transactions provides the fastest insertion speed for large‑scale single‑row operations, achieving insertion of 100 million rows in roughly 174.8 seconds (≈2.9 minutes). The tests also show that transaction overhead is outweighed by batch execution benefits when handling massive data loads.