My Notes

7 min read

ON DUPLICATE KEY UPDATE와 handler

개요

흔히 UPSERT라 불리며, INSERT 시 PRIMARY KEY 혹은 UNIQUE KEY가 겹칠 경우 사전에 정의한 형태로 해당 ROW를 UPDATE함.

  • Oracle: MERGE INTO
  • MySQL: ON DUPLICATE KEY UPDATE

일부 MySQL 모니터링 프로그램에서 Server Status 값인 Handler_XXX를 통해 쿼리 유입 및 동작을 모니터링함. UPSERT 사용 시 해당 값의 변화를 확인해봄.

정리

상황Handler_writeHandler_update
일반 INSERT+행 수변화 없음
ON DUPLICATE KEY — PK 미겹침+1변화 없음
ON DUPLICATE KEY — PK 겹침 (UPDATE 발생)+1+1

Handler 관련 MySQL 공식 문서

  • Handler_update — The number of requests to update a row in a table.
  • Handler_write — The number of requests to insert a row in a table.

TEST

초기 설정 및 handler 값 체크

CREATE TABLE devices (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
 
-- flush status로 상태값을 초기화하는 것이 테스트에 도움이 됨
FLUSH STATUS;
SHOW STATUS WHERE Variable_name IN ('Handler_write', 'Handler_update');

초기 상태:

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_update | 0     |
| Handler_write  | 7     |
+----------------+-------+

일반적인 INSERT

INSERT INTO devices (name) VALUES ('Rou F1'), ('Sw 1'), ('Sw 2');
SHOW STATUS WHERE Variable_name IN ('Handler_write', 'Handler_update');

Handler_write가 INSERT한 행 수(3)만큼 증가함:

작업 전
(empty)

작업 후
+----+--------+
| id | name   |
+----+--------+
|  1 | Rou F1 |
|  2 | Sw 1   |
|  3 | Sw 2   |
+----+--------+

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_update | 0     |
| Handler_write  | 10    |
+----------------+-------+

ON DUPLICATE KEY UPDATE — PK가 겹치지 않는 경우

INSERT INTO devices (name) VALUES ('Pr') ON DUPLICATE KEY UPDATE name = VALUES(name);
SHOW STATUS WHERE Variable_name IN ('Handler_write', 'Handler_update');

일반 INSERT와 동일하게 Handler_write만 1 증가함:

작업 전
+----+--------+
| id | name   |
+----+--------+
|  1 | Rou F1 |
|  2 | Sw 1   |
|  3 | Sw 2   |
+----+--------+

작업 후
+----+--------+
| id | name   |
+----+--------+
|  1 | Rou F1 |
|  2 | Sw 1   |
|  3 | Sw 2   |
|  4 | Pr     |
+----+--------+

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_update | 0     |
| Handler_write  | 11    |
+----------------+-------+

ON DUPLICATE KEY UPDATE — PK가 겹치며 같은 값으로 UPDATE

INSERT INTO devices (id, name) VALUES (4, 'Pr') ON DUPLICATE KEY UPDATE name = VALUES(name);
SHOW STATUS WHERE Variable_name IN ('Handler_write', 'Handler_update');

Handler_write와 Handler_update 각 1 증가함:

작업 전
+----+--------+
| id | name   |
+----+--------+
|  1 | Rou F1 |
|  2 | Sw 1   |
|  3 | Sw 2   |
|  4 | Pr     |  ← id=4 충돌, 같은 값 UPDATE
+----+--------+

작업 후
+----+--------+
| id | name   |
+----+--------+
|  1 | Rou F1 |
|  2 | Sw 1   |
|  3 | Sw 2   |
|  4 | Pr     |  ← 값 동일, 변화 없음
+----+--------+

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_update | 1     |
| Handler_write  | 12    |
+----------------+-------+

ON DUPLICATE KEY UPDATE — PK가 겹치며 다른 값으로 UPDATE

INSERT INTO devices (id, name) VALUES (4, 'Pr') ON DUPLICATE KEY UPDATE name = 'Cen Pri';
SHOW STATUS WHERE Variable_name IN ('Handler_write', 'Handler_update');

Handler_write와 Handler_update 각 1 증가함:

작업 전
+----+--------+
| id | name   |
+----+--------+
|  1 | Rou F1 |
|  2 | Sw 1   |
|  3 | Sw 2   |
|  4 | Pr     |  ← id=4 충돌, 다른 값으로 UPDATE
+----+--------+

작업 후
+----+---------+
| id | name    |
+----+---------+
|  1 | Rou F1  |
|  2 | Sw 1    |
|  3 | Sw 2    |
|  4 | Cen Pri |  ← 값 변경됨
+----+---------+

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_update | 2     |
| Handler_write  | 13    |
+----------------+-------+

VALUES() 함수 — 버전별 호환성

ON DUPLICATE KEY UPDATE name = VALUES(name) 구문은 8.0.20에서 deprecated, 이후 버전에서 제거 예정임.

Warning 1287: 'VALUES function' is deprecated and will be removed in a future release.
Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col)
in the ON DUPLICATE KEY UPDATE clause with alias.col instead

