인덱스 튜닝 - 인덱스 스캔 효율화와 설계

Posted Mar 23, 2025 Updated Mar 26, 2025

By da9dac

33 min read

인덱스 스캔 효율화

IOT, 클러스터, 파티션은 테이블 랜덤 액세스를 최소화할 때 효과적이지만, 이를 적용하려면 성능 검증을 위해 많은 테스트를 진행해야 하므로 어렵기에, 시스템 개발 단계에서 물리적 설계가 중요하다.

그래서 운영 환경에서 당장 시도해볼만한 것이 테이블 스캔과 인덱스 스캔의 효율화를 위한 튜닝이고, 인덱스 스캔 효율화는 다양한 튜닝 요소가 존재한다.

인덱스 탐색

기존에 배운 인덱스 탐색 방식에 대해 까먹었을 수도 있으니 다시 한 번 확인해보자

수직적 탐색
스캔을 시작할 리프 블록을 찾는 탐색으로, 루트 블록이 가리키고 있는 리프 블록으로 가는 것이 아니라, 직전 리프 블록으로 가야한다. 이는 루트 블록은 자신보다 크거나 같은 값을 갖고 있기 때문에 직전 블록부터 탐색해야 원하는 모든 값을 찾을 수 있다.

수평적 탐색
스캔 시작 지점에서 리프 블록을 순차적으로 탐색하는 방식이다.

인덱스 스캔 효율성

인덱스 선행 컬럼이 조건절에 없거나, = 조건이 없다면 인덱스 스캔 과정에 비효율이 발생한다.

인덱스 스캔 효율성 측정

SQL 트레이스 분석을 통해 인덱스를 스캔해서 얻은 결과의 수와 스캔 과정에서 읽은 블록의 수를 비교해 스캔한 수에 비해 얻은 결과가 적을 수록 비효율적이라는 것을 알 수 있다.

액세스 조건과 필터 조건

인덱스 액세스 조건
인덱스의 스캔 범위를 결정하는 조건절로, 수직적 탐색을 통해 스캔 시작점을 결정할 때 영향을 미치고, 수평적 탐색의 중단점을 결정할 때 영향을 미친다.

인덱스 필터 조건
테이블로 액세스 할지 결정하는 조건절이다. 예를 들면, C1, C2, C3, C4 컬럼의 인덱스가 있을 때, 조건절에 C1, C2, C4 순으로 존재한다면, 선행 컬럼인 C3를 건너 뛰고 C4를 사용했기 때문에 해당 조건절에 의해 어떤 테이블로 액세스 할지가 결정된다.

즉, 인덱스 스캔으로 데이터를 찾은 후, 추가적으로 필터링해야 할 조건이 있을 때 사용되고, 인덱스가 적용되지 않기 때문에 테이블을 읽게 되는 것이다.

테이블 필터 조건
인덱스 이용, 테이블 전체 스캔 여부와 상관 없이 테이블 액세스 단계에서 처리되는 조건절이다. 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지를 결정한다.

비교 연산자 종류와 컬럼 순서에 따른 군집성

인덱스는 같은 값을 갖는 레코드끼리 군집해 있기 때문에, 같은 값을 찾을 때, = 연산자를 사용하면 누락 없이 조건절을 만족하는 레코드끼리 모여 있다. 반대로, 조건을 누락하거나 = 연산자가 아닌 다른 연산자로 조회하면 레코드가 흩어지게 된다.

아래와 같은 경우에는 군집성을 사용해 효율적으로 인덱스 스캔이 가능하다.

인덱스 구성 컬럼을 모두 = 조건으로 비교하는 경우
선행 컬럼을 모두 = 조건으로 비교하고, 마지막 컬럼만 범위검색 조건인 경우

군집성을 활용하지 못하는 경우는 다음과 같다.

중간 컬럼이 범위검색 조건인 경우, 후행 컬럼까지 만족하는 레코드는 흩어진다.
즉, 선행 컬럼이 모두 = 조건일 때, 이후에 처음 나타나는 범위검색 조건이 인덱스 스캔 범위다.
범위검색 조건 이후에 나타나는 조건은 어떤 조건이든 상관 없이 흩어진다.

액세스 조건이 될 수 없는 케이스

좌변 컬럼을 가공한 조건절
왼쪽 % 또는 양쪽 % 기호를 사용한 조건절
같은 컬럼에 대한 조건절이 두 개 이상일 때, 액세스 조건으로 선택되지 못한 조건절
OR Expansion 또는 INLIST ITERATOR로 선택되지 못한 OR 또는 IN 조건절

