제가 재직하고 있는 아임웹은 누구나 손쉽게 자신만의 웹사이트를 만들 수 있는 노코드 웹빌더 솔루션을 제공하는 기업입니다.

아임웹의 특정 서비스는 단일 요청에서 수십 개의 테이블에 걸쳐
INSERT / UPDATE / DELETE가 복합적으로 발생하는 대량 트랜잭션을 유발합니다.
이 트랜잭션은 하루에도 빈번하게 반복적으로 실행되며,
대규모 데이터를 보유한 사이트일수록 더욱 잦게 발생합니다.

DBA로서 실시간 모니터링을 진행하던 중, 저는 아래와 같은 패턴을 마주하게 됩니다.

대량의 INSERT 쿼리가 동시에 락에 걸리며,
innodb_lock_wait_timeout에 도달할 때까지 지연되다 자연스럽게 해소된다.

처음 이 패턴을 발견했을 때는 단순히 처리할 레코드 수가 많아 발생하는 지연이라고 생각했습니다.
그러나 동료 DBA의 추측과 MySQL 공식 문서를 교차 검토한 결과,
이는 처리 지연이 아닌 InnoDB의 잠금 메커니즘, 그중에서도 갭 락(Gap Lock) 이 원인임을 알 수 있었습니다.

더 흥미로웠던 점은, 저희 환경이 갭 락이 발생하지 않아야 하는 조건을 갖추고 있었다는 것입니다.

▶ 트랜잭션 격리 수준: READ COMMITTED
▶ 바이너리 로그 포맷: ROW (READ COMMITTED를 복제 환경에서 안전하게 사용하기 위한 전제 조건)

갭 락을 비활성화하는 직접적인 원인은 READ COMMITTED 격리 수준입니다.
ROW 포맷은 갭 락과 직접적인 관련은 없지만, RC를 안전하게 사용하기 위해 함께 설정됩니다.
그런데 RC 환경임에도 왜 갭 락이 발생한 것일까요?

이 글에서는 그 원인을 추적하는 과정과,
READ COMMITTED 환경에서도 갭 락이 사라지지 않는 두 가지 예외를 다룹니다.

▶ Unique Key 가 만드는 잠금 구조
▶ Foreign Key 까지 추가했을 때 어떤 일이 벌어지는가

📚 배경 지식: 넥스트 키 락과 갭 락은 왜 생기는가

이전 글 [생각정리 - 외래 키(foreign key) 그만 알아보자!]에서 FK의 전략적 판단 기준을 정리한 바 있습니다.
이번 글은 그 판단의 기술적 근거가 되는 잠금 메커니즘을 한 단계 더 파고듭니다.

🔒 넥스트 키 락(Next-Key Lock)이란?

InnoDB는 기본적으로 넥스트 키 락(Next-Key Lock) 을 잠금의 기본 단위로 사용합니다.
넥스트 키 락은 두 가지 잠금의 결합입니다.

▶ 레코드 락(Record Lock) : 인덱스 레코드 자체에 걸리는 잠금
▶ 갭 락(Gap Lock) : 인덱스 레코드 바로 앞 구간에 걸리는 잠금

-- 예시: id 컬럼에 10, 20, 30이 존재하는 테이블
-- id = 20에 넥스트 키 락이 걸리면 아래 범위가 잠김

(10, 20]  ← 갭 락(10~20 사이 구간) + 레코드 락(20 레코드)

즉, 넥스트 키 락은 레코드 자체와 그 앞의 구간을 동시에 보호하는 구조입니다.

🤔 REPEATABLE READ에서 갭 락이 필요한 이유

REPEATABLE READ 격리 수준은 팬텀 리드(Phantom Read) 를 방지해야 할 의무가 있습니다.

팬텀 리드란, 동일한 트랜잭션 안에서 같은 범위를 두 번 조회했을 때
처음에는 없던 레코드가 두 번째 조회에서 나타나는 현상입니다.

-- 팬텀 리드 예시

트랜잭션 A: SELECT * FROM t WHERE id BETWEEN 10 AND 20;  → 1건 조회
트랜잭션 B: INSERT INTO t (id) VALUES (15);  COMMIT;
트랜잭션 A: SELECT * FROM t WHERE id BETWEEN 10 AND 20;  → 2건 조회 (팬텀!)

이를 방지하기 위해 InnoDB는 REPEATABLE READ에서
조회 범위 사이의 구간에 갭 락을 설정하여 다른 트랜잭션의 INSERT를 차단합니다.

즉, 갭 락은 REPEATABLE READ가 팬텀 리드를 막기 위해 치르는 비용입니다.

✅ READ COMMITTED에서 갭 락이 사라지는 이유

READ COMMITTED 격리 수준은 팬텀 리드 방지 의무가 없습니다.
각 쿼리가 실행되는 시점의 커밋된 데이터를 읽으면 되기 때문에,
굳이 구간을 잠가둘 필요가 없습니다.

그 결과, InnoDB는 READ COMMITTED에서 갭 락을 비활성화합니다.
잠금은 레코드 자체에만 걸리고, 구간은 열려 있습니다.

📌 참고: STATEMENT 포맷과 REPEATABLE READ

종종 "STATEMENT 포맷이기 때문에 넥스트 키 락이 발생한다"는 설명을 볼 수 있는데, 이는 인과관계가 역전된 표현입니다.
넥스트 키 락의 원인은 REPEATABLE READ 격리 수준이며, STATEMENT 포맷은 그 결과적 산물입니다.
MySQL은 STATEMENT 포맷 + READ COMMITTED 조합에서 복제 안전성 문제가 발생할 수 있어, STATEMENT를 사용하려면 REPEATABLE READ를 유지해야 하고 그 결과로 넥스트 키 락이 수반됩니다.

⚠️ RC + ROW 포맷에서도 갭 락이 사라지지 않는 두 가지 예외

READ COMMITTED 격리 수준에서 갭 락이 비활성화된다는 것은 사실입니다.
그러나 MySQL 공식 문서는 이 원칙에 두 가지 명확한 예외를 명시하고 있습니다.

*"Gap locking is disabled for searches and index scans,* except for foreign-key constraint checking and duplicate-key checking."
📎 MySQL 8.0 Reference Manual - InnoDB Locking

해석하면 아래와 같습니다.

▶ 일반적인 검색과 인덱스 스캔에서는 갭 락이 비활성화됨
▶ 단, FK 제약 검사와 중복 키 감지 시에는 예외적으로 갭 락이 유지됨

하나씩 살펴보겠습니다.

1️⃣ Unique Key — 중복 키 감지

INSERT 실행 시 InnoDB는 해당 컬럼에 Unique Key가 설정되어 있다면
중복 여부를 반드시 확인해야 합니다.

이 중복 감지 과정에서 삽입하려는 키가 존재하지 않으면 Exclusive Record Lock을 직접 획득하고,
중복 키가 발견되면 Shared Next-Key Lock 을 획득합니다.

-- 예시: email 컬럼에 Unique Key가 설정된 테이블

INSERT INTO users (email) VALUES ('test@example.com');

  ├─► [중복 없음] InnoDB: 인덱스 레코드 탐색
  │         └─► Exclusive Record Lock 직접 획득 → INSERT 진행
  │
  └─► [중복 있음] InnoDB: 중복 키 에러 발생
              └─► Shared Next-Key Lock 획득 → 대기 또는 데드락

단, 이 Shared Next-Key Lock이 걸리는 조건에는 한 가지 중요한 전제가 있습니다.

▶ 삽입하려는 레코드가 존재하지 않는 경우 → Exclusive Record Lock 직접 획득 (갭 락 없음) ▶ 삽입하려는 레코드가 이미 존재하는 경우 → 중복 키 에러 발생 → Shared Next-Key Lock 획득

즉, RC 환경에서 UK로 인한 Shared Next-Key Lock은 중복 키 에러가 발생했을 때 걸립니다. 그러나 여러 트랜잭션이 동시에 동일한 키 범위로 INSERT를 시도하는 순간, 이 Shared Next-Key Lock이 데드락의 씨앗이 됩니다.
해당 시나리오는 다음 섹션에서 상세히 다룹니다.

2️⃣ Foreign Key — 참조 무결성 검사

FK가 설정된 테이블에서 INSERT 또는 UPDATE가 발생하면,
InnoDB는 참조 대상인 부모 테이블에 해당 레코드가 존재하는지 검사합니다.

이 과정에서도 동일하게 Shared Next-Key Lock 이 획득됩니다.

-- 예시: orders 테이블이 users 테이블을 FK로 참조

INSERT INTO orders (user_id, product) VALUES (42, 'item_A');

  └─► InnoDB: users 테이블에서 user_id = 42 존재 여부 검사
        └─► users의 해당 인덱스 레코드에 Shared Next-Key Lock 획득
              └─► 존재 확인 후 INSERT 진행

여기서 주목할 점은 child 테이블을 쓸 때 parent 테이블에 락이 걸린다는 것입니다.
이는 FK가 설정되는 순간, 잠금의 영향 범위가 단일 테이블을 넘어
참조 체인 전체로 확장된다는 것을 의미합니다.

📌 두 예외의 공통점

두 예외 모두 Shared Next-Key Lock 이라는 동일한 잠금을 사용합니다.
그리고 이 공유 잠금이 여러 트랜잭션 사이에서 충돌할 때,
READ COMMITTED와 ROW 포맷을 사용하더라도 피할 수 없는 데드락 으로 이어질 수 있습니다.

🔒 Unique Key가 만드는 잠금 구조

📌 정상 케이스: 중복이 없는 경우

단일 트랜잭션에서 UK 컬럼으로 INSERT가 발생하면 아래 순서로 처리됩니다.

INSERT INTO users (email) VALUES ('test@example.com');

Step 1. InnoDB: 'test@example.com' 인덱스 레코드 탐색
Step 2. 중복 없음 확인 → Exclusive Record Lock 직접 획득
Step 3. INSERT 완료 후 커밋 시 잠금 해제

중복이 없는 경우 S-lock 없이 X-lock을 직접 획득하여 INSERT를 처리합니다.
Shared Next-Key Lock은 다음에 설명할 중복 키 충돌이 발생했을 때 등장합니다.

💀 데드락 시나리오: 동시 INSERT

아래 테이블을 기준으로 살펴보겠습니다.

create table users (
    id    int          not null auto_increment
  , email varchar(255) not null
  , primary key (id)
  , unique key uk_email (email)
);

현재 테이블에는 test@example.com 레코드가 존재하지 않는 상태입니다.
세션 A, B, C가 동시에 동일한 email로 INSERT를 시도합니다.

Step 1.
  세션 A: INSERT INTO users (email) VALUES ('test@example.com');
          → 중복 없음 확인 → X-lock 획득 → INSERT (미커밋 상태) ✅

  세션 B: INSERT INTO users (email) VALUES ('test@example.com');
          → 세션 A의 미커밋 레코드로 인해 중복 키 에러 발생
          → Shared Next-Key Lock 요청 → 세션 A의 X-lock 때문에 대기 ⏳

  세션 C: INSERT INTO users (email) VALUES ('test@example.com');
          → 세션 B와 동일하게 Shared Next-Key Lock 대기 ⏳

Step 2.
  세션 A: 롤백 (또는 에러)
          → 세션 B, C: Shared Next-Key Lock 동시 획득 ✅
          (Shared 잠금은 서로 호환되므로 두 세션 모두 획득 가능)

Step 3.
  세션 B: Exclusive Lock 전환 시도
          → 세션 C의 Shared Lock 때문에 대기 ⏳

  세션 C: Exclusive Lock 전환 시도
          → 세션 B의 Shared Lock 때문에 대기 ⏳

Step 4.
  세션 B: 세션 C를 기다리는 중
  세션 C: 세션 B를 기다리는 중
  → 교착 상태 → DEADLOCK 🔴

핵심은 두 가지입니다.
첫째, Shared Next-Key Lock은 중복 키 에러가 발생했을 때 걸립니다.
둘째, Shared Lock은 서로 호환되므로 여러 세션이 동시에 획득할 수 있고,
이후 X-lock으로 전환하려는 순간 서로를 기다리며 데드락이 발생합니다.

⚠️ 실무에서 이 패턴이 위험한 이유

단순히 "같은 email로 두 요청이 동시에 들어오는 경우"만의 문제가 아닙니다.
앞서 설명한 아임웹의 특정 서비스처럼, 단일 트랜잭션 내에서 수십 개의 테이블에 대량 INSERT가 발생하는 구조에서는

▶ 여러 트랜잭션이 같은 테이블의 인접한 키 범위에 동시에 접근하고
▶ 각각이 Shared Next-Key Lock을 획득한 채로 Exclusive Lock 전환을 시도하며
▶ innodb_lock_wait_timeout에 도달할 때까지 대기하다 롤백되는 패턴이 반복됩니다.

이는 데드락으로 즉시 종료되지 않더라도,
수많은 트랜잭션이 동시에 락 대기 상태에 빠지는 것만으로도 서비스에 충분한 영향을 줍니다.

🔗 Foreign Key까지 추가하면 어떻게 되는가

UK만으로도 이미 RC 환경에서 갭 락이 발생한다는 것을 확인했습니다.
여기에 FK까지 추가하면 어떤 일이 벌어질까요?

📌 FK가 만드는 잠금 전파 구조

FK가 설정된 환경에서 child 테이블에 INSERT가 발생하면,
InnoDB는 parent 테이블에 참조 레코드가 존재하는지 검사합니다.
이 과정에서 parent 테이블의 해당 인덱스 레코드에 Shared Next-Key Lock이 걸립니다.

-- 구조: orders(child) → users(parent) FK 참조

INSERT INTO orders (user_id, amount) VALUES (42, 10000);

  └─► InnoDB: users 테이블에서 user_id = 42 존재 여부 검사
        └─► users의 uk_user_id 인덱스에 Shared Next-Key Lock 획득
              └─► 존재 확인 후 orders에 INSERT 진행

중요한 것은 이 잠금이 child를 쓰는 트랜잭션이 parent에 걸어두는 잠금이라는 점입니다.

즉, FK가 설정되는 순간 잠금의 영향 범위가 단일 테이블을 벗어납니다.

💀 데드락 시나리오: parent ↔ child 교차 잠금

아래 구조를 기준으로 살펴보겠습니다. 시나리오는 세션 A와 B가 각각 동일한 트랜잭션 안에서 child INSERT 후 parent UPDATE를 순차적으로 실행하는 상황을 가정합니다.

-- parent 테이블
create table users (
    id    int not null auto_increment
  , email varchar(255) not null
  , primary key (id)
  , unique key uk_email (email)
);

-- child 테이블 (users를 FK로 참조)
create table orders (
    id      int not null auto_increment
  , user_id int not null
  , amount  int not null
  , primary key (id)
  , constraint fk_orders_user foreign key (user_id) references users (id)
);

Step 1.
  세션 A: INSERT INTO orders (user_id, amount) VALUES (42, 10000);
          → users의 id = 42 인덱스에 Shared Next-Key Lock 획득 ✅

  세션 B: INSERT INTO orders (user_id, amount) VALUES (42, 20000);
          → users의 id = 42 인덱스에 Shared Next-Key Lock 획득 ✅
          (Shared 잠금은 서로 호환)

Step 2.
  세션 A: users의 id = 42 레코드를 UPDATE 시도
          → Exclusive Lock 필요
          → 세션 B의 Shared Lock 때문에 대기 ⏳

  세션 B: users의 id = 42 레코드를 UPDATE 시도
          → Exclusive Lock 필요
          → 세션 A의 Shared Lock 때문에 대기 ⏳

Step 3.
  세션 A: 세션 B를 기다리는 중
  세션 B: 세션 A를 기다리는 중
  → DEADLOCK 🔴

UK 데드락은 동일 테이블 내에서 충돌이 완결됩니다.
FK 데드락은 child를 쓰면서 parent에 잠금이 전파되고,
parent를 수정하려는 다른 트랜잭션과 교차하며 충돌합니다.
잠금의 전파 범위 자체가 다릅니다.

📊 UK 단독 vs UK + FK 잠금 구조 비교

FK가 추가될수록 하나의 트랜잭션이 잠금을 획득해야 하는 테이블이 늘어납니다.
참조 체인이 길어질수록 이 구조는 더욱 복잡해집니다.

⚠️ 고QPS 환경에서 이 구조가 위험한 이유

단순히 데드락 발생 가능성의 문제가 아닙니다.

▶ parent 테이블이 핵심 테이블일수록 child의 모든 쓰기가 parent에 공유 잠금을 걸어둠
▶ 동시에 수십 개의 트랜잭션이 같은 parent 레코드를 참조하면 공유 잠금이 누적
▶ parent를 수정하려는 트랜잭션은 모든 공유 잠금이 해제될 때까지 대기
▶ Write QPS가 높을수록 대기 트랜잭션이 쌓이는 속도 > 해소되는 속도

데드락이 발생하지 않더라도,
이 구조에서는 락 경합 누적 자체가 레이턴시 저하로 직결됩니다.

🛠️ 그렇다면 실무에서 어떻게 판단하는가

지금까지의 내용을 정리하면 아래와 같습니다.

▶ READ COMMITTED + ROW 포맷으로 전환해도 갭 락이 완전히 사라지지는 않는다
▶ UK가 있는 테이블에 동시 INSERT가 발생하면 Shared Next-Key Lock 충돌로 데드락이 발생할 수 있다
▶ FK까지 추가하면 잠금이 parent 테이블로 전파되며 데드락 패턴이 더 복잡해진다

그렇다면 UK와 FK 각각에 대해 어떻게 판단해야 할까요?

📌 Unique Key에 대한 판단

UK는 데이터 무결성의 핵심 제약조건입니다.
갭 락 예외가 발생한다는 이유만으로 UK를 제거하는 것은 현실적인 선택이 아닙니다.

대신 아래 방향으로 접근하는 것이 실무적입니다.

근본적 해결 방안

▶ 트랜잭션을 짧고 단순하게 유지한다
Shared Next-Key Lock의 보유 시간을 최소화하는 것이 핵심입니다.
트랜잭션이 길어질수록 잠금 충돌 가능성이 높아집니다.

▶ 동일한 UK 범위로의 동시 INSERT 가능성을 애플리케이션 레벨에서 줄인다
요청 직렬화, 분산 락 등을 통해 동일 키로의 동시 접근 자체를 줄이는 것이 근본적인 해결책입니다.

▶ 데드락 발생 시 재시도 로직을 구현한다
UK 충돌로 인한 데드락은 일시적인 경합에서 비롯되는 경우가 많습니다.
애플리케이션 레벨의 재시도 로직으로 상당 부분 흡수할 수 있습니다.

단기 적용 가능 방안

▶ innodb_lock_wait_timeout 조정을 검토한다
위 세 가지를 단기간에 적용하기 어려운 상황이라면,
innodb_lock_wait_timeout 값을 낮춰 대기 트랜잭션이 빠르게 실패하도록 유도하는 방법을 검토할 수 있습니다.

-- 기본값: 50초
-- 세션 레벨 조정 예시
set session innodb_lock_wait_timeout = 5;

-- 전역 레벨 조정 예시
set global innodb_lock_wait_timeout = 5;

대기 시간을 줄이면 락이 누적되는 속도를 억제할 수 있습니다.
그러나 이 방안은 단독으로는 의미가 없으며, 반드시 아래 두 가지 조건이 충족되어야 합니다.

