친절한 SQL 튜닝_3장.인덱스 튜닝_1

 

인덱스 튜닝

 

3.1 테이블 액세스 최소화

 

3.1.1 테이블 랜덤 액세스

 

인덱스 ROWID는 물리적 주소? 논리적 주소?

 

인덱스 ROWID는 논리적 주소다. 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다. (프로그래밍에서 말하는) 포인터가 아니며, 테이블 레코드와 물리적으로 직접 연결된 구조는 더더욱 아니다.

 

메인 메모리 DB와 비교

 

메인 메모리 DB의 경우 인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스를 생성한다. 이때 인덱스는 오라클처럼 디스크 상의 주소정보를 갖는 게 아니라 메모리상의 주소정보, 즉 포인터(Pointer)를 갖는다. 따라서 인덱스를 경유해 테이블을 액세스하는 비용이 오라클과 비교할 수 없을 정도로 낮다.

 

I/O 메커니즘 복습

 

DBA(= 데이터파일 번호 + 블록번ㅇ호)는 디스크 상에서 블록을 찾기 위한 주소 정보다. 그렇다고 매번 디스크에서 블록을 읽을 수는 없다. I/O 성능을 높이려면 버퍼캐시를 활용해야 한다. 그래서 블록을 읽을 때는 디스크로 가기 전에 버퍼캐시부터 찾아본다. 읽고자 하는 DBA를 해시 함수에 입력해서 체인을 찾고 거기서 버퍼 헤더를 찾는다.

 

캐시에 적재할 때와 읽을 때 같은 해시 함수를 사용하므로 버퍼 헤더는 항상 같은 해시 체인에 연결된다. 반면, 실제 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱되는데, 그 메모리 주소값을 버퍼 헤더가 가지고 있다. 정리하면, 해싱 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터 버퍼 블록을 찾아간다.

 

인덱스로 테이블 블록을 액세스할 때는 리프 블록에서 읽은 ROWID를 분해해서 DBA 정보를 얻고, 테이블을 Full Scan 할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다.

 

인덱스 ROWID는 우편주소

 

디스크 DB(오라클, SQL Server 같은 일반 DBMS)가 사용하는 ROWID를 우편주소에, 메인 메모리 DB가 사용하는 포인터를 전화번호에 비유할 수 있다. 전화통신은 연결된 통신망을 이용하므로 전화번호를 누르면 곧바로 상대방과 통화할 수 있다. 하지만, 우편통신은 봉투에 적힌 대로 우체부 아저씨가 일일이 찾아다니는 구조이므로 전화와는 비교할 수 없이 느리다.

 

 

 

3.1.2 인덱스 클러스터링 팩터

 

클러스터링 팩터(Clustering Factor, 이하 ‘CF’)는 ‘군집성 계수’ 쯤으로 번역할 수 있는 용어로서, 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다. CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다. 예를 들어 『거주지역 = ‘제주’』에 해당하는 고객 데이터가 물리적으로 근접해 있으면 흩어져 있을 때보다 데이터를 찾는 속도가 빠르다.

 

 

 

3.1.3 인덱스 손익분기점

 

인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다. 따라서 읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다. Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 흔히 ‘인덱스 손익분기점’이라고 부른다.

 

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

 

1.Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식이다.

 

2.Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O 방식이다.

 

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

 

온라인 프로그램은 보통 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 무엇보다 중요하다. 조인도 대부분 NL 방식을 사용한다. 4장에서 설명하겠지만, NL 조인은 인덱스를 이용하는 조인 방식이다. 인덱스를 이용해 소트 연산을 생략함으로써 2절에서 설명할 부분범위 처리 방식으로 구현할 수 있다면, 온라인 환경에서 대량 데이터를 조회할 때도 아주 빠른 응답 속도를 낼 수 있다.

 

반면, 대량 데이터를 읽고 갱신하는 배치(Batch) 프로그램은 항상 전체범위 처리 기준으로 튜닝해야 한다. 즉, 처리대상 집합 중 일부를 빠르게 처리하는 것이 아니라 전체를 빠르게 처리하는 것을 목표로 삼아야 한다. 대량 데이터를 빠르게 처리하려면, 인덱스와 NL 조인보다 Full Scan과 해시 조인이 유리하다.

 

 

 

3.1.4 인덱스 컬럼 추가

 

테이블 액세스 최소화를 위해 가장 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가하는 것이다.

 

인덱스 스캔량은 줄지 않지만, 테이블 랜덤 액세스 횟수를 줄여주기 때문이다.

 

 

 

3.1.5 인덱스만 읽고 처리

 

인덱스만 읽어서 처리하는 쿼리를 ‘Covered 쿼리’라고 부르며, 그 쿼리에 사용한 인덱스를 ‘Covered 인덱스’라고 부른다.

 

Include 인덱스

 

Oracle엔 아직 없지만, SQL Server 2005 버전에 추가된 유용한 기능을 소개하려고 한다. 바로 Include 인덱스다. 이는 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능이다. 인덱스를 생성할 때 아래와 같이 include 옵션을 지정하면 된다. 컬럼은 최대 1,023개까지 지정할 수 있다.

 

 

CREATE INDEX EMP_X01 ON EMP (DEPTNO) INCLUDE (SAL)

