Real MySQL 시즌1 Part 2 — 에피소드 13~24

지도/치트시트: realmysql · 이전: realmysql-part1

Ep 13. Collation (콜레이션)

콜레이션 = 문자를 비교/정렬할 때 쓰는 규칙. 문자 집합(캐릭터셋)에 종속적이다. MySQL에서는 문자열 컬럼마다 독립적으로 지정할 수 있다.

네이밍 컨벤션

utf8mb4 _ 0900 _ ai _ ci
  ①        ③     ④
utf8mb4 _ ja  _ 0900 _ as _ cs _ ks
  ①      ②      ③        ④

동작 원리 — DUCET 가중치

유니코드 기반 콜레이션은 DUCET(Default Unicode Collation Element Table)에 정의된 가중치로 비교한다. 가중치는 단계별로 구성:

SELECT HEX(WEIGHT_STRING('ㄱ' COLLATE utf8mb4_0900_ai_ci));  -- 가중치 직접 확인

한글 음절은 초성/중성/종성으로 분해해서 각 가중치를 조합한다.

⚠️ 한글 함정 — 가 = ㄱㅏ

기본 콜레이션 utf8mb4_0900_ai_ci에서:

'가나다' = 'ㄱㅏㄴㅏㄷㅏ'   ← 같은 값으로 인식됨 (!)
'각'    ≠ 'ㄱㅏㄱ'         ← 이건 다름

이유: 초성 ㄱ의 1차 가중치와 한글 문자 ㄱ의 1차 가중치가 같고, 중성 ㅏ와 문자 ㅏ도 같다. 그런데 종성 ㄱ의 1차 가중치는 문자 ㄱ과 다르다.

as_cs(3차까지)에서는 초성 ㄱ과 문자 ㄱ의 3차 가중치가 달라서 구분된다.

DUCET 표준 값 문제라 MySQL 버그로 볼 수 없고 개인이 바꾸기도 어렵다. 인지하고 쓰는 수밖에 없다.

-- 회피: WHERE에 다른 콜레이션 비교 조건 추가
WHERE name = '가나다'
  AND name COLLATE utf8mb4_unicode_520_ci = '가나다';  -- 520_ci는 가 ≠ ㄱㅏ

주의사항 4가지

① 서로 다른 콜레이션 컬럼 비교 시 쿼리 실패

ERROR: Illegal mix of collations

→ 컬럼 콜레이션을 통일하거나 COLLATE 키워드로 명시.

② WHERE절의 COLLATE는 인덱스를 무력화한다

인덱스는 컬럼에 지정된 콜레이션 규칙으로 정렬·비교되므로, 다른 콜레이션을 지정하면 쓸 수 없다. 수십만 건 테이블에서 2건 조회인데 풀 스캔이 되는 상황이 생긴다.

-- 회피: 함수 기반 인덱스를 쿼리와 동일한 표현식으로 생성
CREATE INDEX ix ON t1 ((name COLLATE utf8mb4_0900_ai_ci));

③ PK/유니크 키도 콜레이션의 영향을 받는다

name 컬럼 콜레이션 = utf8mb4_0900_as_cs (대소문자 구분)
  'esther' 존재 → 'ESTHER' INSERT → ✅ 성공

name 컬럼 콜레이션 = utf8mb4_0900_ai_ci (구분 안 함)
  'esther' 존재 → 'ESTHER' INSERT → ❌ Duplicate entry

④ 기본 콜레이션은 CI — 대소문자 구분이 필요하면 바꿔야 한다.

• 후행 공백을 무시하고 싶다 → utf8mb4_bin
• 대소문자만 잘 구분되면 충분 → utf8mb4_0900_as_cs
• 모든 문자가 명확히 구분되어야 함 → utf8mb4_0900_bin (as_cs는 가중치 기반이라 서로 다른 문자가 같은 가중치일 수 있음)

설정 계층

글로벌(character_set_server / collation_server) → DB → 테이블 → 컬럼 순으로 상속·재정의.

Ep 14. UUID 컬럼의 함정

UUID 버전 정리

(v2는 RFC에 상세 기술이 없어 구현하지 않은 라이브러리가 많다. 실무는 사실상 v1과 v4)

UUID v1의 구조 — 타임스탬프가 뒤섞여 있다

xxxxxxxx - xxxx - 1xxx - yyyy - zzzzzzzzzzzz
   ↑        ↑      ↑      ↑          ↑
 ts 하위4B  ts중간2B ts상위1.5B  시퀀스   MAC 주소
                 (+버전 4bit)

타임스탬프 7.5바이트를 잘라서 순서를 뒤집어 배치한다. 결과:

• 100나노초 단위 증가 → 약 7분 10초마다 첫 파트가 최대값 f...f 를 찍고 0으로 리셋
• 두 번째 파트가 1 증가하지만 정렬 우선순위는 첫 파트가 높다
• → 시간은 흐르는데 UUID 값은 이전 값이 만들어진다 = 사실상 랜덤

B-Tree와 상극인 이유

B-Tree는 짧고 정렬된 값이 순차적으로 들어올 때 가장 효율적이다. UUID는 정반대:

1. 랜덤 → 삽입 위치가 흩어짐
2. 32바이트(16진수 문자열로 저장하는 경우) → 인덱스가 비대
3. UUID 컬럼은 대개 유니크 제약이 필요 → 유니크 인덱스는 체인지 버퍼를 못 쓴다
4. PK로 쓰면 MySQL은 클러스터링 인덱스라 모든 세컨더리 인덱스가 PK 값을 포함 → 전 인덱스가 비대해짐

워킹 셋(Working Set) 개념

쿼리 처리에 실제로 필요한 인덱스 부분. 인덱스가 100GB여도 워킹셋이 작으면 적은 메모리로도 빠르다.

일반적으로 최근 데이터가 자주 필요한데, UUID는 어제 데이터도 prefix가 0~f 전 범위에 흩어진다. → 인덱스 전체가 워킹셋이 된다. 인덱스 100GB면 메모리도 100GB 필요.

비용 계산 (1억 건 테이블)

