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

인덱스 ROWID = 논리적 주소

DBA = 데이터파일번호 + 블록번호 : 디스크 상에서 블록을 찾기 위한 주소 정보

버퍼캐시 : I/O 성능을 높이기 위해 사용된다

 

인덱스를 이용해 테이블 블록을 찾아가는 과정

 

인덱스를 통해 찾은 ROWID를 통해 데이터 가져올 때 버퍼캐시에서 데이터를 찾기 위해 DBA(데이터파일번호 + 블록번호)를 해시 함수에 입력하여 해시 체인을 찾은 후 해시 체인에서 버퍼 헤더를 찾는다

버퍼 헤더는 메모리의 주소값을 가지고 있기 때문에 그 포인터를 이용해 버퍼 블록을 찾는다

 

인덱스로 테이블 블록을 엑세스할 때는 리프 블록에서 읽은 ROWID를 분해하여 DBA 정보를 얻는다

테이블 Full Scan을 할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다

 

ROWID가 가리키는 테이블 블록을 버퍼캐시에서 먼저 찾고, 못 찾으면 디스크에서 블록을 읽어 버퍼캐시에 적재한 후 읽는다

 

인덱스 클러스터링 팩터(CF)

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

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

 

인덱스 클러스터링 팩터가 가장 좋은 상태

 

인덱스 클러스터링 팩터가 가장 안 좋은 상태

 

CF가 좋은 컬럼에 생성한 인덱스가 검색 효율이 좋은 이유

인덱스 ROWID로 테이블을 액세스할 때, 래치 획득과 해시 체인 스캔 과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터를 바로 해제하지 않고 일단 유지하는데, 이를 버퍼 Pinning이라고 한다

이를 통해 다음 인덱스 레코드를 읽을 때 같은 테이블 블록인 경우 래치 획득과 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있어 논리적인 블록 I/O 과정을 생략할 수 있기 땜누에 블록 I/O가 적게 발생한다

 

인덱스 손익 분기점

Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점

 

인덱스를 이용한 테이블 액세스가 Table Full Scan 보다 더 느려지게 만드는 가장 핵심적인 두 가지 요인

• Table Full Scan은 시퀀셜 액세스이나 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식
• Table Full Scan은 Multiblock I/O인 반면 인덱스 ROWID를 이용한 테이블 액세스는 SingleBlock I/O 방식

인덱스 손익 분기점은 보통 5 ~ 20%의 낮은 수준에서 결정된다(CF에 따라 크게 달라진다)

 

온라인 프로그램 : 소량 데이터를 읽고 갱신, 인덱스를 효과적으로 활용하는 것이 중요

배치 프로그램 튜닝 : 항상 전체범위 처리 기준으로 튜닝 필요, 처리대상 집합 중 일부를 빠르게 처리하는 것이 아닌 전체를 빠르게 처리하는 것을 목표로 한다

 

Covered 쿼리 : 인덱스만 읽어서 처리하는 쿼리

이때 사용하는 인덱스를 Covered 인덱스라고 부른다

 

Include 인덱스

 

include로 포함된 컬럼은 리프 블록에만 저장한다

따라서 수직탐색에는 사용할 수 없다

다만, 수평탐색에서는 필터조건으로 사용할 수 있다(테이블 랜덤 액세스 횟수 줄이는데 사용 가능)

위에 두 인덱스가 있을 때 인덱스 스캔량은 emp_x02가 더 적다, sal 도 액세스 조건으로 사용하기 때문

emp_x02는 소트 연산 생략이 가능하지만 emo_x01은 불가능하다

include 인덱스는 랜덤 액세스를 줄이는 용도로 개발되었다

 

인덱스 구조 테이블(오라클에서는 IOT(Index-Organized Table), MS 는 클러스터형 인덱스)

테이블을 인덱스 구조로 만드는 구문

 

일반 테이블은 heap 구조

힙구조는 데이터를 입력할 때 랜덤 방식을 사용(순서 없음), IOT 구조는 인덱스 구조 테이블이므로 정렬 상태를 유지(인위적으로 클러스터 팩터를 좋게 만드는 방법)

 

클러스터 테이블

인덱스 클러스터 테이블

클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조, 한 블록에 다 담을 수 없을 때는 새로운 블록을 할당해서 클러스터 체인으로 연결

여러 테이블 레코드를 같은 블록에 저장할 수 있는데, 이런 경우는 “다중 테이블 클러스터” 라고 부른다

일반 테이블은 하나의 데이터 블록을 여러 테이블이 공유할 수 없음

 

인덱스 클러스터 테이블 구성

1. 클러스터 생성

 

2. 인덱스 생성

 

3. 클러스터 테이블 생성

 

클러스터 인덱스도 B*Tree 인덱스 구조이지만 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫 번째 데이터 블록을 가리킨다

일반 테이블 인덱스 레코드는 테이블 레코드와 1:1 이지만 클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖는다

클러스터 인덱스의 키 값은 항상 Unique 하다

 

해시 클러스터 테이블

인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다

클러스터 생성 시 아래와 같이 hashkeys ‘숫자’ 를 입력하여 생성한다

 

부분 범위 처리

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

 

정렬 조건이 있을 때 부분 범위 처리

정렬이 있는 경우 모든 데이터를 읽어 정렬을 마치고 나서야 전송을 시작할 수 있기 때문에 전체 범위 처리가 된다

단, 정렬절에 있는 컬럼이 선두인 인덱스가 존재한다면 부분 범위 처리가 가능하다.(인덱스가 정렬되어있기 때문에 정렬을 하지 않아도 되므로 가능)

 

모든 DBMS는 데이터를 조금씩 나누어 전송한다. 다만 부분 범위 처리를 프로그램에 적용하고 안하고는 개발자의 목이다

 

부분 범위 처리가 의미가 있기 위해서는 멈출 수 있어야 한다

DB에 직접 접속하는 2-Tier 환경에서는 구현이 가능하지만, 클라이언트와 DB 사이에 WAS, AP 서버 등 이 존재하는 n-Tier 환경에서는 클라이언트가 특정 DB 커넥션을 독점할 수 없기 때문에 SQL 조회 결과를 클라이언트에게 모두 전송하고 커서를 닫아야 한다

 

배치 I/O

읽는 블록마다 건건이 I/O Call을 발생시키는 것이 아닌 테이블 블록에 대한 디스크 I/O Call을 미루었다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 방식

* 데이터 순서를 보장할 수 없다