Post

복합 인덱스 설계 통한 쿼리 응답 속도 개선

Posted Updated
Preview Image
By Gyun
15 min read

🎬 Intro

Explain 및 Explain Analyze를 통해 인덱스를 설계하는 과정을 소개합니다.

결론

인덱스 설계를 통해 11초 이상 걸리는 병목 쿼리를 0.03초로 개선하였습니다.

문제 상황

img.png

img.png

  • RDS CPU 사용률이 갑자기 99%까지 급격하게 치솟는 문제가 발생
  • 피드백을 오래된 순으로 정렬 조회하는 API 호출에서 11초 이상의 병목이 생김

문제 분석

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

@Query("""
        SELECT f
        FROM Feedback f
        JOIN FETCH f.organizationCategory
        WHERE f.organization.uuid = :organizationUuid
        AND (:status IS NULL OR f.status = :status)
        AND (:cursorId IS NULL OR f.id > :cursorId)
        AND f.deletedAt IS NULL
        ORDER BY f.id ASC
        """)
List<Feedback> findByOldest(
        @Param("organizationUuid") UUID organizationUuid,
        @Param("status") ProcessStatus status,
        @Param("cursorId") Long cursorId,
        Pageable pageable
);

오래된순으로 피드백을 조회하는 JPQL 쿼리 입니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SELECT
  f1_0.id,
  f1_0.comment,
  f1_0.content,
  f1_0.created_at,
  f1_0.is_secret,
  f1_0.like_count,
  f1_0.modified_at,
  f1_0.organization_id,
  f1_0.organization_category_id,
  f1_0.posted_at,
  f1_0.status,
  f1_0.user_name
FROM
  feedback f1_0 JOIN organization o1_0
  ON o1_0.id = f1_0.organization_id
  AND o1_0.deleted_at IS NULL
WHERE
  f1_0.deleted_at IS NULL
  AND o1_0.uuid = 0x34313534386165652D323038332D3433
  AND f1_0.status = 'WAITING'
  AND f1_0.id > 91127
ORDER BY
  f1_0.id ASC
  LIMIT 10;

병목 발생 시 DB에 실행 됐던 쿼리 입니다. 해당 쿼리가 왜 문제가 됐는지는 실행 계획을 분석해보면 알 수 있습니다.

✅ 실행 계획 분석

feedback 테이블 인덱스

1
2
3
4
5
6
7

+----------+------------+--------------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name                             | Seq_in_index | Column_name              | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| feedback |          0 | PRIMARY                              |            1 | id                       | A         |     3584904 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| feedback |          1 | FK_feedback_organization_category_id |            1 | organization_category_id | A         |        8651 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| feedback |          1 | fk_feedback_organization             |            1 | organization_id          | A         |        1069 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+--------------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

병목 쿼리 실행 계획

1
2
3
4
5
6

+----+-------------+-------+------------+-------+----------------------------------+---------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                    | key     | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+----------------------------------+---------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | o1_0  | NULL       | const | PRIMARY,uuid,uuid_2              | uuid    | 16      | const |       1 |   100.00 | NULL        |
|  1 | SIMPLE      | f1_0  | NULL       | range | PRIMARY,fk_feedback_organization | PRIMARY | 8       | NULL  | 1831297 |     0.50 | Using where |
+----+-------------+-------+------------+-------+----------------------------------+---------+---------+-------+---------+----------+-------------+

organization 테이블 탐색 에서는 type = const, key = uuid로 인덱스를 타서 1건을 즉시 찾았습니다. 하지만 feedback 테이블 탐색 시 옵티마이저가 organization_id를 통해 인덱스를 탈 것이라 예상 했었으나, 위와 같이 전혀 다른 실행 계획 결과가 나왔습니다. 이를 한번 분석해보겠습니다.

key = PRIMARY, Extra = Using where

feedback 탐색 시 클러스터링(PK) 인덱스만 사용, 추가 WHERE 조건은 인덱스 범위 이후 필터링됨

type = range, rows = 1831297

인덱스 범위 스캔으로 약 180만 row 스캔

filtered = 0.5

스토리엔진에서 넘어온 값 중 0.5%의 레코드만 사용

위 상황을 보면 병목 쿼리는 인덱스를 전혀 타지 않을 것이라는걸 예상할 수 있습니다.

병목 쿼리 실행 계획 분석

