Top50 데이터베이스 쿼리

모든 테이블을 연결했다면, 이제 쿼리를 몇 개 짜볼 차례야. 근데 몇 개만 쓰는 건 초보들이나 하는 거지. 넌 이미 프로니까, 무려 50(!)개의 쿼리를 네 데이터베이스에 써야 해. 그리고 이게 진짜 필요한 것들만 모은 거야.

데이터베이스 쿼리

1. 메인 페이지용 상품 리스트 가져오기

이 쿼리는 모든 활성화된 상품의 기본 가격과 이미지를 반환해서 메인 페이지랑 카탈로그에 보여줄 수 있게 해줘. 이렇게 하면 쇼케이스를 빠르게 만들고 상품 정보를 최신으로 유지할 수 있어.

2. 키워드로 상품 검색

사용자가 상품 이름이나 설명에 키워드가 포함된 상품을 찾을 수 있게 해줘. 이건 카탈로그에서 빠른 검색을 위한 필수 기능이야.

3. 상품 ID로 상품 상세 정보

특정 상품의 상세 정보(브랜드, 카테고리 포함)를 반환해. 상품 상세 페이지에 꼭 필요하지.

4. 상품 옵션 리스트

상품의 모든 옵션(SKU): 사이즈, 색상, 재고, 가격을 보여줘. 상품 페이지에서 원하는 옵션을 고를 때 써.

5. 상품 이미지 갤러리

상품 카드에 모든 사진이 중요해. 이 쿼리는 메인 이미지를 포함한 모든 이미지를 반환해.

6. 상품 평균 평점과 리뷰 수

상품 평점과 리뷰 개수를 보여주는데, 이건 신뢰도와 평판에 중요해.

7. 상품 리뷰 상세 리스트

상품 카드의 리뷰 섹션: 평점, 내용, 작성자, 날짜. 신규 구매자들이 구매 결정할 때 도움돼.

8. 상품 Q&A

각 상품별 질문과 답변을 가져오는 쿼리야. 상품 카드의 FAQ 블록에 필요해.

9. 카테고리 계층 구조

카탈로그 구조를 시각화하고, 필터/메뉴용 네비게이션 트리를 만들 때 써.

10. 카테고리 및 하위 카테고리별 상품

선택한 카테고리 또는 그 "자식" 카테고리(계층 포함)의 모든 상품을 보여줘.

11. 브랜드 리스트

브랜드별 필터, 브랜드 리스트, 랜딩 페이지 만들 때 써.

12. 인기 태그와 해당 상품 수

가장 많이 쓰인 태그를 분석해서 트렌드 상품이나 태그 클라우드 만들 때 사용해.

13. 상품별 가격 변경 이력

가격 변화(이전/현재 가격, 프로모션 등) 분석 및 표시용.

14. 상품 상태 변경 이력

상품의 라이프사이클, 쇼케이스에서 사라진 이유, 복귀 이유 추적에 써.

15. 인증서 및 라이선스 검색

프로 구매자와 B2B에 필수(품질, 합법성 체크).

16. 상품 공급자 정보

관리, 품질 관리, 공급자 연락에 중요해.

17. 창고별 상품 재고

창고별 실시간 재고 관리. 물류 시스템과 "품절" 방지에 필수.

18. 임계치 이하 재고 상품

자동 재고 보충, 품절로 인한 판매 손실 방지.

19. 창고 내 상품 이동(감사)

기간별 상품 입출고, 조정 내역 추적. 재고 조사, 손실 방지에 중요.

20. 창고 간 이동 이력

물류센터 간 상품 이동 내역과 상태를 볼 수 있어.

21. 배송 방법 및 요금

주문 시 배송비 계산 및 사용자 안내용.

22. 사용자 주문 이력

마이페이지의 핵심 — 모든 주문, 상태, 금액.

23. 주문 상세(아이템 포함)

