삽질 기록 (15) 한 번에 여러 Entity를 저장해야한다면? 벌크벌크 BulkInsert

 

보통 삽질 기록은 한 시간 이상 고민한 것만 올리는데

이건 그냥 시간 남고 심심해서 올림 ㅎ

 

이번 프로젝트에서 크롤링 시 데이터를 150~300개 가량 한 번에 저장하게 되는데, 엄청나게 대단한 용량은 아니지만 그래도 쿼리를 한 개씩 날리는 것보다는 BulkInsert 방식을 적용해야겠다는 생각이 들었다.

근데 JPA는 BulkInsert 설정해봤자 쿼리가 한 개씩 날아가서 아무 의미가 없다.

 

정확히 말하자면 JPA의 잘못이라기보다는, GenerateValue가 Identity인 경우에는 JPA가 매번 INSERT 쿼리를 날려서 다음 ID 값을 가져오기 때문에 생기는 일이다.

 

해결하기 위해서 여러가지 방법이 있겠지만 가장 간단한 방법은 JDBC의 Batch Insert 메서드를 이용하는 것이다.

 

그래서 바로 만들었다.

 

 

@Repository
@Transactional
@RequiredArgsConstructor
public class JobRepository {
    private final JdbcTemplate jdbcTemplate;
 
    public void saveAllJob(List<Job> jobs) {
        String sql =
                " INSERT INTO job (" +
                        "name, state, career_requirement, start_date, end_date, url" +
                        ") values (" +
                        "?, ?, ?, ?, ?, ?" +
                        ")";
 
        jdbcTemplate.batchUpdate(
                sql,
                new BatchPreparedStatementSetter() {
 
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        Job job = jobs.get(i);
                        ps.setString(1, job.getName());
                        ps.setString(2, job.getState());
                        ps.setString(3, job.getCareerRequirement());
                        ps.setDate(4, job.getStartDate());
                        ps.setDate(5, job.getEndDate());
                        ps.setString(6, job.getUrl());
                    }
 
                    @Override
                    public int getBatchSize() {
                        return jobs.size();
                    }
                }
        );
    }
 
    public List<Job> findAllJob() {
        String sql =
                "SELECT * FROM job";
 
        return jdbcTemplate.query(
                sql,
                new RowMapper<Job>() {
                    @Override
                    public Job mapRow(ResultSet rs, int rowNum) throws SQLException {
                        return Job.builder().url(rs.getString("url"))
                                .startDate(rs.getDate("start_date"))
                                .endDate(rs.getDate("end_date"))
                                .name(rs.getString("name"))
                                .careerRequirement(rs.getString("career_requirement"))
                                .state(rs.getString("state"))
                                .id(rs.getLong("job_id"))
                                .build();
                    }
                }
        );
    }
}

 

 

그냥 그런 뻔한 쿼리문과 jdbcTemplate이 들어간다.

findAllJob 메서드를 만든 이유는 그냥 테스트할 때 쓰기 위해서이다.

 

근데, 기껏 이렇게 적용하고 테스트를 돌려보니 이상하다.

 

 

 

 

 

쿼리가 엄청나게 날아가고 있다.

속도도 너무 느리다.

4189ms가 걸렸는데, 아무리 생각해도 BulkInsert가 적용된 속도라고는 생각되지 않는다..

 

 

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-performance-extensions.html

MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.3.13 Performance Extensions

 

이유는 rewriteBatchedStatements의 기본값이 false이기 때문이다.

 

Should the driver use multi-queries, regardless of the setting of 'allowMultiQueries', as well as rewriting of prepared statements for INSERT and REPLACE queries into multi-values clause statements when 'executeBatch()' is called?
Notice that this might allow SQL injection when using plain statements and the provided input is not properly sanitized. Also notice that for prepared statements, if the stream length is not specified when using 'PreparedStatement.set*Stream()', the driver would not be able to determine the optimum number of parameters per batch and might return an error saying that the resultant packet is too large.
'Statement.getGeneratedKeys()', for statements that are rewritten only works when the entire batch consists of INSERT or REPLACE statements.
Be aware that when using "rewriteBatchedStatements=true" with "INSERT ... ON DUPLICATE KEY UPDATE" for rewritten statements, the server returns only one value for all affected (or found) rows in the batch, and it is not possible to map it correctly to the initial statements; in this case the driver returns "0" as the result for each batch statement if total count was zero, and 'Statement.SUCCESS_NO_INFO' if total count was above zero.

executeBatch()가 불릴 때 allowMultiQueries의 설정 값에 관계 없이 multi-queries를 insert와 replace 쿼리문을 multi-values절로 rewriting해야 하나요?
주의 : 이것은 일반 명령어를 사용할 때 SQL Injection을 허용할 수 있습니다. 그리고 제공된 input이 적절하게 sanitized되지 않습니다. (무슨 말인지 모름 ㅋㅋ)
또한 만약 PReparedStatement.set*stram()을 사용할 때 스트림 길이가 명시되지 않았다면 주의해라. 드라이버는 파라미터 배치의 최적값을 판별하지 못할 것이고, 결과 패킷이 너무 길다는 에러를 반환할 것이다.
그리고 rewritten statements를 위해 rewriteBatchedStatement=true를 적용할 때 서버가 해당 데이터 중 하나의 column에 대한 값만 반환하는 것은 불가능하다. 따라서 이것의 저장된 총 개수가 0이라면 0을 반환하며, 1개 이상 저장된 경우 Statement.Success_no_info를 반환한다.

 

심심해서 읽어본 건데 SQL Injection 취약점이 있나보다. 그 밑에는 별로 중요한 말은 없다.

마침 내가 시큐어 코딩을 공부하기 위한 책을 사놓은 상태라 SQL Injection 파트를 공부할 때 이유를 배울 수 있을 것 같다.

 

잠깐 얘기가 샜는데 그냥 application.yml의 datasource-url에 해당 옵션만 추가해서 true로 표기해주면 된다.

 

다시 한 번 테스트해보자.

 

 

 

깔끔하게 한 번만 쿼리가 날아갔으며 시간은 고작 137ms 소요된 것을 확인할 수 있다.

세 번씩 테스트 해봤는데 해당 옵션 적용 전에는 4000ms~5000ms가 소요되었으며, 이후에는 90~150ms가 소요되었다.

최소 26배의 차이가 나는 것이다.

 

 

 

 

JPA가 지원하지 않는 BulkInsert는 JDBC 너굴맨이 해결했으니까 안심하라구~~ 그럼 20000