[MySQL] INSERT ON DUPLICATE KEY UPDATE와 Handler

목차

1. INSERT ... ON DUPLICATE KEY UPDATE? handler?
2. TEST
   1. 초기설정 및 handler 값 체크
   2. 일반적인 insert 했을 때 변화
   3. DUPLICATE KEY UPDATE를 사용 했을 때 변화
      1. PK가 겹치지 않는 값을 사용할 때
      2. PK가 겹치는 값을 사용하며 insert하려던 값으로 update 진행할 때
      3. PK가 겹칠 때 다른 값으로 UPDATE를 진행
3. 정리

INSERT ... ON DUPLICATE KEY UPDATE? handler?

흔하게 UPSERT라고 불리며 데이터를 INSERT 할 때 PRIMARY KEY 혹은 UNIQUE KEY가 겹칠 경우 사전에 정의한 형태로 해당 ROW를 UPDATE하는 기능을 가진 SQL문.

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

일부 MySQL 모니터링 프로그램에서 Server Status값인 Handler_XXX를 통해서 각종 쿼리의 유입 혹은 동작을 모니터링하는데,
UPSERT문인 ON DUPLICATE KEY UPDATE를 사용할 때 해당 값의 변화를 확인해보았다.

• 해당 handler에 대한 MySQL5.7 도큐먼트의 설명
  • 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로 상태값을 초기화하는게 TEST하는데 도움이 됨.
flush status;

show status where Variable_name in ('Handler_write', 'Handler_update');

• 특별한 작업을 하지 않은 상태의 handler값 확인

23:38:35 57TEST ktw> show status where Variable_name in ('Handler_write', 'Handler_update');
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_update | 0     |
| Handler_write  | 7     |
+----------------+-------+

일반적인 insert 했을 때 변화

-- multi value 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증가 한 것을 확인가능.

23:40:28 57TEST ktw> show status where Variable_name in ('Handler_write', 'Handler_update');
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_update | 0     |
| Handler_write  | 10    |
+----------------+-------+

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증가.

00:08:37 57TEST ktw> show status where Variable_name in ('Handler_write', 'Handler_update');
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_update | 0   |
| Handler_write  | 11  |
+----------------+-------+

PK가 겹치는 값을 사용하며 insert하려던 값으로 update 진행할 때

INSERT INTO devices(id, name)
VALUES (4, 'Pri') ON DUPLICATE KEY
UPDATE name = values(name);

show status where Variable_name in ('Handler_write', 'Handler_update'); 

• write와 update가 각 1씩 증가

00:17:39 57TEST ktw> show status where Variable_name in ('Handler_write', 'Handler_update');
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_update | 1     |
| Handler_write  | 12    |
+----------------+-------+

PK가 겹칠 때 다른 값으로 UPDATE를 진행

INSERT INTO devices(id, name)
VALUES (4, 'Pri') ON DUPLICATE KEY
UPDATE name = 'Cen Pri';

show status where Variable_name in ('Handler_write', 'Handler_update'); 

• write와 update가 각 1씩 증가

00:18:07 57TEST ktw> show status where Variable_name in ('Handler_write', 'Handler_update');
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_update | 2     |
| Handler_write  | 13    |
+----------------+-------+

정리

• DUPLICATE KEY UPDATE는 PK가 겹치지 않을 경우 일반적인 insert와 동일하게 Handler_write 만 증가.
• PK가 겹쳐 update 동작이 일어날 경우 Handler_write 와 Handler_update 가 증가.