AWS 기준 db.r*.4xlarge vs db.r5.2xlarge 수준의 차이 → 월 4,500달러, 클러스터당 인스턴스 3~4대면 월 18,000달러 차이.

논리 모델의 오류는 눈에 띄지만 물리 모델의 오류는 나중에 되돌릴 수 없다. 서비스가 커진 뒤엔 연관 서비스가 너무 많아져 모델 변경이 불가능하고, 결국 스펙만 올리게 된다.

대안

① MySQL 내장 함수

UUID_TO_BIN(uuid, swap_flag)   -- 32글자 문자열 → 16바이트 BINARY
BIN_TO_UUID(bin, swap_flag)

-- swap_flag = 0/생략 : 변환만
-- swap_flag = 1      : 1~3번째 파트를 재정렬해 타임스탬프를 앞에 배치 → 단조 증가!

swap_flag=1이면 최근 데이터일수록 UUID가 크므로, 최근 몇 개월의 prefix만 메모리에 있으면 된다 = 워킹셋이 작아진다.

(UUID v6 제안이 정확히 이 내용이었으나 IETF에서 드롭됨)

② 8바이트 정수 UID

Snowflake의 추가 이점: 값 자체에 타임스탬프가 있어 파티션 키로 활용 가능.

-- AUTO_INCREMENT + created_at 레인지 파티션이면
PRIMARY KEY (id, created_at)   -- 파티션 키는 모든 유니크 인덱스에 포함되어야 함
-- Snowflake면 created_at을 PK에 추가하지 않아도 됨

③ 그래도 UUID가 꼭 필요하다면

-- PK는 AUTO_INCREMENT, UUID는 별도 컬럼 + 유니크 인덱스
CREATE TABLE t (
  id     BIGINT AUTO_INCREMENT PRIMARY KEY,
  ext_id CHAR(36) NOT NULL,
  UNIQUE KEY ux_ext (ext_id)
);

테이블 크기·성능 효율을 유지하면서 외부 노출용 랜덤값의 기밀성도 확보.

외부에 노출되지 않고 시스템 내부에서만 쓰는 식별자에 UUID를 쓰는 건 아무 혜택 없는 자원 낭비다.

Ep 15. 인덱스를 못 타는 대표 케이스

① 컬럼이 가공되는 경우

인덱스는 컬럼의 원본 값으로 구성되어 있다. 연산 결과는 인덱싱되어 있지 않다.

-- (a) 산술 연산
WHERE id * 1 = 100        -- ❌ → WHERE id = 100

-- (b) 함수의 인자
WHERE DATE(joined_at) = '2022-07-24'
  -- ❌ → WHERE joined_at >= '2022-07-24' AND joined_at < '2022-07-25'

-- (c) 형변환 (묵시적 포함)
WHERE account_type = 7    -- account_type이 VARCHAR → ❌ 컬럼이 숫자로 변환됨
                          -- → WHERE account_type = '7'

예외: 숫자 컬럼에 문자열 조건 WHERE id = '100' 은 인덱스를 탄다. MySQL의 형변환 우선순위상 "숫자→문자"보다 "문자→숫자"가 우선이라 조건 값이 변환되고 컬럼은 그대로이기 때문. 그래도 매번 확인할 수 없으니 타입을 맞추는 습관이 옳다.

② 인덱싱되지 않은 컬럼과 OR

-- account_type만 인덱스 있음
WHERE account_type = 7 OR joined_at > '2022-07-24';   -- ❌ 풀 스캔

OR는 각 조건을 개별적으로 확인해야 하므로 모든 조건 컬럼에 인덱스가 있어야 풀스캔을 피한다(이 경우 index_merge). AND라면 하나만 있어도 1차 필터링이 가능하다.

③ 복합 인덱스의 선행 컬럼 누락

인덱스 (account_type, joined_at) 에서 joined_at 만 조건으로 주면 사용 불가. 인덱스는 구성 컬럼 순서대로 정렬되어 있기 때문.

④ LIKE의 시작 와일드카드

WHERE first_name LIKE '%esther%'   -- ❌ 범위 지정 불가
WHERE first_name LIKE 'esther%'    -- ✅ 프리픽스 범위 지정 가능

⑤ 정규식 연산

REGEXP는 값 일치가 아니라 표현식 만족 여부를 하나하나 확인해야 하므로 프리픽스 패턴이어도 항상 풀 스캔.

⑥ 풀 스캔이 더 효율적일 때 (옵티마이저 판단)

group_name 분포: a=대다수, b=대다수, c=극소수, d=극소수
WHERE group_name IN ('a','b')  → 풀 스캔 (거의 전부 → 인덱스 경유가 손해)
WHERE group_name IN ('c','d')  → 인덱스 사용

번외 — !=, IS NULL은 항상 인덱스를 못 탄다? 아니다

WHERE group_name NOT IN ('a','b')  -- 대상이 c,d(극소수) → ✅ 인덱스 사용
WHERE group_name NOT IN ('c','d')  -- 대상이 a,b(대다수) → 풀 스캔
WHERE dormant_at IS NULL           -- 30건뿐 → ✅ 인덱스 사용

데이터 분포도에 따라 결정된다. "항상 못 쓴다"는 잘못된 정보.

Ep 16. COUNT(*) vs COUNT(컬럼)

COUNT 함수의 정의

매뉴얼: COUNT(expr) — expr이 NULL이 아닌 값의 개수를 반환한다.

-- ix1: NOT NULL, fd1: NULLABLE (NULL 1건 존재)
COUNT(*)     → 14108
COUNT(1)     → 14108
SUM(1)       → 14108
COUNT(id)    → 14108
COUNT(ix1)   → 14108   -- NOT NULL 컬럼이라 동일
COUNT(fd1)   → 14107   -- ← NULL 1건 제외

SELECT COUNT(NULL);  -- 0
SELECT COUNT(2);     -- 1

조건 없는 COUNT — 처리 방식 4종

MySQL 엔진이 InnoDB에 호출하는 스토리지 엔진 API가 무엇인가에 따라 성능이 갈린다. (실행 계획은 똑같이 보인다!)

