EXPLAIN 기초: 점검/리포트 쿼리가 느려질 때 “왜 느린지” 확인하는 방법

포인트 원장(point_history)은 시간이 지날수록 커집니다. 데이터가 커지면, 기능 쿼리(내역/잔액)뿐 아니라 점검 쿼리(원장 vs balance 비교, 월별 집계, ref_id 그룹핑)도 점점 느려집니다.

이때 흔히 “인덱스를 더 만들면 되지 않을까?”라고 생각하지만, 인덱스는 무조건 늘린다고 좋아지지 않습니다. 어떤 쿼리가 어떤 방식으로 실행되는지 모르면 잘못된 인덱스를 만들거나, 효과 없는 튜닝을 반복할 가능성이 큽니다.

그래서 필요한 도구가 EXPLAIN입니다. EXPLAIN은 “이 쿼리를 DB가 어떤 순서로 실행할지”를 보여주는 실행 계획입니다. 즉, 느린 이유를 추측하지 않고 눈으로 확인할 수 있게 해줍니다.

이 단원의 목적: 실행 계획을 보고 “어디를 고칠지” 결정하기

이번 글의 목표는 아래 3가지입니다.

  1. EXPLAIN 결과에서 최소한의 핵심 항목을 읽을 수 있게 된다
  2. 포인트 시스템 쿼리에서 자주 나오는 느린 패턴(풀 스캔, filesort)을 구분한다
  3. 인덱스를 “조회 패턴에 맞게” 설계하는 감각을 만든다

1) EXPLAIN 사용법: SELECT 앞에 붙이면 된다

가장 기본은 SELECT 앞에 EXPLAIN을 붙이는 것입니다. 

EXPLAIN SELECT id, action_type, amount, created_at FROM point_history WHERE uid = '11111111-1111-1111-1111-111111111111' AND hide = 0 ORDER BY created_at DESC, id DESC LIMIT 50;

실행 결과는 DBMS/버전에 따라 조금씩 다르지만, 공통적으로 “어떤 테이블을 어떤 방식으로 읽는지”를 보여줍니다.

2) EXPLAIN에서 최소로 봐야 할 6가지 컬럼

EXPLAIN은 항목이 많습니다. 초반에는 아래 6개만 봐도 충분합니다.

  • table: 어떤 테이블을 읽는지
  • type: 접근 방식(대략 “얼마나 똑똑하게 찾는지”)
  • possible_keys: 사용 가능 인덱스 후보
  • key: 실제로 선택된 인덱스
  • rows: 읽을 것으로 예상하는 행 수(많을수록 위험 신호)
  • Extra: 추가 작업(filesort, temporary 등)

이 중 가장 직관적인 위험 신호는 rows가 매우 크다, Extra에 filesort/temporary가 있다입니다.

3) type 컬럼의 감각: ALL이면 일단 의심

type은 접근 방식인데, 초반에는 “좋고 나쁨”을 너무 세밀하게 외우기보다 아래 정도만 잡아도 충분합니다.

  • ALL: 테이블 풀 스캔(전체 훑기) 가능성 큼
  • range: 인덱스 범위 탐색(기간 조건 등)
  • ref: 인덱스로 특정 값 매칭(= 조건)
  • const: PK/UNIQUE로 1건 확정 수준

포인트 원장처럼 데이터가 큰 테이블에서 type=ALL이 나오면 대부분 “인덱스가 없거나, 인덱스를 못 쓰는 형태”입니다.

4) 대표 사례 1: “내 포인트 내역”이 느릴 때

포인트 내역은 보통 uid로 필터링하고 최신순으로 정렬합니다. 가장 기본적인 인덱스는 (uid, created_at)입니다. 

EXPLAIN SELECT id, action_type, amount, created_at FROM point_history WHERE uid = @uid AND hide = 0 ORDER BY created_at DESC, id DESC LIMIT 50;

