챗지피터에서 쿼리 조언 구하기

2025. 4. 29. 15:23ㆍ잡

반응형

 

 

 

 

 

아래 쿼리를 리팩토링 안해도 될거 같아? 잘짠거 같지?

SELECT DISTINCT 
    1,
    IFNULL(rss.user_count, 0) AS user_count,
    IFNULL(pt.price_won, 0) AS price_won,
    IFNULL((
        (
            SELECT SUM(rw.star)
            FROM review rw
            INNER JOIN product pt ON pt.id = rw.product_id
                AND pt.deleted_at IS NULL
            WHERE pt.golf_or_tour_id = gf.id
                AND rw.deleted_at IS NULL
        ) / (
            SELECT COUNT(1)
            FROM review rw
            INNER JOIN product pt ON pt.id = rw.product_id
                AND pt.deleted_at IS NULL
            WHERE pt.golf_or_tour_id = gf.id
                AND rw.deleted_at IS NULL
        )
    ), 0) AS avg_rating,
    (
        SELECT COUNT(1)
        FROM golf_reservation
        WHERE golf_id = gf.id
            AND deleted_at IS NULL
    ) AS reservation_count,
    gf.*
FROM golf gf
INNER JOIN region rg ON rg.id = gf.region_id
    AND rg.deleted_at IS NULL
INNER JOIN country cy ON cy.id = gf.country_id
    AND cy.deleted_at IS NULL
LEFT JOIN product pt ON pt.golf_or_tour_id = gf.id
    AND pt.deleted_at IS NULL
LEFT JOIN review_star_summary rss ON rss.golf_id = gf.id
    AND rss.deleted_at IS NULL
WHERE gf.deleted_at IS NULL;

 

 

 

 

 

 

이 쿼리는 전반적으로 의도는 명확하고 잘 작성된 편이지만,
성능과 가독성 측면에서 리팩토링 여지가 충분히 있습니다.
특히 서브쿼리 중복, DISTINCT 1의 불필요성, JOIN 중복 가능성 등을 고려하면 리팩토링이 권장됩니다.

🔍 핵심 문제 및 개선 포인트

1. ✅ DISTINCT 1은 불필요

  • 1은 모든 행에 대해 같은 값이므로 DISTINCT로 중복 제거하는 건 무의미합니다.
  • 추천: DISTINCT를 제거하거나, gf.id 같은 고유 값으로 판단하세요.
-- 이 줄은 삭제해도 무방
SELECT 
    ifnull(rss.user_count, 0) as user_count,

2. ⚠️ AVG 계산 서브쿼리 → 같은 조건이 두 번 중복됨

(
  select sum(rw.star) ...
) / (
  select count(1) ...
)

이건 같은 조건으로 SUM과 COUNT를 두 번 반복하고 있어 불필요한 계산이 두 배입니다.

👉 아래처럼 JOIN한 후, AVG()로 처리하는 게 훨씬 효율적입니다.

✅ 리팩토링 예시:

LEFT JOIN (
    SELECT 
        pt.golf_or_tour_id AS golf_id,
        AVG(rw.star) AS avg_rating
    FROM review rw
    INNER JOIN product pt ON pt.id = rw.product_id AND pt.deleted_at IS NULL
    WHERE rw.deleted_at IS NULL
    GROUP BY pt.golf_or_tour_id
) review_avg ON review_avg.golf_id = gf.id

→ 이후 SELECT에선 그냥 ifnull(review_avg.avg_rating, 0) 사용.

3. ⚠️ gf.* 사용 → 명시적 컬럼 지정 권장

  • gf.*는 편리하지만, 나중에 컬럼 변경 시 의도치 않게 결과가 바뀔 수 있음.
  • 명시적으로 gf.id, gf.name, gf.created_at 등 필요한 컬럼만 선택하는 것이 유지보수에 좋습니다.

4. ✅ LEFT JOIN vs INNER JOIN 적절히 잘 사용

  • product, review_star_summary는 있을 수도 없을 수도 있으니 LEFT JOIN 잘 쓰셨습니다.
  • region, country는 반드시 있어야 하니 INNER JOIN 사용도 적절합니다.

5. ⚠️ reservation_count 서브쿼리도 JOIN + GROUP BY로 통합 가능

  • 성능을 위해 LEFT JOIN + GROUP BY 형태로 변경 가능.
LEFT JOIN (
    SELECT golf_id, COUNT(1) AS reservation_count
    FROM golf_reservation
    WHERE deleted_at IS NULL
    GROUP BY golf_id
) gr ON gr.golf_id = gf.id

✅ 리팩토링 버전 (요약)

