토미의 Coupon 인덱스 정리 - 3
4번 문제
회원이 가지고 있는 사용 가능한 쿠폰을 조회하는 API 이다.
@Test
void 회원이_가지고_있는_사용_가능한_쿠폰_조회() throws InterruptedException {
AtomicBoolean running = new AtomicBoolean(false);
AtomicInteger requestCount = new AtomicInteger(0);
AtomicLong totalElapsedTime = new AtomicLong(0);
int statusCode = RestAssured.get("/member-coupons/by-member-id?memberId=" + ThreadLocalRandom.current()
.nextLong(MIN_MEMBER_ID, MAX_MEMBER_ID + 1)).statusCode();
assertThat(statusCode).withFailMessage("회원이 가지고 있는 쿠폰 조회 API 호출에 실패했습니다. 테스트 대상 서버가 실행중인지 확인해 주세요.")
.isEqualTo(200);
executeMultipleRequests(running, requestCount, totalElapsedTime,
() -> RestAssured.get("/member-coupons/by-member-id?memberId=" + ThreadLocalRandom.current()
.nextLong(MIN_MEMBER_ID, MAX_MEMBER_ID + 1)));
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(100L);
}
MemberCouponController
@GetMapping("/by-member-id")
public List<MemberCouponResponse> getMemberCoupons(@RequestParam("memberId") Long memberId) {
return memberCouponService.findUsableMemberCoupons(memberId).stream()
.map(MemberCouponResponse::from)
.collect(toList());
}
MemberCouponService
@Transactional(readOnly = true)
public List<MemberCoupon> findUsableMemberCoupons(Long memberId) {
return memberCouponRepository.findByMemberIdAndUsedAndUseEndedAtAfter(memberId, false, LocalDateTime.now())
.stream()
.filter(memberCoupon -> memberCoupon.getCoupon().isUsableCoupon())
.collect(toList());
}
MemberCouponRepository
List<MemberCoupon> findByMemberIdAndUsedAndUseEndedAtAfter(Long memberId, boolean used, LocalDateTime now);
MemberCoupon
@Entity
@Table(name = "member_coupon")
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class MemberCoupon {
/**
* 모든 쿠폰은 발급 일시부터 7일간 사용할 수 있다.
*/
private static final int COUPON_USABLE_DAYS = 7;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "member_id")
private Long memberId;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "coupon_id")
private Coupon coupon;
@Column(name = "issued_at", columnDefinition = "DATETIME(6)")
private LocalDateTime issuedAt;
@Column(name = "use_ended_at", columnDefinition = "DATETIME(6)")
private LocalDateTime useEndedAt;
@Column(name = "used", columnDefinition = "BOOLEAN")
private boolean used;
@Column(name = "used_at", columnDefinition = "DATETIME(6)")
private LocalDateTime usedAt;
@Column(name = "modified_at", columnDefinition = "DATETIME(6)")
private LocalDateTime modifiedAt;
public static MemberCoupon issue(Long memberId, Coupon coupon) {
coupon.issue();
MemberCoupon memberCoupon = new MemberCoupon();
memberCoupon.memberId = memberId;
memberCoupon.coupon = coupon;
memberCoupon.issuedAt = LocalDateTime.now();
memberCoupon.useEndedAt = memberCoupon.issuedAt.plusDays(COUPON_USABLE_DAYS);
memberCoupon.modifiedAt = LocalDateTime.now();
memberCoupon.used = false;
return memberCoupon;
}
public void use() {
if (this.used) {
throw new IllegalStateException("이미 사용한 쿠폰입니다.");
}
this.coupon.use();
this.used = true;
this.usedAt = LocalDateTime.now();
this.modifiedAt = LocalDateTime.now();
}
}
SQL 쿼리
findByMemberIdAndUsedAndUseEndedAtAfter메서드가 생성하는 쿼리를 살펴보면 아래와 같은 형태를 띈다.
select * from member_coupon mc
where mc.member_id = 1
and mc.used = true
and mc.use_ended_at > '2019-01-12 00:38:50.000000';
show index from member_coupon;
![[Pasted image 20240831185816.png]](/image/for-post/tommy-index/Pasted image 20240831185816.png)
위 쿼리에서 사용되는 member_coupon 테이블의 member_id, used, used_ended_at 컬럼에 대한 인덱스는 아무것도 걸려있지 않은 상태이다.
explain
explain select * from member_coupon mc
where mc.member_id = 1
and mc.used = true
and mc.use_ended_at > '2019-01-12 00:38:50.000000';
| # | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|----|----|--------------|-------|------------|------|----------------|-----|---------|-----|--------|----------|--------------|
| 1 | 1 | SIMPLE | mc | | ALL | | | | | 495482 | 0.33 | Using where |
explain 결과 type는 ALL로 전체 테이블을 스캔했고, 사용된 인덱스는 없다. 또 495,482행을 스캔했는데, filtered된 비율은 0.33%였다. 인덱스가 없으니 필터링 되는 행이 거의 없어서 매우 비효율적이다.
explain analyze
-> Filter: ((mc.used = true) and (mc.member_id = 1) and (mc.use_ended_at > TIMESTAMP'2019-01-12 00:38:50')) (cost=50149 rows=1651) (actual time=122..294 rows=2 loops=1)
-> Table scan on mc (cost=50149 rows=495482) (actual time=0.473..272 rows=500020 loops=1)
Table scan을 사용하고, 비용이 50,149로 매우 비싸다.
성능 테스트 결과
// Table scan
Total request count: 493
Total elapsed time: 100578ms
Average elapsed time: 204ms
인덱스가 없이 매우 비효율적으로 테이블 풀 스캔을 했기 때문에 검증에 실패했다.
인덱스를 사용해서 개선
explain select * from member_coupon mc
where mc.member_id = 1
and mc.used = true
and mc.use_ended_at > '2019-01-12 00:38:50.000000';
조건절에 사용하는 컬럼은 member_id, used, use_ended_at 세 개다. 각 컬럼 마다 고유한 값이 몇개나 있는지 확인해야 한다.
select count(distinct mc.member_id) member_id,
count(distinct mc.used) used,
count(distinct mc.use_ended_at) use_ended_at
from member_coupon mc;
-- member_id : 246670
-- used : 2
-- use_ended_at : 135584
distinct 한 값을 count 한 것과 cardinality가 일치하지 않지만, 그럼에도 use_ended_at 컬럼에 들어있는 값의 범위가 넓다는 것을 확인했다. cardinality가 높은 순서로 보면 member_id, use_ended_at, used 컬럼 순으로 인덱스를 설정하는 것이 좋다. 하지만 쿼리에 사용되는 use_ended_at 컬럼의 조건은 범위 조건으로, use_ended_at 컬럼 뒤에 오는 컬럼은 인덱스를 사용하지 않게 된다.
그럼 member_id, use_ended_at, used 컬럼 순서로 인덱스를 설정하여 cardinality가 높은 member_id, use_ended_at 컬럼만 인덱스를 사용하고 used의 인덱스를 사용하지 않는 것이 좋을까? 아니면 member_id, used, use_ended_at 순서로 설정하여 모든 컬럼의 인덱스를 사용하고 마지막에 index range를 사용하는 것이 좋을까?
Cardinality 고려한 인덱스 컬럼 순서
member_id, use_ended_at, used 순서로 인덱스를 걸면, 두 번째 컬럼인 use_ended_at 컬럼이 범위 조건이기 때문에 해당 범위 내의 모든 데이터를 검색하게 되므로 used 컬럼의 인덱스는 적용되지 않는다. 그러므로 member_id, use_ended_at 두 컬럼만 복합 인덱스로 설정한다.
create index idx_member_use_ended_at on member_coupon (member_id, use_ended_at);
![[Pasted image 20240831204415.png]](/image/for-post/tommy-index/Pasted image 20240831204415.png)
explain
| # | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|-----|----|-------------|-------|------------|-------|--------------------------------|-------------------------|---------|-----|------|----------|--------------------------------|
| 1 | 1 | SIMPLE | mc | | range | idx_member_use_ended_at | idx_member_use_ended_at | 18 | | 22 | 10 | Using index condition; Using where |
인덱스 설정 후 실행 계획을 보면 type는 range로 인덱스 범위 검색을 하고, idx_member_use_ended_at 인덱스를 사용하고 있다. 또 Extra로 Using index condition, Using where가 나타난다.
Using index condition은 인덱스를 사용하여 조건을 평가하고 있다는 의미이다. 즉, 쿼리에서 필터링 조건을 적용하기 위해 인덱스를 사용하고 있으며, 인덱스에서 직접 조건을 확인하여 불필요한 데이터 접근을 줄이는 방식이다.
Using where는 앞에 인덱스를 사용한다고 하더라도, 최종적으로 데이터를 필터링하기 위해 where 절이 필요하다는 것을 의미한다. 즉, 인덱스를 사용해서 일부 데이터를 빠르게 찾았지만, 추가적인 조건이 필요해 데이터베이스에서 직접적으로 추가 필터링이 이루어지는 것이다.
즉, 인덱스가 모든 조건을 만족시키지 못하고, 최종적으로 결과를 위해 추가적인 필터링이 필요하다는 의미이다.
explain analyze
-> Filter: (mc.used = true) (cost=10.2 rows=2.2) (actual time=1.28..1.37 rows=2 loops=1)
-> Index range scan on mc using idx_member_use_ended_at over (member_id = 1 AND '2019-01-12 00:38:50.000000' < use_ended_at), with index condition: ((mc.member_id = 1) and (mc.use_ended_at > TIMESTAMP'2019-01-12 00:38:50')) (cost=10.2 rows=22) (actual time=1.28..1.36 rows=22 loops=1)
필터링 조건으로 used를 확인하고 있고, member_id과 use_ended_at 인덱스를 통해 index range scan을 사용하고 있다. 즉, 인덱스 범위 검색으로 데이터들을 걸러내고 최종적으로 필터링 조건을 확인한다.
Cardinality 고려한 인덱스 적용 후 성능 테스트
Total request count: 14609
Total elapsed time: 100034ms
Average elapsed time: 6ms
used 컬럼을 인덱스 설정하지 않고 where 조건으로 필터링 해도, 앞에서 member_id, use_ended_at 컬럼을 통해 index range scan을 해서 충분히 데이터들을 많이 걸러서 조회 성능이 매우 개선되었다.
커버링 인덱스 적용
이번에는 member_id, used, use_ended_at 컬럼 순서로 인덱스를 설정해서 비교한다.
create index idx_member_used_use_ended_at on member_coupon (member_id, used, use_ended_at);
![[Pasted image 20240831210721.png]](/image/for-post/tommy-index/Pasted image 20240831210721.png)
explain
explain select * from member_coupon mc
where mc.member_id = 1
and mc.used = true
and mc.use_ended_at > '2019-01-12 00:38:50.000000';
| # | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|-----|----|-------------|-------|------------|-------|----------------------------------------|----------------------------|---------|-----|------|----------|---------------------|
| 1 | 1 | SIMPLE | mc | | range | idx_member_use_ended_at, idx_member_used_use_ended_at | idx_member_used_use_ended_at | 20 | | 2 | 100 | Using index condition |
눈여겨 볼 점은 explain으로 실행 계획을 살펴보니 방금 생성한 컬럼 3개로 구성된 복합 인덱스를 사용한다. SQL Optimizer가 판단했을 때 cost가 더 낮아서 효율적이라고 판단한 것 같다. type은 range, Extra는 Using index condition이다.
explain analyze
-> Index range scan on mc using idx_member_used_use_ended_at over (member_id = 1 AND used = 1 AND '2019-01-12 00:38:50.000000' < use_ended_at), with index condition: ((mc.used = true) and (mc.member_id = 1) and (mc.use_ended_at > TIMESTAMP'2019-01-12 00:38:50')) (cost=1.16 rows=2) (actual time=0.036..0.048 rows=2 loops=1)
이전 인덱스 설정과 비교하여 모든 컬럼을 인덱스 범위 검색을 통해 세 가지 조건을 모두 인덱스에서 처리한다는 차이가 있어서 더 효율적으로 동작한다.
커버링 인덱스 성능 테스트
Total request count: 14330
Total elapsed time: 100011ms
Average elapsed time: 6ms
평균 소요시간을 보면 이전 인덱스 설정과 크게 차이나지는 않는다. 하지만 분명히 데이터가 더 많아질 수록 커버링 인덱스를 통해 모든 조건을 인덱스에서 처리하는 것과 추가로 where 조건을 필터링 하는 것의 차이가 크게 작용할 것이다.
5번 문제
월별 쿠폰 할인을 가장 많이 받는 회원을 조회하는 테스트다.
@Test
void 월별_쿠폰_할인을_가장_많이_받은_회원_조회() throws InterruptedException {
AtomicBoolean running = new AtomicBoolean(false);
AtomicInteger requestCount = new AtomicInteger(0);
AtomicLong totalElapsedTime = new AtomicLong(0);
int statusCode = RestAssured.get("/marketing/max-coupon-discount-member?year=2019&month=1").statusCode();
assertThat(statusCode).withFailMessage("월별 쿠폰 할인을 가장 많이 받은 회원 조회 API 호출에 실패했습니다. 테스트 대상 서버가 실행중인지 확인해 주세요.")
.isEqualTo(200);
executeMultipleRequests(running, requestCount, totalElapsedTime, () -> {
RestAssured.get(
"/marketing/max-coupon-discount-member?year=2019&month=" + ThreadLocalRandom.current()
.nextInt(1, 6));
});
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(100L);
}
MarketingController
@GetMapping("/marketing/max-coupon-discount-member")
public MonthlyMemberBenefit findMaxCouponDiscountAmountMemberByMonth(@RequestParam("year") int year,
@RequestParam("month") int month) {
return marketingService.findMaxCouponDiscountAmountMemberByMonth(Year.of(year), Month.of(month));
}
MarketingService
@Transactional(readOnly = true)
public MonthlyMemberBenefit findMaxCouponDiscountAmountMemberByMonth(Year year, Month month) {
return monthlyMemberBenefitRepository.findTopByYearAndMonthOrderByCouponDiscountAmountDesc(year, month)
.orElseThrow(() -> new IllegalArgumentException("해당 월에 쿠폰을 사용한 회원 정보가 없습니다."));
}
MonthlyMemberBenefitRepository
Optional<MonthlyMemberBenefit> findTopByYearAndMonthOrderByCouponDiscountAmountDesc(Year year, Month month);
MonthlyMemberBenefit
@Entity
@Table(name = "monthly_member_benefit")
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class MonthlyMemberBenefit {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;
@Column(name = "member_id")
private Long memberId;
@Column(name = "year", columnDefinition = "SMALLINT")
private Year year;
@Column(name = "month", columnDefinition = "SMALLINT")
private Month month;
@Column(name = "coupon_discount_amount")
private int couponDiscountAmount;
@Column(name = "created_at", columnDefinition = "DATETIME(6)")
private LocalDateTime createdAt;
@Column(name = "modified_at", columnDefinition = "DATETIME(6)")
private LocalDateTime modifiedAt;
public static MonthlyMemberBenefit create(Long memberId, Year year, Month month) {
MonthlyMemberBenefit monthlyMemberBenefit = new MonthlyMemberBenefit();
monthlyMemberBenefit.memberId = memberId;
monthlyMemberBenefit.year = year;
monthlyMemberBenefit.month = month;
monthlyMemberBenefit.couponDiscountAmount = 0;
monthlyMemberBenefit.createdAt = LocalDateTime.now();
monthlyMemberBenefit.modifiedAt = monthlyMemberBenefit.createdAt;
return monthlyMemberBenefit;
}
public void increaseCouponDiscountAmount(int discountAmount) {
couponDiscountAmount += discountAmount;
modifiedAt = LocalDateTime.now();
}
}
MonthlyMemberBenefit 엔티티는 다른 테이블의 외래 키를 가지고 있지 않아서 member_id 컬럼의 PK 인덱스만 있다.
SQL 쿼리 분석
findTopByYearAndMonthOrderByCouponDiscountAmountDesc 메서드를 호출하면 아래와 같은 쿼리가 발생한다.
select * from monthly_member_benefit m
where m.year = 2019
and m.month = 1
order by coupon_discount_amount desc
limit 1;
이 쿼리는 monthly_member_benefit 테이블에 있는 모든 행을 대상으로 year, month가 일치하는 행들 중에서 coupon_discount_amount를 내림차순 하여 가장 큰 값인 행을 하나 반환한다. 즉, coupon_discount_amount 값이 가장 큰 값을 반환하기 위해서 정렬을 해야만 한다. 조건절에서 데이터를 걸렀다고 하더라도 정렬할 데이터가 많은 상황이면 정렬을 수행하면서 데이터베이스에 부담이 되지 않을까?
데이터베이스에서 정렬 vs 애플리케이션에서 정렬
인덱스가 걸려있지 않은 상황에서 정렬을 수행할 경우 애플리케이션으로 데이터를 불러와서 정렬하는 것이 데이터베이스 커넥션 연결을 조금이라도 짧게 하기 때문에 유리할 것이다. 하지만 이 경우에도 데이터베이스에서 애플리케이션으로 많은 양의 데이터를 불러와야 하기 때문에 좋지 않다.
인덱스가 걸려있는 상황이라면, order by, limit으로 데이터를 정렬한 후 값을 가져오는 부분에 대한 정렬을 따로 수행하지 않아도 된다. 왜냐하면 데이터가 저장될 때 이미 정렬된 상태로 인덱스에 저장되기 때문에 데이터에 직접 접근하지 않고 가져오기만 하면 된다. 그래서 애플리케이션에서 정렬하는 것 보다 훨씬 효율적이다.
인덱스 설정
위 쿼리에 사용되는 조건은 year, month, coupon_discount_amount 세 개다.
그렇다면 인덱스 순서는 어떻게 하면 좋을까? 실제 데이터 상황에 따라 다르겠지만, 보편적으로 생각해보면 month는 시간이 지남에 따라 생길 수 있는 최대 범위가 12개이고, year, coupon_discount_amount는 month 보다 범위가 넓을 것이다. 그보다 중요한 점은 coupon_discount_amount로 인덱스를 시작하면 데이터베이스는 이 컬럼을 기준으로 정렬된 데이터를 먼저 검색하게 된다. 그 결과, year, month 조건을 만족하지 않는 데이터도 많이 검색하게 되어 전체 쿼리 성능이 떨어지게 된다. 그래서 이전의 범위 조건과 마찬가지로 인덱스 가장 마지막에 설정하는 것이 좋다.
결론적으로 위 쿼리에 사용하는 인덱스는 year, month, coupon_discount_amount 순으로 설정하는 것이 좋다.
create index idx_year_month_coupon_discount_amount on monthly_member_benefit (year, month, coupon_discount_amount);
explain
explain select * from monthly_member_benefit m
where m.year = 2019
and m.month = 1
order by coupon_discount_amount desc
limit 1;
| # | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|----|----|-------------|-------|------------|------|--------------------------------------------|---------------------------------------|---------|----------------|---------|----------|----------------------|
| 1 | 1 | SIMPLE | m | | ref | idx_year_month_coupon_discount_amount | idx_year_month_coupon_discount_amount | 6 | const,const | 374770 | 100 | Backward index scan |
type은 ref로 인덱스를 사용하여 특정 키를 조회하는 것을 의미한다. filtered는 100%로 모든 행이 조건에 만족한다. Extra는 Backward index scan이다. 이는 인덱스를 역방향으로 스캔하고 있다는 것을 의미한다.
explain analyze
-> Limit: 1 row(s) (cost=43141 rows=1) (actual time=1.42..1.42 rows=1 loops=1)
-> Index lookup on m using idx_year_month_coupon_discount_amount (year=2019, month=1) (reverse) (cost=43141 rows=374770) (actual time=1.42..1.42 rows=1 loops=1)
(year=2019, month=1) (reverse) 이 조건을 기반으로 인덱스를 조회하고 (reverse) 인덱스를 역방향으로 스캔하고 있다. 이는 DESC와 같이 특정한 정렬 조건이 필요할 때 효율적일 수 있다.
인덱스 성능 테스트
Total request count: 12663
Total elapsed time: 100059ms
Average elapsed time: 7ms
인덱스를 설정하여 조회를 효율적으로 수행했다.
댓글남기기