TIL - 20260821

juni·2026년 8월 21일

목록 보기

436/468

0821 데이터베이스 실무 심화 (9/N): Query 분석, EXPLAIN과 느린 쿼리 개선

✅ 1. 느린 쿼리란 무엇인가?

느린 쿼리는 DB가 결과를 반환하는 데 오래 걸리는 SQL입니다.
관리자 상담 목록, 주문 목록, 유입 분석, 엑셀 Export, 상태 이력 조회처럼 데이터가 많이 쌓이는 기능에서 자주 발생합니다.
처음에는 빠르던 API도 데이터가 늘어나면 갑자기 느려질 수 있습니다.

초기 데이터 1,000건:
목록 조회 빠름

운영 데이터 100,000건:
검색/필터/정렬 느려짐

운영 데이터 1,000,000건:
count, join, like, export에서 병목 발생

➕ 1-1. 느린 쿼리가 만드는 문제

관리자 목록 로딩 지연
상담 처리 속도 저하
API timeout
DB CPU 증가
전체 서비스 응답 지연
엑셀 다운로드 실패
장애 원인 추적 어려움

느린 쿼리는 단순히 “화면이 느리다”로 끝나지 않습니다.
DB 리소스를 잡아먹어 다른 API까지 같이 느려지게 만들 수 있습니다.

✅ 2. 쿼리 최적화의 기본 원칙

쿼리 최적화는 감으로 인덱스를 추가하는 일이 아닙니다.
먼저 어떤 쿼리가 느린지 확인하고, 실행 계획을 보고, 실제 병목을 줄여야 합니다.

1. 느린 API 확인
2. 실제 SQL 확인
3. EXPLAIN으로 실행 계획 확인
4. 병목 원인 파악
5. 인덱스/쿼리/응답 구조 개선
6. 개선 전후 비교

➕ 2-1. 먼저 확인해야 할 것

어떤 API가 느린가?
어떤 query parameter에서 느린가?
데이터가 몇 건인가?
where 조건은 무엇인가?
orderBy는 무엇인가?
include/join이 많은가?
count가 느린가?
프론트 렌더링이 느린 것은 아닌가?

DB 문제가 아닌데 DB만 붙잡고 있으면 시간을 낭비합니다.
API 응답 시간, DB query 시간, payload 크기, 프론트 렌더링 시간을 나눠서 봐야 합니다.

✅ 3. EXPLAIN이란 무엇인가?

EXPLAIN은 PostgreSQL이 SQL을 어떻게 실행할지 보여주는 명령어입니다.
어떤 인덱스를 쓰는지, 전체 테이블을 훑는지, 정렬 비용이 큰지, join 방식이 무엇인지 확인할 수 있습니다.

EXPLAIN
SELECT *
FROM consults
WHERE status = 'NEW'
ORDER BY created_at DESC
LIMIT 20;

➕ 3-1. EXPLAIN ANALYZE

EXPLAIN은 예상 실행 계획을 보여줍니다.
EXPLAIN ANALYZE는 실제로 쿼리를 실행하고 실제 시간을 보여줍니다.

EXPLAIN ANALYZE
SELECT *
FROM consults
WHERE status = 'NEW'
ORDER BY created_at DESC
LIMIT 20;

➕ 3-2. 주의

EXPLAIN ANALYZE는 실제 쿼리를 실행함
SELECT는 괜찮지만 UPDATE/DELETE에는 매우 주의
운영 DB에서 무거운 쿼리에 바로 실행하지 않기
가능하면 staging/복제 환경에서 먼저 확인

EXPLAIN ANALYZE는 강력하지만 실제 실행된다는 점을 잊으면 안 됩니다.
운영 DB에서는 조심해서 사용해야 합니다.

✅ 4. 실행 계획에서 자주 보는 용어

용어	의미	해석
Seq Scan	순차 스캔	테이블 전체를 훑음
Index Scan	인덱스를 사용해 조회	인덱스 활용
Bitmap Index Scan	인덱스로 후보를 찾음	많은 row 후보
Sort	정렬 작업	orderBy 비용
Nested Loop	반복 join	소량 데이터에 적합
Hash Join	해시 기반 join	대량 join에 사용
Filter	조건 필터링	스캔 후 걸러냄
Rows Removed by Filter	필터로 제거된 row	낭비 확인
Planning Time	실행 계획 생성 시간	보통 짧음
Execution Time	실제 실행 시간	핵심 지표

