📌인덱스를 활용한 쿼리 개선해보기

JPA를 활용한 프로젝트에서 nativeQuery를 활용한 로직이 있었다. 해당 화면에서 보여지는 데이터를 조회하는 쿼리인데, 이 쿼리문을 분석해보고 성능을 개선해보고자 한다.

---

1. 기존 쿼리문 분석

🔹 기존 쿼리문 개요

해당 쿼리는 `daily_analysis` 테이블에서 최근 1년동안의 일별 평균 기준 가격`avg_reference_price`과 `sm_score`와 `구독수` 1위 전략의 1년동안의 기준 가격`reference_price`을 조회하는 쿼리이다.

SELECT
    DA.daily_date,
    AVG(DA.reference_price) AS avg_reference_price,
    COALESCE((
        SELECT DA2.reference_price
        FROM daily_analysis AS DA2
        JOIN strategy AS st ON DA2.strategy_id = st.strategy_id
        WHERE st.strategy_id = (
            SELECT strategy_id FROM strategy WHERE sm_score = (
                SELECT MAX(sm_score) FROM strategy
            ) LIMIT 1
        )
        AND DA2.daily_date = DA.daily_date
    ), 0) AS highest_sm_score_reference_price,
    COALESCE((
        SELECT DA2.reference_price
        FROM daily_analysis AS DA2
        JOIN strategy AS st ON DA2.strategy_id = st.strategy_id
        WHERE st.strategy_id = (
            SELECT strategy_id FROM strategy WHERE subscription_count = (
                SELECT MAX(subscription_count) FROM strategy
            ) LIMIT 1
        )
        AND DA2.daily_date = DA.daily_date
    ), 0) AS highest_subscribe_score_reference_price
FROM daily_analysis AS DA
WHERE DA.daily_date BETWEEN '2024-02-26' AND '2025-02-26'
GROUP BY DA.daily_date
ORDER BY DA.daily_date ASC;

🔹쿼리 구조

• 기본 조회 테이블 (DA)
  • `daily_analysis` 테이블을 기준으로 `daily_date` 범위를 지정  

FROM daily_analysis AS DA
WHERE DA.daily_date BETWEEN '2024-02-26' AND '2025-02-26'

• • `AVG(DA.reference_price)`를 통해 해당 날짜의 평균 `reference_price`를 계산.

AVG(DA.reference_price) AS avg_reference_price,

• sm_score 1위 전략의 기준 가격 (highest_sm_score_reference_price)

 COALESCE((
        SELECT DA2.reference_price
        FROM daily_analysis AS DA2
        JOIN strategy AS st ON DA2.strategy_id = st.strategy_id
        WHERE st.strategy_id = (
            SELECT st1.strategy_id
            FROM strategy AS st1
            WHERE st1.sm_score = (
                SELECT MAX(st2.sm_score) FROM strategy AS st2
            )
            LIMIT 1
        )
        AND DA2.daily_date = DA.daily_date
    ), 0) AS highest_sm_score_reference_price,

• `strategy` 테이블에서 `sm_score`가 가장 높은 `strategy_id`를 찾음.
• 해당 `strategy_id`와 같은 `strategy_id`를 가진` daily_analysis`의 `reference_price`를 조회.
• 특정 `daily_date`에 대해 `daily_analysis` 데이터가 없을 경우 `COALESCE(..., 0)`를 사용해 NULL 대신 0 반환.
• 구독 수 1위 전략의 기준 가격 (highest_subscribe_score_reference_price)

 COALESCE((
        SELECT DA2.reference_price
        FROM daily_analysis AS DA2
        JOIN strategy AS st ON DA2.strategy_id = st.strategy_id
        WHERE st.strategy_id = (
            SELECT st1.strategy_id
            FROM strategy AS st1
            WHERE st1.subscription_count = (
                SELECT MAX(st2.subscription_count) FROM strategy AS st2
            )
            LIMIT 1
        )
        AND DA2.daily_date = DA.daily_date
    ), 0) AS highest_subscribe_score_reference_price

• `strategy` 테이블에서 `subscription_count`가 가장 높은 `strategy_id`를 찾음.
• 해당`strategy_id`와 같은 `strategy_id`를 가진 `daily_analysis`의 `reference_price`를 조회.
• 특정 `daily_date`에 대해 `daily_analysis` 데이터가 없을 경우 `COALESCE(..., 0)`를 사용해 NULL 대신 0 반환.

