3장 인덱스 튜닝 (1)

3장 인덱스 튜닝

3.1 테이블 엑세스 최소화

SQL 튜닝은 랜덤I/O를 줄이는 것.

랜던I/O를 최소화 하는 방법을 알아보자.

3.1.1 테이블 랜덤 액세스

• 아무리 데이터가 많아도 인덱스를 사용하면 금방 조회된다
• 어쩔때는 테이블 전체를 스캔하는 것보다 인덱스가 더 느릴때도 있다.

왜 그럴까? 이 질문에 대한 대답을 찾아보자.

인덱스를 사용하는 이유는 ROWID(테이블에 있는 레코드를 찾기 위한 주소값)를 얻는데에 있다.

즉 인덱스를 사용하면 데이터가 실제로 있는 물리 주소가 아닌 물리 주소를 찾을 수 있는
주소를 얻는다. 반대로 테이블 Full Scan 시에는 직접적인 물리 주소를 얻기 때문에 위와 같은 문제점들이 나타난다.

• ROWID
  • 논리적 주소(즉 메모리 주소가 아니다)
  • 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담고 있다
  • 즉, DBA(데이터파일번호 + 블록번호)를 가지고 있다.

• I/O 메커니즘
  • 인덱스를 통해 ROWID를 찾고 그 안에서 DBA를 읽어 온다
  • 캐시 검색
    • DBA를 해시 함수에 넣어서 해시 체인을 찾는다
    • 해시 체인 안에서 버퍼 헤더를 찾는다
    • 버퍼 해더가 있다면 hit
  • 캐시에 없다면 DBA를 통해 디스크 램덤 I/O

데이터가 캐싱돼있더라도 매번 DBA해싱과 래치 획득 과정을 반복한다. 동시 액세스가 심할때는 캐시버퍼 채인 래치와 버퍼 Lock 경합까지 발생. ROWID를 통한 테이블 액세스는 생각보다 고비용 구조

또한, 이러한 구조는 메인메모리DB와 비교할 때 히트레이트가 아무리 높아도 메모리DB의 속도를 따라갈 수 없는 구조.

3.1.2 인덱스 클러스터링 팩터

CF 좋은 케이스

CF 안좋은 케이스

• 클러스터링 팩트
  • 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도.
  • 예) "거주지역 = 제주"에 해당하는 데이터가 물리적으로 근접해 있으면 데이터를 찾는 속도가 빠름
  • 가능한 이유는 오라클은 버퍼 Pinning 기능을 통해 래치 획득과 해시 체인 스캔 과정을 통해 찾아간 테이블 블록에 대한 포인터를 바로 해제하지 않고 가지고 있기 때문.
  • 따라서 인덱스는 레인지 스캔을 하기 때문에 비슷한 데이터가 같은 블록에 위치한다면 바로 물리적인 주소를 획득할 수 있다
  • 

3.1.3 인덱스 손익 분기점

위에서 나왔듯이 인덱스 ROWID를 이용한 데이터 액세스는 생각보다 고비용 구조다. 따라서 읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다. 이 점을 손익분기점이라고 한다.

• 손익분기점이 발생하는 두가지 이유
  • Table Full Scan은 시퀀셜 액세스 VS. 인덱스 ROWID 방식은 랜덤 액세스
  • Table Full Scan은 Multi Block I/O VS. 인덱스 ROWID 방식은 Single Block I/O

• 인덱스 손익분기점과 버퍼캐시 히트율
  • 데이터량이 많아질수록 손익분기점은 더 낮아진다
  • 데이터량이 많아질수록 캐시 히트율이 낮아지기 때문이다.
  • 데이터량이 많아질수록 CF가 안좋아지기 때문이다.
  • 따라서, 손익분기점을 보고 인덱스를 쓸지 테이블 전체 스캔을 할지 결정해야 한다.(항상 인덱스 방식이 좋은건 아니다!!)

온라인 프로그램 튜닝 vs. 배치 프로그램 튜닝