100만 건 실측 (innodb_parallel_read_threads 기본값 4):

• ha_records() 호출은 핸들러 메트릭에 기록되지 않아 SHOW STATUS에서 0으로 보인다.
• 병렬 처리 여부에 따라 NOT NULL 컬럼의 동작이 달라진다(레코드에서 컬럼 추출 수행 여부).
• 어느 경우든 COUNT(*)가 항상 가장 좋다.

⚠️ 알려진 버그: 조건 없는 COUNT(*)는 실행 계획엔 idx1 인덱스를 쓴다고 나오지만 실제로는 항상 PK를 스캔한다(버퍼 풀 적재 페이지로 확인됨). 분석에 시간 낭비하지 말 것. 조건이 있는 COUNT엔 해당 없음.

조건 있는 COUNT — 커버링 인덱스가 전부

-- idx1(ix1) 인덱스만 존재
SELECT COUNT(*)   FROM counter WHERE ix1 = 'comment';         -- ✅ Using index
SELECT COUNT(ix1) FROM counter WHERE ix1 = 'comment';         -- ✅ Using index
SELECT COUNT(fd1) FROM counter WHERE ix1 = 'comment';         -- ❌ fd1이 인덱스에 없음
--   → 인덱스로 16건 찾고 PK로 레코드를 1건씩 다시 읽어 fd1 NULL 여부 확인

1천만 건 실측: COUNT(fd2)(비커버링) vs COUNT(fd1)(커버링) → 약 10배 차이. 읽는 건수가 많아질수록 격차는 더 벌어진다.

실행 계획 읽는 법 요약

• Extra: Using index = 커버링 인덱스 (데이터 파일 접근 없음)
• 옵티마이저는 크기가 작은 인덱스를 선호한다. PK는 레코드 데이터를 다 갖고 있어 크므로, 컬럼 1개짜리 세컨더리 인덱스를 고르는 게 정상.
• OLTP 튜닝의 두 축: (1) 인덱스를 타게 하고 (2) 데이터 파일을 읽어야 하는 건수를 최소화한다.

권장

COUNT(*)          -- 기본. 이것만 써라
COUNT(1), SUM(1)  -- 성능 손실은 없지만 의도 전달이 나쁨
COUNT(nullable)   -- 진짜 NOT NULL 건수가 필요하다면 주석 + WHERE에 명시
  → SELECT COUNT(*) FROM t WHERE nullable_col IS NOT NULL;   -- 의도가 명확

Ep 17. NOWAIT / SKIP LOCKED

NOWAIT

잠금 대상이 이미 잠겨 있으면 대기 없이 즉시 에러.

SELECT * FROM users WHERE id = 4 FOR UPDATE NOWAIT;

• innodb_lock_wait_timeout = 0 과 유사한 효과 (이 옵션은 1 이상만 설정 가능, 기본값 50초)
• ⚠️ 에러가 나도 열어둔 트랜잭션은 유지된다. 명시적으로 COMMIT/ROLLBACK 필요.
• 용도: 잠금 대기가 비즈니스적으로 비정상이라 빠르게 에러 처리하고 싶은 경우

SKIP LOCKED

이미 잠긴 레코드는 건너뛰고 잠기지 않은 레코드를 잠근 뒤 반환.

-- 선착순 쿠폰 발급
BEGIN;
SELECT * FROM coupons WHERE used = 0 ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
-- 세션1 → 1번 쿠폰, 세션2 → 2번 쿠폰 (대기 없음)

• 잠금 대상이 비결정적으로 정해진다 (실행해 봐야 안다)
• 전부 잠겨 있으면 에러가 아니라 빈 결과
• 반환 데이터가 없어도 갭 락을 점유할 수 있다
• 보통 ORDER BY + LIMIT과 함께 쓴다
• 용도: 선착순 쿠폰, 작업 큐 테이블을 여러 배치 잡이 동시 처리

SKIP LOCKED 없이 그냥 FOR UPDATE면? 세션2는 세션1의 트랜잭션이 끝날 때까지 대기하거나 타임아웃 → 동시 처리성 급락 + 잠금 경합으로 DB 부하 폭증.

조인과 함께 쓸 때 — OF 구문이 핵심

-- events(1) : coupons(N)
SELECT c.* FROM coupons c JOIN events e ON e.id = c.event_id
WHERE e.id = 1 AND c.used = 0 ORDER BY c.id LIMIT 1
FOR UPDATE SKIP LOCKED;

FOR UPDATE는 조인되는 테이블에도 모두 잠금을 건다. 그래서:

• 세션1이 events 1번 행까지 잠금
• 세션2는 1(events) 쪽이 잠겨 있으니 그에 연결된 N개 데이터를 전부 스킵 → 빈 결과 반환

-- ✅ 실제 변경할 테이블만 잠근다
... FOR UPDATE OF c SKIP LOCKED;

이러면 세션2도 정상적으로 다음 쿠폰을 받고, 다른 세션이 events 테이블을 UPDATE 하는 것도 막지 않는다.

조인이 들어간 FOR UPDATE는 테이블 관계와 조건에 따라 결과가 달라지므로 반드시 사전 검증할 것.

Ep 18. UNION ALL vs UNION DISTINCT

JOIN vs UNION

• JOIN: 결과셋의 컬럼을 확장. 인덱스를 잘 쓰면 임시 테이블 불필요.
• UNION: 결과셋의 레코드를 확장. 인덱스를 아무리 잘 써도 가공 작업이 필요할 수 있다.

동작 차이

10만 건 × 2 집합이면 첫 레코드 반환 시점이 극적으로 차이 난다. UNION ALL은 클라이언트가 다운로드하면서 병렬 처리도 가능하다.

UNION DISTINCT의 내부 동작

1. 결과와 동일한 형태의 임시 테이블 생성
2. 모든 컬럼으로 구성된 유니크 인덱스 생성
3. 각 레코드마다 임시 테이블에서 SELECT로 중복 확인 → 없으면 INSERT
4. 임시 테이블을 읽어 반환 → 삭제

