토미의 Coupon 인덱스 정리 - 2

9 분 소요

3번 문제

@Test  
void 현재_발급_가능한_쿠폰_조회() throws InterruptedException {  
    AtomicBoolean running = new AtomicBoolean(false);  
    AtomicInteger requestCount = new AtomicInteger(0);  
    AtomicLong totalElapsedTime = new AtomicLong(0);  
  
    int statusCode = RestAssured.get("/coupons/issuable").statusCode();  
    assertThat(statusCode).withFailMessage("발급 가능한 쿠폰 조회 API 호출에 실패했습니다. 테스트 대상 서버가 실행중인지 확인해 주세요.").isEqualTo(200);  
  
    executeMultipleRequests(running, requestCount, totalElapsedTime, () -> RestAssured.get("/coupons/issuable"));  
  
    System.out.println("Total request count: " + requestCount.get());  
    System.out.println("Total elapsed time: " + totalElapsedTime.get() + "ms");  
  
    long averageElapsedTime = totalElapsedTime.get() / requestCount.get();  
    System.out.println("Average elapsed time: " + totalElapsedTime.get() / requestCount.get() + "ms");  
  
    assertThat(averageElapsedTime).isLessThanOrEqualTo(500L);
}

3번 문제는 현재 발급 가능한 쿠폰을 조회하는 API 테스트이다. 호출하는 메서드를 살펴보자.

CouponController

// CouponController.class
@GetMapping("/issuable")  
public List<CouponResponse> findIssuableCoupons() {  
    return couponService.findIssuableCoupons().stream()  
            .map(CouponResponse::from)  
            .collect(toList());  
}

CouponService

// CouponService.class
public List<Coupon> findIssuableCoupons() {  
    LocalDateTime now = LocalDateTime.now();  
    return couponRepository.findAllByIssuableAndCouponStatusAndIssueStartedAtLessThanAndIssueEndedAtGreaterThan(  
                    true, CouponStatus.ISSUABLE, now, now).stream()  
            .filter(coupon -> coupon.isIssuableCoupon(now))  
            .collect(toList());  
}

CouponRepository

// CouponRepository.class
List<Coupon> findAllByIssuableAndCouponStatusAndIssueStartedAtLessThanAndIssueEndedAtGreaterThan(boolean issuable, CouponStatus couponStatus, LocalDateTime issueStartedAt, LocalDateTime issueEndedAt);

findAllByIssuableAndCouponStatusAndIssueStartedAtLessThanAndIssueEndedAtGreaterThan 메서드는 발급 가능한지 여부를 나타내는 issuable, 쿠폰의 상태를 나타내는 CouponStatus, 쿠폰의 유효 시작기간 issueStartedAt과 만료기간 issueEndedAt을 매개변수로 받아서 쿠폰 리스트를 조회한다.

CouponStatus

public enum CouponStatus {  
    PREPARING,  
    ISSUABLE,  
    EXPIRED,  
    DISCARDED  
    ;  
  
    public boolean isNotIssuable() {  
        return this != ISSUABLE;  
    }  
  
    /**  
     * 폐기된 쿠폰은 사용할 수 없다.
     */    
    public boolean isNotUsable() {  
        return this == PREPARING || this == DISCARDED;  
    }  
}

CouponStatus enum은 이런 값들을 가지고 있다.

SQL 쿼리 예상

findAllByIssuableAndCouponStatusAndIssueStartedAtLessThanAndIssueEndedAtGreaterThan 메서드를 사용해서 만료된 쿠폰들을 조회한다고 가정하고, 해당 메서드를 sql 쿼리로 작성해보면 이런 식이다.

select * from coupon c
where c.issuable = false
and c.coupon_status = 'EXPIRED'  
and c.issue_started_at < '2019-02-01 00:00:00.000000'  
and c.issue_ended_at > '2019-01-01 00:00:00.00000';

query creation으로 작성된 부분 중에 IusseStartedAtLessThan, IssueEndedAtGreaterThan 부분으로 인해 약간 날짜 값이 반대로 뒤집혀있는 느낌이 든다. 마치 젓가락질을 X자로 하는 듯 한..? 개인적인 생각으로 IssueStartedAtGreaterThanOrEqual, IssueEndedAtLessThanOrEqual 이런 식으로 작성하는 것이 가독성이 더 좋을 거라고 생각한다.