주문 구조(구성, 가격, 수량 등)를 프론트나 지원팀에서 볼 때 써.

24. 기간/상태별 주문 리포트

판매 분석 및 리포트: 기간, 상태(예: "완료")별 주문 반환.

25. "버려진" 장바구니

마케터용 분석: 주문하지 않은 장바구니 — 리타겟팅 후보.

26. 베스트셀러

"인기 상품" 블록, 마케팅 추천용: 가장 많이 팔린 상품.

27. 일별 판매(그래프용)

일일 매출 리포트 — 비즈니스 동향 분석, 그래프 생성의 기본.

28. 반품 리스트

모든 주문의 반품 내역, 사유와 상태 포함. 반품 원인 분석에 도움.

29. 주문 취소 리스트

손실 및 취소 사유 관리: 누가, 언제, 왜 취소했는지 표시.

30. 발송 대기 주문

창고/배송팀용 — 포장 및 발송 대기 주문, 배송 상세 포함.

31. 평균 주문 금액

"Average Order Value" — 마케팅/상품 구성 효율성 평가 핵심 지표.

32. 프로모코드 사용 주문

프로모션 효과 분석: 어떤 프로모코드가 얼마나 자주 쓰였는지.

33. 카테고리/브랜드별 할인 사용

어떤 프로모션이 효과적인지, 카테고리/브랜드별 할인 인기 모니터링.

34. 사용된 프로모코드와 사용자

프로모코드 사용 모니터링, 이상/남용 탐지.

35. 주문별 결제 이력

지원/회계용: 모든 결제 트랜잭션, 상태, 결제 방법 표시.

36. 환불 주문

환불 분석, 회계 리포트 생성, 사기 방지용.

37. 사용자 지갑 잔액 및 트랜잭션 이력

보너스/캐시백 잔액, 이력 관리 및 표시.

38. 사용자의 지원 요청

사용자가 자신의 문의 및 처리 상태를 볼 수 있게 해줘.

39. 지원 요청 SLA 분석

우선순위별 평균 응답/해결 시간 분석. SLA 관리에 중요.

40. 지원 요청 메시지

선택한 지원 요청의 전체 대화 내역 보기. 사용자/지원팀 모두에 중요.

41. 카테고리별 활성 FAQ

고객 지식베이스용 자주 묻는 질문. 지원팀 부담 줄여줌.

42. 활성 마케팅 캠페인 및 배너

사이트에 최신 광고/프로모션 표시용.

43. 메인 페이지 추천 상품

"추천" 블록: 메인에 강조할 상품.

44. A/B 테스트 이력

UX/마케팅 최적화를 위한 실험 분석.

45. 특정 사용자의 상품 조회 이력

"최근 본 상품" 또는 개인화 추천에 사용.

46. 인기 검색어

사용자 수요 분석, 검색/추천 최적화에 도움.

47. 트래픽 소스 분석

어떤 광고 채널이 트래픽/전환을 유도하는지 평가.

48. 코호트별 사용자 유지율

충성도, 재구매 평가 핵심 지표.

49. 메인용 뉴스/블로그

블로그 뉴스/기사 노출, 사용자 참여도 향상.

50. 활성 사이트 페이지 및 연관 콘텐츠 블록

사이트 콘텐츠 무결성, CMS 작동, 페이지 데이터 표시 체크용.

인덱스 추가하기

쿼리만 잘 짜면 뭐해, 빨라야지! 그래서 인덱스를 좀 추가해야 해. 프로젝트 주요 테이블에 40개 인덱스를 추가해서 쿼리 성능과 운영 편의성을 높여보자.

1. product.product(status) 인덱스

거의 모든 상품 쿼리가 status로 필터링돼(예: 쇼케이스, 검색 등). 이 인덱스는 특정 status 상품을 빠르게 찾게 해줘서 전체 테이블 스캔을 줄여.

2. product.variant(product_id, is_active) 인덱스

