🔑 SQL 튜닝 - CH 3. 인덱스 튜닝

 

1️⃣ 인덱스 클러스터링 팩터 (Index Clustering Factor)

클러스터링 팩터란?

특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미

 

CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋음

 

 

1) 좋은 CF의 예

 

 

2) 안좋은 CF의 예

CF가 좋은 경우는 매우 드물고 대부분 사진 2번의 경우이다.

 

---

2️⃣ 인덱스 손익분기점

 

인덱스 ROWID를 이용한 테이블 액세스는 고비용 구조이다.

따라서 인덱스는 소량 검색에 유리하다 (일반적으로 5% ~ 20%)

읽어야 할 데이터가 일정량을 넘는 순간, 풀 스캔보다 오히려 느려진다.

Index Range Scan이 Table Full Scan보다 느려지는 지점을 "인덱스 손익분기점" 이라고 한다. 

 

 

3️⃣ 인덱스 컬럼 추가

테이블 액세스 최소화를 위해 가장 일반적으로 사용하는 튜닝 기법

위와 같은 테이블에서 다음과 같은 sql문이 주어졌을 때

select /*+ index(emp emp_x01)*/ *
from emp
where deptno = 30
and sal >= 2000
;

 

조건에 만족하는 사원은 단 한명인데, 이를 찾기 위해 테이블을 6번 액세스 하였다.

이때 SAL 컬럼을 EMP_X01에 추가함으로써 큰 효과를 얻을 수 있다.

 

 

인덱스 스캔량은 줄지 않지만 테이블 랜덤 액세스 횟수를 줄여준다.

4️⃣ 인덱스 구조 테이블

IOT (Index-Organized Table)

오라클에서 랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성하는 것

5️⃣ 인덱스 클러스터 테이블 

클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조

클러스터  인덱스를 이용하는 방법

인덱스 대신 해시를 이용하는 방법

6️⃣ 부분 범위처리

1억 건짜리 테이블인데도 결과를 빨리 출력할 수 있는 이유는, DBMS가 데이터를 모두 읽어 한번에 전송하지 않고
먼저 읽는 데이터부터 일정량을 전송하고 멈추기 때문이다.

전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다

일정량씩 나누어 전송하는 것을 '부분범위 처리'라고 한다.

 

7️⃣ 인덱스 스캔 효율화

 

-- <조건절 1>
WHERE C1 = 'B'

-- <조건절 2>
WHERE C1 = 'B'
AND   C2 = 3

-- <조건절 3>
WHERE C1 = 'B'
AND   C2 >= 3

-- <조건절 4>
WHERE C1 = 'B'
AND   C2 <= 3

-- <조건절 5>
WHERE C1 = 'B'
AND   C2 BETWEEN 2 AND 3

-- <조건절 6>
WHERE C1 BETWEEN 'A' AND 'C'
AND   C2 BETWEEN 2 AND 3

위와 같은 조건절에서 어떤 조건이 가장 성능이 좋을까?
▶ 가장 성능이 좋다는 것은 스캔 범위가 좁다는 것을 뜻한다.

<조건절 2> 는 C1 = 'B'이면서 C2 = 3 인 부분이 시작점이고 C2 = 4인 곳을 만나는 순간 스캔을 멈춘다.
따라서 수평적 탐색의 화살표가 가장 짧다.

반대로 <조건절 6>은 C1이 BETWEEN으로 첫번째 조건부터 범위가 매우 넓다. 그러므로 6개 조건 중에 가장 화살표가 길다.

 

8️⃣ 인덱스 스캔 효율성

where c1 = '성'
and   c2 = '능'
and   c3 = '검'


where c1 = '성'
and   c2 = '능'
and   c4 = '선'

정렬되어있는 위의 그림과 두가지의 조건이 있다. 두 조건은 c1과 c2은 동일하고 c3와 c4에서 차이가 있다.

 

 

첫번째 조건은 '성능검'으로 시작하는 레코드를 검색하고 있기 때문에 빨간색 화살표처럼 길이가 짧다.

그러나 두번째는 '성능'으로 시작하고 네번째 컬럼이 '선'인 레코드를 검색하기 때문에 '성능'으로 시작하는 레코드를

모두 스캔한다. 따라서 첫번째 조건이 효율성이 좋다.

이러한 차이가 나타나는 이유는 인덱스 선행 컬럼이 조건절에 있냐 없냐의 차이다.

 

9️⃣ 액세스 조건과 필터 조건

인덱스 액세스 조건 : 인덱스 스캔 범위를 결정하는 조건절
인덱스 필터 조건 : 테이블로 액세스할지를 결정하는 조건절
테이블 필터 조건 : 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지를 결정한다.

이전 예제에서 봤을 때 첫번째 조건에서는 C1, C2, C3가 모두 인덱스 액세스 조건이다.
그러나 두번째 조건에서 인덱스 액세스 조건은 C1, C2이고, C4는 인덱스 필터 조건이다.

 

이를 보고 다음과 같이 튜닝의 목표를 정의할 수 있다.

• 인덱스 스캔 범위를 좁히기
• 액세스 하는 화살표 줄이기