Spring Data JPA 에는 ON DUPLICATE KEY UPDATE 가 없다 ! 어떻게 해야할까 !

 

 

 

이번에 새로운 서비스를 개발하다가 필요한 기능이 생겼다.

이번 서비스는 일종의 크롤링과도 관련이 있다.

 

 

주기적으로 타 API를 호출하여 데이터를 가져와서

1. 기존 데이터셋에 추가하거나

2. 기존 데이터의 필드 중 변경사항이 있다면 업데이트해주어야 한다.

 

문제는, 타 API에서 하나의 데이터를 두 개로 관리해주고 있다.

그래서 나는 이 데이터를 합쳐서 하나의 테이블로 관리해주어야 한다.

 

 

API 1은 기본 정보에 관한 것이다.

{
    userId: 1,
    name: "john",
    phone: "012-3456-789"
}

 

 

API 2는 세부 정보에 관한 것이다.

{
    userId: 1,
    job: "Student",
    introduce: "Hello, my name is jonh",
    viewCounts: 2
}

 

 

이 데이터를 다음과 같이 하나의 테이블에서 관리하여 저장해주어야 한다.

userId	name	phone	job	introduce	viewCounts
1	john	012-3456...	Student	Hello, my...	2
...	...	...	...	...	...
10,000	kim	000-0000...	Chief	Hi, nice to...	23

 

그리고 주기적으로 두 개의 API를 호출해서 위 테이블 정보를 업데이트시켜주어야 한다.

나는 한 번의 API 호출 마다, 약 100개의 데이터씩 가져와서 테이블에 저장 or 업데이트 해 줄 것이다.

 

 

쿼리를 최적화

데이터가 많고, 호출되는 API와 그에 따른 데이터 업데이트 SQL문도 잦게 호출될 것이기에 오버헤드 방지가 중요하다고 생각했다.

어떻게 하면 쿼리를 최적화 할 수 있을까?

 

 

시도: 일반적인 방법, saveAll()을 사용해보자

Spring Data JPA에는 saveAll() 이라는 함수가 있다.

saveAll() 이라는 함수는 내부적으로 INSERT INTO 문을 통해 데이터를 저장해주는 방식을 사용하고 있다.

 

옛날에는 saveAll()로 100개의 데이터를 저장한다고 하면, INSERT INTO 문이 100번 실행되는 비효율적인 면이 있었는데

이제는 Bulk INSERT 라고 하여, INSERT INTO 문이 한 번으로, VALUES를 나열해서 실행된다.

 

그리고 만약 이미 PK에 대한 데이터가 저장되어 있다면, SELECT 문으로 데이터를 찾고, 변경된 필드가 있다면 UPDATE로 반영한다.

 

 

 

나는 이러한 쿼리 횟수가 굉장히 낭비될 수 있다고 느꼈다.

또한, 실제 테이블에는 userId, name, phone, job, introduce, viewCounts 라는 6개의 필드가 있는데

1번 API에서 saveAll로 저장할 때는 userId, name, phone 밖에 담지 않은 객체로 어떻게 저장을 할 것인가와

2번 API에서 얻은 데이터로 덮어씌울 때에도 마찬가지로 충분히 문제가 되는 얘기였다.

 

 

 

ON DUPLICATE KEY UPDATE를 사용할 순 없을까?

단일 쿼리로 해결할 방법 중 하나는, on duplicate key update 방법이라고 생각했다.

INSERT INTO로 데이터를 처음 저장할 때 필요한 필드만 저장하고, 만약 해당 필드가 이미 저장되어 있다면 덮어씌워준다.

또한 저장하지 않은 필드들은 건드리지 않으므로

 

- 주기적으로 데이터를 크롤링하여 테이블을 업데이트 해야 하는 현재의 상황

- 1번 API와 2번 API의 데이터를 각각 합쳐서 하나의 테이블로 관리해야 하는 현재의 상황

 

두 가지 상황을 모두 만족할 만한 방법이라고 판단했다.

이런 유지관리의 장점 말고도 단일 쿼리를 통해 쿼리 갯수를 기하급수적으로 절감할 수 있는 방법이다.

 

 

Spring Data JPA에서는 ON DUPLICATE KEY UPDATE를 지원하지 않으므로, SQL 문을 직접 작성하면서 따로 구현해주어야 했다.

그리고 이 과정에서 Batch Insert와 Bulk Insert의 개념이 헷갈릴 수 있어서 따로 정리하자면

 

Batch Insert

여러 개의 SQL 문을 매번 전달하는게 아니라, 한 번에 네트워크로 전달.

그렇다고 해서 SQL 문이 하나로 합쳐지는 것은 아니다. (INSERT는 여전히 100번) 

 

Bulk Insert

INSERT를 하나로 묶는다.

INSERT INTO my_table (column1, column2) VALUES 
('value1', 'value2'),
('value3', 'value4'),
('value5', 'value6');

 

 

따라서 Bulk Insert를 활용해야 한다. Bulk Insert를 활용하면 Batch Insert도 적용될 수 있기 때문이다.

처음에는 Batch Insert만 활용했다가, 실행되는 쿼리의 갯수는 여전히 줄어들지 않는 것을 보고, Bulk Insert로 구현하였다.

 

 

1번 API의 예시 코드는 다음과 같다.

    @Modifying
    @Transactional
    override fun insertOrUpdateAll(data: List<Data>) {
        if (data.isEmpty()) return

        val sqlBuilder = StringBuilder()
        sqlBuilder.append("""
        INSERT INTO person (
            id, name, phone
        ) VALUES 
    """)

        data.forEachIndexed { index, d ->
            sqlBuilder.append("""
            (:id$index, :name$index, :phone$index
        """)

            if (index < data.size - 1) {
                sqlBuilder.append(", ")
            }
        }

        sqlBuilder.append("""
        ON DUPLICATE KEY UPDATE
            name = VALUES(name), 
            phone = VALUES(phone)
    """)

        val query: Query = entityManager.createNativeQuery(sqlBuilder.toString())

        data.forEachIndexed { index, d ->
            query.setParameter("id$index", d.id)
            query.setParameter("name$index", d.name)
            query.setParameter("phone$index", d.phone)
        }

        query.executeUpdate()
    }

 

INSERT INTO 문을 활용한 Bulk Insert를 직접 구현해준다.

 

테이블의 필드 중에서, 추가/변경 할 필드에 대해서만 INSERT INTO의 인자, ON DUPLICATE KEY의 인자로 정의해준다.

또한 parameter 방식을 사용해주어야 sql injection이나 기타 특수문자 첨가 오류를 방지할 수 있다.

 

 

이렇게 하면

기존에 saveAll 또는 batch Insert로 100개의 데이터를 100번씩 추가/변경할 때 총 10,000의 쿼리를 날려야 했다면

100개의 데이터를 100번씩 추가/변경할 때 100번의 쿼리로 충분하다.

 

JPA를 사용한다고 해서 JPA 안에서만 갇혀야 하는 것은 아니다.