SELECT 
    ifnull(rss.user_count, 0) as user_count,
    ifnull(pt.price_won, 0) as price_won,
    ifnull(review_avg.avg_rating, 0) as avg_rating,
    ifnull(gr.reservation_count, 0) as reservation_count,
    gf.id, gf.name, gf.region_id, gf.country_id -- 필요한 컬럼만 명시
FROM golf gf
INNER JOIN region rg ON rg.id = gf.region_id AND rg.deleted_at IS NULL
INNER JOIN country cy ON cy.id = gf.country_id AND cy.deleted_at IS NULL
LEFT JOIN product pt ON pt.golf_or_tour_id = gf.id AND pt.deleted_at IS NULL
LEFT JOIN review_star_summary rss ON rss.golf_id = gf.id AND rss.deleted_at IS NULL
LEFT JOIN (
    SELECT pt.golf_or_tour_id AS golf_id, AVG(rw.star) AS avg_rating
    FROM review rw
    INNER JOIN product pt ON pt.id = rw.product_id AND pt.deleted_at IS NULL
    WHERE rw.deleted_at IS NULL
    GROUP BY pt.golf_or_tour_id
) review_avg ON review_avg.golf_id = gf.id
LEFT JOIN (
    SELECT golf_id, COUNT(1) AS reservation_count
    FROM golf_reservation
    WHERE deleted_at IS NULL
    GROUP BY golf_id
) gr ON gr.golf_id = gf.id
WHERE gf.deleted_at IS NULL

✍️ 요약

항목 상태 개선 포인트

DISTINCT 1 ❌ 의미 없음 삭제 권장
AVG 계산 ⚠️ 서브쿼리 중복 JOIN + AVG() 추천
gf.* ⚠️ 유지보수 어려움 명시적 컬럼 선택
서브쿼리 (count) ⚠️ 성능 우려 LEFT JOIN + GROUP BY로 리팩토링
JOIN 논리 ✅ 적절함 문제 없음

 

 

 

 

 

 아래 부분에서 review 테이블의 데이터가 방대해도 기존에 적용했던 쿼리보다 조인 쿼리가 더 나은 방법인가요?

LEFT LEFT JOIN (
    SELECT 
        pt.golf_or_tour_id AS golf_id, 
        AVG(rw.star) AS avg_rating
    FROM review rw
    INNER JOIN product pt 
        ON pt.id = rw.product_id 
        AND pt.deleted_at IS NULL
    WHERE rw.deleted_at IS NULL
    GROUP BY pt.golf_or_tour_id
) review_avg ON review_avg.golf_id = gf.id

 

 

 

 

 

좋은 질문입니다.
리뷰(review) 테이블의 데이터가 방대하다면, 쿼리 성능에 영향을 주는 요소는 다음과 같습니다:

🔍 쿼리 방식 비교

✅ 기존 쿼리 (서브쿼리 2회 방식):

IFNULL((
    (SELECT SUM(...) FROM review ...)
    /
    (SELECT COUNT(...) FROM review ...)
), 0)
  • review 테이블을 2번 스캔합니다 (sum / count 각각 따로).
  • gf.id마다 2번씩 서브쿼리를 반복 실행 → N+1 문제 발생 가능.
  • 리뷰 수가 많으면 큰 부하 발생.

✅ JOIN + GROUP BY 방식:

LEFT JOIN (
    SELECT pt.golf_or_tour_id AS golf_id, AVG(rw.star) AS avg_rating
    FROM review rw
    INNER JOIN product pt ON pt.id = rw.product_id AND pt.deleted_at IS NULL
    WHERE rw.deleted_at IS NULL
    GROUP BY pt.golf_or_tour_id
) review_avg ON review_avg.golf_id = gf.id
  • review 테이블을 단 1회 스캔.
  • AVG를 미리 그룹화해놓고 join만 하므로, 스케일이 좋아지고 쿼리 캐시 활용 가능.
  • 인덱스만 잘 설정하면 매우 빠릅니다.

🔧 성능 비교 요약

항목 서브쿼리 방식 JOIN + GROUP BY

스캔 횟수 2회 이상 (SUM, COUNT 따로) 1회
확장성 나쁨 (N+1 구조) 좋음 (사전 집계 후 join)
인덱스 활용 제한적 효과적
캐시 재사용 어렵다 가능 (서브쿼리 캐시)
유지보수 복잡함 깔끔함

📌 추천: JOIN + GROUP BY 사용

단, 다음 조건을 만족하면 더 좋습니다:

✅ 성능 최적화를 위한 인덱스 팁

  • review(product_id) → 인덱스 필수
  • product(id) → 기본키
  • product(golf_or_tour_id) → 인덱스 있으면 좋음
  • review(deleted_at) → 조건 필터링용 인덱스
  • product(deleted_at) → 필터링 인덱스