여기서 기대하는 모습은 대략 이렇습니다.

  • key가 idx_uid_created(또는 유사 인덱스)로 잡힌다
  • type이 ref 또는 range로 나온다
  • rows가 “uid 전체 행” 수준으로 너무 크지 않다(기간 조건이 있으면 더 줄어든다)
  • Extra에 filesort가 없거나, 최소화된다

반대로 다음이면 느릴 가능성이 큽니다.

  • key가 NULL(인덱스 미사용)
  • type=ALL
  • Extra에 Using filesort

5) 대표 사례 2: 점검 쿼리(uid별 SUM)가 느릴 때

uid별 SUM(amount)은 point_history 전체를 그룹핑해야 합니다. 이 쿼리는 구조상 비용이 클 수밖에 없습니다. 다만 인덱스로 “그룹핑/조인” 비용을 줄일 수는 있습니다. 

EXPLAIN SELECT uid, SUM(amount) AS ledger_balance FROM point_history WHERE hide = 0 GROUP BY uid;

이런 쿼리는 rows가 크게 나오는 것이 자연스러울 수 있습니다. 중요한 건 “이 점검을 얼마나 자주 돌릴 건지”입니다. 매 요청마다 돌리는 게 아니라, 배치/관리자 점검용으로 스케줄링하거나, 범위를 줄여서 실행하는 식으로 운영합니다.

그리고 아래 인덱스는 점검 쿼리에 간접적으로 도움이 될 수 있습니다. 

-- uid 기반 그룹핑에 도움 CREATE INDEX idx_uid_hide ON point_history(uid, hide);

다만 hide의 선택도가 낮으면(대부분 0이면) 효과가 미미할 수 있습니다. 인덱스는 “조건으로 얼마나 잘 걸러지느냐”가 중요합니다. 그래서 EXPLAIN으로 실제로 key가 선택되는지 확인해야 합니다.

6) filesort/temporary: “정렬/그룹핑 비용이 크다”는 신호

Extra에 아래가 보이면 대체로 비용이 큽니다.

  • Using filesort: 인덱스 순서로 정렬을 못 해서 별도 정렬 수행
  • Using temporary: 그룹핑/정렬을 위해 임시 테이블 사용

예를 들어 내역 조회에서 filesort가 뜬다면, ORDER BY가 인덱스와 맞지 않을 가능성이 있습니다. (uid, created_at, id)처럼 정렬 축을 인덱스에 포함시키는 방향을 검토할 수 있습니다.

7) 인덱스 튜닝의 기본 순서: “쿼리 패턴 → EXPLAIN → 인덱스”

튜닝은 보통 다음 순서가 안전합니다.

  1. 쿼리를 “자주 쓰는 형태”로 고정한다(WHERE/ORDER BY/그룹 기준 확정)
  2. EXPLAIN으로 인덱스 사용 여부와 예상 rows를 확인한다
  3. 정말 필요한 인덱스만 추가한다
  4. 인덱스 추가 후 다시 EXPLAIN으로 개선 여부를 확인한다

이 과정 없이 인덱스를 늘리면, 쓰기 성능만 떨어지고(INSERT/UPDATE 비용 증가), 기대한 쿼리는 여전히 느린 상황이 생길 수 있습니다.

8) 점검 쿼리를 빠르게 만드는 현실적인 방법: “범위를 줄인다”

uid별 전체 합계를 매번 돌리는 대신, 최근 1일/7일처럼 범위를 줄인 점검을 자주 돌리고, 전체 점검은 하루 1회 같은 형태로 운영하는 방식이 흔합니다. 

EXPLAIN SELECT uid, SUM(amount) AS ledger_delta_1d FROM point_history WHERE hide = 0 AND created_at >= NOW() - INTERVAL 1 DAY GROUP BY uid;

이 쿼리는 created_at 조건이 들어가므로 (created_at) 또는 (uid, created_at) 인덱스가 더 직접적으로 도움이 됩니다. 즉, “점검도 조회 패턴”이고, 패턴에 맞게 인덱스를 선택해야 합니다.