인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 생기는 비효율

인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 등치 조건으로 사용할 때 좋은데, 이는 리프 블록을 스캔하면서 읽은 레코드를 하나도 버리지 않고 모두 테이블 액세스로 이어지기 때문에 비효율이 없어서다.

인덱스 컬럼 중 일부가 조건절에 없거나 등치 조건이 아니라도 해당 조건들이 뒤쪽 컬럼인 경우에는 비효율이 없지만, 인덱스 선행 컬럼이 조건절에 없거나, 범위검색 조건이면 인덱스 스캔 단계에서 비효율이 발생한다.

BETWEEN → IN-List

범위검색으로 발생하는 비효율을 줄이기 위해 범위검색 컬럼이 맨 뒤로 가도록 인덱스 구성을 변경하는 방법이 있지만, 실제 운영 시스템에서 구성을 바꾸는 것은 쉽지 않기 때문에, IN-List를 사용하도록 바꿔주면 효율을 향상시킬 수 있다.

IN-List를 사용하면 IN 절의 개수만큼 수직적 탐색을 수행 후, 수평적 탐색을 진행하기 때문에, 기존 BETWEEN 범위검색처럼 한 번의 수직적 탐색 후 스캔 중단점까지 모든 인덱스에 수평적 탐색을 수행할 필요가 없다.

즉, 인덱스 탐색이 IN-List의 개수만큼 수행된다는 것인데, 범위검색에 사용된 컬럼이 등치 조건으로 리스트의 개수만큼 인덱스 탐색을 수행하게 되는 것이다.

IN-List의 개수가 늘어날 수 있거나 많다면, NL 방식의 조인문이나 서브쿼리로 구현하면 되고, 방식은 범위검색 조건절은 그대로 두고 해당 컬럼을 조인 조건으로 사용해주면 끝이다.

BETWEEN 조건을 IN-List로 전환할 때 주의 사항

IN-List 개수가 많다는 것은 수직적 탐색 횟수가 그만큼 늘어난다는 것이기 때문에 리스트 개수가 너무 많은 경우에는 오히려 BETWEEN으로 인한 리프 블록 탐색 수보다 브랜치 블록을 반복 탐색하는 비효율이 클 수도 있고, 이는 루트에서 브랜치 블록까지 Depth가 깊을 때 특히 더 비효율적이다.

리스트 개수가 적어도 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다. 가까이 붙어 있다면 BETWEEN 검색과 큰 차이가 없고, 오히려 더 비효울적일 수 있기 때문이다.

Index Skip Scan 활용

BETWEEN 조건을 유지하면서 IN-List를 사용한 것과 같은 효과를 낼 수도 있다. 조건절을 유지하고 Index Skip Scan 힌트를 추가해주면 되고, 이 방법도 IN-List와 마찬가지로 레코드의 군집성이 낮을 때 효율적이다.

IN 조건은 ‘=’인가

IN 조건이 등치 조건이 되려면 IN-List Iterator 방식으로 풀려야만 하고, 그렇지 않다면 필터 조건이 된다. IN 조건을 액세스 조건으로 만들기 위해 IN-List Iterator 방식을 사용할 수는 있어도, 해당 조건에 사용되는 컬럼이 액세스 조건으로서 의미가 크지 않다면 액세스 조건보다 필터 방식으로 처리되는게 나을 수도 있다.

즉, IN 조건은 등치 조건이 아니다!!

NUM_INDEX_KEYS

IN=List를 액세스 조건이나 필터 조건으로 유도하려면, num_index_keys 힌트를 사용해 인덱스의 특정 순서 컬럼까지만 액세스 조건으로 사용하게 할 수 있다.

BETWEEN과 LIKE 스캔 범위 비교

두 범위검색 조건 모두 비효율 원리가 똑같이 적용되지만, BETWEEN이 조금이라도 더 스캔량이 적을 수 있기 때문에, 가급적이면 LIKE 보단 BETWEEN을 선호하는 것이 좋다. BETWEEN은 시작과 끝이 명확하지만, LIKE는 명확하지 않기 때문에 BETWEEN에 비해 앞뒤로 스캔량이 더 많다.

범위검색 조건을 남용할 때 생기는 비효율