SKU/쇼케이스 쿼리는 상품 연결과 옵션 활성화 여부로 필터링해. 이 복합 인덱스 덕분에 특정 상품의 활성 옵션을 최적화해서 뽑을 수 있어.

3. product.image(product_id, is_main DESC) 인덱스

상품의 메인 이미지(또는 전체 리스트)를 가져올 때 상품별 필터와 "메인" 정렬을 써. 이 인덱스가 갤러리 데이터 반환을 빠르게 해줘.

4. product.product(name text_pattern_ops) 인덱스

상품명 키워드 검색(ILIKE '%...%')을 빠르게 하려면 name text_pattern_ops 인덱스가 좋아. 특히 대용량에서 부분 문자열 검색에 효과적이야.

5. product.product(description gin_trgm_ops) 인덱스

4번과 비슷하게, 상품 설명 검색(ILIKE/풀텍스트)에 gin_trgm_ops GIN 인덱스가 텍스트 필드 필터링을 빠르게 해줘.

6. product.product(category_id) 인덱스

카테고리별(또는 하위 카테고리 포함) 상품 조회가 많아. 이 인덱스가 특정 카테고리 상품을 빠르게 찾아줘.

7. product.category(parent_id) 인덱스

카테고리 계층 구조/네비 트리 만들 때 parent_id로 조회를 자주 해. 이 인덱스가 재귀적 계층 쿼리를 빠르게 해줘.

8. product.review(product_id) 인덱스

상품 리뷰 쿼리는 product_id로 필터링해(평균 평점, 리뷰 리스트 등). 이 필드 인덱스가 집계/리뷰 조회를 훨씬 빠르게 해줘.

9. product.review(product_id, created_at DESC) 인덱스

최신 리뷰 빠른 조회(ORDER BY createdat DESC, productid 필터)엔 복합 인덱스가 좋아.

10. product.question(product_id, created_at DESC) 인덱스

특정 상품 Q&A 쿼리, 생성일 정렬까지 커버. 인덱스가 Q&A 섹션 빠른 조회에 도움.

11. product.answer(question_id, created_at) 인덱스

상품 Q&A 답변은 question_id로 빠른 접근이 필요하고, 날짜 정렬도 자주 써. 이 인덱스가 Q&A 생성 지연을 최소화해.

12. product.price_history(variant_id, changed_at DESC) 인덱스

상품 옵션별 가격 변경 이력, 최근 변경 위주 조회에 필수. 가격 변화/이전-현재 가격 분석 쿼리 속도 향상.

13. product.status_history(product_id, changed_at DESC) 인덱스

상품 상태 변경 이력(감사, 라이프사이클 관리) 쿼리에 복합 인덱스가 필수.

14. product.certificate(product_id) 인덱스

B2B/인증 쇼케이스에서 상품 인증서 id로 빠른 검색이 필요해. 이 인덱스가 체크를 빠르게 해줘.

15. product.license(product_id) 인덱스

상품별 라이선스 검색, 특히 라이선스 타입 필터 쿼리에 사용.

16. product.product_tag(tag_id) 인덱스

특정 태그의 모든 상품(또는 반대) 빠른 조회. 태그 클라우드/필터에 필수.

17. product.product_tag(product_id) 인덱스

특정 상품에 연결된 태그 빠른 조회, 태그별 상품 추천에 도움.

18. logistics.inventory(product_id, warehouse_id) 인덱스

창고별 상품 재고 실시간 확인(또는 전체 창고 계산)에 필수. 물류, 재고 체크, 실시간 쇼케이스에 중요.

19. logistics.inventory(variant_id) 인덱스

옵션별(색상/사이즈) 재고 관리, 통합 리포트에 사용.

20. logistics.stock_level(product_id, warehouse_id) 인덱스

창고별 상품 최소 재고 임계치 빠른 체크(자동 주문, 재고 부족 알림 등). inventory와 비교용.

21. logistics.inventory_movement(product_id, changed_at DESC) 인덱스

