인덱싱을 적용한 tps 속도 개선

거래성사 목록 쿼리에 인덱스를 적용하여 tps 속도를 높일려고 한다.

거래성사 목록 조회 쿼리

explain select
    completere0_.complete_request_id as col_0_0_,
    completere0_.from_card as col_1_0_,
    item3_.item_name as col_2_0_,
    card2_.thumbnail as col_3_0_,
    item3_.price_range as col_4_0_,
    completere0_.to_card as col_5_0_,
    item8_.item_name as col_6_0_,
    card7_.thumbnail as col_7_0_,
    item8_.price_range as col_8_0_,
    completere0_.created_date as col_9_0_,
    completere0_.modified_date as col_10_0_
from
    complete_requests completere0_
        left outer join
    cards card1_
    on (
            card1_.card_id=completere0_.from_card
        ) cross
            join
    cards card2_ cross
            join
    items item3_ cross
            join
    cards card7_ cross
            join
    items item8_
where
        completere0_.from_card=card2_.card_id
  and card2_.item_id=item3_.item_id
  and completere0_.to_card=card7_.card_id
  and card7_.item_id=item8_.item_id
  and completere0_.complete_request_status='ACCEPTED'
order by
    completere0_.modified_date desc,
    completere0_.complete_request_id desc limit 5

jmeter

1. 쓰레드 그룹 설정값

2. 요약 보고서

explain 실행 결과

우리가 주의깊게 봐야할 부분은 extra이다.

extra에서 가장 피해야할 부분이

using temprorary(가상 테이블 생성), using filesort(정렬) 이다.

extra에 using filesort가 있으니 해결해보도록 하자.

Using Filesort

MySQL의 정렬 처리는 크게 2가지로 나뉜다.

• Index를 이용한 정렬
• File Sort ("Using filesort")

MySQL은 인덱스를 통해 정렬을 할 수 없다면 FileSort를 사용한다.

File Sort는 레코드가 많아질수록 쿼리의 응답 속도가 현저히 떨어지게 된다.

인덱스를 사용한 정렬을 수행해보자.

다중 컬럼 인덱스 정렬

1. 다중 컬럼 인덱스에서 각 인덱스를 구성하는 컬럼은 첫번째 컬럼부터 차례대로 정렬되고, N번째 인덱스 컬럼은 N-1번째 인덱스의 정렬에 의존해서 정렬된다.
2. 다중 컬럼 인덱스 내 컬럼의 순서는 성능에 큰 영향을 미칠 수 있으므로 어떤 방식의 조회가 자주 일어나는지, 필터링이 어떻게 될지 고민해야한다.
3. 다중 컬럼 인덱스에서 선행하는 컬럼에 대한 조건을 where 문에 포함하지 않는다면 인덱스를 사용할 수 없다.

컬럼의 순서를 정해보자

스캔할 row수가 적은 컬럼이 우선순위를 가져야한다.

where절에 있는 컬럼은 from_card, toCard, complete_request_status, modifired_date, created_date이다.

기본적으로 ACCEPTED 상태인 row의 수가 많을 것이므로 complete_request_status를 제일 후순위로 설정한다.

인덱스 생성

create index idx_all_complete_request_status on complete_requests(modified_date,created_date,from_card,to_card,complete_request_status);
create index idx_from_card_to_card_complete_request_status on complete_requests(from_card,to_card,complete_request_status);

Ref

• https://jaehoney.tistory.com/192 ****
• https://studyandwrite.tistory.com/553