Post

Real MySQL - INSERT, UPDATE, DELETE

Posted
Preview Image
By Hyeon Jae Kim
23 min read
Real MySQL - INSERT, UPDATE, DELETE

들어가며

SELECT 문은 데이터를 조회하는 데 사용되지만, 실제 서비스에서는 데이터의 생성, 수정, 삭제가 빈번하게 발생한다. INSERT, UPDATE, DELETE는 데이터를 변경하는 DML(Data Manipulation Language)로, 잘못 사용하면 성능 저하는 물론 데이터 무결성에도 심각한 영향을 줄 수 있다. 이번 글에서는 Real MySQL 8.0 2권 11장의 INSERT 고급 옵션, LOAD DATA 명령, 성능을 위한 테이블 구조, 그리고 UPDATE와 DELETE의 다양한 활용법에 대해 다룬다.


INSERT

INSERT 문은 단순해 보이지만, MySQL에서는 다양한 고급 옵션을 제공한다. 이 옵션들을 적절히 활용하면 중복 처리, 대량 데이터 적재, 성능 최적화 등 다양한 상황에 대응할 수 있다.

고급 옵션

INSERT IGNORE

INSERT IGNORE 는 INSERT 중 에러가 발생해도 해당 레코드를 무시하고 다음 레코드로 진행한다. 주로 유니크 인덱스나 프라이머리 키 중복 시 에러 대신 경고로 처리하고 싶을 때 사용한다.

1
2
3
4
5
6
7

-- 일반 INSERT: 중복 시 에러 발생
INSERT INTO users (id, name) VALUES (1, 'Alice');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

-- INSERT IGNORE: 중복 시 무시하고 진행
INSERT IGNORE INTO users (id, name) VALUES (1, 'Alice');
-- Query OK, 0 rows affected, 1 warning

INSERT IGNORE는 단순히 중복 키 에러만 무시하는 것이 아니다. 데이터 타입 불일치, NOT NULL 칼럼에 NULL 삽입 등 다양한 에러를 경고로 변환한다.

INSERT IGNORE는 에러를 경고로 변환하므로, 의도치 않은 데이터 손실이 발생할 수 있다. 프로덕션 환경에서는 SHOW WARNINGS 로 경고 내용을 반드시 확인해야 한다.

ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE 는 프라이머리 키나 유니크 키가 중복되면 INSERT 대신 UPDATE를 수행한다. 이른바 UPSERT(UPDATE + INSERT) 패턴을 구현할 때 유용하다.

1
2
3
4
5
6
7
8
9
10
11
12

-- 통계 테이블: 날짜별 방문 횟수 카운터
CREATE TABLE daily_statistic (
    target_date DATE NOT NULL,
    stat_name VARCHAR(10) NOT NULL,
    stat_value BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY(target_date, stat_name)
);

-- 첫 방문이면 INSERT, 이미 있으면 stat_value 증가
INSERT INTO daily_statistic (target_date, stat_name, stat_value)
VALUES (CURDATE(), 'VISIT', 1)
ON DUPLICATE KEY UPDATE stat_value = stat_value + 1;

이 패턴은 별도의 SELECT 없이 원자적(atomic) 으로 레코드의 존재 여부를 확인하고 적절한 작업을 수행한다. 카운터, 집계 테이블 등에서 매우 유용하다.

affected-rows 값의 의미도 알아두면 좋다.

상황 affected-rows
새 레코드 INSERT 1
기존 레코드 UPDATE (값이 실제로 변경됨) 2
기존 레코드 UPDATE (값이 동일) 0

MySQL 8.0.19부터는 VALUES() 함수 대신 별칭(alias) 을 사용하는 것이 권장된다. VALUES() 함수는 향후 버전에서 제거될 예정이다.

1
2
3
4
5
6
7

-- 구버전 문법 (deprecated)
INSERT INTO tb (id, col1, col2) VALUES (1, 10, 20)
ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2);

-- MySQL 8.0.19+ 권장 문법
INSERT INTO tb (id, col1, col2) VALUES (1, 10, 20) AS new
ON DUPLICATE KEY UPDATE col1 = new.col1, col2 = new.col2;

