개발/개발 공부

[MySQL] 검색 조회 쿼리 성능 개선 - 1편

codesparkling 2025. 7. 3. 14:10

팔구삼 프로젝트 - 구름 프로펙트 검색 조회 쿼리 성능 개선 1편

노션에 작성된 내용을 티스토리로 옮기다 보니 문단이 깨지거나 코드 블럭이 깨지는 등의 문제가 생기는 것 같다.
노션에 바로 접근할 수 있도록 링크를 첨부한다.

노션 링크

상황: 어떤 문제가 있었나?

구름 프로펙트에서 진행하는 우리 프로젝트에서 검색 쿼리의 성능이 너무 느렸다.
개발 초기에는 몰랐지만 JOIN 4번에 ORDER BY 정렬에 GROUP BY 집계가 모두 짬뽕이 되어 있어서 데이터가 많아지면 필연적으로 느려질 수밖에 없었다.
데이터가 100개 정도로 적었을 때는 문제가 드러나지 않았다.
경매 기록 130만 건, 입찰 기록 1200만 건, 좋아요 기록 2000만 건, 더미 사용자 데이터 1000건, 경매 이미지 URL 데이터 400만 건 정도를 생성하니 심각한 성능 저하가 발생했다.
API 요청 후 응답에 10초씩 걸리고, DB에 직접 연결해서 쿼리를 확인해보니 레코드 개수가 많은 데이터는 30초가 넘게 걸리기도 했다.

그래서 실행 계획을 보면서 내 쿼리가 어떻게 조회되고 어느 부분이 병목지점인지 판별하는 능력이 필요했다.
한번도 쿼리 성능 측정을 해 본 경험이 없었기에 박재성님의 MySQL 성능 개선 강의를 먼저 학습했다.

접근: 어떤 기술로 어떻게 해결했나? / 사고 과정 설명하기

가장 먼저 한 것은 어떤 식으로 SQL문을 개선할 수 있을지 생각하는 것이었다.
예전에 인덱스를 생성하고 실행 계획을 분석하면서 SQL 성능을 개선할 수 있다는 사실을 들었다.

그러면 이제 내 문제 상황을 정의하면 실행 계획을 분석해야 하는데, 처음 해보는 거라 하는 방법을 모른다는 것이었다.
일단은 먼저 EXPLAIN/EXPLAIN ANALYZE명령을 실행시켜 보면서 어떤 식으로 데이터가 나오는지 감을 잡았다.
읽어보려고 했는데 도저히 모르겠어서 강의를 참고하려고 했다.
이런 이유에서 먼저 박재성님의 MySQL 성능 개선 강의를 들으러 갔다.
그 과정에서 어떻게 성능 개선을 하는지 여러 예시를 확인했고 대부분 인덱스를 어떻게 하면 잘 생성할 지에 대한 내용이었다.

여러 번 실행 계획을 실행시켜보면서 어떤 식으로 읽어야 할 지 감을 잡아나가는 과정이 있었다.
대상 쿼리는 아래와 같다.
우리가 검색으로 사용하는 동적 쿼리에서 몇 가지 조건을 빼고 실제 사람들이 검색할 법한 쿼리로 설정했다.

  • 검색 조건, 정렬 조건 및 조회할 데이터
    • 검색 조건
      • 원피스라는 단어가 제목 혹은 상품 설명에 포함된 경매
      • 카테고리 id가 80 (여성 의류 / 원피스)
      • 물품 상태가 새 것(brand_new)
      • 경매 상태가 ‘시작 전, 진행중, 완료’ 중 하나라도 해당하는 것 (사실상 취소된 것을 제외한 모든 경매)
      • 경매 시작가 혹은 경매 입찰가가 10000원 이상
    • 정렬 조건
      • 경매 생성 시간
    • 조회 데이터
      • 경매 고유 번호(ID)
      • 경매 제목
      • 경매 시작 시간
      • 경매 종료 시간
      • 경매의 상태
      • 경매 시작가
      • 현재 입찰가
      • 경매 썸네일 이미지 주소
      • 입찰자의 수
      • 찜(스크랩)한 사람의 수
  • 개선 전 쿼리
