Index Skip Scan과 In-List 튜닝

인덱스 탐색의 중요성

 

보통 DB에서 조회 작업을 하는 경우 인덱스를 잘 사용하는지 여부에 따라 조회 쿼리 성능이 크게 향상될 수 있다.

그렇기 때문에 같은 결과를 내는 쿼리라도 WHERE 절의 조건을 적절하게 사용하여 인덱스 사용 및 인덱스 탐색 방식을 다르게 하여 성능이 잘 나오게 할 수도 있으며, 반대로 성능이 나오지 않을 수도 있다.

이 글에서 간략하게 Index Skip Scan과 Index Range Scan에 대해 정리해보고, 같은 인덱스를 사용하더라도 WHERE 절의 조건에 따라 옵티마이저가 어떻게 실행계획을 선택하는지 정리해 보려고 한다.

 

 

 

INDEX SKIP SCAN이란?

• INDEX SKIP SCAN 개념
  INDEX SKIP SCAN은 인덱스의 선두 컬럼이 WHERE 조건에 없을 때도, 후행 컬럼을 활용해 인덱스를 탐색하는 기법이다. 즉, 결합 인덱스(A, B)가 있을 때, WHERE 절에서 A가 빠졌더라도 B 조건을 활용하여 인덱스를 탐색할 수 있음.
  
  
•  INDEX SKIP SCAN이 발생하는 경우
  예를 들어, 아래와 같은 결합 인덱스가 있다고 가정하자.
  

  CREATE INDEX idx_emp ON 사원 (성별, 연봉);

  
  이때, 아래 쿼리는 성별 컬럼(선두 컬럼)이 WHERE 절에 포함되지 않았음에도 인덱스를 활용할 가능성이 있다.
  

  SELECT * FROM 사원 WHERE 연봉 BETWEEN 2000 AND 4000;

  
  이 경우 옵티마이저는 INDEX SKIP SCAN을 수행할 수도 있고, 테이블 풀 스캔(Table Full Scan)을 선택할 수도 있다.
  
  
• INDEX SKIP SCAN이 발생할 가능성이 높은 경우
  1) 성별 값이 소수(예: '남', '여' 2개뿐인 경우) : INDEX SKIP SCAN은 선두 컬럼이 다양한 값(High Cardinality)이면 비효율적이지만, 값이 적으면 유리함.
  2) 연봉 조건이 상대적으로 좁은 범위일 때 : 옵티마이저가 인덱스를 타는 것이 더 유리하다고 판단할 경우.
  INDEX SKIP SCAN이 발생하지 않는 경우
  
  
• INDEX SKIP SCAN이 발생하지 않는 경우
  1) 성별 값이 매우 많을 때 (예: 1000개 이상이면 비효율적)
  2) 연봉 조건이 너무 넓거나, 결과 건수가 많을 때
  3) 옵티마이저가 테이블 풀 스캔이 더 유리하다고 판단할 때

 

INDEX RANGE SCAN이란?

• INDEX RANGE SCAN은 인덱스에서 선두 컬럼(A)의 값이 WHERE 절에서 명확하게 지정되었을 때 발생하는 탐색 방식이다.
  
  
• 아래처럼 WHERE 절에서 성별을 명시해주면 INDEX RANGE SCAN을 유도할 수 있다. (In-List 튜닝)
  

  SELECT * FROM 사원 WHERE 연봉 BETWEEN 2000 AND 4000 AND 성별 IN ('남', '여');

  
  옵티마이저는 이제 성별이 '남' 또는 '여'일 때 각각의 인덱스 범위를 순차적으로 검색하는 것이 더 효율적이라고 판단할 가능성이 크다.
  따라서 INDEX SKIP SCAN이 아니라 INDEX RANGE SCAN이 발생할 확률이 높아진다.
  
  
• INDEX RANGE SCAN이 유리한 경우
  1) WHERE 절에 선두 컬럼(A)이 포함되어 있을 때
  2) IN 조건을 사용하여 특정 범위만 검색할 때
  3) 선두 컬럼이 소수의 값만 포함하고 있을 때
  
  
• INDEX RANGE SCAN이 발생하지 않는 경우
  1) WHERE 절에서 선두 컬럼이 아예 포함되지 않을 때
  2) 너무 넓은 범위를 검색할 때 (Full Scan이 더 빠를 수도 있음)

 

 

 

In-List 튜닝과 INDEX RANGE SCAN – 성능 최적화

 

In-List 튜닝이란 WHERE 절에서 특정 조건을 IN (값1, 값2, …) 형식으로 작성하여 인덱스 탐색 방식을 최적화하는 기법이다. 이 방법을 사용하면 옵티마이저가 INDEX RANGE SCAN을 수행하도록 유도할 수 있어 성능적으로 더 유리해질 수 있다.

결합 인덱스(A, B)가 있을 때 WHERE 조건에서 선두 컬럼(A)이 빠지면 INDEX SKIP SCAN이 발생할 수도 있지만, In-List 튜닝을 사용하면 INDEX RANGE SCAN을 유도할 수 있다.

 

• In-List 튜닝 적용
  위에서 잠깐 언급된 대로 WHERE 절에서 선두 컬럼(A)에 IN 조건을 추가하면 옵티마이저는 INDEX RANGE SCAN을 더 선호하게 된다.
  

  SELECT * FROM 사원 WHERE 연봉 BETWEEN 2000 AND 4000 AND 성별 IN ('남', '여');

  
  이렇게 하면 옵티마이저는 각각의 성별 값에 대해 별도의 범위 검색을 수행하는 것이 더 효율적이라고 판단할 가능성이 크다.
  • 성별 = '남' AND 연봉 BETWEEN 2000 AND 4000
  • 성별 = '여' AND 연봉 BETWEEN 2000 AND 4000
  이런식으로 두 개의 범위 검색을 수행하는 방식으로 실행 계획이 최적화될 수 있다.
  
  
• In-List 튜닝을 강제하는 방법
  만약 실행 계획에서 INDEX SKIP SCAN이 발생했다면, 아래와 같이 힌트를 추가하여 INDEX RANGE SCAN을 강제할 수 있다.
  

  SELECT /*+ INDEX(사원 idx_emp) */ * 
  FROM 사원 WHERE 연봉 BETWEEN 2000 AND 4000 AND 성별 IN ('남', '여');

  
  
  

 

결론

WHERE 절에 선두 컬럼이 없는 조회 쿼리의 경우 선두 컬럼을 포함하면 INDEX RANGE SCAN을 유도할 수 있다.
선두 컬럼이 없으면 옵티마이저는 INDEX SKIP SCAN을 고려할 수 있기 때문에 쿼리 실행 비용이 더 커질 수 있기 때문에  실행 계획을 확인해 보고 선두 컬럼을 조건절에 포함시켜 주는것이 좋다.

 

그 예시로 위에서 언급되었던 In-List 튜닝을 사용하면 옵티마이저가 INDEX RANGE SCAN을 수행하도록 유도할 수 있어 성능적으로 더 이점이 있을 수 있다.

따라서 WHERE 절에서 선두 컬럼을 명확히 지정하고, 필요하면 IN 조건을 사용하여 성능을 최적화하는 방식도 고려될 수 있다.