➕ 4-1. Seq Scan이 무조건 나쁜가?

작은 테이블:
Seq Scan이 더 빠를 수 있음

큰 테이블:
자주 조회하는 조건에서 Seq Scan이면 문제 가능

결론:
테이블 크기와 쿼리 패턴 기준으로 판단

Seq Scan이 보인다고 무조건 인덱스를 추가하면 안 됩니다.
하지만 상담/주문처럼 데이터가 많아지는 테이블에서 자주 쓰는 조건이 Seq Scan이면 개선 후보입니다.

✅ 5. 느린 관리자 상담 목록 예시

➕ 5-1. 문제 쿼리

SELECT *
FROM consults
WHERE status = 'NEW'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

➕ 5-2. 문제 가능성

status 조건에 인덱스 없음
created_at 정렬에 인덱스 없음
SELECT *로 불필요한 컬럼 조회
데이터가 많아지면 정렬 비용 증가

➕ 5-3. 개선 후보

CREATE INDEX idx_consults_status_created_at_id
ON consults (status, created_at DESC, id DESC);

➕ 5-4. 개선된 쿼리

SELECT id, customer_name, phone_masked, status, product_id, created_at
FROM consults
WHERE status = 'NEW'
ORDER BY created_at DESC, id DESC
LIMIT 20;

인덱스는 where와 order by를 함께 보고 설계해야 합니다.
목록에서는 SELECT *보다 필요한 컬럼만 가져오는 것이 좋습니다.

✅ 6. Index Scan과 Seq Scan 비교

➕ 6-1. 인덱스가 없는 경우

consults 전체 row 확인
  ↓
status = NEW인 row만 필터
  ↓
created_at 기준 정렬
  ↓
상위 20건 반환

문제:

데이터가 많을수록 느림
정렬 비용 큼
DB CPU 사용 증가

➕ 6-2. 인덱스가 있는 경우

idx_consults_status_created_at_id 사용
  ↓
status = NEW인 최신 데이터부터 접근
  ↓
20건만 빠르게 반환

장점:

필터와 정렬을 인덱스로 처리 가능
LIMIT과 잘 맞음
관리자 최신 목록 조회에 유리

최신순 목록 + limit는 적절한 인덱스와 궁합이 좋습니다.
관리자 목록은 이 패턴이 많습니다.

✅ 7. 복합 인덱스 설계

복합 인덱스는 여러 컬럼을 묶은 인덱스입니다.
관리자 목록에서는 status + createdAt, source + createdAt, productId + createdAt 조합이 자주 후보가 됩니다.

➕ 7-1. 예시

CREATE INDEX idx_consults_status_created_at_id
ON consults (status, created_at DESC, id DESC);

➕ 7-2. 컬럼 순서 기준

where에서 자주 고정되는 값:
앞쪽

범위 검색/정렬에 쓰는 값:
뒤쪽

동일값 보조 정렬:
id

➕ 7-3. 예시 해석

(status, created_at DESC, id DESC)

status:
NEW, CALLED 같은 필터

created_at:
최신순 정렬과 날짜 범위

id:
같은 created_at에서 안정적 정렬

➕ 7-4. 주의

(status, created_at)과 (created_at, status)는 다름
쿼리 패턴에 따라 효율이 달라짐
모든 필터 조합마다 인덱스를 만들면 안 됨

복합 인덱스는 컬럼 순서가 핵심입니다.
운영자가 실제로 자주 쓰는 필터 조합부터 잡아야 합니다.

✅ 8. 인덱스를 너무 많이 만들면 생기는 문제

인덱스는 조회를 빠르게 하지만 쓰기 비용을 증가시킵니다.
insert/update/delete 시 인덱스도 함께 갱신해야 하기 때문입니다.

consults row insert
  ↓
테이블에 데이터 추가
  ↓
각 인덱스도 업데이트
  ↓
인덱스가 많을수록 쓰기 비용 증가

➕ 8-1. 인덱스 남용의 문제

쓰기 성능 저하
저장공간 증가
migration 시간 증가
DB 유지보수 비용 증가
실제 사용되지 않는 인덱스 누적

