개요
인덱스에 대해서 공부하고 나니, Cabi 내에서 사용되는 인덱스에는 어떤 것들이 있고 어떻게 적용되어 사용하는지 궁금해졌다. 직접 인덱스 검색이 사용되는 상황을 찾아보고, 인덱스 스캔의 성능을 비교해보자.
인덱스 검색이 사용되는 상황 알아보기
cabi에 적용된 인덱스와 인덱스를 통해 검색되는 로직을 찾아보자. 현재 cabi는 따로 인덱스를 생성해 사용하지 않고 있다.
인덱스 고유 스캔
일단 기본 키에 대해서는 클러스터 인덱스가 적용되어 있기 때문에, 기본 키를 통해 조회를 시도하면 인덱스를 사용해 검색한다. 위의 실행계획에서는 type이 const로 되어있는데, where 조건에 등호(=)와 PK를 사용하여 인덱스 고유 스캔이 수행된 것이다.
또한 cabi에서는 대부분의 연관관계에서 외래 키 조건에도 유니크 조건이 걸려있기 때문에, 자동으로 보조 인덱스가 생성되어 있다. 때문에 외래 키로 연관관계를 Join해서 가져오는 쿼리의 실행계획을 살펴보면, where 조건을 기본 키만 사용하여 위와 같이 const + const 조합으로 빠르게 조회한다.
인덱스 풀 스캔
이와 같이 새로 ended_at이라는 인덱스를 지정하고 해당 컬럼을 조건으로 검색 쿼리를 수행하면,
이처럼 인덱스 풀 스캔이 수행된다.
인덱스 범위 스캔
이처럼 인덱스 설정된 컬럼에 대해 범위로 검색을 하게되면, 인덱스 범위 스캔(range)이 수행된다.
참고로 테이블의 데이터 양이 적으면, 범위 조건을 지정해도 옵티마이저가 인덱스 풀 스캔이 효율적이라 판단해 인인덱스 풀 스캔이 수행된다.
인덱스 온리 스캔
그 외 추가적으로 where 조건이 없거나 조건에 사용된 컬럼이 인덱스 지정이 안되어있더라도, 조회하는 컬럼이 모두 인덱스 지정된 컬럼으로 되어있다면 이처럼 인덱스 온리 스캔(커버링 인덱스)가 수행된다. 주로 인덱스 검색이 불가능하지만 위처럼 인덱스에 포함된 컬럼만으로 처리할 수 있는 경우나 인덱스를 통해 정렬이나 그룹핑이 수행될 수 있는 경우에 사용된다. 성능은 풀 테이블 스캔보다는 낫지만, 크게 좋지는 않다.
ref 인덱스 스캔(MySQL)
제약 조건을 걸지 않거나 제약 조건으로 인덱스가 지정된 컬럼만 사용하는게 아니라면, 위처럼 테이블 풀 스캔(ALL)이 발생한다. 다만 주체가 되는 테이블 조회 이후 연관관계를 가지는 테이블을 조회할 때는, 외래 키에 설정된 보조 인덱스를 통해 검색을 수행한다(eq_ref).
여기서 eq_ref는 여러 테이블이 Join 되는 쿼리에서만 사용되고, 연관 테이블에 기본 키 혹은 유니크 컬럼을 통해 조회를 수행하여 매칭되는 결과가 딱 1개인 경우에 사용되는 인덱스 검색이다. lent_history 테이블을 테이블 풀 스캔으로 조회하고, 조회한 결과에 Join의 ON 조건에 맞춰 외래 키 인덱스로 매칭되는 user를 하나씩 찾아오는 것이다.
기본적으로 Join을 통한 여러 테이블 조회 시 외래 키 인덱스를 통한 인덱스 검색이 수행된다. 다만 위의 결과에서는 type이 ref로 되어있는데, 이는 매칭되는 결과가 1개 이상으로 예측 되는 경우의 인덱스 검색을 말한다. ref는 PK, 외래 키, 유니크 조건 관계 없이 where 조건에 동등 조건이 들어가는 경우 수행되고, eq_ref에 비해 느릴뿐이지 충분히 빠른 인덱스 검색이다.
사실 ref와 ef_ref는 정확히 어떤 인덱스 스캔에 매칭되는지 잘 모르겠다. DBMS마다 인덱스의 구조가 다르고 사용하는 방식이 달라 위에서 기술한 인덱스의 여러 스캔 방식들이 MySQL에 정확히 들어맞지 않는 것 같다.
두 개 이상의 여러 테이블을 Join하게 되면, 이처럼 실행계획이 작성된다.
인덱스를 직접 찾아보면서, 인덱스 루스 스캔과 인덱스 병합 스캔이 수행되는 것을 확인하려 했지만 실패했다.
MySQL의 실행계획 중 type에는 index_merge라는 인덱스 병합 스캔에 대한 항목도 있지만, 실제 UNION ALL을 통해 두 개의 서브쿼리에서 각각 인덱스 조건을 걸어도
이처럼 인덱스 풀 스캔 + ref 조합으로 수행이 된다. index_merge가 생각보다 효율적이지 않다는 글을 보았는데, 그러한 이유로 옵티마이저에서 선택을 하지 않는게 아닐까 추측해본다.
인덱스 스캔 성능 비교해보기
이와 같이 인덱스를 생성하거나 인덱스를 삭제 후, analyze를 통해 통계 정보를 업데이트하여 인덱스 성능을 비교해보았다.
인덱스 풀 스캔
인덱스 적용 전에는 테이블 풀 스캔이 발생하고 15ms 정도 소요된다.
인덱스를 적용하면 인덱스 풀 스캔이 수행되고 10ms 정도 소요된다. 실질적으로 조회 쿼리 수행 시간이 감소하기는 하지만, 성능 개선 폭이 그리 크지 않다.
인덱스 온리 스캔
인덱스 적용 전에는 테이블 풀 스캔이 발생하고 17ms 정도 소요된다.
인덱스를 적용하면 인덱스 온리 스캔이 수행되고 7ms 정도 소요된다. 절반 이상 성능이 개선되었으며, 나름 유의미한 성능 개선이 될 수 있다.
ref 스캔
인덱스 적용 전에 단일 동등(=) 조건으로 검색을 수행 시 테이블 풀 스캔이 발생하고 5ms 정도 소요된다.
인덱스를 적용하면 ref 인덱스 스캔이 수행되고 1ms 정도 소요된다. 상당히 유의미한 성능 개선을 확인할 수 있다.
요약하자면 ref 스캔 > 인덱스 온리 스캔 > 인덱스 풀 스캔 순으로 성능 개선 폭이 컸으며, 이를 참고하여 인덱스를 지정할 때 잘 고려해보자.
인덱스 고유 스캔은 PK나 외래 키 + 유니크 조건이 필요하고, 인덱스 범위 스캔의 경우 클러스터 인덱스가 필요하다. 하지만 PK의 경우 인덱스가 없는 경우를 측정할 수 없어 비교하지 못했다.
이를 위해 PK를 해제하려고 시도하면, 당연한 이야기지만 실패한다.
