MySQL ‐ MySQL Fundamentals - woojin-playground/Backend-PlayGround GitHub Wiki

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,'김철수', '[email protected]', 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,'김철수', '[email protected]', 13, 'Seoul'),
    (100,'김철수', '[email protected]', 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', '[email protected]', 20, 'Seoul'),
    ('User2', '[email protected]', 21, 'Busan'),
-- ... 많은 레코드
    ('User10000', '[email protected]', 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
);

MySQL DELETE Guide

• RESTRICT : 참조하는 데이터가 있으면 삭제(기본값)
• CASCADE : 참조하는 데이터도 삭제
• SET NULL : 참조하는 데이터의 외래키를 NULL로 설정
• NO ACTION : RESTRICT과 유사하지만 아무것도 하지 않는다.

-- 한 번에 1000개씩 삭제(MySQL CPU/Memory 사용량 최소화)
DELETE FROM users
WHERE status = 'deleted'
LIMIT 1000;

-- 비효율적: 인덱스 사용 불가(인덱스 컬럼을 가공하게 되면 인덱스를 사용할 수 없다)
DELETE FROM users
WHERE DATE(created_at) < '2024-01-01';

-- 효율적: 인덱스 사용 가능
DELETE FROM users
WHERE created_at < '2024-01-01';

-- 특정 이메일들을 가진 사용자들 삭제(IN 쿼리)
DELETE FROM users
WHERE email IN ('[email protected]', '[email protected]', '[email protected]');

-- NOT EXISTS 사용 (더 안전하고 효율적)
DELETE FROM users
WHERE NOT EXISTS (
    SELECT 1 FROM orders WHERE user_id = users.id
);

-- 다른 테이블과 조인하여 삭제(위험성이 있어 잘 사용X)
DELETE u FROM users u
      JOIN inactive_list il ON u.email = il.email
WHERE il.inactive_date < DATE_SUB(NOW(), INTERVAL 6 MONTH);

-- 완전 삭제 대신 삭제 표시
UPDATE users
SET deleted_at = NOW()
WHERE email = '[email protected]';

MySQL SELECT Guide

• SELECT → 읽기 작업 (트랜잭션 격리 수준)
• SELECT는 데이터를 읽기만 하므로, 어떤 격리 수준을 쓰느냐에 따라 동작이 달라진다.
• MySQL의 InnoDB 기본 격리 수준은 REPEATABLE READ이다.

격리 수준	동작
READ UNCOMMITTED	커밋 안 된 데이터도 읽는다.
READ COMMITTED	커밋된 데이터만 읽는다.
REPEATABLE READ	트랜잭션 내에서 같은 SELECT는 항상 같은 결과를 가진다.
SERIALIZABLE	완전한 직렬화, 가장 엄격

• UPDATE, DELETE → EXCLUSIVE LOCK (배타 락)

트랜잭션 A: UPDATE orders SET amount = 2000 WHERE id = 1;
                    ↓
            해당 row에 X-Lock 획득
            
트랜잭션 B: UPDATE orders SET amount = 3000 WHERE id = 1;
                    ↓
            X-Lock 대기 (블로킹) → A 커밋 후에야 실행 가능

• X-Lock은 다른 트랜잭션의 읽기/쓰기를 차단한다.
• SELECT는 보통 S-Lock(공유 락) 혹은 락 없음, UPDATE/DELETE는 반드시 X-Lock을 가진다.

❓LIKE 인덱스 사용 여부

패턴	인덱스	이유
LIKE 'abc%'	✅ 사용	앞이 고정 → B-Tree 범위 탐색 가능
LIKE '%abc'	❌ 미사용	앞이 불확정 → 풀스캔
LIKE '%abc%'	❌ 미사용	양쪽 불확정 → 풀스캔

• B-Tree는 정렬된 값의 앞부분으로 탐색하기 때문에 접두어가 고정이어야 인덱스를 탈 수 있다.

❓MySQL의 FileSort 메커니즘

쿼리 실행
  └─ 인덱스로 정렬 가능? ──YES──> 인덱스 순서대로 반환 (빠름)
              │
             NO
              │
          filesort 실행 (느림)

1. WHERE — 대상 row 필터링

SELECT * FROM orders WHERE status = 'PAID' ORDER BY created_at DESC;

• 먼저 WHERE 조건으로 정렬 대상 row를 추린다.
• 이 단계에서 인덱스를 잘 타면 FileSort 부하가 줄어든다.
• WHERE 결과가 많을수록 정렬 비용이 증가한다.

2. 정렬할 데이터를 메모리로 로드

• 추려진 row를 sort_buffer에 올린다.
• 이 때, sort_buffer_size = 256KB (기본값)이다.
• 두 가지 방식이 있다.
  • Single-pass : SELECT 컬럼 전체를 버퍼에 로드 → 정렬 후 바로 반환
  • Two-pass : row pointer + 정렬 키만 로드 → 정렬 후 다시 원본 읽기
• 컬럼이 많고 row가 크면 Two-pass 방식을 사용하고 이 선택 기준은 MySQL 옵티마이저가 자동 선택한다.

3. sort_buffer_size — 메모리 vs 디스크 분기점

데이터 크기 ≤ sort_buffer_size  →  메모리 정렬 (빠름)
데이터 크기 >  sort_buffer_size  →  External Sort (느림)

• External Sort(외부 정렬) 발생 시:

sort_buffer가 꽉 참
  └─ 임시 파일(temp file)로 flush
  └─ 다음 청크 로드 → 정렬 → flush
  └─ 모든 청크를 병합(merge)

• 디스크 I/O가 발생하므로 성능이 크게 떨어진다.

4. 정렬된 결과 반환

• 메모리 정렬이면 버퍼에서 바로 반환한다.
• External Sort면 병합된 임시 파일에서 읽어서 반환한다.

❓ORDER BY 성능과 순서

• ORDER BY A, B의 의미는 다음과 같다.

1. A로 먼저 정렬
2. A가 같은 값끼리 묶인 그룹 안에서 B로 정렬

• 즉 A의 카디널리티가 높을수록 2번에서 다시 정렬할 그룹이 작아진다.

A = user_id (카디널리티 높음, 10만 종류)
B = status  (카디널리티 낮음, 3종류)

ORDER BY A, B
  └─ A로 정렬 → 각 그룹 크기 ~1~2개 → B 재정렬 비용 거의 없음

ORDER BY B, A
  └─ B로 정렬 → 각 그룹 크기 ~3만개 → 그 안에서 A 재정렬 비용 큼

• 그러나 MySQL FileSort는 두 컬럼을 하나의 복합 키로 묶어서 한 번에 정렬한다.
• 그래서 실제로 ORDER BY A, B나 ORDER BY B, A든 퀵소트 비교 횟수 자체가 거의 동일하다.
• 진짜 차이가 나는 순간은 인덱스가 있을 때 발생하게 된다.

❓GROUP BY절 최적화

• GROUP BY는 내부적으로 정렬 → 집계 순서로 동작한다.

1. 대상 row 읽기
2. GROUP BY 컬럼 기준으로 정렬 (filesort 발생 가능)
3. 같은 그룹끼리 집계 (COUNT, SUM 등)
4. 결과 반환

• 커버링 인덱스를 사용하면 1번과 2번을 동시에 해결할 수 있기에 커버링 인덱스를 많이 사용한다.
• 꼭 커버링 인덱스가 아니더라도 WHERE로 대상 row를 줄이거나 인덱스 컬럼 순서 맞추기, 집계 함수 단순화 등을 통해 GROUP BY절 최적화도 도모할 수 있다.