➕ 8-2. 인덱스 추가 기준

자주 사용되는 쿼리인가?
데이터가 충분히 많은 테이블인가?
where/orderBy에 자주 쓰이는가?
EXPLAIN에서 병목이 확인됐는가?
기존 인덱스로 커버되지 않는가?

인덱스는 “혹시 모르니까”가 아니라 “확인된 조회 패턴” 기준으로 추가하는 것이 맞습니다.

✅ 9. SELECT * 줄이기

SELECT *는 모든 컬럼을 가져오는 방식입니다.
목록 조회에서는 대부분 불필요한 데이터까지 가져오게 됩니다.

➕ 9-1. 위험한 예시

SELECT *
FROM consults
ORDER BY created_at DESC
LIMIT 20;

문제:

상담 메모까지 가져올 수 있음
긴 JSON 컬럼까지 가져올 수 있음
개인정보 노출 범위 증가
응답 payload 증가
프론트 렌더링 부담 증가

➕ 9-2. 개선 예시

SELECT id, customer_name, phone_masked, status, product_id, source, created_at
FROM consults
ORDER BY created_at DESC, id DESC
LIMIT 20;

➕ 9-3. Prisma select

const consults = await prisma.consult.findMany({
  select: {
    id: true,
    customerName: true,
    phoneMasked: true,
    status: true,
    source: true,
    createdAt: true,
    product: {
      select: {
        id: true,
        modelName: true,
      },
    },
  },
});

목록 API는 화면에 필요한 최소 데이터만 가져와야 합니다.
상세 정보는 상세 API에서 따로 조회하는 것이 좋습니다.

✅ 10. LIKE / contains 검색 문제

LIKE '%keyword%'나 Prisma contains 검색은 편하지만 대량 데이터에서 느려질 수 있습니다.
일반 B-tree 인덱스를 잘 활용하지 못하는 경우가 많습니다.

➕ 10-1. 문제 쿼리

SELECT *
FROM consults
WHERE customer_name ILIKE '%준%'
ORDER BY created_at DESC
LIMIT 20;

문제:

앞쪽 wildcard 때문에 일반 인덱스 사용 어려움
데이터가 많으면 전체 스캔 가능성
OR 조건과 함께 쓰면 더 무거워짐

➕ 10-2. 대안

정확 검색 가능한 컬럼 분리
전화번호 정규화 컬럼 사용
이름 초성/검색용 컬럼 고려
PostgreSQL trigram index 검토
검색 전문 엔진은 나중 단계에서 검토

➕ 10-3. Trigram index 예시

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_consults_customer_name_trgm
ON consults
USING gin (customer_name gin_trgm_ops);

이름/메모 부분 검색이 자주 필요하고 데이터가 많다면 trigram index를 고려할 수 있습니다.
하지만 처음부터 적용하기보다 실제 병목 확인 후 도입하는 것이 좋습니다.

✅ 11. 전화번호 검색 최적화

전화번호는 관리자 목록에서 자주 검색됩니다.
전화번호 검색은 부분 검색보다 정규화된 컬럼을 활용하는 것이 좋습니다.

➕ 11-1. 저장 구조

phoneEncrypted:
원본 전화번호 암호화

phoneMasked:
010****5678

phoneNormalized:
01012345678

phoneLast4:
5678

➕ 11-2. 검색 방식

SELECT id, customer_name, phone_masked, status
FROM consults
WHERE phone_normalized = '01012345678';

또는:

SELECT id, customer_name, phone_masked, status
FROM consults
WHERE phone_last4 = '5678'
ORDER BY created_at DESC
LIMIT 20;

➕ 11-3. 인덱스 후보

CREATE INDEX idx_consults_phone_normalized
ON consults (phone_normalized);

CREATE INDEX idx_consults_phone_last4_created_at
ON consults (phone_last4, created_at DESC);

➕ 11-4. 주의

전화번호 원본 로그 출력 금지
검색어도 로그에 남기지 않기
전화번호 컬럼 접근 권한 제한
암호화하면 contains 검색 어려움

전화번호는 성능과 보안을 함께 고려해야 합니다.
운영 편의를 위해 검색 컬럼을 만들되, 로그와 권한을 조심해야 합니다.

✅ 12. Count 쿼리 최적화