개발할 때 간편하게 작성하려고 범위검색 시 LIKE를 남용하는 경우가 많은데, 해당 컬럼이 인덱스 구성 컬럼일 때는 조심해야 한다. 선행 혹은 중간 컬럼에 대해 LIKE 조건을 사용 시, 해당 조건은 액세스 조건이 아닌 필터 조건으로 바뀌게 된다.

그렇다고 무조건 BETWEEN을 사용하라는 것은 아니다. 코딩을 쉽게 할려고 인덱스 컬럼에 범위검색을 과도하게 사용해서, 하나의 쿼리로 한 번에 처리하려고 하기 보다는 귀찮더라도 상황에 맞는 효율적인 쿼리를 각각 작성해주는 것이 좋다. 특히 대량 테이블에 대해 범위 검색 시에는 영향이 더 크기 때문에 주의해야 한다.

다양한 옵션 조건 처리 방식의 장단점 비교

OR 조건 활용

OR 조건 사용 시 흔히 발생하는 문제점은 옵션 조건 컬럼을 선두에 두고 인덱스를 구성해도 인덱스를 사용할 수 없는 것으로, 인덱스 선두 컬럼에 대한 옵션 조건 OR을 사용하면 안되는 것이다.

OR 조건은 인덱스 액세스 조건과 필터 조건으로 사용할 수 없고, 테이블 필터 조건으로만 사용 가능하다. 단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면 18c 부터 인덱스 필터 조건으로 사용 가능하다.

결국 OR 조건은 인덱스를 효율적으로 사용할 수 없는 특성이 있기 때문에, 가급적이면 해당 조건을 사용한 옵션 처리는 사용하지 않는 것이 좋다. 해당 방식의 장점은 다른 옵션들과 다르게 NULL 허용 컬럼이더라도 결과집합을 보장한다는 것뿐이다. (다른 조건식에서의 NULL과의 비교는 FALSE나 UNKNOWN을 반환하거나, 결과에서 제외되지만, OR은 한 쪽 조건이 NULL이더라도 다른 쪽 조건이 참이라면 결과에 포함된다.)

LIKE/BETWEEN 조건 활용

변별력이 좋은 필수 조건이 있는 상황에서 두 옵션들을 사용하는 것은 나쁘지 않다. 필수 조건 컬럼을 인덱스 선두로 두고 액세스 조건으로 사용하면, 범위검색 조건이 인덱스 필터 조건이어도 충분한 성능이 나온다. 필수 조건이 등치 조건으로 사용된다면, 뒤에 오는 범위검색도 인덱스 액세스 조건으로 사용될 수도 있어서 더욱 좋다.

하지만, 필수 조건의 변별력이 좋지 않은 경우에는 인덱스 스캔보다 Table Full Scan이 유리할 수도 있다.

참고로, BETWEEN 조건을 사용할 때는 두 가지 경우에 해당하지 않는지 확인하고 사용해야 한다.

인덱스 선두 컬럼인가?
NULL을 허용하는 컬럼인가?

LIKE 조건을 사용할 때는 위의 두 가지 경우에 더해 아래의 경우도 확인해야 한다.

숫자형 컬럼인가?
가변 길이 컬럼인가?

인덱스 선두 컬럼에 대해 범위검색을 사용하면 인덱스 스캔 과정에 비효율이 존재하지만, 변별력이 좋은 컬럼이라면 비교적 빠르게 조회가 가능하다. 하지만 LIKE 조건에 입력이 주어지지 않았다면, 인덱스를 전체 스캔하면서 이후에 오는 컬럼을 필터링하는 비효율의 비효율이 발생한다.

NULL 허용 컬럼에 대해 범위검색을 사용하면 해당 조건들은 NULL 값을 결과집합에서 제외하기 때문에 NULL 값인 경우도 조회하고 싶다면 범위검색을 사용해서는 안된다.

또한, LIKE는 숫자형 컬럼과 비교 시 문자열 비교로 처리하여 형변환이 발생하기 때문에 해당 컬럼을 인덱스 액세스 조건으로 사용할 수 없고, 필터 조건으로 사용하게 된다.

마지막으로, LIKE는 컬럼 값 길이가 고정적일 때만 사용해야 한다. 가변 길이 컬럼이라면 예상치 못한 데이터까지 스캔하기 때문에 조심해야 하고, 가변적일 때는 길이가 같은 레코드만 조회하도록 조건을 추가하거나, % 자체를 사용하지 않는 방법도 있다.

UNION ALL 활용

  
where :value is null
union all
where :value is not null

