[3] 인덱스 기본 사용법

이 글은 [친절한 SQL 튜닝] 을 학습하고 정리한 글입니다.

 

인덱스 사용의 기본은 Index Range Scan 하는 방법을 이해하는 것인데요. 

리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미합니다. 

 

인덱스 컬럼을 가공하면 인덱스를 이용한 Range Scan을 할 수 없게 됩니다.

인덱스 컬럼을 가공한다는 것은, 인덱스 컬럼이 제공하는 데이터의 일부 혹은 변형된 값을 사용하여 쿼리를 하는 것을 의미합니다. 

 

예를 들어, "날짜" 에 대해서 index가 구성되어 있었다고 가정을 하면

날짜가 2007년 6월 1일 ~ 2007년 6월 31일에 발생한 이벤트에 대해 쿼리를 날리면

2007년 6월 1일을 인덱스로 탐색을 시켜서 Leaf 노드를 찾을 수 있습니다. 

 

그런데 만약 년도에 상관없이 5월에 발생한 이벤트에 대해 쿼리를 날리면

인덱스를 통해 값을 "비교"할 수 없게 되어 스캔 시작점을 찾지 못하게 되고

결국 인덱스를 Range Scan 할 수 없게 됩니다.

 

마찬가지로, Full Text Search 와 같은 쿼리 (where name like %smth%) 는 Range Scan이 불가능합니다. 

 

OR 구문 같은 경우에도, where (전화번호 = :num OR 고객명 = :cust) 

이런식으로 들어가면 대체 무엇을 인덱스로 두어야 할지 판단하기 힘들기 때문에 인덱스의 이점을 활용할 수 없습니다.

그래서 OR 구문은 인덱스를 이용하기 위해 OR Expansion을 하는데요, 이 주제에 대해서는 나중에 다루기로 합시다. 

 

-----

 

조건절에서 인덱스 컬럼을 가공하면 인덱스를 정상적으로 쓸 수 없다는 것을 알았습니다. 

근데 더 중요한 것은, 인덱스 선두 컬럼이 조건절에 있어야 한다는 사실입니다.

예를 들어, 인덱스가 소속팀 / 사원명 / 연령 순으로 구성 되어있는 상황에서

사원명을 조건으로 쿼리를 하는 상황을 생각해봅시다. 

 

인덱스를 통해 접근하려고 보니, 소속팀에 대해 먼저 정렬이 되어있는 상황이고

사원 명에 대해서 연속적이지 않고 떨어져있는 것을 확인할 수 있었습니다. 

왜냐면, 소속팀에 대해서 먼저 정렬이 되어있기 때문입니다. 

 

이렇게 검색하면 결국 전체를 모두 스캔해야 됩니다.

그래서 중요한 건 인덱스 선두 컬럼이 조건절에 있어야 한다는 점입니다. 

반대로 말하면, 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면, 인덱스 Range Scan은 무조건 가능하게 됩니다. 

인덱스를 잘 타는 것에서 그치지 않고, 스캔하는 데이터량을 더 줄이는 방법에 대해서 고민을 해보아야 합니다 (추후에)

 

---

 

인덱스를 이용한 소트 연산 생략

 

인덱스의 "정렬" 되어있는 특성을 이용해서 소트 연산 생략 효과를 얻을 수 있습니다.

어떤 테이블이 [ 장비 번호 / 변경 일자 / 변경 순번 ] 순으로 인덱스가 구성되어 있다고 가정하면

 

장비번호와 변경일자에 where 절을 이용해서 쿼리하면

이미 변경 순번에 대해 정렬된 결과가 반환이 될 것입니다.

Optimizer는 이를 이미 알아서 Order by가 있어도 정렬 연산을 따로 수행하지 않게 됩니다. 

오름차순이든 내림차순이든 상관 없습니다. 인덱스 리프 블록은 양방향으로 연결되어있기 때문이죠. 

오름차순일 떈 왼쪽(작은거)부터 스캔해서 오른쪽(큰거)으로 올라가고

내림차순일 땐 오른쪽(큰거)부터 스캔해서 왼쪽(작은거)로 탐색합니다. 

 

Order by 절에서 컬럼 가공

 

장비번호 / 변경일자 / 변경순번 으로 인덱스가 적용되어있을 때 

Select *

from 상태변경이력

Where 장비번호 = 'C'

ORDER BY 변경일자, 변경순번

이렇게 되어있으면 변경일자, 변경순번에 대한 정렬 연산이 인덱스로만 가능한데

 

만약 ORDER BY 변경일자 || 변경순번 이런식으로 되어있으면 or이기 때문에 당연히 안될 것입니다..

 

Aggregate 에서 컬럼 가공

 

가공을 먼저 하고 나서 비교하냐, 비교한 것을 가공하느냐는 큰 차이가 있는데요

 

Select NVL(MAX(TO_NUMBER(변경순번)), 0) 

from 상태변경이력

where 장비번호 = 'C'

and 변경일자 = '20180316'

 

일자 테이블이 varchar형식으로 인덱스가 되어잇는데

TO_NUMBER으로 변환한 값에 대해 MAX를 수행하니

형변환에 의해 인덱스간 비교가 불가능해집니다.

 

TO_NUMBER 빼고 비교했으면 장비번호랑 변경일자에 대해 수직 탐색을 할 때

만족하는 가장 오른쪽 지점으로 내려가서 첫번째 읽는 레코드를 가져오면 끝나는데

인덱스로 비교를 못하니까 그게 불가능해집니다.

 

NVL(TO_NUMBER(MAX(변경순번)), 0) 처럼 쿼리한 후 형변환해야 인덱스를 쓸 수 있습니다.