페이지네이션에서 total과 totalPages를 보여주려면 count 쿼리가 필요합니다.
하지만 데이터가 많고 검색 조건이 복잡하면 count가 느려질 수 있습니다.

➕ 12-1. 일반 구조

const [items, total] = await prisma.$transaction([
  prisma.consult.findMany({
    where,
    take: limit,
    skip,
    orderBy,
    select,
  }),
  prisma.consult.count({
    where,
  }),
]);

➕ 12-2. count가 느려지는 이유

where 조건이 복잡함
OR 조건이 많음
contains 검색 포함
join 조건 포함
인덱스를 못 탐
대량 테이블 전체 count

➕ 12-3. 개선 방향

자주 쓰는 필터에 인덱스 추가
검색 조건 단순화
정확한 total이 꼭 필요한지 검토
로그성 화면은 hasNextPage 방식 고려
집계 테이블 또는 materialized view 검토

관리자 상담 목록은 total이 유용합니다.
하지만 대량 로그 화면에서는 정확한 total보다 “다음 페이지 있음”만 보여주는 것이 더 나을 수 있습니다.

✅ 13. Offset이 커질 때의 문제

offset pagination은 구현이 쉽지만, 뒤 페이지로 갈수록 느려질 수 있습니다.

➕ 13-1. 문제 쿼리

SELECT id, customer_name, status, created_at
FROM consults
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

문제:

앞의 100,000건을 건너뛰는 비용 발생
뒤 페이지로 갈수록 느려짐
대량 데이터에서 비효율적

➕ 13-2. 대안

cursor pagination
날짜 범위 필터 강제
최근 3개월 기본 조회
아카이브 분리
엑셀 다운로드는 ExportJob으로 분리

➕ 13-3. 실무 기준

일반 상담 목록:
offset으로 시작 가능

몇 년치 전체 데이터:
기본 날짜 필터 적용

대량 로그:
cursor pagination 추천

무한 스크롤:
cursor pagination 추천

offset이 무조건 나쁜 것은 아닙니다.
하지만 깊은 페이지 이동이 잦거나 데이터가 매우 많으면 구조를 바꿔야 합니다.

✅ 14. JOIN 최적화

관리자 목록에서는 상담과 상품, 관리자, 유입 정보를 함께 보여줘야 할 수 있습니다.
이때 join이 많아지면 쿼리가 느려질 수 있습니다.

➕ 14-1. 문제 예시

consults
  join products
  join product_options
  join admin_users
  join lead_sources
  join latest_status_history
  join notification_logs

문제:

쿼리 복잡도 증가
중복 row 발생 가능
정렬/페이지네이션 꼬임
응답 payload 증가

➕ 14-2. 개선 기준

목록에 꼭 필요한 relation만 join
상세 정보는 상세 API에서 조회
마지막 이력은 별도 API 또는 요약 컬럼 고려
집계/최근 상태는 원본 테이블에 denormalize 고려

➕ 14-3. Snapshot 활용

consults에 저장:
productNameSnapshot
carrierSnapshot
planNameSnapshot

장점:
목록에서 product join 없이 표시 가능
상담 당시 조건 보존

모든 값을 정규화해서 join으로 가져오는 것이 항상 좋은 것은 아닙니다.
관리자 목록에서는 snapshot/요약 컬럼이 실무적으로 유리할 때가 많습니다.

✅ 15. 최신 이력 조회 최적화

상담 목록에 마지막 상태 변경 시간이나 마지막 처리자를 보여주고 싶을 수 있습니다.
매 row마다 이력 테이블을 조회하면 N+1 또는 무거운 join이 생깁니다.

➕ 15-1. 위험한 구조

상담 목록 20건 조회
  ↓
각 상담마다 statusHistories 최신 1건 조회
  ↓
추가 쿼리 20번

➕ 15-2. 개선 후보

consults에 lastStatusChangedAt 저장
consults에 lastHandledByAdminId 저장
상세에서만 이력 조회
별도 materialized view 사용

➕ 15-3. Denormalized 컬럼 예시

consults
  - status
  - lastStatusChangedAt
  - lastHandledByAdminId

상태 변경 transaction 안에서 consults.status, lastStatusChangedAt, lastHandledByAdminId, 이력 테이블을 함께 업데이트할 수 있습니다.
목록 조회 성능이 좋아지고 이력은 상세에서만 가져오면 됩니다.

