🎳

인덱스 최대 활용하기 (where + group by + order by)

생성일

2023/01/22

태그

MySQL

Spring

목차

배경

저희 모락에서는 역할 정하기 기능 중 역할 기록 조회 API 에서 있던 한방 쿼리는 서브 쿼리가 2개가 발생하여 성능상 비효율적이었고, 이를 4개의 쿼리로 나누어 성능을 개선하였습니다. 이 4개의 쿼리 중 다른 쿼리들에 비해 유난히 오래 걸리는 쿼리가 있었는데요, 바로 조건절, 그룹화, 정렬이 모두 들어간 쿼리입니다. 기본적으로 존재하는 클러스터링 인덱스와 외래키 인덱스로는 해당 쿼리가 인덱스를 타지 않아 다른 기본 조회 쿼리에 비해 약 60배정도 소요 시간이 발생하였습니다. 이에 따라 해당 쿼리의 성능을 최대한 끌어올려보고자 최적의 인덱스를 찾아보기로 하였습니다.

테스트 환경

•

테스트 DB 환경

◦

테스트 DB 환경으로는 로컬 docker에 mysql을 띄워, 프로젝트와 동일하게 DB를 설정해 진행하였습니다. MySQL 은 8 버전을 사용하였습니다.

•

DB erd

◦

조회할 테이블인 role_history 에 role 테이블의 PK 가 외래키로 존재합니다.

•

더미 데이터 정보

◦

role: 100

◦

role_history: 50000 * 100

▪

하나의 role 당 role_history 를 50000 건씩 삽입하였습니다.

▪

role 의 각 role_history 마다 생성 시간 차이는 1분으로 두었습니다.

문제점

현재 다루는 역할 기록 조회 는 매번 갱신할 수 있는 역할들을 일별 가장 늦게 갱신한 역할을 조회하는 API 입니다.

위 그림과 같이 하루에 100 번의 역할 갱신을 한다고 하더라도, 그 날 가장 늦게 갱신한 역할이 해당 날짜의 역할로 조회를 합니다. 이러한 API 에서 역할을 조회하고, 그룹화와 정렬하는 조회를 한 뒤, 최종적으로 조회된 역할 히스토리에 해당하는 역할 결과를 조회를 하는데, 문제가 된 쿼리는 다음과 같습니다.

select
      max(rolehistor0_.id) as col_0_0_ 
  from
      role_history rolehistor0_ 
  where
      rolehistor0_.role_id=17
  group by
      date(rolehistor0_.date_time) 
  order by
      date(rolehistor0_.date_time) desc;
SQL
복사

위의 쿼리는 where + group by + order by desc 가 섞여 쿼리의 길이에 비해 다소 복잡한 쿼리입니다. 조금 더 자세히 보면 외래키로 있는 role_id 로 조건절 필터링을 하며, date_time을 date 로 변형하여 그룹화를 한 뒤 date_time 역순 즉 최신 날짜 순으로 정렬을 하고, 그룹화된 date_time 에서 가장 id 가 큰 데이터(날짜 별 가장 늦게 갱신한 역할) 를 조회합니다.

위 쿼리의 실행 속도는 더미 데이터 약 500만건을 기준으로 약 0.64초가 소요되었습니다. 서브쿼리가 섞인 한방 쿼리보다는 성능상 훨씬 좋았지만, 응답된 레코드 수에 비해서는 다소 오래 걸리는 것이 사실입니다.

쿼리 프로파일링을 통해 해당 쿼리가 어떤 상태인지 확인해 보았습니다.

컨텍스트 스위칭이 발생하는 빈도를 나타내는 context_voluntary 와 context_involuntary 컬럼에서 각각 약 100 건과 200 건이 발생하였습니다. 이 또한 이전 한방 쿼리의 컨텍스트 스위칭 수인 약 10000건에 비해서는 1% 수준이지만, 35건 데이터 응답 수에 비해서는 많은 편에 속합니다. 이러한 분석 결과로는 CPU 입출력이 많이 발생하여 시간이 오래 소요되었다고 판단하였습니다. 현재 스토리지 엔진으로 사용하는 InnoDB 에서 CPU 입출력을 줄일 수 있는 가장 보편적인 방식은 자주 조회되는 컬럼에 인덱스를 적용하여 성능을 높이는 방식입니다. 이에 따라 현재 적용된 인덱스를 확인해보고, 어떤 형태로 인덱스를 저장해야 가장 효율적일 수 있는지 알아보았습니다.

index 조사

현재 생성되어 있는 인덱스는 위의 3개입니다. PRIMARY 와 role_id 는 테이블이 생성될 때 자동으로 생성되는 인덱스입니다. 테이블이 생성될 때, Inno DB 는 기본키와 외래키에 대해 자동으로 인덱스를 생성합니다. 또한 date_time 항목으로 그룹화하기때문에 date_time 에 대해서 인덱스를 생성하였습니다. 하지만 실행 계획을 살펴보면!

