복합 인덱스가 적용 안되었던 이유를 실험을 통해 알게 되어 공유하고자 한다.
상황
경매 목록 조회 쿼리 성능을 개선해보고자 하였다.
- 필터링 조건: 카테고리 목록
- 정렬 조건: 북마크 수
쿼리는 아래와 같다.
select ...
from auction_search as1_0
where as1_0.category in ('디지털 기기','패션/잡화')
order by as1_0.bookmark_count desc
limit 100;
조회 성능을 개선하기 위해 (카테고리, 북마크 수)를 복합 인덱스를 생성하였다.
create index idx_category_bookmark_cnt
on auction_search (category, bookmark_count desc);
문제
실행 계획을 확인해 보니 복합 인덱스를 타지 않고, full table scan이 발생하였다.
실험
왜 복합 인덱스가 사용되지 않는 건지 궁금해 여러 조건에서 실험을 해보았다.
동등 연산 사용하기
in 연산으로 인해 인덱스가 사용되지 않는 건지 확인해 보고자 동등 연산을 사용했다.
단일 값 동등 연산 사용하기
where as1_0.category = '디지털 기기'
인덱스가 적용된다.
다중 값 동등 연산 사용하기
where as1_0.category = '디지털 기기' or as1_0.category = '패션/잡화'
인덱스가 적용되지 않는다.
결론
in 연산 자체는 인덱스 사용에 직접적인 영향을 주지 않는다.
SQL에서는 동등 연산과 in 연산을 내부적으로 동일하게 처리하기 때문이다.
즉 category in ('A','B','C')와 category = ‘A’ or category = ‘B’ or category = ‘C’ 는 동일하게 인식된다.
인덱스 활용 여부에 영향을 미치는 것은 필터링에 다중 값을 사용하는지 여부였다.
정렬 연산 제거하기
in 연산과 정렬 연산이 함께 있어서 인덱스가 사용되지 않는 건지 확인하고자, 정렬 조건을 제외한 상태로 쿼리를 실행했다.
where as1_0.category in ('디지털 기기','패션/잡화')
# order by as1_0.bookmark_count desc
limit 100;
실행 계획 확인 결과 인덱스가 사용되었다.
결론
정렬 조건의 유무가 인덱스 사용에 영향을 주었다.
다중 값 조건이 있어도, 정렬 조건이 없으면 인덱스가 적용된다.
카테고리 카디널리티 높이기
필터링 대상 컬럼의 카디널리티가 낮으면 MySQL 옵티마이저는 full table scan을 선택한다고 한다.
실제로 카테고리 카디널리티가 13개뿐이었고, 전체 데이터에서 18%만 필터링되었다.
옵티마이저는 전체 테이블 중 10%~20% 이상을 읽으면 full table scan이 빠르다고 판단하기도 한다.
이러한 이론을 바탕으로 카테고리 종류가 적어서 인덱스를 사용하지 않는 건지 의문이 들었다.
이에 카테고리 카디널리티를 10만개로 늘려보았다.
UPDATE auction_search
SET category = CONCAT(
'카테고리_',
LPAD(
MOD(auction_search_id - 1, 100000) + 1,
6, '0'
)
)
WHERE auction_search_id >= 1;
인덱스 스캔 후 정렬 연산이 수행되었다.
그런데 정렬 인덱스가 있음에도 추가로 정렬 연산을 하는 게 의문이 들었다.
결론
카디널리티가 높아지면 인덱스가 적용된다.
원인
실험과 구글링을 토대로 복합 인덱스가 적용되지 않은 원인을 파악할 수 있었다.
인덱스가 적용되지 않은 이유는 다중 필터링 연산과 정렬 연산을 같이 사용하기 때문이다.
인덱스 내부에서 카테고리별로 bookmark_count가 이미 정렬되어 있다.
여러 카테고리를 필터링하면, 인덱스는 카테고리 그룹별로 정렬된 데이터 블록을 각각 반환한다.
디지털 기기, 920
디지털 기기, 900
패션/잡화, 1000
패션/잡화, 910
각 카테고리 내에서는 이미 정렬되어 있지만, 카테고리 간에는 정렬되어 있지 않다.
그래서 전체 결과를 반환할 때 다시 한번 병합 정렬이 필요하다.
실제로 해당 복합 인덱스 사용을 강제한 후 쿼리 플랜을 조회해 보았다.
FORCE INDEX (idx_category_bookmark_cnt)
병합 정렬을 위해 추가로 정렬 연산을 수행함을 확인할 수 있다.
옵티마이저는 카테고리별로 정렬 후 추가로 병합 정렬하는 게 비용이 크다고 판단해 풀스캔을 진행한 것으로 보인다.
다만 카디널리티가 높으면, 강제하지 않아도 복합 인덱스를 사용한다.
이유는 아래와 같다.
i) 카디널리티가 낮으면
- in 연산으로 필터링해도 남는 행이 많다
- 인덱스를 탐색한 후 추가 정렬하는 것보다 풀스캔 후 정렬이 더 빠르다
ii) 카디널리티가 높으면
- in 조건으로 필터링하면 남는 행이 적다
- 인덱스를 타서 소량의 데이터만 병합 정렬하면 풀스캔보다 효과적이다.
해결
실제 서비스에서는 정렬 조건을 없애거나 카디널리티를 없애는 게 불가능하다.
따라서 북마크 수에 대한 단일 인덱스를 생성해 성능을 개선하였다.
create index idx_bookmark_cnt
on auction_search (bookmark_count desc);
아래 실행 계획을 살펴보면 인덱스를 사용하기 때문에 정렬 연산이 필요 없다.
그 결과, API 조회 성능도 인덱스 적용 전후로 750ms에서 259ms로 개선되었다.
정리
처음에는 쿼리에서 사용하는 컬럼에 인덱스를 추가하면 옵티마이저가 해당 인덱스를 사용할 줄 알았다.
하지만 실제로는 인덱스를 사용하지 않고 풀스캔을 진행하였다.
이에 실험을 해보고 그에 따른 실행 계획을 보며 원인을 파악해 보는 시간을 가졌다.
이 과정에서 아래 사실을 배울 수 있었다.
- 다중 필터링 연산과 정렬 연산을 같이 사용하면 병합 정렬 비용이 추가로 발생한다.
- in 연산은 or 연산과 동일하게 처리된다.
- 카디널리티가 높은 컬럼을 인덱스로 사용하는 것이 성능 개선에 효과적이다.
인덱스를 생성한다고 해도 옵티마이저가 해당 인덱스를 사용하는 것이 아니기 때문에 실행 계획을 꼼꼼히 확인해야겠다는 생각이 들었다.