✅ 16. EXPLAIN 결과를 볼 때 핵심 포인트

실행 계획을 처음 보면 어렵지만, 몇 가지만 먼저 보면 됩니다.

➕ 16-1. 체크 포인트

Seq Scan인가 Index Scan인가?
예상 rows와 실제 rows 차이가 큰가?
Sort가 큰 비용을 차지하는가?
Rows Removed by Filter가 많은가?
Execution Time이 얼마나 걸리는가?
Join 방식이 적절한가?
사용하려던 인덱스를 실제로 쓰는가?

➕ 16-2. Rows Removed by Filter

많은 row를 읽고 대부분 버림
  ↓
where 조건에 맞는 인덱스가 없을 가능성

➕ 16-3. Sort 비용

정렬 대상 row가 많음
  ↓
orderBy에 맞는 인덱스 검토

➕ 16-4. 예상 rows와 실제 rows 차이

DB 통계가 실제 데이터 분포를 잘못 예측
  ↓
ANALYZE 필요 가능성

EXPLAIN은 한 번에 완벽히 읽으려 하지 않아도 됩니다.
처음에는 Seq Scan, Sort, Execution Time, Rows Removed by Filter만 봐도 충분합니다.

✅ 17. ANALYZE와 통계 정보

PostgreSQL은 테이블 통계를 기반으로 실행 계획을 세웁니다.
통계가 오래되었거나 부정확하면 비효율적인 실행 계획을 선택할 수 있습니다.

➕ 17-1. ANALYZE

ANALYZE consults;

➕ 17-2. VACUUM ANALYZE

VACUUM ANALYZE consults;

➕ 17-3. 의미

ANALYZE:
통계 정보 갱신

VACUUM:
죽은 row 정리

VACUUM ANALYZE:
정리 + 통계 갱신

➕ 17-4. 주의

운영에서 무거운 작업이 될 수 있음
대량 변경 후 성능 문제 시 검토
RDS autovacuum 설정 확인
무조건 수동으로 자주 실행할 필요는 없음

PostgreSQL은 autovacuum이 자동으로 관리합니다.
다만 대량 update/delete 후 성능 문제가 생기면 통계 갱신 여부를 확인할 수 있습니다.

✅ 18. Partial Index

Partial Index는 특정 조건에 해당하는 row만 인덱싱하는 방식입니다.
Soft Delete, 활성 상품, 특정 상태 데이터에 유용합니다.

➕ 18-1. Soft Delete 예시

CREATE INDEX idx_products_active_created_at
ON products (created_at DESC)
WHERE deleted_at IS NULL;

➕ 18-2. 활성 상담 예시

CREATE INDEX idx_consults_active_status_created_at
ON consults (status, created_at DESC)
WHERE deleted_at IS NULL;

➕ 18-3. 장점

인덱스 크기 감소
자주 조회하는 활성 데이터에 집중
Soft Delete 조건과 잘 맞음

➕ 18-4. 주의

쿼리 where 조건이 partial index 조건과 맞아야 함
Prisma schema에서 직접 표현이 제한될 수 있음
raw SQL migration 필요할 수 있음

Soft Delete를 쓰는 테이블에서는 partial index가 꽤 유용합니다.
단, 실제 쿼리에 deleted_at IS NULL 조건이 있어야 인덱스를 잘 활용합니다.

✅ 19. Covering Index 개념

Covering Index는 쿼리에 필요한 컬럼 대부분을 인덱스만으로 처리할 수 있게 하는 개념입니다.
PostgreSQL에서는 INCLUDE 컬럼을 사용할 수 있습니다.

➕ 19-1. 예시

CREATE INDEX idx_consults_status_created_at_include
ON consults (status, created_at DESC, id DESC)
INCLUDE (customer_name, phone_masked, source);

➕ 19-2. 의미

검색/정렬:
status, created_at, id

결과 표시:
customer_name, phone_masked, source

일부 경우 테이블 접근을 줄일 수 있음

➕ 19-3. 주의

인덱스 크기 증가
쓰기 비용 증가
자주 쓰는 핵심 목록에만 적용
처음부터 남용하지 않기

