인덱스 튜닝 - 테이블 액세스 최소화와 부분범위 처리 활용

테이블 액세스 최소화

SQL 튜닝은 랜덤 I/O를 줄이는 것이라고 1장과 2장에서도 언급했지만,
3장에 도착하면 랜덤 I/O가 뭐였더라는 생각이 들 수도 있다. 내가 그렇다는건 아니고…

그런 사람들을 위해 다시 한 번 정리하고 넘어가자

순차 I/O : 연속된 블록을 순차적으로 탐색해 디스크의 헤드 이동이 최소화 되어 빠름
랜덤 I/O : 여러 위치에 흩어진 블록을 탐색해 디스크의 헤드 이동 시간이 길어져 느림

테이블 랜덤 액세스

인덱스는 대량의 데이터에서 소량의 데이터를 찾을 때 효율적이지
대량의 데이터를 조회할 때는 테이블 풀 스캔보다도 효율적이지 않다.

인덱스의 ROWID

SQL이 참조하는 컬럼을 인덱스가 모두 포함하는 경우가 아니면 인덱스 스캔 후 반드시 테이블에 액세스 하는데
이런 경우 실행계획에서 TABLE ACCESS BY INDEX ROWID 라는 것을 볼 수 있다.

즉, 인덱스 스캔의 이유는 검색 조건을 만족하는 소량의 데이터를 빠르게 찾고,
거기서 테이블 레코드를 찾아가기 위한 ROWID를 얻기 위함이다.
(커버링 인덱스를 제외한 경우에는 테이블 스캔이 발생한다 생각하면 된다)

ROWID는 물리적 요소인 데이터파일 번호, 오브젝트 번호, 블록 번호로 구성되어 있어
물리적 주소라고 볼 수도 있지만, 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고 있지
물리적으로는 연결되어 있지 않기에 논리적 주소에 가깝다.

결국 ROWID는 프로그래밍의 포인터처럼 물리적으로 메모리의 데이터를 가리키고 있는 것이 아니기에
포인터처럼 빠르지는 않고 테이블 레코드를 찾아가기 위한 위치 정보에 불과하다.

메인 메모리 DB

데이터를 모두 메모리(버퍼 캐시)에 로드 후에 인덱스를 수행하는 DB로, 메모리에서 I/O를 수행하는데,
해당 DB의 인덱스는 포인터처럼 메모리 상의 주소 정보를 갖는다.

하지만, 오라클의 경우에는 테이블 블록이 버퍼 캐시에서 밀려나고 다시 새로운 공간에 캐싱되는 것을
반복하기 때문에 인덱스에서 포인터로 직접 연결을 할 수 없다.

즉, 인덱스는 포인트가 아니고, 이는 다른 말로 메모리 주소 정보가 아닌 디스크 주소 정보라는 것이다.

I/O 매커니즘 복습

인덱스의 경우 : 리프 블록 → DBA → 버퍼 캐시(버퍼 헤더 → 버퍼 블록) → 디스크
테이블의 경우 : 익스텐트 맵 → DBA → 버퍼 캐시(버퍼 헤더 → 버퍼 블록) → 디스크

기억해두자!

1. 디스크를 읽기 전에 버퍼 캐시에 등록한 후에 읽는다
2. 모든 데이터가 캐싱되어 있어도 매번 DBA 해싱과 래치 획득 과정을 거쳐야한다.
3. 동시 액세스가 심한 경우에는 캐시버퍼 체인 래치와 버퍼 Lock 경합도 발생한다.

즉, ROWID를 이용한 테이블 액세스는 가벼운 작업이 아니다.

인덱스 클러스터링 팩터(Clustering Factor:CF)

CF : 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 밀집되어 있는 정도

CF기 높은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다
= 데이터가 물리적으로 근접해 있어 검색 속도가 빠르다
= 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 비슷하다

CF가 높은 컬럼은 테이블 액세스량에 비해 블록 I/O가 적게 발생해서 효율적인데
이는 오라클에선 ROWID로 테이블 액세스 시 어렵게 찾아간 테이블 블록에 대한 포인터를
바로 해제하지 않고 유지하는 Buffer Pinning 방식을 사용하기 때문이다.
CF가 높을수록 직전과 같은 태이블 블록을 가리킬 확률이 높기 때문에
매번 래치 획득과 해시 체인 스캔을 할 필요 없이 생략하고 테이블 블록을 읽을 수 있다.