위와 같이 변수의 값을 입력했는지에 따라 위 아래 중 하나의 쿼리만 실행되도록 분기 처리를 할 수도 있다. 두 분기에서 인덱스를 가장 최적으로 사용할 수 있게 만들 수 있는 방법이다.

NVL/DECODE 함수 활용

NVL : NULL 값을 지정된 기본값으로 대체하거나, NULL이 아닌 경우 원래 값 반환
DECODE : IF-THEN-ELSE 로직과 같은 원리로 여러 조건 비교후 해당 결과 반환

UNION ALL과 마찬가지로 NULL인 경우와 아닌 경우에 대한 분기처리가 가능한데 NVL과 DECODE 중 어떤 방식을 사용하든 실행 계획은 똑같다. 두 함수를 사용해서 컬럼을 가공해도 인덱스를 사용할 수 있는 이유는 OR Expansion 쿼리 변환이 일어나서 가능하다. 즉, 자동으로 UNION ALL로 변환이 된 것이다.

이 방식은 옵션 조건 컬럼을 인덱스 액세스 조건으로 사용할 수 있고, UNION ALL과 같은 성능을 더 단순한 쿼리로 작성할 수 있다는 것이다. 하지만, LIKE 조건과 마찬가지로 NULL인 레코드는 결과집합에 포함시키지 않는다.

그러나 이 방식을 여러 개 사용하면 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR Expansion이 일어나기 때문에 기준으로 선택되지 않는다면 인덱스 구성 컬럼이어도 모두 필터조건으로 처리된다.

Dynamic SQL
Dynamic SQL을 사용할 수 있는 환경에서는 UNION ALL이나 NVL/DECODE를 사용하지 않고도 동적 쿼리를 사용할 수 있지만, Dynamic SQL에 힌트를 명시해 동적으로 구성된 조건절과 서로 상충해 성능이 떨어지거나, 하드파싱이 발생할 수도 있기 때문에 주의해서 사용해야 한다.

함수호출부하 해소를 위한 인덱스 구성

PL/SQL 함수의 성능적 특성

PL/SQL 함수는 소량의 데이터를 처리할 때는 성능적으로 체감이 되지 않지만,
대량의 데이터를 처리할 수록 처리 속도가 느린 것을 체감할 수 있다.

PL/SQL 사용자 정의 함수가 느린 이유

가상머신 상에서 실행되는 인터프리터 언어
호출 시마다 컨텍스트 스위칭 발생(SQL 실행엔진 ↔ 가상머신)
내장 SQL에 대한 재귀 호출 발생

위의 이유들 중에서도 재귀 호출이 성능 저하의 가장 결정적인 요소인데, PL/SQL 호출 수만큼 함수 안에 내장된 SQL도 같은 횟수 실행되고, 대부분의 PL/SQL 함수에는 SQL이 내장되어 있어 재귀 호출로 인한 부하가 가장 크다.

효과적인 인덱스 구성을 통한 함수호출 최소화

PL/SQL 함수 내부 로직이 너무 복잡해 SQL로 변환할 수 없어서 어쩔 수 없이 사용해야 한다면 액세스 조건을 고려해 인덱스를 구성해 함수 호출 횟수 자체를 줄여야 한다.

즉, 함수 결과를 인덱스 구성에 추가해 해당 결과를 인덱스 액세스 조건으로 사용하게 만들어 함수 호출 횟수를 줄여야 한다.

인덱스 설계

인덱스 설계가 어려운 이유

인덱스가 많으면 발생하는 문제

DML 성능 저하 → TPS 저하
데이터베이스 크기 증가
데이터베이스 관리 및 운영 비용 상승

데이터 추가 시 인덱스에도 데이터를 추가해야 하고, 인덱스는 정렬 상태를 유지해야 하기에 수직적 탐색을 통해 입력할 블록을 찾고, 공간이 없다면 인덱스 분할까지 발생한다. 삭제도 마찬가지로, 인덱스에서 해당 레코드를 직접 찾아 삭제해줘야 한다.

가장 중요한 두 가지 선택 기준

Index Range Scan 방식을 사용하기 위해서는 인덱스 선두 컬럼을 조건절에 반드시 사용해야 하기에, 결합 인덱스를 구성할 때는 아래의 두 가지 선택 기준을 기억해야 한다.

조건절에 항상 사용되거나, 자주 사용하는 컬럼을 선정한다.
등치 조건으로 자주 조회되는 컬럼을 앞쪽에 둔다.

스캔 효율성 이외의 판단 기준