Covering Index는 고급 최적화에 가깝습니다.
먼저 기본 복합 인덱스와 select 최소화를 적용한 뒤 검토하는 것이 좋습니다.

✅ 20. Materialized View

Materialized View는 쿼리 결과를 미리 저장해두는 방식입니다.
복잡한 집계나 대시보드 조회에 유용할 수 있습니다.

➕ 20-1. 예시

CREATE MATERIALIZED VIEW daily_consult_stats AS
SELECT
  DATE(created_at) AS date,
  source,
  status,
  COUNT(*) AS count
FROM consults
GROUP BY DATE(created_at), source, status;

➕ 20-2. 조회

SELECT *
FROM daily_consult_stats
WHERE date >= '2026-08-01';

➕ 20-3. 갱신

REFRESH MATERIALIZED VIEW daily_consult_stats;

➕ 20-4. 적합한 경우

유입 source별 일자 통계
상품별 상담 신청 통계
관리자별 처리량 통계
대시보드 집계

➕ 20-5. 주의

실시간 데이터가 아님
refresh 전략 필요
동시 refresh 주의
초기에는 일반 query/집계 테이블로 충분할 수 있음

Materialized View는 대시보드 성능 개선에 좋습니다.
다만 운영 복잡도가 올라가므로 실제 병목이 생긴 뒤 도입해도 됩니다.

✅ 21. 느린 쿼리 로그

PostgreSQL에서는 느린 쿼리를 로그로 남길 수 있습니다.
RDS에서도 파라미터 설정을 통해 slow query를 확인할 수 있습니다.

➕ 21-1. 주요 설정 개념

log_min_duration_statement:
특정 ms 이상 걸린 쿼리 로그 기록

예:
1000ms 이상 쿼리 기록

➕ 21-2. 활용

어떤 쿼리가 실제로 느린지 확인
배포 후 느린 쿼리 증가 여부 확인
관리자 목록/엑셀/통계 병목 파악

➕ 21-3. 주의

로그에 개인정보/쿼리 파라미터가 포함될 수 있음
로그 저장 비용 증가 가능
운영 설정 변경 전 영향 확인

느린 쿼리 로그는 감이 아니라 실제 운영 데이터를 기반으로 개선하게 해줍니다.
다만 로그 보안과 비용을 함께 봐야 합니다.

✅ 22. Prisma에서 실제 SQL 확인하기

Prisma를 사용하면 ORM 코드만 보고 실제 SQL을 놓치기 쉽습니다.
성능 문제를 분석하려면 Prisma가 어떤 SQL을 실행하는지 확인해야 합니다.

➕ 22-1. Query log 설정 예시

const prisma = new PrismaClient({
  log: ['query', 'error', 'warn'],
});

➕ 22-2. 개발 환경에서만 사용

운영에서 모든 query 로그 출력은 위험
개인정보/검색어 노출 가능
로그량 급증 가능
개발/스테이징에서 분석용으로 사용

➕ 22-3. 확인할 것

findMany가 어떤 SQL로 변환되는가?
include가 join인지 별도 query인지?
count가 별도로 실행되는가?
where/orderBy가 예상대로 들어가는가?
limit/offset이 적용되는가?

ORM을 써도 최종적으로는 SQL이 실행됩니다.
성능 문제가 생기면 Prisma 코드가 아니라 실제 SQL을 봐야 합니다.

✅ 23. 쿼리 최적화 전후 비교

최적화는 반드시 전후 비교가 있어야 합니다.
“빨라진 것 같다”가 아니라 숫자로 확인해야 합니다.

➕ 23-1. 비교 항목

Execution Time
DB query time
API response time
CPU 사용률
응답 payload 크기
rows scanned
rows returned

➕ 23-2. 기록 템플릿

# 쿼리 최적화 기록

## 대상 API
- 

## 기존 문제
- 

## 기존 쿼리
```sql

기존 EXPLAIN 요약

Seq Scan:
Sort:
Execution Time:

개선 작업

개선 후 EXPLAIN 요약

Index Scan:
Execution Time:

결과

API 응답 시간:
DB 쿼리 시간:
특이사항:

추가 TODO


*   이런 기록은 나중에 경력기술서에도 성과로 쓰기 좋습니다.
*   “관리자 목록 성능 개선”은 숫자가 있으면 훨씬 강한 성과가 됩니다.

---

### ✅ 24. 관리자 목록 성능 개선 우선순위

#### ➕ 24-1. 1순위: 응답 데이터 줄이기

```txt id="perf-priority-1"
SELECT * 제거
select 최소화
목록/상세 API 분리
불필요한 include 제거