인덱스 손익분기점

Index Range Scan이 Table Full Scan보다 느려지는 지점

Table Full Scan은 전체에서 몇 건을 조회하든 성능상 차이가 거의 없지만,
인덱스로 테이블에 액세스 할 때는 테이블 랜덤 액세스로 인해 조회하려는 데이터가 많을수록 느려진다.

인덱스가 많은 데이터 조회 시 비효율적인 가장 큰 이유는 다음과 같다.

• 랜덤 액세스 방식 사용
• Single Block I/O 방식 사용

이러한 이유들로 인덱스 손익분기점은 CF가 높고 낮은지에 따라 결정되는데,
낮은 경우에는 5% 미만에서 결정되고, 높으면 90% 수준까지도 상승하지만,
일반적으로는(10만~100만 정도의 테이블) 5% ~ 20%다.

테이블의 크기가 커질수록 손익분기점은 낮아지는데 이는 BCHR과 연관이 있다.

테이블 크기가 작을 때는 인덱스 컬럼 기준 값이 같은 블록에 있을 확률이 높기 때문에
인덱스를 스캔하다보면 블록을 캐시에서 찾을 확률이 높다.

하지만, 테이블 크기가 커지면 값들이 여러 블록에 흩어져 있을 확률이 높기 때문에
인덱스를 스캔하다보면 초기에 읽어야하는 블록들이 많아질 수 밖에 없고,
여러 건의 요청이 같이 버퍼 캐시를 사용하다보면 캐싱해둔 블록들이 자주 사라져서
블록을 다시 읽어야 하는 경우가 많아져서 BCHR이 높지 않고, 당연히 손익분기점도 낮아진다.

온라인 프로그램 튜닝과 배치 프로그램 튜닝

온라인 프로그램 : 주로 소량 데이터를 읽고 갱신하기에 인덱스의 효과적인 활용이 중요하다.
(인덱스와 NL 조인이 효율적으로 자주 사용되는 이유)

배치 프로그램 : 대량 데이터를 읽고 갱신해 전체범위 처리 기준으로 튜닝해야 한다.
(Full Scan과 해시 조인이 효율적으로 자주 사용되는 이유) + (파티셔닝, 병렬 처리)

인덱스 컬럼 추가

테이블 액세스를 최소화 하기 위해 가장 일반적으로 사용할 수 있는 튜닝 기법이다.

인덱스의 기존 구성을 변경하는 작업은 다른 SQL들에 영향을 줄 수 있고,
매번 필요한 인덱스를 추가하면 인덱스 관리 비용 증가와 DML 부하로 인한 트랜잭션 성능 저하가 생긴다.

그래서 가장 무난하게 시도해볼 수 있는 방식이 기존 인덱스에 컬럼을 추가하는 것이다.
특히, 테이블 액세스 단계에서 필터 조건에 의해 버려지는 레코드가 많을 때 효율적이다.

인덱스만 읽고 처리

하지만, 필터 조건에 의해 버려지는 레코드가 거의 없어서 얻는 데이터가 그냥 많은 것이라면
랜덤 액세스는 많이 발생해 성능은 느리지만 비효율이 있다고 볼 수는 없다.

이렇게 절대 일량이 많아서 어쩔 수 없이 느린 경우에는 쿼리에 사용된 모든 컬럼을 인덱스로 갖는
Covered Index를 통해 테이블 액세스 자체를 발생하지 않게 하는 방법을 고려해볼 수 있다.

테이블 액세스가 사라지니 성능은 확실히 좋지만, 추카해야할 컬럼이 많다면 적용하기가 힘들다.

Include Index

SQL Server에 추가된 기능으로, 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장한다.

기존 인덱스 방식은 인덱스에 사용된 컬럼 전체를 루트와 브랜치 블록에 저장하지만,
해당 방식은 포함하는 컬럼은 리프 블록에만 저장한다.

