Skip to content

01.DBPerformanceOptimizationReport.md

Latest commit

 

History

History
324 lines (248 loc) · 13.2 KB

01.DBPerformanceOptimizationReport.md

File metadata and controls

324 lines (248 loc) · 13.2 KB

DB 성능 최적화 보고서

📋 성능 개선 요약

기능 인덱스 전 쿼리 시간 인덱스 후 쿼리 시간 개선율 비고
잔액 조회 30.9ms 0.258ms 약 99.2% 개선 단일 인덱스 (user_id)
재고 조회 18.7ms 0.224ms 약 98.8% 개선 단일 인덱스 (product_id)
사용자 쿠폰 조회 (유형 1) 33.3ms 0.101ms 약 99.7% 개선 복합 인덱스 (user_id, used_status)
사용자 쿠폰 조회 (유형 2) 35.9ms 0.0431ms 약 99.9% 개선 복합 인덱스 (user_id, coupon_id)
상품 조회 (인덱스 적용) 39.4ms 68.5ms 약 73.4% 성능 저하 인덱스 비효율 (카디널리티 낮음)
상품 조회 (일반 페이징) - 0.456ms - OFFSET 적을 때 유리
상품 조회 (커서 페이징) - 0.14ms - 가장 효율적
인기 상품 조회 (결제 테이블) 40.9ms 20.9ms 약 48.8% 개선 복합 인덱스 (payment_status, paid_at)
인기 상품 조회 (주문 상품) 46.3ms 1.21ms 약 97.4% 개선 단일 인덱스 (order_id)

🏆 목적

  • 조회 시 성능 저하가 발생할 수 있는 주요 기능을 선정하고, 쿼리 튜닝, 인덱스 설계, 테이블 구조 재검토 등을 통해 효율적인 DB 성능 개선 방안을 도출하는 것이 목적이다.

🎯 대상 기능 선정

1️⃣ 잔액 조회

  • 단건 조회지만, 결제 및 잔액 사용 및 충전에서도 사용되는 기능으로 속도 보장 필요.
  • 사용자 ID(user_id) 기반으로 잔액을 단건 조회.
  • user_id는 유니크하므로 단일 인덱스 생성으로 빠른 조회 가능.

2️⃣ 재고 조회

  • 결제 및 상품 구매 시 사용되는 기능으로 속도 보장 필요하며, 상품이 많아질수록 성능 저하 위험이 있어 최적화 필요.
  • 상품 ID(product_id) 기반의 단건 재고 조회.
  • product_id도 유니크한 값으로 단일 인덱스가 효과적.

3️⃣ 사용자 쿠폰 조회

조회 유형 1: 사용자 ID + 사용 가능 상태(used_status)

  • 사용 가능한 쿠폰 목록 조회 기능으로 사용자 수가 늘어날수록 성능 저하 우려.
  • user_id: 카디널리티 높음
  • used_status: 카디널리티 낮음 → (user_id, used_status) 순서의 복합 인덱스가 유리함.

조회 유형 2: 사용자 ID + 쿠폰 ID

  • 단건 쿠폰 조회 시 사용되는 기능으로 성능 저하 우려는 낮으나, 복합 인덱스가 필요한지 검토 필요.
  • 두 컬럼 모두 조건에 사용되므로 복합 인덱스가 효과적.

4️⃣ 상품 조회

  • 판매 중인 상품 목록 전체 조회.
  • 상품 수가 증가할수록 성능 저하 우려.
  • "인덱스 + 페이징 처리(커서 기반)" 을 통해 성능 최적화 가능.

5️⃣ 인기 상품 조회

  • 결제 이력을 기반으로 인기 상품을 조회하는 기능.
  • 실시간 조회는 쓰기 성능에 영향을 줄 수 있음.
  • 통계 테이블 + 배치 처리 전략, 인덱스 적용 필요.
  • 비지니스 요구사항이 바뀔 수 있는 우려가 있어 이번에는 인덱스만 적용해서 성능을 최적화 하기로 함.

기타 기능은 대부분 PK 기반 단건 조회이며, 현재는 성능 이슈 우려가 낮음.

🔍 상세 분석 및 개선 내역

10만 건 이상의 더미 데이터를 기반으로, EXPLAIN ANALYZE 명령어를 통해 인덱스 적용 전후의 성능을 분석하였다.

EXPLAIN ANALYZE 는 쿼리를 실제 실행하면서 각 연산의 비용, 실제 시간, 행 수를 분석할 수 있다.

📌 잔액 조회

🛠️ JPA

balanceJpaRepository.findByUserId(userId);

🔍 인덱스 적용 전

EXPLAIN ANALYZE SELECT * FROM balance WHERE user_id = 10000;
-> Filter: (balance.user_id = 1000)  (cost=9565 rows=9493) (actual time=1.96..30.9 rows=1 loops=1)
-> Table scan on balance  (cost=9565 rows=94931) (actual time=1.69..25.7 rows=100000 loops=1)

✅ 인덱스 생성

CREATE INDEX idx_user_id ON balance(user_id);

