성능 개선 2편: Index 설계하기 (with Explain analyze, Explain)

인덱스 없이 조회, 얼마나 걸릴까?

데이터 개수

개선이 시급한 쿼리는 거래소/경매장 | 전체조회/인기아이템 이렇게 4개다.

총 4개 쿼리의 실행속도를 우선 알아보자.

hibernate 2차캐시는 사용하지 않고 있다.(default설정으로 사용 중)

거래소 메인페이지 /markets/main : 36초, 40초

경매장 메인페이지 /auctions/main : 21초, 25초

 

거래소 인기아이템 /markets/populars : 6시에 보낸 요청이 6시 31분까지 조회되지 않는다.

경매장 인기아이템 /auctions/populars : 1분 30초, 1분 38초

거래소 인기아이템에 대한 조회는 해당 거래소 아이템의 trade내역까지 풀스캔 해야 해서 엄청나게 오래걸리다 못해 컴퓨터가 조회하기를 포기중이다...

 

 

인덱스 스코프 정하기 - 추정

우선 생각해볼 수 있는 건, 인덱스를 사용하면 삽입 시에 오버헤드가 발생한다는 점이다.

따라서 insert가 빈번한 trade, bid에는 인덱스를 최대한 지양해야된다.

또한 bid는 auction과 1대1매핑이라 비교적 괜찮은데, trade와 market은 일대다 관계라서 조회속도가 말도안되게 느리다.

그래서 삽입 오버헤드가 걱정되긴 해도 trade에 대한 인덱스는 필연적으로 들어가야 한다고 생각한다.

 

일단 auction, market, trade에만 사용하는 것으로 쿼리 검색 조건을 정리하면,

 

1. Auction - Bid

findPopularAuctionItems와 findAllActionItems에서 auction.createdAt>=startDate 조건을 쓰고 있으니까, created_at에 인덱스를 추가한다면 b+tree의 장점을 극대화 할 수 있다.

인기 경매 항목을 가져올 때 bidderCount를 기준으로 내림차순 정렬하고 있다.

findAllAuctionsItems에서 sortBy값으로 dueDate나 startPrice를 사용 중이다.

 

2. Market - Trade

findPopularMarketItems에서 trade.createdAt으로 날짜 범위검색을 하고있으니 인덱스 효과가 굉장히 클거다.

 

3. Item

item.name으로 검색을 하고 있으니, full-text index사용을 고려해봐야 한다.

 

총 6개정도의 인덱스를 고려해볼 수 있다. 이걸 하나하나 적용하면서 포스트맨으로 분석하고 노가다해야될까?

안된다. 귀찮기도 하고, 제대로 된 분석이 아니다. 

"EXPLAIN ANALYZE"과 "EXPLAIN"을 이용해 분석해보자

 

 

인덱스 스코프 정하기 - EXPLAIN ANALYZE

https://velog.io/@wisepine/MySQL-%EC%8A%AC%EB%A1%9C%EC%9A%B0%EC%BF%BC%EB%A6%AC-%EC%9E%A1%EB%8A%94-%EB%AA%85%EB%A0%B9%EC%96%B4-EXPLAIN-ANALIZE-%ED%95%B4%EC%84%9D%EB%B2%95

MySQL :: 슬로우쿼리 잡는 명령어, ‘EXPLAIN ANALYZE’ 해석법

EXPLAIN ANALYZE란, Mysql이 제공하는 쿼리 프로파일링 도구다.

쿼리 실행 중 Mysql이 어디서 시간을 많이 썼는지, 그 이유가 뭔지 분석할 수 있다.

쿼리를 실제 실행하며 그 중 얻는 모든 시간과 행수를 측정한다. 결과적으로 실행 전 계획과 실제 측정치를 같이 출력한다.

 

EXPLAIN과 비교하자면, 실행결과가 나와야 분석할 수 있기 때문에 거래소 인기아이템처럼 도무지 결과가 안 나오는 쿼리는 EXPLAIN으로 분석해야 할 거다.

 

방법은 간단하다. 실행하려는 쿼리문 맨 위에 EXPLAIN ANALYZE만 쓰면 된다. EXPLAIN도 똑같이 실행가능하다.

모르고 주석처리 하려고 그냥 쓴건데 #제목 쓰니까 콘솔이름이 "제목"으로 변해서 편하더라

 

 

1. 경매장 - 전체 아이템 불러오기

비슷한 이슈끼리 같은 색으로 묶어봤따

1. LIKE '%검색어%' 때문에 인덱스를 못 타고, item 테이블 전체를 스캔하면서 필터링하는 풀테이블스캔이 일어나고있다

2. trade.id.countDistinct()를 구하기 위해 각 row마다 서브쿼리를 돌리는 구조로 실제 실행에선 nested loop가 많이 일어나 성능 부담이 크다