수직적 탐색에는 인덱스에 사용된 컬럼을, 수평적 탐색에는 포함된 컬럼을 필터 조건으로 사용할 수 있다.
즉, 포함되는 컬럼은 테이블 랜덤 액세스를 줄이기 위한 용도로만 사용된다.

두 인덱스 모두 같은 쿼리에 대한 랜덤 액세스 측면에서는 일량은 같지만,
기존 인덱스 방식이 인덱스 스캔량은 더 적다.

그리고 Include Index는 결국 랜덤 액세스를 줄이기 위한 용도로만 존재하기 때문에
조회 시 기존 인덱스 방식처럼 정렬 연산을 생략할 수는 없다.

인덱스 구조 테이블

인덱스를 이용한 테이블 액세스는 고비용 구조다!
그러면 테이블을 인덱스 구조로 생성해서 랜덤 액세스를 없애자!
= IOT(Oracle, Index-Organized Table), Clustered Index(MS-SQL)

일반적인 인덱스는 테이블을 찾아가기 위한 ROWID를 갖고 있지만,
IOT는 인덱스 리프 블록에 기존 테이블 블록이 갖고 있을 데이터를 모두 저장하고 있는다.
(인덱스 리프 블록 == 데이터 블록)

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

IOT는 인위적으로 CF를 높이는 방식 중 하나로 같은 값을 가진 레코드들이 항상 정렬된 상태로
모여 있기 때문에 시쿼션 방식으로 데이터를 액세스 한다.

따라서, BETWEEN, 부등호 조건으로 넓은 범위를 읽거나, 입력과 조회 패턴이 다른 테이블에 유용하다.

입력과 조회 패턴이 다르다는 것은 실적은 일별로 100명의 사원이 한 블록에 저장된다면
실적 조회 시에는 사원별로 이루어져서 1년이면 365개의 블록을 읽어야 하는 경우다.

이러한 경우에 IOT를 사번, 일자로 구성하면 실적이 사번순 정렬을 유지하며 저장되기 때문에 100개당 한 블록이라 가정 했을 때, 1년 조회 시 4개의 블록만 조회하면 된다.

클러스터 테이블

클러스터 테이블은 인덱스 클러스터와 해시 클러스터 두 종류가 존재한다.

인덱스 클러스터 테이블

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

다중 테이블 클러스터

일반 테이블은 하나의 데이터 블록을 여러 테이블이 공유할 수 없지만,
여러 테이블 레코드를 같은 블록에 저장할 수 있는 방식

키 값이 같은 데이터를 같은 공간에 저장만 하지, IOT나 클러스터형 인덱스처럼 정렬하지는 않는다.

일반 인덱스와 마찬가지로 B*Tree 구조를 사용하지만, 키와 테이블 레코드가 1:1 대응하는 것이 아닌
키와 데이터 블록이 대응하는 관계로, 키 값은 항상 Unique하다.

그래서 클러스터 인덱스 스캔 시 값에 관련된 클러스터에 도달하기 위한 스캔 한 번만 이루어진 후에
클러스터 도달 후에는 시퀀셜 방식으로 스캔해 넓은 범위를 효율적으로 탐색할 수 있다.

해시 클러스터 테이블

인덱스 클러스터 테이블처럼 인덱스 사용을 하는 것이 아닌,
해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다른 방식이다.

부분범위 처리 활용

부분범위 처리

전체 쿼리 결과집합을 계속해서 전송하는 것이 아닌, 사용자로부터 Fetch Call이 있을 때만
먼저 읽은 데이터 중 일정량(Array Size)씩 나누어 전송하는 처리 방식이다.

Array Size는 클라이언트 프로그램에서 설정하며 아래와 같은 순서로 요청이 처리된다.

1. 최초에는 Fetch Call을 통해 DB로부터 Array Size 만큼의 데이터를 전송 받아 클라이언트 캐시에 저장
2. 이후 호출 시 Fetch Call 없이 캐시에서 데이터를 읽음
3. 캐시에 저장한 데이터 소진 시 Fetch Call을 통해 캐싱
4. 위 과정을 필요한 데이터를 다 읽을 때까지 반복

