인덱스 스캔 효율화

인덱스 스캔 효율화 과정

인덱스 스캔 효율성은 인덱스 칼럼을 등치(=) 조건으로 사용할 때 가장 좋다. 만약 인덱스 칼럼 중 일부가 등치 조건이 아니더라도 그 칼럼이 뒤쪽 칼럼일 경우에는 비효율이 없다.

 

왜 그런지 내가 실제 업무에서 인덱스 스캔 효율화를 한 과정을 통해 살펴보도록 하겠다.

---

로그 테이블 인덱스 개선 과정

팀에서 새로운 로그 테이블을 만들었고, 그 로그 테이블에서 PK 인덱스만 있었다. PK 인덱스는 다음과 같이 설정되었다.

..., LOG_DT(로그 날짜), LOG_HMS(로그 시간), ...

 

로그 테이블에서 주로 사용되는 쿼리를 알아보니 처리일자를 등치 조건(=), 처리시간을 BETWEEN 조건으로 사용하고 있었다. 또한 PK에 없는 GUID라는 필드는 IN 조건으로 활용되고 있었다.

   select
        ...
    from
        ...
    where
		    ...
        LOG_DT=? 
        and (
            LOG_HMS between ? and ?
        ) 
        and (
            GUID in (
                ? , ? , ?
            )
        ) 
	    ...

위 쿼리에서 인덱스를 최대로 활용할 수 있는 인덱스는 무엇일까? 다음은 새로운 인덱스 후보이다.

• …, LOG_DT, LOG_HMS, GUID, …
• …, LOG_DT, GUID, LOG_HMS, …

첫 번째 인덱스를 인덱스 1, 두 번째 인덱스를 인덱스 2라고 부르겠다. 인덱스 1과, 인덱스 2를 생성 후 실험을 진행했다.

 

테스트 결과

약 25만 개의 로그를 생성 후 테스트 해봤다. 대부분의 RDB의 경우 메모리에 값을 캐시 한다. 따라서 정확한 테스트를 위해 쿼리 실행마다 캐시를 비워주었다. 쿼리를 작성 후 실행 계획을 살펴보았다.

 

인덱스 1 결과

 

• rows : 185,626
• filtered : 50

185,626건을 스캔하고 그중 50%가 최종 결과로 넘어간다는 뜻이다. 조회시간은 약 0.05s

 

인덱스 2 결과

• rows : 216
• filtered : 100

216건을 스캔하고 그중 100% 전부 최종 결과로 넘어간다. 조회시간은 약 0.005이다. 왜 이런 결과가 나왔을까?

---

인덱스 스캔 효율성

인덱스 선행 칼럼이 조건절에 없거나 ‘=’ 조건이 아니면 인덱스 스캔 과정에서 비효율이 발생한다.

 💡선행 칼럼, 후행 칼럼

‘선행 칼럼’은 어떤 칼럼보다 ‘상대적으로 앞쪽’에 놓인 칼럼을 지칭할 때 사용하겠다. 반대로 ‘후행 칼럼’은 ‘상대적으로 뒤쪽’에 놓인 칼럼을 지칭할 때 사용하겠다.

 

다음 순서대로 인덱스가 설정되었다면 LOG_DT는 선행 칼럼, LOG_HMS는 후행 칼럼이다.

• ..., LOG_DT(로그 날짜), LOG_HMS(로그 시간), ...

 

인덱스 선행 칼럼이 조건절에 없거나 ‘=’ 조건이 아니면 인덱스 스캔 과정에서 비효율이 발생하는 이유를 알기 위해 다음 예시를 보자. 4 문자를 잘라 테이블 칼럼에 각각 저장하고 [C1 + C2 + C3 + C4] 순서대로 인덱스를 생성했다.

 

[인덱스 테이블]

예시 1.

위 테이블에서 ‘성능검’으로 시작하는 레코드를 검색하려면 어디서 스캔을 시작하고 멈춰야 될까?

 where c1 = '성'
 and c2 = '능'
 and c3 = '검'

‘성능검’으로 시작하는 레코드를 검색할 때 인덱스 수직적 탐색을 통해 ‘성능검사’ 레코드로 찾아간다. 거기서부터 스캔을 시작해 ‘성능계수’까지 총 3개의 레코드를 스캔하고 멈춘다. 두 건을 얻기 위해 세 건을 스캔했다.

예시 2.

‘성능’으로 시작하고 네 번째 칼럼이 ‘선’인 레코드를 검색하면 어디서 스캔을 시작하고 어디서 멈출까? 쿼리문은 다음과 같다.

 where c1 = '성'
 and c2 = '능'
 and c4 = '선'

위 질문은 ‘성능’으로 시작하고 네 번째 칼럼이 ‘선’인 레코드를 검색할 때 아래처럼 ‘성능’으로 시작하는 레코드를 모두 스캔해야 한다. 결과는 똑같이 두 건이지만, 예시 1보다 훨씬 더 많은 인덱스 레코드를 스캔해야 한다.

 

인덱스 조건과 필터 조건

인덱스 액세스 조건

• 인덱스 스캔 범위를 결정하는 조건절
• 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는데 영향 미친다.
• 인덱스 리프 블록 스캔하다 어디서 멈출지 결정 미치는 조건절

인덱스 필터 조건

• 테이블로 액세스 할지 결정하는 조건절
• 쿠리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지 결정

앞선 1번 예제에서 C1, C2, C3가 모두 인덱스 액세스 조건이었다. 2번 에제는 C1, C2가 인덱스 액세스 조건이고, C4는 인덱스 필터 조건이었다.

비교 연산자 종류와 칼럼 순서에 따른 군집성