▶ 전제 조건: 빠른 실패는 빠른 에러입니다. 애플리케이션 레벨의 재시도 로직이 없으면 에러율만 높아집니다.
▶ 한계: innodb_lock_wait_timeout은 전역 설정입니다. 문제가 되는 트랜잭션만 선택적으로 적용할 수 없으며,
값을 낮출수록 정상적인 장시간 트랜잭션도 영향을 받으므로 충분한 테스트 후 적용해야 합니다.

📌 Foreign Key에 대한 판단

FK는 UK와 다르게 DB 레벨 설정을 대체할 수 있는 수단이 존재합니다.

고QPS 환경에서 FK를 DB 레벨에서 제거하기로 결정했다면,
참조 무결성은 아래 방식으로 대체할 수 있습니다.

▶ 애플리케이션 레벨 보장 — BE에서 참조 무결성 검사 로직을 직접 구현
▶ 주기적 배치 탐지 — orphan row를 감지하는 쿼리를 정기적으로 실행해 정합성 모니터링
▶ ERD 문서화 — 논리적 관계는 ERD에 명시해 개발자가 참조 관계를 인지할 수 있도록 유지

DB가 무결성을 강제하지 않는다고 해서 무결성 관리를 포기하는 것이 아닙니다.
책임의 위치를 DB에서 애플리케이션으로 이동하는 것입니다.

🎯 마치며

이 글을 한 줄로 요약하면 아래와 같습니다.

"갭 락은 READ COMMITTED로 없앨 수 있다 — 이 말은 절반만 맞습니다."

READ COMMITTED와 ROW 포맷은 분명히 대부분의 갭 락을 제거합니다.
그러나 Unique Key의 중복 감지와 Foreign Key의 참조 무결성 검사는 MySQL 공식 문서가 명시한 예외이며, 격리 수준과 무관하게 동작합니다.

DBA로서 이 예외를 인지하고 있는 것과 그렇지 않은 것은
장애 상황에서 원인을 찾는 속도에 큰 차이를 만들어냅니다.

💡 핵심 포인트

▶ 넥스트 키 락 = 레코드 락 + 갭 락, InnoDB의 기본 잠금 단위
▶ 갭 락의 원인은 격리 수준(REPEATABLE READ)이며, STATEMENT 포맷은 결과적 산물
▶ READ COMMITTED에서도 UK 중복 감지, FK 참조 검사 시에는 Shared Next-Key Lock 발생
▶ UK 데드락은 단일 테이블 내에서, FK 데드락은 parent 테이블로 전파되어 발생
▶ 고QPS 환경에서는 데드락이 없더라도 락 경합 누적 자체가 레이턴시 저하로 직결

제가 재직하고 있는 아임웹은 노코드 웹빌더 솔루션을 제공하는 기업으로, 디자인모드라는 기능을 통해 사용자가 자신만의 웹 페이지를 직접 제작할 수 있습니다.

디자인모드는 아임웹의 근간이자, 지금의 아임웹이라는 이름을 만들어온 핵심 기능입니다. 그러나 그만큼 오래된 기능이기도 하고, 소위 말하는 레거시 코드와 구성으로 구현되어 있습니다.

최근 이 기능을 더욱 안정적으로 서비스하기 위해 모던 스택으로 전환하는 과정에서, 각 디자인 데이터를 버전별로 관리하고 제어할 수 있도록 아래와 같은 DDL 작업이 필요했습니다.

▶ 신규 컬럼 추가 ▶ 기존 유니크 제약 삭제 ▶ 신규 유니크 제약 생성

문제는 디자인모드를 구성하는 수많은 테이블 중에는 수억 건의 데이터가 존재하는 테이블부터 수천만 건 규모의 테이블까지 다양하게 분포해 있다는 점이었습니다.

대규모 테이블의 스키마를 변경하는 일은, 수백만 명이 건너고 있는 다리의 교각을 교체하는 것과 같습니다. 다리를 닫을 수 없고, 사람들은 계속 건너야 하며, 공사는 어떻게든 완료해야 합니다.

이번 글에서는 이 고민의 출발점이 된 두 가지 선택지를 소개합니다.

▶ MySQL 8이 지원하는 Online DDL 알고리즘 (INSTANT / INPLACE / COPY) 과 각 알고리즘의 동작 방식 ▶ pt-osc(pt-online-schema-change) 가 무엇인지, 그리고 어떤 원리로 동작하는지

동작 원리부터 실무 판단 기준까지, 하나의 글로 정리합니다. 🚀

📚 배경 지식: DDL 변경이 왜 위험한가

🔒 MDL(Metadata Lock)이란?

MySQL은 테이블 구조를 변경하는 DDL 작업을 수행할 때, 해당 테이블에 MDL(Metadata Lock) 을 획득합니다. MDL은 테이블의 메타데이터(스키마 정보)를 보호하기 위한 잠금으로, DDL 작업 중 다른 트랜잭션이 테이블 구조를 동시에 변경하거나 읽는 것을 제어합니다.

MDL은 크게 두 가지 모드로 동작합니다.

▶ Shared MDL : 일반적인 DML(SELECT, INSERT, UPDATE, DELETE) 수행 시 획득 ▶ Exclusive MDL : DDL 수행 시 획득, 다른 모든 접근을 차단

⚠️ 왜 문제가 되는가?

여기서 핵심은 MDL의 획득 순서와 대기 방식입니다. 아래 시나리오를 살펴보겠습니다.

시나리오: 대용량 테이블에 ALTER TABLE 실행

① 트랜잭션 A (기존 DML): SELECT * FROM big_table WHERE ...  → Shared MDL 보유 중
② ALTER TABLE big_table ADD COLUMN ...                       → Exclusive MDL 대기 중
③ 트랜잭션 C (신규 DML): SELECT * FROM big_table WHERE ...  → Shared MDL 대기 중 (②번 뒤에서 줄 서기)
④ 트랜잭션 D, E, F ...                                      → 동일하게 대기 적체

① 트랜잭션 A가 끝나지 않으면 ALTER는 Exclusive MDL을 획득하지 못합니다. ② ALTER가 Exclusive MDL을 기다리는 동안, 이후 모든 DML 요청은 그 뒤에서 대기합니다. ③ 결과적으로 커넥션이 빠르게 고갈되어 서비스 장애로 이어집니다.

즉, ALTER TABLE 하나가 테이블 전체의 트래픽을 막는 댐이 되어버리는 것입니다.

📉 서비스 영향도

대규모 트래픽 환경에서 이 문제가 발생하면 아래와 같은 연쇄 반응이 일어납니다.

▶ 쿼리 적체: 대기 중인 쿼리가 급격히 쌓임 ▶ 커넥션 고갈: DB 커넥션 풀이 가득 차 신규 요청 처리 불가 ▶ 애플리케이션 타임아웃: 응답 지연 → 사용자 오류 화면

💡 그래서 Online DDL과 pt-osc가 등장했다

위와 같은 문제를 해결하기 위해 두 가지 접근 방식이 존재합니다.

▶ MySQL Online DDL: MySQL 엔진 자체에서 잠금을 최소화하며 DDL을 수행하는 방식 ▶ pt-osc: Percona에서 개발한 툴로, 잠금 없이 Shadow Table을 이용해 스키마를 변경하는 방식

두 방식 모두 "서비스를 멈추지 않고 스키마를 변경한다" 는 목표를 가지고 있지만, 그 내부 동작 방식과 각각의 한계점은 분명히 다릅니다.

지금부터 하나씩 살펴보겠습니다.

⚙️ MySQL 8 Online DDL 동작 원리

MySQL은 DDL 작업 시 어떻게 변경할 것인가(ALGORITHM) 와 어느 수준으로 잠글 것인가(LOCK) 를 옵션으로 제어할 수 있습니다.

ALTER TABLE big_table
  ADD COLUMN version INT NOT NULL DEFAULT 0,
  ALGORITHM = INPLACE,
  LOCK = NONE;

📌 ALGORITHM 옵션

① INSTANT

MySQL 8.0에서 새롭게 도입된 알고리즘으로, 테이블 데이터를 건드리지 않고 메타데이터만 변경합니다.

▶ 실제 데이터 파일을 수정하지 않음 ▶ 작업 시간이 데이터 양과 무관하게 거의 즉시 완료 ▶ DDL 수행 중 DML 차단 없음

-- INSTANT 지원 예시
ALTER TABLE big_table ADD COLUMN memo TEXT, ALGORITHM = INSTANT;

⚠️ 단, 지원되는 작업이 제한적입니다. 컬럼 추가(8.0.29 이전은 마지막 위치만 가능), 기본값 변경 등 일부 작업만 해당됩니다.

② INPLACE

테이블을 통째로 복사하지 않고 제자리에서 변경하는 방식입니다. MySQL Online DDL의 핵심 알고리즘이며, 작업 중에도 DML이 허용됩니다.

▶ 데이터 파일을 직접 수정하여 작업 완료 ▶ 작업 중 발생한 DML 변경사항은 온라인 로그(Online Log)에 임시 기록 후 반영 ▶ DDL 시작/완료 시점에 짧은 Exclusive MDL 구간 존재

ALTER TABLE big_table
  ADD INDEX idx_created_at (created_at),
  ALGORITHM = INPLACE,
  LOCK = NONE;

③ COPY

가장 오래된 방식으로, 임시 테이블을 생성 후 데이터를 전체 복사합니다.

▶ 원본 테이블의 모든 데이터를 새 임시 테이블에 복사 ▶ 작업 중 DML 불가 (테이블 전체 잠금) ▶ 디스크 여유 공간이 원본 테이블 크기만큼 추가 필요 ▶ INSTANT, INPLACE가 지원되지 않는 작업에 fallback으로 사용됨

⚠️ 수억 건 테이블에 COPY가 선택된다면 그 시간만큼 서비스가 멈출 수 있습니다.

📌 LOCK 옵션

▶ ALGORITHM과 LOCK은 조합이 가능한 모든 경우가 허용되지는 않습니다. ▶ 예: ALGORITHM=INSTANT는 LOCK=DEFAULT만 허용되며, 다른 값 지정 시 에러 발생 ▶ 예: ALGORITHM=COPY는 LOCK=NONE 지정 불가 (에러 발생)

📌 INPLACE 내부 동작 흐름

INPLACE 알고리즘은 내부적으로 3단계 페이즈로 동작합니다.

Phase 1. Prepare ▶ Exclusive MDL 획득 (짧은 순간) ▶ 내부 임시 구조 및 온라인 로그 공간 초기화 ▶ Exclusive MDL 해제 → DML 재개

Phase 2. Execute ▶ 실제 DDL 작업 수행 (인덱스 재구성, 컬럼 추가 등) ▶ 이 구간 중 발생한 DML은 온라인 로그에 임시 기록 ▶ DML 허용 상태 유지 (LOCK=NONE 기준)

Phase 3. Commit ▶ Exclusive MDL 재획득 (짧은 순간) ▶ 온라인 로그에 쌓인 DML 변경사항 반영 ▶ 메타데이터 최종 업데이트 후 MDL 해제

핵심은 Phase 1과 Phase 3의 Exclusive MDL 구간이 매우 짧다는 점입니다. 하지만 동시성이 높은 환경에서는 이 짧은 순간도 문제가 될 수 있습니다. (2편에서 다룰 예정 🚨)

⚠️ Online DDL의 한계점

INSTANT가 지원되지 않는 케이스 ▶ 컬럼을 중간 위치에 추가하는 경우 (8.0.29 이전) ▶ 컬럼 타입 변경 ▶ PK 변경 또는 재정의 ▶ 외래 키(FK) 추가/삭제 (일부 케이스)

INPLACE 중 발생하는 잠금 구간 ▶ Prepare / Commit 페이즈에서 짧지만 Exclusive MDL이 존재 ▶ 높은 동시성 환경에서는 이 구간이 MDL 대기 적체로 이어질 수 있음 ▶ 온라인 로그 크기 초과 시(innodb_online_alter_log_max_size) 작업 실패

작업 중단 시 재시작 불가 ▶ 작업이 중단되면 처음부터 다시 시작해야 함 ▶ 수억 건 테이블에서 중단 발생 시 매우 치명적

🔧 pt-osc(pt-online-schema-change) 동작 원리

📌 pt-osc란?

pt-osc는 Percona에서 개발한 오픈소스 툴로, MySQL의 Online DDL이 가진 한계를 보완하기 위해 만들어진 무중단 스키마 변경 도구입니다.

핵심 아이디어는 단순합니다.

"원본 테이블을 직접 건드리지 않고, Shadow Table(그림자 테이블)을 만들어 데이터를 옮긴 뒤 테이블을 교체한다"

📌 동작 흐름 단계별 설명

Step 1. Shadow Table 생성

▶ 원본 테이블과 동일한 구조의 _테이블명_new 테이블 생성 ▶ 이 시점에 변경하고자 하는 DDL도 함께 적용

-- 원본: big_table
-- 생성: _big_table_new (+ 변경된 스키마 적용)

Step 2. Trigger 3종 세트 생성

▶ 원본 테이블에 INSERT / UPDATE / DELETE Trigger를 각각 생성 ▶ 데이터 복사가 진행되는 동안 원본에 발생하는 DML 변경사항을 _new 테이블에 실시간으로 반영하기 위함

-- 원본 테이블에 INSERT 발생 시 → _new 테이블에도 INSERT
-- 원본 테이블에 UPDATE 발생 시 → _new 테이블에도 UPDATE
-- 원본 테이블에 DELETE 발생 시 → _new 테이블에도 DELETE

Step 3. Chunk 단위 데이터 복사

▶ 원본 테이블의 데이터를 chunk 단위로 나누어 _new 테이블에 복사 ▶ 한 번에 전체를 복사하지 않기 때문에 서비스 부하를 분산할 수 있음 ▶ --chunk-size 옵션으로 한 번에 복사할 행 수 조절 가능 ▶ --max-load, --critical-load 옵션으로 DB 부하 수준에 따라 작업 속도 자동 조절 또는 중단 가능

-- chunk 단위 복사 예시 (내부 동작)
INSERT INTO _big_table_new
SELECT *
  FROM big_table
 WHERE id BETWEEN 1 AND 1000;  -- chunk-size: 1000

Step 4. 원자적 테이블 교체 (RENAME)

▶ 데이터 복사가 완료되면 단 하나의 쿼리로 원본과 신규 테이블을 교체 ▶ RENAME TABLE은 원자적(Atomic)으로 동작하기 때문에 순간적인 잠금만 발생

RENAME TABLE big_table TO _big_table_old,
             _big_table_new TO big_table;

Step 5. Trigger 및 구 테이블 정리

▶ 작업에 사용된 Trigger 3종 제거 ▶ _big_table_old (구 원본 테이블) 삭제 ▶ 작업 완료 🎉

📌 주요 파라미터

⚠️ pt-osc의 한계점

Trigger 오버헤드 ▶ 데이터 복사 중 원본 테이블에 발생하는 모든 DML이 Trigger를 통해 이중으로 처리됨 ▶ 트래픽이 높은 환경에서는 Trigger 자체가 부하 요인이 될 수 있음

FK(Foreign Key) 제약 처리 이슈 ▶ FK가 걸린 테이블은 별도의 옵션(--alter-foreign-keys-method) 처리가 필요 ▶ 잘못 설정 시 FK 정합성 문제 발생 가능

Trigger를 지원하지 않는 환경 ▶ 이미 해당 테이블에 동일한 이벤트의 Trigger가 존재하면 pt-osc 사용 불가 ▶ MySQL은 동일 이벤트에 대해 여러 Trigger를 지원하지 않기 때문

작업 중단 시 수동 정리 필요 ▶ 작업이 비정상 종료되면 _new 테이블, Trigger가 남아있을 수 있음 ▶ 재시작 전 수동으로 잔여물 정리 필요

🆚 두 방식 직접 비교

두 방식을 한눈에 비교하면 아래와 같습니다.

핵심 요약

▶ Online DDL — MySQL이 직접 처리하므로 간편하지만, 높은 동시성 환경에서 MDL 대기 적체 위험 존재

▶ pt-osc — 부하 제어와 모니터링이 유연하지만, Trigger 오버헤드와 추가 디스크 공간이 필요

🎯 실무에서 어떻게 선택하는가

모든 상황에 맞는 정답은 없습니다. 아래 기준을 순서대로 따라가면 대부분의 케이스에서 올바른 선택을 할 수 있습니다.

📌 의사결정 흐름

Step 1. INSTANT DDL이 가능한가?

▶ 가능하다면 고민 없이 선택합니다. ▶ 메타데이터만 변경하므로 데이터 양과 무관하게 즉시 완료됩니다. ▶ 단, 지원 여부를 반드시 사전에 확인해야 합니다.

-- 사전 확인: INSTANT 지원 여부 체크
ALTER TABLE target_table
  ADD COLUMN new_col INT NOT NULL DEFAULT 0,
  ALGORITHM = INSTANT;
-- ERROR 발생 시 → INSTANT 미지원, 다음 단계로

⚠️ INSTANT DDL이 지원되지 않는 케이스

MySQL 공식 문서(dev.mysql.com)를 기준으로, 아래 케이스에서는 INSTANT 알고리즘을 사용할 수 없습니다.

① ROW_FORMAT=COMPRESSED 테이블 ▶ 압축 테이블은 INSTANT 알고리즘 적용 불가 ▶ INPLACE 또는 COPY 알고리즘 사용 필요

② FULLTEXT 인덱스가 존재하는 테이블 ▶ FULLTEXT 인덱스가 있는 테이블에는 INSTANT로 컬럼 추가 불가

③ 임시 테이블 (TEMPORARY TABLE) ▶ 임시 테이블은 ALGORITHM=COPY만 지원

④ 컬럼 타입 변경 ▶ 컬럼의 데이터 타입을 변경하는 작업은 INSTANT 미지원 ▶ INPLACE (일부) 또는 COPY 필요

⑤ PK(Primary Key) 추가 / 삭제 / 변경 ▶ 클러스터드 인덱스 재구성이 수반되므로 INSTANT 미지원 ▶ 반드시 INPLACE 또는 COPY 사용

⑥ FK(Foreign Key) 추가 / 삭제 ▶ 외래 키 관련 작업은 INSTANT 미지원 ▶ foreign_key_checks = OFF 상태에서 INPLACE 가능

⑦ 컬럼 중간 위치 삽입 (8.0.29 이전) ▶ MySQL 8.0.29 이전 버전에서는 INSTANT로 컬럼을 추가할 때 반드시 테이블의 맨 끝에만 추가 가능 ▶ MySQL 8.0.29부터는 임의의 위치에 컬럼 추가 가능

-- 8.0.29 이전: 중간 위치 컬럼 추가 시 INSTANT 불가 → INPLACE 또는 COPY 필요
ALTER TABLE target_table
  ADD COLUMN new_col INT NOT NULL DEFAULT 0 AFTER existing_col,
  ALGORITHM = INSTANT;
-- ERROR: ALGORITHM=INSTANT is not supported (8.0.29 이전 버전)

-- 8.0.29 이후: 임의의 위치에도 INSTANT 가능
ALTER TABLE target_table
  ADD COLUMN new_col INT NOT NULL DEFAULT 0 AFTER existing_col,
  ALGORITHM = INSTANT;
-- OK