💡 보너스: 리뷰 데이터가 너무 많다면?

  • review_avg 결과를 주기적으로 캐시하거나 summary 테이블로 저장하는 것도 고려해볼 수 있습니다.
    • 예: 하루에 한 번 평균 평점을 갱신하는 테이블 생성
    • materialized view 또는 ETL로 처리

결론적으로:

✅ 데이터가 많을수록 서브쿼리보다 JOIN + GROUP BY 방식이 훨씬 유리합니다.

 

 

 

 

 

그런데, 너가 말한 조인 방식은
golf_id 컬럼을 기준으로 전체 테이블의 데이터를 모두 스캔해서 카운트하는 방식 아닌가?

그리고 위에서 제시한 LEFT JOIN도
golf_or_tour_id 컬럼을 기준으로 모든 데이터의 평균을 구하는 거니까,
결국 해당 테이블의 전체 golf_or_tour_id 컬럼을 한 번씩 스캔하게 되는 구조잖아.

반면에, 내가 제시한 서브쿼리 방식은
조회하고자 하는 데이터에 대해서만 golf_or_tour_id 컬럼으로 조인을 걸기 때문에
내 방식이 더 효율적인 접근 아닐까?

 

LEFT JOIN (
    SELECT 
        pt.golf_or_tour_id AS golf_id, 
        AVG(rw.star) AS avg_rating
    FROM review rw
    INNER JOIN product pt 
        ON pt.id = rw.product_id 
        AND pt.deleted_at IS NULL
    WHERE rw.deleted_at IS NULL
    GROUP BY pt.golf_or_tour_id
) review_avg ON review_avg.golf_id = gf.id

LEFT JOIN (
    SELECT 
        golf_id, 
        COUNT(1) AS reservation_count
    FROM golf_reservation
    WHERE deleted_at IS NULL
    GROUP BY golf_id
) gr ON gr.golf_id = gf.id

 

 

 

 

 

 

 

 

결론부터 말씀드리면:

✅ 데이터가 방대하고 조회 대상(golf row 수)이 적을 경우,
👉 당신이 제시한 서브쿼리 방식이 더 효과적일 수 있습니다.

📌 왜 그런가?

당신의 방식은 예를 들어 golf 테이블에서 10건만 조회할 때,
각 gf.id에 대해 review나 reservation에서 필요한 데이터만 쿼리합니다.

SELECT (
  SELECT AVG(rw.star)
  FROM review rw
  JOIN product pt ON pt.id = rw.product_id AND pt.deleted_at IS NULL
  WHERE pt.golf_or_tour_id = gf.id AND rw.deleted_at IS NULL
)
  • 👉 gf.id 하나에 대해서만 review를 찾습니다.
  • 인덱스가 잘 되어 있다면 빠르게 접근 가능합니다.
  • 리스트 건수가 적다면, 반복 비용(N번 실행)은 부담되지 않음.

⚠️ 하지만 주의할 점

  • golf row 수가 많아질수록 → review를 N번 쿼리해야 하므로 느려질 수 있음.
  • 이게 바로 N+1 문제입니다.

반면, JOIN + GROUP BY 방식은 review 전체를 한 번에 스캔해서 요약하므로:

  • 처음엔 느려 보일 수 있지만,
  • 캐시 재사용 / 인덱스 효율 / 메모리 조인 등 덕분에
    대량 데이터 처리에서는 더 안정적인 성능을 냅니다.

✅ 최종 정리

상황 추천 방식

조회 대상이 적고, 테이블이 매우 클 때 서브쿼리 방식 (당신의 방식)
조회 대상이 많고, 전체 목록을 뿌릴 때 JOIN + GROUP BY

 

 

 

결론적으로 챗지피터가 제시한 쿼리도 좋지만 내가 원하는 데이터는 메인테이블의 데이터가 적기 때문에 서브쿼리를 시행하는 단건처리가 적기 때문에 내가 제시한 서브쿼리로 적용하기로 결정

 

반응형

'잡' 카테고리의 다른 글

메이븐(Maven) 기반 서버 배포 세팅방법  (1) 2025.06.01
로컬 스프링부트 프로젝트 서버 배포 방법  (1) 2025.06.01
encodeURI 와 encodeURIComponent 차이  (0) 2025.04.28
[Docker] 실행중인 도커 컨테이너의 포트포워딩 변경  (2) 2024.11.25
윈도우에서 서버와 ssh key을 이용하여 접속하는 방법  (3) 2024.09.03

관련글

댓글 0

티스토리툴바