REPLACE

REPLACE 는 INSERT와 유사하지만, 중복 키가 발견되면 기존 레코드를 삭제 한 후 새 레코드를 삽입한다.

1

REPLACE INTO users (id, name, email) VALUES (1, 'Bob', 'bob@email.com');

REPLACE와 ON DUPLICATE KEY UPDATE의 핵심 차이점은 다음과 같다.

구분 REPLACE ON DUPLICATE KEY UPDATE
동작 방식 DELETE + INSERT UPDATE
AUTO_INCREMENT 새 값 발급 기존 값 유지
트리거 DELETE + INSERT 트리거 실행 UPDATE 트리거 실행
외래 키 연쇄 삭제 발생 가능 영향 없음

REPLACE는 내부적으로 DELETE를 수행하므로 AUTO_INCREMENT 값이 새로 발급되고, 외래 키의 ON DELETE CASCADE가 동작할 수 있다. 대부분의 경우 ON DUPLICATE KEY UPDATE가 더 안전한 선택이다.


LOAD DATA 명령 주의 사항

LOAD DATA INFILE 은 CSV나 TSV 같은 텍스트 파일에서 대량의 데이터를 테이블로 적재하는 명령이다. MySQL 공식 문서에 따르면 일반 INSERT보다 약 20배 빠르다.

1
2
3
4
5
6
7

LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES  -- 헤더 행 건너뛰기
(emp_no, first_name, last_name, hire_date);

성능이 빠른 이유

LOAD DATA가 빠른 이유는 다음과 같다.

  1. 단일 SQL 파싱 : INSERT가 레코드마다 SQL을 파싱하는 것과 달리, LOAD DATA는 한 번만 파싱한다
  2. 스트리밍 처리 : 파일을 스트림으로 읽어 직접 스토리지 엔진에 전달한다
  3. 배치 인덱스 업데이트 : 인덱스와 제약 조건 검사를 배치로 처리한다
  4. 네트워크 오버헤드 감소 : 서버 로컬 파일 사용 시 네트워크를 거치지 않는다

주요 주의 사항

LOCAL 옵션 : LOAD DATA LOCAL INFILE은 클라이언트 측 파일을 사용한다. 서버에 직접 파일을 두는 것보다 느리지만, 클라이언트에서 바로 실행할 수 있다.

1
2
3
4
5

-- 서버 측 파일 (빠름, FILE 권한 필요)
LOAD DATA INFILE '/var/lib/mysql-files/data.csv' INTO TABLE tb;

-- 클라이언트 측 파일 (약간 느림, local_infile 활성화 필요)
LOAD DATA LOCAL INFILE '/home/user/data.csv' INTO TABLE tb;

secure_file_priv 설정 : 보안상의 이유로 MySQL은 특정 디렉터리의 파일만 읽을 수 있도록 제한한다.

1
2
3

-- 허용된 디렉터리 확인
SHOW VARIABLES LIKE 'secure_file_priv';
-- /var/lib/mysql-files/ (이 디렉터리에 파일을 두어야 함)

문자셋 주의 : 파일의 문자셋과 테이블의 문자셋이 다르면 데이터가 깨질 수 있다.

1
2
3
4

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE tb
CHARACTER SET utf8mb4  -- 파일 문자셋 명시
FIELDS TERMINATED BY ',';

사용자 변수 활용 : 파일의 칼럼과 테이블 칼럼이 다르거나, 데이터 변환이 필요한 경우 사용자 변수와 SET 절을 활용한다.

1
2
3
4
5
6
7
8

LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
(emp_no, @first_name, @last_name, @hire_date)
SET 
    full_name = CONCAT(@first_name, ' ', @last_name),
    hire_date = STR_TO_DATE(@hire_date, '%Y-%m-%d'),
    created_at = NOW();

LOAD DATA 명령을 사용하기 전에 local_infile 시스템 변수가 ON인지, secure_file_priv 설정이 올바른지 확인해야 한다.


성능을 위한 테이블 구조