⑧ INSTANT 변경 횟수 64회 초과 ▶ INSTANT로 ADD COLUMN / DROP COLUMN을 수행할 수 있는 횟수는 테이블당 최대 64회로 제한 ▶ 64회 초과 시 아래 에러 발생, 테이블 rebuild(INPLACE 또는 COPY) 필요

ERROR 4092 (HY000): Maximum row versions reached for table test/t1.
No more columns can be added or dropped instantly.
Please use COPY/INPLACE.

▶ 현재 남은 횟수는 INFORMATION_SCHEMA로 확인 가능

SELECT NAME, TOTAL_ROW_VERSIONS
  FROM information_schema.INNODB_TABLES
 WHERE NAME = 'your_database/your_table';

⑨ 컬럼명 변경 (8.0.28 이전) ▶ 컬럼명 변경(RENAME COLUMN)은 MySQL 8.0.28부터 INSTANT 지원 ▶ 8.0.28 이전 버전에서는 INPLACE 또는 COPY 필요

⑩ 행 크기(Row Size) 제한 초과 가능성 ▶ INSTANT로 컬럼 추가 시 최대 행 크기를 초과하면 INSTANT 거부

ERROR 4092 (HY000): Column can't be added with ALGORITHM=INSTANT
as after this max possible row size crosses max permissible row size.

📎 참고: MySQL 8.0 Reference Manual - Online DDL Operations

Step 2. 테이블 규모와 트래픽을 확인한다

Step 3. FK 또는 기존 Trigger 존재 여부를 확인한다

▶ FK 존재 → Online DDL은 네이티브 지원, pt-osc는 --alter-foreign-keys-method 별도 설정 필요 ▶ 동일 이벤트 Trigger 존재 → pt-osc 사용 불가, Online DDL만 선택 가능

Step 4. 작업 중단 가능성을 고려한다

▶ 작업 도중 중단 가능성이 있는 환경이라면 pt-osc 선택 ▶ Online DDL은 중단 시 처음부터 재시작해야 하므로, 수억 건 테이블에서는 치명적

📌 저의 실무 판단 기준

아임웹 환경(Aurora MySQL, 높은 QPS)에서는 아래 기준을 따르고 있습니다.

▶ INSTANT 가능 → 무조건 INSTANT ▶ 수천만 건 이상 + 피크 트래픽 테이블 → pt-osc ▶ 수천만 건 이상 + 트래픽 낮은 테이블 → INPLACE, 단 MDL 대기 모니터링 병행 ▶ 수백만 건 이하 → INPLACE (트래픽이 몰리는 13~17시는 되도록 피함)

⚠️ Online DDL을 선택했더라도 작업 전 반드시 SHOW PROCESSLIST와 롱 트랜잭션 여부를 확인해야 합니다. 롱 트랜잭션이 존재하는 상태에서 DDL을 실행하면 MDL 대기가 발생하고, 이것이 서비스 장애로 이어질 수 있습니다. 서버에 직접 접속해 수동으로 확인하는 것은 번거롭고 놓치기 쉽습니다. Dolphin(돌핀) 또는 innotop 과 같은 모니터링 솔루션을 활용하면 실시간으로 프로세스와 트랜잭션 상태를 한눈에 파악할 수 있어, 작업 자체에 더욱 집중할 수 있습니다. 이 이야기는 다음 편에서 자세히 다룹니다. 🚨

💻 실제 사용 예시

📌 Online DDL 커맨드 예시

-- ① INSTANT: 컬럼 추가 (가장 빠름)
ALTER TABLE design_data
  ADD COLUMN version INT NOT NULL DEFAULT 0,
  ALGORITHM = INSTANT;

-- ② INPLACE: 인덱스 추가 (DML 허용)
ALTER TABLE design_data
  ADD INDEX ix_version (version),
  ALGORITHM = INPLACE,
  LOCK = NONE;

-- ③ INPLACE: 유니크 제약 삭제 + 신규 유니크 제약 생성
ALTER TABLE design_data
  DROP INDEX uk_old_key,
  ADD UNIQUE INDEX uk_code_version (code, version),
  ALGORITHM = INPLACE,
  LOCK = NONE;

⚠️ ALGORITHM을 명시하지 않으면 MySQL이 자동으로 선택합니다. 운영 환경에서는 반드시 명시하여 예상치 못한 COPY 알고리즘 선택을 방지해야 합니다.

📌 pt-osc 커맨드 예시

pt-online-schema-change \
  --host=your-aurora-endpoint \
  --port=3306 \
  --user=dba_user \
  --ask-pass \
  --database=your_database \
  --table=design_data \
  --alter="ADD COLUMN version INT NOT NULL DEFAULT 0,
            DROP INDEX uq_old_key,
            ADD UNIQUE INDEX uk_code_version (code, version)" \
  --chunk-size=1000 \
  --chunk-time=0.45 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=50" \
  --pause-file=/tmp/pt-osc.pause \
  --no-drop-old-table \
  --print \
  --execute

주요 옵션 설명

▶ --no-drop-old-table : 작업 완료 후 구 테이블을 바로 삭제하지 않고 보존 (롤백 대비) ▶ --print : 실행 중 상세 로그 출력 ▶ --ask-pass : 비밀번호를 커맨드에 노출하지 않고 입력 프롬프트로 처리

💡 --no-drop-old-table 옵션은 작업 완료 후 _design_data_old 형태로 구 테이블이 남습니다. 서비스 이상이 없음을 확인한 후 직접 삭제하는 것을 권장합니다.

🎯 마치며

대규모 테이블의 스키마를 변경하는 일은 단순히 ALTER TABLE 한 줄로 끝나는 작업이 아닙니다. 테이블의 크기, 트래픽 패턴, 작업의 종류, 환경의 특성까지 종합적으로 고려해야 하는 의사결정의 연속입니다.

이번 글에서 다룬 내용을 한 줄로 요약하면 아래와 같습니다.

▶ INSTANT가 되면 → 고민 없이 INSTANT ▶ INSTANT가 안 되면 → 테이블 규모와 트래픽을 보고 INPLACE와 pt-osc 중 선택 ▶ 어떤 방식이든 → 작업 전 롱 트랜잭션 확인은 필수

이론은 언제나 깔끔합니다. 하지만 실제 운영 환경에서는 이론대로 흘러가지 않는 순간이 반드시 찾아옵니다.

다음 편에서는 Online DDL을 믿었다가 서비스 장애로 이어진 실제 경험담과, 그 수습 과정에서 pt-osc와 약 3일을 싸웠던 이야기를 솔직하게 기록할 예정입니다. 🚨

이번에는 기존에 작성했던 쿼리 튜닝 게시글과 다르게 아래 문제를 바탕으로 작성된 쿼리를 튜닝해보는 시간을 가져보려 합니다.

테이블은 총 2개이며, 각 테이블의 정보는 다음과 같습니다.

📌 테이블 구성

작업목록

▶ 테이블의 총 레코드는 1,000만 건 ▶ 작업구분코드의 분포도는 아래를 참고 - AAL: 40% - CCL: 55% - CML: 5% ▶ 인덱스 구성 - 작업목록_PK: 작업ID - 작업목록_X1: 작업구분코드

작업실행로그

▶ 하루 평균 1만 개의 작업실행로그 생성 ▶ 총 레코드는 1억 5,000만 건이며, 최초 생성 이후 별도의 데이터 삭제는 없었음 ▶ 작업목록 테이블에 등록된 작업의 실행로그만 관리하며, 작업ID는 Not Null ▶ 인덱스 구성 - 작업실행로그_PK: 작업로그ID - 작업실행로그_X1: 작업ID - 작업실행로그_X2: 작업일시

❓ 실전 문제

Q1. 2025년 05월 01일자에 실행된 작업중 작업구분코드가 'CCL'인 작업의 작업명, 등록자, 마지막 작업일시를 구하는 쿼리를 작성하시오.

select c.작업명, c.등록자, a.max_time as 마지막작업시간
  from (
        select 작업ID
             , MAX(작업일시) as max_time
         from 작업실행로그
        group by 작업ID
       ) a
  join 작업실행로그 b on a.작업ID = b.작업ID 
                   and a.max_time = b.작업일시
  join 작업목록 c on a.작업ID = c.작업ID
 where c.작업구분코드 = 'CCL'
   and DATE_FORMAT(b.작업일시, '%y-%m-%d') = '2025-05-01'
;

😭 아쉬운 점

위와 같은 문제를 작성한 쿼리를 보았을 때 여러분은 어떤 생각이 드시나요? 제가 가장 먼저 든 생각은 과연 결과값이 나올 수 있을까? 였습니다. 물론 하염없이 기다리다 보면 결과는 나오겠지만, 수행시간에 초점을 둔다면 "No" 입니다.

제가 생각했을 때 위의 쿼리 문제점은 아래와 같이 나열해 볼 수 있을 것 같습니다.

1️⃣ 인라인뷰를 사용한 불필요한 GROUP BY ▶ 작성자가 문제를 너무 복잡하게 생각한 탓일까요? 단순하게 작업실행로그와 작업목록 테이블간의 조인만으로 해결할 수 있어 보이는데, 작성자의 경우 작업실행로그 테이블의 모든 작업ID의 마지막 작업일시를 구했습니다. 물론 이렇게 모든 작업ID와 각 작업의 마지막 작업일시를 미리 구한다면 작업목록에서 데이터를 빠르게 가져올 수 있다고 생각했을 수 있겠습니다. 다만, 작성자가 작업실행로그 테이블에 얼마나 많은 작업ID가 존재하는지 파악하지 못한게 조금 아쉽다고 볼 수 있을 것 같습니다. 집계 함수의 경우 정렬, 조인등의 작업보다는 리소스가 적게 발생하기 때문에 여기서의 문제점은 없겠으나 작업목록 테이블이 1,000만 건이라는 것을 생각하면 확실히 좋은 방법은 아니겠죠?
2️⃣ 불필요한 JOIN ▶ 앞서 인라인뷰를 통해 각각의 작업ID별 마지막 작업일시를 구했는데도 불필요하게 작업실행로그 테이블과 다시 한번 조인을 하도록 작성되어 있습니다. 인라인뷰에서는 1,000만 건의 데이터를 가지고 있으므로 수많은 데이터와 조인을 시도하게 될텐데요, NL 조인만을 지원하는 MySQL 의 경우 다음과 같은 조인 방식은 엄청난 리소스를 사용하게 됩니다.
3️⃣ 인덱스 컬럼 가공 ▷ 제가 엔지니어들에게 가장 많이 강조하는 것 중 하나가 바로 "인덱스 컬럼을 가공하지 마세요." 입니다. 많은 엔지니어들이 DATE형 컬럼에 작성의 편의성을 위해 DATE_FORMAT()과 같은 함수로 가공하여 작성합니다. ex) DATE_FORMAT(작업일시, '%y-%m-%d') = '2001-01-19' 그러나 이와 같이 작성하게 되면 인덱스를 사용할 수 없게 되는 문제가 발생합니다. 위 쿼리에서도 [작업일시] 구성의 작업실행로그_X2 인덱스가 존재하지만 조건절 컬럼을 가공했기 때문에 효과적으로 인덱스를 사용하지 못하게 됩니다.

그러면 위와 같은 문제를 풀기 위해 효율적인 SQL문은 어떻게 작성해야 할까요? 저는 아래와 같이 작성하여 위 문제들을 해결해 보았습니다.

✅ 최적화된 쿼리

select b.작업명
     , b.등록자
     , max(a.작업일시) as 마지막작업시간
  from 작업실행로그 a
  join 작업목록 b on a.작업ID = b.작업ID
 where a.작업일시 >= '2025-05-01'
   and a.작업일시  < '2025-05-02'
   and b.작업구분코드 = 'CCL'
 group by b.작업명
        , b.등록자
;

📈 개선 효과

위와 같이 쿼리를 작성하게 되면 작업실행로그 테이블에서는 [작업실행로그_X2] 인덱스를 사용해 해당 기간 동안의 데이터를 빠르게 Range Scan 할 수 있게 되고, 작업목록 테이블에서는 [작업목록_PK] 인덱스를 사용해 데이터를 빠르게 가져와 작업구분코드가 'CCL'인 데이터를 필터링해 데이터를 처리할 수 있게 됩니다.

위와 같이 쿼리를 작성하게 되면 작업실행로그 테이블에서는 [작업실행로그_X2] 인덱스를 사용해 해당 기간 동안의 데이터를 빠르게 Range Scan 할 수 있게 되고,

작업목록 테이블에서는 [작업목록_PK] 인덱스를 사용해 데이터를 빠르게 가져와 작업구분코드가 'CCL' 인 데이터를 필터링해 데이터를 처리할 수 있게 됩니다.

💬 Q&A

Q. 왜 작업구분코드로 구성된 작업목록_X1 인덱스를 사용하지 않고 작업목록_PK 인덱스를 사용했나요?

A. 작업목록에서 작업구분코드가 'CCL'인 데이터의 분포도는 약 55% 입니다. 이 때문에 옵티마이저는 작업목록_X1 인덱스가 아닌 작업목록_PK 인덱스를 사용했을 가능성이 높겠네요.

💡 핵심 포인트

▶ 인덱스 컬럼은 절대 가공하지 않기 - 함수 사용 시 인덱스 활용 불가 ▶ 필요한 데이터만 먼저 필터링 - 전체 데이터 집계 지양 ▶ 불필요한 서브쿼리/인라인뷰 제거 - 쿼리 구조 단순화 ▶ 데이터 분포도 고려 - 인덱스 선택에 영향

오늘의 주제는 “EXISTS 활용하기” 입니다.

현재 근무하고 있는 직장에서는 "쿼리문 검수 및 튜닝 업무"를 수행하고 있습니다. 서비스의 규모가 커지면 기존의 애플리케이션단에서 사용하던 쿼리는 점점 느려지고, 이는 서버 부하 및 고객에게 부정적인 경험으로 연결됩니다.

엔지니어가 작성한 쿼리문을 확인하면 대부분의 경우 JOIN 또는 WHERE 절에 IN 연산자를 사용하여 서브 쿼리를 작성하는 것을 볼 수 있었습니다. 다른 테이블의 데이터를 참조하여 조회할 때는 다양한 방법을 사용할 수 있지만, 조회하고자 하는 테이블의 데이터 양과 분포에 따라 최적화 방법이 달라집니다.

📚 기본 예제로 EXISTS 이해하기

예제 테이블

테이블: emp

테이블: dept

  ### EXISTS 예제 쿼리
  select a.*
    from test.emp a
   where exists (
                 select 1
                   from test.dept b
                  where a.deptno = b.deptno
                    and b.deptno = 10
                )
  ;

-- 결과: 2건
-- 101 SANGWON 1996-05-23 10
-- 102 HYOJU   1996-09-11 10

💼 실전 시나리오: 주문-상품 데이터 분석

다음과 같은 테이블 구조를 가정해보겠습니다.

📊 테이블 구성

▶ 테이블명: 상품 ▶ 데이터: 10,000건

CREATE TABLE 상품 (
    item_id INT NOT NULL AUTO_INCREMENT,
    item_code VARCHAR(20) NOT NULL,
    create_date DATETIME NOT NULL DEFAULT NOW(),
    ...
    PRIMARY KEY (item_id),
    UNIQUE (item_code),
    KEY (create_date)
);

▶ 테이블명: 주문 ▶ 데이터: 10,000,000건

CREATE TABLE 주문 (
    order_id INT NOT NULL AUTO_INCREMENT,
    order_code VARCHAR(20) NOT NULL,
    item_code VARCHAR(20) NOT NULL,
    ...
    create_date DATETIME NOT NULL DEFAULT NOW(),
    PRIMARY KEY (order_id),
    UNIQUE (order_code),
    KEY (create_date)
);

🎯 비즈니스 요구사항

"2023년 1년 동안 등록되었던 상품을 주문한 주문 건수를 조회하라"

데이터 분포 가정 ▶ 전체 상품: 10,000건 ▶ 2023년 등록 상품: 2,000건 (20%)

▶ 전체 주문: 10,000,000건 ▶ 2023년 상품에 대한 주문: 약 2,000,000건 (평균 상품당 1,000건) 이 요구사항을 구현하는 3가지 쿼리 방식을 비교해보겠습니다.

🔍 쿼리 비교 및 실행 계획 분석

쿼리 1: INNER JOIN 방식

SELECT COUNT(DISTINCT a.order_code)
  FROM 주문 a
  JOIN 상품 b ON a.item_code = b.item_code
 WHERE b.create_date >= '2023-01-01'
   AND b.create_date < '2024-01-01';

실행 계획 (EXPLAIN)

EXPLAIN FORMAT=TREE
SELECT COUNT(DISTINCT a.order_code)
  FROM 주문 a
  JOIN 상품 b ON a.item_code = b.item_code
 WHERE b.create_date >= '2023-01-01'
   AND b.create_date < '2024-01-01';

분석 ▶ MySQL은 기본적으로 Nested Loop Join을 사용 ▶ 상품 테이블(1만 건)을 먼저 필터링 → 2,000건 ▶ 각 상품별로 주문 테이블 인덱스 룩업 → 총 200만 건 조인 결과 ▶ DISTINCT 처리로 인한 추가 오버헤드 발생

문제점

1. 대량의 중간 결과 생성: 200만 건의 조인 결과가 메모리에 생성됨
2. DISTINCT 오버헤드: 200만 건에서 중복 제거 작업 필요
3. 메모리 압박: 임시 테이블 생성으로 인한 메모리 사용량 증가

예상 실행 흐름

1. 상품 테이블 스캔: idx_item_code_create_date 인덱스 사용 ▶ 2023년 상품 2,000건 추출 (빠름)
2. 각 상품에 대해 주문 테이블 조인 ▶ 2,000개 상품 × 평균 1,000건 주문 = 200만 건 조인 결과
3. DISTINCT 처리 ▶ 200만 건에서 중복 제거 ▶ 임시 테이블 생성 가능
4. COUNT 집계

✅쿼리 2: EXISTS 방식 (권장)

SELECT COUNT(*)
  FROM 주문 a
 WHERE EXISTS (
               SELECT 1
                 FROM 상품 b
                WHERE a.item_code = b.item_code
                  AND b.create_date >= '2023-01-01'
                  AND b.create_date < '2024-01-01'
              );

실행 계획 (EXPLAIN)

EXPLAIN FORMAT=TREE
SELECT COUNT(*)
  FROM 주문 a
 WHERE EXISTS (
               SELECT 1
                 FROM 상품 b
                WHERE a.item_code = b.item_code
                  AND b.create_date >= '2023-01-01'
                  AND b.create_date < '2024-01-01'
              );

