How Fast Can You Insert 100 Million Rows into MySQL? A Java Performance Test

Utilizing Java to Insert Hundreds of Millions of Rows into MySQL – Efficiency Evaluation

When testing MySQL query optimization, small data sets (around 100 k rows) are insufficient; therefore, a large synthetic dataset of person records (ID, name, gender, age, email, phone, address) was generated to benchmark insertion performance.

Test Data

Randomly generated person data, illustrated by a sample of 33 million rows.

Insertion Strategies

MyBatis lightweight framework (no transaction)

JDBC direct handling (with and without transaction)

JDBC batch processing (with and without transaction)

MyBatis without Transaction

The MyBatis test inserts 5 million rows (actual run stopped at 520 k due to speed). Inserting 10 000 rows took 28.6 seconds.

Utilizing MyBatis to insert 10 000 rows took 28 613 ms (≈28.6 seconds).

JDBC Direct Handling

Two scenarios were measured: without transaction and with transaction.

Without Transaction

Average time per 10 000 rows: 21.2 seconds (total 212 106 ms for 100 000 rows).

With Transaction

Average time per 10 000 rows: 3.9 seconds (total 39 255 ms for 100 000 rows).

JDBC Batch Processing

Both transaction states were evaluated.

Without Transaction

Average time per 100 000 rows: 2.1 seconds (total 21 737 ms for 1 000 000 rows).

With Transaction

Average time per 100 000 rows: 1.9 seconds (total 19 003 ms for 1 000 000 rows).

Sample Code for MyBatis Insertion (No Transaction)

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("插入500W条数据耗时：" + (eTime - bTime));
}

Sample Code for JDBC Direct Handling (With Transaction)

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++) {
            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();
    }
}

Sample Code for JDBC Batch Processing (With Transaction)

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);
        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.addBatch();
                begin++;
            }
            pstm.executeBatch();
            conn.commit();
            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();
    }
}

Overall Conclusion

Combining JDBC batch processing with transaction management yields the fastest insertion speed for massive single‑row inserts. In the final test, inserting 100 million rows using batch + transaction completed in 174 756 ms (≈2.9 minutes).

Designing large‑scale single‑row insert operations should prioritize JDBC batch processing together with transactions for optimal performance.