[SQLP] 2-2. 인덱스 활용

🧷 인덱스 활용

데이터베이스 성능 최적화를 위해 인덱스를 활용하는 것은 매우 중요하다. 하지만 인덱스를 어떻게 활용하느냐에 따라 실제 성능 향상 효과가 달라진다.

🖇️ 인덱스 스캔 방식

인덱스는 정렬되어 있기 때문에, 리프(Leaf) 블록에서 스캔 시작점부터 더 이상 데이터가 없는 지점까지 중간에 스캔을 중단할 수 있다. 이러한 경우를 Index Range Scan이라고 하며, 이는 스캔 시작점과 종료점을 명확히 파악하여 필요한 부분만 읽어 들임을 의미한다. 반면, 인덱스 전체를 순차적으로 스캔하는 경우를 Index Full Scan이라고 한다.

(1) Index Range Scan

SELECT EMPNO
FROM EMP
WHERE NAME LIKE 'ABD%'

Leaf 블록

[ABC] [ABE] [ABF]
       ↑
    스캔 정지

 

인덱스는 정렬되어 있기 때문에, 'ABD%'에 해당하는 값은 [ABE] 이후에 존재하지 않음을 알 수 있다. 따라서 시작점과 종료점을 명확히 결정할 수 있어 인덱스를 정상적으로 사용할 수 있다.

(2) Index Full Scan

SELECT EMPNO
FROM EMP
WHERE NAME LIKE '%ABD%'

Leaf 블록

[ABC] [ABE] [ABF]
       ↑
  스캔 정지 불가

 

조건절에 인덱스를 사용하였지만, '%ABD%'처럼 패턴의 시작이 와일드카드로 되어 있으면 [ABE] 이후에 해당 패턴이 존재하는지 알 수 없으므로 스캔 중간에 멈출 수 없다. 즉, 스캔 시작점과 종료점을 파악할 수 없어 인덱스를 정상적으로 사용했다고 보기는 어렵다.

 

🖇️ 인덱스 사용 조건

B * Tree 인덱스를 효과적으로 활용하려면, 인덱스의 선두 컬럼을 기준으로 루트 블록부터 리프 블록까지 수직 탐색하여 범위 스캔의 시작점을 찾아야 한다. 만약 선두 컬럼이 조건절에 포함되지 않거나 가공되어 있다면, 옵티마이저는 인덱스 전체 또는 테이블 전체를 스캔하게 되어 효율성이 크게 떨어진다.

 

즉, 스캔 시작점과 종료점을 명확히 알 수 있어야 인덱스를 효율적으로 사용하고 있다고 판단할 수 있다.

1. 인덱스 사용 조건

효율적인 인덱스 사용을 위해서는 가공되지 않은 인덱스의 선두 컬럼이 조건절에 포함되어야 한다. 인덱스 컬럼에 가공이 가해지거나, 선두 컬럼이 조건절에 명시되지 않으면 Range Scan이 발생하지 않는다. 반대로, 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있다면 Index Range Scan은 무조건 가능하다.

 

단, Index Range Scan을 수행한다고 해서 항상 성능이 개선된다고 볼 수는 없다. 인덱스를 사용하더라도 스캔해야 하는 데이터의 양이 지나치게 많다면, 실제 성능 향상 효과는 미미할 수 있다. 따라서 인덱스 사용 시, Leaf 블록에서 실제로 스캔하는 데이터의 양을 확인하는 것이 중요하다.

(1) 인덱스 컬럼 가공

인덱스 컬럼에 함수나 가공 처리를 적용하면, 인덱스 스캔의 시작점을 찾기 어렵게 되어 효율적인 Range Scan이 불가능해진다.

 

• DAY BETWEEN '2023-12-01' AND '2023-12-31' → 스캔 시작점과 종료점을 정확히 알 수 있다.
• SUBSTR(day, 5, 2) = '12' → 스캔 시작점과 끝지점을 알 수 없기 때문에 스캔을 멈출 수 없다.

(2) OR / IN 연산자

① OR 연산자

 

OR 조건은 단일 범위 스캔의 시작점을 찾기 어렵다. 그러나, UNION ALL을 활용하거나 옵티마이저의 쿼리 변환 기능인 OR Expansion 을 통해 각 조건을 분리하면, 인덱스를 효과적으로 사용할 수 있다.

 

UNION ALL을 사용하는 경우, 아래와 같은 같은 형태로로 변형하면 Index Range Scan을 작동시킬 수 있다.

 

 

OR Expansion은 OR 조건식을 옵티마이저가 위와 같은 형태로 변환할 수 있다. use_concat힌트를 통해 OR Expansion를 유도할 수 있다.

 

 

② IN 연산자

 