앞선 두 가지 선택 기준은 인덱스 스캔 효율성이 판단 기준이었지만, 해당 요소만 고려해서 인덱스를 설계할 수는 없다. 그 외 고려해야 할 판단 기준은 아래와 같다.

수행빈도
업무상 중요도
클러스터링 팩터
데이터량
DML 부하 (기존 인덱스 개수, 초당 DML 발생량, 자주 갱신하는 컬럼 여부)
저장 공간
인덱스 관리 비용 등

수행빈도가 높은 SQL이라면 테스트 과정에서 성능이 좋게 나오더라도 인덱스를 최적으로 구성해줘야 하고, 데이터량이 적다면 인덱스가 있으나, 없으나 성능적인 부분에서 문제가 될 가능성이 없지만, 많다면 DML 성능을 위해 효과적으로 인덱스를 줄일 줄도 알아야 한다.

공식을 초월한 전략적 설계

조건절 패턴마다 인덱스를 만들 수는 없기에 최적을 달성해야 할 가장 핵심적인 액세스 경로 몇 개만 전략적으로 선택해 최적의 인덱스를 설계하고, 나머지 액세스 경로는 약간의 비효율이 있어도, 목표 성능을 만족하는 수준으로 인덱스를 구성하는 것을 목표로 해야한다.

이때, 공식적으로 설계를 하는 것이 아니라, 업무 상황과 부가적인 요소들을 고려해 전략적이고 효율적으로 인덱스를 구성하는 것이 중요하다.

소트 연산을 생략하기 위한 컬럼 추가

조건절에 사용하지 않는 컬럼이더라도 소트 연산을 생략하기 위해 인덱스 구성에 포함시킬 수도 있다. 이 과정에서 I/O를 최소화하면서 소트 연산을 생략하려면 아래 공식을 참고하면 된다.

등치 연산자로 사용한 조건절 컬럼 선정
ORDER BY 절에 기술한 컬럼 추가
등치 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

IN 조건은 ‘=’이 아니다

IN 조건절이 IN-List Iterator 방식으로 풀려서, IN 조건절이 인덱스 액세스 조건으로 사용하면 인덱스의 정렬을 사용할 수 없어서 소트 연산을 생략할 수 없기 때문에 필터조건으로 사용해야만 한다.

즉, IN 조건절은 액세스 조건 뒤에 와야만 하고, 이는 정렬이 끝난 뒤에 와야 한다는 말이다.

결합 인덱스 선택도

인덱스 선택도는 인덱스 컬럼을 등치 조건으로 조회 시 평균적으로 선택되는 비율로, 선택도가 높으면 인덱스로 스캔하는 데이터가 많아 테이블 액세스가 많기 때문에 가능하면 선택도가 낮은 컬럼을 사용하는 것이 좋다. 따라서 인덱스 생성 시 선택도와 카디널리티를 반드시 확인해야 한다.

컬럼 순서 결정 시, 선택도 이슈

모두 같은 인덱스 액세스 조건이라면 어떤 컬럼이 앞으로 오든 인덱스 스캔 범위는 똑같다. 중요한건 필수 조건이 앞에 오기만 하면 된다는 것이다. 스캔 범위는 앞의 액세스 조건에서 정해지기 때문에 필터 조건의 순서도 상관이 없다.

하지만, 필수 조건인데 조건절에서 누락되거나 범위검색 조건일 수 있다면, 해당 조건을 앞쪽에 두어 Index Skip Scan이나 IN-List 조건을 활용하거나, 인덱스 압축 효율까지 생각하는 것이 좋다.

결론은 인덱스 생성 여부에서는 선택도가 중요하지만, 컬럼 간 순서는 필수 조건 여부, 연산자 형태가 더 중요하다.

중복 인덱스 제거

다른 인덱스의 선두 컬럼에 모두 포함되는 인덱스는 중복이니 삭제한다.
선두 컬럼만 같고 이후의 컬럼이 모두 다르지만 선두 컬럼의 카니널리티가 매우 낮다면 사실상 중복이다.

인덱스 설계도 작성

다시 볼 내용

p234 : 4장을 학습한 후, NL 조인에 대해 이해하고 다시 보기
p242 : 둘 다 인덱스 액세스 조건일 때 어떤 컬럼이 앞에 오든 인덱스 스캔 범위가 같은 이유 (까먹었다…)
p245 : 중복제거 실습 다시 보기

Book, 친절한 SQL 튜닝

SQL DB Oracle

This post is licensed under CC BY 4.0 by the author.