INSERT 성능은 테이블 구조, 특히 프라이머리 키 설계 에 큰 영향을 받는다. InnoDB 스토리지 엔진의 특성을 이해하면 왜 그런지 알 수 있다.

InnoDB의 클러스터링 인덱스

InnoDB에서 테이블 데이터는 프라이머리 키 순서대로 물리적으로 정렬 되어 저장된다. 이를 클러스터링 인덱스 라고 한다.

1
2
3
4
5
6
7
8
9

┌──────────────────────────────────────────┐
│           InnoDB 클러스터링 인덱스          │
├──────────────────────────────────────────┤
│  PK: 1 → [데이터]                         │
│  PK: 2 → [데이터]                         │
│  PK: 3 → [데이터]                         │
│  ...                                     │
│  PK: N → [데이터]                         │
└──────────────────────────────────────────┘

이 구조 때문에 프라이머리 키 값이 순차적으로 증가 하면 새 레코드는 항상 테이블 끝에 추가된다. 하지만 프라이머리 키가 랜덤 하면 중간에 삽입되므로 페이지 분할과 재정렬이 발생한다.

AUTO_INCREMENT vs UUID

AUTO_INCREMENT 를 프라이머리 키로 사용하면

  • 항상 순차적으로 증가하므로 INSERT가 빠르다
  • 페이지 분할이 거의 발생하지 않는다
  • 정수형(4~8바이트)으로 인덱스 크기가 작다

UUID 를 프라이머리 키로 사용하면

  • 완전 랜덤이므로 INSERT마다 저장 위치를 찾아야 한다
  • 페이지 분할이 빈번하게 발생한다
  • 36바이트(또는 BINARY(16)으로 16바이트)로 인덱스 크기가 크다
  • 세컨더리 인덱스도 PK를 참조하므로 전체적으로 저장 공간이 증가한다
1
2
3
4
5
6
7

-- 권장: AUTO_INCREMENT 프라이머리 키
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_uuid BINARY(16) UNIQUE,  -- UUID는 유니크 세컨더리로
    customer_id BIGINT,
    ...
);

InnoDB에서 최고의 INSERT 성능을 위해서는 프라이머리 키를 AUTO_INCREMENT로 설정하고, UUID가 필요하다면 세컨더리 인덱스로 사용하는 것이 좋다.

UUID를 사용해야 하는 경우

그럼에도 분산 환경이나 보안 이유로 UUID를 프라이머리 키로 사용해야 하는 경우가 있다. 이때는 시간 순서형 UUID 를 고려할 수 있다.

MySQL 8.0에서는 UUID_TO_BIN() 함수의 swap_flag 를 활용할 수 있다.

1
2
3
4
5

-- UUID v1의 타임스탬프 부분을 앞으로 이동 (순차성 확보)
INSERT INTO tb (id) VALUES (UUID_TO_BIN(UUID(), 1));

-- 조회 시 원래 형태로 복원
SELECT BIN_TO_UUID(id, 1) FROM tb;

이 방법은 UUID v6과 유사하게 동작하여 어느 정도의 순차성을 확보할 수 있다.

또는 ULID(Universally Unique Lexicographically Sortable Identifier) 나 Snowflake ID 를 사용하는 방법도 있다. Snowflake ID는 다음과 같은 구조로 64비트 안에 시간 정보, 데이터센터 ID, 서버 ID, 일련번호를 담아 분산 환경에서도 순차적인 ID를 생성한다.

1
2
3
4
5
6

┌────────────────────────────────────────────────────────────────┐
│                    Snowflake ID (64비트)                        │
├────────────┬──────────┬──────────┬─────────────────────────────┤
│ 타임스탬프   │ 데이터센터│  서버ID   │        일련번호              │
│  (41비트)   │  (5비트) │ (5비트)   │        (12비트)              │
└────────────┴──────────┴──────────┴─────────────────────────────┘


UPDATE와 DELETE

UPDATE와 DELETE는 데이터를 변경하거나 삭제하는 명령이다. SELECT와 달리 실행 취소가 불가능하므로 더욱 신중하게 사용해야 한다.

UPDATE … ORDER BY … LIMIT n

