인덱스 튜닝

조시형님의 친절한 SQL 튜닝을 읽고 개인적으로 공부한 내용을 정리한 글입니다.

---

인덱스를 통해 레코드를 찾아가는 과정

먼저, 인덱스를 활용할 수 있는 인덱스 테이블이 캐싱되어 있는지 확인한다.

 

캐싱되어 있다면, 인덱스 테이블에서 인덱스 조건에 맞는 레코드를 찾는다.

 

그러면, 해당 조건에 맞는 실제 디스크 상 레코드의 위치 정보를 파악할 수 있다.(ROWID)

 

실제 접근해야하는 디스크 상 레코드의 위치는 일반적으로 블록으로 관리하므로, 해당 블록이 이미 버퍼에 캐싱되어 있는지 확인하고 있다면 캐싱된 블록에 접근해서 가져온다.

 

만약, 그렇지 않다면 실제 디스크로 접근한다.

 

버퍼에서는 테이블 블록 위치 값을 해싱하여 고유한 값으로 변경 후 관리한다. 

그 과정에서, 해싱 계산이 필요하므로 바로 주 메모리 위치를 가리키는 포인터 방식보다는 느리다.

포인터는 해당 레코드의 주소값을 가리키고 있다면, 인덱스는 해당 레코드가 위치한 블록 정보를 가리키기 때문이다.

 

클러스터링 인덱스가 효율적인 이유

일반적인 인덱스는 인덱스 상으로는 정렬되어 있지만, 

 

각 인덱스가 가리키는 블록의 위치는 정렬되어 있지 않으므로 인덱스 레인지 스캔을 하게되면

 

랜덤 I/O 가 많이 발생한다.

 

인덱스에 따라 실제 테이블의 물리적 주소도 연속적으로 저장되는 인덱스를 클러스터링 인덱스라고 한다.

 

클러스터링 인덱스를 통해 탐색하게 되면 랜덤 블록 I/O를 생략할 수 있다.

 

💡 DBMS는 인덱스가 가리키는 테이블 블록을 엑세스 한 후, 일단 유지해둔다. 그리고 다음 인덱스가 가리키는 테이블 블록과 이전에 엑세스 했던 테이블 블록이 일치하면, 테이블 블록에 해당하는 값을 해싱해서 해싱된 값이 버퍼 캐시에 있는지 확인하는 과정을 생략하고 바로 테이블을 읽을 수 있다.

 

 

인덱스 스캔은 테이블 풀 스캔보다 항상 효율적?

 

그렇지 않다.

 

테이블 풀 스캔의 경우 시퀀셜 엑세스이고 인덱스 스캔은 랜덤 엑세스이다.

 

테이블 풀 스캔의 경우는 조건절에 영향을 받지 않고 일정한 성능을 보인다.

 

하지만, 인덱스 스캔은 조건절에 해당하는 레코드가 많이 발견될수록, 인덱스와 연결된 블록을 랜덤 엑세스 해야하는 횟수가 늘어난다.

 

데이터베이스의 성능은 랜덤 I/O를 줄이는 것이 가장 중요한 포인트인데, 인덱스 스캔의 경우 소량의 데이터는 약간의 랜덤 I/O만으로 결과를 가져올 수 있지만 해당하는 인덱스 레코드가 많아질수록 랜덤 I/O 횟수가 증가하게되므로 비효율적일 수 있다.

 

 

인덱스를 추가함으로서 랜덤 I/O를 줄이는 방법

 

 

위와 [DEPTNO + JOB] 으로 인덱스를 구성한 테이블이 있다고 가정해보자.

 

만약, 

 

SELECT
	*
FROM
	emp
WHERE
	deptno = 30
AND
	sal >=2000

 

위 쿼리가 수행된다면,

 

 

인덱스 테이블의 deptno 30 인 레코드를 인덱스 레인지 스캔을 해서

 

직접 테이블 레코드를 찾아가서 sal 이 2000 이상인지 확인할 것이다.

 

총 4번의 랜덤 I/O 가 발생한다.

 

하지만, 인덱스에 sal 컬럼을 추가한다면

 

 

deptno 가 30 인 인덱스 레코드를 레인지 스캔하는 범위는 달라지지 않지만

 

인덱스 테이블에서 sal 값도 체크할 수 있기 때문에 한번의 랜덤 I/O를 수행하면 된다.