[데이터베이스] 복합 인덱스 순서는 중요할까?

최근 통계 대시보드를 조회하는 페이지에서 에러가 발생하기 시작해서 로그를 살펴보았습니다. 

"message" : "timeout of 10000ms exceeded"

통계 데이터를 만드는 통계 워커 서버에서 타임아웃이 발생해서 에러가 발생했고, 로그의 응답 속도를 보니 responseTime : 86187ms 로 조회 조건의 필터로 조회했을 때 쿼리가 굉장히 오래 걸리는 것을 알 수 있었습니다.

 

이 문제를 해결한 과정을 설명하기 위해 [제휴 업체의 판매량과 매출 통계에 대한 대시보드]로 예를 들어 통계 대시보드의 화면을 재구성하였습니다.

• 프랜차이즈명 (agencyId) : 헤일리 프랜차이즈
• 주류 도매 업체명 (alcoholAgencyId) : 헤일리 주류 도매
• 조회 기간 (date) : 1월 1일 하루
• 가맹점 이름 (storeId) : "헤일리"

위의 네 가지 조건을 필터로 조회하면 다음과 같이 헤일리치킨과 헤일리편의점 두 가맹점의 데이터를 얻을 수 있습니다. 

통계 대시보드 화면

이 대시보드 조회 화면의 정책은 다음과 같습니다.

• 조회 기간은 필수로 선택해야 한다.
• 프랜차이즈명 또는 주류 도매 업체명을 선택하지 않으면 데이터를 조회할 수 없다. 이 경우 가맹점 이름 조회란은 비활성화 된다.
• 조회 가능한 필터 조합
  • 프랜차이즈명
  • 프랜차이즈명 + 가맹점 이름
  • 주류 도매 업체명
  • 주류 도매 업체명 + 가맹점 이름
  • 프랜차이즈명 + 주류 도매 업체명
  • 프랜차이즈명 + 주류 도매 업체명 + 가맹점 이름

 

문제가 되었던 상황을 살펴보겠습니다. 조회 조건은 다음과 같습니다. 첫 페이지에서는 타임아웃이 발생하지 않았으나, 데이터의 양이 많아 100페이지가 넘어가게 되었고, 79페이지를 선택하니 타임아웃이 발생했습니다.

• 프랜차이즈명 : 헤일리 프랜차이즈
• 조회 기간 : 1월 1일 ~ 2월 15일
• 페이지 : 79페이지

[조회 쿼리]

SELECT
    "storeId"
FROM
    hailey_statistics
where "date" >= '2024-01-01'::timestamp AND "date" < '2024-02-15'::timestamp
    AND (
       "agencyId" IN ('hailey','hailey_1') 
    )
GROUP BY
    "storeId",
    "totalOrderCount",
    "foodOrderCount",
    "alcoholOrderCount",
    "totalPriceSum"
ORDER BY
    "storeId" DESC
limit 10 OFFSET 790;

 

 

쿼리를 살펴봐도 JOIN도 없고 딱히 타임아웃이 발생할만한 요소는 없는 것 같아 보여서 Explain 옵션으로 쿼리 실행계획을 살펴보았습니다. cost가 23만이 넘어 조회비용이 꽤 높은 것을 알 수 있습니다.

Limit  (cost=232847.45..235794.88 rows=10 width=52)
  ->  GroupAggregate  (cost=0.56..2636770.56 rows=8946 width=52)
        Group Key: "storeId"
        ->  Sort  (cost=232847.41..424761.58 rows=8946 width=52)
        	Sort Key: "storeId" DESC
             ->  Seq Scan on hailey_statistics  (cost=0.56..2636510.13 rows=8946 width=52)
                    Filter: ((date >= '2024-02-15 00:00:00'::timestamp without time zone) AND (date < '2024-01-01 00:00:00'::timestamp without time zone) AND (("agencyId")::text = ANY ('{hailey,hailey_1}'::text[])))

 

 

LIMIT과 OFFSET 조건을 제거해서 실행계획을 다시 살펴보았습니다. 이번엔 cost가 42만이 넘어 조회비용이 더 높아졌습니다.