최근 기간 상품 이동 이력(감사) 빠른 조회. 오류 방지, 손실 분석, 공급 관리에 도움.

22. logistics.transfer(product_id, requested_at DESC) 인덱스

창고 간 이동 분석, 상품별 필터, 요청일 정렬 쿼리에 사용.

23. logistics.shipping_rate(shipping_method_id, destination_zone) 인덱스

배송비 계산 시 방법 id와 목적지 존으로 요금 선택. 주문 시 계산 속도 향상.

24. "order".order(user_id, placed_at DESC) 인덱스

사용자 주문 이력 쿼리는 user_id, 주문일 정렬을 써. 복합 인덱스가 마이페이지 주문 이력 빠른 반환에 필수.

25. "order".order(status, placed_at) 인덱스

기간별/상태별 주문 분석, "처리중"/"완료" 등 상태 검색에 사용.

26. "order".order_item(order_id) 인덱스

주문별 아이템 전체 조회 — 주문 상세 쿼리에서 가장 많이 쓰임.

27. "order".order_item(product_id) 인덱스

상품별 판매 분석, 통계 쿼리에 필수.

28. "order".return(order_id) 인덱스

반품-주문 연결, 반품 지원/분석 쿼리 속도 향상.

29. "order".cancellation(order_id) 인덱스

반품과 비슷하게, 주문 취소 분석/지원 쿼리 속도 향상.

30. "order".cart(user_id, updated_at DESC) 인덱스

사용자 최근 장바구니(예: "버려진" 장바구니) 조회에 user_id, 최근 업데이트일 정렬 인덱스가 편해.

31. payment.payment_transaction(order_id) 인덱스

결제 이력 쿼리 대부분이 주문별 필터를 써. 이 인덱스가 주문 트랜잭션 즉시 접근을 보장해.

32. payment.refund(transaction_id) 인덱스

특정 트랜잭션별 환불 빠른 조회. 지원, 리포트, 사기 방지에 필수.

33. payment.wallet(user_id) 인덱스

사용자 지갑 잔액/이력 빠른 확인용.

34. payment.wallet_transaction(wallet_id, created_at DESC) 인덱스

지갑 트랜잭션 일자 정렬 조회(예: 이력 표시)에 사용.

35. support.support_ticket(user_id, created_at DESC) 인덱스

특정 사용자의 지원 요청 이력(마이페이지/고객센터) 복합 인덱스가 필수.

36. support.ticket_message(ticket_id, sent_at) 인덱스

티켓별 전체 메시지 일자 정렬 조회 — 시간순 정렬에 빠름.

37. support.ticket_sla_tracking(ticket_id) 인덱스

SLA 분석/관리, 티켓별 SLA 데이터 빠른 접근.

38. marketing.promo_usage(user_id, used_at DESC) 인덱스

프로모코드 사용자별 활동 분석(남용 방지 포함), user_id+일자 정렬 인덱스가 필수.

39. analytics.product_view(user_id, viewed_at DESC) 인덱스

사용자별 상품 조회 이력(개인화, 추천)에 user_id+일자 정렬 인덱스가 필수.

40. analytics.search_query_log(query_text) 인덱스

인기 검색어, 사용 빈도 집계 — 검색 분석의 핵심. 쿼리 텍스트 인덱스가 집계/카운트 속도를 높여줘.

참고

ILIKE 텍스트 검색엔 pg_trgm 확장 GIN 인덱스를 추천해. 부분 문자열/퍼지 검색에 효과적이야. 날짜별 집계/정렬이 많은 대용량 테이블엔 날짜 DESC 인덱스가 최근 데이터 조회를 빠르게 해줘.

실제 실행 계획/부하 통계에 따라 인덱스를 튜닝하는 게 좋지만, 위 인덱스만으로도 우리 마켓플레이스의 주요 쿼리 시나리오를 커버할 수 있어.

