인덱스 구조 및 탐색 with 친절한 SQL 튜닝

인덱스 탐색 과정

인덱스 탐색 과정은 수직적 탐색과 수평적 탐색, 두 단계로 이루어진다. 테이블에서 데이터를 찾는 방법은 크게 두 가지로 나누어진다.

1. 테이블 전체를 스캔한다.

2. 인덱스를 이용한다. 

인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용한다. 온라인 트랜잭션 시스템에서는 소량 데이터를 주로 검색하므로 인덱스 튜닝이 중요하다. 인덱스 튜닝의 첫 번째 요소는 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것이고, 두 번째는 테이블 액세스 횟수를 줄이는 것이다. 이 두 가지 요소 중 더 중요한 것은 랜덤 액세스 최소화 튜닝이다. SQL 튜닝은 랜덤 I/O와의 전쟁이다. 

기본적으로, 데이터 베이스 성능이 느린 이유는 디스크 I/O 때문이다. 읽어야 할 데이터량이 많고 그 과정에서 디스크 I/O가 많이 발생할 때 느리다. 인덱스를 사용하는 OLTP 시스템이라면 디스크 I/O 중 랜덤 I/O가 특히 가장 중요하다.  

데이터베이스에서 인덱스 없이 데이터를 검색하려면, 테이블을 처음부터 끝까지 모두 읽어야 한다. 반면, 인덱스를 이용하면 일부만 읽고 멈출 수 있는데 이게 가능한 이유는 인덱스가 정렬되어 있기 때문이다. 이런 방식을 범위 스캔(Range Scan)이라고 한다. 

정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정은 수직적으로 탐색된다. 즉, 인덱스 스캔 시작지점을 찾는 과정이다. 

수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 안 나타날때까지 인덱스 리프 블록을 수평적으로 스캔한다. 인덱스에서 본격적으로 데이터를 찾는 과정이다. 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖는다. 즉 양방향 연결 리스트 구조다. 좌에서 우로, 또는 우에서 좌로 수평검색이 가능하다. 

인덱스를 수평적으로 탐색하는 이유는 첫째로 조건절을 만족하는 데이터 모두를 찾기 위함이고, 둘째는 ROWID를 얻기 위해서다. 필요한 컬럼을 인덱스가 갖고 있지 못하는 경우 인덱스 스캔에 더불어 테이블 액세스를 해야하는데, 이 때 ROWID가 필요하다. 

두 개 이상의 컬럼을 결합해서 인덱스를 만들 수도 있다. 고객 테이블에 성별과 고객명 기준으로 만든 인덱스가 있다고 하자. 이런 멀티 인덱스의 경우에 수직적 탐색은 인덱스를 구성하는 모든 컬럼을 만족하는 레코드를 찾는다. 다시말하면, 인덱스 스캔 시작점이 컬럼을 모두 만족하는 레코드라는 사실이다

결합 인덱스 컬럼 순서는 선택도가 낮은 컬럼을 앞쪽에 두어 결합 인덱스를 생성해야 검사 횟수를 줄일 수 있어 성능에 유리하다고 익히 알려져 있다. 하지만, DBMS가 사용하는 B*Tree 인덱스는 엑셀 필터링 처럼 동작하는 것이 아니라 루트에서 브랜치를 거쳐 리프 블록까지 탐색하면서 모든 컬럼 조건을 만족하는 블록을 탐색한다. 따라서 어느 컬럼을 앞에 두든 일량에는 차이가 없다고 한다. (자세한 건 3장 인덱스 구성 전략을 공부한 후 발행하겠습니다. ㅎㅎ)

인덱스 기본 사용법

인덱스 기본 사용법은 인덱스를 Range Scan하는 방법을 의미한다. 색인이 정렬돼 있더라도 가공한 값이나 중간값으로는 스캔 시작점을 찾을 수 없다. 따라서, 시작점을 찾을 수 없기에 색인 전체를 스캔해야한다. '인덱스를 정상적으로 사용한다'는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다 중간에 멈추는 것을 의미하고 이것을 Index Range Scan이라고 한다.