Finalize GroupAggregate  (cost=425751.43..427045.29 rows=8946 width=52)
  Group Key: "storeId"
  ->  Gather Merge  (cost=425751.43..426813.35 rows=8142 width=52)
        Workers Planned: 2
        ->  Partial GroupAggregate  (cost=424751.41..424873.54 rows=4071 width=52)
              Group Key: "storeId"
              ->  Sort  (cost=424751.41..424761.58 rows=4071 width=52)
                    Sort Key: "storeId" DESC
                    ->  Parallel Seq Scan on hailey_statistics  (cost=0.00..424507.33 rows=4071 width=52)
                          Filter: ((date >= '2024-01-01 00:00:00'::timestamp without time zone) AND (date < '2024-02-15 00:00:00'::timestamp without time zone) AND (("agencyId")::text = ANY ('{hailey, hailey_1}'::text[])))

 

그런데 뭔가 이상합니다. hailey_statistics 테이블에는 다음과 같이 인덱스가 두개 있습니다. 그러나 조회 쿼리들은 인덱스를 타지 않고 있습니다.

• hailey_statistics_pkey
• hailey_statistics_storeid_agencyid_alcoholagencyid

이 상황을 계기로 인덱스에 대해 좀 더 알아보니, 복합인덱스로 인덱스를 설정할 때에는 순서가 중요하다는 것을 알게되었습니다.

 

 

인덱스란

인덱스는 지정한 컬럼들을 기준으로 목차를 생성하여 테이블의 SELECT 속도(조회속도)를 높여주는 자료구조입니다.

인덱스가 설정되지 않으면 테이블 풀스캔을 하게 되고, 이는 성능에 중요한 영향을 미칩니다.

하지만, 인덱스를 설정하면 조회속도는 빨라지지만 UPDATE, INSERT, DELETE의 속도는 저하된다는 단점이 있습니다.

(index 색인 정보를 갱신하는 추가적인 비용 소요)

 

그렇기 때문에 효율적인 인덱스 설계로 단점을 최대한 보완하는 방법을 생각해보아야 합니다.

 

 

문제 해결에 적용한 인덱스의 특징

• 복합인덱스(다중 컬럼 인덱스) 사용 시, 첫번째 인덱스 조건은 조회 조건에 포함되어야만 합니다.

인덱스에 대해 더 알아보면서 위와 같은 복합인덱스의 특징을 알게되었습니다.

꼭 인덱스의 컬럼을 모두 사용해야만 인덱스가 사용되는 것은 아니지만, 첫번째 인덱스 조건은 반드시 조회조건에 포함되어야 한다는 것입니다.

 

현재 hailey_statistics_storeid_agencyid_alcoholagencyid 인덱스는 storeId - agencyId - alcoholAgencyId 컬럼의 순서로 설정되어 있습니다. 하지만 대시보드의 정책은, agencyId 또는 alcoholAgencyId가 선택되어야만 storeId를 선택할 수 있습니다.

즉, storeId가 조회조건에 없다면 이 인덱스를 타지 않게 되는데, storeId 컬럼은 이 대시보드 통계 조회에서 필수 조건이 아닙니다.

 

 

그래서 조회 시 필수로 포함되어야 하는 날짜 조건을 위주로 인덱스를 다시 추가해보고, 실행계획을 비교해보았습니다.

 

date 인덱스가 있을 때

Limit  (cost=62190.01..62191.46 rows=10 width=52)
  ->  Finalize GroupAggregate  (cost=62075.75..63369.62 rows=8946 width=52)
        Group Key: "storeId"
        ->  Gather Merge  (cost=62075.75..63137.67 rows=8142 width=52)
              Workers Planned: 2
              ->  Partial GroupAggregate  (cost=61075.73..61197.86 rows=4071 width=52)
                    Group Key: "storeId"
                    ->  Sort  (cost=61075.73..61085.91 rows=4071 width=52)
                          Sort Key: "storeId" DESC
                          ->  Parallel Index Scan using hailey_statistics_date on hailey_statistics  (cost=0.43..60831.65 rows=4071 width=52)
                                Index Cond: ((date >= '2024-01-01 00:00:00'::timestamp without time zone) AND (date < '2024-02-15 00:00:00'::timestamp without time zone))
                                Filter: (("agencyId")::text = ANY ('{hailey,hailey_1}'::text[]))

 