MySQL에서는 UPDATE와 DELETE 문에도 ORDER BY 와 LIMIT 을 사용할 수 있다. 이 기능은 표준 SQL에는 없는 MySQL 확장 기능이다.

1
2
3
4
5
6

-- 가장 오래된 주문 50건의 상태를 변경
UPDATE orders 
SET status = 'archived'
WHERE status = 'completed'
ORDER BY completed_at ASC
LIMIT 50;

이 기능이 유용한 이유는 다음과 같다.

복제 환경의 안정성 : ORDER BY 없이 LIMIT만 사용하면 마스터와 슬레이브에서 처리되는 레코드가 달라질 수 있다. ORDER BY로 명확한 순서를 지정하면 복제 환경에서도 동일한 레코드가 처리된다.

대량 변경의 분할 처리 : 한 번에 수백만 건을 UPDATE하면 장시간 테이블 잠금이 발생한다. LIMIT으로 나눠서 처리하면 다른 트랜잭션의 대기 시간을 줄일 수 있다.

1
2
3
4
5
6
7
8
9
10

-- 100만 건을 한 번에 처리 (X: 다른 쿼리 블로킹)
UPDATE large_table SET status = 'processed' WHERE status = 'pending';

-- 1000건씩 나눠서 처리 (O: 다른 쿼리와 공존)
-- 아래를 반복 실행 (affected rows가 0이 될 때까지)
UPDATE large_table 
SET status = 'processed' 
WHERE status = 'pending'
ORDER BY id
LIMIT 1000;

ORDER BY 없이 LIMIT만 사용하면 어떤 레코드가 변경될지 보장되지 않는다. 특히 복제 환경에서는 마스터와 슬레이브에서 다른 레코드가 처리될 수 있으므로, 항상 ORDER BY와 함께 사용해야 한다.


JOIN UPDATE

JOIN UPDATE 는 다른 테이블의 데이터를 참조하여 특정 테이블을 업데이트할 때 사용한다.

1
2
3
4
5

-- departments 테이블의 dept_name을 employees에 반영
UPDATE employees e
INNER JOIN departments d ON e.dept_id = d.id
SET e.dept_name = d.name
WHERE d.active = 1;

MySQL의 JOIN UPDATE는 특이한 특징이 있다. 조인에 참여하는 모든 테이블을 업데이트 할 수 있다.

1
2
3
4
5
6
7

-- 두 테이블을 동시에 업데이트
UPDATE users u
INNER JOIN user_profiles p ON u.id = p.user_id
SET 
    u.updated_at = NOW(),
    p.last_activity = NOW()
WHERE u.status = 'active';

LEFT JOIN UPDATE 는 일치하는 레코드가 없는 경우에도 기준 테이블을 업데이트할 때 사용한다.

1
2
3
4
5

-- 부서가 없는 직원에게 기본 부서 할당
UPDATE employees e
LEFT JOIN departments d ON e.dept_id = d.id
SET e.dept_id = 1  -- 기본 부서
WHERE d.id IS NULL;

여러 테이블을 조인하는 UPDATE 문에서는 ORDER BY와 LIMIT을 사용할 수 없다.


여러 레코드 UPDATE

같은 테이블의 여러 레코드를 각각 다른 값으로 업데이트해야 하는 경우가 있다. 전통적인 방법은 여러 개의 UPDATE 문을 실행하는 것이다.

1
2
3
4

-- 전통적 방법: 여러 쿼리 실행 (비효율적)
UPDATE user_coupon SET expired_at = '2024-09-30' WHERE coupon_id = 1;
UPDATE user_coupon SET expired_at = '2024-12-31' WHERE coupon_id = 2;
UPDATE user_coupon SET expired_at = '2024-11-30' WHERE coupon_id = 3;

MySQL 8.0.19부터는 VALUES ROW() 문법을 활용하여 하나의 쿼리로 여러 레코드를 각각 다른 값으로 업데이트할 수 있다.

1
2
3
4
5
6
7
8
9
10

