[SQL 튜닝] 인덱스 튜닝(1) - 테이블 액세스 최소화 및 부분범위 처리 활용

테이블 액세스 최소화

ROWID

• 논리적 주소에 가까움
• 포인터라고 이야기 하는 사람이 많지만 포인터가 아니다. 지시봉처럼 어딘가를 가리킨다는 의미에서는 포인터가 맞지만, 프로그래밍에서 배우는 포인터와는 전혀 상관 없음

 

메인메모리DB(MMDB) 

• 잘 츄닝된 OLTP성 데이터베이스 시스템이면 버퍼캐시 히트율 99%이상. -> 디스크를 경유하지 않고 대부분 데이터를 메모리에서 읽는다는 뜻 But 메인메모리 DB만큼 빠르지 않음
• 메인 메모리DB의 경우 인스턴스를 기동하며 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스로 생성한다.
• 오라클처럼 디스크 상의 주소정보를 갖는게 아니라 메모리상의 주소정보, 즉 포인터를 가지기 때문에 인덱스를 경유해 테이블을 액세스하는 비용이 낮다.

 

I/O 매커니즘

• DBA(=데이터 파일번호 + 블록번호)는 디스크상에서 블록을 찾기위한 주소정보
• 읽고자 하는 DBA를 해시함수에 입력해서 해시ㅔ인을 찾고 거기서 버퍼헤더를 찾음
• 인덱스로 테이블블록을 액세스할때는 리프블록에서 읽은 ROWID를 분해해서 DBA정보를 얻고 테이블을 FUll Scan할때는 익스텐트 맵을 통해 읽을 블록들의 DBA정보를 얻는다.
• 모든 데이터가 캐싱되어있떠라도 테이블 레코드를 찾기 위해 매번 DBA해싱과 래치획득과정을 반복해야하며, 동시 액세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 LOCK에 대한 경함까지 발생하므로, 인덱스 ROWID를 이용한 테이블 액세스는 고비용구조이다.
• 인덱스 ROWID는 우편주소와 같은 것으로, 일일이 찾아나서는 구조이므로 고비용 연산이라고 할 수 있다.

 

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

• 특정칼럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미
• CF가 좋은 컬럼에 생성한 인덱스는 검색효율이 매우 좋으며, CF가 안좋은 인덱스를 사용하면 테이블을 액세스하는 횟수만큼 블록 I/O가 발생

 

인덱스 손익분기점

• Index Range Scan에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점을 말함
• 인덱스를 이용한 테이블 액세스가 Table Full Scan 보다 느린 이유는 첫째, Table full scan은 시퀀셜 액세스인 반면, rowid를 이용한 것은 랜덤액세스 방식이며, 둘째 Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 액세스는 Single Block I/O이기 때문
• CF가 나쁘면 손익분기점은 5%(심하면 1%)미만에서 결정되며, CF가 아주좋으면 90%수준까지 올라감
• 만건만 넘어도 Table Full Scan이 더빠를 수 있따.
• 대량 배치 프로그램에서는 인덱스보다 Full Scan이 효과적이지만, 초대용량 테이블을 Full Scan 하면 상당히 오래 기다려야하고 시스템이 주는 부담도 적지 않으므로, 배치 프로그램에서는 파티션 활용전략이 매우 중요한 튜닝 요소이며 병렬처리까지 더할 수 있으면 더 좋다.

 

인덱스 칼럼 추가

• 테이블 액세스 최소화를 위해 가장 일반적인 튜닝방법은 인덱스에 칼럼을 추가하는 것
• 테이블 액세스 단계 필터 조건에 의해 버려지는 레코드가 많을 때, 인덱스에 컬럼을 추가하면 테이블 랜덤액세스 횟수를 줄여줄 수 있음

 

인덱스만 읽고 처리

• 만약 테이블 랜덤액세스가 많아도 필터 조건에 의해 버려지는 레코드가 없고(비효율이 없고), 인덱스 스캔과정에서 얻은 데이터가 많다면 성능이 느려지게 된다
• 이때 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세스가 아예 발생하지 않게 하는 방법이 있다
• 이를 Covered 쿼리라고 한다.
• 효과는 좋지만 추가해야할 칼럼이 많아 실제 적용하기 힘들다

 