date + agencyId + alcoholAgencyId + storeId 인덱스가 있을 때

Limit  (cost=80881.36..80881.67 rows=10 width=52)
  ->  GroupAggregate  (cost=80856.20..81141.06 rows=8946 width=52)
        Group Key: "storeId"
        ->  Sort  (cost=80856.20..80880.63 rows=9770 width=52)
              Sort Key: "storeId" DESC
              ->  Index Scan using hailey_statistics_date_agencyid_alcoholagencyid_storeid on hailey_statistics  (cost=0.56..80208.74 rows=9770 width=52)
                    Index Cond: ((date >= '2024-01-01 00:00:00'::timestamp without time zone) AND (date < '2024-02-15 00:00:00'::timestamp without time zone) AND (("agencyId")::text = ANY ('{hailey,hailey_1}'::text[])))

 

 

date + alcoholAgencyId + storeId 인덱스와 date + agencyId + storeId가 있을 때

Limit  (cost=74520.58..74520.90 rows=10 width=52)
  ->  GroupAggregate  (cost=74495.44..74778.26 rows=8886 width=52)
        Group Key: "storeId"
        ->  Sort  (cost=74495.44..74519.68 rows=9698 width=52)
              Sort Key: "storeId" DESC
              ->  Index Scan using hailey_statistics_date_agencyid_storeid on hailey_statistics  (cost=0.56..73853.26 rows=9698 width=52)
                    Index Cond: ((date >= '2024-01-01 00:00:00'::timestamp without time zone) AND (date < '2024-02-15 00:00:00'::timestamp without time zone) AND (("agencyId")::text = ANY ('{hailey,hailey_1}'::text[])))

 

date + alcoholAgencyId  인덱스와 date + agencyId가 있을 때

Limit  (cost=44598.47..44598.49 rows=10 width=52)
  ->  Sort  (cost=44596.49..44618.71 rows=8886 width=52)
        Sort Key: "storeId" DESC
        ->  HashAggregate  (cost=44034.73..44123.59 rows=8886 width=52)
              Group Key: "storeId"
              ->  Index Scan using hailey_statistics_date_agencyid on hailey_statistics  (cost=0.43..43865.01 rows=9698 width=52)
                    Index Cond: ((date >= '2024-01-01 00:00:00'::timestamp without time zone) AND (date < '2024-02-15 00:00:00'::timestamp without time zone) AND (("agencyId")::text = ANY ('{hailey,hailey_1}'::text[])))

 

 

이렇게 비교해보았을 때 date + agencyId 인덱스와 date + alcoholAgencyId가 있을 때 쿼리 실행계획의 cost가 가장 낮았습니다.

또한, 통계 대시보드의 정책도 날짜는 무조건 포함되어야 하고, 그 다음엔 agencyId 와 alcoholAgencyId 둘 중 하나는 꼭 포함되어야만 조회할 수 있으므로, date + agencyId 인덱스와 date + alcoholAgencyId 인덱스를 추가하기로 했습니다.

 

 

 

 

이번 문제상황 해결을 위해 인덱스에 대해 알아보면서, 추가적으로 다음과 같은 인덱스의 특징도 알게되었습니다.

 

인덱스 추가 시 고려 사항

• WHERE 에서 OR을 사용할 때는 AND와 달리 주의가 필요함. AND 연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역할을 하지만, or 연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높다.
• 복합 인덱스를 잡는다면 카디널리티가 높은순에서 낮은순으로 (데이터 중복도가 낮은 순에서 높은순)으로 구성하는것이 더 성능이 뛰어나다.
• 복합 인덱스에 사용되는 컬럼은 가급적 UPDATE가 안되는 값을 선정해야 한다.
• 인덱스는 너무 많아도 좋지 않다. 가급적 테이블 당 3-5개를 넘지 않도록 하는 것이 좋다.

 

 

 

다음 글에서는 운영중인 DB에 인덱스를 추가하는 테스트 도중에 마주했던 에러에 대해 작성해보겠습니다!

 

 

[참고자료]

• [mysql] 인덱스 정리 및 팁
• [Postgresql] 인덱스(INDEX) 개념 및 효과적인 인덱스 설계 방법 정리