인덱스를 사용해서 쿼리 성능 개선시키기

뱅크셀러드 같은 예산 관리 애플리케이션 API를 개발하고 있는데, 여러 기능 중 이번 달에 설정할 예산을 추천해주는 기능을 개발하고 있었다. 

 

예시

 

사용자가 예산금액을 입력하고 서버에 보내면, 서버는 이번 달에 이미 예산을 설정한 기존 사용자들을 통계내서 각 카테고리별 예산 금액으로 추천해주는 흐름이다. 저장돼있는 예산을 카테고리별로 그룹화한 후 각 그룹의 예산 금액을 더한 값에서 총 예산 금액의 합을 나누면 평균 비율을 계산할 수 있다. 

 

네이티브 쿼리로 작성된 로직은 다음과 같다. 

select SUM(amount)
from budget b

 

먼저 모든 예산 금액의 합을 구한다. 이렇게 구한 총 금액과 사용자가 이번달에 설정할 예산금액을 가지고 각 카테고리별 예산 금액을 추천한다. 추천하는 쿼리는 다음과 같다.

-- 사용자가 요청한 예산 금액: 1000원
-- 총 예산 금액: 200000원
select c.name, SUM(b.amount) / 200000 * 1000
from budget b
join category c on b.category_id = c.id
group by b.category_id;

 

위에서 말했듯이 카테고리를 기준으로 그룹화한 후 각 그룹의 예산 금액을 더한 값에서 총 예산 금액을 나누는 것을 볼 수 있다. 그다음 사용자가 요청한 예산금액을 곱함으로써 원하는 응답이 나온다.

 

이 때 쿼리를 짜면서 궁금한 점이 들었다. 현재 모든 유저의 예산을 가지고 처리하는데, 만약 유저 수가 많아지면 많아질수록 쿼리가 응답하는 속도는 떨어질 것 같았다.  그래서 가상의 시나리오를 짜봤다.

 

만약 사용자 50만명이 우리 서비스를 이용중이고, 각 유저당 카테고리별 예산을 8개씩 설정했다고 가정한다. 이 상태에서 추천기능을 사용할 때 과연 응답속도는 얼마나 나올까? 사용자가 각 8개의 예산을 설정했다면 총 400만개의 예산이 데이터베이스에 저장되어있을 것이다. 응답속도를 알기 위해 쿼리 프로시저로 유저 데이터 50만개와 400만개의 예산 데이터를 넣고 테스트해봤다.

 

먼저 예산금액의 합을 구했다. 

select SUM(amount)
from budget b

 

응답값은 50000000000이 나왔고, 응답 속도는 794ms가 나온것을 볼 수 있었다.

 

다음으로 추천하는 쿼리를 날려보자. 사용자가 요청한 예산금액은 100000원으로 가정했다.

 

select c.name, SUM(b.amount) / 50000000000 * 100000
from budget b
join category c on b.category_id = c.id
group by b.category_id

 

속도는 13.213ms가 나온것을 볼 수 있다. 

 

API가 응답하는데 13초가 걸리면 당장 성능을 개선해야 할 정도로 느리다. 또 한가지 문제점이 있는데, 지금은 사용자 50만명이 예산을 8개씩 설정했다고 시나리오를 가정했다. 여기서 중요한 점은 이번 달 예산만으로 계산한 것이다. 만약 이번 달 예산으로만 계산하는게 아니라 지금까지 설정했던 예산들을 전부 계산하라고 한다면,  400만은 가볍게 뛰어넘는 예산 데이터 수 때문에 응답시간이 몇배는 더 지연될 것이다. 

 

따라서 쿼리 속도를 개선시켜보자. 먼저 옵티마이저가 생성한 SQL 처리경로인 실행계획을 보면서 어떤부분을 개선시켜야할지 파악해봤다. 실행계획을 보는 이유는 실행계획에 데이터를 어떤 식으로 가져오는지에 대한 경로를 볼 수 있기 때문이다. 

 

실제로 요청한 SQL은 DB Server를 타고 Server가 이해할 수 있는 수준으로 잘게 쪼개진 다음 옵티마이저가 효율적으로 데이터를 꺼낼 경로를 판단하여 생성한다. 그리고 생성된 처리경로를 토대로 Storage Engine(ex: InnoDB)에서 메모리에 접근하거나 디스크에 접근하여 데이터를 꺼낸다. 

 