인덱스 분석

coupon 테이블의 인덱스를 살펴보면coupon_id PRIMARY 키를 제외한 어떠한 인덱스도 없다.

[Pasted image 20240830143759.png]

실행 계획 분석

해당 쿼리를 explain을 통해서 살펴보자.

explain select * from coupon c
where c.issuable = false
and c.coupon_status = 'EXPIRED'  
and c.issue_started_at < '2019-02-01 00:00:00.000000'  
and c.issue_ended_at > '2019-01-01 00:00:00.00000';

[Pasted image 20240830143842.png]

실행 계획을 보면 해당 쿼리는 조건절에 coupon_id를 포함하지 않기 때문에 인덱스를 사용하지 않는다. type는 ALL, key는 null, filtered는 0.11, Extra는 Using where다.

-> Filter: ((c.coupon_status = 'EXPIRED') and (c.issuable = false) and (c.issue_started_at < TIMESTAMP'2019-02-01 00:00:00') and (c.issue_ended_at > TIMESTAMP'2019-01-01 00:00:00'))  (cost=32839 rows=357) (actual time=3.52..174 rows=8 loops=1)
    -> Table scan on c  (cost=32839 rows=321576) (actual time=3.4..142 rows=351160 loops=1)

explain analyze 결과를 분석해보면 filter 조건으로 쿠폰의 상태, 발행 가능한지, 발행 시작일과 만료일 4개의 조건으로 조회했고, 실제 실행 시간은 3.52ms에서 174ms 사이였고, 비용은 32,839가 나왔다. 인덱스를 사용하지 않은 Table scan이 이루어졌고, 전체 351,160 행 중에서 필터 조건에 만족될 것으로 예상되는 행 수는 321,576행이다. 여기서 중요한 점은 예상되는 321,576행 중 실제로 조건에 맞는 행은 8개의 행만 남았다는 것이다.

예상 행 수와 실제 결과의 차이가 많이 난다는 것은 인덱스 사용이나 쿼리 조건을 재검토하여 성능을 개선할 필요가 있다는 것을 의미한다.

Table scan을 통한 성능 테스트 결과

인덱스가 아닌 Table scan을 통해 주어진 테스트를 돌려보고 요청 당 평균 소요시간을 살펴보면 이런 결과가 나온다.

Total request count: 471
Total elapsed time: 100600ms
Average elapsed time: 213ms

조건절에 포함된 매개변수 인덱스 설정

select * from coupon c
where c.issuable = false
and c.coupon_status = 'EXPIRED'  
and c.issue_started_at < '2019-02-01 00:00:00.000000'  
and c.issue_ended_at > '2019-01-01 00:00:00.00000';

조건절에 포함된 4가지 컬럼에 인덱스를 설정한다.

create index idx_issuable_coupon_status_issue_started_at_issue_ended_at on coupon (issuable, coupon_status, issue_started_at, issue_ended_at);

[Pasted image 20240830151003.png]

explain

[Pasted image 20240830151049.png]

| #  | id | select_type | table | partitions | type  | possible_keys                                      | key                                               | key_len | ref | rows | filtered | Extra                    |
|----|----|-------------|-------|------------|-------|---------------------------------------------------|---------------------------------------------------|---------|-----|------|----------|--------------------------|
| 1  | 1  | SIMPLE      | c     |            | range | idx_issuable_coupon_status_issue_started_at_issue_ended_at | idx_issuable_coupon_status_issue_started_at_issue_ended_at | 134     |     | 8    | 33.33    | Using index condition     |

type은 range로 인덱스를 사용하여 범위 검색을 하도록 변경되었고, key로는 방금 만든 인덱스를 사용하고 있다. filtered는 33.33%, Extra는 Using index condition이 되었다. Using index는 봤었는데, Using index condition은 뭘까?

`Using index`와 `Using index condition`의 차이점

Using index와 Using index condition은 두 가지 다른 인덱스 사용 방식이다.

