MySQL SELECT 쿼리 성능 개선

최근 회사에서 저장기간이 지난 데이터 삭제 중 slow query가 발생하였다.

해당 운영 환경에는 2천만건의 데이터가 저장되어있고 과거의 데이터를 추출하려다보니 이 있어 문제가 되었다.

 

문제가 발생한 데이터와 쿼리는 아래와 유사한 구조였다.(예시를 위한 테스트 테이블,쿼리)

SELECT
seq, date, subject, content
FROM test_data
WHERE date < {date} AND type IN ({type},{type}...)
LIMIT {offset}, {limit};

 

결합 인덱스 수정

우선 처음으로 select 시 해당 테이블의 인덱스를 정상적으로 사용하지 못하여 문제가 발생한다 생각하였다.

실행 계획을 확인하였을 때 아래와 같았다.

type 인덱스만을 사용한다.

따라서 인덱스를 date와 type 모두 사용하도록 수정해주면 개선되지 않을까? 라는 생각을 했다.

 

해당 테이블에서 select 시 date와 type는 where 절에 항상 같이 들어가기 때문에 둘을 결합 인덱스로 묶어주었다.

결합 인덱스를 사용할 경우 데이터의 분포도가 좋아져(데이터 중복 줄어듦) 성능이 더 개선될 것이라 판단되었다.

더 유니크한 값인 date를 결합 인덱스의 첫번째 컬럼으로 하여 생성하였다.

결합 인덱스 추가

 

결과

둘의 컬럼 모두 인덱스에 사용되었으나 쿼리 실행 시간에 큰 변화는 없었다.

 

order by 절 추가

그러던 중 데이터 검색 시 일반적인 인덱스가 아닌 PK값을 사용하면 성능이 더 개선되지 않을까? 라는 생각이 들었다.

확인해보니 PK의 경우 클러스터링 인덱스로 데이터가 물리적으로 정렬되어있어 성능이 더 잘나온다는 점을 알게되었다.

 

클러스터링 인덱스의 특징
- 한 테이블당 하나만 생성 가능하다.
- MySQL에서 PK가 있다면 PK를 클러스터링 인덱스로 사용한다. 만일 PK가 없을 경우 UNIQUE하면서 NOT NULL인 컬럼을 이러한 컬럼도 없을 경우 보이지 않는 임의의 컬럼을 만들어 클러스터 인덱스로 사용한다.
- 물리적인 실제 데이터를 저장하는데 사용된다.(영어사전의 알파벳처럼 정렬되어있는 형태)
- 위의 특성으로 인하여 조회를 하는데 있어서는 성능이 좋으나 추가/수정/삭제 시에는 성능이 떨어진다.

 

결과

수정 전

수정 후

해당 쿼리의 실행 계획을 확인하니 row값(쿼리를 실행하기 위해 읽어들인 레코드)은 더 컸다.

허나 클러스터링 인덱스를 사용하였기 때문에 조회 속도가 더 빨라져 쿼리 속도는 2/3으로 줄어들었다.

 

그래도 아직 해당 쿼리의 성능은 좋지 않았고(1분 이상)

당시에는 더 이상 좋은 방안이 떠오르지 않아 문제의 운영 환경에서는 데이터 저장 기간을 줄여 slow query가 발생하지 않도록 하였다...

 

OFFSET 제거

그렇게 지내던 중 어느 날 이동욱 개발자님의 페이징 성능 개선하기라는 글을 보게 되었다.

https://jojoldu.tistory.com/528

1. 페이징 성능 개선하기 - No Offset 사용하기

내용을 간략히 정리해보자면 offset이 있는 쿼리의 경우 이전 데이터도 모두 읽어 온 후 그 이후의 값만 잘라서 출력해주는 것이다.

불필요한 앞부분까지 불러온다.

불필요한 데이터까지 읽어오다 보니 쿼리 성능이 저하될 수 밖에 없었고, 이를 해결하기 위해 마지막으로 불러온 데이터의 PK값을 저장해 둔 후 다음 쿼리를 실행할 때 해당 PK값 이후부터 limit만큼 불러오도록아래와 같이 수정해주었다.

SELECT
seq, date, subject, content
FROM test_data
WHERE 
seq > {start} AND #추가된 부분
date < {date} AND type IN ({type},{type}...)
LIMIT {limit}; #offset 제거

 

결과

해당 쿼리의 수행 시간은 1초 이하로 줄어들었다.

허나 이미 해당 운영 환경에서는 데이터 저장기간을 변경하여 운영 환경에서의 결과는 확인할 수 없었다...

 

그렇게 지내던 중 마침 다른 운영환경에서 동일한 문제가 발생하여 실제로 반영하여 결과를 확인할 수 있었다.

해당 운영 환경도 데이터 저장기간이 매우 길었고 패치 후 작업 시간 확인 결과 약 20시간 걸리던 작업이 3분 이내로 개선되었다.