중복 판단 기준은 PK나 유니크 키가 아니다. 집합의 출처가 다르고 이미 가공된 상태라 쓸 수 없다. 그래서 모든 컬럼의 값이 일치하는지 비교한다. → 컬럼이 수백 개면 임시 테이블 컬럼도, 인덱스 구성 컬럼도, 비교 기준도 수백 개. 컬럼 수가 많을수록 급격히 느려진다.

실측

결과가 전혀 겹치지 않는 두 집합(= DISTINCT가 무의미한 경우):

UNION ALL      : 2.62초
UNION DISTINCT : 14.76초   (약 5.6배)

⚠️ 가장 위험한 함정

SELECT ... UNION SELECT ...   -- MySQL은 DISTINCT가 생략된 것으로 판단!

그냥 UNION을 쓰면 느린 UNION DISTINCT가 된다. 중복 제거가 필요한지 판단해서 명시적으로 쓰자.

추가 전략

DBMS는 공유 자원이고 무한 확장이 어렵다. 반면 웹 서버는 상태가 없어 얼마든지 확장 가능하다. → UNION DISTINCT를 UNION ALL로 바꾸고 중복 제거를 애플리케이션에서 하는 것도 좋은 분산 전략이다.

Ep 19. JSON 타입

특징

• 최적화된 바이너리 포맷으로 저장 (파싱 → 유효성 검사 → 직렬화)
• 저장 시 중복 키는 마지막 것이 선택되고, 전체가 키로 정렬되어 저장
• 부분 업데이트 지원
• 특정 키에 인덱스 생성 가능 (함수 기반 인덱스)

저장

c1 JSON DEFAULT NULL
c1 JSON DEFAULT (JSON_OBJECT())    -- 표현식 형태로만 값 지정 (8.0.13+)
c1 JSON DEFAULT ('{}')

-- 함수 방식 (다른 빌트인 함수를 인자로 쓸 수 있음)
INSERT INTO t VALUES (JSON_ARRAY(1, 2, JSON_OBJECT('k','v')));
-- 직접 입력 (객체의 키는 반드시 쌍따옴표!)
INSERT INTO t VALUES ('{"key1": "abc"}');

⚠️ 기존 테이블에 JSON 컬럼 추가 시: DEFAULT NULL이면 INSTANT 가능, DEFAULT에 표현식을 주면 COPY 방식만 가능(DML 차단됨). DDL 방식을 따로 고려해야 한다.

바이너리 포맷에는 키/값의 주소가 포함되어 직접 접근이 가능하다. 단 키는 JSON 데이터마다 중복 저장되므로 키 이름을 짧게 유지하자.

조회 — JSON Path

JSON_EXTRACT(fd, '$.key1', '$.key2')  -- 함수: 변수/컬럼 모두 가능, 여러 경로 가능
fd -> '$.key1'                         -- 컬럼 패스 오퍼레이터 (컬럼만, 1개만)
fd ->> '$.key1'                        -- 인라인 패스 = JSON_UNQUOTE(fd -> ...)
--                                        따옴표/이스케이프 제거된 문자열 반환

비교 함수

문자열 비교 시 쌍따옴표를 같이 넣어야 원하는 결과가 나온다.

변경 함수

부분 업데이트 최적화 (핵심)

조건 4가지 — 모두 만족해야 한다

1. JSON_SET, JSON_REPLACE, JSON_REMOVE 만 사용
2. 함수 인자의 컬럼 = 변경 대상 컬럼
3. 기존 값을 새 값으로 대체하는 형태 (새 키 추가는 안 됨)
4. 새 값의 크기 ≤ 기존 값의 크기

SELECT JSON_STORAGE_SIZE(fd), JSON_STORAGE_FREE(fd) FROM t;
-- 부분 업데이트가 일어나면 size는 그대로, free에 남는 공간이 잡힌다

실측 (10MB JSON × 20건)

binlog 설정이 부분 업데이트 성능을 좌우한다

JSON 타입을 활발히 쓴다면 이 설정들을 반드시 검토하자.

인덱싱

일반 인덱스로는 불가. 함수 기반 인덱스로만 가능하고 CAST가 필수다.

CREATE TABLE tb_json_index (
  fd JSON,
  INDEX ix_name  ((CAST(fd->>'$.name'      AS CHAR(30)))),
  INDEX ix_acct  ((CAST(fd->>'$.account_no' AS SIGNED))),
  INDEX ix_hire  ((CAST(fd->>'$.hire_date'  AS DATE))),
  INDEX ix_dept  ((CAST(fd->>'$.dept_no'    AS UNSIGNED ARRAY)))  -- 멀티 밸류드
);

왜 CAST가 필수인가: 인라인 패스 오퍼레이터의 반환 타입이 LONGTEXT다. LONGTEXT는 프리픽스 길이 없이 인덱싱 불가한데, 함수 기반 인덱스에서는 프리픽스 지정도 불가하다. → CAST로 적절한 타입을 명시해야 한다.

배열 인덱스 (Multi-Valued Index)

일반 인덱스는 데이터:인덱스 = 1:1, 배열 인덱스는 1:N.

WHERE 14 MEMBER OF (fd->'$.dept_no')   -- ✅

사용 가능한 함수는 MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS 뿐.

⚠️ 버그와 제약이 많다 (강사가 직접 제보한 건 포함)

• COUNT 값이 잘못 반환 (8.0.29에서 해결)
• 일반 컬럼 + 배열 값 복합 인덱스의 성능 문제 (미해결)
• 온라인 인덱스 생성 불가 / 커버링 인덱스 불가 / 범위 스캔 불가 / 빈 배열은 찾을 수 없음

사용 전 반드시 실제 동작을 검증할 것.

문자열 인덱싱의 두 가지 함정

① 따옴표 — 어떤 오퍼레이터로 인덱싱했는가에 따라 조건 값이 달라진다.

INDEX ((CAST(fd->>'$.name' AS CHAR(30))))   -- 따옴표 제거됨 → WHERE ... = 'esther'
INDEX ((CAST(fd->'$.name'  AS CHAR(30))))   -- 따옴표 포함   → WHERE ... = '"esther"'