인덱스 구조 테이블

• 랜덤 액세스가 발생하지 않도록 테이블을 인덱스 구조로 생성하는 것
• 오라클에서는 IOT(Index Organized Table), MS-SQL Sever는 클러스터형 인덱스라고 부른다.
• 테이블 블록에 있어야할 데이터를 인덱스 리프 블록에 모두 저장
• 힙 구조 테이블(일반 테이블)에 데이터를 입력할 때는 랜덤방식을 활용하지만 IOT는 인덱스 구조 테이블이므로 정렬상태를 유지하며 데이터를 입력한다.
• 인위적으로 CF를 좋게 만드는 방법
• 시퀸실(범위) 방식으로 데이터를 액세스 하기때문에 BETWEEN이나 부등호 조건으로 넓은 범위를 읽을 때 유리

 

클러스터 테이블

1. 인덱스 클러스터 테이블

• 클러스터 키값이 같은 레코드를 한 블록에 모아서 저장하며 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당하여 클러스터 체인으로 연결
• 다중 테이블 클러스터 : 여러 테이블 레코드를 같은 블록에 저장
• SQL Server의 클러서터형 인덱스와는 다르며 이는 오히려 IOT에 가까움
• B-Tree를 인덱스 구조를 사용하지만, 테이블 레코드를 일일이 가리키지 않고 해당 키값을 저장하는 첫 번째 데이터 블록을 가리킨다는 점이 다르다
• 즉, 일반 테이블의 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖지만, 클러스터 인덱스는 테이블 레코드와 1:M관계를 가진다.
• 따라서 클러스터 인덱스의 키값은 항상 Unique하다.(중복값이 없다)

2. 해시 클러스터 테이블

• 인덱스를 활용하지 않고 해시 알고리즘을 사용

 

부분범위 처리 활용

부분범위 처리

• DBMS가 클라이언트에게 데이터를 전송할 때 일정량씩 나누어 전송, 전송하지 않은 분량이 남아있어도 서버 프로세스는 클라이언트로부터 추가 fetch call을 받기 전까지 그대로 멈춰 서서 대기
• 아무리 데이터가 많더라도 DBMS는 먼저 읽는 데이터 부터 일정량을 전송하고 멈줘서 결과를 빨리 출력할 수 있음
• 다음 fetch call이 나오면 그 다음 데이터로 부터 일정량을 읽어서 전송
• 부분범위 처리 : 전체 쿼리 결과집합을 연속적으로 처리하지 않고 fetch call이 있을 때 마다 일정량씩 나누어 전송
• 만약 order by 같이 정렬조건이 있으면 전체 범위 처리를 하고 전송
• Array Size(전송량)과 fetch call의 횟수를 잘 조절해야한다.

 

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

• OLTP(Online Transaction Processing) : 온라인 트랜잭션을 처리하는 시스템이며 보통 소량데이터를 읽고 갱신
• 물론 대용량 데이터를 처리할 경우도 있음
• OLTP성 업무에서 쿼리 결과 집합이 아주 많을 때, 사용자가 모든 데이터를 다 확인하지 않고, 특정한 정렬 순서로 상위 일부 데이터만 확인
• 항상 정렬 상태를 유지하는 인덱스를 이용하면, 정렬 작업을 생략하고 앞쪽 일부 데이터를 빠르게 보여줄 수 있음
• 부분처리는 앞쪽 일부만 출력하고 멈출 수 있을 때 의미가 있지만, 클라이언트와 DB사이에 WAS, AP서버 등이 존재하는 n-Tier 아키텍쳐에서는 클라이언트가 특정 DB커넥션을 독점할 수 없다. 단위 작업을 마치면 DB커넥션을 곧바로 커넥션 풀에 반환해야 하므로 그전에 SQL 조회 결과를 모두 전송해야한다.