Using index:
- 쿼리가 인덱스를 사용하여 데이터를 검색하고, 인덱스에서 직접 필요한 데이터를 가져오는 경우이다. 이 경우, 테이블의 데이터 페이지를 읽지 않고, 다시 말해서 데이터가 있는 테이블에 직접 접근하지 않고 인덱스만으로 쿼리를 처리할 수 있어 성능이 향상된다.
Using index condition:
- 인덱스를 사용하여 조건을 평가하는 경우입니다. 즉, 인덱스의 값을 기반으로 특정 조건을 필터링하는 과정을 포함합니다. 이 경우, 인덱스가 적용되지만, 최종적으로 조건에 맞는 데이터를 찾기 위해 테이블의 데이터 페이지를 읽어야 할 수도 있다.

expain analyze

-> Index range scan on c using idx_issuable_coupon_status_issue_started_at_issue_ended_at over (issuable = 0 AND coupon_status = 'EXPIRED' AND NULL < issue_started_at < '2019-02-01 00:00:00.000000'), with index condition: ((c.coupon_status = 'EXPIRED') and (c.issuable = false) and (c.issue_started_at < TIMESTAMP'2019-02-01 00:00:00') and (c.issue_ended_at > TIMESTAMP'2019-01-01 00:00:00'))  (cost=3.86 rows=8) (actual time=0.0441..0.09 rows=8 loops=1)

쿼리 실행 계획 분석을 보면 Index range scan을 하고있고, 조건과 인덱스 조건이 각각 있다. 비용과 실제 실행 시간은 눈에 띄게 줄어들은 것을 확인할 수 있다.

커버링 인덱스를 통한 성능 테스트 결과

issuable, coupon_status, issue_started_at, issue_ended_at 컬럼 순서로 인덱스.

Total request count: 12853
Total elapsed time: 100049ms
Average elapsed time: 7ms

그런데 조건, 인덱스 조건(index condition)은 무슨 차이가 있을까?

조건(where)과 인덱스 조건(Index condition)

1.조건 (where 조건)

쿼리에서 주로 where절에 명시되는 데이터를 필터링하기 위해 사용되는 조건이다. 예를 들어,issuable = 0이나coupon_status = 'EXPIRED'같은 조건이 여기에 해당한다. 데이터베이스가 검색해야 할 데이터를 제한하는 역할을 한다.

2.인덱스 조건 (Index condition)

인덱스를 사용할 때 적용되는 조건이다. 인덱스 조건은 인덱스를 통해 데이터 검색을 최적화하는 데 도움을 준다. 예를 들어,c.coupon_status = 'EXPIRED'와 같은 조건이 인덱스를 기반으로 필터링할 때 사용된다. 인덱스를 통해 더 빠르게 데이터를 찾고, 검색 성능을 향상시키는 역할을 한다.

결과적으로 위 쿼리와 인덱스 설정으로 인해, 4가지 컬럼 모두 where, index condition으로 적용된다. 그런데 issuable, coupon_status 컬럼의 경우 대입 조건이고, issue_started_at, issue_ended_at 컬럼의 경우 범위 검색 조건이다. 이 두 조건은 어떤 식으로 적용될까?

MySQL EXPLAIN Documentation
MySQL 쿼리 최적화

조건의 종류

범위 검색 조건
- issue_started_at:NULL < issue_started_at < '2019-02-01 00:00:00.000000'
- issue_ended_at:issue_ended_at > TIMESTAMP '2019-01-01 00:00:00'
- 이 조건들은 특정 범위 내의 값을 찾는 데 사용된다. 데이터베이스는 이 범위에 해당하는 날짜를 가진 행들을 검색한다.
대입 조건
- issuable = 0: 발행 가능한 쿠폰이 아닌 데이터 필터링.
- coupon_status = ‘EXPIRED’: 쿠폰이 만료된 상태인 데이터 필터링.
- 이 조건들은 특정 값을 가진 행들을 찾는 데 사용된다.

적용 과정

쿼리가 실행될 때, 다음과 같은 순서로 조건이 적용된다.