emp_x02 인덱스는 deptno와 sal 컬럼 모두 루트와 브랜치 블록에 저장한다. 둘 다 수직적 탐색에 사용할 수 있다.

 

 

CREATE INDEX EMP_X02 ON EMP(DEPTNO, SAL)

emp_x01 인덱스는 sal 컬럼을 리프 블록에만 저장한다. 수직적 탐색에는 deptno만 사용하고, 수평적 탐색에는 sal 컬럼도 필터 조건으로 사용할 수 있다. sal 컬럼은 테이블 랜덤 액세스 횟수를 줄이는 용도로만 사용한다.

 

 

 

3.1.6 인덱스 구조 테이블

 

인덱스를 이용한 테이블 액세스가 고비용 구조라고 하니, 랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성하면 어떨까? 실제 그런 방법이 제공되는데, 오라클은 이를 ‘IOT(Index-Organized Table)’라고 부른다. 참고로, MS-SQL Server는 ‘클러스터형(Clustered) 인덱스’라고 부른다.

 

테이블을 찾아가기 위한 ROWID를 갖는 일반 인덱스와 달리 IOT는 그 자리에 테이블 데이터를 갖는다. 즉, 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있다. IOT에서는 ‘인덱스 리프 블록이 곧 데이터 블록’이다.

 

테이블을 인덱스 구조로 만드는 구문은 아래와 같다.

 

CREATE TABLE INDEX_ORG_T (
A NUMBER, 
B VARCHAR(10), 
CONSTRAINT INDEX_ORG_T_PK PRIMARY KEY (A) )
ORGANIZATION INDEX;

 

참고로, 일반 테이블은 ‘힙 구조 테이블’이라고 부른다. 테이블 생성할 때 대개 생략하지만, 아래와 같이 organization 옵션을 명시할 수도 있다.

 

CREATE TABLE INDEX_ORG_T (
A NUMBER, 
B VARCHAR(10),
 CONSTRAINT INDEX_ORG_T_PK PRIMARY KEY (A) )
ORGANIZATION HEAP;

 

일반 힙 구조 테이블에 데이터를 입력할 때는 랜덤 방식을 사용한다. 즉, Freelist로부터 할당 받은 블록에 정해진 순서 없이 데이터를 입력한다. 반면, IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력한다.

 

 

 

3.1.7 클러스터 테이블

 

인덱스 클러스터 테이블

 

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

 

심지어 여러 테이블 레코드를 같은 블록에 저장할 수도 있는데, 이를 ‘다중 테이블 클러스터’라고 부른다. 일반 테이블은 하나의 데이터 블록을 여러 테이블이 공유할 수 없음을 상기하기 바란다.

 

이름 때문에 SQL 서버나 Sybase에서 말하는 ‘클러스터형 인덱스(Clustered Index)’와 같다고 생각하지 모르지만 클러스터형 인덱스는 오히려 IOT에 가깝다. 오라클 클러스터는 키 값이 같은 데이터를 같은 공간에 저장해 둘 뿐, IOT나 SQL Server의 클러스터형 인덱스처럼 정렬하지 않는다.

 

인덱스 클러스터 테이블은 구성하려면 먼저 아래와 같이 클러스터를 생성한다.

 

CREATE CLUSTER C_DEPT# ( DEPTNO NUMBER(2) ) INDEX;

 

그리고 클러스터에 테이블을 담기 전에 아래와 같이 클러스터 인덱스를 반드시 정의해야 한다. 왜냐하면, 클러스터 인덱스는 데이터 검색 용도로 사용할 뿐만 아니라 데이터가 저장될 위치를 찾을 때도 사용하기 때문이다.

 

CREATE INDEX C_DEPT#_INX ON CLUSTER C_DEPT#;

 

클러스터 인덱스를 만들었으면 아래와 같이 클러스터 테이블을 생성한다.

 

CREATE TABLE DEPT (
DEPTNO NUMBER(2) NOT NULL,
DNAME VARCHAR2(14) NOT NULL, 
LOC VARCHAR2(13) )
CLUSTER C_DEPT#( DEPTNO );

 

클러스터 인덱스도 일반 B*Tree 인덱스 구조를 사용하지만, 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫 번째 데이터 블록을 가리킨다는 점이 다르다. 즉, 일반 테이블에 생성한 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖지만, 클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖는다. 따라서 클러스터 인덱스의 키 값은 항상 Unique하다(= 중복 값이 없다).

 

이런 구조적 특성 때문에 클러스터 인덱스를 스캔하면서 값을 찾을 때는 랜덤 액세스가 (클러스터 체인을 스캔하면서 발생하는 랜덤 액세스를 제외하고) 값 하나당 한 번씩 밖에 발생하지 않는다. 클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는 게 핵심 원리다.

 

 

 

해시 클러스터 테이블

 

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

 

해시 클러스터 테이블을 구성하려면 먼저 아래와 같이 클러스터를 생성한다.

 

CREATE CLUSTER C_DEPT# ( DEPTNO NUMBER(2) ) HASHKEYS 4;

 

그리고 아래와 같이 클러스터 테이블을 생성한다.

 

CREATE TABLE DEPT (
DEPTNO NUMBER(2) NOT NULL, 
DNAME VARCHAR2(14) NOT NULL, 
LOC VARCHAR2(13) )
CLUSTER C_DEPT#( DEPTNO );

출처 : 친절한 SQL 튜닝 - 조시형 지음