INSERT·UPDATE·DELETE 고급

DML(Data Manipulation Language, 데이터 조작어)은 행 데이터를 읽고 바꾸는 SQL이다. 이 문서는 그중 INSERT에서 MySQL이 제공하는 확장 문법을 다룬다. 같은 키의 행이 이미 있을 때 무엇을 할지 정하는 세 가지 문법과, 많은 행을 한 번에 넣는 방법이다. 잘못 고르면 데이터가 조용히 사라지거나 잘려 들어가고, 잠금 대기가 길어진다.

중복 키를 만났을 때 — 세 가지 문법

PK(Primary Key, 기본 키)나 유니크 인덱스(unique index)에 이미 있는 값을 INSERT하면 중복 키 에러(에러 1062)로 문장이 실패한다. MySQL에는 이 상황을 다르게 처리하는 문법이 세 가지 있다.

문법	이미 있으면	영향받은 행 수
INSERT IGNORE	새 행을 버리고 경고만 남긴다	버린 행은 0
INSERT ... ON DUPLICATE KEY UPDATE	기존 행을 UPDATE한다	삽입 1, 갱신 2, 값이 같으면 0
REPLACE	기존 행을 지우고 새 행을 넣는다	지운 행 수 + 1

영향받은 행 수(affected rows)는 문장이 클라이언트에 돌려주는 숫자다. ON DUPLICATE KEY UPDATE는 갱신이면 2를 돌려줘 삽입과 구분할 수 있게 한다.

INSERT INTO stock (sku, qty) VALUES ('A-1', 5), ('B-2', 3) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;

**INSERT ... ON DUPLICATE KEY UPDATE**는 없으면 넣고 있으면 고치는 업서트(upsert)를 한 문장으로 한다. 위 예처럼 넣으려던 값은 행 별칭(row alias, AS new)으로 참조한다. 행 별칭은 8.0.19부터 쓸 수 있고, 예전 방식인 VALUES(qty) 함수는 8.0.20부터 폐기 예정(deprecated)이다. 8.4에서도 동작은 하지만 새 코드에서는 쓰지 않는다. 유니크 인덱스가 둘 이상인 테이블에서는 조심한다. 새 행이 서로 다른 인덱스로 두 기존 행과 겹쳐도 한 행만 갱신되며, 어느 행이 갱신될지 직관과 다를 수 있다. 매뉴얼도 이런 테이블에서는 이 문법을 피하라고 권장한다. InnoDB는 번호를 먼저 받아 두므로, 삽입되지 않고 갱신으로 끝나도 AUTO_INCREMENT 값이 올라가 번호에 빈 곳이 생길 수 있다.

**REPLACE**는 이름과 달리 "고쳐 쓰기"가 아니라 삭제 후 삽입이다. 그래서 문장에 적지 않은 칼럼은 기존 값이 아니라 기본값으로 바뀐다. DELETE 트리거(trigger)와 INSERT 트리거가 모두 발동한다. 유니크 인덱스가 여러 개면 새 행 하나 때문에 기존 행이 여러 개 지워질 수도 있다. 기존 값을 살리면서 일부 칼럼만 바꾸려면 ON DUPLICATE KEY UPDATE를 쓴다.

**INSERT IGNORE**는 중복 행만 버리는 것이 아니다. 데이터 변환 오류도 경고로 낮추고 값을 칼럼에 맞는 가장 가까운 값으로 고쳐 넣는다. 엄격 모드(strict mode)여도 IGNORE가 우선한다. 길이를 넘는 문자열이 잘려 들어가도 문장은 성공하므로, 쓴 뒤에는 SHOW WARNINGS로 무엇이 버려지고 고쳐졌는지 확인한다.

