친절한 SQL 튜닝 - 2. 인덱스 구조 및 탐색

미리 보는 인덱스 튜닝

특정 데이터를 찾는 방법은 크게 아래의 두 가지로 나뉜다.

• 테이블 전체를 스캔한다.
• 인덱스를 이용한다.

찾으려는 데이터가 중복이 많다면 전자가 빠를 것이고, 몇 안된다면 후자가 빠르다. 그리고 인덱스의 경우 큰 테이블에서 소량의 데이터를 검색할 때 사용한다.인덱스 튜닝의 핵심 요소는 크게 두 가지로 나뉜다.

첫번째는 인덱스 스캔 과정에서 발생하는 비효율을 줄이는것이다. 인덱스 스캔 효율화 튜닝이라고 한다.예를 들어 아래의 표에서 시력이 1.0에서 1.5인 홍길동을 찾는다고 해보자.표1과 같이 이름->시력 순으로 인덱스 정렬이 이루어졌다면 빠르게 소량만 스캔하면 된다. 아래의 표에서는 2개의 로우만 스캔한다.

반면 표2와 같이 시력->이름 순으로 인덱스 테이블을 정렬해두었다면 표1과 달리 3개의 로우를 탐색해야한다.

두번째 인덱스 튜닝의 핵심요소는 테이블 엑세스 횟수를 줄이는 것이다.인덱스 스캔 후 테이블 레코드들 액세스할 때 랜덤 I/O 방식을 사용하므로 이를 랜덤 엑세스 최소화 튜닝이라고 한다. 동일하게 시력이 1.0에서 1.5인 홍길동을 찾아보자. 여기서 시력이 1.0에서 1.5인 학생이 50명이고, 이름이 홍길동인 동명이인이 5명 있다고 한다.인덱스가 시력을 기준으로 정렬된 것과 이름순으로 정렬된 것 두가지만 존재한다.여기서 효율적으로 접근하려면 당연히 이름순으로 정렬된 인덱스를 사용해야한다. 왜냐하면 접근을 5번만 하면 되기 때문이다.반면 시력순으로 정렬된 인덱스를 사용하면 50번의 접근을 해야해서 비효율적이다.

결론적으로 두번째 튜닝인 랜덤 엑세스(레코드 하나를 읽기 위해 하나의 블럭에 접근)를 최소화 하는것이 훨씬 중요하다. 왜냐하면 성능이 극적으로 좋아지기 때문이다.

인덱스 구조

인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트이다. 데이터베이스에서도 인덱스 없이 데이터를 검색하려면 테이블을 처음부터 끝까지 순회해야한다. 반면 인덱스를 사용하면 일부만 읽고 멈출 수 있다.즉, 범위 스캔이 가능해진다.아래는 B✱ Tree 인덱스의 구조이다. B Tree는 이진 균형 트리의 일종이고 한 노드에 여러 키와 데이터가 저장될 수 있는 자료구조이다.

고객 테이블이 있다고 가정하고 해당 테이블의 고객명 컬럼을 기준으로 생성된 인덱스 테이블이다. 루트와 브랜치 노드의 레코드는 하위 블록에 대한 주소값을 가지고 있다.루트와 브랜치 블록에는 키값을 값지 않는 LMC 라는 레코드가 있다. LMC는 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가르킨다. LMC가 가진 주소를 따라가보면 키값을 가진 레코드보다 작거나 같은 레코드가 저장되어 있다.(강덕승 레코드 기준으로 더 작거나 같은 레코드들이 LMC에 주소에 연결된 노드에 저장된다)

리프 노드에 저장된 레코드들은 키 값으로 정렬되어 있고 테이블 레코드들 가르키는 주소값인 ROWID를 갖는다. 만약 인덱스 키값이 동일한 레코드들이 존재한다면 ROWID 기준으로 저장된다. ROWID는 (데이터 블록의 주소 + 로우 번호)로 구성되어 곧바로 해당하는 테이블 레코드를 찾아 갈 수 있다.

이러한 인덱스를 통한 레코드 탐색 과정은 두 가지로 나뉜다.두 방식을 자세히 살펴보자.

인덱스 수직적 탐색

정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정이다.즉, 인덱스 스캔 시작지점을 찾는 과정이다. 이는 루트 블록(노드)에서부터 시작된다. 찾으려는 레코드의 컬럼 값보다 크거나 같은 값을 만나면 바로 직전 레코드가 가르키는 주소로 이동한다.결국 수직적 탐색은 해당 조건을 만족하는 레코드를 찾는것이 아니라 해당 조건을 만족하는 첫번째 레코드를 찾는 것이 목적이다.

인덱스 수평적 탐색

