인덱스 확장 기능 사용법 : 인덱스 스킵 스캔 외(친절한 SQL 튜닝)

인덱스 범위 스캔 vs 인덱스 풀 스캔

Index Range Scan

가장 기본적인 인덱스를 사용한 액세스 방식이다.

아래와 같은 그림처럼 인덱스 루트(시작점)까지 수직 이동 후 필요한 범위를 스캔한다.

 

 

 

이전에 이야기했듯, 선두 컬럼을 가공하지 않는 등의 인덱스 순서를 그대로 사용할 수 있는 방법을 사용할 경우 일반적으로 Range Scan이 사용된다.

Index Full Scan

수직 탐색 없이 바닥의 리프 노드 블록을 ‘전부’ 순회하여 스캔할 경우를 의미한다.

 

 

 

 

Index Full Scan은 그냥 순차 스캔과 동일한가? (인덱스 풀 스캔의 효용)

인덱스 Full Scan과 순차 스캔은 비슷해 보이지만 완전히 동일하지 않다. 인덱스를 읽지만, 정렬된 상태로 인덱스의 모든 엔트리를 순차적으로 읽는 작업이다.

기본적으로 테이블 전체를 가져오기보다는, 인덱스 테이블을 가져오는 것이 더 가볍고, 빠르다.

성능?

정렬이 필요하지 않은 경우, 인덱스 Full Scan은 테이블 전체를 읽는 순차 스캔과 성능이 거의 동일하거나, 오히려 더 느려질 수도 있다.

→ 테이블 스캔 : 테이블 데이터를 한 번에 쭉 읽으니까 오히려 빠를 때도 있음

왜 쓰는가? (언제 쓰는가?)

1. 정렬(Order by) 등이 필요
2. 인덱스만으로 답을 구할 수 있으면(쿼리 결과를 테이블까지 접근하지 않고 인덱스 테이블만으로 얻을 수 있는 경우) 인덱스 Full Scan이 유리하다.

즉, 테이블에 아예 접근하지 않고, 인덱스만 봐도 결과를 낼 수 있는 경우 사용하는 것이다.

 

사용 예시

-- 1. 사용 예시
-- 테이블: users(id, name, age)
-- 인덱스: CREATE INDEX idx_users_age ON users(age)

SELECT age FROM users;

-- 이 경우, 인덱스 Full Scan만으로 결과를 얻을 수 있다.
-- 1. 쿼리에서 필요한 컬럼(age)이 인덱스에 포함되어 있음.
-- 2. 따라서 테이블 데이터를 읽을 필요가 없음.

-- 2. 반대로 이 경우는 성능이 떨어질 가능성이 있다.

SELECT name FROM users WHERE age > 30;

-- 인덱스를 통해 age > 30 조건을 만족하는 행들을 찾음.
-- name 컬럼은 테이블에 있으므로, 해당 행의 데이터를 테이블에서 읽어야 함.

요약

이를 요약하면 다음과 같을 것이다.

1. 스캔 대상순차 스캔: 테이블 전체를 스캔.
2. 인덱스 Full Scan: 테이블 대신 인덱스만 스캔.
3. I/O 비용하지만 인덱스 스캔 후 테이블 데이터를 추가로 읽어야 하면 Random I/O가 발생해 느려질 수 있음.
4. 인덱스는 테이블보다 작아 디스크 I/O가 줄어들 수 있음.
5. 사용 목적순차 스캔: 모든 데이터를 읽는 게 더 효율적일 때 사용.
6. 인덱스 Full Scan: 정렬된 데이터가 필요하거나, 테이블 전체를 읽기보다 인덱스를 통해 빠르게 데이터 위치를 찾을 때 사용.

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

SQL> select /*+ first_rows */ *
2 from emp
3 where sal > 1000
4 order by ename;

Execution Plan
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS
1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
2 INDEX (FULL SCAN) OF 'EMP_ENAME_SAL_IDX' (INDEX)

여기서 ENAME 컬럼은 인덱스에 포함되어 있고, 쿼리가 SAL > 1000 조건을 만족하는 데이터만 가져온다. 이 과정에서 인덱스 풀 스캔을 사용하게 된다. 이 경우 어떤 일이 일어날까?

ORDER BY ENAME은 인덱스가 이미 정렬된 데이터를 반환하므로 추가적인 정렬 작업이 필요없다. 즉, 연산 자체가 생략된다.

인덱스는 기본적으로 정렬된 데이터를 제공하기 때문에, ORDER BY 조건이 인덱스 컬럼과 일치하면 정렬 연산을 생략할 수 있다.

항상 Index Full Scan이 Table Full Scan보다 좋은 것은 아니다.

 

 