다음 글 예고: 제약조건(CONSTRAINT) — UNIQUE/FOREIGN KEY/CHECK로 ‘깨진 데이터’를 미리 막기

속도와 점검을 다뤘다면, 이제는 “애초에 이상한 데이터가 들어오지 않게” 만드는 단계로 넘어갑니다. 다음 글에서는 UNIQUE(중복 요청 방지), FOREIGN KEY(참조 무결성), CHECK(값 범위 제한) 같은 제약조건을 통해 포인트 시스템에서 흔히 발생하는 데이터 오류를 DB 레벨에서 차단하는 방법을 정리합니다.

댓글

댓글 쓰기

이 블로그의 인기 게시물

JOIN 기초: users와 point_history를 합쳐 ‘회원별 요약(잔액/최근 활동)’ 만들기

포인트 원장(point_history)만으로도 잔액과 통계를 만들 수 있지만, 실제 화면이나 운영 도구에서는 거의 항상 “사용자 정보”가 함께 필요합니다. 예를 들어 이런 질문들은 point_history만으로는 답이 부족합니다. 이 uid가 누구인지(가입일, 상태 등) 함께 보고 싶다 회원 목록을 보면서 각 회원의 잔액/최근 활동을 한 줄로 요약하고 싶다 최근 7일 동안 사용이 많은 회원을 “회원 정보와 함께” 추려 보고 싶다 이때 필요한 것이 JOIN입니다. JOIN은 테이블을 “합치는” 기능이지만, 정확히는 두 테이블의 행을 연결 해 새로운 결과를 만드는 방식입니다. 이번 글에서는 포인트 시스템에서 가장 흔한 JOIN 패턴(회원 + 원장 요약)을 중심으로, INNER JOIN/LEFT JOIN의 차이와 실전에서 헷갈리는 포인트를 정리합니다. 이 단원의 목적: “회원 목록 + 포인트 요약”을 안정적으로 뽑아내기 JOIN은 문법을 외우는 것보다 “어떤 결과를 만들고 싶은지”를 먼저 그리는 것이 중요합니다. 이번 글의 목표는 아래 3가지입니다. INNER JOIN과 LEFT JOIN의 차이를 이해하고 상황에 맞게 선택한다 집계(GROUP BY)와 JOIN을 결합해 ‘회원별 잔액/통계’를 만든다 JOIN에서 자주 발생하는 오류(행 폭증, 중복 집계)를 피하는 패턴을 익힌다 준비: users에 최소한의 컬럼을 더해 “JOIN 결과가 읽히게” 만들기 이전 글에서는 users가 uid와 created_at만 있었지만, JOIN 결과를 보기 편하게 하려면 display_name 같은 컬럼이 하나 있으면 좋습니다. 실제 서비스라면 이메일/닉네임/상태값이 있을 텐데, 여기서는 학습 목적상 간단히 display_name을 추가합니다. -- 이미 있다면 생략 ALTER TABLE users ADD COLUMN display_name VARCHAR(50) NULL; -- 예시 값 입력(학습용) UPDATE users SET display_n...
자세한 내용 보기

집계 기초: SUM/COUNT로 잔액·기간별 적립/사용 리포트 만들기