수직적 탐색을 통해 스캔 시작점을 찾았으면 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프블록을 수평적으로 범위 스캔한다. 인덱스에서 본격적으로 데이터를 찾는 과정이다.인덱스의 리프 블록끼리는 서로 앞뒤 블록에 대한 주소를 가져 연결된 리스트의 형태로 존재한다. 일반적으로 인덱스를 스캔하고 나서 테이블도 ROWID를 통해 액세스한다. 왜냐하면 필요한 컬럼을 인덱스가 모두 갖지 않은 경우가 있기 때문이다.

인덱스 기본 사용법

인덱스 컬럼을 가공할경우 해당 컬럼의 조건을 만족하는 시작점을 찾을 수 없다. 예를 들어 특정 단어가 포함되거나 특정 단어가 몇번째 구성 요소인지 판단하는 컬럼의 경우가 그렇다. 이러한 경우도 컬럼을 아예 사용할 수 없는것은 아니다. 다만 범위 검색의 시작점을 찾을 수 없고 인덱스 전체를 풀 스캔해야한다.

인덱스 컬럼을 가공하지 않아야 정상적으로 인덱스를 사용할 수 있다. 인덱스를 정상적으로 사용한다는것은 리프 블록에서 스캔 시작점을 찾고 중간에 멈추는 범위 스캔을 한다는 것이다.

그러면 왜 인덱스 컬럼을 가공하면 인덱스 테이블 풀 스캔을 통해서 검색을 하는걸까? 다음 예를 들어보자. 학교에서 1학년에서 4학년까지 생년월일 순으로 줄세웠다. 98년 2월 12일인 학생을 찾으려면 98년,2월 순으로 탐색하면 검색할 수 있다. 반면 년도에 상관없이 6월에 태어난 학생을 찾는다고 해보자. 해당 쿼리에서 사용될 인덱스 스캔 시작점은 어디고 탐색이 종료되는 지점은 어디인가? 두 지점 모두 알 수없다. 아래의 조건절들도 마찬가지이다.

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

가공되지 않은 주문수량 컬럼을 기준으로 인덱스를 만들었는데 null이면 0으로 치환한 값 기준 100보다 작은 조건이니 시작점을 찾을 수 없다.

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

다음으로 인덱스 사용조건에 대해 알아보자. 아래와 같이 인덱스 테이블을 소속 + 이름 + 연령 순으로 구성한다. 아래의 인덱스 테이블에서 다음 쿼리를 실행할 경우 정상적인 range scan이 가능할까?

select 사번,팀,연령
from 사원
where 사원명 = '홍길동'

홍길동이라는 이름을 가지는 레코드들은 리프 블록의 전 구간에 흩어져 있다. 그래서 아래 인덱스를 사용하면 인덱스 스캔 시작점을 찾을 수 없고, 어디서 중지할지도 모른다. 즉, 인덱스 리프 블록을 처음부터 끝까지 모두 스캔해야한다.

결론적으로 인덱스를 range scan하기 위해서는 인덱스 선두 컬럼이 조건절에 있어야 한다

그러면 인덱스가 range scan만 수행하면 항상 최적의 성능일까? 그렇지는 않다. 다음과 같은 경우를 생각해보자. 한 인덱스는 주문일자 + 상품번호 순으로 인덱스 컬럼을 구성된다. 하루에 해당 테이블에는 평균 100만건의 데이터가 쌓인다. 이 상황에서 아래의 쿼리를 실행한다.

SELECT *
FROM 주문상품
WHERE 주문일자 = :ord_dt
AND 상품번호 LIKE '%PING';

SELECT *
FROM 주문상품
WHERE 주문일자 = :ord_dt
AND SUBSTR(상품번호, 1, 4) = 'PING';

위 쿼리에서 상품번호 컬럼은 스캔 범위를 줄이는데 도움이 되지 않는다. 각각 중간 값 검색과 컬럼을 가공했기 때문이다. 결국 인덱스를 통한 range scan을 한다고 해도 100만건의 데이터를 스캔해야한다.

인덱스를 통한 소트 연산 생략

지금까지 얘기한 것은 찾고자 하는 데이터가 전체 구간에 흩어져 있는 경우가 있어서 range scan이 불가능할 수 있다는 것이다. 지금부터 얘기하는것은 인덱스가 정렬되어 있기 때문에 얻을 수 있는 부수적인 효과이다. 아래의 인덱스 테이블이 있다.

여기서 아래와 같은 쿼리를 실행할 경우에서 실행계획을 살펴보자. 옵티마이저는 ORDER BY가 있더라도 정렬 연산을 수행하지 안흔다. PK 인덱스를 스캔하면서 출력한 결과집합은 어차피 변경순번 순으로 정렬되기 때문이다.

