인덱스 튜닝 (1) - 기본 이론

 

1. 기본 내용

 

(1) 인덱스 ROWID는 논리적 주소이다.

 

TABLE ACCESS BY INDEX ROWID는 인덱스로 스캔한 후에 테이블을 엑세스하는 과정이다.

친절한 SQL 튜닝

 

인덱스를 스캔하는 이유는 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 거기서 테이블 레코드를 찾아가기 위한 주소값, ROWID를 얻으려는 데 있다.

ROWID가 데이터파일 번호, 오브젝트 번호, 블록 번호 같은 물리적 요소로 구성돼 있기 때문에 물리적 주소로 생각할 수 있지만, 사실은 논리적 주소이다. 

 

프로그래밍 언어에서 포인터는 메모리 주소값을 담는 변수이다. 이를 통해 데이터를 찾아가는데 비용은 0에 가깝다 물리적으로 직접 연결된 구조와 다름없다.

 

인덱스 ROWID는 포인터가 아니라, 지시봉에 가깝다. 디스크상에서 테이블 레코드를 찾아가기 위한 정보만을 담을 뿐이다.

 

(2) 메인 메모리 DB와 비교 

 

메인 메모리 DB란 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB라고 할 수 있다.

데이터베이스 시스템이라면 버퍼캐시 히트율이 99% 이상이다. 디스크를 경유하지 않고 대부분 메모리에서 읽는다는 뜻이다.

그렇지만 메인 메모리 DB만큼 빠르지 않다. 특히 대량 데이터를 인덱스로 엑세스할 때는 많은 차이가 난다.

 

메인 메모리 DB는 인스턴스 시동시 디스크에 저장된 데이터를 버퍼캐시에 로딩 후 인덱스를 생성 -> 인덱스가 메모리 주소를 가져서 빠르게 접근 가능 

 

반면 오라클은 데이터 블록이 수시로 버퍼캐시에서 밀려났다가 다시 캐싱됨 -> 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 포인터로 직접 연결할 수 없는 구조이다 -> 메모리 주소 정보가 아닌 디스크 주소 정보를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다. 

 

즉, 캐싱이 된다고 해도, 인덱스가 메모리에 저장된 블록에 직접 연결되는 게 아니기 때문에 속도 차이가 난다!

또하, DBA 해싱과 래치 획득 과정을 반복해야 한다. 동시 엑세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생한다. (인덱스 ROWID를 이용한 테이블 엑세스는 생각보다 고비용 구조임) 

 

(3)  I/O메커니즘 복습

 

DBA는 디스크 상에서 블록을 찾기 위한 주소 정보다. 매번 디스크에서 블록을 읽을 수 없음으로, I/O성능을 높이려면 버퍼캐시를 활용해 한다. 

블록을 읽을 때는 디스크로 가기 전에 버퍼캐시부터 찾아본다. DBA를 해시 함수에 입력해서 해시 체인을 찾고 거기서 버퍼 헤더를 찾는다. 

캐시를 적재할 때와 읽을 때 같은 해시 함수를 사용하므로 버퍼 헤더는 항상 같은 해시 체인에 연결된다.

반면, 실제로 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱된다. 그 메모리 주소값을 버퍼 헤더가 가지고 있다.

 

*DBA는 물리적 주소(불변) -> 해싱 ->  버퍼체인 + 버퍼헤더 연결 (불변) -> 버퍼헤더가 가르키는 버퍼 블록 주소(가변)

 

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

 

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

 

클러스터링 팩터(CF)는 군집성 계수로 번역될 수 있다. 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 모여이는 정도를 의미한다. 

CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다. -> 특정 데이터가 물리적으로 근접해 있으면 데이터를 찾는 속도가 빠르다.

즉 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 얼마나 일치하는 가이다. 

 

* 같은 I/O가 발생하는데 성능이 좋은 이유

• ROWID로 엑세스 시 테이블 블록에 대한 포인터를 유지함 (버퍼 Pinning)
• 인덱스 레코드 읽었는데, 직전과 같은 블록을 가리킨다면, 래치와 체인 과정 생략하고 바로 읽음! (논리적 IO과정 생략)

