[친절한 SQL 튜닝] 인덱스 튜닝 2

인덱스 스캔 효율화

 

루트 블록 스캔 과정에서 특정 레코드를 찾았다면 그것이 가리키는 리프 블록으로 내려가면 안되고 그 이전 레코드가 가리키는 리프 블록 으로 내려가야 한다. 레코드의 값보다 크거나 같은 값들이 리프 블록에 존재하므로, 이전 레코드에 조건에 포함되는 레코드가 존재할 수 있기 때문이다

 

루트 블록에서 C1, C2가 B,3 이전의 레코드의 리프블록으로 내려가서 B, 3인 레코드를 찾고 B, 4인 레코드를 만나는 순간 스캔을 멈춘다

 

루트 블록에서 C1, C2가 B, 3 이전의 레코드의 리프블록으로 내려가 B, 3인 레코드를 찾고 C1이 C인 값을 만나면 스캔을 멈춘다. C2는 스캔을 멈추는데는 역할을 하지 못했지만 스캔의 시작점을 결정하는데는 역할을 했다

 

C2는 수직적 탐색(스캔 시작지점)을 찾는데는 역할을 하지 못했으나 스캔을 멈추는데는 역할을 했다

 

C2는 수직적 탐색(스캔의 시작지점)을 찾는데 역할을 했고, 스캔을 멈추는데도 역할을 했다. 즉, 스캔량을 줄이는데 역할을 했다

 

C1 조건은 스캔의 시작과 끝을 결정하는데 역할을 했으나, C2는 그렇지 못했다

C2는 스캔량을 줄이는데 거의 역할을 못했다

 

좌측은 성능검으로 시작하는 용어를 스캔할 때,

우측은 성능으로 시작하고 4번째 값이 선 인 단어를 스캔할 때의 경우이다

 

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

 

인덱스를 7463개의 블록을 읽으며서 10개의 블록을 찾았고, 이 블록을 가지고 10개의 블록에 테이블 엑세스를 했다

단, 버퍼 피닝에 의해 같은 블록에 있는 데이터는 추가로 블록에 엑세스 하지 않아 7463블록에 8블록을 추가로 읽어 총 7471개의 블록을 읽었다

 

선두 컬럼 : 인덱스 구성상 ‘맨 앞쪽’에 있는 컬럼을 지칭할 때 사용

선행 컬럼 : 어떤 컬럼보다 ‘상대적으로 앞쪽’에 놓인 컬럼을 지칭할 때 사용

비용 = 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 액세스 비용 = 인덱스 루트와 브랜치 레벨에서 읽은 블록 수 + 인덱스 리프 블록을 스캔하는 과정에 읽는 블록 수 + 테이블 액세스 과정에 읽는 블록 수

 

테이블과 달리 인덱스에는 같은 값을 갖는 레코드들이 서로 군집해 있다

첫번째 나타나는 범위 검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다

 

인덱스 선행 컬럼이 모두 = 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문!

 

Between 을 IN-List로 전환

IN-Llist를 사용하는 경우 IN-List 내 존재하는 개수 만큼 수직적 탐색이 발생한다

 

IN-List 항목 개수가 늘어날 수 있다면 BETWEEN을 IN-List로 전환하는 방식은 사용하기 곤란하다

이런 경우는 NL조인이나 서브쿼리로 구현하면 된다

 

BETWEEN 조건을 IN-List 조건으로 전환할 때 IN-List 개수가 많으면 수직적 탐색이 많이 발생한다

이는 BETWEEN 조건으로 리프 블록을 많이 스캔하는 비효율보다 IN-List 개수만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있다

 

NUM_INDEX_KEYS 힌트 : 인덱스의 지정한 번호의 컬럼까지만 액세스 조건으로 사용하라는 의미

해당 힌트를 쓰지 않고 컬럼을 가공하여 같은 효과를 낼 수 있다

 

 

BETWEEN과 LIKE 비교

 

BETWEEN의 경우 판매월이 201912이고 판매구분이 B인 첫번째 레코드를 만나면 스캔을 멈춘다

LIKE는 판매월이 201912인 레코드를 모두 스캔하고나서야 멈춘다

OR 조건 활용한 옵션 처리

인덱스 선두 컬럼에 대해 옵션 조건에 OR 조건을 사용하면 인덱스를 사용할 수 없다

 

인덱스에 포함되지 않는 컬럼의 경우는 OR 조건을 사용해도 괜찮다

 

OR 조건을 활용한 옵션 조건 처리

• 인덱스 액세스 조건으로 사용 불가
• 인덱스 필터 조건으로도 사용 불가
• 테이블 필터 조건으로만 사용 가능

OR 조건 사용의 장점은 NULL 허용 컬럼이어도 사용할 수 있다는 것

LIKE/BETWEEN 패턴을 사용하고자 할 때 점검해야할 것

• 인덱스 선두 컬럼

• NULL 허용 컬럼
  이때 고객ID가 NULL 허용 컬럼이면 결과집합에 오류가 생긴다

• 숫자형 컬럼
• 가변 길이 컬럼

UNION ALL 을 사용하면 코딩량은 늘지만 고객ID를 입력할 때와 그러지 않을 때에 대해 인덱스를 사용할 수 있다

 

PL/SQL 사용자 정의 함수는 생각보다 느리다

그 이유는

• 가상머신 상에서 실행되는 인터프리터 언어
• 호출 시마다 컨텍스트 스위칭 발생
• 내장 SQL에 대한 Recursive Call 발생 *

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

조건절에 PL/SQL 함수를 사용했을 때 Full Scan으로 읽으면 해당 함수가 테이블 건수만큼 수행된다

하지만 다른 조건절과 같이 있으면 함수는 조건절을 만족하는 건 수 만큼만 실행되게 되므로 좀 더 효율적이게 된다

 

마지막으로 함수가 = 로 동등 비교하게 되는 컬럼이 포함된 인덱스를 사용하여 해당 컬럼이 인덱스 액세스 조건으로 사용되면 함수는 한번만 수행된다

 

인덱스 구성 시 중요한 두가지 선택 기준

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

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

• 수행 빈도
• 업무상 중요도
• 클러스터링 팩터
• 데이터량
• DML 부하
• 저장 공간
• 인덱스 관리 비용 등

I/O를 최소화 하면서 소트 연산을 생략하기 위한 인덱스 구성 공식

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

IN-LIst 는 = 이 아니다

IN-List 가 = 이 되기 위해 IN-List Iterator 로 풀리게 되면 UNION ALL로 결과 집합을 묶기 때문에 Sort 연산을 생략 할 수 없다