IN 조건 역시 시작점을 명확히 파악하기 어려워 Index Range Scan이 어려울 수 있다. 옵티마이저는 IN-List Iterator 방식을 사용하여 IN-List 개수만큼 Index Range Scan을 반복 수행한다. 이 과정은 use_concat 힌트를 통해 유도할 수 있어, 각 조건별로 인덱스가 정상적으로 활용할 수 있다.

 

2. 인덱스 비정상적 사용

인덱스 컬럼에 함수나 기타 가공 처리를 적용하여 조건절에 사용하면, 인덱스의 효율적인 활용이 어렵다. 또한, 부정형 비교 연산자(<> 또는!=)나 is not null 조건 역시 부정 조건으로 간주되어 정상적인 인덱스 사용에 제약을 줍니다. 다만, 이 세 경우 모두 인덱스 범위 스캔(index range scan)은 불가능할 뿐, 인덱스 전체를 대상으로 하는 Index Full Scan은 수행할 수 있다.

(1) IS NULL 조건

is null 조건만으로 검색하는 경우, 인덱스 사용이 불가능하다. 단일 컬럼 인덱스뿐 아니라 복합 인덱스의 경우에도, 구성 컬럼의 값이 모두 null이면 인덱스만으로 완전한 결과 집합을 도출할 수 없기 없기 때문이다.

반면, is not null 조건의 경우 Oracle에서는 단일 컬럼 인덱스에 null 값을 저장하지 않으므로, 조건에 해당하는 레코드를 효과적으로 찾을 수 있어 인덱스 사용이 가능하다.

 

① not null 제약

 

해당 컬럼에 not null 제약 조건이 설정되어 있다면, is null 조건을 사용하더라도 옵티마이저는 조건을 만족하는 레코드가 없음을 인지하고 인덱스 스캔을 통해 공집합을 반환할 수 있다. 이는 논리적 모순처럼 보일 수 있으나, Tabel Full Scan을 피하기 위한 트릭이라 볼 수 있다.

 

또한, 복합 인덱스 구성 컬럼 중 어느 하나라도 not null 제약 조건이 있다면, 해당 컬럼에 조건이 포함되어 있지 않더라도 항상 Index Range Scan이 가능해진다

 

② is null 이외의 조건식

 

is null 조건과 함께 복합 인덱스의 다른 구성 컬럼에 is null 이외의 조건이 존재하면, 해당 컬럼에는 null이 아닌 값이 저장되므로 Index Range Scan이 가능하다. 이때는 인덱스 선두 컬럼에 대한 조건이 반드시 포함되어야 한다.

 

참고로, Oracle은 null 값을 인덱스의 맨 뒤에 저장하는 반면, MSSQL은 맨 앞에 저장된다.

(3) 묵시적 형변환

명시적으로 인덱스 컬럼을 가공하지 않았더라도, 옵티마이저가 자동 형변환을 적용하면서 인덱스 컬럼에 가공이 발생할 수 있다. 이로 인해 Table Full Scan이 발생하는 경우가 있다.

• 날짜형과 문자형 비교: 날짜형으로 자동 형변환
• 숫자형과 문자형 비교: 숫자형으로 자동 형변환
• LIKE 연산자 사용 시: 비교되는 값이 문자형으로 자동 형변환

인덱스 컬럼의 데이터 타입과 동일한 형식으로 조건절을 작성하여, 불필요한 형변환이 발생하지 않도록 해야 한다.

 

✔️ 함수 기반 인덱스(FBI) 활용

 

성능 이슈의 원인이 묵시적 형변환에 있다면, 전체 프로그램을 수정할 여력이 부족할 때 임시 해결책으로 함수 기반 인덱스(FBI)를 사용할 수 있다.

FBI는 함수나 표현식을 인덱스 컬럼으로 활용함으로써, 형변환 문제를 회피하여 인덱스 효율을 높이는 이다. 다만, 이는 임시방편에 불과하므로, 그다지 권장할 만한 해범은 못되므로 추후 일정을 잡아 반드시 개선해야 한다.

🖇️ SORT 연산 생략

인덱스는 내부적으로 정렬된 상태로 저장되기 때문에, Index Range Scan을 수행할 때 별도의 정렬(SORT) 연산 없이도 정렬된 결과를 바로 반환할 수 있다. 특히, 인덱스의 선두 컬럼에 대해 모두 '=' 조건을 사용하면, 결과 집합이 인덱스 정의 순서대로 정렬되므로 옵티마이저는 ORDER BY 절의 정렬 연산을 생략할 수 있다.

 

 

실행 계획을 확인해 보면, 인덱스를 활용한 경우 별도의 SORT ORDER BY 단계 없이 결과가 반환되지만, 인덱스를 사용하지 않으면 추가적인 정렬 단계를 수행한다.

 

내림차순(DESC) 정렬에서도 동일하게 정렬 연산을 생략할 수 있다. 인덱스의 Leaf 블록은 양방향 연결 리스트 구조로 되어 있어, 양 방향에서 탐색이 가능하다.

 