key 컬럼에 role_id_index 만 있어, 그룹화는 인덱스를 타지 않은 것을 확인할 수 있습니다. 인덱스를 타지 않은 이유는, where 절 또는 group by 에 사용되는 컬럼에 인덱스를 적용하려면 동일한 컬럼이 사용되어야 합니다. 하지만 현재 적용되어 있는 인덱스는 조건절과 그룹화에 사용되는 컬럼이 다르기때문에 그 중 하나만 인덱스가 적용된 것입니다.

복합 인덱스 적용

이에 따라 조건절과 그룹화가 모두 인덱스에 적용될 수 있도록 컬럼 순서(role_id, datetime)에 맞추어 복합 인덱스를 생성하였습니다.

create index role_history_index_role_id_date_time on role_history (role_id, date_time);
SQL
복사

그리고 실행 계획을 확인해보면!

key 컬럼에 방금 생성한 (role id, date time) 의 복합 인덱스가 인덱스로 사용된 것을 확인할 수 있습니다. 그리고 Extra 컬럼에 추가적으로 Using index 가 포함되었습니다. Extra 컬럼의 Using index 는 커버링 인덱스가 적용되었다는 표기입니다.

커버링 인덱스는 조회할 컬럼, 조건절, 그룹화, 정렬 등에 사용되는 컬럼이 순서에 맞게 모두 인덱스에 포함된 것을 의미합니다. 모두 인덱스로 저장되어 있기때문에 데이터에 직접 접근할 필요가 없어 쿼리의 효율이 매우 좋습니다. 그런데 현재 복합 인덱스에 설정된 컬럼은 ‘role_id’ 와 ‘date_time’ 이고, 조회하는 컬럼은 ‘id’ 여서 적용된 복합 인덱스에는 id 가 없는데, 어떻게 커버링 인덱스 적용이 가능했을까요?

이는 Inno DB 의 세컨더리 인덱스(Non-Clustering Index)의 특수한 구조 덕분입니다. Inno DB 의 세컨더리 인덱스의 리프 노드에는 해당 레코드의 데이터 주소가 아닌 클러스터링 인덱스의 값(PK)을 저장하도록 구현되어 있습니다. 이에 따라 세컨더리 인덱스인 복합 인덱스의 리프 노드에는 role_history 테이블의 PK 인 id 값이 저장되어 있기때문에 직접적인 데이터 접근 없이 인덱스만으로 조회가 가능하여 커버링 인덱스가 적용된 것입니다.

커버링 인덱스 적용으로 인해 성능이 0.64 → 0.09 로 약 7배 향상되었습니다. 하지만 아직 눈에 걸리는 부분이 있습니다. Extra 필드에 Using index 이외에, 처음부터 달고 다닌 Using temporary 와 Using filesort 가 표기되어 있습니다.

•

Using temporary

◦

레코드를 그룹화하거나 정렬할 때 임시 테이블을 만들어 수행합니다.

◦

처음에는 메모리에 생성되었다가, 처리해야 할 데이터의 양이 커지면 디스크에 저장되어 성능 저하를 일으킬 수 있습니다.

•

Using filesort

◦

인덱스로 정렬되어 있지 않은 레코드를 정렬합니다.

위의 두 정보는 성능 저하를 일으키는 항목입니다. Using temporary 와 Using filesort 가 발생한 것은 그룹화/정렬 시 컬럼에 인덱스가 적용되지 않았다는 것을 의미합니다. 인덱스가 적용되지 않는 조건에는 여러 가지가 있는데 이번 경우에서는 인덱스 컬럼을 변형하여 인덱스가 적용되지 않았습니다. 현재 그룹화와 정렬 시 이용하는 컬럼은 인덱스로 설정한 date_time 이 아니라, 비즈니스 정책상 날짜로 변형이 필요한 date(date_time) 입니다. 이에 따라 변형된 컬럼을 그룹화/정렬하기 위해 Using temporary 와 Using filesort 가 발생한 것입니다.

date 컬럼 추가

이에 따라 Using temporary 와 Using filesort 를 없애기 위해 기존 date_time 을 기반으로, 새로운 date 컬럼을 만드는 반정규화를 진행하기로 하였습니다. 비록 데이터 의미가 중복되어 저장되는 단점이 있지만, 조회 성능이 사용자 입장에서 더 중요하다고 판단하였습니다. 그리고 새로 생성한 date 컬럼이 포함된 복합 인덱스를 생성하였습니다.

alter table role_history add date date;
update role_history set date=date(date_time);

create index role_history_index_role_id_date on role_history (role_id, date);
SQL
복사

그리고 그룹화와 정렬하는 컬럼을 새로 생성한 date 컬럼으로 변경한 뒤 실행 계획을 살펴보면,

select
		max(rolehistor0_.id)ascol_0_0_
from
		role_history rolehistor0_
where
		rolehistor0_.role_id=17
group by
		date
order by
		date desc;
SQL
복사

