안녕하세요. cosign에서 back-end 개발을 하고 있는 Ian입니다.
오늘은 MariaDB에서 결합 인덱스 활용하여 데이터베이스 성능 최적화에 대해 적어보고자 합니다.
API를 개발하면서 SQL문을 작성하다 보면 쿼리의 성능이 저조할 때가 있습니다. 성능을 올릴 수 있는 방법은 무엇이 있을까 하는 그 정답은 멀리 있지 않았습니다. 바로 인덱스에 있었습니다.
이번 글에서는 데이터베이스 성능 최적화에 대한 경험을 공유하고자 글을 작성했습니다. 회사에서 차트 위에 패턴을 뿌려주는 API를 개발하면서 발생했던 문제와 그 해결 방법에 대해 소개하고자 합니다.
아래 사진은 해당 API를 만들었을 때 VIEW에 보여지는 사진을 가져온 것입니다.
문제 발생
차트 위에 패턴을 표시하기 위해서는 각각의 포인트와 그래프 그 외의 데이터들이 여러 테이블에 분산 되어 있었고, 특히 패턴의 각 포인트를 나타내는 테이블에는 약 60만개의 데이터가 저장되어 있었습니다. 초기에는 단순하게 인덱스 설정도 없이 JOIN 쿼리를 사용하여 데이터를 가져왔으나, 데이터의 양이 커짐에 따라 쿼리의 실행 시간이 점점 증가하는 문제가 발생 했습니다.
위 이미지는 결합 인덱스를 적용하기 전 요청에 대한 응답 시간입니다. 점점 늘어나더니 요청에 대한 응답이 2초 가까이가 걸리더군요. 2초에 가까운 시간이면 유저들은 상당히 느리다고 판단할 시간입니다. 그러기 위해 빠르게 성능을 개선 시켜야 했습니다.
때문에, JOIN 연산의 최적화, 검색 속도 향상에 최적화 되어있는 결합 인덱스를 사용하고자 결심하게 되었습니다.
결합 인덱스란?
두 개 이상의 컬럼을 합쳐서 인덱스를 구성하는 것을 결합 인덱스라고 합니다. AND 조건으로 검색되는 경우 성능에 중요한 역할을 하게 됩니다. 하지만 자칫 결합 인덱스를 잘못 배열하여 결합 인덱스를 사용하게 되면 인덱스를 탈 확률이 낮아지게 됩니다. 그렇기 때문에 인덱스를 설정하는 것은 매우 신중하게 진행해야 합니다.
결합 인덱스를 구성하기 위해서는 WHERE문에서 사용되는 컬럼의 연산자를 주목할 필요가 있다고 생각합니다. 보통 결합 인덱스는 아래와 같은 조건에서 자주 생성합니다.
- WHERE 조건으로 사용되는 컬럼
- JOIN 절에서 ON절에 사용되는 컬럼
- ORDER BY절에서 사용되는 컬럼
결합 인덱스에 대한 자료를 찾던 중 귀중한 글을 보게 되었습니다.
점 조건+점 조건 – 두 조건에 의해 처리 범위 감소
점 조건+선분 조건 – 두 조건에 의해 처리 범위 감소
선분 조건+선분 조건 – 앞의 선분 조건에 의해 처리 범위 감소
선분 조건+점 조건 – 앞의 선분 조건에 의해서만 처리 범위 감소
위 문서를 통해 작업을 하게 될 때 최소의 처리 범위를 보장 받기 위해서는 4번 케이스와 같이 점 조건(=,IN) 앞에 **선분 조건(=, IN을 제외한 연산자)**은 존재하면 안된다는 것까지 알게 되었습니다.
그럼 우리 서비스에도 적용 해봐야겠죠?
인덱스를 적용하기 전인 레거시를 단순하게 재구성한 케이스입니다.
select p.*
from pattern p
left join crypto c on p.crypto_id = c.crypto_id
left join digital_assets d on c.digital_assets_id = d.digital_assets_id
...
where (p.start_point_time between {gte} and {lte} or p.end_point_time between {gte} and {lte})
and p.crypto_id = {crypto_id}
and p.interval = {interval}
...
order by p.end_point_time desc, p.start_point_time desc
위 SQL문을 보면서 저는 두 가지의 문제점을 찾게 되었습니다.
- LEFT JOIN 사용, INNER JOIN과 LEFT JOIN의 성능 차이는 다양한 요인에 따라 달라질 수 있지만 일반적으로 LEFT JOIN 보다 INNER JOIN이 더 빠르게 실행되는 경우가 많습니다. :INNER JOIN은 두 테이블에서 조건이 일치하는 행만 가져오기 때문에 탐색 범위가 줄어듭니다. 그렇기 때문에 INNER JOIN은 인덱스를 더 효과적으로 이용합니다. 반면, LEFT JOIN은 왼쪽 테이블의 모든 행을 매칭 여부에 상관없이 모든 결과를 반환하므로 탐색 범위가 더 넓습니다. 또한, 데이터베이스 엔진 시스템은 INNER JOIN에 더 최적화 되어 빠릅니다. 하지만 LEFT JOIN은 INNER JOIN으로 가져올 수 없는 누락된 데이터를 가져올 수 있는 장점이 있습니다. 저희가 수집한 데이터 기반으로 JOIN 할 시 누락된 데이터가 없는 것을 인지한 상태였습니다. 그렇기 때문에 INNER JOIN으로 쿼리를 수정해야 했습니다.
- 점 조건(=,IN)보다 선분 조건(=, IN을 제외한 연산자)이 더 앞에 있어 두 개의 조건에 의해 처리 범위가 크게 감소하지 않습니다. 인덱스를 설정하기에 앞서 분포도라는 개념을 알면 좋습니다. ‘분포도가 좋다’란 동일한 데이터가 매우 적다는 것을 의미합니다. 흔히 분포도라는 것은 점조건에서 쓰이는(=,IN)으로 조회할 경우를 의미합니다. 또한 특정 컬럼의 데이터가 아무리 분포도가 좋아도 선분조건으로(LIKE, BETWEEN)으로 탐색을 시작하면 좋은 분포도가 될 수 없습니다. 그렇기 때문에 점조건을 먼저 탐색하는 것이 좋습니다. 또한, 결합 인덱스를 설정하기에 분포도가 아닌 연산자가 중요하다는 것을 알아야 합니다.
결합 인덱스 적용
결합 인덱스를 적용하기 앞서 pattern 테이블에서 자주 사용하는 컬럼들을 파악 해보았습니다.
- WHERE 절에서 사용하는 컬럼
- 점 조건으로 crypto_id, interval 컬럼을 여러 sql문에서도 자주 사용
- 선분 조건으로 start_point_time, end_point_time 컬럼을 여러 sql문에서도 자주 사용
- JOIN 절에서 사용하는 컬럼
- crypto_id컬럼을 가지고 crypto_currency 테이블과 JOIN하는데 사용
- ORDER BY에서 사용하는 컬럼
- end_point_time, start_point_time을 가지고 order by에서 자주 정렬 조건으로 사용
최종적으로 결합 인덱스를 아래와 같이 구성하였습니다.
crypto_id, interval, start_point_time, end_point_time
수정한 SQL문
select p.*
from pattern p
inner join crypto c on p.crypto_id = c.crypto_id
inner join digital_assets d on c.digital_assets_id = d.digital_assets_id
...
where p.crypto_id = {crypto_id}
and p.interval = {interval}
and (p.start_point_time between {gte} and {lte} or p.end_point_time between {gte} and {lte})
...
order by p.end_point_time desc, p.start_point_time desc
결합 인덱스 적용 이후
마치며…
성능을 개선하기 위한 방법은 무수히 많을 것입니다. 하지만 결합 인덱스를 선택한 방법은 간단하지만 인덱스 설정을 통해 검색 속도가 향상되는 강력함에 끌렸던 것 같습니다. 올바른 도구와 전략을 사용하면 많은 이점을 얻을 수 있지만 반대로 잘못된 설정을 하게 된다면 오히려 성능이 낮아질 수 있기에 신중하게 선택해야만 했습니다.
오늘도 저는 데이터베이스의 세상은 알면 알수록 끝이 없다는 것을 몸소 느끼게 되네요. 잘못된 정보가 있다면 댓글로 알려주시면 감사하겠습니다.😊
참고 자료