② 콜레이션 — MySQL은 JSON 안의 문자열을 다룰 때 기본적으로 utf8mb4_bin(대소문자 구분)을 쓴다. 그런데 CAST 반환 문자열은 서버 기본 콜레이션(보통 utf8mb4_0900_ai_ci, 대소문자 구분 안 함)을 갖는다.

→ 인덱스를 만들기 전과 후의 쿼리 결과가 달라진다. (인덱스 추가 후 대문자 데이터까지 반환됨) 반드시 주의.

TEXT vs JSON 타입, 무엇을 쓸까

• TEXT: 단순 저장 + 전체 조회 패턴, JSON이 아닌 데이터도 들어올 수 있음 → 더 나은 성능. (TEXT 컬럼에서도 JSON 함수와 키 인덱싱은 가능하다)
• JSON: 특정 키만 주로 조회/변경하는 패턴

정규화 컬럼 vs JSON 컬럼

정답은 없다. 극단적으로 한쪽만 쓰지 말고 섞어서 쓰자.

마지막 주의 — SELECT 절

1MB JSON × 100건 조회 실측: JSON 컬럼 포함 6초 vs 미포함 0.03초. (200배)

ORM은 관성적으로 전체 컬럼을 읽으므로 특히 주의. 그리고 너무 큰 데이터는 OLTP용 RDBMS에 적합하지 않다. 별도 DB 서버로 분리하거나 다른 유형의 저장소를 고려하자.

Ep 20. 데드락

시나리오 1 — 교차 송금 (교과서적)

T1: A → B 100원 송금        T2: B → A 500원 송금
T1: A 레코드 X락, -100      T2: B 레코드 X락, -500
T1: B 레코드 X락 요청 (대기) T2: A 레코드 X락 요청 (대기)   → 데드락

원인: "보내는 사람 차감 → 받는 사람 증가"라는 자연스러운 사람의 사고 흐름. 코드 자체에는 아무 문제가 없다.

해결: 차감/증가 순서가 아니라 사용자 ID(= 인덱스) 순서대로 처리한다.

T1: A 차감 → B 증가
T2: A 증가 → B 차감    ← B부터가 아니라 A부터

두 트랜잭션은 A에서 직렬화되지만 데드락은 없다. 무관한 레코드끼리는 여전히 병렬 처리되므로 일반적인 상황에서 성능 이슈는 없다.

실무에서는 변경 쿼리의 실행 계획과 인덱스를 먼저 확인한 뒤 정렬 기준을 고르면 된다.

시나리오 2 — DELETE 후 동시 INSERT (난이도 높음)

T1: DELETE WHERE pk=2  (커밋 안 함) → pk=2에 X락
T2, T3: INSERT pk=2 시도 → 대기
T1: COMMIT → X락 해제
T2, T3: 동시에 pk=2 레코드에 공유 락 획득 (S락은 동시 획득 가능)
T2, T3: INSERT 위해 X락 요청 → 서로의 S락 때문에 영원히 못 얻음 → 데드락

왜 공유 락을 먼저 거는가? InnoDB에서 PK는 유니크해야 한다. INSERT 시 중복 PK 레코드가 있으면 유니크 속성 보장을 위해 먼저 S락을 걸어야 한다. (한 트랜잭션에서 INSERT가 여러 번 실행돼도 동일한 결과를 보장하기 위한 장치)

왜 삭제된 레코드에 락을 거는가? InnoDB는 레코드 삭제 시 영구 삭제하지 않고 일정 시간 삭제 표시(delete-marked)만 한다. 우리 쿼리에서 안 보일 뿐 InnoDB는 여전히 유효한 레코드로 인식한다.

데드락 탐지

• 락을 걸 때마다 메모리에 그래프 자료구조 생성
• Deadlock Detection Thread가 주기적으로 그래프를 스캔
• 스캔 중에는 다른 모든 트랜잭션이 락을 획득/해제할 수 없다
• 보통은 매우 빨라 영향이 없지만, 동시 트랜잭션이 많고 잠금이 복잡하면 성능 저하 가능

innodb_deadlock_detect = OFF   -- 탐지 비활성화

구글 사례: 일부 서비스가 PK 기반 DML/SELECT만 써서 잠금이 매우 단순 → 데드락이 거의 없고 탐지 스레드의 손실만 있음 → 비활성화해서 사용.

⚠️ 비활성화하면 데드락 발생 시 innodb_lock_wait_timeout(기본 50초)까지 대기 후 에러. 비활성화한다면 타임아웃을 2~3초로 짧게 가져가자.

희생자(Victim) 선정

MySQL은 롤백이 가장 쉬운 트랜잭션 = 언두 레코드 개수가 가장 적은 트랜잭션을 강제 롤백한다.

그래서 배치 작업 vs 서비스 쿼리가 데드락이 되면, 변경이 적은 서비스 쿼리 쪽이 죽는다.

왜 데드락 분석이 어려운가

• 같은 DML이라도 눈에 보이지 않는 삭제 표시 레코드 유무에 따라 잠금 내용이 달라진다
• 실행 시점에 따라서도 달라진다
• 레코드 락 외에 갭 락, 넥스트 키 락이 있다
• 로그 파일에 모든 상황이 기록되지 않는다 (3개 트랜잭션이 얽혀도 2개만 기록되기도)
• 잠금마다 라이프사이클이 다르다 (AUTO_INCREMENT 락은 statement 단위, 레코드 락은 트랜잭션 단위)
• 찰나의 시점 차이로만 발생하는 경우가 있어 재현이 거의 불가능한 경우도 많다
• PK/세컨더리 인덱스/FK로 인해 다른 테이블의 세컨더리 인덱스에도 락이 걸린다

PK와 유니크 인덱스만 있는 테이블에서 INSERT만 하는 트랜잭션끼리도 데드락이 날 만큼 민감하다. 특히 REPEATABLE READ는 사용하는 잠금 종류가 많아 더 민감하다.

실무 태도