실행계획을 보기전 참고할 ERD는 다음과 같다.

 

실행계획 결과는 다음과 같다.

 

위 사진에서 type컬럼과 Extra컬럼을 중심으로 확인했다. 먼저 type이다. type은 테이블의 데이터를 접근한 방식을 나타낸다.

 

 

카테고리 테이블에서는 테이블의 모든 데이터를 스캔했다고 나온다. 일반적으론 풀 스캔을 때려버리면 성능에 문제가 있지만 카테고리의 수가 8개 밖에 되지 않기 때문에 옵티마이저가 최적의 경로를 찾은거라고 봤다. 

 

예산 테이블에서는 인덱스를 사용했다고 나와있다. 아래 사진을 보면 사용된 인덱스를 나타내는 key컬럼에서 카테고리 id인 외래키를 사용하고 있다고 나와있다. (show index from budget 명령어로 인덱스를 보면 똑같은 key 이름이 인덱스로 등록되어있는 것을 볼 수 있다.)

 

 

다음으로 Extra 컬럼을 확인했다. Extra 컬럼은 이름 그대로 추가 정보를 보여준다. 

 

 

카테고리 테이블은 쿼리 실행중에 임시 테이블을 사용했다고 나왔다. 임시 테이블이란 Mysql이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그룹핑할 때 내부적으로 사용하는 테이블을 말한다. 여기서 중요한 점은 임시 테이블이 처음에는 메모리에 생성되었다가, 크기가 커지면 디스크로 옮겨진다. 따라서 대용량의 레코드가 임시 테이블을 사용하여 쿼리를 실행한다면 디스크 I/O 비용으로 성능 저하의 원인이 된다. 

 

하지만 카테고리 테이블의 데이터 수는 8개밖에 되지 않는다. 따라서 현재까지는 디스크에 임시테이블이 생성될 일은 없기 때문에 Using temporary에 대한 처리는 하지 않는다. NULL은 일반적인 쿼리를 뜻한다.

 

여기까지 실행계획에 대해서 중요한 부분을 분석해봤다. 가장 눈에 띄는 것은 예산 테이블의 Extra부분인데, 인덱스를 제대로 사용하지 못한다고 생각했다. 

 

출처: jojoldu

 

위 사진처럼 Non Clustered Key로 등록된 category_id로 그룹화에 사용되었지만, select절의 SUM(amount)를 실행하기 위해 실제 데이터에 접근해서 amount의 값을 하나하나 더하는 것을 Extra를 통해 알 수 있었다. 

 

그럼 amount를 인덱스로 적용하면 간단히 끝날 문제네? 하고 적용하면 amount 인덱스가 사용되지 않는 것을 볼 수 있다. 그 이유는 옵티마이저와 관련이 있다. 현재 amount를 집계함수를 통해서 조회하고 있는데, 옵티마이저에서 집계함수 특성 상 전체 스캔을 해야 하기 때문에 인덱스를 걸어도 풀 스캔을 했다고 생각했다. 즉 인덱스를 걸어도 인덱스를 사용하지 않기 때문에 의미가 없는 것이다. 

 

대신 category_id와 amount를 하나의 인덱스로 묶어버리면 된다. 위에서도 말했듯이, 실제 데이터에 접근하지 않는 것이 중요하다. category_id와 amount를 묶으면 쿼리에서 필요로 하는 데이터가 인덱스 자체에 포함된다. 이를 커버링 인덱스라고 한다. 커버링 인덱스는 실제 데이터에 접근하지 않고 인덱스 스캔만으로 결과를 얻을 수 있어 I/O작업을 줄이고 성능을 개선시킬 수 있다. 

 

따라서 category_id와 amount를 하나의 복합 인덱스로 만들어서 커버링 인덱스로 조회할 수 있게 만들었다.

create index idx_budget_test on budget(category_id, amount);

 

이렇게 인덱스를 적용 후 실행하면?

 

 

커버링 인덱스로 조회가 된다. 

 

 

속도도 13.213에서 1.264초로 약 13배가 개선되었다.