함수 추가하기

아직 안 지쳤지? 그럼 기존/미래 쿼리 작성을 더 쉽게 해줄 함수도 몇 개 만들어보자. 핵심 쿼리 구현 속도, 코드 중복 감소, DB단 비즈니스 로직 중앙화를 위해서야.

1. 태그/브랜드까지 고려한 키워드 상품 검색 함수

왜 필요해:

이름/설명만 검색하면 부족하지. 태그/브랜드까지 검색해야 할 때가 많아. 이 함수로 확장 검색 로직을 중앙화해서 코드 중복 줄이고 프론트 연동도 쉬워져.

2. 상품 ID로 전체 상품 카드(모든 정보) 가져오기

왜 필요해:

프론트에선 상품의 모든 정보(기본 필드, 브랜드, 카테고리, 이미지, 태그, 속성, 평균 평점, 리뷰 수 등)가 한 번에 필요해. 이 함수로 한 번에 상품 카드 데이터를 받아서 DB 호출 횟수를 줄일 수 있어.

3. 계층 구조 카테고리 트리 가져오기

왜 필요해:

쇼케이스, 필터, breadcrumbs용 카테고리 트리(또는 경로) 생성에 필요해. 클라이언트에서 재귀 쿼리 대신, 이 함수로 전체 계층을 한 번에 받아.

4. 카테고리별 평균/최소/최대 가격 계산

왜 필요해:

카탈로그 필터/분석에 카테고리별 가격 범위, 평균값이 필요해. 이 함수로 반복 서브쿼리 없이 집계 통계를 받아.

5. 모든 창고별 상품 재고 자동 계산/체크

왜 필요해:

상품(및 옵션) 전체 재고를 즉시 알 수 있어. 쇼케이스, 창고, 물류에 유용. 계산 로직을 중앙화해서 비즈니스 로직 중복을 막아.

6. 사용자 주문 이력(상세 포함) 가져오기

왜 필요해:

사용자 주문 리스트(아이템, 금액, 상태 포함)를 반환해서 프론트에서 한 번에 마이페이지를 만들 수 있어.

7. 사용자 판매자/구매자 평균 평점 계산

왜 필요해:

플랫폼에서 신뢰/평판 표시용. 판매자/구매자 평균 평점을 집계 계산해.

8. 프로모코드 사용(모든 조건 포함 검증) 함수

왜 필요해:

프로모코드 검증/차감(활성, 한도, 날짜 등) 비즈니스 로직을 한 함수에 중앙화. 앱 로직이 단순해지고, 조건 중복 오류도 막아.

9. 사용자 이벤트 로깅 통합 함수

왜 필요해:

분석/감사용 이벤트 로깅을 중앙화해서 코드 중복 줄이고, 사용자 행동 데이터 누락 위험도 줄여.

10. 보너스 지갑 잔액/누적 적립액 조회 함수

왜 필요해:

한 번에 현재 잔액과 누적 적립액을 받아서 대시보드에 표시할 수 있어. SQL 쿼리 수도 줄어.

11. 주문 상태 변경+로깅 통합 함수

왜 필요해:

주문 상태를 바꾸고, 상태 변경 이력 로그도 남겨. 앱 여러 곳에서 상태 변경 오류를 최소화해.

12. 지원 티켓 전체 메시지(티켓+모든 메시지) 가져오기

왜 필요해:

티켓 전체 대화(요청 상세+모든 메시지)를 반환해서 프론트에서 티켓 히스토리 만들기 쉬워.

13. 이메일/전화로 사용자 존재 여부 체크

왜 필요해:

회원가입/비번 찾기용. 프론트/백엔드 로직 중복 방지.

참고

이 함수 세트는 주요 비즈니스 시나리오를 커버하고, 데이터 작업 편의성, 로직 최적화, 프론트/통합 개발 속도를 높여줘. 재밌게 공부했길 바래 :)

솔루션이 포함된 파일