[MySQL] 인덱스를 활용한 쿼리 튜닝

개요

JPA를 사용하여 쿼리를 사용하다보면 의도하지 않은 인덱스를 사용하거나 못하는 경우가 있다. 회사에서 이런 경험을 하고 튜닝했던 과정을 소개하려고 한다.

배경

아래는 내가 겪었던 상황과 비슷한 느낌을 살려서 가정을 해보겠다.

SELECT *
  FROM student s
 WHERE s.created_at BETWEEN '2022-06-03' AND '2022-09-03'
   AND s.grade = '1학년'
   AND s.status = '재학중'
ORDER BY s.created_at DESC

student : 학생 테이블
created_at : 학생 정보가 생성된 날짜 컬럼
grade : 학생의 학년 정보 컬럼
- 1학년
- 2학년
- 3학년
- 4학년
status : 학생의 재학 상태
- 입학전
- 재학중
- 퇴학
- 졸업

테이블의 row 수가 많아지면 전체적으로 해당하는 조건에 맞는 row를 찾느라 시간이 오래걸린다. 이를 해결하기 위해 많이 사용하는 것이 인덱스다

문제

물론, 내가 처한 상황에서 인덱스는 있었고 예를 들어 아래와 같이 인덱스가 생성되어 있었다.

인덱스 A : created_at 1개의 컬럼으로 만든 단일 인덱스
인덱스 B : grade, status, created_at 3개의 컬럼으로 만든 결합 인덱스

where 절에 3개의 조건을 다 넣었기 때문에 당연히 인덱스 B를 활용하여 검색을 할줄 알았지만 DBMS가 상황에 따라 다른 인덱스를 사용한다는 것을 확인하였다.

첫번째로 다른 상황은 데이터의 양이었다. 데이터의 양이 적을 때는 인덱스 B 사용했지만 데이터가 많아지자 인덱스 A를 사용하는 것을 확인하였다.

두번째는 데이터의 분포였다. 예를 들어, 조건에 들어가는 학생의 학년 정보는 같은 경우 1,2,3,4학년이 거의 골고루 분포되어 있기 때문에 1학년을 넣을 때나 2학년을 넣을 때 큰 차이를 보이지 않았지만 재학 상태와 같이 졸업이 압도적으로 많거나 입학전처럼 적은 경우에는 인덱스를 다르게 선택하는 것을 확인하였다.

해결 방법

이를 해결하기 위해 사용한 방법은 아래와 같이 3가지 방법이 있었다.

MySQL의 인덱스 조정
ORDER BY절 추가
여러개의 단일 인덱스 사용

MySQL 인덱스 조정

MySQL에서는 아래와 같이 쿼리에서 사용할 인덱스를 조정할 수 있는 방법이 있다.

USE INDEX : 해당 인덱스를 사용하도록 추천해준다. (추천이기 때문에 적용이 안될 수도 있다.)
FORCE INDEX : 해당 인덱스를 사용하도록 강제한다.
INGNORE INDEX : 해당 인덱스를 사용하지 않도록 강제한다.

SELECT *
  FROM student s USE_INDEX(인덱스 B)
 WHERE s.created_at BETWEEN '2022-06-03' AND '2022-09-03'
   AND s.grade = '1학년'
   AND s.status = '재학중'
ORDER BY s.created_at DESC

좀 더 자세한 사용법은 블로그를 확인해보길 바란다.

ORDER BY절 추가

강제로 ORDER BY절에 조건절에 사용되는 컬럼을 추가하였다.

SELECT *
  FROM student s
 WHERE s.created_at BETWEEN '2022-06-03' AND '2022-09-03'
   AND s.grade = '1학년'
   AND s.status = '재학중'
ORDER BY s.created_at DESC, s.status

위와 같이 사용하였더니 인덱스B를 사용하였다. 하지만 이 방법을 추천하지는 못할거 같다. 일단, 내 상황의 경우 인덱스 순서가 맞지 않기 때문에 인덱스를 통한 정렬이 아니라 진짜 정렬 연산이 들어가 file sorting이 일어나서 성능이 떨어졌다. 그닥 좋은 방법은 아니었던 것으로 보인다.

여러개의 단일 인덱스 사용

이 방법이 가능 성능이 좋았는데 쿼리를 아래와 같이 작성하였다.

SELECT *
  FROM student s
 WHERE s.id IN (SELECT s2.id
                  FROM student s2
                 WHERE s2.created_at BETWEEN '2022-06-03' AND '2022-09-03'
                ORDER BY s.created_at DESC)
   AND s.grade = '1학년'
   AND s.status = '재학중';

하나의 결합 인덱스를 사용하기보다 위와 같이 인덱스를 분할하여 사용할 수 있도록 하는 것이 더 효과적으로 성능을 발휘하였다. 이 쿼리가 왜 빨라지는지에 대한 정확한 원인은 모르겠지만 추측되는 원인은 다음과 같다.

MySQL은 between, like, <,>등의 범위 조건에 사용된 컬럼은 인덱스를 타지만 그 뒤의 인덱스 컬럼들은 인덱스로 사용되지 않는다. 블로그(4. 인덱스 조회시 주의 사항 참고)
그렇기 때문에 범위 인덱스로 먼저 필터링하고 PK를 이용해 인덱스를 한번 더 사용하기 때문에 오히려 더 빨라는 것으로 파악이 된다.

요약

DBMS는 데이터의 양, 분포 그리고 쿼리의 WHERE 절, ORDER BY절 등을 통해 어떤 인덱스를 사용할지 선택한다.
MySQL에는 인덱스 사용을 조정하는 USE INDEX, FORCE INDEX, IGNORE INDEX등이 있다.
꼭 결합인덱스를 사용하는 것이 답은 아니고 상황에 따라 단일 인덱스를 여러번 사용하게 하는 것이 더 나을 수 있다.