Slow query 개선 경험기

 

글을 작성하기 전에 먼저 기존 쿼리가 연산에 불리하게 작성되어 있었다는 점 (비즈니스 로직에서 사용하기 위한 이슈) 이 있었다는 점을 참고 바란다.

 

현재 회사에서 운영하는 서버에서 특정 쿼리 (유저의 구매 데이터의 요약본을 알려줌) 에서 상당히 느린 쿼리가 있다는 것을 알게 되었다. 서비스 특성상 해당 쿼리를 자주 호출할 일은 없지만, 평균 10초정도 걸리는 쿼리가 있다는 것 조차 문제가 있다고 생각하여 해당 쿼리를 확인해 보았다.

 

SELECT
  DISTINCT "i".*
FROM
  (
    SELECT
      "department"."name" AS "target",
      "department"."id" AS "id"
    FROM
      "order_info_entity" "oi"
      INNER JOIN "order_sheet_bundle_entity" "bundle" ON "bundle"."orderInfoId" = "oi"."id"
      ... 그 외 5개 정도의 INNER JOIN 존재 (각 테이블마다 데이터가 10만~100만개 정도 존재)
    WHERE
      (
        (
          SELECT
            COUNT("orderCancel"."id")
          FROM
            "order_cancel_entity" "orderCancel"
          WHERE
            ...orderCacel에 대한 단순한 where
        ) = 0
        ... 그 외 3개 정도의 and 연산자
      )
      AND ("oi"."deleteAt" IS NULL)
  ) "i";

 

기존 쿼리를 요약해 보았다. 해당 쿼리를 확인해 보면  특이한 점이 보인다. 전체 where 절에서 orderCancel의 수를 count하여 0인지 판별하는 로직이 들어가있다. 해당 로직이 문제가 되는 것이 inner join된 데이터에서 where로 데이터를 필터링할 때 마다 해당 쿼리가 실행되게 된다. 즉, 말도 안되게 많은 연산이 실행되는 것이다. 해당 쿼리를 explain anayse 해서 실행 계획을 보면 첫번째 줄은 다음과 같이 보인다.

 

Unique  (cost=13534.71..2088324.35 rows=1 width=19) (actual time=275.516..7426.945 rows=3 loops=1)

 

최종 cost가 200만이 나온다. 절대로 답이 없다. 중간에 select count 쿼리를 보니 다행히 join이 가능해서, left join을 수행한 다음 orderCancel에 대한 조건절을 추가하는 방식으로 쿼리를 변경했다.

여기서 추가적으로 알게된 점은 inner join 하는 테이블에서 where절의 위치를 변경한다고 해서 쿼리의 실행 계획이 개선되지는 않는다는 것 이였다.

 

위의 정보들을 바탕으로 쿼리를 개선했고, 쿼리와 결과를 확인해 보았다.

SELECT DISTINCT "i".*
FROM (
    SELECT
        "department"."name" AS "target",
        "department"."id" AS "id"
    FROM "department_entity" "department"
    INNER JOIN "order_info_entity" "oi" ON "oi"."departmentId" = "department"."id"
    ... 다수의 INNER JOIN
    LEFT JOIN "order_cancel_entity" "orderCancel" ON "orderCancel"."orderSheetId" = "orderSheet"."id"
    ... LEFT JOIN 조건절
    WHERE
        "department"."status" = 'ACTIVATE'
        AND "department"."deleteAt" IS NULL
        AND "orderCancel"."id" IS NULL
) "i";

Unique  (cost=13341.45..13341.46 rows=1 width=19) (actual time=1121.992..1122.122 rows=3 loops=1)

 

최종 cost는 13000정도 나오고 실행 시간은 1.1초 정도가 나오게 된다. 하지만 해당 쿼리도 개선할 점이 있을 것이라고 생각하여, 인덱스를 추가하여 join 속도를 높이기로 했다.

인덱스를 추가하기 전 query plan의 첫번째 줄은 다음과 같다.

Unique  (cost=13341.45..13341.46 rows=1 width=19) (actual time=1121.992..1122.122 rows=3 loops=1)

 

여기에 join하는 각 테이블에 대하여 모두 복합 컬럼 인덱스를 추가한 이후 query plan을 실행해 보았다.

Unique  (cost=12794.11..12794.12 rows=1 width=19) (actual time=54.479..59.815 rows=3 loops=1)

 

여기서 query plan의 맨 윗부분을 보면은 최종 cost는 13300 과 12794로 차이는 많이 나지 않는 것을 볼 수 있다. 하지만 actual time을 보게 되면 1100ms 와 59ms로 엄청난 속도의 차이를 보여준다. 이에 대한 원인을 찾기 위해서 query plan visualize를 실행해 보았다.

 

 

두 쿼리의 차이를 보면 nested loop의 유무의 차이가 존재한다. 인덱스를 통해서 결과값의 성능을 향상시킬 수 있었다.

 

결과

최초 실행 속도 : 평균 10초 (최대 300초;;)

개선 후 실행 속도 : 평균 50밀리초