MySQL REPLACE와 ON DUPLICATE KEY UPDATE 구문

코드를 보다가 MySQL 쿼리에서 `REPLACE INTO`라는 구문을 보았습니다. 이름에서 대강 어떤 일을 할지 예상은 되지만 처음 보는 구문이라 조금 찾아보았습니다.

 

아래는 `REPLACE` 구문을 설명하는 MySQL 문서입니다. 표준 SQL은 아니고, 확장 구문으로 정의된 구문이라고 하네요.

MySQL :: MySQL 8.0 Reference Manual :: 13.2.12 REPLACE Statement

 

첫 문장을 발췌하면 다음과 같습니다.

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

해석하면,

REPLACE는 INSERT 구문과 동일합니다. 단, 만약 테이블에 PRIMARY KEY나 UNIQUE index가 동일한 행이 이미 존재하는 경우에는 그 행을 삭제한 뒤 새 행을 삽입합니다.

 

즉 `REPLACE` 구문은 경우에 따라서

• 이미 삽입하고자 하는 행이 존재하는 경우 `DELETE` + `INSERT`
• 삽입하고자 하는 행이 존재하지 않는 경우 `INSERT`

로 동작합니다.

 

문서에서는 `INSERT ... ON DUPLICATE KEY UPDATE` 구문도 참고해보라고 안내하고 있습니다.

 

MySQL :: MySQL 8.0 Reference Manual :: 13.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement

`INSERT ... ON DUPLICATE KEY UPDATE`는 이름 자체가 친절하게 설명하고 있듯이 삽입을 하되 중복 키가 있으면 update를 수행하는 구문입니다. 의미상으로는 `REPLACE` 구문과 거의 동일하며, 중복 키가 존재하는 경우에 `REPLACE`는 삭제 후 삽입을 하고 `INSERT ... ON DUPLICATE KEY UPDATE`는 update를 수행한다는 점이 다릅니다.

 

사소한 차이처럼 보이지만, `DELETE`가 수행되는지 여부가 중요한 차이가 됩니다.

• 해당 row를 가리키는 외래키(foreign key)가 존재하는 경우, `DELETE`가 발생하면 연쇄 삭제(cascaded delete)가 일어나거나 정합성이 깨지는 일이 발생할 수 있습니다.
• Primary key에 auto increment를 설정해 놓은 경우, unique index 충돌에 의해 기존 행이 삭제되고 새로운 행이 추가되면 PK가 새로 발급됩니다.

특히 두 번째 특징 때문에 성능 면에서도 차이가 발생할 수 있는 것으로 보입니다. 직접 실험을 해 보지는 못했지만, Stack Overflow의 답변을 보면 거의 32배에 달하는 성능 차이가 난다고 합니다. 아마 index 변경이 많이 일어나서 생기는 문제라고 생각합니다.

 

또한 Vitess와 같은 분산 환경에서는 PK가 변경되면 데이터 저장 위치가 변경될 수 있기 때문에 `REPLACE` 구문을 지원하지 않습니다.

 

(그런데 문서에서는 `REPLACE` 구문이 storage engine에 따라서 중복 키가 존재하는 경우 `DELETE` 후 `INSERT`를 수행하는 대신 `UPDATE`를 수행할 수 있으며, 사용자에게 보이는 효과는 동일하다고 기술되어 있습니다. 위와 같은 차이가 명백한데, 왜 사용자에게 보이는 효과가 동일하다고 하는지 이해하기는 어렵습니다.)

 

일반적으로 `INSERT ... ON DUPLICATE KEY UPDATE` 대신에 `REPLACE`를 사용해야 할 이유는 별로 없다고 생각되네요.