위의 예제에서 SAL > 1000의 상황에서 인덱스 풀 스캔을 유도하면, 사실 위의 사진과 같이 거의 모든 레코드에 ‘랜덤 액세스’ 가 발생한다.

즉, IO에 의한 수행 속도 감소가 일어날 수 있다는 점을 명시하자.

 

*현재 예제에서는 소트 연산 생략으로 인해 결론적으로 Index Full Scan이 유리(order by 생략)하나, IO에서는 손해를 보았다.

Index Unique Scan

 

 

특정 조건을 만족하는 유일한 값(혹은 레코드)을 찾아내는 인덱스 접근 방식이다. (주로 equals = 연산에 사용)

쿼리가 고유한 값(Unique)을 반환하도록 보장하는 경우, 데이터베이스는 대부분 최소한의 I/O로 정확한 레코드에 접근한다.

주의할 점은, between이나 부등호, like 등의 요소가 들어간다면 대부분 Range Scan으로 변경된다는 것이다. ‘=’ 부호를 유념하도록 하자.

*또한, Unique 결합 인덱스에 일부 컬럼만 검색할 경우에도 Range Scan이 발생한다.

이는 복합 인덱스 등에서 일부 요소만으로 ‘유니크한 값’ 을 찾을 수 없기 때문.

CREATE UNIQUE INDEX idx_emp_name_dept ON emp(name, dept_id);

-- 이 상황에서 다음과 같이 유일함을 보장할 수 없는 조회를 요청했을 경우

SELECT * FROM emp WHERE name = 'Alice';

Index Skip Scan

복합 인덱스에서 선두 컬럼의 조건이 없거나 일부 값만 만족하는 경우, 후속 컬럼의 조건을 이용해 인덱스를 검색하는 방식

인덱스 선두 컬럼을 조건절에 사용하지 않으면 우리는 Index Range Scan이 불가능하며(시작 위치를 찾을 수 없다) Table Full Scan을 사용한다고 배웠다.

오라클의 경우 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식을 도입했는데, 바로 이것을 의미한다.

이 방식은 선두 컬럼의 Distinct Value(고유 값)가 적고, 후속 컬럼의 고유 값이 많을 때 유효하다.

예제

예를 들어 직원 테이블을 검색 시 다음과 같은 경우일 것이다.

"성별"은 고유 값이 적고(남/여), "연봉"은 고유 값이 많을 때의 상황이다.

 

 

select * from 사원 where 성별 = '남' and 연봉 between 2000 and 4000

 

해당 쿼리의 동작은 다음과 같다.

1. 성별이 남성이며 연봉이 2000 이상인 레코드를 먼저 찾는다(Range Scan 시작)
2. 1에 의해 4번 블럭 이전의 3번 블럭으로 이동한다
3. 시작점으로부터 리프 블록을 차례로 스캔하다 성별 = 남 and 연봉 > 4000인 레코드를 찾으면 멈춘다.

이제, 여기에 힌트를 주어 Index Skip Scan을 사용해 보자.

select /*+ index_ss(사원 사원_IDX) */ *
  from 사원
 where 연봉 between 2000 and 4000;
 
 Execute plan
 ------------------
0 Select STATEMENT Optimizer=ALL_ROWS
  0 TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
  1   INDEX (SKIP SCAN) OF '사원_IDX' (INDEX)

 

Index Skip Scan은 루트나, 브랜치 블록에서 읽은 컬럼의 정보를 이용해 Where에 부합하는 레코드를 ‘포함할 가능성이 있는’ 리프 블록만 골라서 액세스하는 방식이다.

즉, 쉽게 말하면 다음 그림과 같다.

 

 

루트와 브랜치 노드의 정보를 기반으로, 애초에 밑바닥의 리프 노드(블럭) 자체를 건너뛰는 것이다.

다시 예제 그림을 보자.

 

 

• 2번 블럭 : 800 이상이면서 1500 이하의 블럭을 담고 있다. 이는 그냥 Random Access를 스킵한다.
• 3번 블럭 : 시작점이었다. 조건에 맞으므로 액세스한다.
• 4, 5번 블럭 : 마찬가지로 5000, 8000의 크기 조건이므로 액세스하지 않는다.

6번째 블럭은 당연히 액세스되지 않을 것 같지만 ‘엑세스’ 된다. 7번 블럭도 마찬가지이다.

‘여자’이면서 연봉 3000 이하이거나, 다른 성별일 경우 해당 블럭에 저장되기 때문이다.

• 8번 블럭 : 5000 이상, 7000 이하 where절에 부합하지 않아 액세스되지 않는다.
• 9번 블럭 : 7000 이상, 1000 이하, whrere 절 부합 x, 액세스 x