잠금도 다르다. 기본 격리 수준(isolation level)인 REPEATABLE READ에서 일반 INSERT가 중복을 만나면 기존 레코드에 공유 락(shared lock)을 건다. 여러 세션이 같은 키에서 각각 공유 락을 잡은 뒤 각자 행을 넣으려고 배타 락(exclusive lock)을 요청하면, 서로 상대의 공유 락이 풀리기를 기다리게 되어 데드락(deadlock)이 날 수 있다. ON DUPLICATE KEY UPDATE는 중복 행에 처음부터 배타 락을 건다. 중복이 PK면 그 레코드만 잠그는 레코드 락(record lock)을, 유니크 인덱스면 앞 구간까지 잠그는 넥스트키 락(next-key lock)을 건다. REPLACE도 충돌한 행에 배타 넥스트키 락을 건다. 잠금이 어떻게 걸리는지는 InnoDB 잠금과 유니크 인덱스에서 본다.

대량 INSERT와 LOAD DATA

• 여러 행을 한 문장으로. VALUES (...), (...), (...)처럼 한 문장에 여러 행을 넣으면 한 행씩 INSERT하는 것보다 훨씬 빠르다. 문장 하나의 크기는 max_allowed_packet(8.4 기본 64MB)을 넘을 수 없다.
• 한 번에 넣는 양을 나눈다. 수백만 행을 한 트랜잭션(transaction)으로 넣으면 롤백(rollback)에 대비한 언두 로그(undo log)가 쌓이고, 실패하면 되돌리는 데도 오래 걸린다. 복제(replication)를 쓰면 레플리카(replica)는 큰 트랜잭션이 원본 서버(소스, source)에서 끝난 뒤에야 받아 적용하므로 지연이 커진다. 일정 행 수마다 커밋(commit)한다.
• LOAD DATA. 텍스트 파일을 테이블에 읽어 넣는 문장으로, 매뉴얼 기준 INSERT보다 보통 20배쯤 빠르다. 파일 위치에 따라 두 가지다. LOCAL 없이 쓰면 서버 컴퓨터의 파일을 읽으며, FILE 권한(privilege)이 필요하고 secure_file_priv가 허용한 디렉터리의 파일만 읽는다. LOCAL을 붙이면 클라이언트의 파일을 서버로 보내는데, 서버의 local_infile이 8.4 기본값 OFF라 켜야 쓸 수 있다.
• LOAD DATA LOCAL은 IGNORE를 붙인 것처럼 동작해 중복 행과 변환 오류가 에러가 아니라 경고가 된다. 적재가 성공했다고 끝내지 말고 경고 수를 확인한다.
• INSERT INTO t SELECT ... FROM s는 REPEATABLE READ에서 원본 s에서 읽은 행에 공유 넥스트키 락을 건다. 복제나 복구는 이 문장을 다시 실행하므로, 그때도 같은 행이 복사되도록 복사하는 동안 원본 행이 바뀌지 않게 막는 것이다. 그동안 다른 세션이 s의 그 행을 고치지 못하므로, 운영 중인 테이블을 원본으로 대량 복사할 때는 범위를 나눠 짧게 끝낸다.

이어지는 내용은 INSERT·UPDATE·DELETE 고급 2에서 다룬다. 지울 행 수를 제한하거나 다른 테이블과 조인해 고치는 UPDATE·DELETE, 여러 행을 서로 다른 값으로 한 번에 고치는 방법을 본다.

면접 포인트

• "INSERT IGNORE·ON DUPLICATE KEY UPDATE·REPLACE 차이?" → 버림 / 기존 행 갱신 / 삭제 후 삽입. REPLACE는 적지 않은 칼럼이 기본값이 되고 DELETE 트리거가 돈다.
• "INSERT IGNORE가 위험한 이유?" → 중복뿐 아니라 변환 오류도 경고로 낮춰 값이 잘려 들어간다.
• "업서트 동시 실행에서 데드락" → 중복 시 공유 락을 거는 INSERT, 배타 락을 거는 ON DUPLICATE KEY UPDATE의 차이와 유니크 인덱스 넥스트키 락.
• "대량 적재는?" → 여러 행 VALUES나 LOAD DATA, 일정 행 수마다 커밋. LOAD DATA LOCAL은 오류가 경고가 되므로 경고 수를 확인한다.