인덱스에는 ‘같은 값’을 갖는 레코드들이 군집해 있다. ‘같은 값’을 찾을 때 ‘=’ 연산자를 사용하므로 인덱스 칼럼을 앞쪽부터 누락 없이 ‘=’ 연산자로 조회하면 조건절을 만족하는 레코드는 모두 모여 있다. 인덱스 칼럼 중 어느 하나를 누락하거나 ‘=’ 조건이 아닌 연산자로 조회하면 조건절 만족하는 레코드가 흩어진다.

다음은 인덱스 테이블이다.

아래 조건절로 인덱스 구성 칼럼을 모두 ‘=’ 조건으로 비교할 때 조건을 만족하는 레코드들이 5~7번까지 모여 있다.

where C1 = 1
and C2 = 'A'
and C3 = '나'
and C4 = 'a'

 

이와 달리 칼럼 중간에 범위검색 조건일 때는 다른 결과를 가져온다. 다음 조건절처럼 세 번째 칼럼 C3가 범위검색 조건인 경우는 C1부터 C3까지 세 조건을 만족하는 인덱스 레코드는 모여 있지만(2~12번), C4 조건까지 만족하는 레코드는 흩어지게 된다.(2, 3, 5, 6, 7, 11번)

where C1 = 1
and C2 = 'A'
and C3 between '가' and '다'
and C4 = 'a'

선행 칼럼이 모두 ‘=’ 조건인 상태에서 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다는 규칙을 도출할 수 있다.

즉 인덱스 스캔 범위를 결정하는 것은 인덱스 액세스 조건이고, 선행 칼럼이 ‘=’ 조건인 상태에서 첫 번째 나타나는 범위검색 조건이 인덱스 스캔 범위를 결정한다.

---

BETWEEN을 IN-List로 전환

범위검색 칼럼이 갖는 문제점을 어떻게 해결할까? 범위검색 칼럼이 맨 뒤로 가도록 인덱스 순서를 변경하면 좋겠지만 운영 시스템에서 쉽지 않다. 이럴 때 BETWEEN 조건을 IN-List로 바꿔주면 효과를 얻을 수 있다.

예시 1.

[인터넷 매물 + 아파트시세 코드 + 평형 + 평형 타입] 순서대로 인덱스를 설정 후 다음 BETWEEN 조건을 검색했다고 가정해 보자.

select *
from 매물아파트매매
where 아파트시세코드='A01011350900056'
and 평형 '59'
and 평형타입 = 'A'
and 인터넷매물 between '1' and '3'
order by 입력일 desc

인터넷 선두 칼럼 인터넷매물에 BETWEEN 연산자를 사용하면 나머지 조건(아파트시세코드, 평형, 평형타입)이 뿔뿔이 흩어지게 된다. 따라서 조건을 만족하지 않는 레코드까지 스캔하고서 버리는 비효율이 발생한다.

예시 2.

BETWEEN 조건절을 아래와 같이 IN-List로 바꿔보자.

select *
from 매물아파트매매
where 인터넷매물 in ('1', '2', '3')
and 아파트시세코드='A01011350900056'
and 평형 = '59'
and 평형타입 = 'A'
order by 입력일 desc

위 그림처럼 인덱스 수직 탐색이 3번 발생한다. 실행 계획을 보면 INLIST ITERATOR가 발생하는 것을 알 수 있다. IN-List 개수만큼 아파트매물이 ‘1’인 경우 ‘2’인 경우 ‘3’인 경우 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 칼럼을 ‘=’ 조건으로 검색하므로 선두 칼럼이 BETWEEN을 사용할 때와 같은 비효율이 사라진다.

주의사항

BETWEEN 조건을 IN-List 조건으로 전환 시 주의할 점은 IN-List 개수가 많지 않아야 한다. IN-List 개수가 많으면 수직 탐색 비용이 많이 발생한다. 그러면 BETWEEN 조건 때문에 리프 블록을 스캔하는 비효율보다 IN-List 개수만큼 브랜치 블록을 탐색하는 비효율이 클 수 있다.

또한 인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다.

where 고객등급 between 'C' and 'D'
and 고객번호 = 123

위와 같이 [고객등급 + 고객번호] 순으로 구성한 인덱스에서 고객번호 = 123 조건을 만족하는 레코드가 멀리 떨어져 있을 때만 BETWEEN 조건을 IN-List로 전환하는 기법이 유용하다. BETWEEN 조건 때문에 인덱스를 비효율적으로 스캔하더라도 블록 IO 측면에서 소량에 그치는 경우가 많다. 인덱스 리프 블록에는 테이블 블록과 달리 많은 레코드가 담기기 때문이다. IN-List 개수가 많아질수록 수직적 탐색 과정에서 많은 블록을 읽게 되므로 성능이 안 좋아질 수 있다.

결론

다시 로그 데이터 쿼리를 보겠다.

   select
        ...
    from
        ...
    where
		    ...
        LOG_DT=? 
        and (
            LOG_HMS between ? and ?
        ) 
        and (
            GUID in (
                ? , ? , ?
            )
        ) 
	    ...

LOG_DT는 등치(=) 조건이고, LOG_HMS는 범위(BETWEEN) 조건이다. 따라서 LOG_DT가 LOG_HMS보다 인덱스 순서가 앞서야 한다. 마찬가지로 인덱스에서 GUID도 LOG_HMS보다 앞서야 한다. GUID는 범위 조건이 아닌 In-List조건이기 때문이다. BETWEEN을 사용하는 LOG_HMS는 인덱스에 맨 뒤쪽에 있어야 한다.

 

 

참고자료

https://product.kyobobook.co.kr/detail/S000001975837

친절한 SQL 튜닝 | 조시형 - 교보문고