[SQL] 인덱스(Index) - (2) 인덱스 기본 사용법 - 1

이전 글:

https://sewonzzang.tistory.com/34

[SQL] 인덱스(Index) - (1) 미리보는 인덱스 튜닝

---

인덱스 기본 사용법은 인덱스를 Range Scan 하는 방법을 의미합니다. 인덱스를 Range Scan할 수 없게 되는 이유를 알고 나면, 인덱스를 Range Scan하는 방법도 자연스럽게 터득할 수 있습니다. 

인덱스 확장기능은 Index Range Scan 이외의 다양한 스캔 방식을 말합니다.

 

1. 인덱스를 사용한다는 것

책에서 색인을 찾을 때, 어떤 알고리즘이라고 해야 할지 모르지만, 우리가 찾는 단어가 위치한 시작지점으로 바로 찾아갔을 것입니다.

그 과정이 수직적 탐색에 해당됩니다. 이런 알고리즘이 잘동할 수 있는 이유는 색인이 가나다 순으로 정렬돼 있기 때문입니다. 즉, 우리가 찾고자 하는 단어들이 서로 모여있기 때문입니다. 스캔하다가 조건을 만족하지 않는 단어를 만나는 순간 멈출 수 있습니다.

 

만약, 중간부터 시작되는 단어를 찾는 경우가 있다면, 위의 경우와 무슨 차이점이 있을까요?

 

바로 시작점을 찾을 수 없다는 점이 다릅니다. 색인이 정렬돼 있더라도 가공한 값이나 중간값(중간에 포함된 값)으로는 스캔 시작점을 찾을 수 없습니다. 스캔하다가 중간에 멈출 수도 없습니다. 찾고자 하는 단어들이 흩어져 있기 때문입니다.

 

그렇다고 색인을 아예 사용할 수 없는 것은 아닙니다. 시작점을 찾을 수 없고 멈출 수 없을 뿐입니다.

즉, 가공한 값이나 중간값(중간에 포함된 값)을 찾을 때도 색인을 사용할 수 있지만, 색인 전체를 스캔해야 합니다.

 