그러나, 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해야하는 것이 Index Full Scan 방식이다. 예를 들면 인덱스는 가공하지 않은 컬럼으로 만들고, 가공된 값으로 질의문을 구성하게 된 경우 가공된 값을 기준으로 검색하게 되면 어디서 스캔하게 될지 알 수 없다. 그래서 인덱스를 정상적으로 사용할 수 없다. OR 조건으로 검색하는 경우에도 여러 컬럼 조건 중 어느 한 시작점을 바로 찾을 수가 없다. 따라서 인덱스를 어떤 방식으로 구성해도 Range Scan을 할 수 없다. 

IN 조건은 OR 조건을 표현하는 다른 방식인데, 이를 UNION ALL방식으로 작성하면, 각 브랜치 별로 인덱스 스캔 시작점을 찾을 수 있어 Range Scan이 가능하다. 그래서 IN 조건절에 대해서는 SQL 옵티마이저가 IN-List Iterator 방식을 사용하여 IN-List개수만큼 Index Range Scan을 반복한다.

또한, 인덱스를 Range Scan하기 위한 가장 첫 번째 조건은 인덱스 선두 컬림이 조건절에 있어야 한다. 예를 들어, 결합 인덱스 (컬럼1, 컬럼2, 컬럼3) 에 대하여 질의문에 컬럼2만을 명시한 경우를 생각해보자. B*Tree 구조는 컬럼1, 컬럼2, 컬럼3 순서로 정렬되어 있기 때문에 컬럼2 조건만을 만족하는 블록은 순서를 보장할 수 없다. (컬럼1을 우선적으로 정렬했기 때문에)

인덱스를 타는지를 확인했다면, 인덱스 리프 블록에서 스캔하는 양을 따져봐서 인덱스를 잘 타는지 확인해 봐야한다. 예를 들어 (주문일자 + 상품번호) 순으로 인덱스를 구성했고, 이 테이블에 쌓이는 데이터량이 하루 평균 100만건이라고 가정해보자.

아래 조건절은 인덱스 선두 컬럼인 주문일자가 조건절에 있고, 가공하지 않은 상태이므로 인덱스를 Range Scan하는 데 문제가 없다. 스캔 시작점을 찾아 스캔하다가 중간에 멈출 수 있다. 그런 의미에서는 인덱스를 잘 탄다고 할 수 있다. 그런데 인덱스를 정말 잘 타는지는 인덱스 리프 블록에서 스캔하는 양을 따져봐야 한다.

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

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

위 SQL 에서 상품번호는 스캔 범위를 줄이는 데 전혀 사용되지 않는다. 따라서 위 조건절을 처리할 때 인덱스에서 스캔하는 데이터량은 주문일자 조건을 만족하는 100만 건이다. (이 문제는 나중에 '인덱스 스캔 효율화' 에서 더 자세히 다룬다고 한다!)

ORDER BY 절에서 컬럼 가공

만약 '장비번호 + 번경일자 + 변경 순번'으로 구성된 결합 인덱스에서 아래와 같이 SQL을 작성하면 정렬 연산을 생략할 수 없다.

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

또 다른 예시로 아래와 같은 쿼리도 정렬 연산을 생략할 수 없는데, 이는 ORDER BY 절에 기술한 '주문번호'가 순수한 주문번호가 아니라 TO_CHAR 함수로 가공한 주문번호를 가리키기 때문이다.

SELECT *
FROM (
	SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
    FROM 주문 A
    WHERE A.주문일자 = :dt
    AND A.주문번호 > NVL(:next_ord_no, 0)
   	ORDER BY A.주분번호
)
WHERE ROWNUM <= 30

 

SELECT-LIST에서 컬럼 가공

인덱스를 '장비번호+변경일자+변경순번'으로 구성하면, 아래와 같은 경우에도 문제가 된다.

SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
FROM 상태변경이력
WHERE 장비번호='C'
AND 변경일자 = '20180316'

인덱스에는 문자열 기준으로 정렬돼 있는데, 이를 숫자값으로 바꾸려 했기 때문이다.

이를 정렬 연산 없이 최종 변경 순번을 쉽게 찾으려면 아래와 같이 수정하면 된다.

SELECT NVL(TO_NUMBER(MAX(변경순번)), 0)
FROM 상태변경이력
WHERE 장비번호='C'
AND 변경일자 = '20180316'