SELECT 
    a.id, 
    a.title, 
    a.start_time, 
    a.end_time, 
    a.status, 
    a.base_price,
    COALESCE(i.url, 'test') as thumbnail_url,
    COALESCE(MAX(b.price), a.base_price) as current_price,
    COUNT(DISTINCT(b.bidder_id)) as bidder_count,
    COUNT(DISTINCT(s.id)) as scrap_count
FROM auction a
LEFT JOIN bid b ON a.id = b.auction_id AND b.is_deleted = false
LEFT JOIN scrap s ON a.id = s.auction_id
LEFT JOIN auction_image i ON a.id = i.auction_id AND i.image_seq = 0
JOIN category c ON a.category_id = c.id
WHERE
    MATCH(a.title, a.description) AGAINST ("원피스" IN NATURAL LANGUAGE MODE) 
    AND a.category_id IN (
        WITH RECURSIVE CategoryHierarchy AS (
            SELECT id FROM category WHERE id = 80
            UNION ALL
            SELECT c.id FROM category c
            JOIN CategoryHierarchy ch ON c.parent_id = ch.id
        )
        SELECT id FROM CategoryHierarchy
    )
    AND a.item_condition = 'brand_new'
    AND a.status IN ('pending', 'active', "completed")
GROUP BY a.id, a.title, a.start_time, a.end_time, a.status, a.base_price, a.created_at, i.url
HAVING
    COALESCE(MAX(b.price), a.base_price) >= 10000
ORDER BY a.created_at DESC;

첫 번째 실험 - 현재 쿼리의 상태 점검 및 분석

실행 계획

10회의 조회

10회의 조회 쿼리를 실행시켰을 때, 평균적으로 33.8초의 시간이 걸렸다.
여기서 구체적으로 어떤 곳이 병목 구간인지 파악하기 위해 EXPLAIN ANALYZE를 사용해서 실행 결과에 기반한 결과를 분석했다. (EXPLAIN은 실행 전 예측 값을 기반으로 하기 때문에 실제 실행 전략과 조금 다를 수도 있다.)

가장 먼저 실행되는 것은 초록색 부분인 Full-text search를 진행하는 것이다.
원피스라는 글자가 포함되는지 검색해서 130만 건의 경매 중 99590개를(rows값) 필터링해서 가져왔다.
여기서 왜 Full-text index search on a using title이라고만 나오는지 의문일 수도 있다. description도 함께 복합 인덱스로 설정했는데 왜? 라는 의문이 든다면 아래 자료를 보면된다.

4번째 풀텍스트 인덱스를 확인하면 인덱스 이름이 title이다. (나도 처음에 이것 때문에 헷갈렸다.)

다음으로 item_condition 값이 일치하는지, 원피스가 title과 description에 포함되어 있는지, 그리고 경매 상태를 통해 99590개를 필터링해서 19881개의 데이터로 추려냈다.
(여기서 첫 번째 단계에 풀텍스트 인덱스를 통해 필터링을 했는데 왜 다시 필터링을 하는지는 아직 의문이다.)

같은 단계에서 category에 해당하는 재귀 쿼리가 동작한다.

-> Covering index lookup on CategoryHierarchy using <auto_key0> (id=a.category_id)  (cost=8.48..11 rows=2) (actual time=219e-6..219e-6 rows=0.913 loops=19881)
   -> Materialize recursive CTE categoryhierarchy  (cost=5.98..5.98 rows=6.71) (actual time=0.306..0.306 rows=1 loops=1)
      -> Rows fetched before execution  (cost=0..0 rows=1) (actual time=83e-6..83e-6 rows=1 loops=1)
      -> Repeat until convergence
         -> Nested loop inner join  (cost=5.3 rows=5.71) (actual time=0.284..0.284 rows=0 loops=1)
            -> Filter: (ch.id is not null)  (cost=2.73 rows=2) (actual time=0.00154..0.00162 rows=1 loops=1)
               -> Scan new records on ch  (cost=2.73 rows=2) (actual time=834e-6..834e-6 rows=1 loops=1)
            -> Covering index lookup on c using FK2y94svpmqttx80mshyny85wqr (parent_id=ch.id)  (cost=1.15 rows=2.86) (actual time=0.281..0.281 rows=0 loops=1)