-- MySQL 8.0.19+: 하나의 쿼리로 처리
UPDATE user_coupon uc
INNER JOIN (
    VALUES 
        ROW(1, '2024-09-30'),
        ROW(2, '2024-12-31'),
        ROW(3, '2024-11-30')
) AS changes(coupon_id, new_expired_at) 
ON uc.coupon_id = changes.coupon_id
SET uc.expired_at = changes.new_expired_at;

VALUES ROW() 문법은 임시 테이블을 생성하지 않고도 상수 집합을 만들어 조인할 수 있다. 이 방법은 여러 쿼리를 실행하는 것보다 훨씬 효율적이다.

MySQL 8.0.19 이전 버전에서는 UNION을 사용한 서브쿼리로 비슷한 효과를 낼 수 있다.

1
2
3
4
5
6
7
8

-- MySQL 8.0.19 이전 버전
UPDATE user_coupon uc
INNER JOIN (
    SELECT 1 AS coupon_id, '2024-09-30' AS new_expired_at
    UNION ALL SELECT 2, '2024-12-31'
    UNION ALL SELECT 3, '2024-11-30'
) AS changes ON uc.coupon_id = changes.coupon_id
SET uc.expired_at = changes.new_expired_at;


JOIN DELETE

JOIN DELETE 는 조인 조건을 만족하는 레코드를 삭제할 때 사용한다. DELETE와 FROM 사이에 삭제할 테이블을 명시 한다.

1
2
3
4
5
6

-- 특정 부서(d001)의 직원과 부서 이력을 모두 삭제
DELETE e, de
FROM employees e
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
INNER JOIN departments d ON de.dept_no = d.dept_no
WHERE d.dept_no = 'd001';

위 쿼리에서 DELETE e, de 는 employees와 dept_emp 테이블에서 조건을 만족하는 레코드를 모두 삭제하라는 의미다. departments 테이블은 조인에만 사용되고 삭제 대상이 아니다.

하나의 테이블만 삭제하고 싶다면 해당 테이블만 명시하면 된다.

1
2
3
4
5

-- employees만 삭제 (dept_emp는 유지)
DELETE e
FROM employees e
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE de.to_date < '2020-01-01';

JOIN DELETE에서 삭제할 테이블을 명시하지 않으면 구문 에러가 발생한다. 반드시 DELETE와 FROM 사이에 삭제 대상 테이블을 지정해야 한다.


마치며

이번 글에서는 INSERT의 고급 옵션, LOAD DATA 명령, 테이블 구조 설계, 그리고 UPDATE/DELETE의 다양한 활용법을 살펴보았다. 핵심 내용을 정리하면 다음과 같다.

INSERT 고급 옵션:

  • INSERT IGNORE 는 에러를 경고로 변환하며, 의도치 않은 데이터 손실에 주의해야 한다
  • ON DUPLICATE KEY UPDATE 는 UPSERT 패턴을 구현하며, MySQL 8.0.19+에서는 별칭 문법을 사용한다
  • REPLACE 는 내부적으로 DELETE 후 INSERT하므로 AUTO_INCREMENT와 외래 키에 영향을 준다

LOAD DATA:

  • 일반 INSERT보다 약 20배 빠르다
  • secure_file_priv와 local_infile 설정을 확인해야 한다
  • 사용자 변수와 SET 절로 데이터 변환이 가능하다

테이블 구조:

  • InnoDB의 클러스터링 인덱스 때문에 프라이머리 키가 INSERT 성능에 큰 영향을 준다
  • AUTO_INCREMENT가 가장 효율적이며, UUID가 필요하면 세컨더리 인덱스로 사용한다
  • Snowflake ID나 ULID 같은 시간 순서형 ID도 대안이 될 수 있다

UPDATE/DELETE:

  • ORDER BY와 LIMIT을 함께 사용하여 대량 변경을 분할 처리할 수 있다
  • JOIN UPDATE/DELETE로 여러 테이블을 동시에 처리할 수 있다
  • MySQL 8.0.19+의 VALUES ROW()로 여러 레코드를 한 번에 다른 값으로 업데이트할 수 있다

References

  • Real MySQL 8.0
데이터베이스
Database mysql
This post is licensed under CC BY 4.0 by the author.