SELECT * 
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'
ORDER BY 변경순번

ORDER BY 절에서 컬럼 가공

인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다는 것을 앞선 단락에서 얘기했다. 여기서 말하는 인덱스 컬럼은 주로 조건절에서 사용된 컬럼을 얘기한다. 조건절 뿐만 아니라 Order By 절에서도 컬럼을 가공하여 인덱스를 정상적으로 사용할 수 없는 경우도 있다. 아래 SQL을 보자.정상적으로 sorting 연산을 생략해서 쿼리를 수행할 수 있다.

SELECT * 
FROM 상태변경이력
WHERE 장비번호 = 'C'
ORDER BY 변경일자, 변경순번

아래의 SQL으로 생각해보자.당연히 sorting 연산을 생략할 수 없다. 가공되지 않은 값 기준으로 인덱스를 정렬했는데 쿼리에서는 가공된 값 기준으로 정렬하기 때문이다.

SELECT * 
FROM 상태변경이력
WHERE 장비번호 = 'C'
ORDER BY 변경일자 || 변경순번

인덱스 확장 기능 사용법

지금까지 Index Range Scan을 중심으로 살펴봤다. 인덱스의 스캔 방식에는 그 외에도 여러 방식들이 존재한다. 다양한 방식들을 살펴보고 이들의 주요 특징을 비교해본다.

Index Range Scan

인덱스의 가장 일반적이고 정상적인 형태이다. 앞서 나온것처럼 수직적 + 수평적 탐색을 통해 범위 검색을 수행한다. 해당 방식을 사용하려면 선두 컬럼을 가공하지 않은 상태에서 조건절에 사용해야한다. 다만 앞에서 살펴본 예외상황과 같이 Range Scan을 한다는것이 성능이 최적화 되는것은 아니다.

Index Full Scan

수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다. 아래와 같은 예시가 있을 수 있다.

create index emp_ename_sal_idx on emp(ename, sal);

select * from emp
where sal > 2000
order by ename;

Select 쿼리의 조건절에 선두 컬럼인 ename이 없으므로 인덱스 처음부터 스캔을 시작한다. 보통 풀 스캔은 최적의 인덱스가 없을 때 차선책으로 사용된다.위 쿼리의 경우도 ename을 사용하는 조건문이 없기에 full scan을 통해 조건에 맞는 레코드들을 찾을 수 있다.

Index Full Scan의 경우 range scan에 비해 비효율적인거 같은데 어떤 효용이 있는지 살펴보자. 위 select문처럼 인덱스의 선두 컬럼이 조건절에 없는 경우 옵티마이져는 먼저 Table Full Scan을 고려한다. 테이블 Full 스캔과 인덱스 Full 스캔을 비교해서 생각해보자. Table의 경우 (컬럼 길이 x 레코드 수)만큼의 데이터 저장 공간을 가지고 이를 전부 탐색해야한다.반면 인덱스 테이블이 차지하는 공간은 훨씬 작다.만약 인덱스 Full 스캔에서 대부분의 레코드를 필터링하여 걸러내고 일부의 레코드에만 엑세스한다면 인덱스 Full 스캔이 성능적으로 유리해진다. 이런 상황에서는 옵티마이져가 Index Full Scan 방식을 선택하게 된다. 물론 위와 같은 쿼리가 자주 수행된다면 조건절의 컬럼을 선두 컬럼으로 하는 인덱스를 생성하는게 당연히 훨씬 좋다.

다음으로 쿼리에 옵티마이져 힌트를 사용하여 인덱스를 활용한 소트 연산 생략을 살펴보자.앞선 내용에서 인덱스를 range scan할 경우 그 결과 집합이 인덱스 컬럼 순으로 정렬되어 sorting 연산이 생략된다는것을 배웠다. 이는 인덱스 Full 스캔을 하는 경우에도 적용된다. 아래의 쿼리를 보자.

select /*+ first_rows */ *
from emp
where sal > 1000
order by ename;

대부분의 사원 레코드가 sal > 1000을 만족한다면 인덱스 Full 스캔이 테이블 Full 스캔보다 효율이 떨어진다.그럼에도 위 쿼리는 옵티마이져가 인덱스 Full 스캔을 선택한다.그러한 이유는 사용자가 first_rows로 힌트를 통해 옵티마이져를 변경했기 때문이다.sorting 연산을 생략함으로써 전체 집합 중 일부를 빠르게 출력하기 위해 Index Full Scan이 선택된다. 만약 위 쿼리로 모든 레코드를 전부 fetch한다면 Table Full Scan보다 훨씬 많은 I/O가 발생하여 속도가 불리하다.