MySQL - ON DUPLICATE KEY UPDATE 중복없이 레코드 바꿔치기(대량 레코드) MyBatis foreach문

데이터를 갱신(INSERT, UPDATE) 할 때에는 중복 키 에러를 피하기 위한 여러 방법이 있다.

이 포스팅에서는 ON DUPLICATE KEY UPDATE에 대해 기술한다. (이하 UPSERT)

 

다른 방법은 아래 포스팅을 참고 바랍니다.

 

2021.05.04 - [MySQL] - MySQL - REPLACE INTO 레코드 치환하기(INSERT IGNORE)

MySQL - REPLACE INTO 레코드 치환하기(INSERT IGNORE)

필요에 의해서 약 5000만 건의 ROW 데이터를 갱신해줘야 하는 서비스를 만들어야 했다.

최종적으로 갱신해줘야 하는 ROW데이터 SELECT부터 INPUT까지 약 5시간이 걸렸다.

 

SELECT시 페이징을 하였고, 속도 문제 때문에 OFFSET은 사용하지 않았으며, Mybatis의 foreach문을 사용했다.

 

전체 ROW를 DELETE 하고 BULK INSERT

 

이 방법도 있는 데, 문제는 만약 DELETE 타깃 테이블이 실 서비스 테이블일 경우, DELETE 후 INSERT 하는 과정에서 불안전한 연결 또는 데이터 문제로 중단되었을 때 서비스를 해야 할 데이터 테이블이 텅 비어버리는 말도 안 되는 상황이 생길 수 있다.

(실제로 1년 차 때에 생긴 일이다.)

 

 

그다음으로 REPLACE INTO

 

REPLACE INTO는 무조건 DELETE 후 INSERT라는 성능상의 문제도 있지만, 내가 원했던 건 레코드가 존재하지 않을 때만 INSERT, 존재했을 때엔  UPDATE로 역할을 확실히 나누어 갱신할 컬럼을 재지정해줘야 했다.

처음 INSERT시 넣었던 데이터를 보존해야 하는 경우(시간, 레코드 식별용 컬럼)

 

 

ON DUPLICATE KEY UPDATE

 

(위와 같은 삽질을 하다가 찾았다.)

Oracle의 MERGE INTO와 유사하게 INSERT, UPDATE 역할을 나눌 수 있다.

(여기서 나는 대량 레코드를 갱신해야 하기 때문에, Mybatis의 foreach문을 사용했다.)

INSERT INTO 테이블명 (컬럼a, 컬럼b, 컬럼c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE 컬럼c = 컬럼c+1;

 

ON DUPLICATE KEY UPDATE는 INSERT 쿼리 수행 후 대응하는 중복 키가 있을 경우 업데이트한다

foreach문을 사용하여 Multiple rows를 갱신할 경우는 아래와 같다

<insert id="insertTest" parameterType="testModel">
  INSERT INTO TEST_TABLE ( no, use, type) VALUES 
    <foreach collection = "list" item = "item" separator = ","> 
    (
    	${item.no},
        ${item.use},
        ${item.type}
     ) 
    </foreach> 
  ON DUPLICATE KEY UPDATE     
    use = VALUES(use),
    type = VALUES(type)				
</insert>

업데이트 컬럼 구문에서 VALUES를 확인할 수 있는 데, foreach문으로 작성된 BULK 데이터 행에 각각 대응하는 값으로

UPDATE 하기 위함이다. (이렇게 안 해서 모든 데이터가 리스트의 마지막 데이터로 도배되었다. 확인 또 확인)

VALUES()는 MySQL향후 버전(MySQL 8.0.20이후)에서 제거될 수 있기때문에 별칭사용을 권한다.

 

 

 alias를 주는 방법이다. (MySQL 8.0.19 버전부터 가능)

<insert id="insertTest" parameterType="testModel">
  INSERT INTO TEST_TABLE (no, use, type) VALUES (${item.no}, ${item.use}, ${item.type}) AS new
  ON DUPLICATE KEY UPDATE use = new.use, type = new.type	
</insert>

또는

<insert id="insertTest" parameterType="testModel">
  INSERT INTO TEST_TABLE (no, use, type) VALUES 
  	(${item.no}, ${item.use}, ${item.type}) AS new(a, b, c)
  ON DUPLICATE KEY UPDATE use = b, type = c
</insert>

 

 

또한, union을 지원한다. 단 union파생 테이블로 다시 묶어서 사용해야 한다. 아래 예시 참조

INSERT INTO TEST_TABLE(no, use, type)
SELECT * FROM
  (SELECT a, b, c FROM t1
   UNION
   SELECT d, e, f FROM t2) AS dt
ON DUPLICATE KEY UPDATE use = b, type = c

 

 

max_allowed_packet?

mysql과 클라이언트 통신 시 핸들링 할 수 있는 데이터의 양

 

대량레코드가 담긴 쿼리를 통신할 때에 레코드 건수 즉, 몇 건의 ROW를 갱신할 지에 대한 기준 값은

max_allowed_packet설정 값을 확인! 쿼리 사이즈 확인은 아래 링크! SQL을 단정하게 바꿔줌과 사이즈 체크!

https://codebeautify.org/sqlformatter

Best SQL Formatter Online

 

주의할 점

• INSERT INTO VALUES ... SELECT 명령문 결과는 행 순서에 따라 달라지며 SELECT의 순서는 보장할 수 없다.
• 테이블에 2개 이상의 유니크 인덱스가 걸려있을 때는 위 구문으로 원하는 결과를 이룰 수 없다.                      INSERT VALUES에서 2개의 유니크 값이 겹쳤을 때엔 둘 중 한 개의 ROW만 업데이트된다.
• max_allowed_packet값을 너무 높은 값으로 설정하면 서버 부하를 발생시킬 수 있다.

Reference

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html