⚡️ 인덱스 적용 후

-> Index lookup on balance using idx_user_id (user_id=10000)  (cost=0.35 rows=1) (actual time=0.248..0.258 rows=1 loops=1)

30.9ms → 0.258ms (약 99.2% 개선)

📌 재고 조회

🛠️ JPA

stockJpaRepository.findByProductId(productId);

🔍 인덱스 적용 전

EXPLAIN ANALYZE SELECT * FROM stock WHERE product_id = 10000;
-> Filter: (stock.product_id = 10000)  (cost=10095 rows=10022) (actual time=2.5..18.7 rows=1 loops=1)
-> Table scan on stock  (cost=10095 rows=100224) (actual time=0.907..15.4 rows=100000 loops=1)

✅ 인덱스 생성

CREATE INDEX idx_product_id ON stock(product_id);

⚡️ 인덱스 적용 후

-> Index lookup on stock using idx_product_id (product_id=10000)  (cost=0.35 rows=1) (actual time=0.217..0.224 rows=1 loops=1)

18.7ms → 0.224ms (약 98.8% 개선)

📌 사용자 쿠폰 조회

🛠️ JPA (유형 1 : 사용자 ID + 사용 가능 상태)

userCouponJpaRepository.findByUserIdAndUsedStatusIn(userId, statuses);

🔍 인덱스 적용 전

EXPLAIN ANALYZE SELECT * FROM user_coupon where user_id = 10 and used_status = 'UNUSED';
-> Filter: ((user_coupon.used_status = 'UNUSED') and (user_coupon.user_id = 10))  (cost=10101 rows=4998) (actual time=6.29..33.3 rows=6 loops=1)
-> Table scan on user_coupon (cost=10101 rows=99964) (actual time=0.577..23.6 rows=100000 loops=1)

✅ 인덱스 생성

CREATE INDEX idx_user_status ON user_coupon(user_id, used_status);

⚡️ 인덱스 적용 후

-> Index lookup on user_coupon using idx_user_status (user_id=10, used_status='UNUSED'), with index condition: (user_coupon.used_status = 'UNUSED')  (cost=2.1 rows=6) (actual time=0.0959..0.101 rows=6 loops=1)

33.3ms → 0.101ms (약 99.7% 개선)

🛠️ JPA (유형 2 : 사용자 ID + 쿠폰 ID)

userCouponJpaRepository.findByUserIdAndCouponId(userId, couponId);

🔍 인덱스 적용 전

EXPLAIN ANALYZE SELECT * FROM user_coupon where user_id = 10 and coupon_id = 242;
-> Filter: ((user_coupon.coupon_id = 242) and (user_coupon.user_id = 10))  (cost=10101 rows=1000) (actual time=1.38..35.9 rows=1 loops=1)
-> Table scan on user_coupon  (cost=10101 rows=99964) (actual time=1.37..30.3 rows=100000 loops=1)

✅ 인덱스 생성

CREATE INDEX idx_user_coupon ON user_coupon(user_id, coupon_id);

⚡️ 인덱스 적용 후

-> Index lookup on user_coupon using idx_user_coupon (user_id=10, coupon_id=242)  (cost=0.35 rows=1) (actual time=0.0406..0.0431 rows=1 loops=1)

35.9ms → 0.0431ms (약 99.9% 개선)

📌 상품 조회

🛠️ JPA

productJpaRepository.findBySellStatusIn(statuses);

🔍 인덱스 적용 전

EXPLAIN ANALYZE SELECT * FROM product WHERE sell_status IN ('SELLING');
-> Filter: (product.sell_status = 'SELLING')  (cost=10087 rows=33328) (actual time=0.0709..39.4 rows=89997 loops=1)
-> Table scan on product  (cost=10087 rows=99984) (actual time=0.0675..26.9 rows=100000 loops=1)

⛔️ 인덱스 생성

CREATE INDEX idx_sell_status ON product(sell_status);

🔥 인덱스 적용 후

-> Index lookup on product using idx_sell_status (sell_status='SELLING'), with index condition: (product.sell_status = 'SELLING')  (cost=5264 rows=49992) (actual time=0.455..68.5 rows=89997 loops=1)

39.4ms → 68.5ms (약 73.4% 성능 저하)

  • sell_status = 'SELLING' 조건의 결과가 전체 상품의 약 90%를 차지
  • 카디널리티가 낮은 컬럼이므로 전체 테이블 스캔이 더 빠름
  • 인덱스가 오히려 성능을 저하시킴

✅ 일반 페이징 적용

SELECT * FROM product WHERE sell_status IN ('SELLING') LIMIT 0, 10;
-> Limit: 10 row(s)  (cost=10087 rows=10) (actual time=0.445..0.456 rows=10 loops=1)
    -> Filter: (product.sell_status = 'SELLING')  (cost=10087 rows=33328) (actual time=0.444..0.453 rows=10 loops=1)
        -> Table scan on product  (cost=10087 rows=99984) (actual time=0.439..0.444 rows=12 loops=1)
  • actual time=0.445..0.456 : 실제 쿼리 실행시간이 0.456ms 소요됨
  • offset이 깊어질수록 성능 저하가 발생할 수 있음