• 오름차순(ASC) 정렬: 가장 작은 값을 찾아 좌측으로 수직 탐색한 후, 우측으로 수평 탐색한다.
• 내림차순(DESC) 정렬: 가장 큰 값을 찾아 우측으로 수직 탐색한 후, 좌측으로 수평 탐색한다.

 

즉, 단순히 탐색 방향을 반대로 조정하면 되므로, 실행 계획에서는 SORT ORDER BY 연산 대신 INDEX RANGE SCAN 단계에 DESCENDING 옵션이 추가된 것을 확인할 수 있다.

 

✔️ 생략이 되지 않는 경우

 

결합 인덱스 구성이 [STATUS + CUSTOMER_ID + ORDER_DATE]일 경우, 다음과 같은 SQL 문에서는 정렬 연산이 생략되지 않는다.

SELECT *
FROM ORDERS o 
WHERE o.STATUS = 'Pending'
AND o.CUSTOMER_ID BETWEEN 1 AND 100
ORDER BY o.ORDER_DATE 

 

위 SQL은 CUSTOMER_ID 컬럼이 범위 검색 조건에 해당함에도 ORDER BY 절에 포함되지 않았기 때문에 인덱스 순서대로 정렬되지 않는다.

즉, 정렬 연산을 생략하려면 조건절에 모든 선두 컬럼에 '=' 조건을 사용하거나, ORDER BY 절에 인덱스의 순서를 정확하게 반영해야 한다.

 

예를 들어,

• 조건절에 ORDER_DATE에 대한 '=' 조건을 추가
• ORDER BY o.CUSTOMER_ID, o.ORDER_DATE와 같이 인덱스 순서를 완전히 반영

하는 구문을 사용해야 정렬 연산을 생략할 수 있다.

 

🖇️ 조건 절 이외의 컬럼 가공

 

인덱스 컬럼에 함수나 가공 처리를 적용하면, 해당 컬럼의 인덱스를 정상적으로 활용할 수 없다. 이는 조건절뿐만 아니라 ORDER BY 절이나 SELECT-LIST에서도 동일하게 적용된다.

(1) ORDER BY 절에서의 가공

① ORDER BY 절에서의 가공

 

결합 인덱스가 [STATUS + CUSTOMER_ID + ORDER_DATE]로 구성된 경우, 조건절의 선두 컬럼은 가공되지 않았더라도, ORDER BY 절에서 인덱스 컬럼에 함수 등 가공 처리가 적용되면 정렬 연산 생략이 불가능하다.

 

 

② alias 별칭 사용

 

아래와 같이, SELECT 절에서 가공된 컬럼을 ORDER BY에서 그대로 참조하면 SORT 연산이 발생한다.

 

 

ORDER BY 절에서 사용된 ORDER_DATE 컬럼이, SELECT 절에서 가공된 결과를 참조하고 있기 때문이다. 이는, ORDER BY절의 o.ORDER_DATE 처럼 alias를 정확히 지정해 주면 해결된다.

 

 

(2) SELECT-LIST

 

결합 인덱스가 [STATUS + CUSTOMER_ID + ORDER_DATE]로 구성된 경우, 조건절의 선두 컬럼이 가공되지 않은 상태라면 인덱스의 최소값(MIN) 또는 최대값(MAX)을 빠르게 조회할 수 있다. 인덱스의 Leaf 블록에서 단 한 건의 레코드만 읽어도 되므로, 실행 계획에서는 FIRST ROW 을 확인 할 수 있다.

 

 

① 인덱스 컬럼 가공

 

만약 SELECT 리스트에서 인덱스 컬럼에 함수 등 가공 처리를 적용하면, 이미 정렬된 인덱스를 다시 문자열 등 다른 기준으로 재정렬해야 하므로 SORT 연산을 생략할 수 없다.

 

 

데이터 가공은 MIN 또는 MAX와 같은 인덱스 기반 집계 후에 수행하면, 정렬 연산을 생략할 수 있다. 예를 들어,
TO_CHAR(MAX(o.ORDER_DATE), 'YYYYMMDD')와 같이 집계 후 가공하면 인덱스를 정상적으로 활용할 수 있다.

 

② 스칼라 서브쿼리의 Unnesting

 

스칼라 서브쿼리에서도 MIN/MAX와 FIRST ROW 접근 방식은 정상적으로 작동한다. 다만, Oracle 12c 이상에서는 스칼라 서브쿼리의 Unnesting을 방지하기 위해 NO_UNNEST 힌트를 추가해야 한다.

 

 

하지만 스칼라 서브쿼리를 여러 번 사용하면 테이블을 반복해서 읽어야 하므로 비효율적일 수 있다. 또한, 여러 값을 출력하기 위해 CONCAT 등으로 인덱스 컬럼을 가공하는 경우에도, 인덱스의 정렬 기능을 활용할 수 없어 성능 문제가 발생할 수 있다.