8.0.19부터 지원되는 alias 문법으로 교체함:

-- deprecated (8.0.20+)
INSERT INTO devices (id, name) VALUES (4, 'Pri')
  ON DUPLICATE KEY UPDATE name = VALUES(name);
 
-- 권장
INSERT INTO devices (id, name) VALUES (4, 'Pri') AS new
  ON DUPLICATE KEY UPDATE name = new.name;
버전VALUES() 상태
5.7정상
8.0.19 미만정상
8.0.20 ~deprecated (경고)
추후 버전제거 예정

MySQL 8.4.8에서 직접 확인 — 실행은 되나 Warning 1287 발생

ROW_COUNT() 반환값

ODKU 실행 후 ROW_COUNT()는 상황마다 다르게 반환됨. 앱/프로시저에서 “몇 건 처리됐냐”를 판단할 때 오해하기 쉬움.

-- INSERT 성공
작업 전: (empty)
INSERT INTO rc_test (id, name) VALUES (1, 'A');
작업 후: id=1, name='A'
ROW_COUNT() → 1

-- UPDATE 발생 (다른 값)
작업 전: id=1, name='A'
INSERT INTO rc_test (id, name) VALUES (1, 'B') ON DUPLICATE KEY UPDATE name = 'B';
작업 후: id=1, name='B'
ROW_COUNT() → 2

-- 변경 없음 (같은 값)
작업 전: id=1, name='B'
INSERT INTO rc_test (id, name) VALUES (1, 'B') ON DUPLICATE KEY UPDATE name = 'B';
작업 후: id=1, name='B'  ← 동일
ROW_COUNT() → 0
상황ROW_COUNT()
INSERT 성공1
UPDATE 발생 (다른 값)2
UPDATE 시도 (같은 값, 변경 없음)0

MySQL 8.4.8에서 직접 확인

REPLACE INTO와의 비교

REPLACE INTO도 upsert처럼 보이지만 내부 동작이 다름. 키 충돌 시 기존 행을 DELETE하고 새 행을 INSERT함.

-- REPLACE INTO (uid=10 충돌)
작업 전
+----+-----+------+
| id | uid | name |
+----+-----+------+
|  1 |  10 | A    |
|  2 |  20 | B    |
+----+-----+------+

REPLACE INTO ai_test (uid, name) VALUES (10, 'X');

작업 후
+----+-----+------+
| id | uid | name |
+----+-----+------+
|  2 |  20 | B    |
|  3 |  10 | X    |  ← id가 1→3으로 변경 (DELETE+INSERT)
+----+-----+------+


-- ODKU (uid=20 충돌)
작업 전
+----+-----+------+
| id | uid | name |
+----+-----+------+
|  2 |  20 | B    |
|  3 |  10 | X    |
+----+-----+------+

INSERT INTO ai_test (uid, name) VALUES (20, 'Y') AS new
  ON DUPLICATE KEY UPDATE name = new.name;

작업 후
+----+-----+------+
| id | uid | name |
+----+-----+------+
|  2 |  20 | Y    |  ← id=2 유지, name만 변경
|  3 |  10 | X    |
+----+-----+------+
항목REPLACE INTOON DUPLICATE KEY UPDATE
내부 동작DELETE + INSERTUPDATE
AUTO_INCREMENT충돌 시 새 값으로 증가기존 id 유지
LAST_INSERT_ID()새 id 반환변경 없음
FK 참조 행 존재 시cascade/restrict 발생영향 없음

Handler_delete는 8.4.8 테스트에서 0으로 나타남. InnoDB 내부 최적화로 카운터에 반영되지 않을 수 있으나, AUTO_INCREMENT 증가로 실제 DELETE+INSERT 동작임이 확인됨.

그래프 뷰