1
2
3
4
5
6
7
8

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> 3️⃣ Limit: 10 row(s)  (cost=244091 rows=10) (actual time=56.6..12212 rows=8 loops=1)
    -> 2️⃣ Filter: ((f1_0.organization_id = '147') and (f1_0.`status` = 'WAITING') and (f1_0.deleted_at is null) and (f1_0.id > 91127))  (cost=244091 rows=9156) (actual time=55.8..12211 rows=8 loops=1)
        -> 1️⃣ Index range scan on f1_0 using PRIMARY over (91127 < id)  (cost=244091 rows=1.83e+6) (actual time=0.0713..11993 rows=3.64e+6 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

실행 계획 분석을 통해 실제로 어떻게 동작했는지 확인해볼 수 있습니다. 실행된 순서는 넘버링한 것처럼 역순이므로 역순으로 분석하면 됩니다.

1️⃣ Index range scan
  • PRIMARY 인덱스로 id > 91127 범위 스캔 진행
  • 약 183만 건 스캔 예상했으나, 실제로는 약 364만 건 스캔
  • 소요 시간: 11.99초
2️⃣ Filter
  • 364만 건에 대해 WHERE 조건 필터링 수행
    • organization_id = 147
    • status = ‘WAITING’
    • deleted_at IS NULL
  • 약 9,156건 통과 예상했으나, 실제로는 8건만 통과
  • 소요 시간: 12.21초 (누적)
3️⃣ Limit
  • 요청 10건
  • 최종 8건 반환
  • 소요 시간: 12.21초 (최종)

결과적으로 실제로 인덱스를 전혀 타지 않고 있다는것을 확인할 수 있습니다.

개선 과정

✅ 인덱스를 어디에 걸어야 할까??

우선 인덱스를 걸때 고려되는것들은 다음과 같습니다.

인덱스 컬럼의 크기는 되도록 작게

  • 인덱스는 B-tree 기반 페이지로 관리 되고, 해당 페이지에는 인덱스 컬럼 데이터와 PK가 key-value 쌍으로 저장 됩니다. 따라서 인덱스의 컬럼 데이터의 크기가 크다면, 한 페이지에 담을 수 있는 인덱스가 적어지고 B-tree의 깊이도 깊어지게 됩니다. 이는 결국 탐색의 비용을 증가시킵니다.
  • 따라서 인덱스 컬럼의 데이터 크기가 작을 수록 유리합니다.

인덱스 컬럼 순서는 조건절 순서에 맞게

  • 복합 인덱스 설정 시 항상 다음 인덱스는 이전 인덱스에 의존해서 정렬 됩니다.
  • 따라서 앞쪽 인덱스 컬럼에서 충분히 조건이 걸리지 않으면 뒤쪽 인덱스 컬럼은 활용이 되지 못합니다.
  • 일반적으로 쿼리의 조건절의 순서대로 인덱스를 컬럼 순서를 정하면 되지만, 범위 조건 인덱스 다음에는 인덱스가 동작하지 않으므로 반드시 범위조건은 마지막 순서에 위치 시켜야 합니다.

카디널리티가 높은곳에

  • 카디널리티가 높다는것은 레코드의 고유성이 높다는 뜻이므로, 카디널리티가 높은 컬럼에 인덱스를 걸면 그만큼 검색 대상이 줄어들어 성능 측면에서 유리합니다.

인덱스의 정렬 및 스캔 방향 고려

  • Index Forward Scan, Index Backward Scan 이 있다.
  • 이는 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다.
  • 인덱스는 구조적으로 Index Forward Scan이 유리하다.

✅ 인덱스 적용하기

병목 쿼리 조건절

1
2
3
4
5
6
7
8
9
10
11

feedback f1_0 JOIN organization o1_0
ON o1_0.id = f1_0.organization_id
AND o1_0.deleted_at IS NULL
WHERE
f1_0.deleted_at IS NULL
AND o1_0.uuid = 0x34313534386165652D323038332D3433
AND f1_0.status = 'WAITING'
AND f1_0.id > 91127
ORDER BY
f1_0.id ASC
LIMIT 10;

병목 쿼리 조건절에 사용된 각 테이블의 컬럼 입니다. organization은 인덱스를 잘 타고 있으니 feedback 테이블 컬럼만 확인하면 됩니다. feedback 테이블에는 status, id, deleted_at, organization_id 컬럼이 조건절에 사용됩니다.

feedback 테이블 카디널리티

1
2
3
4
5

+----------------+--------------------+--------------------+------------------------+
| id_cardinality | org_id_cardinality | status_cardinality | deleted_at_cardinality |
+----------------+--------------------+--------------------+------------------------+
|        3675773 |               1095 |                  2 |                      0 |
+----------------+--------------------+--------------------+------------------------+

feedback 테이블 스키마

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

+--------------------------+-----------------------------+------+-----+---------+----------------+
| Field                    | Type                        | Null | Key | Default | Extra          |
+--------------------------+-----------------------------+------+-----+---------+----------------+
| id                       | bigint                      | NO   | PRI | NULL    | auto_increment |
| created_at               | datetime(6)                 | NO   |     | NULL    |                |
| modified_at              | datetime(6)                 | NO   |     | NULL    |                |
| content                  | varchar(500)                | YES  |     | NULL    |                |
| is_secret                | bit(1)                      | NO   |     | NULL    |                |
| organization_id          | bigint                      | NO   | MUL | NULL    |                |
| status                   | enum('CONFIRMED','WAITING') | NO   |     | NULL    |                |
| user_name                | varchar(255)                | NO   |     | NULL    |                |
| like_count               | int                         | NO   |     | NULL    |                |
| posted_at                | datetime(6)                 | NO   |     | NULL    |                |
| organization_category_id | bigint                      | YES  | MUL | NULL    |                |
| comment                  | varchar(500)                | YES  |     | NULL    |                |
| deleted_at               | datetime                    | YES  |     | NULL    |                |
| image_url                | varchar(500)                | YES  |     | NULL    |                |
+--------------------------+-----------------------------+------+-----+---------+----------------+

위 정보로 인덱스의 크기, 카디널리티, 순서를 고려했을때 인덱스는 organization_id, status, deleted_at, id 이 4가지 컬럼에 걸기로 결정하였습니다. status와 deleted_at는 카디널리티가 낮음에도 인덱스를 거는 이유는 다음과 같습니다.

  • 2컬럼 모두 데이터 크기가 작음
  • 인덱스로 인해 쓰기 비용이 증가하더라도 조회가 압도적으로 많아 이득이 큼
  • deleted_at 경우 소프트 삭제에 이용되므로 추후 카디널리티가 계속 올라감

이제 인덱스를 걸고 조회 속도와 실행 계획 및 분석 결과를 살펴보겠습니다.

✅ 인덱스 적용 결과

인덱스 크기

1
2
3
4
5
6

+------------------------------------+---------+
| type                               | size_mb |
+------------------------------------+---------+
| feedback table                     |  632.67 |
| idx_feedback_org_status_deleted_id |   95.67 |
+------------------------------------+---------+

테이블 대비 인덱스가 차지하는 비율은 약 15% 입니다.

조회 속도

img.png

약 11초에서 0.03초로 개선되었습니다.

실행 계획

1
2
3
4
5
6

+----+-------------+-------+------------+-------+---------------------------------------------------------------------+------------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys                                                       | key                                | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------------------------------------------------------------+------------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | o1_0  | NULL       | const | PRIMARY,uuid,uuid_2                                                 | uuid                               | 16      | const |    1 |   100.00 | NULL                  |
|  1 | SIMPLE      | f1_0  | NULL       | range | PRIMARY,fk_feedback_organization,idx_feedback_org_status_deleted_id | idx_feedback_org_status_deleted_id | 23      | NULL  |    8 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------------------------------------------------------------+------------------------------------+---------+-------+------+----------+-----------------------+
key = idx_feedback_org_status_deleted_id, Extra = Using index condition

복합 인덱스 사용 (organization_id, status, deleted_at, id), WHERE 조건을 스토리지 엔진에서 모두 처리

type = range, rows = 8

인덱스 범위 스캔으로 약 8건만 스캔

filtered = 100.00

스토리지 엔진에서 넘어온 8건 중 100% 모두 사용

인덱스가 잘타고 있는것을 확인할 수 있습니다.

실행 계획 분석

1
2
3
4
5
6
7

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> 2️⃣ Limit: 10 row(s)  (cost=10.6 rows=8) (actual time=0.025..0.056 rows=8 loops=1)
    -> 1️⃣ Index range scan on f1_0 using idx_feedback_org_status_deleted_id over (organization_id = 147 AND status = 'WAITING' AND deleted_at = NULL AND 91127 < id), with index condition: ((f1_0.organization_id = '147') and (f1_0.`status` = 'WAITING') and (f1_0.deleted_at is null) and (f1_0.id > 91127))  (cost=10.6 rows=8) (actual time=0.0241..0.0546 rows=8 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1️⃣ Index range scan
  • idx_feedback_org_status_deleted_id 복합 인덱스로 범위 스캔 진행
  • 4개 조건 모두 인덱스에서 처리
    • organization_id = 147
    • status = ‘WAITING’
    • deleted_at IS NULL
    • id > 91127
  • 약 8건 스캔 예상, 실제로도 8건 스캔
  • 소요 시간: 0.0546초

2️⃣ Limit

  • 요청 10건
  • 최종 8건 반환
  • 소요 시간: 0.056초 (최종)

최종적으로 실제로 인덱스가 잘타고 있는것을 확인할 수 있습니다.

RDS CPU 사용률

img.png

  • RDS CPU 사용률 약 30%로 개선

마무리하며

성능 개선은 언제해도 재밌는것 같습니다! 그리고 옵티마이저가 왜 기존 organization_id를 안탔는지 아직도 의문입니다. 옵티마이저가 어떤 통계를 보고 실행 계획을 짜는지 추후 기회가 된다면 학습해 볼 예정입니다!

ETC, Refactoring
MySQL Explain Query Tuning Index
This post is licensed under CC BY 4.0 by the author.