• MySQL에서 유니크 인덱스는 성능적인 장점이 전혀 없고, 잠금 경합과 데드락 빈도만 올린다. → 모델링 시점에 유니크 인덱스를 제거할 수 있는 모델을 선택하고, 불필요한 유니크 인덱스는 만들지 말자.
• 해결책은 두 가지: 원인 제거 또는 재처리(retry) 로직 추가. 원인 제거는 쿼리 변경으로 끝날 수도, 데이터 모델 변경까지 필요할 수도 있다.
• 발생 빈도를 보고 판단하자. 모든 데드락을 없애려고 과도한 시간을 쓸 필요는 없다.

데드락이 났다고 여러분의 코드가 잘못된 것도 아니고, 재시도 로직을 넣는다고 코드 품질이 낮아지는 것도 아니다.

Ep 21. JOIN UPDATE / JOIN DELETE

용도

1. 다른 테이블의 컬럼 값을 참조해서(또는 연산해서) 업데이트/삭제
2. 여러 연관 테이블을 한 번에 업데이트/삭제

문법

-- JOIN UPDATE: UPDATE 뒤에 조인문
UPDATE products p JOIN fee_info f ON f.company_id = p.company_id
SET p.fee_amount = p.price * f.fee_rate
WHERE f.company_id = 10;

-- 여러 테이블 동시 업데이트 → SET에 모두 명시
UPDATE products p JOIN orders o ON o.product_id = p.id
SET p.name = '새이름', o.product_name = '새이름'
WHERE p.id = 1;

-- JOIN DELETE: DELETE와 FROM 사이에 삭제 대상 테이블(별칭) 나열
DELETE ul FROM user_logs ul JOIN users u ON u.id = ul.user_id
WHERE u.last_active_at < NOW() - INTERVAL 6 MONTH;

DELETE p, c FROM products p JOIN categories c ON c.id = p.category_id
WHERE c.deprecated = 1;

INNER JOIN뿐 아니라 LEFT JOIN 등 다른 조인 유형도 사용 가능.

실무 팁 — VALUES 테이블 생성자로 다건 개별 업데이트

각 행마다 업데이트할 값이 다를 때, 단건 UPDATE를 N번 날리는 대신:

UPDATE user_coupons uc
JOIN (VALUES ROW(1, '2024-12-31'), ROW(2, '2025-01-15'), ROW(3, '2025-02-28'))
     AS v(coupon_id, expired_at)
  ON v.coupon_id = uc.coupon_id
SET uc.expired_at = v.expired_at;

가상 테이블을 만들어 조인하는 방식. 한 번의 쿼리로 처리 → 작업 효율이 훨씬 높다.

옵티마이저 힌트

UPDATE / DELETE 키워드 바로 뒤에 명시한다.

DELETE /*+ JOIN_FIXED_ORDER() */ p, c FROM products p JOIN categories c ...
-- FROM절에 명시된 순서대로 조인 (STRAIGHT_JOIN 힌트와 동일한 역할)

주의사항

1. 참조하는 모든 테이블에 읽기 잠금이 걸린다(변경 대상은 쓰기 잠금). 이전에 없던 잠금 경합이 생길 수 있다.
2. 1:N 관계에서 N → 1 방향 업데이트는 결과가 불명확하다. (어떤 값으로 갱신될지 정해지지 않음) N:M도 마찬가지. 대상 데이터를 명확히 정하자.
3. 반드시 실행 계획을 사전 확인한다. 조인 순서가 예상대로인지, 적절한 인덱스가 없어 대량 스캔 → 대량 잠금을 유발하지 않는지.
4. 운영 DB에서 대량 데이터를 다룬다면 한 쿼리로 한 번에 처리하지 말 것.

Ep 22. 커넥션 관리

MySQL의 특성

• Oracle/PostgreSQL과 달리 스레드 기반 → 커넥션당 메모리 사용량이 상대적으로 낮다
• 일부 버전은 스레드 풀을 제공(커넥션 전용 스레드 대신 공유 스레드) → 더 컴팩트
• 실측(2,500 커넥션): 대부분 16KB ~ 3MB, 일부는 10MB까지
• max_connections는 논리적으로 10만까지 설정 가능하지만 현실적으로 1~2만 수준. 강사 경험상 최대치가 1만 정도.
• 참고: PostgreSQL은 5,000 커넥션 근처에서 메모리 압박이 심해지고, 버퍼 풀 외에 리눅스 페이지 캐시에도 의존해 메모리 부족의 영향을 더 크게 받는다. 그래서 미들웨어가 거의 필수인 반면, MySQL은 아직 그렇지 않다.

커넥션 풀 설정

목적: 연결 생성/해제 오버헤드 감소 + MySQL과의 커넥션 최소화. 후자를 잊는 경우가 많다.

① max는 20~30부터 시작

코어 2개짜리 앱 서버에서 max를 200으로 시작하는 경우가 많다. 처음부터 높게 잡으면 낮추는 방향의 튜닝이 어려워진다.

쿠버네티스 환경에서 파드 100200개 × 파드당 max 100200 = 2만 커넥션이 필요해진다.

② min ≠ max 로 두자

같은 값으로 두면 커넥션이 부족한 상황인지 아닌지 MySQL 쪽에서 예측할 수 없다.

min=20, max=30 설정
→ MySQL에서 클라이언트 IP별 커넥션 수를 확인
→ 피크에 앱 서버당 25개  → "30이 적당하구나"
→ 피크에 앱 서버당 30개  → "부족하구나" (간단히 감지 가능)

③ 타임아웃

타임아웃이 짧을 때의 악순환: 커넥션을 못 가져와도 대부분의 앱에는 폴백 로직이 없다. → 계속 재요청 → 웹 서버와 MySQL 양쪽에 커넥션 생성 요청만 쌓임 → 악순환.

커넥션 획득 실패 시 에러 화면을 보여주거나 다른 로직을 실행할 수 있다면 짧게 잡아도 좋다. 재요청을 반복한다면 짧게 잡지 말 것.

idle timeout이 길어야 하는 이유: 커넥션 생성 자원 소모도 있지만, 커넥션 하위에 캐시된 객체(Prepared Statement 등)가 한 번에 사라진다. → 한 번 만든 커넥션은 최대한 오래 재활용하자. (MySQL 자체 기본값은 8시간)