• 온라인프로그램 => 소량의 데이터를 읽거나 갱신 => 인덱스와 NL 조인 유리
• 배치 프로그램 => 한번에 전체 데이터를 처리 => Full Scan과 해시 조인 유리
  • Full Scan 튜닝을 위하여 파티셔닝과 병렬처리 가능

3.1.4 인덱스 컬럼 추가

인덱스에 컬럼을 추가하는 튜닝 기법.

쓸데없이 테이블을 여섯번이나 엑세스.

인덱스를 새로 만드는건 매우 비효율적일 수 있기 때문에 컬럼을 하나 추가하는 것으로 튜닝.

테이블 액세스 횟수가 1회로 준다.

• 실제 사례 학습

'서비스 번호' 단일 컬럼으로 구성된 인덱스를 사용하기 때문에 매우 비효율적인 액세스 발생.

이 인덱스에 '사용 여부' 컬럼 추가.

3.1.5 인덱스만 읽고 처리

필터 조건에 의해 버려지는 레코드가 거의 없는데도 불구하고 속도를 개선해야 하는 상황이라면?

이때는 쿼리에 사용된 컬럼을 모두 인덱스에 넣어서 테이블 액세스가 아예 발생하지 않도록 할 수 있다.

이를 Covered 쿼리하고 한다. 그리고 이때 사용한 인덱스를 Covered 인덱스라 한다.

include 인덱스

Oracle에는 없지만 SQL Server 2005 버전에 추가된 기능.

미리 지정한 컬럼을 리프 레벨에만 함께 저장하여 효율을 높이는 방식.

create index emp_x01 on emp (deptno) include (sal)

리프 레벨에만 포함되기 때문에 수직적 탐색에는 당연히 사용될 수 없고 테이블 랜덤 엑세스를 줄이는 용도로만 사용된다.

3.1.6 인덱스 구조 테이블

ROWID 방식이 고비용 구조이기 때문에 테이블 자체를 인덱스 형태로 구성한 방식.

오라클에서는 IOT(Index-Organized Table)이라 부르고

SQL Server에서는 클러스터형(Clustered) 인덱스라 부른다.

IOT에서는 인덱스 리프 블록이 곧 데이터 블록.

만드는 방법

• 장점
  • CF가 매우 좋다. 100%
  • 따라서 BETWEEN이나 부등호 조건으로 넓은 범위를 읽을 때 유리
  • 데이터 입력과 조회 패턴이 서로 다른 테이블에도 유용
  • 책의 예처럼 강제로 CF를 좋게하여 인덱스를 쓰더라도 느린 경우에 속도를 올릴 수 있다.

예) 사원 100명. 실적등록은 일자별로 입력. 한블록에 100개씩 하루에 한블록 생성. 조회는 사원번호순으로 진행. 인덱스를 걸더라도 1년치를 조회하면 365 블럭에 모두 액세스. 하지만 사번으로 IOT구성하면 CF가 좋아져서 4번 액세스로 모든 값 가져올 수 있음.

3.1.7 클러스터 테이블

인덱스 클러스터 테이블

키값이 같은 레코드를 같은 블럭에 저장. 블록을 넘어서면 체인으로 묶는다.

여러 테이블을 같은 블록에 저장할 수 있는데 이를 다중 테이블 클러스터라 한다.

클러스터를 생성하고 인덱스도 필수적으로 생성한다. 데이터 저장위치를 판별하기 위해서다.

create cluster c_dept# (deptno number(2)) index;

create index c_dept#_idx on cluster c_dept#;

클러스터 인덱스도 B-Tree로 구성되지만 리프노드는 해당 키 값을 저장하는 첫번째 테이터 블록을 가리킨다. 따라서 인덱스와 레코드의 관계가 1:M 이다. 그리고 키값이 항상 유니크하다.

또한, 랜덤 액세스가 값 하나당 한번씩만 발생하고(체인 스캔 제외) 클러스터에 도달해서는 시퀀셜로 스캔하기 때문에 넓은 범위를 읽더라도 효율적이다.

해시 클러스터 테이블

인덱스 대신에 해시를 사용한다.