---

🔹 실행 계획 분석

초기 쿼리문 실행계획

• 쿼리 비용 (Cost)이 1,052.45로 상당히 높음!
• Full Table Scan 발생: `daily_analysis` 및 `strategy` 테이블에서 `MAX(sm_score)` 및 `MAX(subscription_count)`를 찾기 위해 전체 스캔이 발생.
• Nested Loop 603, 1796번 반복 실행: DA2에서 각 날짜별 서브쿼리를 반복 실행하여 불필요한 연산 증가.
• Nested Loop 실행 횟수가 많은 이유는?
  • `daily_analysis` 테이블에서 각 날짜별로 `strategy` 테이블의 최대 `sm_score` 및 `subscription_count`를 가진 행을 찾기 위해 각 날짜마다 서브쿼리가 실행됨.
  • 즉, `daily_date`가 100개라면 서브쿼리도 각각 100번씩 실행됨.
  • 서브쿼리 내에서 `MAX(sm_score)`, `MAX(subscription_count)`를 찾기 위해 `strategy` 테이블을 모두 스캔해야 하므로 실행 횟수가 기하급수적으로 증가

---

2. 기존 쿼리문의 문제점 분석

🔹 주요 문제점

Full Table Scan 발생

 `daily_analysis` 테이블에서 `daily_date` 조건으로 조회할 때 인덱스를 사용하지 않아 전체 테이블 스캔이 일어났다. 인덱스가 없을 경우 데이터베이스는 첫 행부터 끝까지 테이블 모든 행을 순차 탐색해야 하며, 테이블이 클수록 그 비용은 크게 증가한다​. 또한 `strategy` 테이블에서 최대값을 구하는 `MAX(sm_score)`와 `MAX(subscription_count)` 연산도 인덱스를 사용하지 못해 Full Scan이 발생했다.인덱스가 없는 상태에서의 EXPLAIN 결과. type 컬럼이 ALL로 표시되어 Full Table Scan이 진행됨을 보여준다. 이런 경우 인덱스가 없으므로 테이블 전체를 탐색해야 하며, 대량의 데이터에 대해선 성능 상 큰 부담이 된다

서브쿼리 중복 실행 문제

날짜별 최대 sm_score와 subscription_count 값을 찾기 위해 동일한 서브쿼리가 반복 실행되고 있었다. 한 쿼리 내에서 서브쿼리를 여러 번 호출하면서 불필요한 중복 연산이 발생했고, 그 결과 Nested Loop 조인의 실행 횟수가 많아져 쿼리 성능이 저하되었다. 이처럼 동일 계산을 반복하면 전체 쿼리의 수행 비용이 기하급수적으로 늘어나게 된다.

---

3. 성능 최적화 적용

🔹 인덱스 추가

• 실행 계획에서 Full Scan의 문제 원인이 된 컬럼들에 인덱스를 생성하여 쿼리 최적화를 시작했다.
• `daily_analysis` 테이블의 `daily_date` 컬럼에 인덱스를 추가하고, `strategy `테이블의 `sm_score`및 `subscription_count` 컬럼에도 각각 인덱스를 생성했다.
• 이를 통해 해당 컬럼을 이용한 검색이나 정렬 시 인덱스 조회를 활용하여 데이터를 훨씬 빠르게 찾을 수 있다.

CREATE INDEX idx_daily_date ON daily_analysis(daily_date);
CREATE INDEX idx_strategy_sm_score ON strategy(sm_score DESC);
CREATE INDEX idx_strategy_subscription ON strategy(subscription_count DESC);

🔹 서브쿼리 최적화 

• 기존 쿼리에는 `MAX(sm_score)`, `MAX(subscription_count)`을 구하기 위해 서브쿼리가 실행될 때마다 전체 테이블을 스캔하는 비효율적인 방식이었다. 즉 여기서 `daily_analysis의` 날짜별로 이 서브쿼리가 반복 실행되서 Nested Loop가 발생한다.

WHERE st.strategy_id = (
            SELECT st1.strategy_id
            FROM strategy AS st1
            WHERE st1.sm_score = (
                SELECT MAX(st2.sm_score) FROM strategy AS st2
            )
            LIMIT 1
        )
        