10번 블럭은 6번 블럭과 마찬가지로, 10000을 넘어가지만 ‘미지의 성별 값이 존재한다면’ 여기 저장되므로 액세스된다.

이제, 다시 이 그림을 보면 이해가 될 것이다.

 

 

요약

Index Skip Scan의 핵심 원리는 선두 컬럼의 값을 스캔하면서 후속 컬럼 조건을 활용해 "접근할 필요 없는 블록"을 건너뛴다는 것이다. 이 과정을 좀 더 자세히 설명하면 다음과 같다

Access되지 않는 블럭 건너뛰기

• 복합 인덱스는 선두 컬럼 → 후속 컬럼 순으로 정렬되어 저장된다.
• Index Skip Scan은 선두 컬럼 값별로 후속 컬럼 조건을 확인하여, 해당 조건에 맞지 않는 경우 해당 블록을 건너뛴다.
• 즉, "후속 컬럼 값"을 통해 인덱스의 리프 블록에서 조건을 만족하지 않는 데이터를 제외한다.

후속 컬럼 값으로 건너뛸 블럭 유추

예를 들어, (성별, 연봉)으로 구성된 복합 인덱스가 있다고 하자.

인덱스는 성별='남'과 성별='여' 값이 정렬되어 저장되어 있음. 각 성별 그룹 안에서 연봉이 오름차순으로 정렬.

이 경우 다음과 같이 동작한다.

1. "성별=남" 범위를 스캔하면서 연봉 BETWEEN 2000 AND 4000 조건을 확인.
2. "성별=남" 범위에서 연봉이 조건에 맞지 않는 블록은 바로 건너뜀.
3. 이후, "성별=여" 범위로 넘어가 같은 방식으로 처리.

= 인덱스가 선두 컬럼(성별)을 기준으로 정렬되어 있지만, 후속 컬럼(연봉)의 조건을 이용해 불필요한 블록에 접근하지 않음.

이 과정이 가능한 이유

복합 인덱스는 선두 컬럼과 후속 컬럼 간의 계층적 정렬 구조를 가지고 있다.

이 정렬된 구조를 이용해서 다음과 같이 동작한다.

1. 선두 컬럼 값을 기반으로 탐색.
2. 후속 컬럼 조건을 만족하지 않는 블록은 효율적으로 스킵.

Index Skip Scan이 작동하기 위한 조건

해당 스캔은 선두 컬럼의 Distinct Value(고유 값)가 적고, 후속 컬럼의 고유 값이 많을 때 유효하다고 언급했다.

1. 복합(결합) 인덱스가 존재해야 한다

Index Skip Scan은 복합 인덱스에서만 작동한다

2. 선두 컬럼에 조건이 없거나 일부 값만 사용할 때

선두 컬럼이 전체적으로 조건에 포함되지 않거나, 일부 값만 사용될 때 Index Skip Scan이 유효하다.

• 예를 들어 WHERE salary > 50000 (선두 컬럼 department가 조건에 포함되지 않음).
• 선두 컬럼 조건이 완전히 없을 경우에도, 인덱스 스킵 스캔이 선두 컬럼의 각 고유 값(Distinct Value)을 순차적으로 확인하며 탐색한다.

3. 후속 컬럼에 조건이 있어야 한다

후속 컬럼의 조건이 인덱스를 필터링할 수 있어야 한다.

• 예를 들어 WHERE salary > 50000과 같이 후속 컬럼에 대한 명시적 조건이 있을 경우.
• 후속 컬럼 조건이 없으면 단순히 모든 값을 스캔해야 하므로 Skip Scan의 이점이 없다

4. 선두 컬럼의 고유 값(Distinct Value)이 적어야 한다

선두 컬럼의 고유 값이 적으면, 각 고유 값에 대해 블록을 탐색하는 비용이 낮아져 Skip Scan의 성능이 향상된다.

• 예를 들어 department의 고유 값이 5개(인사, IT, 영업 등)라면, 각 고유 값에 대해 탐색하면서 후속 컬럼 조건을 적용하기에 효율적.
• 반대로, 선두 컬럼의 고유 값이 많으면 탐색 범위가 넓어져 비효율적일 수 있다.

5. 후속 컬럼의 고유 값이 많아야 한다

4번과 반대되는 내용이다. 후속 컬럼의 고유 값이 많을수록 Skip Scan의 필터링 효과가 커진다.

• 예를 들어 salary가 다양한 값을 가지는 경우 조건을 만족하지 않는 블록을 더 많이 건너뛸 수 있다.
• 후속 컬럼의 값이 적으면 조건 필터링 효과가 낮아져 성능이 저하될 수 있다.