쿼리 수행 시 결과 집합을 버퍼캐시에 모두 적재한 뒤에 사용자에게 전송하는 것이 아니라
상황에 따라 다르다는 것을 주의하자

정렬 조건이 있을 때 부분범위 처리

이런 경우에는 모든 데이터를 다 읽어 정렬을 마친 후에 클라이언트에게 전송하는 전체범위 처리지만,
정렬에 사용되는 컬럼이 선두인 인덱스가 있다면 부분범위 처리가 가능하다.

Array Size 조정을 통한 Fetch Call 최소화

네트워크를 통해 전송해야 할 데이터량에 따라 Array Size를 조절해줘야 한다.

대량 데이터를 전송해야 한다면 어차피 데이터를 전부 전송해야 하므로
Array Size를 늘려 Fetch Call 횟수를 줄이는 것이 좋다.

반대로, 앞쪽의 일부 데이터만 Fetch하고 멈추는 프로그램이라면
Array Size를 작게 설정해 많은 데이터를 전송하고 버리는 비효율을 줄이는 것이 좋다.

부분범위 처리 구현

조회한 레코드 수가 Array Size에 도달하면 멈추고, 요청이 다시 발생하면 Fetch 하는 방식으로
개발자가 직접 구현할 일은 없고, 프레임워크에 미리 구현된 기능을 사용하면 된다.

OLTP 환경에서 부분범위 처리에 의한 성능개선 원리

OLTP에선 일반적으로 소량 데이터를 읽고 갱신하지만, 대량의 데이터를 조회할 때도 있다.

이런 경우에 인덱스를 사용하면 많은 랜덤 액세스로 인해 성능을 내기 어렵지만
OLTP에선 주로 특정 정렬 순서로 상위 일부 데이터만 확인하기에
항상 정렬 상태를 유지하는 인덱스와 부분범위 처리를 잘 활용하면 성능개선을 할 수 있다.

멈출 수 있어야 의미있는 부분범위 처리

클라이언트-DB 구성의 2-Tier 환경에서는 앞쪽 일부만 출력하고 멈출 수 있게
클라이언트가 특정 DB 커넥션을 독점할 수 있어 관리가 가능하지만
사이에 다른 서버 등이 존재하는 n-Tier 아키텍처에서는 특정 DB 커넥션 독점이 불가능하다.

그렇다고 n-Tier 환경에서 부분범위 처리가 의미 없는 것은 아니라고 한다.(5장에서 다룰 내용)

배치 I/O

인덱스를 이용해 대량 데이터를 조회하면 디스크 I/O 발생량이 증가해 비효율적인데,
인덱스를 이용해 정렬 연산을 생략하고 부분범위 처리를 통해 상위 N개 집합을 빠르게 출력한다면
조회할 레코드가 아무리 많아도 빠른 응답속도를 보여줄 수 있다.

배치 I/O는 읽는 블록마다 I/O Call을 발생시키는 비효율을 줄이기 위해
버퍼 캐시에서 블록을 찾지 못해 디스크 블록을 읽을 때, 디스크 블록에 대한 I/O Call을 모아뒀다
일정량 이상 쌓인 경우에 한 번에 처리하는 방식이다.

11g에서는 NL 조인 Inner 쪽 테이블 액세스 시에만 작동하던 기능이지만
12c부터는 ROWID로 테이블에 액세스 하는 모든 부분에서 작동가능한 기능이다.

데이터 정렬 이슈

배치 I/O의 특성상 인덱스를 이용해도 데이터 정렬 순서가 매번 다를 수 있는데
테이블 블록을 모두 버퍼 캐시에서 찾으면 정렬이 보장되지만, 아니라면 다를 수 있기 때문에,
옵티마이저는 인덱스로 정렬을 생략할 수 없거나, ORDER BY가 없다면 배치 I/O를 선택한다.

좋은 기능이라고 생각할 수도 있지만 일부러 시스템 레벨에서 배치 I/O를 비활성화 하는 경우도 있는데,
SQL에서 의도적으로 인덱스의 정렬을 생각해서 정렬 연산을 사용하지 않는 경우도 있기 때문인데
가능하면 명시적으로 ORDER BY를 사용해주도록 하자

나중에 찾아볼 내용

1. Partition Pruning