인덱스 활용
- 인덱스가 설정된 컬럼(예:issuable,coupon_status,issue_started_at,issue_ended_at)에 대해 인덱스 범위 스캔이 이루어진다.
- 이 과정에서 인덱스 조건이 먼저 적용되어 인덱스를 통해 필터링된 데이터 집합이 생성된다.
범위 검색 조건 적용
- 인덱스를 통해 검색된 데이터에서 먼저 범위 검색 조건이 적용된다. 즉,issue_started_at과issue_ended_at의 범위에 맞는 행들이 필터링된다.
대입 조건 적용
- 이후, 범위 검색을 통과한 결과에서 대입 조건인issuable과coupon_status를 적용하여 최종적으로 조건을 만족하는 행들을 찾는다.

최종 결과

인덱스를 사용하여 범위 조건으로 데이터를 축소한 후, 대입 조건을 통해 남은 데이터에서 최종적으로 반환할 행을 결정한다. 쿼리의 성능을 최적화하고, 필요 없는 데이터를 미리 걸러내는 효과가 있다.

Cardinality 고려한 인덱스 컬럼 순서

[Pasted image 20240830151003.png]

그런데 위 index에서 Cardinality 부분을 자세히 살펴보면 이상하다. issuable, coupon_status, issue_started_at, issue_ended_at 순서로 인덱스가 걸려있는데, Cardinality는 시작, 만료 기간을 나타내는 컬럼이 앞의 두 컬럼보다 훨씬 높다. 그렇다면 인덱스가 필터링을 더 잘 수행하게 하기 위해 issue_started_at, issue_ended_at, coupon_status, issuable 순서로 인덱스를 생성하는 것이 더 좋지 않을까?

create index idx_issue_started_at_issue_ended_at_coupon_status_issuable  
    on coupon (issue_started_at, issue_ended_at, coupon_status, issuable);

실험을 위해 새로운 인덱스를 issue_started_at, issue_ended_at, coupon_status, issuable 순서로 생성한다.

[Pasted image 20240830162423.png]

이제 컬럼 순서가 다른 복합 인덱스 두 개가 생성되어 있다.

explain analyze select * from coupon c 
use index (idx_issue_started_at_issue_ended_at_coupon_status_issuable)  
        where c.issuable = false  
          and c.coupon_status = 'EXPIRED'  
          and c.issue_started_at < '2019-02-01 00:00:00.000000'  
          and c.issue_ended_at > '2019-01-01 00:00:00.00000';

이렇게 from절 뒤에 use index (인덱스명) 형태로 인덱스를 지정해주면 MySQL Optimizer가 cost 기반으로 최적의 선택을 하는 대신, 지정한 인덱스를 사용하도록 강제할 수 있다.

explain

| #  | id | select_type | table | partitions | type  | possible_keys                                         | key                                                   | key_len | ref | rows | filtered | Extra                     |
|----|----|-------------|-------|------------|-------|------------------------------------------------------|------------------------------------------------------|---------|-----|------|----------|---------------------------|
| 1  | 1  | SIMPLE      | c     |            | range | idx_issue_started_at_issue_ended_at_coupon_status_issuable | idx_issue_started_at_issue_ended_at_coupon_status_issuable | 9       |     | 2180 | 3.33     | Using index condition      |

새로 만든 시작, 만료 기간이 우선으로 설정된 인덱스를 사용하고 있고, type은 range로 전과 똑같다. filtered가 3.33%로 이전의 커버링 인덱스와 비교하여 1/10 정도 떨어졌지만, 똑같은 Extra Using index condition인 것을 볼 수 있다.

explain analyze

-> Index range scan on c using idx_issue_started_at_issue_ended_at_coupon_status_issuable over (NULL < issue_started_at < '2019-02-01 00:00:00.000000'), with index condition: ((c.coupon_status = 'EXPIRED') and (c.issuable = false) and (c.issue_started_at < TIMESTAMP'2019-02-01 00:00:00') and (c.issue_ended_at > TIMESTAMP'2019-01-01 00:00:00'))  (cost=981 rows=2180) (actual time=0.0358..1.34 rows=8 loops=1)