포인트 시스템의 “진짜 쓸모”는 원장(point_history)에 기록된 사실을 사람이 이해할 수 있는 형태로 요약하는 데서 나옵니다. 화면에서는 잔액 한 줄로 보이지만, 그 뒤에는 적립/사용/만료/취소가 뒤섞인 수많은 기록이 존재합니다. 그래서 포인트 시스템에서 SQL의 핵심은 단순 조회(SELECT)보다 집계(aggregation) 입니다. 집계는 원장 데이터를 합치고(SUM), 개수를 세고(COUNT), 기준별로 묶어서(GROUP BY) “의미 있는 숫자”로 바꾸는 과정입니다. 이 단원의 목적: 원장 데이터로 ‘요약 결과’를 만드는 습관 만들기 이번 글에서는 아래 3가지를 목표로 합니다. SUM으로 잔액을 만들고, 기간별 합계를 안정적으로 계산한다 COUNT로 이벤트 건수를 집계해 “양”을 파악한다 집계를 할 때 자주 발생하는 실수(NULL, 숨김 데이터, 중복 기준)를 피한다 준비: 집계를 위한 최소 데이터 점검 집계는 데이터가 적으면 티가 안 나지만, 데이터가 쌓이면 작은 실수가 큰 오차로 확대됩니다. 그래서 집계 전에 “필수 조건”을 먼저 확인해 두면 좋습니다. amount가 NULL이 아닌가 숨김 데이터(hide=1)가 섞여 있다면 집계에서 제외할 것인가 부호 정책(적립 + / 차감 -)이 일관적인가 아래 예시는 hide 컬럼이 있다고 가정하고, 집계에서는 hide=0만 포함하는 패턴으로 진행합니다. SET @uid := '11111111-1111-1111-1111-111111111111'; 1) 잔액 계산의 기본: SUM(amount) 가장 기본적인 잔액 조회는 “해당 사용자의 amount를 전부 더하는 것”입니다. 이 방식의 장점은 단순함과 신뢰성입니다. 원장이 올바르면, 잔액은 항상 재현 가능합니다. SELECT uid, SUM(amount) AS balance FROM point_history WHERE uid = @uid AND hide = 0 GROUP BY uid; 여기서...
자세한 내용 보기

키 설계 기초: PRIMARY KEY/UNIQUE로 ‘중복 적립’과 ‘중복 차감’을 구조적으로 막는 방법

포인트 시스템에서 가장 골치 아픈 문제는 “한 번만 처리되어야 하는 요청이 두 번 처리되는 것”입니다. 네트워크 재시도, 클라이언트 중복 클릭, 서버 타임아웃 후 재요청은 운영에서 흔한 일이고, 이때 코드만으로 완벽하게 막는 건 생각보다 어렵습니다. 그래서 많은 서비스는 마지막 방어선으로 키(Primary Key/Unique Key) 를 활용합니다. 키 설계가 잘 되면, 애플리케이션이 실수하더라도 DB가 중복을 거절 합니다. 이번 글에서는 “키는 그냥 ID 하나”라는 수준을 넘어서, 포인트 원장(히스토리)에서 실제로 필요한 멱등성(idempotency) 을 어떻게 만들지에 초점을 맞춥니다. 이 단원의 목적: “중복이 생길 수밖에 없는 현실”을 전제로 설계하기 포인트 기능은 보통 결제, 이벤트, 쿠폰, 고객센터 보정 등 다양한 경로에서 호출됩니다. 호출 경로가 늘어날수록 중복 처리 가능성도 늘어납니다. 이번 글의 목표는 아래 3가지입니다. PK/UNIQUE의 역할을 정확히 이해하고 “어디에 걸어야 하는지” 결정한다 포인트 원장에서 중복을 막는 대표 전략(요청 ID/ref_id)을 설계한다 실제로 중복 요청이 들어오는 상황을 가정해, DB 레벨에서 안전하게 막는다 1) PRIMARY KEY는 “행을 식별하는 유일한 주소”다 PRIMARY KEY(PK)는 테이블에서 한 행을 유일하게 식별합니다. 흔히 AUTO_INCREMENT id를 PK로 두는 이유는 단순합니다. 쉽고, 빠르고, 안정적 이기 때문입니다. 하지만 포인트 시스템에서는 “id가 유일하다”만으로는 부족합니다. id는 단지 “기록의 번호”일 뿐, “같은 요청이 두 번 들어왔다”는 문제를 막아주지 않습니다. 즉, PK는 필요조건이지만 중복 방지의 충분조건은 아닙니다. -- 기록 자체의 식별자(기본) PRIMARY KEY (id) 2) UNIQUE는 “같은 의미의 기록이 두 번 들어오는 것”을 막는다 UNIQUE는 “비즈니스적으로 같은 의미”의 중복을 차단하는 도구입니다. ...
자세한 내용 보기