테스트 준비

대량의 Row를 Table에 Insert하는 방법은 Batch Insert와 Load Data가 있다.
테스트에 이용한 데이터는 https://launchpad.net/test-db에서 제공하는 employees Table을 사용했고
테스트 Row 수는 1000개, 10000개, 50000개, 100000개, 300000개로 설정했으며
각각의 테스트마다 미리 생성한 employees_batch_N, employees_load_N Table에 Insert 했다.

테스트 과정은 다음과 같다.
1. employees Table에서 N개의 Row를 조회하여 List에 담는다.
2. Batch Insert는 List를 Batch를 돌려 employees_batch_N Table에 Insert하는데 걸리는 시간을 측정한다.
3. load Data는 List로 csv 파일을 생성하고 employees_load_N Table에 Insert하는데 걸리는 시간을 측정한다.

Batch Insert

코드

    public static void batchInsert(String table, List<Employee> employees) throws ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql = "insert into " + table + " values (?, ?, ?, ?, ?, ?)";
        try (
                Connection con = DriverManager.getConnection(url, user, password);
                PreparedStatement query = con.prepareStatement(sql)
        ) {
            con.setAutoCommit(false);

            int rowCount = 0;
            for (Employee employee : employees) {
                query.setInt(1, employee.getEmpNo());
                query.setDate(2, Date.valueOf(employee.getBirthDate()));
                query.setString(3, employee.getFirstName());
                query.setString(4, employee.getLastName());
                query.setString(5, String.valueOf(employee.getGender()));
                query.setDate(6, Date.valueOf(employee.getHireDate()));

                query.addBatch();
                query.clearParameters();
                rowCount++;

                if (rowCount % 1000 == 0 || rowCount == employees.size()) {
                    query.executeBatch();
                    query.clearBatch();
                    con.commit();
                }
            }

            query.executeBatch();
            con.commit();

            con.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Load Data

DB URL을 연결할 때 allowLoadLocalInfile=true 옵션을 추가해야 한다.

코드

    public static void loadDataIntoTable(String table, String fileName) throws ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql = "load data local infile '" + fileName + "' INTO TABLE " + table + " \n" +
                "CHARACTER SET utf8 \n" +
                "FIELDS TERMINATED BY ',' \n" +
                "LINES TERMINATED BY '\r\n' \n" +
                "(emp_no, birth_date, first_name, last_name, gender, hire_date)";
        try (
                Connection con = DriverManager.getConnection(url, user, password);
                PreparedStatement query = con.prepareStatement(sql)
        ) {
            query.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

테스트 결과

rows	1000	10000	50000	100000	500000
Load Data (ms)	65	60	92	166	311
Batch Insert (ms)	1112	8795	41426	81638	244449

결론

Load Data 방식이 Batch Insert보다 월등히 빠르다.
특히 insert할 row 수가 많아지면 많아질수록 그 차이는 비교할 수 없을 만큼 커진다.

Batch Insert는 기존에 Connection을 열어 Insert하는 방식에서 살짝 변화를 준 정도지만, Data Load는 파일을 생성하여 관리해야 하고 필요에 따라 쿼리문을 Custom해야 한다.

performance를 고려했을 때 Batch Insert보다 Load Data 방식을 사용하는 것이 좋지 않을까.

코드: https://github.com/mike-urssu/sample-codes/blob/develop/mysql/src/bulkInsert/BulkInsert.java

수정 (2022-02-06)

Load Data 방식은 처리 속도가 정말 빠르지만 문제가 발생했을 때 파일을 쓰는 과정에서 문제가 발생한건지 파일을 Insert할 때 문제가 발생한건지 원인을 찾기 힘들수도 있다. 반면 Bulk Insert는 처리 시간은 Load Data보다 오래 걸리지만 insert 도중 오류가 발생했을 경우 Transaction 처리가 가능하다.

내가 Load Data를 선택한 이유는 Load Data를 하기 전에 동영상을 프레임으로 추출해야 하는 작업이 있는데 시간이 오래 걸린다. 시간이 오래 걸리는 작업을 연속으로 실행하기에는 서비스 제공에 부적합하다고 판단했다. 프레임 추출하는 시간을 줄일 수는 없으니 데이터를 insert하는데 걸리는 시간이라도 줄이기 위해 Load Data 방식을 선택했다.

선택의 폭이 여러 개가 있다면 Trade-Off를 살펴보고 타당한 이유와 함께 선택을 해야 한다.