[MySQL] 중복 데이터 처리하기: INSERT IGNORE, REPLACE INTO, ON DUPLICATE KEY UPDATE 가이드

개요

데이터베이스 작업에서 중복 키 처리는 매우 중요한 부분입니다. MySQL은 이러한 중복 상황을 처리하기 위한 여러 방법을 제공하고 있습니다. 이 포스트에서는 INSERT IGNORE, REPLACE INTO, ON DUPLICATE KEY UPDATE 세 가지 방식에 대해 각각의 특징, 장단점, 그리고 적절한 사용 시나리오를 살펴보겠습니다.

중복 키란?

중복 키(Duplicate Key)는 테이블에 이미 존재하는 고유 값(UNIQUE 또는 PRIMARY KEY)과 동일한 값을 다시 삽입하려 할 때 발생합니다. 기본적으로 MySQL은 중복 키 삽입 시도 시 에러를 발생시키지만, 이를 다양한 방식으로 처리할 수 있습니다.

예를 들어, 다음과 같은 users 테이블이 있다고 가정해 보겠습니다:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

위 테이블에서 username과 email은 UNIQUE 제약조건이 있어 중복된 값을 가질 수 없습니다.

INSERT IGNORE

개념

INSERT IGNORE는 중복 키 에러가 발생할 때 해당 작업을 무시하고 계속 진행하도록 지시하는 명령입니다. 즉, 중복된 데이터 삽입 시도는 무시되고 에러가 발생하지 않습니다.

구문

INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

예시

-- 첫 번째 사용자 삽입
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

-- 중복된 username으로 삽입 시도 (에러 발생)
INSERT INTO users (username, email) VALUES ('john_doe', 'john_new@example.com');

-- IGNORE를 사용하여 에러 없이 중복 무시
INSERT IGNORE INTO users (username, email) VALUES ('john_doe', 'john_new@example.com');

위 예시에서 세 번째 쿼리는 에러 없이 실행되지만, 중복된 username 때문에 실제로 데이터는 삽입되지 않습니다.

장점

• 간단하고 사용하기 쉽습니다.
• 배치 삽입에서 일부 행이 중복되더라도 전체 작업이 중단되지 않습니다.

단점

• 중복된 데이터는 단순히 무시되어 어떤 업데이트도 수행되지 않습니다.
• 어떤 행이 실제로 삽입되었는지 명확하게 알기 어려울 수 있습니다.
• ON DUPLICATE KEY UPDATE에 비해 성능이 다소 떨어집니다.

REPLACE INTO

개념

REPLACE INTO는 중복 키가 발생할 경우 기존 행을 삭제한 후 새 행을 삽입합니다. 내부적으로는 DELETE 후 INSERT 작업이 수행됩니다.

구문

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

예시

-- 첫 번째 사용자 삽입
INSERT INTO users (username, email) VALUES ('jane_smith', 'jane@example.com');

-- 동일한 username으로 REPLACE 사용
REPLACE INTO users (username, email) VALUES ('jane_smith', 'jane_updated@example.com');

두 번째 쿼리에서 jane_smith 사용자의 기존 행은 삭제되고, 이메일이 업데이트된 새 행이 삽입됩니다.

장점

• 중복 데이터를 새 데이터로 완전히 교체하므로 명확한 결과를 얻습니다.
• 단순하고 직관적입니다.

단점

• 기존 행을 삭제하기 때문에 해당 행의 모든 데이터가 손실됩니다.
• 명시되지 않은 열의 값은 기본값으로 재설정됩니다.
• 내부적으로 DELETE와 INSERT 두 개의 작업이 필요하므로 성능 오버헤드가 발생할 수 있습니다.
• 외래 키 제약조건이 있는 경우 관련된 레코드에 영향을 미칠 수 있습니다.

ON DUPLICATE KEY UPDATE

개념

ON DUPLICATE KEY UPDATE는 중복 키가 발생할 경우 지정된 열의 값만 업데이트하는 방식입니다. 기존 행이 유지되면서 특정 필드만 갱신됩니다.

구문

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
    column1 = value1,
    column2 = VALUES(column2);

예시

-- 사용자 삽입
INSERT INTO users (username, email)
VALUES ('alex_wong', 'alex@example.com');

-- 이메일만 업데이트하는 ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email)
VALUES ('alex_wong', 'alex_new@example.com')
ON DUPLICATE KEY UPDATE
    email = VALUES(email);

두 번째 쿼리에서 중복된 username이 발견되면 이메일 필드만 새 값으로 업데이트됩니다.

고급 사용 예시

-- 여러 열 업데이트 및 조건부 처리
INSERT INTO products (product_id, name, stock, last_updated)
VALUES (101, 'Smartphone', 50, NOW())
ON DUPLICATE KEY UPDATE
    stock = stock + VALUES(stock),
    name = IF(LENGTH(VALUES(name)) > LENGTH(name), VALUES(name), name),
    last_updated = NOW();

