MySQL ‐ MySQL Fundamentals

MySQL - MySQL Fundamentals

MySQL INSERT Guide

-- [동작] id(PK 또는 UK) 충돌 시 UPDATE, 없으면 INSERT
-- [장점] INSERT/UPDATE를 단일 쿼리로 처리 (Upsert 패턴)
-- [주의] VALUES()는 MySQL 8.0.20부터 deprecated → alias 방식 권장, AUTO_INCREMENT 컬럼이 있을 경우, 충돌 시에도 내부 카운터가 증가하므로 ID 단절(gap) 발생 가능
INSERT INTO users (id, name, email, age, city)
    value (100,'김철수', 'kim@naver.com', 13, 'Seoul')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    age = VALUES(age),
    city = VALUES(city),
    updated_at = NOW();

-- [동작] PK/UK 충돌 시 해당 행을 조용히 무시(skip), 에러 미발생
-- [장점] 중복 데이터 유입 방어에 간편하게 사용 가능
-- [주의] 충돌 외 다른 에러(NOT NULL 위반 등)도 silently 무시, 데이터 정합성 문제가 로그 없이 묻힐 수 있어 운영 환경에서 위험
INSERT IGNORE INTO users (id, name, email, age, city)
    value
    (100,'김철수', 'kim@naver.com', 13, 'Seoul'),
    (100,'김철수', 'kim@naver.com', 13, 'Seoul');

-- [동작] PK/UK 충돌 시 기존 행을 DELETE 후 새 행을 INSERT → 내부적으로 DELETE + INSERT이므로 AUTO_INCREMENT 값이 항상 증가
-- [장점] 문법이 단순
-- [위험1] 운영 환경에서 사용 비권장 - 충돌한 행이 삭제되므로 연관 테이블에 CASCADE DELETE가 걸려 있으면 자식 데이터까지 삭제될 수 있음
-- [위험2] 삭제-삽입이므로 binlog 기반 복제 환경에서 부하 증가
-- [위험3] created_at, 소프트딜리트 컬럼 등 기존 데이터가 전부 유실됨
REPLACE INTO users (id, name, email, age,city)
VALUE (1, 'a', 'b', 3, 's')

-- INSERT ... WHERE NOT EXISTS (중복 방지 패턴)
-- [동작] 서브쿼리로 중복 확인 후 없을 때만 INSERT
-- [문제] Race Condition 위험
-- [개선] UNIQUE 제약 + ON DUPLICATE KEY UPDATE 조합이 원자적(atomic)으로 안전
INSERT INTO users (name, email, age, city) SELECT '새 사용자', 'test메일', 25, 'seoul'
    WHERE NOT EXISTS(
        SELECT 1 FROM users WHERE email = 'test메일'
    );

-- 배치 INSERT (대량 데이터 삽입)
-- [동작] 단일 INSERT로 다수 행을 한 번에 삽입
-- [장점] 단건 INSERT 반복 대비 네트워크 왕복 및 파싱 비용 절감
-- [주의] 한 번에 너무 많은 행을 넣으면 아래 문제 발생
  -- 단일 트랜잭션이 길어져 언두 로그(undo log) 폭증 → InnoDB 성능 저하
  -- max_allowed_packet 초과 시 쿼리 자체가 실패
  -- 실패 시 전체 롤백 → 재처리 범위가 커짐
-- [권장] 1,000 ~ 5,000건 단위로 청크 분할 + 트랜잭션 명시
INSERT INTO users (name, email, age, city) VALUES
    ('User1', 'user1@test.com', 20, 'Seoul'),
    ('User2', 'user2@test.com', 21, 'Busan'),
-- ... 많은 레코드
    ('User10000', 'user10000@test.com', 30, 'Daegu');

-- LOAD DATA INFILE (CSV 파일 직접 로드)
-- [동작] MySQL 서버가 직접 파일을 읽어 테이블에 삽입 (배치 INSERT보다 훨씬 빠름)
-- [장점] 대용량 데이터 로드에서 가장 빠른 방식 (bulk insert path 사용)
-- [위험] 보안 및 운영 이슈 다수 존재
-- [개선] 클라이언트 측 파일을 사용하는 LOCAL 옵션 또는 S3 연동
LOAD DATA INFILE '/path/to/users.csv'
    INTO TABLE users
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    (name, email, age, city);

MySQL UPDATE Guide

UPDATE users
SET city = 'Sejong'
WHERE age BETWEEN 25 AND 35; -- O(log n + m) n : 전체 레코드, m :범위 내 레코드

UPDATE users
SET city = 'Busan'
WHERE city IN ('Incheon', 'Gyeonggi'); -- WHERE city = 'Incheon' OR city ='Gyeonggi'

/*
====================================================================
[쿼리 설명] 
user_preferences 테이블에 존재하는 유저에 한하여, users 테이블의 city 정보를 업데이트합니다.

[시간 복잡도 및 성능 분석]
이 쿼리의 성능은 'user_preferences' 테이블의 인덱스 여부에 크게 좌우됩니다.
(n = users 레코드 수, m = user_preferences 레코드 수)

1. O(n log m) : 정상적인/권장되는 상황 (인덱스 O)
   - user_preferences.user_id 에 인덱스가 걸려있는 경우입니다.
   - users 테이블을 순회(n)하며, 각 레코드마다 인덱스를 타고 user_preferences를
     빠르게 탐색(log m)하므로 가장 이상적인 성능을 냅니다.

2. O(n * m) : 최악의 상황 (인덱스 X)
   - user_preferences.user_id 에 인덱스가 없는 경우입니다.
   - users를 순회(n)할 때마다 user_preferences 전체를 Full Scan(m) 해야 하므로,
     데이터가 많아질수록 쿼리 성능이 기하급수적으로 저하됩니다.

3. O(n + m) : DBMS 옵티마이저 최적화 개입 시
   - 최신 RDBMS(MySQL, PostgreSQL 등) 옵티마이저가 이 상관 서브쿼리를 분석하여,
     내부적으로 해시 조인(Hash Join) 등으로 실행 계획을 변경할 경우
     각 테이블을 한 번씩만 스캔하여 훨씬 빠르게 처리될 수도 있습니다.
====================================================================
*/

UPDATE users AS u 
SET city = (
    -- u.id와 일치하는 데이터를 인덱스를 통해 탐색하여 city 값을 가져옵니다.
    SELECT city 
    FROM user_preferences AS up 
    WHERE up.user_id = u.id
)
WHERE EXISTS(
    -- 이 구문이 없으면 user_preferences에 없는 유저의 city가 NULL로 덮어씌워집니다.
    -- 서브쿼리 내 'SELECT 1'은 데이터 존재 여부(True/False)만 빠르게 판별합니다.
    SELECT 1 
    FROM user_preferences AS up 
    WHERE up.user_id = u.id
);