CH01.인덱스 원리와 활용 - 05. 테이블 Random 액세스 최소화 튜닝

(1)인덱스 컬럼추가
기존에 사용하던 인덱스에 컬럼을 추가하는 것만으로도 성능향상에 큰 영향을 미칠 수 있습니다.

위 SQL을 위해 서비스번호 하나로 구성된 단일 컬럼인덱스 로밍렌탈_N2가 사용됐는데 테이블을 액세스 하는 단계에서만 265,957(=266968-1011)개의 블록 I/O가 발생했고 이는 전체 I/O의99.6퍼를 차지하는 수치입니다. 수행시간도 49초가 걸리고 있습니다. 266,476번의 테이블을 방문하는 동안 블록 I/O가 265,957개 발생한 것을 보면, 인덱스 클러스터링 팩터가 안좋은 것일 수 도 있습니다. 이 경우에는 테이블 Reorg하지 않는 한 어쩔 수 없습니다. 여기서 문제는 테이블을 총 266,476번 방문해지만 최종 결과집합에서는 1,909건 밖에 되지 않는다는 것입니다. 테이블을 방문하고나서 사용여부 = ‘Y’조건을 체크하는 과정에서 대부분 버려진 것이라고 볼 수 있습니다.
기존 인덱스에 사용여부컬럼을 추가하고 난 후의 SQL트레이스입니다.
비효율없이 쿼리가 잘 수행되는 것을 확인할 수 있습니다.

(2) PK 인덱스에 컬럼 추가
단일 테이블을 PK로 액세스할 때는 단 한건만 조회하는 것이므로 테이블 Random 액세스도 단 1회 발생하게 됩니다. 하지만 NL조인을 할 때 Inner 족(=right side)에서 액세스 될 때는 Random 액세스 부하가 만만치 않습니다. 특히 Outer 테이블에서 Inner 테이블 쪽으로 조인 액세스가 많은 상황에서 Inner 쪽 필터 조건에 의해 버려지는 레코드가 많다면 그 비효율은 매우 심각한 것일 수 있습니다.

PK 인덱스에는 원래 컬럼을 추가할 수 없습니다. 그러다 보니 [PK컬럼 + 필터조건 컬럼]형태의 새로운 Non-Unique인덱스를 추가하게 됩니다. 그럴 때 생긴 인덱스를 이용해 Pk 제약 설정을 다시 한다면 인덱스 개수를 줄일 수 있습니다.
PK제약에는 중복 값 확인을 위한 인덱스가 반드시 필요합니다. 인덱스가 없다면 값이 입력될 때마다 테이블 전체를 읽어 중복 값 존재 여부를 체크해야 하기 때문입니다. 하지만 중복 체크를 위해 반드시 Unique 인덱스가 필요한 것은 아니며, Non-Unique인덱스로도 가능합니다. 하지만 이 인덱스를 이요하면 중복 여부를 체크할 때 one-plus 스캔이 발생하는 약간의 비효율은 발생하지만 무시할만 하므로 사용하면 좋습니다.
아래와 같이 사용합니다.
alter table dept drop primary key;
create index dept_x01 on dept(deptno,loc);
alter table dept add constraint dept_pk primary key(deptno) using index dept_x01;

참고로, PK제약을 위해 사용되는 인덱스는 PK제약순서와 서로 일치하지 않아도 상관없습니다. 단지 중복 값 유무를 체크하는 용도이므로 PK 제약 컬럼들이 선두에 있기만 하면 됩니다.
예를들어 PK제약 컬럼 구성이 [고객번호 , 상품번호, 거래일자]일 때, 아래 1~3번의 인덱스는 PK 제약을 위해 사용될 수 있지만 4와 같은 구성은 허용되지 않습니다. 4와 같은 구성으로도 중복 값 유무를 확인할 수는 있지만 비효율적이기 때문에 허용하지 않습니다.
1. 거래일자 + 고객번호 + 상품번호
2. 상품번호 + 거래일자 + 고객번호 + 거래구분
3. 고객번호 + 거래일자 + 상품번호 + 매체구분 + 거래구분
4. 고객번호 + 상품번호 + 거래구분 + 거래일자

(3)컬럼 추가에 따른 클러스터링 팩터 변화
인덱스에 컬럼을 추가함으로써 테이블 Random 액세스 부하를 줄이는 효과가 있지만 인덱스 클러스터링 팩터가 나빠지는 부작용이 생길 수 있습니다.
인덱스 내에서 키 값이 같은 레코드는 rowid 순으로 정렬됩니다. 그런데 여기에 변별력이 좋은 object_name 같은 컬럼을 추가하면 rowid 이전에 object_name 순으로 정렬되므로 클러스터링 팩터를 나쁘게 만드는 요인으로 작용합니다.
결론적으로 변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변화에 주의를 기울여야 합니다.