조금은 복잡하지만 정리하면 계층형 카테고리를 표현하기 위해 category id가 주어지면 재귀적으로 그 자식들을 찾아서 CTE로 반환하는 로직이다.
지금은 category id 80인 원피스만 설정했지만, 여성 의류인 category id 19를 주면 여성 의류 + 여성 의류의 자식 카테고리들(아우터, 상의, 하의, 언더웨어 등)을 같이 CTE로 설정할 것이다.

다음으로 IN 절이 시작된다.

AND a.category_id IN (
        WITH RECURSIVE CategoryHierarchy AS (
            SELECT id FROM category WHERE id = 80    
            UNION ALL
            SELECT c.id FROM category c
            JOIN CategoryHierarchy ch ON c.parent_id = ch.id
        )
        SELECT id FROM CategoryHierarchy
    )

-> Nested loop semijoin (cost=1.08 rows=0.671) (actual time=16.4..1849 rows=18144 loops=1) 이 부분은 IN 절을 옵티마이저가 최적화를 하면서 세미조인으로 변경한 부분이다.

다음으로 CategoryHierachy와의 Nested Inner Join이 수행된다.
아래 부분이다.

JOIN category c ON a.category_id = c.id

여기서부터가 병목으로 파악된 주요 지점이다. 윗 과정까지 모두 진행되었을 때, rows가 18144로 99590으로 시작되었을 때보다 많이 필터링되었다.
이제 bid와 LEFT JOIN을 수행하는 지점을 확인해보자.

-> Nested loop left join (cost=14.1 rows=103) (actual time=17.1..3747 rows=4.06e+6 loops=1)
rows를 확인해보면 4.06e+6이라는 값을 확인할 수 있다.
e+숫자는 10^숫자가 곱해진다는 의미이다.
계산하면 4.06 * 10^6이고 4060000, 406만이다.