✅ 커서 기반 페이징 적용

SELECT * FROM product WHERE sell_status IN ('SELLING') AND product_id > 1050 LIMIT 0, 10;
-> Limit: 10 row(s)  (cost=10014 rows=10) (actual time=0.132..0.14 rows=10 loops=1)
    -> Filter: (product.product_id > 1050)  (cost=10014 rows=49992) (actual time=0.13..0.137 rows=10 loops=1)
        -> Index range scan on product using PRIMARY over (1050 < product_id)  (cost=10014 rows=49992) (actual time=0.127..0.133 rows=10 loops=1)
  • actual time=0.132..0.14 : 실제 쿼리 실행시간이 0.14ms 소요됨
  • product_id가 PK 이므로 인덱스 스캔을 통해 빠른 조회 가능

📊 성능 비교

전략 쿼리 시간 특징
인덱스 미사용 39.4ms Full Table Scan이지만 오히려 효율적
인덱스 사용 68.5ms Row 수가 너무 많아 비효율적
일반 페이징 0.456ms 작은 OFFSET에선 빠르나, 페이지가 깊어질수록 느려짐
커서 페이징 0.14ms 인덱스 스캔을 통해 빠른 조회 성능

📌 인기 상품 조회

🛠️ JPA (결제 정보 조회)

paymentJpaRepository.findByPaymentStatusInAndPaidAtBetween(statuses, startDateTime, endDateTime);

🔍 인덱스 적용 전

EXPLAIN ANALYZE 
SELECT * FROM payment 
WHERE payment_status IN ('COMPLETED') 
AND paid_at BETWEEN '2024-01-01' AND '2024-12-31';
-> Filter: ((payment.payment_status = 'COMPLETED') and (payment.paid_at between '2024-01-01' and '2024-12-31'))  (cost=10094 rows=2220) (actual time=0.904..40.9 rows=13858 loops=1)
-> Table scan on payment  (cost=10094 rows=99899) (actual time=0.866..25.6 rows=100000 loops=1)

✅ 인덱스 생성

  • payment_status: 카디널리티가 낮을 가능성이 높지만, IN 조건이 먼저 나오므로 선행 컬럼으로 지정
  • paid_at: 범위 조건(BETWEEN)이기 때문에, 복합 인덱스의 두 번째 컬럼으로 배치
CREATE INDEX idx_payment_status_paid_at ON payment(payment_status, paid_at);

⚡️ 인덱스 적용 후

-> Index range scan on payment using idx_payment_status_paid_at over (payment_status = 'COMPLETED' AND '2024-01-01 00:00:00.000000' <= paid_at <= '2024-12-31 00:00:00.000000'), with index condition: ((payment.payment_status = 'COMPLETED') and (payment.paid_at between '2024-01-01' and '2024-12-31'))  (cost=11879 rows=26398) (actual time=8.21..20.9 rows=13858 loops=1)

40.9ms → 20.9ms (약 48.8% 개선)

🛠️ JPA (주문 상품 조회)

orderProductJpaRepository.findByOrderIdIn(orderIds);

🔍 인덱스 적용 전

EXPLAIN ANALYZE SELECT * FROM order_product WHERE order_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20);
-> Filter: (order_product.order_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20))  (cost=10104 rows=49918) (actual time=0.559..46.3 rows=36 loops=1)
-> Table scan on order_product  (cost=10104 rows=99835) (actual time=0.395..41.9 rows=100000 loops=1)

✅ 인덱스 생성

CREATE INDEX idx_order_id ON order_product(order_id);

⚡️ 인덱스 적용 후

-> Index range scan on order_product using idx_order_id over (order_id = 1) OR (order_id = 2) OR (18 more), with index condition: (order_product.order_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20))  (cost=23 rows=40) (actual time=0.246..1.21 rows=36 loops=1)

46.3ms → 1.21ms (약 97.4% 개선)

📈 결론 및 향후 계획

✅ 결론 요약

  • 총 5개 주요 기능에 대한 인덱스 적용을 통해 최대 99.9%의 성능 개선 효과를 확인함.
  • 특히 잔액 조회, 사용자 쿠폰 조회 등 핵심 트래픽 구간에서 대폭 개선.
  • 반면, 카디널리티가 낮은 컬럼에 대한 인덱스는 오히려 성능 저하를 유발할 수 있음.

🔧 향후 계획

인덱스 한계

  • 인덱스로 해결이 어려운 경우, Sync Schedule Strategy, Materialized View, Sharding 등 구조적 대안 검토

기술적 확장 고려

  • Redis, Elasticsearch 등 서브 시스템 도입을 통한 분산 처리 전략 수립

CQRS 기반의 인프라 디자인

  • 조회(Read)는 리플리케이션 DB(슬레이브)로 분리하고, 쓰기(Write)는 마스터 DB에서 처리하는 구조로 전환 이를 통해 읽기 부하 분산 및 서비스 확장성 확보