3. MIN(m.price), SUM(m.amount) 등을 그룹 함수로 구하고 정렬까지 해서 Mysql이 내부적으로 temporary, filesort로 처리 중이다. 각각 임시 테이블 생성, 임시 파일을 생성을 하다보니 정렬 과정이 추가 비용을 일으키고 있다

4. market.status != ('COMPLETED', 'CANCELLED') 조건이 있지만, 현재 인덱스 사용은 item_id 뿐이고 status와의 복합 인덱스가 없어 범위가 한 번에 좁혀지지 않는다

 

actual time을 보면 Nested loop에서 가장 많은 시간을 사용하고 있는데

그 이유는 행 단위로 다른테이블을 반복 조회하기 때문이다.

mysql의 기본 조인 방식은 대부분 nested loop라서 한 테이블에서 나온 여러 row 각각에 대해 다른 테이블을 인덱스 룩업이든, 풀스캔이든 반복해서 접근해야 한다.

 

건수가 수천에서 수백만으로 불어나면 반복 횟수도 기하급수적으로 커져서 쿼리의 대부분 시간을 조인 과정에 쏟아붓게 된다.

특히나 지금처럼 like나 status not in같은 조건으로 테이블을 풀스캔 할 때나,

또다른 테이블과 row - by - row 서브쿼리로 조인을 반복하거나,

그와중에 집계함수까지 써서 매번 계산도 해야되면

 

무수~~~~~~~~~~~~~~~히 많은 row를 다뤄야 하니까 실제 실행 시간이 길어질 수밖예 없고, 결국 이 단계가 쿼리에서 가장 오래 걸리는 핵심 병목 구간으로 드러나게 되는 것이다.

 

 

 

2. 거래소 - 전체 아이템 불러오기

1. 이 또한 item을 풀스캔하고있다

2. i -> auction-> bid 순으로 조인하며, groupBy를 위한 temporary와 filesort까지 동원되고, 이를 nested loop join이라 한다. groupBy + orderBy가 복잡하면 mysql이 내부적으로 임시 테이블을 만들고 그 위에서 sort를 수행해 성능 비용이 커진다. EXPLAIN을 보면 a와 b 조인 단계에서 row 수가 누적돼 꽤 많은 루프를 돌고있다

3. 정렬 조건이 case를 이용한 동적인 구문이라 인덱스 정렬이 불가능하고 결과적으로 filesort가 일어난다

 

 

3. 경매장 - 인기 아이템 불러오기

1. 주된 병목은 auction 테이블을 대량으로 읽어온 뒤 temporary와 filesort를 하는 것이다. where 조건과 orderBy를 인덱스로 처리하지 못해서 발생하였다. 결과적으로 풀 테이블 스캔 + 임시 테이블 생성 + 파일sort과정을 거치게 된다.

2. (status, created_at, bidder_count) 복합 인덱스를 고려하고, not in 대신 in으로 변환해 인덱스 범위 스캔을 유도하거나,

정렬 칼럼을 인덱스로 처리하여 filesort 없이 빠르게 상위 200건만 가져오도록 개선할 수 있을 것이다.

 

 

4. 거래소 - 인기 아이템 불러오기

1. EXPLAIN 결과를 보면, trade 테이블을 풀 스캔하며, 그 뒤 market, item 테이블을 eq_ref로 조인하고 있다.

2. group by + order by를 처리하기 위해 temporary와 filesort가 발생해서 모든 데이터를 훑은 뒤 정렬/그룹화 후 마지막에 limit 200을 적용하고 있는 상태다.

 

 

인덱스 최종 설계

1. itemname의 FULLTEXT index - 모든 item에 대해 풀테이블스캔이 일어나기 때문. b+tree로는 불가능함

 

2. market.status index - status not in() 때문에 마찬가지로 풀스캔이 일어나고있음.

"최근 거래 내역"과 "상태" 가 where절에 함께 들어가는 경우가 많기에 created_at과 함께 복합인덱스로 사용하는 것도 고려해볼만 함.

또한 not in()보다는 in()을 사용하는게 좋음. not in은 원천적으로 인덱스가 어렵다.

 

3. MIN(market.price) 때문에 temporary + filesort가 빈번히 일어나므로, item_id별 min_price를 캐싱하는 방안도 고려해볼만 함.

 

4. bidder_count 정렬 - 인기아이템 조회 limit200조건을 위해 풀스캔이 일어남

 

5. trade_count를 인덱스를 따로 만들고, 주기적으로 계산(스케줄러 or 트리거)하여 limit200조건의 조회속도를 향상시킬 수 있음

trade_count같은 집계결과를 별도의 테이블을 만들어 유지한다면 훨씬 효율적일 것임

 

 

다음 글에서 풀텍스트 제외하고

복합 인덱스, not in->in, 집계 테이블, 순수 정렬 인덱스(bidder_count) 등

을 적용해보고 처음과 비교해보자.