④ 커넥션 선택 방식과 검증

• 풀에 따라 가장 최근 사용한 커넥션을 주기도, 가장 오래 안 쓴 커넥션을 주기도 한다.
• 전자는 성능 이점이 있다지만 크지 않고, 오히려 안 쓰이는 커넥션의 idle 시간이 길어져 외부에서 끊긴 상태로 남을 가능성을 높인다.
• 라운드 로빈으로 고르게 사용하고, 가져올 때/반환할 때/idle일 때 validation 체크를 하자.
• 요즘 MySQL은 SELECT 1 대신 ping 커맨드로 가볍게 상태 확인이 가능하다.
• 그래도 끊어진 커넥션 에러는 발생할 수 있으니 중요한 로직엔 retry를 넣자.

각자 쓰는 커넥션 풀이 내부적으로 어떻게 동작하는지 공부해 두면 문제 발생 시 대처가 빨라진다.

미들웨어 (서버 사이드 커넥션 풀)

MySQL Router / ProxySQL / AWS RDS Proxy

• 미들웨어 수준에서 커넥션을 공유 → 실제 MySQL 커넥션 수를 크게 줄인다
• 응용 프로그램 ↔ MySQL 커넥션 매핑 + 쿼리 라우팅
• 단점: 별도 하드웨어/관리 비용, 네트워크 홉이 하나 늘어 트러블슈팅 난이도 상승
• → 커넥션이 정말 감당 안 될 때만 투입. Aurora라면 Writer/Reader 중 선별적으로 적용하는 방법도 좋다.

⚠️ ProxySQL + Aurora Reader Endpoint 함정

• Aurora의 하나의 Reader Endpoint는 DNS 룩업마다 여러 리더 서버의 IP를 순차 반환한다(DNS Round Robin). 이게 자연스러운 부하 분산 역할을 한다.
• 그런데 ProxySQL에는 DNS 캐시 기능(mysql-monitor_local_dns_cache_ttl)이 내장되어 있다.
• → 한 번 룩업한 IP를 계속 재사용 → 리더가 10대여도 1대에만 부하가 몰린다.
• 해결: DNS 캐시를 끄면 부하 분산이 정상 동작한다.

Ep 23. 테이블 파티셔닝

하나의 테이블을 물리적으로 여러 테이블로 나눠 저장. 데이터와 인덱스가 나뉘어 저장되지만 사용자는 여전히 하나의 테이블로 접근한다.

왜 쓰나 — 두 가지 이점

① 이력 데이터 관리

로그성 데이터를 DELETE로 지우는 대신 파티션을 DROP한다. 테이블 DROP처럼 디스크 공간을 완전히 반납한다.

② 파티션 프루닝 (Partition Pruning)

조건 범위에 해당하지 않는 파티션을 쿼리 처리에서 자동 제외한다.

일반 테이블   : 하나의 커다란 인덱스를 탐색
파티션 테이블 : 해당 파티션의 인덱스만 탐색  → 메모리 효율 ↑, 응답 시간 ↓

파티션 프루닝이 자원 효율과 성능 향상의 핵심 기술이다.

파티션 타입

• LINEAR 계열: 데이터가 덜 균등할 수 있지만 파티션 추가/삭제/병합/분할 시 성능이 더 좋다
• 실무에서는 RANGE와 RANGE COLUMNS가 거의 전부

제약과 주의사항

1. 외래키, 공간 데이터 타입(POINT/GEOMETRY), 전문 검색 인덱스가 있는 테이블은 파티셔닝 불가
2. 파티션 표현식에 쓸 수 있고 프루닝까지 되는 함수는 4개뿐: TO_DAYS, TO_SECONDS, YEAR, UNIX_TIMESTAMP
3. 파티션 기준 컬럼은 모든 고유키(PK·유니크 키)에 반드시 포함되어야 한다
   • MySQL 일반 테이블의 인덱스는 글로벌 인덱스지만, 파티션 테이블은 각 파티션이 독립 테이블처럼 취급되는 로컬 인덱스다. 그래서 파티션 키를 고유키에 포함시켜야 파티션별 유니크성이 곧 전체 유니크성이 된다.
4. WHERE에 파티션 기준 컬럼 조건이 있어야 프루닝이 동작한다. 없으면 전체 파티션 탐색.
5. 값이 자주 바뀌는 컬럼을 파티션 키로 쓰지 말 것 → 파티션 간 데이터 이동 발생

생성 예시

TIMESTAMP + RANGE

CREATE TABLE user_logs (
  id BIGINT AUTO_INCREMENT,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (id, created_at)          -- 파티션 키 포함
) PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
  PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
  PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')),
  PARTITION p202403 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

-- 조회 시엔 UNIX_TIMESTAMP를 쓸 필요 없이 컬럼 그대로 → 프루닝 동작
SELECT * FROM user_logs WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';

⚠️ TIMESTAMP(6)은 파티셔닝은 되지만 프루닝이 안 된다. 소수점 때문에 FLOOR(UNIX_TIMESTAMP(...))가 필요한데, 이 형태로는 프루닝이 동작하지 않는다.

밀리초/마이크로초까지 저장하면서 날짜 기반 파티셔닝을 할 가능성이 있다면 TIMESTAMP 대신 DATETIME을 쓰자. DATETIME(6)은 RANGE COLUMNS로 파티셔닝 + 프루닝 모두 정상 동작한다.

DATETIME + RANGE COLUMNS (가장 깔끔)

PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
  ...
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

DATETIME + RANGE: YEAR(created_at) 처럼 프루닝 지원 + 정수 반환 함수 사용.

파티션 관리

-- 마지막 이후 신규 파티션 추가 (MAXVALUE 파티션이 없을 때만)
ALTER TABLE t ADD PARTITION (PARTITION p202405 VALUES LESS THAN (...));