(5) 인덱스 손익분기점 

 

친절한 SQL 튜닝

읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다. 

Index Range Scan에 의한 테이블 엑세스가 Table Full Scan보다 느려지는 지점을 인덱스 손익분기점이라고 부른다.

 

인덱스를 활용한 데이터 추출이 느려지는 원인

1. 인덱스는 ROWID를 이용한 랜덤 엑세스 방식
2. 인덱스는 Single Block I/O 방식이다.

위와 같은 이유로 손익분기점은 보통 5~20%의 낮은 수준에서 결정된다. 또한 CF에 많은 영향을 받는다.

친절한 SQL 튜닝

*일반적으로 5~20% 손익분기점은 10만 건 이내 많아야 100만 이내 테이블에 적용된다. 1000만 건 수준의 큰테이블에선 손익분기점이 더 낮아진다.

 

(5) 온라인 프로그램 튜닝 VS 배치 프로그램 튜닝

 

온라인 프로그램

• 소량의 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 중요 
• 조인도 대부분 NL방식을 사용한다. 
• 인덱스를 사용해 소트 연산을 생략함으로써 부분범위 처리 방식을 구현한다면, 대량 데이터 조회를 빠르게 해낼 수 있다.

배치 프로그램

• 대량 데이터를 읽고 갱신한다. 
• 항상 전체범위 처리를 기준으로 생각해야 한다. 
• 대량 데이터를 빠르게 처리하려면, Full Scan과 해시 조인이 유리하다. 

 

대량 배치 프로그램에선 인덱스보다 Full Scan이 효과적이지만, 초대용량 테이블을 Full Scan하는 것 또한 큰 부담이므로, 

이럴 땐, 파티션 활용 전략이 매우 중요한 튜닝요소이다. 

파티션 테이블에도 인덱스를 사용할 수 있지만, 월 단위, 주단위 심지어 2~3일 데이터 조회할 때도 Full Scan이 유리할 수 있다. 

테이블을 파티셔닝 하는 이유는 Full Scan을 빠르게 처리하기 위함이다! 

 

*모든 성능 문제를 인덱스로 해결하려 하지말자, 인덱스는 다양한 튜닝 도구 중 하나일 뿐이며, 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾고자 할 때 주로 사용한다. 

---

2. 인덱스 칼럼 추가

 

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

 

ex 1) EMP 테이블에 현재 PK 이외 DEPTNO + JOB 순으로 구성한 EMP_X01 인덱스 하나만 있는 상태에서 아래 쿼리를 수행한다고 해보자 

SELECT /*+ index(emp emp_x01) */ *
FROM emp
WHERE deptno = 30
AND sal >= 2000

 

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

친절한 SQL 튜닝

 

인덱스 구성을 변경하거나, 새로운 인덱스를 만드는 일은 실무에서 적용하기 쉽지 않다.

위와 같은 경우 기존 인덱스에 새로운 칼럼을 추가하는 방식을 사용하자 

친절한 SQL 튜닝

 

인덱스 스캔량은 줄지 않지만, 랜덤 엑세스 횟수를 줄일 수 있다.

 

ex 2) 

SELECT 렌탈관리번호, 고객명, 서비스관리번호 -- 이하생략
FROM 로밍렌탈
WHERE 서비스번호 like '010%'
AND 사용여부 = 'Y'

친절한 SQL 튜닝

 

서비스번호 단일 칼럼으로 구성된 인덱스를 사용한 쿼리이다. 

스캔을 통해 얻은 Rows 266,476건 (이만큼 랜덤 엑세스함) -> 랜덤 엑세스를 하며, 265,957(266968 - 1011)블록을 읽었다.

이는 전체 블록 I/O에 99.6%를 차지한다.

 

하지만, 최종 결과 Rows는 1909이며, Filter 전 인덱스를 통해 너무 많은 Row를 읽는 것이 문제이다. 

따라서, WHERE 조건절에 또 다른 조건인 사용여부를 인덱스에 추가해보자 

친절한 SQL 튜닝

인덱스를 통해 테이블을 1,909번 방문했고, 모두 결과집합에 포함되었다. (불필요한 테이블 엑세스 줄임) 