➕ 24-2. 2순위: 기본 인덱스 추가

createdAt
status + createdAt
productId + createdAt
source + createdAt
phoneNormalized

➕ 24-3. 3순위: 검색 조건 개선

전화번호 정규화
contains 검색 줄이기
날짜 범위 기본값 제공
OR 조건 단순화

➕ 24-4. 4순위: 페이지네이션 개선

limit 최대값 제한
큰 offset 방지
대량 로그 cursor pagination
엑셀 ExportJob 분리

➕ 24-5. 5순위: 집계 최적화

집계 테이블
materialized view
대시보드 캐싱
비동기 통계 생성

처음부터 고급 인덱스나 materialized view로 가지 않아도 됩니다.
대부분은 select 최소화, include 제거, 기본 인덱스만으로도 많이 개선됩니다.

✅ 25. 자주 하는 실수

EXPLAIN 없이 인덱스 추가
모든 컬럼에 인덱스 추가
SELECT * 그대로 사용
목록 API에서 상세 relation 모두 include
전화번호/이름 contains 검색 남발
count 쿼리 비용 무시
offset이 큰 페이지를 방치
엑셀 다운로드를 목록 API로 처리
프론트 렌더링 병목을 DB 문제로 착각
운영 DB에서 무거운 EXPLAIN ANALYZE 실행

➕ 25-1. 특히 피해야 할 것

운영에서 where 없는 update/delete
느린 쿼리 해결한다고 무작정 DB 스펙만 올림
인덱스 추가 후 효과 측정 안 함
쿼리 로그에 개인정보 남김

DB 성능 문제는 서버 스펙을 올리면 일시적으로 숨을 수는 있습니다.
하지만 쿼리 구조가 나쁘면 데이터가 더 쌓였을 때 다시 터집니다.

✅ 26. 실무 체크리스트

➕ 26-1. 느린 쿼리 분석 체크리스트

어떤 API가 느린지 확인했는가?
특정 검색 조건에서만 느린지 확인했는가?
Prisma가 생성한 실제 SQL을 확인했는가?
EXPLAIN 또는 EXPLAIN ANALYZE를 확인했는가?
Seq Scan 여부를 확인했는가?
Sort 비용을 확인했는가?
Rows Removed by Filter를 확인했는가?
count 쿼리 비용을 확인했는가?

➕ 26-2. 인덱스 체크리스트

where 조건에 자주 쓰이는 컬럼인가?
orderBy와 함께 쓰이는 컬럼인가?
복합 인덱스 컬럼 순서가 쿼리 패턴과 맞는가?
기존 인덱스로 커버되지 않는가?
데이터가 충분히 많은 테이블인가?
인덱스 추가 후 쓰기 비용을 고려했는가?
Soft Delete 조건에는 partial index를 고려했는가?
인덱스 추가 후 성능을 다시 측정했는가?

➕ 26-3. 관리자 목록 최적화 체크리스트

목록 API에서 SELECT *를 피하는가?
select로 필요한 필드만 가져오는가?
상세 정보는 상세 API로 분리되어 있는가?
N+1 쿼리가 발생하지 않는가?
기본 정렬이 createdAt + id처럼 안정적인가?
전화번호 검색 컬럼이 정규화되어 있는가?
limit 최대값이 있는가?
엑셀 다운로드가 ExportJob으로 분리되어 있는가?

➕ 26-4. 운영 체크리스트

느린 쿼리 로그 설정을 검토했는가?
쿼리 로그에 개인정보가 남지 않는가?
대량 데이터에서 staging 성능 테스트가 가능한가?
migration으로 인덱스를 추가할 때 lock 영향을 고려하는가?
배포 후 API 응답 시간을 확인하는가?
성능 개선 전후 기록을 남기는가?
DB CPU/RAM/Connection 지표를 확인하는가?
성능 문제가 DB인지 프론트인지 구분하는가?

✅ 27. AI에게 쿼리 최적화를 물어볼 때 좋은 질문법