Index range scan을 하고있고, 인덱스 범위 스캔의 조건으로 issue_started_at이 NULL보다 크고, '2019-02-01 00:00:00.000000'보다 작은 범위를 지정하고 있다. 인덱스 범위 스캔의 조건을 먼저 적용한 후, index condition에 명시된 조건들을 적용한다. cost는 981로 이전 커버링 인덱스보다 비용이 많이 늘어났다.

범위 조건 우선하여 인덱스 설정하고 성능 테스트

기존에 issuable, coupon_status를 먼저 설정한 인덱스를 지우고 테스트를 실행했다.

alter table coupon drop index idx_issuable_coupon_status_issue_started_at_issue_ended_at;

[Pasted image 20240830170424.png]

Total request count: 329
Total elapsed time: 101617ms
Average elapsed time: 308ms

3번 문제의 검증 조건이 요청 평균 소요 시간 500ms 이하라서 테스트에는 통과했지만, 이전 커버링 인덱스를 통한 요청 평균 소요 시간인 7ms과 비교해서 현재 308ms가 나왔으므로 성능이 4285.71% 떨어졌다. 왜 이렇게까지 효율이 많이 떨어졌을까?

효율이 떨어진 이유

Cardinality가 높은 컬럼의 순서를 우선하도록 하여 성능이 더 좋아질 줄 알았는데 오히려 성능이 하락한 이유는 바로 인덱스의 첫 번째 컬럼인issue_started_at의 조건을 우선적으로 평가하기 때문이다. 근데 해당 컬럼은 범위 조건이기 때문에 인덱스 검색 방식을 변경한다. 사실 첫 번째가 아니더라도 범위 검색 조건 뒤에 오는 컬럼은 인덱스의 효율성을 잃게 된다. 향로님의 인덱스 포스팅 참고

between,like,<,> 등 범위 조건이 적용되면, 인덱스는 해당 범위 내의 모든 데이터를 검색하게 되므로, 이후 인덱스의 다른 컬럼들은 효율적으로 사용하지 못한다. 따라서, 위의 인덱스 기준으로issue_started_at조건 이후에 오는 issue_ended_at, coupon_status, issuable 컬럼은 인덱스에서 최적화되지 않고, 전체 데이터에서 필터링 하게 된다.

Cardinality가 높은 순서로 인덱스를 구성하는 것은 중요하지만, 인덱스 컬럼을 사용할 때 고정 조건이냐, 범위 조건이냐에 따라서 다르게 구성하는 것이 좋다. 데이터 상황에 따라서도 달라지겠지만, Cardinality가 높으면서 고정 조건 인덱스 컬럼을 앞에 배치하고, 범위 조건 인덱스 컬럼을 뒤에 배치하는 것이 인덱스를 더 효율을 극대화 시킬 수 있는 방법이다. 위 경우에는 범위 조건이 issue_started_at, issue_ended_at 두 개 이기 때문에 둘 중 Cardinality가 더 높은 한 개만 추가하여 coupon_status, issuable, issue_started_at 이렇게 세 개의 컬럼으로 인덱스를 구성하는 것이 데이터 추가 시 인덱스 테이블에 데이터를 정렬하면서 발생하는 부담을 줄일 수 있을 것이다.

쿼리에서 사용된 모든 컬럼에 대한 인덱스를 만드는 것이 좋을까?

MySQL Document

The best way to improve the performance ofSELECToperations is to create indexes on one or more of the columns that are tested in the query. The index entries act like pointers to the table rows, allowing the query to quickly determine which rows match a condition in theWHEREclause, and retrieve the other column values for those rows. All MySQL data types can be indexed. Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.

MySQL 문서에서도 언급하듯이, 불필요한 인덱스는 여러 가지 문제를 야기할 수 있다. 쿼리 성능을 개선하려면 최적의 인덱스 세트를 찾아야 한다. 이를 위해 Cardinality, 고정 조건과 범위 조건의 조합, 그리고 쿼리의 사용 패턴을 고려해야 한다. 모든 쿼리를 대상으로 커버링 인덱스를 구성하기보다는, 자주 사용되거나 성능이 중요한 쿼리를 분석하여 필요한 인덱스를 선택적으로 구성하는 것이 더 효율적이다.