총 블록 I/O도 2902로 크게 준 것을 확인 할 수 있다. 

 

---

3. 인덱스만 읽고 처리 

 

테이블 랜덤 엑세스가 아무리 많아도 필터 조건에 의해 버려지는 레코드가 거의 없다면 비효율이 없다.

예를 들어 쿼리에 부서번호 단일 컬럼으로 구성된 인덱스를 사용하면, 비효율이 없는데 인덱스 스캔 과정에서 얻은 데이터가 많다면 그만큼 테이블 랜덤 엑세스가 많이 발생하므로 성능이 느릴 수 밖에 없다. 

 

이때 반드시 성능을 개선해야 한다면, 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 엑세스 자체를 막을 수 있다.

이를 Covered 쿼리라고 부른다. 

SELECT 부서번호, SUM(수량)
FROM 판매집계
WHERE 부서번호 LIKE '12%'
GROUP BY 부서번호;

 

-> 수량과 부서번호 전체에 인덱스를 걸어버리면, 테이블에 엑세스하지 않는다! 

 

이 방법은 효과는 좋지만, 추가해야 할 컬럼이 많아 적용하기 곤란한 경우가 많다.

 

---

4. 인덱스 구조 테이블

 

인덱스를 이용한 테이블이 고비용 구조라 하니, 랜덤 엑세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성해보자 

오라클은 이를 IOT라고 부른다. (MS-SQL Server는 클러스터형 인덱스라 부름)

 

테이블을 찾아가기 위한 ROWID를 갖는 일반 인덱스와 달리 IOT는 인덱스 리프 블록이 곧 테이블 블록이다.

친절한 SQL 튜닝

 

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

create table index_org_t (a number, b varchar(10), constraint index_org_t_pk primary key(a))
organization index;

 

* 일반 테이블은 힙 구조 테이블이라 부름! 

 

일반 힙 구조 테이블에 데이터를 입력할 때는 랜덤 방식을 사용한다. 

 

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

 

IOT의 장점

• 인위적으로 클러스터링 팩터를 좋게 만드는 방법 
• 랜덤 엑세스가 아닌 시퀀셜 방식으로 데이터 엑세스 -> BETWEEN이나 부등호 조건 넓은 범위 유리

---

5. 클러스터 테이블 

 

클러스터 테이블에는 인덱스 클러스터와 해시 클러스터 두 가지가 있다. 

 

5.1 인덱스 클러스터 테이블 

 

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

한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당하고 클러스터 체인으로 연결한다. 

여러 테이블 레코드를 같은 블록에 저장할 수도 있다 ! 이를 다중 테이블 클러스터라 부른다.

 

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

 

친절한 SQL 튜닝

 

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

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

 

그리고 클러스터에 테이블을 담기 전에 아래와 같이 클러스터 인덱스를 반드시 정의해야 한다.

클러스터 인덱스는 데이터 검색 + 저장될 위치 찾을 때도 사용하기 때문이다.

create index c_dept#_idx 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하다)

 

친절한 SQL 튜닝

 

이와 같은 구조적 특징 때문에, 클러스터 인덱스를 스캔하면서 값을 찾을 때는 랜덤 엑세스가 

값 하나당 한 번 씩 밖에 발생하지 않고, 클러스터에 도달해서는 시퀀셜 방식으로 스캔하므로, 넓은 범위를 읽더라도 비효율이 없다는 게 핵심이다. 

친절한 SQL 튜닝

 

5.2 해시 클러스터 테이블

 

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

친절한 SQL 튜닝

 

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

create cluster c_dept# (deptno number(2)) hashkeys 4;

create table dept(
	deptno number(2) not null
    , dname varchar(14) not null
    , loc varchar(13) 
    )
cluster c_dept#(deptno);

 

해시 클러스터를 조회할 때 실행계획은 아래와 같다. 

 

친절한 SQL 튜닝

 

정리

 

- 테이블 랜덤 엑세스가 성능에 미치는 영향, 이를 최소화하기 위해 인덱스 칼럼을 추가하고, 저장 구조 개선 방법