-- 결과 예시:
-- -> Aggregate: count(0)  (cost=1005245.00 rows=1)
--     -> Filter: exists(select #2)  (cost=1005245.00 rows=5000000)
--         -> Table scan on a  (cost=1005245.00 rows=10000000)
--         -> Index lookup on b using idx_item_code_create_date
--            (item_code=a.item_code), with index condition:
--            ((b.create_date >= '2023-01-01') and (b.create_date < '2024-01-01'))
--            (cost=0.25 rows=0.2)

주문 테이블의 각 행(1만 건)에 대해

1. 서브쿼리 실행: 상품 테이블에서 해당 item_code + 날짜 조건 검색
2. 인덱스 룩업: idx_item_code_create_date 복합 인덱스 활용
3. 첫 번째 매칭 발견 시 즉시 TRUE 반환 (Short-circuit evaluation)
4. 매칭 없으면 FALSE, 다음 주문 행으로 이동 ▶ 결과: 2023년 상품 주문만 카운트에 포함

장점

1. 조기 종료 (Short-circuit): 매칭되는 첫 번째 행을 발견하면 즉시 종료
2. 불필요한 데이터 생성 없음: JOIN처럼 200만 건의 중간 결과를 생성하지 않음
3. 인덱스 활용 최적화: idx_item_code_create_date 복합 인덱스 활용
4. DISTINCT 불필요: 주문 테이블의 각 행당 한 번만 판단
5. 메모리 효율: 임시 테이블 생성 없음

성능 추정 (실제 환경 기준): 인덱스 룩업 비용: ▶ 주문 1천만 건 × 인덱스 룩업(~0.01ms) = 약 100초 (이론상) ▶ 실제 캐시 효과로 20-30초 가능

vs JOIN 방식: ▶ 200만 건 조인 결과 생성 = 약 10-15초 ▶ DISTINCT 처리 (200만 건) = 약 15-25초 ▶ 총 25-40초 + 메모리 부담

쿼리 3: IN 방식

SELECT COUNT(*)
  FROM 주문 a
 WHERE a.item_code IN (
                       SELECT b.item_code
                         FROM 상품 b
                        WHERE b.create_date >= '2023-01-01'
                          AND b.create_date < '2024-01-01'
                      );

실행 계획 (EXPLAIN)

EXPLAIN FORMAT=TREE
SELECT COUNT(*)
  FROM 주문 a
 WHERE a.item_code IN (
                       SELECT b.item_code
                         FROM 상품 b
                        WHERE b.create_date >= '2023-01-01'
                          AND b.create_date < '2024-01-01'
                      );

-- 결과 예시 (MySQL 8.0.18+):
-- -> Aggregate: count(0)  (cost=1005245.00 rows=1)
--     -> Filter: (a.item_code in (select #2))  (cost=1005245.00 rows=5000000)
--         -> Table scan on a  (cost=1005245.00 rows=10000000)
--         -> Materialize with deduplication  -- 또는 First Match
--            (cost=202.50..202.50 rows=2000)
--            -> Index range scan on b using idx_item_code_create_date
--               (cost=202.50 rows=2000)

MySQL 8.0의 세미 조인 최적화 MySQL 8.0부터는 IN 서브쿼리에 대해 다양한 세미 조인 전략을 사용합니다

1. First Match: EXISTS와 유사하게 첫 매칭 발견 시 중단
2. Materialization: 서브쿼리 결과를 임시 테이블로 구체화
3. Duplicate Weedout: 중복 제거 전략
4. LooseScan: 인덱스 스캔 최적화
5. Table Pull-out: 서브쿼리를 조인으로 변환

위 케이스에서 예상되는 옵티마이저 선택

-- Materialization 전략 선택 가능성이 높음
-- 이유: 서브쿼리 결과(2,000건)가 작아서 해시 테이블 생성이 효율적

실행 순서:
1. 서브쿼리 실행: 2023년 상품 2,000건 추출
2. 임시 해시 테이블 생성: 2,000개 item_code
3. 주문 1천만 건 스캔하며 해시 테이블 룩업
4. 매칭되는 주문 카운트 (약 200만 건)

문제점과 고려사항 MySQL 8.0의 세미 조인 최적화가 IN 연산자에도 적용되어 많은 경우 EXISTS와 유사한 성능을 보입니다. 하지만 다음과 같은 이유로 EXISTS 사용을 권장합니다

1. 옵티마이저 의존성: 통계 정보의 부정확성으로 인한 잘못된 실행 계획 선택 가능 ```sql

-- 예시: 통계가 오래된 경우 -- 옵티마이저가 Materialization을 선택할 수 있음 -- (실제로는 First Match가 더 효율적인 상황에서도)

2. **대용량 주문 테이블 스캔 부담**
```sql
-- Materialization 전략 시:
   -- 주문 1천만 건 전체 스캔 → 각 행마다 해시 테이블 룩업
   -- vs EXISTS: 인덱스 활용으로 스캔 범위 제한 가능

3. 명시적 의도 표현: EXISTS는 "존재 여부 확인"이라는 의도를 명확히 표현 ```sql

-- EXISTS: 명확하게 "존재하는가?"를 질문 WHERE EXISTS (SELECT 1 FROM ...)

-- IN: "값이 집합에 포함되는가?"를 질문 -- (옵티마이저가 세미 조인으로 변환) WHERE item_code IN (SELECT ...)

3. **예측 가능성**: **`EXISTS`는 항상 세미 조인 방식으로 동작**
▶ `IN`은 옵티마이저가 상황에 따라 다른 전략 선택 가능
▶ 통계 정보 변화, 데이터 분포 변경 시 실행 계획이 달라질 수 있음

---
## ⚠️ 옵티마이저와 통계 정보
옵티마이저는 **통계 정보와 인덱스를 기반으로 실행 계획을 결정**합니다. 
하지만 다음과 같은 상황에서 최적이 아닌 선택을 할 수 있습니다

### 📉 통계 정보 불일치 시나리오
```sql
-- 시나리오: 대량의 주문 데이터 정리 작업 후
DELETE FROM 주문 WHERE create_date < '2020-01-01';
-- 1천만 건 → 500만 건으로 감소

-- 하지만 통계 정보는 업데이트되지 않음
SELECT TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_NAME = '주문';
-- 여전히 10,000,000으로 표시될 수 있음

-- 실제 데이터
SELECT COUNT(*) FROM 주문;
-- 5,000,000건

문제 발생 ▶ 옵티마이저는 여전히 상품 테이블이 100만 건이라고 판단 ▶ Materialization 전략 선택 (임시 테이블 생성) ▶ 실제로는 First Match가 더 효율적인 상황

해결 방법

-- 1. 통계 정보 갱신
ANALYZE TABLE 주문;
ANALYZE TABLE 상품;

-- 2. 통계 정보 확인
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME IN ('주문', '상품');

-- 3. 또는 명시적으로 EXISTS 사용하여 의도 명확화
WHERE EXISTS (...)

이처럼 옵티마이저는 예측 가능하게 동작하지만, 통계 정보와 실제 데이터의 불일치가 발생하면 차선의 선택을 할 수 있습니다. EXISTS를 사용하면 이러한 변수를 줄이고 의도를 명확히 표현할 수 있습니다.

⚡ Hash Join

🆕 MySQL 8.0.18 이후의 Hash Join

"MySQL은 기본적으로 Nested Loop Join만 사용한다"는 과거의 이야기입니다. MySQL 8.0.18부터는 Hash Join이 도입되어 조인 성능이 크게 개선되었습니다.

Hash Join 지원 현황 ▶ MySQL 8.0.18 (2019년 10월): Inner Hash Join 도입 ▶ MySQL 8.0.20 (2020년 4월): Outer, Semi, Anti Join에도 Hash Join 지원 확대 ▶ MySQL 8.0.19 ~ 현재 (8.4): 계속 지원 및 개선

Hash Join 동작 확인

EXPLAIN FORMAT=TREE
SELECT COUNT(DISTINCT a.order_code)
  FROM 주문 a
  JOIN 상품 b ON a.item_code = b.item_code
 WHERE b.create_date >= '2023-01-01'
   AND b.create_date < '2024-01-01';

-- 결과 예시 (인덱스가 없거나 옵티마이저가 Hash Join 선택 시):
-- -> Aggregate: count(distinct a.order_code)
--     -> Inner hash join (b.item_code = a.item_code)
--         -> Filter: ((b.create_date >= '2023-01-01') 
--                     and (b.create_date < '2024-01-01'))
--             -> Table scan on b
--         -> Hash
--             -> Table scan on a

Hash Join의 장점

1. 대량 데이터 조인 시 Nested Loop보다 효율적
2. 메모리 내 해시 테이블 활용으로 빠른 매칭
3. equi-join 조건에서 최적화

그렇다면 JOIN도 괜찮을까? 상황에 따라 다릅니다. ▶ Hash Join 사용 가능 시 (인덱스 없음): JOIN 방식도 효율적 ▶ 인덱스 사용 가능 시: Nested Loop with Index가 선택될 수 있음 ▶ DISTINCT 필요 시: EXISTS가 여전히 유리 (중간 결과 없음)

❗ NOT EXISTS vs NOT IN 중요한 차이점

EXISTS와 IN은 긍정 조건에서는 비슷하지만, 부정 조건(NOT)에서는 큰 차이가 있습니다.

NULL 값 처리의 함정

-- 테스트 데이터
CREATE TABLE test_orders (
   order_id INT, item_code VARCHAR(20)
);

CREATE TABLE test_items (item_code VARCHAR(20));

INSERT INTO test_orders VALUES (1, 'A'), (2, 'B'), (3, 'C');
INSERT INTO test_items VALUES ('A'), (NULL);

-- NOT IN 사용 (위험!)
SELECT * 
  FROM test_orders
 WHERE item_code NOT IN (SELECT item_code FROM test_items);
-- 결과: 0건 (예상과 다름!)

-- NOT EXISTS 사용 (안전)
SELECT * 
  FROM test_orders a
 WHERE NOT EXISTS (
                   SELECT 1 
                     FROM test_items b 
                    WHERE a.item_code = b.item_code
                  );
-- 결과: 2건 (order_id 2, 3) ✅

왜 NOT IN은 0건을 반환할까? ▶ SQL의 3-valued logic (TRUE, FALSE, UNKNOWN) 때문입니다:

-- NOT IN의 내부 동작
WHERE item_code NOT IN ('A', NULL)
-- 전개하면:
WHERE item_code <> 'A' AND item_code <> NULL
-- NULL과의 비교는 항상 UNKNOWN
-- 'B' <> NULL → UNKNOWN
-- 'C' <> NULL → UNKNOWN
-- UNKNOWN AND TRUE → UNKNOWN
-- WHERE 절은 TRUE만 통과 → 결과 없음

📝 권장

-- ❌ 위험: NOT IN
WHERE item_code NOT IN (SELECT item_code FROM 상품)

-- ✅ 안전: NOT EXISTS
WHERE NOT EXISTS (
                 SELECT 1 
                   FROM 상품 b 
                  WHERE a.item_code = b.item_code
                 )

-- ✅ 또는 NULL 명시적 제외
WHERE item_code NOT IN (
                        SELECT item_code 
                          FROM 상품 
                         WHERE item_code IS NOT NULL
                       )

🎯 각 방식의 적합한 사용 사례

✅ EXISTS 사용을 권장하는 경우

1. 서브쿼리 결과가 많을 때

-- 상품 100만 건 중 2023년 데이터 10만 건
   WHERE EXISTS (
                 SELECT 1 
                   FROM 상품 b 
                  WHERE a.item_code = b.item_code
                    AND b.create_date >= '2023-01-01'
   )

2. 존재 여부만 확인하면 될 때

-- "이 주문이 처리된 상품인가?"
   WHERE EXISTS (SELECT 1 FROM 상품 b ...)

3. 부정 조건 (NOT EXISTS) 사용 시

-- NULL 안전 보장
   WHERE NOT EXISTS (...)

4. 복합 조건 체크 시

WHERE EXISTS (
              SELECT 1 
                FROM 상품 b
               WHERE a.item_code = b.item_code
                 AND b.stock > 0
                 AND b.status = 'ACTIVE'
                 AND b.create_date >= '2023-01-01'
             )

📝 IN 사용을 권장하는 경우

1. 서브쿼리 결과가 적고 명확할 때

-- 특정 카테고리 3개
   WHERE category_id IN (1, 2, 3)

   -- 또는 결과가 10건 이하인 서브쿼리
   WHERE dept_id IN (
                     SELECT dept_id 
                       FROM departments 
                      WHERE region = 'APAC'  -- 결과: 3건
                    )

2. 값 목록이 정적일 때

WHERE status IN ('PENDING', 'PROCESSING', 'SHIPPED')

3. 가독성이 중요하고 성능 차이가 미미할 때

-- 직관적인 표현
   WHERE item_code IN (SELECT item_code FROM featured_items)

🔗 JOIN 사용을 권장하는 경우

1. 서브쿼리 테이블의 다른 컬럼도 필요할 때

-- 상품명, 가격 등도 함께 조회
   SELECT a.order_code, b.item_name, b.price
     FROM 주문 a
     JOIN 상품 b ON a.item_code = b.item_code
    WHERE b.create_date >= '2023-01-01'

2. 집계 함수에서 조인 테이블 값 사용 시

SELECT a.order_date, SUM(b.price) as total_price
  FROM 주문 a
  JOIN 상품 b ON a.item_code = b.item_code
 GROUP BY a.order_date

3. Hash Join이 효율적인 대량 조인

-- 두 대용량 테이블 조인 (인덱스 없음)
SELECT /*+ NO_BNL(a, b) */ a.*, b.*
  FROM large_table_a a IGNORE INDEX (idx)
  JOIN large_table_b b IGNORE INDEX (idx) ON a.key = b.key

🎓 결론

⚠️ EXISTS는 강력한 성능 최적화 도구이지만, 만능은 아닙니다.

📌 핵심 정리

1. EXISTS의 강점 ▶ 조기 종료(Short-circuit)로 불필요한 스캔 방지 ▶ 중간 결과 집합 생성 없음 ▶ NOT EXISTS의 NULL 안전성

2. IN의 쓰임 ▶ 소량의 명확한 값 목록 ▶ MySQL 8.0의 세미 조인 최적화 활용 ▶ 단, 옵티마이저 의존성 고려 필요

3. JOIN의 재평가 ▶ Hash Join 시대의 성능 개선 ▶ 다른 컬럼 참조 시 필수 ▶ 인덱스 전략과 함께 고려

🎯 최종 권장사항

쿼리 선택 플로우차트

1. 서브쿼리 테이블의 다른 컬럼이 필요한가?
   YES → JOIN 사용
   NO → 2번으로

2. 존재 여부만 확인하는가?
   YES → EXISTS 사용 (권장)
   NO → 3번으로

3. 서브쿼리 결과가 10건 이하의 명확한 값인가?
   YES → IN 사용 가능
   NO → EXISTS 사용 (권장)

4. NOT 조건인가?
   YES → NOT EXISTS 사용 (필수)
   NO → 상황에 따라 선택

DBA의 업무는 단순히 쿼리를 빠르게 만드는 것이 아니라, 데이터의 특성과 비즈니스 요구사항을 이해하고 최적의 균형점을 찾는 것입니다. 호출 빈도, 데이터 양, 인덱스 전략, 하드웨어 리소스 등 다양한 요소를 종합적으로 고려하여 판단해야 하며, 끊임없는 학습과 실험을 통해 어떤 상황에서도 최선의 쿼리를 작성할 수 있도록 노력해야 합니다.

💡 핵심 포인트

▶ EXISTS는 조건에 맞는 첫 번째 데이터를 찾으면 즉시 반환 - 불필요한 스캔 방지 및 메모리 효율 ▶ 대용량 서브쿼리 테이블 참조 시 EXISTS 사용 권장 - JOIN은 중간 결과(200만 건) 생성 부담 ▶ IN 연산자는 MySQL 8.0의 세미 조인 최적화가 적용되지만 통계 정보 의존성 존재 - EXISTS가 예측 가능하고 안정적 ▶ NOT 조건에서는 반드시 NOT EXISTS 사용 - NOT IN은 NULL 값 존재 시 결과 없음 (치명적 버그) ▶ 통계 정보 갱신(ANALYZE TABLE)과 인덱스 전략이 핵심 - 옵티마이저의 올바른 판단을 위한 필수 작업 ▶ 상황에 따른 최적의 방법 선택 - 다른 컬럼 필요 시 JOIN, 소량 값 목록은 IN, 존재 여부 확인은 EXISTS

🤔 들어가며

이 글을 읽는 엔지니어 혹은 시스템 관리자 여러분은 고객의 액션을 통해 생성되는 데이터를 무작정 삭제하고 계시나요? 그렇다면 이 글을 끝까지 읽어 얼마나 큰 자산을 놓치고 있었는지 고민해 봐주시면 좋을 것 같습니다.

📌 문제 인식

신규 기능을 기획하고 개발하는 과정에서 엔지니어는 기획에 맞는 CRUD 기능을 기본적으로 구현합니다.

다만, 삭제하면 안 될 데이터까지 삭제되도록 기능 구현을 하는 엔지니어가 많다는 것을 최근 알게 되어 오늘은 이 내용에 대해 알아보려 합니다.

데이터를 삭제하는 방법은 대표적으로 2가지가 존재합니다. ▶ Hard Delete: 물리적 삭제 ▶ Soft Delete: 논리적 삭제

위 두 가지에 대해서는 장단점이 존재하지만 서비스의 기획 또는 보안적인 측면에 따라 어느 한쪽만 택하는 것이 아닌 다양하게 사용해야 한다는 것을 알아야 합니다.

🔍 Hard Delete vs Soft Delete

Hard Delete (물리적 삭제)

DELETE FROM schema.tables WHERE pk_id = 1;

▶ 데이터베이스에서 실제로 데이터를 삭제하는 방식입니다.

Soft Delete (논리적 삭제)

UPDATE schema.tables SET deleted_yn = 'Y' WHERE pk_id = 1;

▶ 데이터를 물리적으로 삭제하지 않고, 삭제 여부를 나타내는 플래그 컬럼을 업데이트하는 방식입니다.

💡 언제 어떤 방식을 사용해야 할까?

앞서 어느 한쪽만 택하는 것이 아닌 다양하게 사용해야 한다는 것을 알아야 한다고 했습니다. 어떤 사례에서는 Hard Delete 방식을 사용하고 또 어떤 사례에서는 Soft Delete 방식을 사용해야 할지 알아보겠습니다.

✅ Soft Delete를 사용해야 하는 경우

🥺ྀི 고객 액션에 따른 데이터 삭제

고객의 액션에 따라 데이터를 삭제해야 한다는 기획이 존재한다면 해당 테이블에 필터링 컬럼을 추가하여 Soft Delete 방식을 사용하는 것이 좋습니다.

📌 데이터 복원 가능성

가장 먼저 생각할 수 있는 이유는 데이터 복원이겠죠?? 고객의 실제 액션을 통해서 데이터가 삭제되었음에도 불구하고 이를 인지하지 못한다면 고객 문의로 인입될 가능성이 매우 높아집니다. 이 과정에서 필요시 고객의 데이터를 롤백하여 복원이 가능해집니다. 만약 Hard Delete 방식을 통해 기능 구현이 되었다면 별도의 로그(general log 등)를 통해 트래킹하고 복원을 진행할 때 많은 비용이 발생하게 될 겁니다.

💰 데이터는 기업의 자산

고객 데이터에 대해 고객의 요청, 법령위배가 되지 않는 선에서는 실 데이터를 삭제하지 않는 것이 곧 기업의 자산이 됩니다. 그렇기 때문에 실 데이터 삭제를 지양하고 필터링 컬럼을 통해 데이터를 보관하는 것은 기업의 성장에 큰 역할을 할 수 있을 것입니다.

⚠️ Soft Delete의 한계

하지만 무조건 Soft Delete 방식이 좋은 것은 아닙니다. 테이블의 로우 수가 증가할수록, 컬럼이 많아져 테이블의 크기가 비대해질수록 해당 방식의 한계점은 명확해질 것입니다. 이러한 경우 Hard Delete 방식을 고려해야 하지만 해당 방식을 고려할 만큼 테이블이 비대해졌다면 우리는 Partition Table을 고려해야 합니다.

Partition Table의 개념이 궁금하시면 아래의 링크를 통해 확인할 수 있습니다. ▶ https://velog.io/@swseon_96/mysql-partition

🔧 Partition Table 활용

일별 / 주별 / 월별 / 년별 등 기획에 적절한 기간의 파티션 테이블을 도입하여 Soft Delete 방식과 Hard Delete 방식을 적절히 사용한다면 보다 높은 성능을 체감할 수 있습니다.

또한, 파티션 테이블을 직접 삭제하게 된다면 빈 공간의 낭비 없이 데이터베이스를 보다 더 효율적으로 관리할 수 있습니다. (Partition Table에 대한 성능은 위 링크를 통해 확인이 가능하기 때문에 상세 설명은 생략합니다.)

✅ Hard Delete를 사용해야 하는 경우

🔒 보안 및 개인정보 보호

기술의 발달과 함께 IT 기업에서는 보안을 보다 더 신경 써야 합니다. 기업 규모에 따라 ISMS와 같은 보안 인증 심사를 필수적으로 받아야 할 상황이 발생하기도 합니다. 가장 중요한 건 고객의 개인 정보이기 때문에 고객의 민감성 개인 정보로 판단되는 데이터의 경우 무조건 Hard Delete 방식을 통해 데이터를 삭제해야 합니다.

예시 ▶ 고객 계정 정보 ▶ 결제 정보 등

⚖️ 법적 보관 의무

다만, 일부 데이터에 한해서는 몇 년간 보관해야 하는 의무가 있으므로 법적 지식도 무시할 순 없습니다. 이러한 부분까지 고려된다면 그만큼 기획 단계에서 많은 고민이 필요하게 될 것입니다.

📝 마무리

더 다양한 상황이 존재하겠지만 해당 게시글에서는 여기까지만 소개하고자 합니다. 제가 언급한 상황에서도 다양한 사례가 존재하기에 위에 소개드린 내용이 *꼭 정답이라고 말할 수는 없습니다. * 다만, 고객의 사용 경험을 최상으로 끌어올리고 기업의 가치를 성장시키기 위해서는 끊임없는 고민을 통해 자신의 상황에서 가장 효율적인 방식을 찾아낼 수 있어야 할 것입니다.

💭 DBA와 엔지니어의 시각 차이

많은 엔지니어가 작성한 기술 블로그를 읽었을 때 가장 먼저 느낄 수 있던 것은 엔지니어와 DBA가 바라보는 시각은 정말 다르다는 것이었습니다. ▶ 엔지니어: 얼마나 빠르고, 효율적으로 고객에게 최상의 서비스를 제공할 수 있을지를 고민 ▶ DBA: 얼마나 안정적이고, 지속적으로 고객에게 최상의 서비스를 제공할 수 있을지 고민

각자 바라보는 시각이 다른 것에 비해 DBA 입장으로서 기술된 컨퍼런스가 많지 않아 DBA 입장의 데이터 삭제 방법을 정리해 보았습니다.

💡 핵심 포인트

▶ Soft Delete: 데이터 복원 가능성, 기업 자산 보존 - 고객 액션 기반 삭제에 적합 ▶ Hard Delete: 보안, 개인정보 보호 필수 - 민감 정보 삭제에 필수 ▶ Partition Table 활용: 대용량 데이터 관리 시 Soft/Hard Delete 혼합 사용 ▶ 상황에 맞는 선택: 무조건적인 적용이 아닌 서비스 특성에 맞는 방식 선택

💡 들어가며

MySQL의 경우 Oracle / MSSQL과 달리 클러스터드 인덱스를 사용하면서 내부적으로 논리적인 키값을 사용하지 않고, 테이블의 Primary Key 값을 통해 구성되는 것이 특징입니다.

그렇기 때문에 MySQL을 사용할 경우 PK의 대상이 되는 데이터 타입이 중요합니다.

대부분의 엔지니어분들은 "INT" 혹은 "VARCHAR" 타입을 사용하여 키를 설정하는데요, 과거에는 CHAR / VARCHAR 둘 중 어떤 데이터 타입이 더 효율적인지가 화제였다고 합니다. (물론 테스트 결과, 동일했다 라는 정보가 더 많았습니다.)

이처럼 해당 글에서는 어떤 데이터 타입으로 기본 키(Primary Key)를 설정하면 좋을지 테스트를 해보려고 합니다.

🔧 테스트 환경

1. 운영 환경

▶ Aurora MySQL 3.03 (MySQL 8.0.26)

2. 비교 대상

▶ bigint auto_increment ▶ varchar utf8mb4_unicode_ci ▶ varchar latin1_general_ci

3. 테이블 구조

▉ 원본 테이블 구조

▶ 약 1억 개의 데이터가 저장된 테이블 ▶ PK: INT, AUTO_INCREMENT ▶ Secondary Index: 10개

▉ 테스트 테이블 구조

xxx_bigint ▶ PK: BIGINT, AUTO_INCREMENT ▶ Secondary Index: 10개

xxx_utf8mb4 ▶ PK: VARCHAR utf8mb4_unicode_ci ▶ Secondary Index: 10개 ▶ 기존 PK였던 인조키 제거

xxx_latin1 ▶ PK: VARCHAR latin1_general_ci ▶ Secondary Index: 10개 ▶ 기존 PK였던 인조키 제거

📋 테스트 시나리오

다음 4가지 스캔 방식을 기준으로 성능을 테스트합니다.

1. INDEX FULL SCAN - 첫 번째 Leaf 노드부터 마지막 Leaf 노드까지 전체 스캔 방식
2. TABLE FULL SCAN - 테이블의 첫 번째 레코드부터 마지막 레코드까지 전체 스캔 방식
3. INDEX RANGE SCAN - 필요한 범위만 탐색하는 인덱스 스캔 방식
4. PK RANDOM ACCESS - 물리적으로 떨어진 페이지에 랜덤 접근 방식

⚠️ 테스트 유의사항

테스트 수행 방법에 맞춰 테스트를 진행할 경우, 데이터가 캐시에 저장되어 테스트 목적과 다른 결과가 나올 수 있으므로 회차가 변경될 때마다 인스턴스 재부팅을 통해 캐시 초기화를 진행했습니다.

MySQL의 경우 Oracle / MSSQL과 달리 실행계획을 정확히 알 수 없다는 것이 특징입니다. 그렇기 때문에 정확한 측정이 불가능하여 프로파일을 통한 execute 시간만을 체크했습니다.

🎯 해당 결과값만으로는 부정확한 테스트가 될 확률이 높지만, 어느 정도 납득할 수 있는 결과가 나올 것으로 추정됩니다.

🧪 테스트 준비: 데이터 INSERT

-- TEST DATA INSERT (1개 테이블마다 인스턴스 재부팅)
INSERT INTO test_database.xxx_bigint (`a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`)
SELECT `a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`
FROM test_database.origin_table
ORDER BY 1 DESC
LIMIT 1000000;

INSERT INTO test_database.xxx_utf8mb4 (`a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`)
SELECT `a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`
FROM test_database.origin_table
ORDER BY 1 DESC
LIMIT 1000000;

INSERT INTO test_database.xxx_latin1 (`a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`)
SELECT `a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`
FROM test_database.origin_table
ORDER BY 1 DESC
LIMIT 1000000;

✍ 결과

xxx_bigint ▶ 1회차: 3m 12.45s (executing: 192.37s) ▶ 2회차: 3m 9.91s (executing: 189.87s) ▶ 3회차: 2m 51.99s (executing: 171.94s)

xxx_utf8mb4 ▶ 1회차: 3m 33.78s (executing: 213.66s) ▶ 2회차: 3m 29.52s (executing: 209.43s) ▶ 3회차: 3m 18s (executing: 198.22s)

xxx_latin1 ▶ 1회차: 3m 24.36s (executing: 204.25s) ▶ 2회차: 3m 26.48s (executing: 206.37s) ▶ 3회차: 2m 58.86s (executing: 178.78s)

🧪 테스트 1: TABLE FULL SCAN

SELECT *
FROM test_database.xxx_bigint
WHERE group_code = 'g20240101'
  AND user_number LIKE '%010%'
LIMIT 10000;

SELECT *
FROM test_database.xxx_utf8mb4
WHERE group_code = 'g20240101'
  AND user_number LIKE '%010%'
LIMIT 10000;

SELECT *
FROM test_database.xxx_latin1
WHERE group_code = 'g20240101'
  AND user_number LIKE '%010%'
LIMIT 10000;

📈 실행계획

xxx_bigint (1,2,3회차 모두 동일한 결과)

xxx_utf8mb4 (1,2,3회차 모두 동일한 결과)

xxx_latin1 (1,2,3회차 모두 동일한 결과)

✍ 결과

1차 테스트 ▶ xxx_bigint: 0.33s / 0.32s / 0.32s ▶ xxx_utf8mb4: 0.46s / 0.43s / 0.42s ▶ xxx_latin1: 0.40s / 0.37s / 0.37s

2차 테스트 ▶ xxx_bigint: 0.32s / 0.32s / 0.31s ▶ xxx_utf8mb4: 0.45s / 0.41s / 0.44s ▶ xxx_latin1: 0.39s / 0.38s / 0.37s

3차 테스트 ▶ xxx_bigint: 0.33s / 0.30s / 0.31s ▶ xxx_utf8mb4: 0.44s / 0.43s / 0.41s ▶ xxx_latin1: 0.36s / 0.37s / 0.37s

🧪 테스트 2: INDEX FULL SCAN

SELECT user_address, COUNT(*)
FROM test_database.xxx_bigint
GROUP BY user_address
ORDER BY COUNT(*) DESC
LIMIT 100;

SELECT user_address, COUNT(*)
FROM test_database.xxx_utf8mb4
GROUP BY user_address
ORDER BY COUNT(*) DESC
LIMIT 100;

SELECT user_address, COUNT(*)
FROM test_database.xxx_latin1
GROUP BY user_address
ORDER BY COUNT(*) DESC
LIMIT 100;

📈 실행계획

xxx_bigint (1,2,3회차 모두 동일한 결과)

xxx_utf8mb4 (1,2,3회차 모두 동일한 결과)

xxx_latin1 (1,2,3회차 모두 동일한 결과)

✍ 결과

1차 테스트 ▶ xxx_bigint: 2.25s / 2.22s / 2.20s ▶ xxx_utf8mb4: 2.24s / 2.24s / 2.24s ▶ xxx_latin1: 2.21s / 2.19s / 2.17s

2차 테스트 ▶ xxx_bigint: 2.28s / 2.24s / 2.27s ▶ xxx_utf8mb4: 2.26s / 2.24s / 2.21s ▶ xxx_latin1: 2.26s / 2.22s / 2.23s

3차 테스트 ▶ xxx_bigint: 2.30s / 2.27s / 2.28s ▶ xxx_utf8mb4: 2.33s / 2.33s / 2.29s ▶ xxx_latin1: 2.31s / 2.27s / 2.27s

🧪 테스트 3: INDEX RANGE SCAN

SELECT category, SUM(price), COUNT(*)
FROM test_database.xxx_bigint FORCE KEY(user_id)
WHERE group_code LIKE 'g202401%'
GROUP BY category;

SELECT category, SUM(price), COUNT(*)
FROM test_database.xxx_utf8mb4 FORCE KEY(user_id)
WHERE group_code LIKE '202401%'
GROUP BY category;

SELECT category, SUM(price), COUNT(*)
FROM test_database.xxx_latin1 FORCE KEY(user_id)
WHERE group_code LIKE '202401%'
GROUP BY category;

📈 실행계획

xxx_bigint (1,2,3회차 모두 동일한 결과)

xxx_utf8mb4 (1,2,3회차 모두 동일한 결과)

xxx_latin1 (1,2,3회차 모두 동일한 결과)

✍ 결과

1차 테스트 ▶ xxx_bigint: 0.48s / 0.45s / 0.45s ▶ xxx_utf8mb4: 0.24s / 0.22s / 0.22s ▶ xxx_latin1: 0.21s / 0.20s / 0.20s

2차 테스트 ▶ xxx_bigint: 0.48s / 0.46s / 0.46s ▶ xxx_utf8mb4: 0.24s / 0.22s / 0.22s ▶ xxx_latin1: 0.22s / 0.20s / 0.20s

3차 테스트 ▶ xxx_bigint: 0.48s / 0.46s / 0.45s ▶ xxx_utf8mb4: 0.23s / 0.22s / 0.22s ▶ xxx_latin1: 0.21s / 0.20s / 0.19s

🧪 테스트 4: PK RANDOM ACCESS

SELECT COUNT(*)
FROM (
    SELECT b.user_id, b.user_name, b.group_code
    FROM test_database.xxx_middle_bigint a
    JOIN test_database.xxx_bigint b ON a.middle_user_id = b.user_id
) a;

SELECT COUNT(*)
FROM (
    SELECT b.user_id, b.user_name, b.group_code
    FROM test_database.xxx_middle_utf8mb4 a
    JOIN test_database.xxx_utf8mb4 b ON a.middle_user_id = b.user_id
) a;

SELECT COUNT(*)
FROM (
    SELECT b.user_id, b.user_name, b.group_code
    FROM test_database.xxx_middle_latin1 a
    JOIN test_database.xxx_latin1 b ON a.middle_user_id = b.user_id
) a;

📈 실행계획

xxx_bigint (회차별 차이 발생) 1회차:

2회차:

3회차:

xxx_utf8mb4 (회차별 차이 발생) 1회차:

2회차:

3회차:

xxx_latin1 (회차별 차이 발생) 1회차:

2회차:

3회차:

✍ 결과

1차 테스트 ▶ xxx_bigint: 0.51s / 0.49s / 0.49s ▶ xxx_utf8mb4: 0.58s / 0.56s / 0.55s ▶ xxx_latin1: 0.35s / 0.32s / 0.32s

2차 테스트 ▶ xxx_bigint: 0.51s / 0.51s / 0.50s ▶ xxx_utf8mb4: 0.58s / 0.54s / 0.55s ▶ xxx_latin1: 0.33s / 0.32s / 0.32s

3차 테스트 ▶ xxx_bigint: 0.52s / 0.50s / 0.50s ▶ xxx_utf8mb4: 0.58s / 0.54s / 0.56s ▶ xxx_latin1: 0.35s / 0.31s / 0.32s

📊 최종 결과 정리

스캔 방식별 최적 데이터 타입

💡 결론

테스트 결과, varchar latin1_general_ci 방식이 INDEX FULL SCAN 방식을 제외하고 제일 우수한 성능을 보였습니다.

해당 테스트 결과를 토대로 MySQL에서 Primary Key를 설정한다면, 특히 스캔 방식이 다양한 환경에서는 varchar latin1_general_ci 설정이 도움이 될 수 있을 것 같습니다.

📝 마무리

많은 기술 블로그를 보면 "정수형이 좋다" 또는 "비즈니스에 따라서 다르게 설계해야 한다" 라는 내용이 가장 많았으나 실제 스트레스 테스트를 통한 지표는 많지 않아 의문이 많았습니다. 이번 테스트를 통해서 스캔 방식에 따른 성능 차이가 발생한다는 것을 알 수 있었으며 이를 통해 추후 비즈니스 설계 과정에서 해당 내용을 참고하면 좋을 것 같습니다.

💡 핵심 포인트

▶ MySQL은 클러스터드 인덱스를 PK 기반으로 구성 - PK 데이터 타입 선택이 중요 ▶ 스캔 방식에 따라 성능 차이 발생 - TABLE FULL, INDEX FULL, INDEX RANGE, PK RANDOM ACCESS ▶ latin1_general_ci가 대부분의 스캔 방식에서 우수 - 특히 RANGE SCAN과 RANDOM ACCESS ▶ 비즈니스 요구사항과 함께 고려 - 무조건적인 적용보다는 상황에 맞는 선택 필요

외래키에 대해서 어떻게 생각하냐고 묻는 질문에 대부분의 엔지니어는 "설정하면 좋겠지만 불편한 점이 많은 것 같다" 라고 생각하는 분이 많은 것 같습니다.

어떤 점이 좋고 왜 불편한가? 라는 질문을 한다면 명쾌한 답변을 들을 수 없었는데요 저 역시 해당 게시글을 작성하며 생각을 정리하기 전까지는 명쾌한 답변을 할 수 없었을 것이라 생각이 듭니다.

최근 Foreign Key에 대해서 많은 기술 블로그를 읽어보았습니다. 블로그마다 각기 다른 생각을 이야기하며 Foreign Key에 대한 이슈를 설명했으며 주된 문제점은 "개발의 불편함" 과 "성능 저하" 문제였습니다.

🤔 Foreign Key의 주요 이슈

1️⃣ 개발의 불편함

Foreign Key를 설정할 경우, 참조 무결성 제약조건으로 인해 개발 시 많은 어려움이 있으며 그중에서도 가장 큰 불편함은 테스트 데이터를 만드는 것 일 텐데요.

Foreign Key를 설정하지 않는다면 필요한 테이블의 데이터만 생성해 테스트가 가능하지만, Foreign Key를 설정한다면 해당 테이블의 부모 테이블 여부 확인 + 부모 테이블 데이터 생성 등 추가 작업이 이루어지기 때문입니다.

그러나 저는 개발에 발생하는 불편함(테스트 데이터 생성)은 DISABLE NOVALIDATE 옵션 등을 통해서 제약조건을 OFF 시킬 수 있어 불편함을 해소할 수 있을 것이라고 생각합니다.

💡 DISABLE NOVALIDATE 옵션이란?

• DISABLE: 제약조건을 비활성화하여 새로운 데이터 입력 시 검증하지 않음
• NOVALIDATE: 기존 데이터에 대해서는 검증하지 않음
• 즉, 제약조건은 존재하지만 실제로 작동하지 않는 상태

사용 예시 (Oracle):

ALTER TABLE 자식테이블 DISABLE NOVALIDATE CONSTRAINT FK_제약조건명;

⚠️ 주의사항:

• 테스트 완료 후 다시 ENABLE VALIDATE로 전환 시, 기존 데이터의 정합성 문제가 있다면 에러 발생
• 개발/테스트 환경에서만 사용하고, 운영 환경에서는 신중하게 판단 필요

2️⃣ 성능 저하

Foreign Key 설정으로 인한 성능 이슈는 두 가지 측면에서 발생합니다.

📊 Validation 검사 부하

DML 작업 시 FK 제약조건 검증을 위해 부모/자식 테이블 조회가 추가로 발생합니다.

• 영향도: FK 컬럼에 인덱스가 적절히 설정되어 있다면 인덱스 스캔으로 처리되어 성능 영향은 미미
• 주의점: 자식 테이블의 FK 컬럼에 인덱스가 없으면 부모 레코드 삭제 시 Full Table Scan 발생 가능

🔒 Lock 이슈 (주요 병목)

FK로 인한 실질적인 성능 저하는 Lock 전파에서 발생합니다.

발생 원리:

• 자식 테이블 INSERT/UPDATE 시 부모 테이블의 참조 레코드에 공유 락(Shared Lock) 발생
• 부모 레코드를 수정하려는 다른 트랜잭션이 대기 상태가 되어 동시성 저하
• 복잡한 트랜잭션 구조에서 데드락(Deadlock) 발생 위험 증가

DBMS별 차이:

• Oracle: MVCC 메커니즘으로 읽기 작업이 쓰기를 차단하지 않으며, Lock 경합 최소화
• MySQL/InnoDB: Oracle과 유사하게 MVCC를 사용하지만, FK 제약 검증 시 부모 테이블에 shared record-level lock을 설정하여 동시성 환경에서 추가적인 Lock 경합 발생 가능

고트래픽 환경이나 실시간 처리가 중요한 금융권에서는 이러한 Lock 경합이 응답 시간 지연과 데드락 장애로 이어질 수 있어 FK 설정을 지양합니다.

🛠️ Foreign Key 설정 전략

그렇다면 Foreign Key 설정을 꼭 해야 하는 걸까요?? 제가 생각하는 Foreign Key 설정 구성은 다음과 같은 방법이 있을 것 같습니다.

방안 1️⃣

운영DB 및 테스트DB 서버의 모든 테이블에 대해 Foreign Key 설정을 하되, 운영DB의 경우 성능 이슈가 존재하는 테이블에 대해서만 Validation 중지

방안 2️⃣

운영DB 및 테스트DB 서버의 모든 테이블에 대해 Foreign Key 설정을 하되, 운영DB의 경우 Validation 중지

방안 3️⃣

운영DB 서버에는 Foreign Key 설정을 하지 않으며, 테스트DB 서버에서 Foreign Key 설정을 통해 데이터 검증

📌 MySQL 사용 시 주의사항

다만, MySQL의 경우 Oracle처럼 테이블별로 Validation 설정을 할 수가 없어 FOREIGN_KEY_CHECKS 환경 변수 설정을 통해 전역으로 관리가 가능하다는 점을 참고해야겠습니다.

🎯 결론: 상황별 권장 전략

Foreign Key 설정에 대한 절대적인 정답은 없지만, 다음과 같은 기준으로 판단하는 것을 권장해볼 수 있을 것 같습니다.

📊 소규모 서비스 또는 트래픽이 낮은 경우

▶ 방안 1 추천: 운영/테스트 모두 FK 설정 + Validation 활성화

• 데이터 무결성 보장의 이점이 성능 영향보다 큼

📈 중규모 서비스 (일부 테이블에서 성능 이슈 발생)

▶ 방안 1 추천: 문제되는 테이블만 선택적으로 Validation 중지

• 무결성과 성능의 균형점 확보

🚀 대규모/실시간 서비스 (금융, 거래 시스템 등)

▶ 방안 2 또는 3 추천: 운영DB는 FK 미설정 또는 Validation 중지

• 애플리케이션 레벨에서 데이터 정합성 관리 필수
• 테스트DB에서 FK로 데이터 검증

💭 마무리

핵심은 "데이터 무결성"과 "성능" 사이의 트레이드오프를 서비스 특성에 맞게 조율하는 것입니다.

DBA는 서비스의 QPS, 데이터 중요도, 개발 편의성 등을 종합적으로 고려하여 최적의 전략을 수립해야 합니다.

기업의 서비스 규모, QPS 등 다양한 관리 포인트에 따라서 Foreign Key 설정 유무가 달라질 것으로 보이기 때문에 DBA는 이러한 요소를 고려하여 데이터베이스를 관리할 수 있어야 한다고 생각합니다.

💡 핵심 포인트

▶ Foreign Key 성능 이슈의 진짜 원인은 Validation 검사가 아닌 Lock 전파 ▶ FK 컬럼에 인덱스 설정은 필수 (Full Table Scan 방지 + Lock 최소화) ▶ 고동시성 환경에서는 FK로 인한 데드락 리스크를 반드시 고려해야 함 ▶ DBMS별 Lock 메커니즘 차이를 이해하고 서비스 특성에 맞는 전략 수립 필요

최근 지인으로부터 쿼리 튜닝 요청을 받았습니다.
15초 걸리던 쿼리를 0.6초로 줄이는 과정에서,
많은 엔지니어들이 놓치기 쉬운 SQL 최적화 포인트들을 정리해보았습니다.

쿼리 소개

※ 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다!

1️⃣ 회원가입 7일, 14일된 A지역 회원 조회 쿼리

특이사항: 일자는 서버에서 변수로 넘겨줄 예정이며, 7일차 14일차 따로따로 쿼리 수행 예정

SELECT user_id, name
  FROM user
 WHERE area = 'A'
   AND DATE(created_at) = @created_at
; -- 454건 / 556ms

2️⃣ 회원가입 30일, 90일된 A지역 회원 중, 서비스를 한번도 이용하지 않은 회원 조회 쿼리

특이사항: 일자는 서버에서 변수로 넘겨줄 예정이며, 30일차 90일차 따로따로 쿼리 수행 예정

SELECT a.user_id, a.user_name
 FROM user a
 LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND DATE(a.created_at) = @created_at
  AND b.user_id IS NULL
; -- 9건 / 378ms

3️⃣ 미로그인 45일차 A지역 회원 중, 무료 서비스 플랜을 이용하거나 서비스를 한번도 이용하지 않은 회원 조회 쿼리

SELECT DISTINCT a.email, a.user_name
FROM user a
LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND DATE(a.last_login_time) = @last_login_time
  AND (b.user_id IS NULL OR b.plan = 'free')
; -- 23건 / 14.786s

(2), (3) 번 SQL문에서 Anti-Join 도 잘 작성했으며 이대로 배포한다면 서비스에는 전혀 문제가 없습니다.

그러나, (3) 번 SQL 문의 경우 23건을 조회하는 과정에서 약 15초 가까이 시간이 발생했는데
과연 고객은 15초라는 시간을 어떻게 생각할까요 ??

이처럼 고객이 겪을 불편함을 최소화 시키면서, 데이터베이스의 부담을 최소화 하기 위해서는 쿼리 튜닝 작업이 필수적으로 선행되어야 합니다.

📊 테이블 구조 확인

쿼리 튜닝을 진행하기 전, 조회에 필요한 테이블의 구조를 확인해보겠습니다.

▶ user 테이블

SHOW CREATE TABLE user;

CREATE TABLE user(
  user_id INT(11) NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  area VARCHAR(10) NOT NULL,
  last_login_time DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(user_id),
  KEY idx_user_name (user_name),
  KEY idx_last_login_time (last_login_time)
);

▶ service 테이블

SHOW CREATE TABLE service;

CREATE TABLE service(
  service_id INT(11) NOT NULL AUTO_INCREMENT,
  user_id INT(11) NOT NULL,
  plan VARCHAR(10) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(service_id),
  UNIQUE KEY uk_user_id (user_id),
  KEY idx_created_at (created_at)
);

⚠️ SQL문 문제점 분석

위의 테이블 구조를 바탕으로 작성된 SQL문의 문제점을 정리하여 적어보았습니다.

🔍 주요 문제점

1. DATE() 함수 사용으로 인한 인덱스 미사용

WHERE DATE(created_at) = @created_at

컬럼에 함수를 적용하면 MySQL 옵티마이저는 해당 컬럼의 인덱스를 활용할 수 없습니다.

왜 인덱스를 사용하지 못할까요?

인덱스는 원본 컬럼 값을 기준으로 정렬되어 저장됩니다. 하지만 DATE() 함수를 사용하면

• created_at 컬럼의 원본 값(DATETIME)이 변환되어
• 인덱스에 저장된 값과 비교할 수 없게 됩니다
• 결과적으로 MySQL은 Full Table Scan을 수행하게 됩니다

예시

-- 인덱스 사용 불가 ❌
WHERE DATE(created_at) = '2025-01-01'

-- 인덱스 사용 가능 ✅
WHERE created_at >= '2025-01-01' 
  AND created_at < '2025-01-02'

실행계획으로 확인하면

-- DATE() 함수 사용 시
EXPLAIN SELECT * FROM user WHERE DATE(created_at) = '2025-01-01';
-- type: ALL (전체 테이블 스캔)
-- key: NULL (인덱스 미사용)

-- 범위 조건 사용 시
EXPLAIN SELECT * FROM user 
WHERE created_at >= '2025-01-01' 
  AND created_at < '2025-01-02';
-- type: range (범위 스캔)
-- key: idx_created_at (인덱스 사용)

2. BETWEEN 사용 시 주의사항

BETWEEN은 그 자체로 문제가 되는 것은 아닙니다. 다만 사용 방식에 따라 성능이 달라질 수 있습니다:

-- ✅ 인덱스 활용 가능
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31'

-- ⚠️ 타입이 일치하지 않으면 묵시적 형변환 발생 가능
WHERE created_at BETWEEN 20250101 AND 20250131  -- 숫자형과 DATETIME 비교

권장 방식: 명시적인 범위 조건 사용

WHERE created_at >= '2025-01-01' 
  AND created_at < DATE_ADD('2025-02-01', INTERVAL 1 DAY)

3. LIKE 사용 시 인덱스 활용 여부

• LIKE 'keyword%' (앞 일치): 인덱스 Range Scan 가능 ✅
• LIKE '%keyword' (뒷 일치): Full Table Scan 발생 ❌
• LIKE '%keyword%' (중간 일치): Full Table Scan 발생 ❌

🔧 쿼리 튜닝 결과

1️⃣ 회원가입 7일, 14일된 A지역 회원 조회 쿼리

AS-IS

SELECT user_id, user_name
FROM user
WHERE area = 'A'
  AND DATE(created_at) = @created_at
; -- 454건 / 556ms

TO-BE

-- 인덱스 추가
ALTER TABLE user ADD KEY idx_created_at (created_at);
-- 154ms

-- 개선된 쿼리
SELECT user_id, user_name
FROM user
WHERE area = 'A'
  AND created_at >= @created_at
  AND created_at < DATE_ADD(@created_at, INTERVAL 1 DAY)
; -- 454건 / 132ms

2️⃣ 회원가입 30일, 90일된 A지역 회원 중, 서비스를 한번도 이용하지 않은 회원 조회 쿼리

AS-IS

SELECT a.user_id, a.user_name
FROM user a
LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND DATE(a.created_at) = @created_at
  AND b.user_id IS NULL
; -- 9건 / 378ms

TO-BE

SELECT a.user_id, a.user_name
FROM user a
LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND a.created_at >= @created_at
  AND a.created_at < DATE_ADD(@created_at, INTERVAL 1 DAY)
  AND b.user_id IS NULL
; -- 9건 / 62ms

3️⃣ 미로그인 45일차 A지역 회원 중, 무료 서비스 플랜을 이용하거나 서비스를 한번도 이용하지 않은 회원 조회 쿼리

AS-IS

SELECT DISTINCT a.email, a.user_name
FROM user a
LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND DATE(a.last_login_time) = @last_login_time
  AND (b.user_id IS NULL OR b.plan = 'free')
; -- 23건 / 14.786s

TO-BE

SELECT a.email, a.user_name
FROM user a
LEFT JOIN service b ON a.user_id = b.user_id
WHERE a.area = 'A'
  AND a.last_login_time >= @last_login_time
  AND a.last_login_time < DATE_ADD(@last_login_time, INTERVAL 1 DAY)
  AND (b.user_id IS NULL OR b.plan = 'free')
GROUP BY a.email, a.user_name
; -- 23건 / 664ms

📈 성능 개선 결과

⚠️ 주의사항

💡 user 테이블의 created_at 컬럼에 대한 인덱스를 생성함으로써 엄청난 기대효과를 확인할 수 있었습니다.

그러나, 이렇게 인덱스를 추가할 경우 기존에 사용하던 SQL 문의 실행계획이 변경될 수 있기 때문에 신중할 필요가 있으며 사전에 서비스에 사용되는 쿼리를 전수 조사하여 실행계획을 확인할 필요가 있습니다.

💡 핵심 포인트

▶ 인덱스 컬럼은 절대 가공하지 않기 - 함수 사용 시 인덱스 활용 불가
▶ 범위 조건 활용 - DATE() 대신 >=, < 비교 연산자 사용
▶ 적절한 인덱스 설계 - 자주 조회되는 컬럼에 인덱스 생성
▶ 실행계획 확인 - 인덱스 추가 시 전체 서비스 영향도 검토 필수

오늘의 주제는 “Replication” 입니다.

지난번에는 MySQL 에서 기본적으로 제공하는 데이터베이스 이중화 작업에 대해서 간단하게 셋팅하는 작업을 진행했다면,

오늘은 MySQL Replication 에서도 가장 기본적인 Master / Slave 구성을 셋팅하고, Cold-Backup / Hot-Backup 백업 방식을 통한 동기화 작업을 진행해보려고 합니다.

그전에 간단하게 Cold-Backup 과 Hot-Backup 용어에 대해서 알아볼까요?

Cold-Backup

• 데이터베이스 서버를 완전히 종료한 후, 수행하는 백업 방식
• 서버의 다운타임이 발생하기 때문에 가용성을 포기해야 하며, 작업을 진행하는 동안 사용자에게 서비스를 제공할 수 없는 백업 방식

Hot-Backup

• 데이터베이스 서버를 종료하지 않고, 서비스가 동작하는 상황에서 수행하는 백업 방식
• 가용성을 지키고, 서비스 사용자에게 미칠 수 있는 영향도를 최소화할 수 있는 백업 방식

🚨 데이터베이스 초기 구축 단계부터 이중화를 구성할 경우 문제가 없겠지만, 대부분의 경우 비용이 2배로 발생하기 때문에 서비스의 규모가 커져 인프라가 중요시 될 때, 이중화를 고려하곤 합니다. 데이터가 충분한 상황에서 이중화를 구성해야 하는 상황이라면 무조건 마스터DB 데이터를 백업한 뒤, 백업 데이터를 이용하여 슬레이브DB 서버를 구축해야만 합니다.

첫 번째로는 마스터DB Cold-Backup 방식을 통한 이중화 구성 작업을 시작해보겠습니다.

Cold-Backup

• Master DB
  • HOST: mysql-master
  • PORT: 3306
  • USER: repl_user
  • PASSWORD: mysql1234
• Slave DB
  • HOST: mysql-slave
  • PORT: 3307
  • USER: repl_user
  • PASSWORD: mysql1234

1. Master DB 서버 데이터 확인

    mysql> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | test               |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    5 rows in set (0.08 sec)
   
    mysql> use test;
   
    Database changed
    mysql> SHOW TABLES;
    +----------------+
    | Tables_in_test |
    +----------------+
    | test01         |
    | test02         |
    | test03         |
    +----------------+
    3 rows in set (0.01 sec)

2. Master DB 서버 백업

    mysqldump -u root -p --master-data=1 
    --no-autocommit=1 
    --single-transaction=1 
    --extended-insert=1 
    --databases test > test.sql && cat test.sql | grep "CHANGE MASTER"

   • --master-data: 덤프 파일에 CHANGE TO 구문 생성 (option 1: 구문 생성, option 2: 구문 생성(주석 처리)
   • --no-autocommit: Auto Commit 여부 (option 1: auto_commit 을 끄고 테이블 입력 후 commit)
   • --single-transaction: 작업 후, 변경된 데이터의 내역을 다시 적용하지 않음
   • --extended-insert: INSERT 구문이 늘어나는 것을 방지
   • --databases: 특정 데이터베이스만 백업

🚨 Aurora MySQL 서버의 경우, master-data 옵션을 사용할 수 없도록 설정되어 있으니 주의해야 합니다.

3. Master → Slave 데이터 복구

    # 데이터 복구
    mysql -u root -p < ./test.sql

4. Slave DB 서버 Replication 설정

    STOP SLAVE ;
    CHANGE MASTER TO 
    master_host='mysql-master', 
    master_user='repl_user', 
    master_password='mysql1234', 
    master_log_file='mysql-bin.000004', 
    master_log_pos=605470 ;
    START SLAVE ;
   
    mysql> show slave status \G ;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: mysql-master
                      Master_User: repl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 661826
                   Relay_Log_File: 5a9b1d8f8507-relay-bin.000002
                    Relay_Log_Pos: 56676
            Relay_Master_Log_File: mysql-bin.000004
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 661826
                  Relay_Log_Space: 56890
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 5d485bcf-c6f9-11ed-bae5-0242ac110002
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)

Cold Backup의 경우 서비스를 중지한 상황에서 작업이 진행되기 때문에 작업 난이도가 매우 낮고 안전하지만, 해당 시간 동안 서비스를 고객에게 제공할 수 없기 때문에 사전 점검 안내가 필수적으로 이루어져야 합니다.

다음은 가용성을 지키고, 서비스 사용자에게 미칠 수 있는 영향도를 최소화할 수 있는 Hot-Backup 방법입니다.

Hot-Backup

• Master DB
  • HOST: mysql-master
  • PORT: 3306
  • USER: repl_user
  • PASSWORD: mysql1234
• Slave DB
  • HOST: mysql-slave
  • PORT: 3307
  • USER: repl_user
  • PASSWORD: mysql1234

1. 100 millisecond 마다 Master DB 서버로 데이터를 적재하는 상황 연출

    #!bin/bash
   
    echo "MySQL Replication TEST"
   
    LOOP=100000
    NUMBER=0
    while [ $NUMBER -le $LOOP ]
    do
        mysql -u root -pmysql1234 test -e "INSERT INTO doznut.test01(name) VALUES ('MySQL')"
        echo "number: ${NUMBER}"
        ((NUMBER++))
   
        sleep 0.1
    done
   
    bash test.sh

2. Master DB 실시간 데이터 확인

    mysql> SELECT * FROM test.test01 ORDER BY idx DESC LIMIT 5;
    +------+---------+---------------------+
    | idx  | name    | created_at          |
    +------+---------+---------------------+
    | 1000 | MySQL   | 2023-05-20 06:23:12 |
    |  999 | MySQL   | 2023-05-20 06:23:12 |
    |  998 | MySQL   | 2023-05-20 06:23:12 |
    |  997 | MySQL   | 2023-05-20 06:23:12 |
    |  996 | MySQL   | 2023-05-20 06:23:11 |
    +------+---------+---------------------+
    5 rows in set (0.03 sec)
   
    mysql> SELECT COUNT(*) FROM test.test01;
    +----------+
    | COUNT(*) |
    +----------+
    |     1020 |
    +----------+
    1 row in set (0.07 sec)

3. Master DB 서버 백업

    mysqldump -u root -p --master-data=1 
    --no-autocommit=1 
    --single-transaction=1 
    --extended-insert=1 
    --databases test > test.sql && cat test.sql | grep "CHANGE MASTER"

4. Master → Slave 데이터 복구

    # 데이터 복구
    mysql -u root -p < ./test.sql

5. Slave DB 서버 Replication 설정

    STOP SLAVE ;
    CHANGE MASTER TO 
    master_host='mysql-master', 
    master_user='repl_user', 
    master_password='mysql1234', 
    master_log_file='mysql-bin.000004', 
    master_log_pos=605470 ;
    START SLAVE ;
   
    mysql> show slave status \G ;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: mysql-host
                      Master_User: repl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 661826
                   Relay_Log_File: 5a9b1d8f8507-relay-bin.000002
                    Relay_Log_Pos: 56676
            Relay_Master_Log_File: mysql-bin.000004
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 661826
                  Relay_Log_Space: 56890
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 5d485bcf-c6f9-11ed-bae5-0242ac110002
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)

6. Slave 서버 데이터 확인

    mysql> SELECT * FROM test.test01 ORDER BY idx DESC LIMIT 5;
    +------+---------+---------------------+
    | idx  | name    | created_at          |
    +------+---------+---------------------+
    | 2540 | MySQL   | 2023-05-20 06:27:54 |
    | 2539 | MySQL   | 2023-05-20 06:27:54 |
    | 2538 | MySQL   | 2023-05-20 06:27:53 |
    | 2537 | MySQL   | 2023-05-20 06:27:53 |
    | 2536 | MySQL   | 2023-05-20 06:27:53 |
    +------+---------+---------------------+
    5 rows in set (0.03 sec)
   
    mysql> SELECT COUNT(*) FROM test.test01;
    +----------+
    | COUNT(*) |
    +----------+
    |     2594 |
    +----------+
    1 row in set (0.04 sec)

💡 오늘은 Cold Backup & Hot Backup 방식을 통한 데이터베이스 이중화 작업을 진행해보았습니다.
위의 작업처럼 데이터 용량이 크지 않을 경우라면 mysqldump 명령어를 통해 빠르게 Slave DB 서버로 복원 및 이중화 구성을 셋팅할 수 있습니다. 그러나, 데이터 용량이 100GB, 500GB, 1TB 이상으로 커진 서버를 mysqldump 명령어를 통해 백업한다면 작업 시간을 예상할 수 없을 것입니다.
대용량 데이터를 백업하는 경우에는 Percona 사에서 제공하는 Xtrabackup Toolkit을 이용하여 빠르게 백업하고 복구할 수 있습니다.

오늘의 주제는 “Replication” 입니다.

Replication 이란 ??

• 2대 이상의 별도 서버에 DBMS를 나누어 데이터를 저장하는 방식
• 사용하기 위한 최소 구성은 Master / Slave 구성

MySQL에서는 Replication 기능을 기본적으로 제공하고 있습니다. 그로 인해 많은 기업에서는 On-Premises MySQL 사용 시, 이중화 구성을 통해 데이터를 보호하고 있습니다.

오늘은 MySQL Replication 에서도 가장 기본적인 Master / Slave 구성을 세팅하는 작업을 진행해 보겠습니다.

[MySQL] 데이터 복제 동작 원리

기본적으로 Master 서버에서 데이터 변경이 발생하면 Binary Log 에 해당 내용이 기록됩니다. 내용 기록이 완료된 시점에서 Master 서버는 Slave 서버로 해당 Binary Log 내용을 전달하게 되며, Slave 서버에서는 전달받은 내용을 Relay Log 에 기록한 뒤, 해당 내용을 읽어 데이터 동기화를 진행합니다.

1. Master 서버 데이터 변경 > 바이너리 로그 저장
2. Master 서버에서 Slave 서버로 바이너리 로그 파일 전달
3. 전달받은 바이너리 로그 파일을 Slave 서버에서는 Relay Log 파일에 저장한 후 데이터 동기화 진행

MySQL 이중화 구성 (환경 세팅)

이번 게시글에서는 도커 컨테이너를 사용해 빠르게 이중화를 구성합니다.

• 운영환경: Docker / MySQL 5.7
• 마스터DB
  • PORT: 3306
• 슬레이브DB
  • PORT: 3307

1. Docker Container 세팅

    # MySQL 5.7 이미지 다운로드 (이미지가 없을 경우, run 명령어를 통해 자동 다운로드 가능)
    docker pull mysql:5.7
   
    # 도커 네트워크 생성 (Master - Slave 서버간의 통신을 위한 내부망 생성)
    docker network create mysql-network
   
    # Master 서버 생성
    docker run -d -p 3306:3306 \
    --platform linux/amd64 \
    -v /home/mysql_user/mysql/data:/var/lib/mysql \
    -v /home/mysql_user/mysql/my.cnf:/etc/my.cnf \
    -e MYSQL_ROOT_PASSWORD=mysql1234 \
    --network mysql-network \
    --name mysql-master mysql:5.7
   
    # Slave 서버 생성
    docker run -d -p 3307:3307 \
    --platform linux/amd64 \
    -v /home/mysql_user/mysql_slave/data:/var/lib/mysql \
    -v /home/mysql_user/mysql_slave/my.cnf:/etc/my.cnf \
    -e MYSQL_ROOT_PASSWORD=mysql1234 \
    --network mysql-network \
    --name mysql-slave mysql:5.7

2. my.cnf 파일 수정

    # log-bin 옵션의 경우 사용자 별도의 이름 지정 가능
   
    # Master 서버
    [mysqld]
    port      = 3306
    log-bin   = mysql-bin
    server-id = 1
   
    # Slave 서버
    [mysqld]
    port      = 3307
    log-bin   = mysql-bin
    server-id = 2

3. my.cnf 파일 권한 수정 및 서버 재시작

    # 처음 컨테이너를 생성하여 MySQL 서버를 띄웠을 경우,
    # 마운트한 로컬 my.cnf 파일을 적용하지 않기 때문에 사용자 권한을 수정한 뒤 서버 재시작
   
    # Master & Slave
    chmod 644 /etc/my.cnf
   
    docker restart mysql-master mysql-slave

4. [Master] 테스트 데이터베이스 및 테이블 생성

    CREATE DATABASE repl;
   
    CREATE TABLE repl.test01 (
    `idx` INT AUTO_INCREMENT NOT NULL COMMENT 'Primary KEY',
    `name` VARCHAR(30) NOT NULL COMMENT 'User Name',
    `wtime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Data Insert Time',
    PRIMARY KEY(idx),
    KEY (`name`),
    KEY (`wtime`)
    ) COMMENT='무중단 REPLICATION TEST TABLE'
    ;

MySQL 이중화 구성 (서버 연결)

• 마스터DB
  • HOST: mysql-master
  • PORT: 3306
  • User: repl_user
  • PW: mysql1234
• 슬레이브DB
  • HOST: mysql-slave
  • PORT: 3307
  • User: repl_user
  • PW: mysql1234

# Master DB & Slave DB
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'mysql1234' ;
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' ;
FLUSH PRIVILEGES;

이중화 전용 서비스 계정에 부여되는 권한을 주의해야 합니다. 권한을 최소화하여 보안을 신경쓰는 습관이 필요합니다.

1. 마스터DB 서버 정보 확인

    # Master DB
    # File + Position 기록
    mysql> SHOW MASTER STATUS \G;
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 154
         Binlog_Do_DB:
     Binlog_Ignore_DB:
    Executed_Gtid_Set:
    1 row in set (0.00 sec)

2. Master - Slave 연결

    # Slave DB
    change master to
    master_host='mysql-master', 
    master_user='repl_user', 
    master_password='mysql1234', 
    master_log_file='mysql-bin.000001', 
    master_log_pos=154 ;
   
    # Slave 정보 확인
    mysql> SHOW SLAVE STATUS \G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: mysql-master
                      Master_User: repl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 154
                   Relay_Log_File: b18a27af7131-relay-bin.000001
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 154
                  Relay_Log_Space: 534
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 5d485bcf-c6f9-11ed-bae5-0242ac110002
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.01 sec)

   • Replication 정상 연결 확인
     • Slave_IO_Running: Yes
       • 마스터 바이너리 로그를 읽기 위한 I/O 스레드가 실행 중인지 여부
       • 아직 복제를 시작하지 않았거나 복제를 중지(STOP SLAVE)한 경우 NO 로 표시
     • Slave_SQL_Running: Yes
       • 릴레이 로그에서 이벤트를 실행하기위한 SQL 스레드가 실행 중인지 여부
       • 로그상태나 복제 상태에 문제가 있을 경우 NO 로 표시
       • 상태가 “NO” 일 경우, Last_Error 내용을 참고하여 해결
     • Seconds_Behind_Master: 0
       • Slave 서버의 SQL 스레드가 Master 서버의 바이너리 로그를 처리하는 시간 (초)
       • 숫자가 지속적으로 늘어날 경우, 해당 현상에 대해서 원인 파악이 중요하며, 순간적인 데이터 로드가 많거나 성능상에 문제는 없는지 확인해야 함

💡 오늘은 MySQL 에서 기본적으로 제공하는 데이터베이스 이중화 작업을 진행해 보았습니다.
서비스의 초기 구축 과정이라면 위의 방법만으로 쉽게 이중화를 구성할 수 있겠지만, 대부분의 기업에서는 서비스 운영 과정에서 이중화 구성을 진행하게 될 것입니다.
이러한 경우 무조건 마스터DB 서버의 데이터를 백업하고, 백업본을 통해 슬레이브DB 서버를 구축해야만 합니다.
다음 시간에는 서비스 운영 과정 과정에서 이중화 구성을 어떻게 진행하는지 살펴보겠습니다.

오늘의 주제는 “mysqlbinlog” 입니다.

mysqlbinlog는 MySQL에서 binary log 를 통해 데이터를 추적하거나, 복구할 수 있도록 제공하는 유틸리티 도구입니다.

앞서 MySQL 바이너리 로그에 대해서 다뤄 보았는데요, 오늘은 바이너리 로그를 활용해 데이터를 복구하는 작업을 다뤄보려고 합니다.

MySQL 바이너리 로그 파일을 이용한 데이터 복구

• 바이너리 로그는 DDL & DML 명령문을 통해 데이터의 변화가 발생할 경우 해당 이벤트를 기록하는 로그 파일
• 바이너리 로그의 내용을 기반으로 데이터 복제(Replication) 또는 데이터 복원(Recovery) 작업 가능
• 바이너리 형태로 기록되기 때문에 일반 편집기를 통해서는 내용 확인하기 어려우며, mysqlbinlog 유틸리티를 통해 확인 가능

모든 바이너리 로그 파일을 mysqlbinlog 유틸리티 도구를 통해 추출할 수 있으며, 특정 바이너리 로그 파일만 추출할 수 있습니다.

mysqlbinlog 유틸리티 사용법

• 모든 바이너리 로그 파일 추출

    # mysqlbinlog /var/lib/mysql/binlog.0* > /home/test/all_data.sql

• 특정 바이너리 로그 파일 추출

    # mysqlbinlog /var/lib/mysql/binlog.000001 > /home/test/bin_000005.sql

• 주요 옵션

| —database=[DB_NAME] | -d | **특정 데이터베이스의 내용만을 출력** |
| --- | --- | --- |
| —short-form | -s | 일반 쿼리만 출력, 모든 정보 출력을 원하지 않을 때 사용 |
| —start-datetime=”YYYY-MM-DD hh:mm:ss” |  | 지정한 시간부터 binlog 추출 |
| —stop-datetime=”YYYY-MM-DD hh:mm:ss” |  | 지정한 시간까지 binlog 추출 |
1. 모든 DB 복구

    ```bash
    # mysqlbinlog /var/lib/mysql/binlog.0* > /home/test/all_data.sql
    # mysql -u root -p < /home/test/all_data.sql
    ```

2. 특정 DB 복구

    ```bash
    # mysqlbinlog /var/lib/mysql/binlog.0* -d mydb > /home/test/mydb.sql
    # mysql -u root -p < /home/test/mydb.sql
    ```

3. 특정 시점 복구

    ```bash
    # mysqlbinlog /var/lib/mysql/binlog.0* \
      —-start-datetime="2023-02-01 00:00:00" \
      --stop-datetime="2023-03-01 00:00:00" \
     > /home/test/2023-02.sql
    # mysql -u root -p < /home/test/2023-02.sql
    ```

💡 작업 실수로 인해 특정 데이터가 변경되거나 지워질 경우, mysqlbinlog 유틸리티 도구를 통해서 해당 데이터를 추적하고, 복구하여 데이터를 원복할 수 있기 때문에 mysqlbinlog 유틸리티는 꼭 알아두면 좋은 소프트웨어입니다.

오늘의 주제는 “MySQL 바이너리 로그” 입니다.

MySQL에서 빈로그 혹은 바이너리 로그라고 불리는 로그 파일은 DDL & DML 명령문을 통해서 데이터의 변화가 발생할 경우 해당 이벤트를 기록하는 로그 파일입니다. 바이너리 로그 파일은 기본적으로 Transaction Commit 시점에 기록되며, 데이터 변경 순서를 보장해줍니다.

주로 복제(Replication) & 복구(Recovery) 를 목적으로 사용되며 데이터 복제 시에는 Master 서버에서 바이너리 로그 파일을 Slave 서버로 전달하고, Slave 서버에서는 전달받은 바이너리 로그 파일을 읽어 Master 서버와 데이터를 동기화를 진행합니다.
오늘은 MySQL에서 사용하는 바이너리 로그에는 다양한 로그 작성 방식을 알아보려 합니다.

MySQL Binary log 종류

1. Statement-based logging
   • 데이터 변경 작업을 수행하는 SQL(INSERT, UPDATE, DELETE) 문을 바이너리 로그에 저장

** - 백업 & 복구 작업에 대해서 빠른 속도로 수행 가능** 2. Row-based logging - SQL(INSERT, UPDATE, DELETE) 문에 의해 개별 테이블 행이 어떻게 영향을 받는지를 로그에 저장 - 각 행의 변경 사항을 기록하기에 로그 파일의 크기가 매우 빠르게 증가하고, 복제 지연 시간 증가될 수 있음 - 임시 테이블의 경우 해당 로깅 방식에서는 복제하지 않기 때문에 SBR 방식 사용 3. Mix of both statement-based and row-based logging - MySQL 5.1 이상 지원 - 기본적으로 Statement based logging 방식을 통해 기록하며, 스토리지 엔진 및 특정 명령문에 따라 자동으로 Row based logging 방식으로 기록

💡 MySQL에서 바이너리 로그 파일을 기록하는 방식은 위에세 소개한 3가지 방식이 있습니다. Row-based logging 방식이 MySQL 의 표준으로 제공되고 있지만, 각각의 방식에 장단점이 존재하기 때문에 자신의 상황에 맞는 기록 방식을 사용하는 것이 가장 바람직하다고 볼 수 있을 것 같습니다.

오늘의 주제는 “옵티마이저와 인덱스 힌트” 입니다.

관계형 데이터베이스는 기본적으로 인덱스가 생성된 컬럼이 아닌 일반 컬럼을 조건으로 SELECT 명령문을 수행할 경우 테이블 Full Scan 방식을 통해 데이터를 조회합니다.

그렇기 때문에 DBA는 엔지니어들의 쿼리 검수 과정에서 플랜을 확인하고 과도한 I/O가 발생하지는 않는지 확인하여 튜닝 작업을 진행해야 합니다.

DBA가 없는 경우 엔지니어들의 무분별한 인덱스 추가 작업으로 기존에 잘 동작하던 쿼리의 플랜이 깨져 Slow Query 가 발생하는 경우도 존재합니다.

DBA는 위와 같은 케이스가 존재하기 때문에라도 항상 사전 검수 후, 쿼리 튜닝 및 인덱스 추가 작업을 진행해야 합니다.

앞서 설명한 내용을 조금 더 자세히 설명하기에 앞서 우리는 “옵티마이저” 를 알아야 합니다.

Optimizer

• 사용자가 SQL문을 통해서 데이터를 조회하면 내부적으로 DBMS에 존재하는 옵티마이저가 실행계획을 생성하게 됩니다.
• 옵티마이저는 크게 2가지로 분류
  • RBO (Rule Based Optimizer)
    • 미리 정해둔 규칙대로 쿼리 수행
    • 인덱스가 존재할 경우 무조건 인덱스를 통해 수행
    • 예측이 가능하기에 설계 난이도가 낮지만, 그로 인해 쿼리 작성 난이도가 상승
  • CBO (Cost Based Optimizer)
    • 통계 정보(레코드 수, 인덱스 컬럼 개수 등)를 기반으로 옵티마이저가 생각하는 가장 효율적인 실행계획을 통해 쿼리 수행
    • 인덱스가 존재하더라도 Table Scan 방식 비용이 더 낮을 경우 Table Scan 수행
    • 예측이 힘들기 때문에 작성자가 생각한 실행계획가 달라 Slow Query 발생 가능성이 높음

옵티마이저 이외에도 많은 내용이 존재하지만, 해당 포스트에서는 맨처음 언급한 기존 플랜이 깨져 Slow Query 가 발생하는 경우에 대해 이야기를 하려고 합니다.

위 쿼리는 정상적인 인덱스 키를 통한 데이터 조회했을 때의 실행계획입니다.

기존 플랜이 깨져 Slow Query가 발생하는 경우는 다양합니다. 무분별한 인덱스 추가로 인해 옵티마이저가 적절한 인덱스가 아닌 엉뚱한 인덱스를 스캔하거나, 테이블 Full Scan 방식의 비용이 더 낮다고 판단하는 경우도 있을 것입니다.

이러한 경우 INDEX HINT 를 통해서 사용자가 원하는 인덱스를 옵티마이저가 스캔할 수 있도록 지정할 수 있습니다.

INDEX HINT

• SELECT 쿼리를 작성할 때 사용자가 원하는 인덱스를 옵티마이저가 채택할 수 있도록 도움을 주는 기능

• 특정 인덱스를 사용하고자 할 때 USE / FORCE / IGNORE 예약어 사용

  • USE INDEX

    • 옵티마이저에게 지정한 인덱스를 사용하라고 [권장]

    • 해당 옵션을 사용하면 옵티마이저는 다른 인덱스를 스캔하는 비용이 더 적다고 판단하거나, 테이블 Full Scan 비용이 더 적다고 판단하면 해당 방식을 통해 스캔합니다.

    • 작성 방법

        SELECT *
          FROM dept USE INDEX (`인덱스명`)
         WHERE deptno > 10
        ;

  • FORCE INDEX

    • 옵티마이저에게 지정한 인덱스를 사용하라고 [명령]

    • 해당 옵션을 사용하면 사용자가 지정한 인덱스보다 낮은 비용의 방식이 있더라도 무조건 지정된 인덱스를 통해 스캔합니다.

    • 작성 방법

        SELECT *
          FROM dept FORCE INDEX (`인덱스명`)
         WHERE deptno > 10
        ;

  • IGNORE INDEX

    • 지정한 인덱스 이외에 다른 인덱스만을 사용하도록 지정

    • 작성 방법

        SELECT *
          FROM dept IGNORE INDEX (`인덱스명`)
         WHERE deptno > 10
        ;

INDEX HINT를 사용하기 위해서는 다양한 부분을 고려해야 합니다. 특히나 FORCE INDEX 힌트의 경우 꼭 필요한 상황이 아닌 이상 권장하지 않는 방법입니다.

기본적으로 옵티마이저가 작성한 실행계획에 의해 조회 방식이 결정되기 때문에 FORCE INDEX 힌트는 예기치 못한 사이드 이펙트를 발생시킬 수 있다는 것을 명심해야 합니다.

오늘의 주제는 “Partition Exchange” 입니다.

파티션 테이블의 경우, 이전 게시글에서 다룬 내용이지만 오늘은 Partition Exchange 기능에 대해서 알아보려 합니다.

Partition Exchange

• MySQL 5.6 부터 지원되는 기능으로써 특정 파티선 삭제 시 발생하는 락이나 부하에 대한 최소화
• 일반 테이블을 특정 파티션으로 수정하고자 할 때 작업 영향도를 최소화

Partition Exchange 기능을 사용해야 하는 이유

• 파티션 테이블은 파티션마다 OS에서 .ibd 파일이 생성됨
• 파티션의 물리적인 파일과 내부 메타 데이터 갱신 정도의 작업에서 테이블 락 유지 시간이 길지는 않지만, 서비스에 따라 위험도가 높은 경우가 존재
• Partition Exchange 기능을 사용할 경우 OS 파일 삭제 과정 없이 메타 데이터만 변경하기 때문에 리소스 사용이 적으며, 소요 시간이 적다는 장점

select TABLE_NAME
     , PARTITION_NAME
     , TABLE_LOWS
  from infomation_schema.PARTITIONS
 where TABLE_SCHEMA = 'test'
   and TABLE_NAME = 'test01'
;
/*
+------------+----------------+------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+------------+----------------+------------+
| test01     | p_20221127     |     982053 |
| test01     | p_20221128     |       9922 |
| test01     | p_20221129     |          0 |
+------------+----------------+------------+
3 rows in set (0.01 sec)
*/

[root@905c91ef6de5 test]# ls -lh
total 199M
-rw-r----- 1 mysql mysql  92M Nov 27 05:46 test01#p#p_20221027.ibd
-rw-r----- 1 mysql mysql  10M Nov 27 05:44 test01#p#p_20221028.ibd
-rw-r----- 1 mysql mysql 144K Nov 27 05:44 test01#p#p_20221029.ibd
-rw-r----- 1 mysql mysql 144K Nov 27 05:59 test_1#p#p_20221028.ibd
-rw-r----- 1 mysql mysql 144K Nov 27 05:47 test_1#p#p_20221029.ibd
-rw-r----- 1 mysql mysql  96M Nov 27 05:59 st.ibd

• data 디렉토리에서 파일을 조회해보면 각 파티션별로 테이블 스페이스가 생성된 것을 확인할 수 있으며, 데이터가 가장 많았던 27일자 데이터(98만건)의 경우 약 92MB 정도의 크기를 사용하고 있습니다.
• DROP PARTITION 방식으로 27일 자의 데이터를 삭제했을 때와 PARTITION EXCHAGNE 방식을 사용했을 때를 비교해 보겠습니다.

# 1.
alter table test01 drop partition p_20221027
; -- 0.53s

# 2.
create table test02 like test01
;
alter table test02 remove partitioning
;
alter table test01 exchange partition p_20221027 with table test02
; -- 0.08s
alter table test01 drop partition p_20221027
; -- 0.07s

• DROP PARTITION 방식의 경우 약 0.53초의 시간이 소요됨을 확인할 수 있었으며, PARTITION EXCHAGNE 방식의 경우 약 6배 빠른 0.08초의 시간이 소요됨을 확인할 수 있었습니다.
• PARTITION EXCHAGNE 방식을 통해 데이터가 존재하지 않기 때문에 삭제 작업이 매우 빠른 것 또한 확인이 가능합니다.

PARTITION EXCHANGE 작업 순서

1. 임시 테이블 생성
   create table [temp_table_name] like [table_name] )
2. 임시 테이블 파티션 삭제
   alter table [temp_table_name] remove partitioning
   • 파티션을 삭제할 때는 remove / drop 명령어가 존재
   • remove partitioning: 파티션만 삭제하고 해당 데이터는 유지
   • drop partition: 파티션과 데이터 모두 삭제
3. 임시 테이블로 파티션 이동
   alter table [table_name] exchange partition [partition_name] with table [temp_table_name] )
4. 임시 테이블 삭제
   drop table [temp_table_name]

CREATE TABLE AS SELECT 구문을 사용하면 안되는 이유!

1. CREATE TABLE test.temp (
   idx int(11) NOT NULL,
   name varchar(50) NOT NULL,
   PRIMARY KEY(idx),
   UNIQUE KEY(name)
);
2. CREATE TABLE test.temp_a AS SELECT * FROM test.temp ;
3. CREATE TABLE test.temp_b LIKE test.temp ;
4. SHOW CREATE TABLE test.temp_a ;
   CREATE TABLE test.temp_a (
       idx int(11) NOT NULL,
       name varchar(50) NOT NULL
   );
5. SHOW CREATE TABLE test.temp_b ;
   CREATE TABLE test.temp_b (
       idx int(11) NOT NULL,
       name varchar(50) NOT NULL,
       PRIMARY KEY(idx),
       UNIQUE KEY(name)
    );

create table like 방식과 달리 create table as select 방식은 기존 테이블의 키를 가져오지 않습니다.
exchange partition 사용 시, 두개의 테이블 구조가 모두 동일하지 않으면 에러 발생하기 때문에 create table like 방식을 통해서 구조가 동일한 테이블을 생성해야 합니다.

🚨 마지막 파티션(1개 이하) 테이블에 [REMOVE | DROP] PARTITION 명령어를 사용할 경우 Cannot remove all partitions, use DROP TABLE instead 에러가 발생한다. 자세한 원인은 알 수 없으나, MySQL의 오류가 아닐까 싶네요.
<해결방법>

ALTER TABLE [TABLE NAME] REMOVE PARTITIONING ;

파티션별 데이터 로우 확인 쿼리

select TABLE_SCHEMA
     , TABLE_NAME
     , PARTITION_NAME
     , PARTITION_ORDINAL_POSITION
     , TABLE_ROWS
  from information_schema.partitions
 where TABLE_SCHEMA = ''
   and TABLE_NAME = ''
;

💡 오늘은 파티션 테이블에 존재하는 데이터를 Partition Exchange 기능을 통해 안전하게 삭제하는 법을 알아보았습니다. Partition Exchange 기능을 통해서 파티션 테이블의 데이터를 삭제하더라도 항상 서버의 상황을 고려하여 최대한 안전한 상황에서 작업을 진행해야 한다는 것을 잊지 말아야 합니다.

오늘의 주제는 “파티션 테이블” 입니다.

하나의 테이블에서 반복적인 삭제 작업을 하게 된다면 해당 테이블에 SELECT하거나 INSERT 할 때 성능 저하가 발생한다는 내용을 다뤄보려고 합니다.

아래의 그림은 테이블에 존재하던 데이터를 삭제한 후의 모습입니다.

테이블의 데이터를 삭제하게 되면 더 이상 해당 데이터가 존재하지 않기 때문에 대부분의 사용자는 삭제 작업 과정에서 데이터가 완전히 지워지고 해당 공간마저도 반납된다고 생각하게 되지만,

실제 DBMS 내부에서는 데이터를 삭제하더라도 삭제된 데이터가 사용하던 공간은 반납하지 않습니다.

그렇기 때문에 반복적인 삭제 작업을 수행한 테이블을 조회하면 아래의 그림처럼 DBMS는 HWM 위치까지 스캔하고 결과집합을 리턴합니다.

이러한 반복적인 삭제 작업이 발생하는 테이블에서 성능 저하를 줄이기 위해서 이번에는 파티션에 대한 내용을 다뤄보겠습니다.

Partition(파티션) 이란?

• 파티션이란 하나의 테이블에서 모든 데이터를 관리하지 않고, 내부적으로 하나의 테이블을 여러개로 쪼개어 데이터를 관리하는 방식입니다.
• 사용자는 하나의 테이블에 읽기와 쓰기를 수행하지만 내부적으로는 쪼개진 여러 테이블에서 읽기/쓰기 작업을 수행하게 됩니다.

파티션에는 세 종류(List Partition, Range Partition, Hash Partition)가 존재합니다. 각각의 파티션별 장단점이 존재하지만 오늘은 Range Partition 에 대한 내용을 다뤄보려 합니다.

파티션을 사용하는 이유

1. DML 작업의 성능 증가
   • 인덱스를 이용한 테이블 액세스 비용은 데이터양이 늘고 추출 건수가 많아질수록 기하급수적으로 증가하게 됩니다.
   • 파티션 테이블을 이용한다면 각각의 파티션 테이블에 생성된 독립적인 인덱스를 통해 빠른 작업이 가능해집니다.

     MySQL에서는 각각의 파티션에 로컬 인덱스가 생성되기 때문에 파티션의 키는 무조건 PK 혹은 UK로 설정되어 있어야 합니다.

2. 주기적인 삭제 작업이 존재하는 테이블의 경우 효율적인 관리가 가능
   • 비즈니스 로직 혹은 제도화 된 법에 의해 필수 보관 주기가 지난 데이터의 경우 일반적인 DML(DELETE) 작업으로 처리할 시 많은 성능 저하 발생합니다.
     1. HMW (High Water Mark) 로 인한 불필요 Disk I/O 발생
     2. TABLE LOCK
   • 이럴 경우, 파티션 단위로 데이터를 삭제하여 불필요한 리소스를 사용하지 않고 처리가 가능합니다.
3. 데이터의 물리적인 저장소 분리
   • 파티션을 통해 파일 크기 조절, 저장 위치 등을 지정 / 저장할 수 있게 됩니다.
     

파티션 생성 쿼리

# MySQL
create table emp_log (
    emp_no int not null auto_increment,
    hire_date datetime not null default now(),
    emp_name varchar(30) not null,
    primary key (emp_no, hire_date),
    key (hire_date)
) partition by range (columns(hire_date)) (
    PARTITION p_202212 VALUES LESS THAN ('2023-01') ENGINE = InnoDB,
    PARTITION p_202301 VALUES LESS THAN ('2023-02') ENGINE = InnoDB,
    PARTITION p_202302 VALUES LESS THAN ('2023-03') ENGINE = InnoDB
);

파티션 테이블에서의 쿼리 처리 방법

읽기

• where 조건으로 파티션 키 컬럼이 사용될 경우 레코드가 저장된 파티션에서 빠르게 대상 레코드 조회 가능
• where 조건으로 파티션 키 컬럼이 사용되지 않는다면 레코드를 찾기 위해 모든 파티션을 스캔하고 이에 따른 부하 발생

쓰기

• 파티션 키인 "hire_date" 컬럼 값을 이용해 레코드가 저장될 파티션 결정
• 데이터 삽입 시, 저장될 파티션 선택 이외에는 일반 테이블에서의 삽입 과정과 동일

1. 파티션 선택 가능 + 인덱스 스캔
   • 가장 효율적인 처리 가능
2. 파티션 선택 불가 + 인덱스 스캔
   • 특정 파티션을 선택할 수 없기 때문에 모든 파티션에 대한 스캔이 필수적이며 인덱스 Range Scan 사용 또는 파티션 테이블의 수가 많을수록 성능 저하
3. 파티션 선택 가능 + 테이블 풀스캔
   • 특정 파티션에 대한 풀스캔 진행, 대상 파티션 테이블의 데이터 수에 의해 성능과 연결
4. 파티션 선택 불가 + 테이블 풀스캔
   • 특정 파티션을 선택할 수 없기 때문에 모든 파티션에 대한 스캔이 필수적이며 각 파티션별 풀스캔 진행, 가장 성능이 느리며 많은 부하 발생
     

파티션 테이블을 생성하더라도 조회 과정에서 파티션 테이블의 키를 명시하지 않으면 결국 모든 파티션 테이블을 조회하며 FULL SCAN 작업이 진행되므로 주의해야 합니다.

특히, MySQL을 사용하는 사용자라면 더욱더 주의가 필요합니다. MySQL 환경에서 Range Partition을 사용한다면 오라클과 같이 MAXVALUE 기능이 존재하지 않기 때문에 주기적인 관리가 중요합니다.

파티션 테이블을 사용하게 되면 그만큼 관리 포인트가 늘어나기 때문에 관리자의 입장에서는 장애 발생 가능성을 지닌 채 운영해야 합니다. 그렇기 때문에 파티션 테이블은 적절한 상황에서 적절한 테이블에 도입하여 관리하는 것이 중요합니다.

오늘의 주제는 “SQL 최적화” 입니다.

DBA 업무를 수행하면 데이터베이스와 관련된 모든 운영 업무를 맡게 됩니다. 많은 업무중에서도 오늘은 SQL 최적화에 대한 내용을 다뤄보려 합니다.

SQL 최적화, 쿼리 튜닝이라고도 불리는 해당 업무는 과도한 I/O가 발생하지 않는지(적절한 인덱스가 있음에도 불구하고 테이블 풀스캔을 하거나 다른 인덱스를 통한 스캔을 하지는 않는지) 확인하며, 옵티마이저가 최적의 효율로 데이터를 스캔할 수 있도록 SQL문을 최적화 하는 작업을 의미합니다.

아래의 쿼리는 제가 관리하던 데이터베이스에서 조회 권한을 할당받은 엔지니어가 1회성으로 다른 팀의 데이터 추출 작업을 위해 작성한 쿼리입니다.

해당 쿼리를 통해 여러 쿼리 튜닝 기법을 소개할 수 있을 것 같아 해당 쿼리를 소개하려 합니다.

# 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

select a.code
     , a.price
     , a.time as `datetime`
     , b.type
     , b.number
     , b.time as `request_time`
  from emp a
  left join dept b on a.code = b.code
  left join dept_detail c on b.dept_col = c.dept_col
 where a.code in (
                  select a.code
                       , max(a.emp_no)
                       , max(a.time) as `last_time`
                       , date(max(a.time)) as `last_date`
                       , count(*) as `total_count`
                    from emp a 
                    left join dept b on on a.code = b.code
                    left join dept_detail c on b.dept_col = c.dept_col
                   where a.status = 'wait'
                     and a.country = 'kr'
                     and b.type = 'extend'
                   group by a.code
                 ) extend on a.code = extend.code
   and total_count = 1
union
select a.code
     , 0 as price
     , b.create_date as `datetime`
     , 'unknown' as `type`
     , b.number
     , null as `request_time`
  from emp a
  left join dept b on a.code = b.code
 where a.country = 'kr'
   and b.version = 'v1'
   and b.col_num is null
   and date(b.create_date) between '2020-01-01' and '2023-01-01'
;

위에서 소개한 쿼리는 10분이 넘는 시간 동안 종료되지 않고 수행되던 슬로우 쿼리입니다.

엔지니어가 조회하고자 하는 emp, dept, dept_detail 테이블은 모두 데이블의 크기가 큰 무거운 테이블입니다. 이러한 조건을 고려했을 때 10~20분을 더 기다려도 위 쿼리에 대한 결과집합은 확인하기 어려울 것 같아 보이네요

1회성 쿼리이기 때문에 모든 항목에 대해 최적화를 진행하는 것보다는 작은 공수로 빠르게 스캔될 수 있도록 최적화 방안을 고민하게 되었는데요,

위 쿼리에는 여러 문제점이 존재합니다. 그중에서도 오늘은 크게 3가지 정도만 알아보고자 합니다.

1. 무분별한 OUTER JOIN

• 위 쿼리는 테이블 간의 관계, 테이블의 크기 등 다양한 상황을 고려하지 못한 채 테이블 간의 조인 방식을 Outer 방식을 사용했습니다.
• 쿼리를 작성할 때에는 각 테이블 간의 연관성, 테이블 크기, 적합한 인덱스 유무 등 다양한 항목에 대한 고려가 필요합니다.
• 테이블에 대한 정보를 전혀 모르고 무작정 조회하면 서버에 부하를 줄 수 있습니다.*
• 다른 테이블의 데이터를 참조해서 데이터를 불러오고자 할 때는 JOIN, IN, EXISTS 등 다양한 방법이 있지만 오늘은 JOIN 방식으로 빠르게 튜닝해보고자 합니다.

2. 비효율적인 방식으로 IN 연산자 사용

• IN 과 EXIST 은 WHERE절에서 사용되며, 조건에 따라 데이터를 걸러내고 결과를 조회할 때 사용한다는 공통점이 존재합니다. 그러나, 두 개의 연산자는 명확한 차이점이 존재합니다.

• 연산자(exists, in)	특징
  EXISTS	조건에 해당하는 데이터의 유무를 확인하여 결과값 리턴
  	처리 순서: 메인 쿼리 → 서브 쿼리 순서로서 서브 쿼리에서 메인 쿼리의 정보를 토대로 모든 조건을 한 번에 설정
  IN	조건에 해당하는 데이터의 컬럼과 비교하며 체크
  	SELECT 절의 조회 컬럼 값으로 비교하기 때문에 비교할 데이터가 많을수록 EXISTS에 비해 성능이 떨어짐
  	처리 순서: 서브 쿼리 → 메인 쿼리 순서로서 서브 쿼리가 메인 쿼리의 정보를 가져올 수 없기 때문에 조건을 각각 설정

• 이러한 차이점이 존재하기 때문에 IN 의 경우, 비교하고자 하는 데이터가 적거나, 상수 혹은 문자열 등 사용할 때 높은 효과를 볼 수 있습니다.

        SELECT a.code
          FROM test.emp a
         WHERE a.code in ('a', 'b', 'c', 'd', 'e', 'f')
        ;

3. UNION

• union은 다수의 select 결과값을 하나로 병합할 때 사용하는 방식입니다.
• union은 기본적으로 중복되는 데이터를 제거하고 결과값을 보여줍니다. 중복 데이터를 제거하는 과정에서 임시 테이블을 사용하기 때문에 성능이 매우 나쁩니다.

크게 3가지 + @ 문제점이 존재하여 쿼리 튜닝에 많은 시간을 소요해야 하는 상황이었으나, 빠르게 처리해야 하는 업무임을 엔지니어 분께서 전달 해주셨기에 (1), (2) 번의 문제점만 간단하게 처리하는 과정을 진행했습니다.

튜닝 쿼리

# 내부 보안상 쿼리 내부의 테이블명 / 컬럼명은 존재하지 않는 가상의 명칭을 사용하였음을 알려드립니다.!

select a.code
     , a.price
     , a.time as `datetime`
     , b.type
     , b.number
     , b.time as `request_time`
  from emp a
  join dept b on a.code = b.code
  join dept_detail c on b.dept_col = c.dept_col
 where exists (
               select a.code
                    , max(a.emp_no)
                    , max(a.time) as `last_time`
                    , date(max(a.time)) as `last_date`
                    , count(*) as `total_count`
                 from emp a 
                 join dept b on on a.code = b.code
                 join dept_detail c on b.dept_col = c.dept_col
                where a.status = 'wait'
                  and a.country = 'kr'
                  and b.type = 'extend'
                group by a.code
                having count(*) = 1
               ) extend on a.code = extend.code
union
select a.code
     , 0 as price
     , b.create_date as `datetime`
     , 'unknown' as `type`
     , b.number
     , null as `request_time`
  from emp a
  join dept b on a.code = b.code
 where a.country = 'kr'
   and b.version = 'v1'
   and b.col_num is null
   and b.create_date >= '2020-01-01'
   and b.create_date <  '2023-01-01'
;

급하게 튜닝 작업을 진행하였지만 매우 만족스러운 결과가 나왔습니다. 쿼리에 사용된 3개의 테이블은 각각 40만 / 60만 / 70만 정도로 데이터의 크기가 크지 않았기에 이정도의 튜닝 작업으로도 빠르게 처리가 가능했던 것 같습니다.

오늘은 SQL 최적화 기법에 대한 소개를 진행했습니다. 평소와 달리 1회성이라는 부분 + 긴급성이 존재하여 블로그를 통해 소개하기에 다소 미흡한 부분이 다소 존재했던 것 같습니다.
이외에도 다양한 최적화 기법이 존재하기 때문에 다음 번에는 다른 사례를 통하여 더욱 다양한 최적화 기법을 소개할 예정입니다.