[개념정리] 인덱스 스캔 효율화

인덱스 선행컬럼이 조건절에 없거나, '='조건이 아닐 경우 스캔량이 비효율 적으로 많아진다.

 

인덱스 스캔 효율성 측정

SQL 트레이스에 인덱스 스캔과정에서 읽은 블록(cr 이 블록 개수 인데, 인덱스 리프 블록에는 평균 500개의 레코드가 담기므로, 읽은 블록 수 * 500 = 읽은 레코드 수) 수에 비해 얻은 레코드 수가 현저히 적다면 비효율 정도를 가늠 할 수 있다.

 

액세스 조건 / 필터 조건

• 인덱스 스캔 단계에 처리하는 조건절은 액세스 조건 / 필터 조건으로 나뉨
  • 인덱스 액세스 조건 : 인덱스 스캔 범위를 결정하는 조건 (스캔 시작점을 결정, 리프블록 스캔 중 어디서 멈출지를 결정)
  • 인덱스 필터 조건 : 테이블로 액세스 할 지를 결정하는 조건(p.183 c4가 테이블 액세스..? 여기서 테이블 액세스란?)
  • 테이블 액세스 단계에서 처리되는 조건절은 테이블 필터 조건. 쿼리 수행 다음단계로 전달하거나, 최종 결과 집합에 포함할지를 결정

 

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

• 인덱스에는 같은 값을 갖는 레코드들이 서로 군집해 있는데, '=' 조건이 아닌 연산자로 조회 하면 조건절을 만족하는 레코드가 서로 흩어지게 된다.
• 만약 조건절 중 인덱스의 선행 컬럼에 대해 범위 검색한다면, 후행 컬럼 데이터는 흩어지게 된다. => 첫 번째 범위 검색 조건 까지만, 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건 까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.
  • 첫번째 범위 검색 조건이 인덱스 스캔 범위를 결정한다. -> 첫번째 범위 검색 조건 까지 인덱스 액세스 조건 
  • 나머지 컬럼 조건은 인덱스 필터 조건

 

인덱스 선행 컬럼이 '='조건이 아닐 경우 생기는 비효율

• 인덱스 선행 컬럼이 = 검색 되지 않을 경우 후행 조건을 만족하는 데이터가 뿔뿔히 흩어지게 된다. -> 조건을 만족하지 않는 다른 레코드까지 스캔 하고서 버리는 비효율 발생
• Between을 IN-List로 전환
  • 인덱스의 선행을 후행으로 변경하면 좋겠지만, 인덱스 구성을 바꾸기는 쉽지않기 때문에, 선행에 걸린 조건 Between을 IN-List로 바꾸면 효과를 얻는 경우가 있다. 
  • Between 에 걸리는 값의 개수에 따라 수직적 탐색이 여러번 발생하게 되며 조건을 만족하지 않는 다른 레코드들을 읽을 필요가 없어진다.
  • 주의할 점은 IN-List의 개수가 많지 않아야 한다는 것과(수직적 탐색이 많아져 Between의 불필요한 리프 블록 스캔하는 비효율 보다, 브랜치 블록 반복 탐색 비효율이 클 수 있으므로) 인덱스 스캔과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다는 점이다.(가까운 레코드에 대한 IN-List 탐색은 수직적 탐색 비용이 더 나올 수도 있다.)
    • 그렇다면 IN 조건은 '='인가?
      • IN 조건을 인덱스 필터 방식으로 사용할 수도 있고, IN-List Iterator 방식으로 풀어 '=' 조건절 처럼 인덱스 액세스 조건으로 사용할 수도 있다. 
      • 데이터 분포 모양에 따라 in 값(각각 값들)에 의해 찾아야 하는 데이터가 넓은 범위에 속해 여러 블록에 거쳐 따로 존재한다면 IN-List로 만들어 수직적 탐색을 하는 것이 효율적일 수도 있으나, in값들이 하나의 블록 내부에 속하게 될 경우 (소수의 블록에만 속할 경우) 수직적 탐색보다 필터로 적용하는 것이 블록 I/O를 줄일 수 있는 방법이다.
      • in 조건절을 액세스 조건 또는 필터 조건으로 유도 하는 방법
        • num_index_keys()의 3번째 인자에 몇번째 인덱스 컬럼 까지 액세스 조건으로 사용할지 지정 가능.
        • 혹은 in 조건절의 왼쪽 컬럼을 가공(RTRIM, 혹은 ||)하면 해당 인덱스 컬럼은 액세스 조건으로 사용되지 않음
• Index Skip Scan 활용
  • 선두 컬럼이 Between이어서 나머지 검색조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 경우, Index Skip Scan의 위력이 나타난다.

 

Between과 Like..외 조건들의 스캔 범위

• like로 코딩하는 것이 훨씬 간편해 많이 사용하지만, between 조건절을 사용하면 like 보다인덱스 스캔량을 줄 일 수 있다.(데이터 분포와 조건절 값에 따라 스캔량 차이 없을 수도 있긴하다.)
  • 'between a and b 조건절' + '= c 조건절' vs 'like 조건절' + '= c조건절'
    • between => 인덱스에서 a, c인 값을 찾아 액세스 시작점으로 할 수 있고, c이고 b가 아닌 값을 만나면 스캔을 멈출 수 있지만, like => 액세스 시작점 또는 끝점을 정확히 할 수 없는 경우가 있다.
• 옵션 조건절에 대한 처리를 like 혹은 between으로 해결해 쿼리를 단순하게 만들 경우 like나 between을 적용한 인덱스 컬럼이 몇번째 순번이냐에 따라 비효율적인 인덱스 스캔량을 초래할 수 있다.
  • 예를 들어 인덱스 중간 컬럼이 옵션 조건절이여서 like조건절로 처리할 경우 =조건절 사용에 비해 스캔량이 늘어 날 수 있다.
•  옵션 조건에 OR 조건 활용시
  •