데이터베이스도 마찬가지입니다. 인덱스 컬럼(정확히 말하면 선두컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있습니다.

'인덱스를 정상으로 사용한다'라는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미합니다.

즉, 리프 블록 일부만 스캔하는 Index Range Scan을 의미합니다.

 

인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해야만 합니다.

즉, 일부가 아닌 전체를 스캔하는 Index Full Scan 방식으로 작동합니다.

 

 2. 인덱스를 Range Scan 할 수 없는 이유

 

"인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용 (Range Scan) 할 수 없다"

 

그 이유를 설명하라고 하면 잘 설명하지 못합니다.

 

https://sewonzzang.tistory.com/34

[SQL] 인덱스(Index) - (1) 미리보는 인덱스 튜닝

 

앞에서 인덱스 탐색을 수직적 탐색과 수평적 탐색으로 나눠서 설명했습니다. 인덱스 탐색 과정을 이렇게 둘로 나눠 설명하는 것은 매우 중요합니다. 그래야 인덱스를 Range Scan 할 수 없는 이유를 명확히 설명할 수 있기 때문입니다.

 

인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문입니다.

Index Range Scan에서 'Range'는 범위를 의미합니다. 즉, Index Range Scan은 인덱스에서 일정 범위를 스캔한다는 뜻 입니다. 일정 범위를 스캔하려면 '시작지점'과 '끝지점'이 있어야 합니다.

 

예를 들어, 어느 초등학교에서 1학년부터 6학년까지 전교생을 생년월일 순으로 학교 운동장에 줄 세울 것입니다. 여기서 2007년 1월에 태어난 학생을 찾으려면, 우선 2007년 1월 1일 이후에 태어난 첫 번째 학생을 찾습니다. 거기서부터 스캔하다가 2007년 2월 1일 이후에 태어난 첫 번째 학생을 만나는 순간 멈추면 됩니다.

 

위 예시에서는 분명한 스캔 시작점과 종료지점이 있습니다. 2007년 1월 1일 이후에 태어난 첫 번째 학생을 찾는 과정이 인덱스로 말하면 수직적 탐색에 해당됩니다.

데이터베이스에서 아래 조건절을 처리할 때도 같은 과정을 거칩니다.

where 생년월일 between '20070101' and '20070131'

 

이번에는 년도와 상관없이 5월에 태어난 학생을 찾아보겠습니다. 스캔 시작점은 어디일까요? 스캔하다가 어디서 멈춰야 할까요?

스캔 시작지점과 종료지점을 알 수 없습니다, 위에서 알 수 있듯, 전교생을 다 스캔해야만 합니다.

 

데이터베이스에서 아래 조건절을 처리할 때도 같은 문제에 직면합니다.

인덱스에는 가공되지 않은 값이 저장돼 있는데, 가공된 값을 기준으로 검색하려면 어디서 스캔을 시작해야 될까요? 스캔 시작점과 끝 지점을 찾을 수 없습니다. 어디서 스캔을 멈춰야 할지도 모릅니다.

where substr(생년월일, 5, 2) = '05'

 

아래 조건절을 보면 가공하지 않은 주문수량으로 인덱스를 만들었는데, '값이 NULL이면 0으로 치환한 값' 기준으로 100보다 작은 레코드를 찾아달라고 쿼리를 작성하면 인덱스 스캔 시작지점을 찾을 수 없습니다. 그래서 인덱스를 정상적으로 사용할 수 없습니다. 즉 , 인덱스를 Range Scan할 수 없습니다.

where nvl(주문수량, 0)<100

 

아래와 같이 LIKE로 중간 값을 검색할 때도 마찬가지 입니다. '대한'으로 시작하는 값은 특정 구간에 모여있으므로 Range Scan이 가능하지만, '대한'을 포함하는 값은 전체 구간에 걸쳐 흩어져 있어 Range Scan이 불가능합니다.

where 업체명 like '%대한%'

 

아래와 같이 OR로 조건을 검색할 때, 수직적 탐색을 통해 전화번호가 '01012345678'이거나 고객명이 '홍길동'인 어느 한 시작점을 바로 찾을 수 없습니다. 따라서 인덱스를 어떤 방식으로 구성해도 Range Scan 할 수 없습니다.

where (전화번호 = :tel_no OR 고객명 :=cust_nm)

 

 아래와 같은 IN 조건절은 어떨까요? 수직적 탐색을 통해 전화번호가 '01012345678'이거나 '01098765432'인 어느 한 지점을 바로 찾을 수 있을까요?

불가능하빈다. IN 조건은 OR 조건을 표현하는 다른 방식일 뿐입니다.

하지만, UNION ALL 방식으로 작성하면, 각 브랜치 별로 인덱스 스캔 시작점을 찾을 수 있습니다.

where 전화번호 in ( :tel_no1 , :tel_no2)

//UNION ALL
select *
from 고객
where 전화번호 = :tel_no1
union all
select *
from 고객
where 전화번호 = :tel_no2

 

그래서 IN 조건절에 대해서는 SQL 옵티마이저가 IN-List Iterator 방식을 사용합니다. IN-List 개수만큼 Index Range Scan을 반복하는 것입니다. 이를 통해 SQL을 UNION ALL 방식으로 변환한 것과 같은 효과를 얻을 수 있습니다.

 

정리

'인덱스를 정상적으로 사용한다'는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의밓바니다.

아래와 같은 조건절에는 인덱스를 정상적으로 사용할 수 없습니다. 기본적으로 Index Range Scan이 불가능합니다.

단, OR 또는 IN 조건절은 옵티마이저의 쿼리변환 기능을 통해 Index Range Scan으로 처리되기도 합니다.

where sustr(생년월일, 5, 2) = '05'
where nvl(주문수량, 0) < 100
where 업체명 like '%대한%'
where (전화번호 = :tel_no or 고개명 = :cust_nm
where 전화번호 in ( :tel_no1, :tel_no2 )

 

---

다음글 :

https://sewonzzang.tistory.com/38

[SQL] 인덱스(Index) - (2) 인덱스 기본 사용법 - 2

https://sewonzzang.tistory.com/39