NestJS + Prisma + PostgreSQL 기반 관리자 상담 목록 조회가 느려져서 쿼리 최적화를 하고 싶어.

서비스 상황:
1. 상담 데이터가 계속 쌓이고 있음
2. 관리자 목록은 page/limit 기반 offset pagination을 사용함
3. 주요 필터는 status, productId, source, createdAt 범위임
4. 주요 검색은 phoneNormalized, phoneLast4, customerName keyword임
5. 기본 정렬은 createdAt desc, id desc임
6. 목록에서는 상품명, 통신사, 전화번호 마스킹, 상태, 유입 source, 생성일만 보여주면 됨
7. 상세 정보와 상태 변경 이력은 별도 API에서 조회하고 싶음
8. 엑셀 다운로드는 ExportJob으로 분리할 예정임
9. Prisma에서 실제 SQL과 EXPLAIN 결과를 확인하려고 함

요청:
- 느린 쿼리 분석 순서
- Prisma에서 실제 SQL 확인 방법
- EXPLAIN에서 봐야 할 핵심 항목
- 상담 목록에 필요한 index 후보
- 복합 인덱스 컬럼 순서 추천
- SELECT * / include 줄이는 방법
- contains 검색 최적화 방향
- count 쿼리 최적화 기준
- offset pagination 한계와 cursor 전환 기준
- 성능 개선 전후 기록 템플릿
을 실무 기준으로 정리해줘.

➕ 27-1. AI 답변 검증 기준

EXPLAIN 없이 인덱스부터 추가하라고 하지 않는가?
SELECT *와 과한 include 문제를 지적하는가?
where/orderBy 기준으로 복합 인덱스를 제안하는가?
인덱스가 쓰기 비용을 늘릴 수 있음을 설명하는가?
전화번호 검색과 개인정보 보안을 함께 고려하는가?
count 쿼리와 offset pagination 한계를 설명하는가?
엑셀 다운로드를 목록 API와 분리하는가?
개선 전후 측정을 강조하는가?
운영 DB에서 무거운 EXPLAIN ANALYZE를 조심하라고 하는가?

📌 요약

느린 쿼리는 관리자 목록, 검색, 필터, 정렬, 엑셀 Export, 유입 분석처럼 데이터가 쌓이는 기능에서 자주 발생합니다.
쿼리 최적화는 감으로 인덱스를 추가하는 것이 아니라, 느린 API 확인 → 실제 SQL 확인 → EXPLAIN 분석 → 병목 파악 → 개선 → 전후 비교 순서로 진행해야 합니다.
PostgreSQL의 EXPLAIN은 예상 실행 계획을 보여주고, EXPLAIN ANALYZE는 실제 쿼리를 실행해 시간을 보여주므로 운영 DB에서는 주의해서 사용해야 합니다.
실행 계획에서는 Seq Scan, Index Scan, Sort, Rows Removed by Filter, Execution Time을 우선 확인하면 됩니다.
관리자 상담 목록에서는 status + created_at + id, product_id + created_at, source + created_at, phone_normalized 같은 인덱스가 후보가 될 수 있습니다.
복합 인덱스는 컬럼 순서가 중요하며, 보통 고정 필터 컬럼을 앞에 두고 정렬/범위 컬럼을 뒤에 둡니다.
인덱스는 조회를 빠르게 하지만 insert/update/delete 비용과 저장공간을 증가시키므로 실제 조회 패턴과 EXPLAIN 결과를 기준으로 추가해야 합니다.
목록 API에서는 SELECT *를 피하고, Prisma select로 필요한 필드만 가져오며, 상세 정보와 이력은 상세 API로 분리하는 것이 좋습니다.
LIKE '%keyword%'나 Prisma contains 검색은 대량 데이터에서 느려질 수 있어 전화번호 정규화 컬럼, 정확 검색, trigram index 등을 상황에 따라 고려해야 합니다.
count 쿼리와 큰 offset은 데이터가 많아질수록 병목이 될 수 있으므로, 대량 로그 화면은 cursor pagination이나 hasNextPage 방식을 검토할 수 있습니다.
쿼리 최적화 결과는 API 응답 시간, DB query time, EXPLAIN Execution Time, payload 크기 기준으로 전후 비교해 기록하는 것이 좋습니다.

juni

이전 포스트

TIL - 20260820

다음 포스트