WHERE st.strategy_id = (
            SELECT st1.strategy_id
            FROM strategy AS st1
            WHERE st1.subscription_count = (
                SELECT MAX(st2.subscription_count) FROM strategy AS st2
            )
            LIMIT 1
        )

• 이를 해결하기 위해 서브쿼리로 최대값을 찾던 부분을 별도 쿼리의 결과 활용 방식으로 변경했다.
• 최대값을 구하는 MAX() 함수를 직접 사용하는 대신, 각 테이블에서 인덱스가 있는 컬럼을 DESC 정렬하여 LIMIT 1로 조회하는 방식으로 변경했다.

-- sm_score 최대값을 가진 행의 ID 조회
WHERE st.strategy_id = (
	SELECT strategy_id FROM strategy ORDER BY sm_score DESC LIMIT 1
)

-- subscription_count 최대값을 가진 행의 ID 조회
WHERE st.strategy_id = (
	SELECT strategy_id FROM strategy ORDER BY subscription_count DESC LIMIT 1
        )

효과

• DESC 정렬 후 첫 번째 값만 가져오므로 인덱스 스캔만으로 최댓값을 빠르게 조회할 수 있다.
• 이 결과를 외부 쿼리에서 재사용하므로 Nested Loop가 줄어듦

---

4. 이전 실행 계획 비교 및 성능 개선 결과

🔹 개선 후 실행 계획

개선한 후 실행 계획

최적화 적용 전에는 실행계획 결과 `strategy` 테이블 접근 방식이 ALL로 표시되어 전체 Full Scan이 일어나고, 예측 비용도 약 1052.45로 높게 나왔다. 반면 최적화 후에는 `daily_analysis` 테이블이 `daily_date 인덱스`를 통해 검색되고 `strategy` 테이블 역시 인덱스로 조회되면서, 전체 비용이 523.95로 절반 이하로 감소했다. 즉, 두 테이블 모두 인덱스를 활용한 Index Scan으로 전환되어 불필요한 테이블 스캔이 제거되고, 전반적인 쿼리 성능이 크게 향상되었다.

🔹 실행 계획 비교

항목	최적화 전	최적화 후	개선 효과
쿼리 비용 (Cost)	1,052.45	523.95	✅  50.2% 감소
조회된 행 개수 (Rows)	7,158	4,763	✅ 33.4% 감소
daily_analysis 조회 방식	Full Table Scan (ALL)	Index Scan (index)	✅ Full Scan 제거
strategy 조회 방식	Full Table Scan (ALL)	Index Scan (index)	✅ Full Scan 제거
Nested Loop 실행 횟수	603 / 1,796	2 / 2	✅  99.7% 감소

🔹 최종 성능 개선 결과

1️⃣ 쿼리 비용(Cost) 50%이상 감소

• 쿼리 실행 비용이 1,052.45 → 523.95로 절반 이상 감소 
• Index Scan을 활용하여 불필요한 테이블 스캔 제거

2️⃣ daily_analysis 및 strategy 테이블의 Full Table Scan 제거

• 최적화 전: daily_analysis와 strategy 모두 Full Table Scan(ALL) 발생 → 성능 저하.
• 최적화 후: daily_date, sm_score, subscription_count 컬럼에 인덱스를 추가하여 Index Scan(index) 적용 
• 결과: 조회 속도 향상 & 불필요한 디스크 I/O 감소

3️⃣ Nested Loop 실행 횟수 99.7% 감소

• 최적화 전: Nested Loop #2가 603회, 1,796회 실행됨 → 반복적인 서브쿼리 실행으로 과부하 발생.
• 최적화 후: Nested Loop #2가 2회만 실행됨  (99.7% 감소)
• 개선 이유: 인덱스+ 서브쿼리 최적화 (ORDER BY DESC LIMIT 1로 변경) 으로 중복 연산 제거 

🔹결론

이번 튜닝을 통해 문제였던 쿼리의 성능을 향상시켰으며, 실제로 화면 로딩도 빠르게 적용되었다! 프로젝트 서비스 특성상, 업데이트 작업이 빈번하게 이루어 지지는 않을 것 같지는 않지만, 향후 데이터 양이나 업데이트 빈도가 빈번해지면 인덱스 유지 비용이 부담이 될 쉬 있다. 그때는 업데이트 빈도를 고려하여 추가적인 인덱스 전략 수정(커버링 인덱스?)이나 캐싱 도입 등 추가 최적화를 검토해볼수 있겠다~