(4)인덱스만 읽고 처리
테이블을 액세스하고서 필터 조건에 의해 버려지는 레코드가 많을 때, 인덱스에 컬럼을 추가함으로써 얻는 성능효과를 보았습니다. 그런데 테이블 Random 액세스가 아무리 많더라도 필터 조건에 의해 버려지는 레코드가 거의 없다면 비효율은 없습니다. 이때는 어떤 튜닝을 해야 할까요?
이때는 아예 테이블 액세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에 포함시키는 방법을 고려해 볼 수 있습니다. MS-SQL에서는 Coverd 인덱스라고 부르고 인덱스만 읽고 처리하는 쿼리를 Covered 쿼리라고 합니다.

(5)버퍼 Pinning 효과 활용
오라클의 경우 한번 입력된 테이블 레코드는 절대 rowid가 바뀌지 않습니다. 즉 레코드이동이 발생하지 않습니다. 따라서 아래와 같이 미리 알고 있던 테이블 rowid값을 이용해 레코드를 조회하는 것이 가능합니다. (해당 레코드가 지워지지 않은 경우)
select * From emp where rowid = :rid
실행계획상에는 ‘Table Access By Index ROWID’ 대신 ‘Table Access By User ROWID’라고 표시됩니다.
미리 알고 있던 rowid 값이 아니더라도 인라인 뷰에서 읽은 rowid값을 이용해서 테이블을 액세스 하는것도 가능합니다.
만약 그런 상황에서 버퍼 Pinning 효과까지 나타난다면 비효율은 거의 없어질 것입니다. Random 액세스 비효율은 한 건을 읽기 위해 블록을 통째로 읽기 때문에 발생하는 것인데, 인라인뷰로 rowid값을 정렬하여 CF를 좋은 상태로 만든 후 NL 조인을 하여 inner테이블에 한 건씩 액세스하는 쿼리가 있을때 한 번 액세스로 블록 안에 있는 모든 레코드를 다 읽어 들이는 셈이 됩니다.

(6) 수동으로 클러스터링 팩터 높이기
테이블에는 데이터가 무작위로 입력되는 반면 그것을 가리키는 인덱스는 정해진 키 순으로정렬되기 때문에 CF가 좋지 않게 마련입니다. CF가 나쁜인덱스를 이용해 많은 양의 데이터를 읽어야 할 때 비효율이 발생할 수 있습니다. 하지만 인덱스가 여러 개인 상황에서 특정 인덱스를 기준으로 테이블을 재정렬하면 다른 인덱스의 CF가 나빠질 수 있습니다. 두 인덱스 간에 상관관계가 높다면 두 개 이상의 인덱스 CF가 동시에 좋아질 수 있지만 그런 경우가 아니라면 성능이 좋아지는 인덱스는 테이블당 하나뿐일 것입니다.
따라서 인위적으로 CF를 높일 목적으로 테이블을 reord할 때는 가장 자주 사용되는 인덱스를 기준으로 삼아야 하며 다른 인덱스에 영향을 주지 않는지 체크해봐야 합니다. 그리고 이 방법을 주기적으로 수행해야 한다면 데이터베이스 관리 비용이 증가하므로 테이블과 인덱스를 rebuild하는 부담이 적고 효과가 확실할 때만 사용하는 것이 바람직합니다.

차세대 시스템 구축 시 주의사항
더 좋은 하드웨어 장비 서버로 이관했는데 성능이 안좋아지는 경우가 있습니다. 원인은, 과거 시스템으로부터 데이터를 이관하는 과정에서 CF가 오히려 나빠진 데서 원인이 있을 수 있습니다. 기존에 운영되는 시스템은 트랜잭션이 발생하는 순서대로 데이터가 입력되는 반면 테이블을 이관할 때는 별렬쿼리를 많이 활용하기 때문에 데이터를 무작위로 흩어놓는 경향이 있습니다. 이는 전반적으로 테이블 Random I/O 횟수를 증가시키고 결과적으로 디스크 I/O 발생량과 경합을 증가시키는 요인으로 작용합니다.
따라서 데이터 이관시에는 as is 대비 시스템 CF가 나빠지지 않았는지 조사하고 그 결과에 따라 적절한 조치를 취해 주어야 합니다.