인덱스 활용 최적화

B+-Tree는 특성상 어떤 리프 페이지에 접근하든 거의 동일한 비용이 듭니다.
B+-Tree를 사용할 때 가장 큰 비용이 드는 부분은 Key Range의 시작부터 끝까지 인덱스 리프 노드를 따라 진행하는 스캔과
여기에 대응하는 테이블 데이터의 스캔입니다.
대부분의 DBMS는 페이지 단위로 I/O를 수행합니다.
즉, 하나의 레코드에서 하나의 컬럼만 읽으려해도 레코드가 속한 페이지 전체를 디스크로부터 읽어옵니다.
따라서 질의 성능을 좌우하는 가장 중요한 성능 지표는 I/O를 수행하는 페이지 개수입니다.
디스크 I/O는 메모리 액세스에 비해 비용이 아주 큽니다.
결국, 디스크 I/O를 최소화하고 대부분의 연산을 DB 버퍼에서 처리할 수 있도록
질의 처리 과정에서 액세스하는 페이지 수를 최소화하는 것이 튜닝의 핵심입니다.
액세스하는 페이지 수가 적으면 물리적으로 디스크에서 읽어야 할 페이지 수도 줄기 때문에
DB 버퍼 히트율(DB buffer hit ratio)이 높아져 DB의 전체적인 성능이 향상됩니다.
그럼 인덱스 스캔 과정에서 액세스해야 할 페이지 수를 줄일 수 있는 기법을 알아보도록 하겠습니다.

Key Filter

Key Filter는 Key Range에는 포함되지 않지만 인덱스 키로 처리할 수 있는 조건입니다.
이러한 Key Filter가 WHERE 조건절에 포함되면 데이터 페이지에 접근하는 횟수를 줄일 수 있습니다.
데이터 페이지는 랜덤 액세스로 읽기 때문에 인덱스 페이지 스캔보다 많은 비용이 듭니다.
따라서, WHERE 절에 Key Filter를 포함하면 성능 향상에 유리합니다.
또한, Data Filter가 Key Filter로 적용될 수 있도록 인덱스에 컬럼을 추가하는 것도 방법이 될 수 있습니다.
예를 들어, user 테이블에 (groupid, name)으로 구성된 인덱스 idx_1이 있는 상태에서 아래 질의를 수행한다고 가정합니다.

SELECT * FROM user
WHERE groupid = 10 AND age > 40;

만약, 사용하는 인덱스로 SELECT 질의에 대한 결과를 모두 얻을 수 있는 상황이라면,
데이터 페이지에 저장되어 있는 레코드를 읽어오지 않아도 인덱스 키의 값만으로 결과를 얻을 수 있습니다.
이와 같이, 인덱스가 하나의 질의를 모두 '커버'한 경우를 커버링 인덱스(Covering Index)라고 합니다.

SELECT a, b FROM tb1
WHERE a > 1 AND a < 5
AND b < 'K'
ORDER BY b;

위의 SQL 질의에서 커버링 인덱스를 적용할 수 있습니다.
질의에 사용한 컬럼은 a, b 뿐이고 모두 인덱스 컬럼이기 때문입니다.
커버링 인덱스는 데이터 페이지를 읽지 않는다는 점.
해당 질의를 자주 사용하면 인덱스가 DB 버퍼에 캐시되어 있는 가능성이 높다는 점에서
디스크 I/O를 줄이는 데 큰 역할을 합니다.
따라서, 레코드 크기에 비해 인덱스 키의 크기가 작고, 커버링 인덱스를 이용하는 질의가 자주 수행되는 것이 확실하다면,
커버링 인덱스를 사용하여 SELECT 질의 성능을 크게 향상시킬 수 있습니다.