계속 혹처럼 달고 다니던 Using temporary 와 Using filesort 를 드디어 떼어낼 수 있었습니다! 또한 성능도 0.09 → 0.03 으로 3배 더 개선할 수 있었습니다. 하지만 Backward index scan 이라는 새로운 작은 혹이 새로 발생하였습니다. 비즈니스 정책 상, 날짜를 최신순으로 정렬하여 응답하기때문에 내림차순 정렬을 이용하였고, 이로 인해 인덱스를 뒤에서 부터 읽는 Backward index scan 이 발생하였습니다.

역순 인덱스 설정

MySQL 에서는 인덱스 역순 정렬 기능을 지원합니다. 다만 8.0 이전 버전에서는 문법상으로만 지원을 하고 실제로는 지원되지 않았습니다. 그러나 8.0 버전부터는 역순 인덱스 적용이 가능하게 되었습니다. 역순 인덱스에 대한 더욱 자세한 내용은 MySQL Ascending index vs Descending index 에서 알 수 있는데요, 해당 페이지의 내용을 요약하자면, 인덱스 페이지 구조는 double linked list 여서 앞에서 읽든 역순으로 읽든 성능이 같을 것 같은데 성능 차이가 있는 이유는 1. 페이지 잠금이 forward index scan 에 적합한 구조이고, 2. '인덱스 페이지 내'에서는 인덱스 레코드가 단방향으로만 연결된 구조이기 때문이라고 합니다. 이에 따라 forward index scan 과 backward index scan 의 성능 차이가 발생할 수 있는 경우는 쿼리가 하나의 인덱스 페이지에 집중되는 상황인 경우입니다. 이 상황에서 페이지 잠금을 획득하기 위한 쓰레드끼리의 경합이 빈번하게 발생하여 시간이 오래 걸릴 수 있습니다. 이러한 상황의 예시는 다음과 같습니다.

-- 인덱스: tid desc
set @random_tid=floor(rand()*12994454);
select tid from t1 where tid<=@random_tid order by tid DESC limit 50;
-- 출처: https://tech.kakao.com/2018/06/19/mysql-ascending-index-vs-descending-index/

-- 인덱스: language, rental_datetime desc
select title, rental_datetime from rentals where language = 'Italian' order by rental_datetime desc;
-- 출처: https://medium.com/naver-cloud-platform/%EC%9D%B4%EB%A0%87%EA%B2%8C-%EC%82%AC%EC%9A%A9%ED%95%98%EC%84%B8%EC%9A%94-mysql-8-0-%EA%B0%9C%EB%B0%9C%EC%9E%90%EB%A5%BC-%EC%9C%84%ED%95%9C-%EC%8B%A0%EA%B7%9C-%EA%B8%B0%EB%8A%A5-%EC%82%B4%ED%8E%B4%EB%B3%B4%EA%B8%B0-3-indexes-e32249e2dae5
SQL
복사

두 쿼리 모두 조건절에서 필터링되어 하나의 인덱스 페이지에 집중되는 상황입니다. 현재 인덱스를 적용하려는 쿼리와 많이 닮아 있습니다. 현재의 쿼리도 role_id 로 조건절 필터링을 하기때문에 필터링된 role_id 를 가진 인덱스 페이지에 집중되는 상황입니다. 이에 따라 역순 인덱스를 적용할 필요가 있다고 판단하였습니다.

create index role_history_index_role_id_date_desc on role_history (role_id, date desc);
SQL
복사

Backward index scan 없이 깔끔하게 커버링 인덱스를 적용할 수 있었습니다. 또한 속도도 0.03 → 0.02 로 약 50% 개선할 수 있었습니다.

마무리

MySQL 의 실행 계획으로 쿼리가 실행될 때 적용될 인덱스와 최적화 옵션을 확인하고, 쿼리 프로파일링을 이용하여 실제로 실행된 쿼리의 수행 속도와 속도에 영향을 미치는 컨텍스트 스위칭 수를 확인해 보았습니다. 그리고 복합 인덱스와 커버링 인덱스를 적용하여 where + group by + order by 가 포함된 복잡한 쿼리의 속도를 개선해 보았습니다.

하지만 현재도 아쉬운 점이 존재합니다. 인덱스로 저장한 role id 컬럼과 date 는 카디널리티가 높지 않아 인덱스의 효율이 상대적으로 좋지 않습니다. 그리고 인덱스를 저장하기 위한 여분의 인덱스 저장 공간(통상 약 10%)이 필요합니다. 이러한 문제의 가장 좋은 해결책은 위와 같이 복잡한 기술 적용 없이도, 비즈니스 정책 상으로 쉬운 쿼리를 만들 수 있도록 고민하고 적용하는 것입니다(EO 김범준님의 영상을 참고하였습니다). 하지만 비즈니스 정책상으로 불가피하게 복잡한 쿼리가 생성되어야 한다면 현재 테이블에서 조회하는 대신, 해당 쿼리에 최적화된 컬럼으로 새로운 테이블을 만들어 반정규화를 진행하는 것도 고려해볼 만하다고 생각해 보았습니다. 물론 이 방식 또한 중복된 의미의 컬럼이 여럿 존재하게 되니, 프로젝트의 자원량에 맞춰 어떤 방식이 가장 효율적일지 고민해보고 적용해야 합니다.