18144개의 레코드가 406만개로 뻥튀기된 것이다.
이 데이터들은 -> Group aggregate: max(bid.price), max(bid.price), count(distinct bid.bidder_id), count(distinct [scrap.id](http://scrap.id/)) (actual time=29674..33050 rows=18144 loops=1) 부분에서 18144개로 돌아온다.

위 처럼 데이터가 생성되는 이유는 중첩된 LEFT JOIN 때문이다.
많은 데이터가 생성되지만 INNER JOIN을 사용할 수 없는 이유는 다음과 같다.

  • 입찰이 없어도 경매는 검색되어야 한다.
  • 찜(스크랩)이 없어도 경매는 검색되어야 한다.

위 요구 사항을 만족하기 위해 사용한 LEFT JOIN에 의해서, 1개의 경매에 대해 스크랩이 20개, bid가 10개 있다고 가정하면 200개의 로우를 만들어냈다.
그렇게 200 * 18000 = 3600000, 360만 개의 로우가 생성된다.
(실제로는 406만 개니까 값이 조금은 차이가 있겠지.)

그 후 GROUP BY에 의해 집계가 되면서 다시 18144개로 줄어드는 것이다.

이렇게 데이터가 늘어나는 문제에 의해서 정렬 또한 느려질 것이라 생각이 들었다.
filesort가 발생할 것이기 때문에 디스크에 접근하는 횟수를 최대한 줄이는 것이 답이라 생각했다.

  • 사고 과정 분해하기

    1. 패턴 인식

      • 406만 개 행 → 1.8만 개 행으로 축소
      • 뭔가 비효율적임을 직감함.
        (데이터를 만들었다가 버리고 있음.)

    2. 근본 원인 추적

      • 왜 406만 개 행으로 늘어났지?
      • -> Index lookup on b using FKhexc6i4j8i0tmpt8bdulp6g3g (auction_id=a.id) (cost=19.5 rows=9.86) (actual time=0.0462..0.0485 rows=9.76 loops=18144) -> Covering index lookup on s using FK2b89qa8v12u10yvlfeb3gv4ws (auction_id=a.id) (cost=2.6 rows=15.5) (actual time=0.00315..0.0049 rows=22.9 loops=177203) -> Filter: (i.image_seq = 0) (cost=3.09 rows=3.07) (actual time=0.00248..0.00281 rows=1 loops=4.06e+6) -> Index lookup on i using FKse8t64pajc8a9s8bm0pww8xg8 (auction_id=a.id) (cost=3.09 rows=3.07) (actual time=0.00242..0.00267 rows=3 loops=4.06e+6)
      • loops 값이 점점 늘어남.
      • ⇒ 조인에 의해서 카테시안 곱이 발생하고 행이 증폭하는구나.

    3. 목적 재정의

      • 실제로 필요한 게 무엇인가?
      • GROUP BY, MAX(), COUNT() 등을 확인
      • 모든 행이 필요한 게 아니라 집계 데이터가 필요한 것을 인지

    4. 대안 탐색

      • 집계가 목적이면 조인 말고 다른 방법을 생각하기
      • 서브 쿼리를 떠올림

    5. 검증 질문

      • 이 방법이 더 효율적일까?
      • 어떤 트레이드오프가 있을까?

      ⇒ 이런 사고 과정을 위해서는 자주 쿼리를 보고 연습하고 직접 실행해봐야 한다.
      다른 사람의 튜닝 사례를 많이 보고, 옵티마이저의 입장에서 생각해보면서 실행 계획의 이상한 점을 계속 찾아본다면 이런 과정이 무의식적으로 할 수 있을만큼 익숙해지지 않을까?

위의 사고과정을 거치고 두 가지의 방법을 생각했다.

  1. 스칼라 서브쿼리를 활용하여 SELECT 절에서 바로 집계 함수 호출하기
  2. 집계를 해당하는 테이블에서 먼저 진행하고 그 결과만 조인해서 사용하기

각각의 장단점을 나름 정리해보면 다음과 같았다.

장점 단점
스칼라 서브쿼리 생각하기가 매우 간단하다. LEFT JOIN에 의해 행 개수가 증폭하는 현상을 해결할 수 있다. 서브쿼리에서 사용되는 조건의 종류가 많아지거나 조건 데이터가 바뀌는 경우, 캐싱 효율성이 떨어져서 성능에 문제가 발생할 수 있다. N+1 문제가 발생할 수 있다. (매 경매마다 서브 쿼리 실행) MAX(), COUNT() 등 집계 함수마다 별도의 서브 쿼리가 필요하다.
집계 후 조인하기 여러 조건을 관리하기가 편하다. HAVING 절을 사용하지 않기 때문에 쓸데없는 필터링을 하나 더 줄일 수 있다. 당장 생각나는 위험한 요소는 없다고 판단.

그래서 Bid/Scrap 테이블에서 집계 함수를 먼저 사용한 뒤에 조인하기로 결정했다.

두 번째 실험 - bid 테이블에서 집계 후 조인하기

  • bid 테이블에서 집계 후 조인 쿼리
  • SELECT a.id, a.title, a.start_time, a.end_time, a.status, a.base_price, COALESCE(i.url, 'test') as thumbnail_url, COALESCE(ab.price, a.base_price) as current_price, ab.bidders as bidder_count, COUNT(DISTINCT(s.id)) as scrap_count FROM auction a LEFT JOIN ( SELECT auction_id, MAX(CASE WHEN b.price >= 10000 THEN b.price END) as price, COUNT(DISTINCT bidder_id) as bidders FROM bid as b WHERE b.is_deleted = false GROUP BY auction_id ) ab ON a.id = ab.auction_id LEFT JOIN scrap s ON a.id = s.auction_id LEFT JOIN auction_image i ON a.id = i.auction_id AND i.image_seq = 0 JOIN category c ON a.category_id = c.id WHERE a.item_condition = 'brand_new' AND a.status IN ('pending', 'active', 'completed') AND (a.base_price >= 10000 OR ab.price >= 10000) AND MATCH(a.title, a.description) AGAINST ("원피스" IN NATURAL LANGUAGE MODE) AND a.category_id IN ( WITH RECURSIVE CategoryHierarchy AS ( SELECT id FROM category WHERE id = 80 UNION ALL SELECT c.id FROM category c JOIN CategoryHierarchy ch ON c.parent_id = ch.id ) SELECT id FROM CategoryHierarchy ) GROUP BY a.id, a.title, a.start_time, a.end_time, a.status, a.base_price, a.created_at, i.url ORDER BY a.created_at DESC;```

해당 쿼리의 실행 계획과 10회 실행했을 때 평균을 다음과 같이 구했다.

실행 계획

10회 조회

10회 조회했을 때, 평균 약 13초의 시간이 결렸다. 33.8초에서 13초로 약 61%의 개선이 있었다.
개선이 되었음을 확인하고 scrap 테이블에서 사용되는 집계 데이터 또한 먼저 집계시킨 후에 JOIN 연산이 이루어지도록 수정해봤다.

세 번째 실험 - scrap 테이블 또한 집계 후 조인

  • scrap 테이블 또한 집계 후 조인
  • SELECT a.id, a.title, a.start_time, a.end_time, a.status, a.base_price, COALESCE(i.url, 'test') as thumbnail_url, COALESCE(ab.price, a.base_price) as current_price, ab.bidders as bidder_count, aggregated_scrap.scraps as scrap_count FROM auction a LEFT JOIN ( SELECT auction_id, MAX(CASE WHEN b.price >= 10000 THEN b.price END) as price, COUNT(DISTINCT bidder_id) as bidders FROM bid as b WHERE b.is_deleted = false GROUP BY auction_id ) ab ON a.id = ab.auction_id LEFT JOIN ( SELECT auction_id, COALESCE(COUNT(s.id), 0) as scraps FROM scrap as s GROUP BY auction_id ) aggregated_scrap ON a.id = aggregated_scrap.auction_id LEFT JOIN auction_image i ON a.id = i.auction_id AND i.image_seq = 0 JOIN category c ON a.category_id = c.id WHERE a.item_condition = 'brand_new' AND a.status IN ('pending', 'active', 'completed') AND (a.base_price >= 10000 OR ab.price >= 10000) AND MATCH(a.title, a.description) AGAINST ("원피스" IN NATURAL LANGUAGE MODE) AND a.category_id IN ( WITH RECURSIVE CategoryHierarchy AS ( SELECT id FROM category WHERE id = 80 UNION ALL SELECT c.id FROM category c JOIN CategoryHierarchy ch ON c.parent_id = ch.id ) SELECT id FROM CategoryHierarchy ) GROUP BY a.id, a.title, a.start_time, a.end_time, a.status, a.base_price, a.created_at, i.url ORDER BY a.created_at DESC;

실행 계획

10회 조회

scarp 또한 집계 후 조인하는 방식으로 변경했더니 소요 시간이 오히려 늘어나는 것을 확인했다.
(그 이유 외에도 13~15초의 시간이 걸리는 쿼리는 여전히 성능을 개선해야 했다.)
그래서 두 가지의 실행 계획을 살펴봤더니 아래와 같은 부분이 있었다.

모든 bid 데이터와 scrap 데이터에 접근하고 있었다. type도 인덱스 풀 스캔이라는 개선해야 할 타입으로 출력되었다.
EXPLAIN ANALYZE를 통해서 실행 후 실행 계획을 통해서 다시 병목 분석을 시작했다.

이런 일이 발생한 이유는 집계 후 조인이 진행되기 때문이다.
아래는 각각 bid 테이블과 scrap 테이블에 대해 분석한 실행 계획이다.

-- bid 테이블
-> Index lookup on ab using <auto_key1> (auction_id=a.id)  (cost=1.96e+6..1.96e+6 rows=15.3) (actual time=0.601..0.601 rows=0.997 loops=18144)
   -> Materialize  (cost=1.96e+6..1.96e+6 rows=1.21e+6) (actual time=10777..10777 rows=1.27e+6 loops=1)
      -> Group aggregate: max((case when (b.price >= 10000) then b.price end)), count(distinct b.bidder_id)  (cost=1.84e+6 rows=1.21e+6) (actual time=5.16..9790 rows=1.27e+6 loops=1)
         -> Filter: (b.is_deleted = false)  (cost=1.25e+6 rows=5.97e+6) (actual time=5.14..8695 rows=12e+6 loops=1)
            -> Index scan on b using FKhexc6i4j8i0tmpt8bdulp6g3g  (cost=1.25e+6 rows=11.9e+6) (actual time=5.14..8291 rows=12e+6 loops=1)
-- scrap 테이블
-> Index lookup on aggregated_scrap using <auto_key0> (auction_id=a.id)  (cost=4.05e+6..4.05e+6 rows=17.2) (actual time=0.219..0.219 rows=0.995 loops=17267)
   -> Materialize  (cost=4.05e+6..4.05e+6 rows=1.24e+6) (actual time=3746..3746 rows=1.25e+6 loops=1)
      -> Group aggregate: count(s.id)  (cost=3.92e+6 rows=1.24e+6) (actual time=2.39..2811 rows=1.25e+6 loops=1)
         -> Covering index scan on s using FK2b89qa8v12u10yvlfeb3gv4ws  (cost=1.99e+6 rows=19.3e+6) (actual time=2.39..2405 rows=19.3e+6 loops=1)

자세히 보면 rows의 값이 table에 저장된 데이터 개수만큼 접근하는 것을 볼 수 있다.
입찰자의 수, 스크랩한 사람의 수, 경매의 최대 입찰가를 구하기 위해서 필요한 경매에 대한 집계가 아니라 모든 데이터에 대해 집계하고 있었다.
그나마 bid는 is_deleted가 false인 데이터를 필터링 하기 때문에 조금은 적을 수 있지만, 우리 서비스의 정책상 삭제된 입찰이 많지 않기 때문에 여전히 대부분의 데이터에 접근해야 한다.

여기서 강사님의 조언을 듣고 DISTINCT에 꽂혀서 생각을 잘못했었다.
DISTINCT를 없앨 수 있는 방법을 생각하면 좋을 것 같다는 의견을 들었다.
그래서 무작정 bid 테이블의 데이터에서 DISTINCT를 없애려고 시도했다.

  • 잘못 생각했던 부분
    • DISTINCT에 꽂혀서 모든 데이터에 대해 집계하고 있다는 사실을 자각하지 못한 것
    • 차라리 LEFT JOIN으로 해결했으면 필터된 경매 개수인 18000개에 대해서만 조회하기 때문에 성능이 나았을 것이다. (세 번째 실험에서 성능이 느려진 이유)

네 번째 실험 - DISTINCT 없애기

  • bid 테이블 쪽 DISTINCT를 삭제한 쿼리
  • SELECT a.id, a.title, a.start_time, a.end_time, a.status, a.base_price, COALESCE(i.url, 'test') as thumbnail_url, COALESCE(aggregated_bid.price, a.base_price) as current_price, aggregated_bid.bidders as bidder_count, aggregated_scrap.scraps as scrap_count FROM auction a LEFT JOIN ( SELECT auction_id, MAX(CASE WHEN bidder_max_price >= 10000 THEN bidder_max_price END) as price, COUNT(unique_bidders.bidder_id) as bidders FROM (SELECT auction_id, bidder_id, MAX(price) as bidder_max_price FROM bid as b WHERE b.is_deleted = false GROUP BY auction_id, bidder_id) as unique_bidders GROUP BY auction_id ) aggregated_bid ON a.id = aggregated_bid.auction_id LEFT JOIN ( SELECT auction_id, COALESCE(COUNT(s.id), 0) as scraps FROM scrap as s GROUP BY auction_id ) aggregated_scrap ON a.id = aggregated_scrap.auction_id LEFT JOIN auction_image i ON a.id = i.auction_id AND i.image_seq = 0 JOIN category c ON a.category_id = c.id WHERE a.item_condition = 'brand_new' AND a.status IN ('pending', 'active', 'completed') AND (a.base_price >= 10000 OR aggregated_bid.price >= 10000) AND MATCH(a.title, a.description) AGAINST ("원피스" IN NATURAL LANGUAGE MODE) AND a.category_id IN ( WITH RECURSIVE CategoryHierarchy AS ( SELECT id FROM category WHERE id = 80 UNION ALL SELECT c.id FROM category c JOIN CategoryHierarchy ch ON c.parent_id = ch.id ) SELECT id FROM CategoryHierarchy ) GROUP BY a.id, a.title, a.start_time, a.end_time, a.status, a.base_price, a.created_at, i.url ORDER BY a.created_at DESC;

이번에는 성능이 오히려 너무 안 좋아져서 10번 다 채우기가 힘들어 5번 실행시킨 결과를 가져왔다.
소요 시간을 보면 평균적으로 31.5초가 소요된 것을 확인할 수 있다.

실행 계획을 보면 bid쪽은 이미 테이블 풀스캔이다.
그리고 DISTINCT를 삭제하면서 인라인 뷰가 하나 생기게 되었는데, 그게 derived3로 보여진다.
이 derived3는 임시 테이블이기 때문에 인덱스 적용이 안되어 테이블 풀스캔으로 동작하게 된다.

-> Index lookup on aggregated_bid using <auto_key1> (auction_id=a.id)  (cost=1.25..19.1 rows=15.3) (actual time=1.54..1.54 rows=0.997 loops=18144)
   -> Materialize  (cost=0..0 rows=0) (actual time=27778..27778 rows=1.27e+6 loops=1)
      -> Table scan on <temporary>  (actual time=26778..26913 rows=1.27e+6 loops=1)
         -> Aggregate using temporary table  (actual time=26778..26778 rows=1.27e+6 loops=1)
            -> Table scan on unique_bidders  (cost=2.5..2.5 rows=0) (actual time=20285..20969 rows=5.83e+6 loops=1)
               -> Materialize  (cost=0..0 rows=0) (actual time=20285..20285 rows=5.83e+6 loops=1)
                  -> Table scan on <temporary>  (actual time=18303..19207 rows=5.83e+6 loops=1)
                     -> Aggregate using temporary table  (actual time=18302..18302 rows=5.83e+6 loops=1)
                        -> Filter: (b.is_deleted = false)  (cost=1.25e+6 rows=5.97e+6) (actual time=4.4..2585 rows=12e+6 loops=1)
                           -> Table scan on b  (cost=1.25e+6 rows=11.9e+6) (actual time=4.4..2181 rows=12e+6 loops=1)

메인 쿼리에서는 아까처럼 수백만 개의 rows가 생성되지 않았지만, 그 작업이 JOIN할 테이블로 위임했다고 생각하면 될 것 같다.
결국 이 쪽에서는 계속 테이블 풀 스캔을 도는 것이다.

이제 어떻게 해야 할 지 고민하던 찰나에 이 WHERE 절로 모든 입찰을 집계하는 게 아니라 필요한 입찰을 입찰하는 방법에 대해 고민하게 되었다.
(세 번째 실험이 끝났을 때도 이쪽으로 고민을 해야 맞는 분석인데, DISTINCT에 꽂혀 시야가 좁혀지니 생각이 나지 않았었다.)

다음으로 세 가지 방안을 생각했다.

  • CTE를 활용한다.
  • UNION을 활용한다.
  • 스칼라 서브 쿼리 패턴으로 변경한다.

이제는 2편으로 옮겨서 마저 작성하겠다.

배운 것: 새로 알게 된 것들

성능 비교

쿼리를 여러 번 실행 시켜서 평균이 얼마 정도 걸리는 지 체크한 뒤에 몇 배, 몇 % 정도 빨라졌는지 정확하게 수치를 측정해야 한다는 기초를 먼저 학습했다.

실행 계획 읽어보기

EXPLAIN 명령을 통해서 실행 계획을 확인하고 각각이 어떤 값을 가지는 지 먼저 학습했다.
자세한 것은 다른 글에 정리해두었다.

CTE란?

CTE는 실제 데이터베이스에 생성되는 테이블은 아니지만 쿼리 실행 결과를 테이블처럼 사용하기 위해 논리적인 테이블을 만들 때 사용한다.
바로 다음에 실행할 SELECT 문에만 공통 테이블 표현식(CTE)를 사용해서 데이터를 조회할 수 있다.

WITH RECURSIVE CategoryHierarchy AS (
            SELECT id FROM category WHERE id = 80    
            UNION ALL
            SELECT c.id FROM category c
            JOIN CategoryHierarchy ch ON c.parent_id = ch.id
        )
        SELECT id FROM CategoryHierarchy

WITH 절은 CTE(Common Table Expression)라는 기능을 사용하기 위한 쿼리이다.

크게 일반 CTE와 재귀 CTE 두 가지로 구별할 수 있다.

세미 조인

다른 테이블과 실제 조인을 수행하지는 않고, 다른 테이블에서 조건에 일치하는 레코드가 있는지 체크하는 형태의 쿼리를 세미 조인이라고 한다.

쉽게 설명하면 두 릴레이션을 자연 조인한 뒤에 한 왼쪽 혹은 오른쪽, 한 쪽에 대해서만 릴레이션의 결과를 반환하는 것이다.
그러면 자연스럽게 조인이 되지 않는 데이터는 없어지게 된다.
즉, 조건에 맞지 않는 데이터는 삭제된다는 의미이다.

세미 조인을 최적화 하기 위해 5가지의 전략들이 있다.

  • Table pull-out
  • Duplicate Weed-out
  • First Match
  • Loose Scan
  • Materialization

→ 해당 내용들은 필요할 때 더 학습할 예정이다.

커버링 인덱스

데이터 파일을 읽지 않고 인덱스만으로 쿼리를 처리할 수 있는 경우를 커버링 인덱스라고 부른다.
실행 계획에서 Extra 컬럼에 “using index”라고 표시되는 부분이 있다면 커버링 인덱스가 처리된 것이다.

NODE

노드는 쿼리의 한 부분을 실행하는 개별 작업 단위를 나타낸다.
파스 트리에서 확인할 수 있는 트리의 노드가 같은 것을 의미한다.
예를 들어, 테이블 스캔, 조인, 정렬 등의 작업이 각각 다른 노드로 표현될 수 있다.
실행계획은 노드 단위로 순서에 맞게 읽는 것이 중요하다.
각 노드는 -> 표현된다. 또한 루트노드는 ->가 붙지 않는다.
따라서 노드의 수는 -> 의 개수 + 1개이다.

느낀 것: 이 경험에서 느낀 점

  1. 쿼리 실행 계획을 들여다 보면서 내가 작성한 SQL문의 문제점을 파악하는 것이 생각보다 재밌다.
  2. 데이터를 분석할 수 있는 눈을 키우자.
  3. 한 곳에 꽂히는 것보다 잘 안되면 시야를 넓게도 볼 수 있어야 한다.
  4. 카디널리티를 높이고 많은 데이터를 필터링할 수 있어야 한다.

아쉬운 것: 다음엔 이렇게 하겠다

  1. 강사님의 조언은 조언대로 듣고, 합리적으로 생각해보자.

'개발 > 개발 공부' 카테고리의 다른 글

[AWS] RDS로 데이터 삽입 삽질  (0) 2025.07.03
잘못된 리팩토링, OOP와 순수 함수  (0) 2025.06.07
테스트 코드는 왜 작성하기 어려울까?  (0) 2025.05.31
검색 구현을 위한 기초 공부  (0) 2025.05.05
[밑바닥부터] 8일차 - 2장 불 연산: ALU 구현 및 테스트  (0) 2025.04.13

'개발/개발 공부'의 다른글

  • 현재글[MySQL] 검색 조회 쿼리 성능 개선 - 1편

관련글

티스토리툴바