위 쿼리는 제품이 존재하면 재고를 추가하고, 새 이름이 더 길면 이름을 업데이트하며, 마지막 업데이트 시간을 갱신합니다.

장점

• 기존 행의 다른 데이터는 보존하면서 특정 열만 선택적으로 업데이트할 수 있습니다.
• 단일 쿼리로 삽입과 업데이트를 모두 처리하므로 성능이 우수합니다.
• 복잡한 업데이트 로직을 구현할 수 있습니다.

단점

• 구문이 다소 복잡할 수 있습니다.
• 여러 유니크 키가 있는 경우 어떤 키가 충돌을 일으켰는지 확인하기 어려울 수 있습니다.

성능 비교

세 가지 방식의 성능을 비교해보겠습니다:

1. ON DUPLICATE KEY UPDATE: 일반적으로 가장 빠릅니다. 단일 쿼리로 처리되며 내부적으로 최적화되어 있습니다.
2. REPLACE INTO: 중간 정도의 성능을 보입니다. DELETE와 INSERT 두 작업이 필요하지만 단일 쿼리로 실행됩니다.
3. INSERT IGNORE: 상대적으로 느립니다. 삽입 시도 후 중복 키 에러를 처리하는 추가 작업이 필요합니다.

대량의 데이터 처리 시 성능 차이가 더 두드러집니다:

-- 100만 개의 레코드를 처리하는 예시
-- ON DUPLICATE KEY UPDATE (가장 빠름)
INSERT INTO large_table (id, value)
SELECT id, value FROM source_table
ON DUPLICATE KEY UPDATE value = VALUES(value);

-- REPLACE INTO (중간)
REPLACE INTO large_table (id, value)
SELECT id, value FROM source_table;

-- INSERT IGNORE (가장 느림)
INSERT IGNORE INTO large_table (id, value)
SELECT id, value FROM source_table;

사용 시나리오별 추천

INSERT IGNORE 적합한 경우

• 중복 데이터가 발생해도 기존 데이터를 유지하고 싶을 때
• 로그 데이터와 같이 중복되면 단순히 무시해도 되는 경우
• 배치 처리에서 일부 중복이 있어도 전체 작업이 중단되지 않기를 원할 때

REPLACE INTO 적합한 경우

• 기존 데이터를 완전히 새 데이터로 대체하고 싶을 때
• 모든 필드의 값을 새로 설정해야 하는 경우
• 행의 히스토리가 중요하지 않은 경우

ON DUPLICATE KEY UPDATE 적합한 경우

• 기존 데이터의 일부만 업데이트하고 나머지는 보존하고 싶을 때
• 최상의 성능이 필요한 경우
• 복잡한 업데이트 로직(예: 카운터 증가, 조건부 업데이트)이 필요할 때
• 트랜잭션 내에서 일관성 있는 처리가 필요한 경우

실제 응용 사례

로그 시스템

-- 중복된 로그 항목은 무시
INSERT IGNORE INTO logs (log_id, message, timestamp)
VALUES (UUID(), 'User login attempt', NOW());

사용자 프로필 업데이트

-- 사용자 정보 업데이트, 기존 정보 보존
INSERT INTO users (user_id, username, email, last_login)
VALUES (123, 'user123', 'user@example.com', NOW())
ON DUPLICATE KEY UPDATE
    email = VALUES(email),
    last_login = NOW();

재고 관리 시스템

-- 기존 제품 정보 완전 대체
REPLACE INTO products (product_id, name, description, price, stock)
VALUES (101, 'iPhone 15', 'Latest model with new features', 999.99, 500);

방문자 카운터

-- 페이지 방문 횟수 증가
INSERT INTO page_visits (page_id, visits)
VALUES ('home', 1)
ON DUPLICATE KEY UPDATE
    visits = visits + 1;

결론

MySQL에서 중복 키 처리는 데이터 무결성과 애플리케이션 로직에 직접적인 영향을 미치는 중요한 요소입니다. 세 가지 방식은 각각 다른 상황에 적합하며, 요구사항에 맞게 선택해야 합니다:

• INSERT IGNORE: 단순성과 중복 무시가 필요할 때
• REPLACE INTO: 완전한 데이터 교체가 필요할 때
• ON DUPLICATE KEY UPDATE: 선택적 업데이트와 성능이 중요할 때

대부분의 상황에서 ON DUPLICATE KEY UPDATE는 가장 유연하고 성능이 좋은 옵션이지만, 각 상황에 맞는 적절한 방식을 선택하는 것이 중요합니다. 또한 대량의 데이터를 처리할 때는 성능 차이가 크게 나타날 수 있으므로 성능 테스트를 통해 최적의 방식을 선택하는 것이 좋습니다.