-- MAXVALUE 파티션이 있거나, 중간에 끼워 넣을 때 → REORGANIZE
ALTER TABLE t REORGANIZE PARTITION pmax INTO (
  PARTITION p202405 VALUES LESS THAN (...),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

ALTER TABLE t DROP PARTITION p202401;       -- 제거 (공간 완전 반납)
ALTER TABLE t TRUNCATE PARTITION p202401;   -- 데이터만 비우기

운영 팁

• 주로 접근하는 범위 기준으로 파티셔닝. 최근 한 달 데이터를 주로 본다면 월 단위. 6개월 지난 데이터를 지운다면 월 단위(새 달마다 6개월 전 파티션 제거).
• 범위를 너무 잘게/크게 쪼개면 자원·관리 양쪽에서 비효율.
• MAXVALUE 파티션은 반드시 만들자. 저장할 파티션이 없으면 INSERT 에러 → 서비스 장애로 이어진다.
• 파티션 추가/삭제도 일반 ALTER와 동일하게 MDL 잠금이 발생한다. 트래픽 적은 시점에, 오래 열린 트랜잭션이 없는지 확인 후 수행.
• 관리 대상 파티션 테이블이 많다면 자동화 스크립트가 사실상 필수.

파티션 테이블에서의 인덱스

• 고유키가 아닌 일반 보조 인덱스는 파티션 키를 포함하지 않아도 된다.
• 모든 파티션에 동일한 구조의 인덱스가 생성된다. 파티션별로 다르게 가져갈 수 없다.
• 처리 순서: 파티션 프루닝으로 대상 파티션 결정 → 각 파티션에서 인덱스 스캔

EXPLAIN SELECT ...;                      -- partitions 항목으로 접근 대상 확인
SELECT * FROM user_logs PARTITION (p202402) WHERE ...;   -- 명시적 지정도 가능

Ep 24. 배치 작업 / 대용량 쿼리 / 롱 트랜잭션

"굵고 짧게" vs "가늘고 길게"

• 개발자: 최대한 많은 스레드로 굵고 짧게 끝내고 싶다
• DBA: 가늘고 길게 늘려서, OLTP 쿼리가 평소 속도를 유지하길 바란다

빨간 그래프(개발자 기대) : 짧은 시간 CPU 100% 스파이크 → 나머지는 유휴
파란 그래프(DBA 기대)   : 평균은 더 높지만 일정 수준을 유지

CPU가 급작스럽게 튀면 다른 프로그램의 DB 연결 실패 / 쿼리 지연이 발생한다. 그게 사용자 쿼리라면 서비스 장애다.

클라우드 환경에서 특히 중요

온프레미스 시절엔 8~16코어 표준 사양이라 자원 부족이 드물었다. 클라우드는 다르다.

• db.r5.xlarge = vCPU 4개 → 대용량 정렬 쿼리가 동시에 4개만 돌아도 거의 모든 자원 소모
• db.t3.medium 같은 소형 인스턴스는 튜닝 안 된 쿼리 몇 개만으로 응답 불능
• vCPU 2개인데 CPU 30%까지만 쓸 수 있는 버스터블 인스턴스도 있다

접속하는 DB 서버가 어느 정도 스펙인지, 동시 스레드를 몇 개까지 써도 되는지 확인하는 것이 중요하다.

전략: 배치 시작 시 1~2개 스레드로 시작 → DB 서버 자원 사용량을 보고 늘리거나 줄이도록 프로그램을 설계. 급히 밀린 큐를 처리해야 한다면 모니터링을 붙여 놓고 진행.

장애를 만들기는 쉽지만 원인을 역추적하는 일은 어렵고 시간 소모적이다. DB CPU 급증 장애의 원인을 찾아보면 개발자가 실행한 배치 작업인 경우가 매우 많았다. 배치는 다양하고 빈번해서 매번 공유하기 어려우니, 개발팀이 자체적으로 용량 산정을 하고 속도를 조절해야 한다.

롱 트랜잭션 — Idle vs Active

둘 다 동일한 문제를 유발한다.

왜 문제인가 — 언두 로그와 MVCC

데이터 변경 → 언두 로그에 기록
순수 SELECT → 언두를 이용해 트랜잭션 시작 시점의 데이터를 읽음 (Non-Locking Consistent Read)

• MVCC = Multi Version Concurrency Control. 언두 로그에 저장된 변경 이력으로 필요한 버전을 조회하는 기능
• 정리하면: MySQL은 언두 로그로 MVCC를 구현하고, MVCC로 Non-Locking Consistent Read를 구현했다.
• 언두는 롤백 시 이전 버전 복구에도 쓰인다

공짜 점심은 없다. 언두 로그가 쌓이면 메모리를 많이 쓰고, 넘치면 디스크 읽기/쓰기까지 발생한다. → 주기적인 언두 로그 퍼지 작업이 필요하다.

그런데 퍼지를 방해하는 것이 바로 롱 트랜잭션이다.

MySQL은 실행 중인 액티브 트랜잭션 중 가장 오래된 것보다 이전 트랜잭션의 언두 로그만 제거할 수 있다. 현재 또는 이후 트랜잭션의 언두는 언제든 롤백될 수 있으므로 지울 수 없다.

Aurora MySQL의 함정

Aurora는 Writer와 Reader가 공유 스토리지를 쓰고, 언두 로그도 그 공유 데이터 파일에 기록되기 때문이다.

커뮤니티 버전에서는 "긴 쿼리는 리플리카에서 돌리면 된다"가 통했지만, Aurora에서는 리드 리플리카의 롱 트랜잭션도 위험하다.

정리 — 실무 지침

1. 대용량 처리는 DB 서버 처리 능력을 고려해 속도를 조절하며 실행
2. 빈번하다면 완전 격리된 배치 전용 서버 운영 (대용량 쿼리가 언제든 돌 수 있으니 재처리 로직 필요)
3. AWS라면 커스텀 엔드포인트로 배치 전용 엔드포인트를 분리
4. 단, Aurora 같은 공유 스토리지 환경에서는 배치 전용 서버를 만들어도 롱 트랜잭션은 여전히 Writer에 영향을 준다
5. 오래 걸리는 쿼리는 페이징으로 잘라서 처리 (→ realmysql-part1 Ep4)
6. 대량 변경/삭제는 적당한 건수마다 주기적으로 커밋 → 너무 큰 언두 로그를 만들지 않는다