여러 컬럼으로 인덱스 설정 시 조건 누락

인덱스가 여러 컬럼으로 구성되어 있을 때, 모든 컬럼을 조회 조건에 포함해야만 인덱스가 사용되는 것은 아니다. 어떤 컬럼은 누락될 수 있고, 어떤 컬럼은 반드시 포함되어야 인덱스를 효과적으로 사용할 수 있다.

인덱스 예시:coupon_status,issuable,issue_started_at

첫 번째 조회 쿼리

조건:coupon_status와issuable을 포함

SELECT * 
FROM coupons 
WHERE coupon_status = 'active' AND issuable = true;

인덱스를 정상적으로 사용함. (필터링 비율이 10%로 효율적이지는 않지만, 인덱스 사용 가능)

두 번째 조회 쿼리:

조건:issuable과issue_started_at만 포함,coupon_status제외

SELECT * 
FROM coupons 
WHERE issuable = true AND issue_started_at >= '2023-01-01';

인덱스를 전혀 사용하지 못함.

조회 쿼리를 작성할 때 인덱스를 효과적으로 사용하기 위해서는 반드시 첫 번째 인덱스 컬럼(예: coupon_status)이 포함되어야 한다. 첫 번째 컬럼이 누락되면 인덱스를 사용할 수 없다.

Twitter Facebook LinkedIn

이건상

토미의 Coupon 인덱스 정리 - 2

3번 문제

CouponController

CouponService

CouponRepository

CouponStatus

SQL 쿼리 예상

인덱스 분석

실행 계획 분석

Table scan을 통한 성능 테스트 결과

조건절에 포함된 매개변수 인덱스 설정

explain

`Using index`와 `Using index condition`의 차이점

expain analyze

커버링 인덱스를 통한 성능 테스트 결과

그런데 조건, 인덱스 조건(index condition)은 무슨 차이가 있을까?

조건(where)과 인덱스 조건(Index condition)

조건의 종류

적용 과정

최종 결과

Cardinality 고려한 인덱스 컬럼 순서

explain

explain analyze

범위 조건 우선하여 인덱스 설정하고 성능 테스트

효율이 떨어진 이유

쿼리에서 사용된 모든 컬럼에 대한 인덱스를 만드는 것이 좋을까?

여러 컬럼으로 인덱스 설정 시 조건 누락

공유하기

댓글남기기

참고

토미의 Coupon 인덱스 정리 - 3

토미의 Coupon 인덱스 정리 - 1

org.hibernate.HibernateException: A collection with cascade=”all-delete-orphan” was no longer referenced by the owning entity instance 에러 트러블 슈팅

행동대장을 정산 특화 서비스로 탈바꿈 하기 위한 아이디어

이건상

3번 문제

CouponController

CouponService

CouponRepository

CouponStatus

SQL 쿼리 예상

인덱스 분석

실행 계획 분석

Table scan을 통한 성능 테스트 결과

조건절에 포함된 매개변수 인덱스 설정

explain

Using index와 Using index condition의 차이점

expain analyze

커버링 인덱스를 통한 성능 테스트 결과

그런데 조건, 인덱스 조건(index condition)은 무슨 차이가 있을까?

조건(where)과 인덱스 조건(Index condition)

조건의 종류

적용 과정

최종 결과

Cardinality 고려한 인덱스 컬럼 순서

explain

explain analyze

범위 조건 우선하여 인덱스 설정하고 성능 테스트

효율이 떨어진 이유

쿼리에서 사용된 모든 컬럼에 대한 인덱스를 만드는 것이 좋을까?

여러 컬럼으로 인덱스 설정 시 조건 누락

공유하기

댓글남기기

참고

토미의 Coupon 인덱스 정리 - 3

토미의 Coupon 인덱스 정리 - 1

org.hibernate.HibernateException: A collection with cascade=”all-delete-orphan” was no longer referenced by the owning entity instance 에러 트러블 슈팅

행동대장을 정산 특화 서비스로 탈바꿈 하기 위한 아이디어

`Using index`와 `Using index condition`의 차이점