CH01. 인덱스 원리와 활용 - 07. 인덱스 스캔효율

Sequential 액세스는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식을 말합니다.

Random 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고 한 건을 읽기 위해 한 블록씩 접근하는 방식을 말합니다.

I/O튜닝의 핵심원리는 아래와 같습니다.

- Sequential 액세스의 선택도를 높이고 Random 액세스의 발생량을 줄입니다.

 

이번 장은 테이블을 액세스하기 전 인덱스를 Sequential 방식으로 스캔하는 단계에서 발생하는 비효율 해소 원리를 다룹니다.

일반적인 의미에서의 선택도는 전체 레코드 중에서 조건절에 의해 선택되는 비율을 말합니다. 

-유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)

-유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율(%)

유효 테이블 선택도가 높다면 인덱스 스캔 후 테이블 Random 액세스 비율이 높은 것이므로 앞의 이야기와 반대의 이야기일 수 있습니다. 하지만 여기서 이야기하는 Sequential 액세스 선택도는 인덱스를 스캔한 건수 중 결과로 선택되는 비율을 말하는 것이고, 그 비율이 높아야 효율적이라는 의미는 같은 결과 건수를 내기 위해 적은 양을 읽어야 함을 말하는 것입니다.

 

(1)비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성

테이블과 달리 인덱스 레코드는 '같은 값을 갖는'레코드들이 항상 서로 군집해 있습니다. 그런데 ' 같은 값을 가진다'라고 하면 '='비교가 전제되므로, 만약 비교연산자가 '='조건이 아닐 때는 인덱스 레코드도 서로 흩어진 상태일 수 있습니다.

---

선두 컬럼 : 인덱스 구성상 맨 앞쪽에 있는 컬럼을 지칭할 때 사용

선행 컬럼 : 상대적으로 앞쪽에 놓인 컬럼

---

 

 

위의 그림을 보고 아래와 같이 인덱스 구성 컬럼이(col1,col2,col3,col4) 모두 '=' 조건으로 비교될 때는 조건을 만족하는 레코드들이 모두 연속되게 모여 있게 됩니다.

where col = 1 and col = 'A' and col3 = '나' and col4='a'

 

아래와 같이 선행 컬럼은 '='이고 맨 마지막 컬럼만 범위검색 조건일 때도 조건을 만족하는 레코드가 서로 모여 있게 됩니다.

where col = 1 and col = 'A' and col3 = '나' and col4 >= 'a'

 

맨 마지막 컬럼이 아닌 중간 컬럼이 범위검색 조건일 때는 다릅니다. 세번째 컬럼col3가 범위 검색 조건인 경우는 col1~col3까지 만족하는 인덱스 레코드는 모여 있지만 col4 조건까지 만족하는 레코드는 흩어지게 됩니다.

where col = 1 and col = 'A' and col3 between '가' and  '다' and col4 = 'a'

 

두번째 컬럼 col2가 범위검색 조건인 경우는 col1~col2까지 두 조건만을 만족하는 인덱스 레코드는 서로 모이지만 col3과 col4조건까지 만족하는 레코드는 흩어지게 됩니다.

where col = 1 and col <= 'B' and col3 between '나'  and col4 between 'a' and 'b'

 

선행 컬럼이 모두 '='조건인 상태에서 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속되게 모여있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자의 종류에 상관없이 흩어진다는 것을 확인 할 수 있습니다.

 

(2) 인덱스 선행 컬럼이 = 조건이 아닐 때 발생하는 비효율

Sequential 액세스 효율은 선택도에 의해 결정됩니다. 다른말로 같은 결과 건수를 내는데 얼마나 적은 레코드를 읽느냐로 효율성을 판단할 수 있습니다.

인덱스 Sequential 액세스에 따른 선택도는 인덱스 컬럼이 조건절에 모두 = 조건으로 사용될 때 가장 높습니다. 리프 블록을 스캔하면서 읽은 레코드는 하나도 필터링되지 않고 모두 테이블 액세스로 이어지기 때문입니다.

인덱스 컬럼 중 일부가 조건절에서 생략되거나 = 조건이 아니더라도, 그것이 뒤쪽 컬럼일 때는 비효율이 없습니다.

 

반면, 인덱스 선행 컬럼이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스를 스캔하는 단계에서 비효율이 발생합니다.

 

선두 컬럼이 BETWEEN일 때 스캔 시작과 종료 지점

예를 들어 BETWEEN A and B일때 BETWEEN 시작점 A부터 스캔하는 것이 아니라 인덱스 스캔 시작점을 찾는 수직점 탐색과정에서 A이지만 그 뒤의 조건절에 부합하지 않아 필터링 될 수 있으면 그것은 읽지 않습니다.

마찬가지로 B가 종료값으로 B까지 스캔하는 것이 아니라 그 뒤의 조건절을 보고 필터링 될 수 있으면 그 레코드는 스캔하지 않습니다.

 

(3) BETWEEN 조건을 IN-LIST로 바꾸었을 때 인덱스 스캔 효율

왼쪽은 Between을 사용했을 때이고, 오른쪽은 IN-LIST를 사용했을 때입니다.

오른쪽의 화살표가 2번 발생하는 것은 인덱스의 수직적 탐색이 두 번 발생하기 때문이며 이 때의 실행계획은 아래와 같습니다.

 

인덱스를 위와 같이 두 번 탐색한다는 것은 SQL을 아래와 같이 작성한 것과 마찬가지이며 모든 컬럼이 = 조건인 것처럼 됩니다.

 

실제로 RBO모드에서 인덱스 컬럼이 IN-LIST 조건절을 사용하면 쿼리가 내부적으로 union all 방식으로 변환되고 실행되는 Concatenation 방식의 실행계획이 나타납니다.

IN-LIST개수만큼 union all 브랜치가 생성되고, 각 브랜치마다 모든 컬럼을 '=' 조건으로 검색하기 때문에 앞서 선두 컬럼을 Between 조건으로 비교할 때와 같은 비효율이 사라집니다. INDEX SKIP SCAN을 사용하더라도 비슷한 효율을 얻을 수 있습니다.

 

Between 조건을 IN-List 조건으로 바꿀 때 주의 사항

IN-LIST 개수가 많지 않아야 합니다. 필요없는 범위를 스캔하는 비효율은 사라지지만 인덱스 수직 탐색이 늘어나기 때문입니다. IN-LIST개수가 많을 때는 between 조건 때문에 리프 블록을 추가로 스캔하는 비율보다 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있습니다.

인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 대만 유용하다는 사실도 잇습니다.

between 조건인 선행 컬럼 대문에 많은 인덱스 리프 블록을 스캔하지만 거기서 선택되는 레코드는 소량일 때 IN-LIST가 효과를 냅니다.

 

(4)Index Skip Scan을 이용한 비효율 해소

인덱스 선두 컬럼이 누락됐을 때뿐 아니라 부등호,between,like 같은 범위검색 조건일 때도 Index Skip Scan이 유용하게 사용될 수 있고, 데이터 상황에 따라서는 = 조건 컬럼들을 인덱스 선두에 위치시킨 것에 버금가는 효과를 얻을 수 있습니다.

2008년 1월부터 12월까지 월별로 10만개 판매데이터가 입력되도록 하였고 A는 10만개, B는 110만개입니다.

아래와 같은 쿼리를 수행하려고 합니다.

인덱스를 아래와 같이 구성하는 것이 가장 효과적입니다.

두 조건 다 = 조건이기 때문에 테이블 액세스 비효율 없이 효과적으로 사용할 수 있습니다.

 

이번에는 between 조건인 판매월 컬럼을 선두로 갖는 인덱스를 사용하는 경우를 살펴보겠습니다.

 

위 그림처럼 A는 각 월의 10%정도 차지하게 되기 때문에 A값들은 멀리 떨어지게 됩니다.

 

같은 쿼리를 돌렸을 때 3090개의 블록 I/O가 발생하였습니다. 테이블을 방문하지 않았음에도 이렇게 발생한 이유는 인덱스 선두컬럼이 between조건이어서 판매구분이 B인 레코드까지 모두 스캔하고서 버렸기 때문입니다.

 

IN-LIST 사용시 314개로 블록 I/O가 감소하였습니다. 인덱스 브랜치 블록을 10번 반복 탐색하는 비효율이 있긴 하지만 리프 블록을 스캔할 때의 비효율을 제거함으로써 1/10수준으로 성능이 개선되었습니다.

 

인덱스 선두 컬럼이 between 조건임에도 큰 비효율 없이 300블록만 읽고 쿼리가 마쳤습니다.

선두 컬럼이 between이어서 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 때 Index Skip Scan으로써 효과를 볼 수 있는 전형적인 케이스입니다.

 

(5) 범위검색 조건을 남용할 때 발생하는 비효율

like는 between일 때와 기본 원리는 같고 다만 스캔 범위에 약간의 차이가 생깁니다.

범위검색 조건을 남용함녀 첫 번째 범위검색 조건에 의해 스캔 범위가 대부분 결정되며 그 다음을 따르는 조건부터는 스캔 범위를 줄이는 데에 크게 기여하지 못하므로 성능상 불리해 질수 있습니다.

스캔량이 적을 때보다 대량일 때 성능 차이가 심해질 수 잇습니다.

 

(6) 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항

OR-Expansion을 이용하는 방법과 주의 사항

OR 조건에 대한 expansion(union all 분기)이 일어나면 뒤쪽 조건절이 먼저 실행된다는 특징이 있습니다. 주의할 점은 버전에 따라 실행되는 순서가 달라진다는 사실입니다. 9i까지는 I/O 비용모델, 10g CPU 비용 모델에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행합니다.

10g에서 값 분포에 상관없이 항상 뒤쪽에 있는 조건식이 먼저 처리되도록 하려면 ordered_predicates 힌트를 사용해야 합니다.

 

rowid를 concatenation하면 결과에 오류 발생

문자형으로 변환된 rowid는 orwid값 그대로 비교할 때와 정렬순서가 달라집니다.

 

인덱스를 스캔하면서 rowid를 필터링할 때 발생하는 비효율

rowid를 가지고 = 조건으로 바로 액세스할 땐 어떤 액세스보다 빠르지만 인덱스를 스캔하면서 rowid를 필터링 할때는 아닙니다.

인덱스 rowid는 리프 블록에만 있기 때문에 이를 필터링하려면 일단 다른 액세스 조건만으로 리프 블록을 찾아가야 합니다. 거기서 스캔을 시작해 rowid를 필터링해야 하므로 비효율이 생길 수 있습니다.

 

(7)Between과 Like 스캔 범위 비교

둘 다 범위검색조건으로써 앞에서 설명한 범위검색 조건을 사용할 때의 비효율원리도 똑같이 적용됩니다. 하지만 Between을 사용한다면 적어도 손해 볼 일은 없습니다.

 

각 쿼리에 대한 결과를 위의 그림으로 나타낸 것입니다. between연산자를 사용한 쿼리1은 1월 데이터는 모두 읽더라도 2월 데이터만큼은 판매구분이 A인 데이터만 읽고 멈춥니다. like 연산자를 사용한 쿼리는 1월과 2월 데이터 모두 읽습니다.

쿼리3은 like와 같은 스캔량을 보입니다.

 

(8)선분이력의 인덱스 스캔 효율

예를 들어 고객의 변경이력을 관리할 때 이력의 시작시점만을 관리하는 것을 점이력, 시작시점과 종료시점을 함께 관리하는 것을 선분이력 모델이라고 합니다.

점이력으로 관리할 때 PK가 고객번호 + 변경일자로 구성된다면 선분이력으로 관리될 때는 고객번호 + 시작일자 + 종료일자로 구성됩니다. 가장 마지막 이력의 종료일자는 항상 99991231로 입력해두어야 합니다.

선분형태로 관리하면 무엇보다 쿼리가 간단해진다는 것이 가장 큰 장점입니다.

하지만 이력이 추가될 때마다 기존 최종이력의 종료일자도 같이 변경해 주어야 하는 불편함이 있습니다. 이 때문에 DML성능이 나빠질 뿐아니라 이력 데이터를 관리하는 프로그램이 복잡해 질 수 있습니다.

또한 개체 무경성을 사용자가 직접 관리해 주어야한다는 단점이 있습니다. 선분이력의 개체 무결성을 확보하려면 선분의 중복이 없어야 합니다. 점이력 일때는 PK제약을 설정한느 것만으로 개체 무결성이 완벽히 보장되지만 선분이력일 때는 선분이 겹치거나 끊기지 않도록 방지하는 기능을 제공하지 않아 개체 무결성이 보장되지 않습니다.

 

(9) Access Predicate와 Filter Predicate

인덱스를 경유해 테이블을 액세스할 때는 최대 3가지 Predicate 정보가 나타날 수 있습니다.

1.인덱스 단계에서의 Access Predicate

2.인덱스 단계에서의 Filter Predicate 

3.테이블 단계에서의 Filter Predicate 

인덱스를 경유하지 않고 테이블 전체를 스캔할 때는 3번 한가지 만 나타납니다.

 

1번은 인덱스 스캔 범위를 결정하는 데에 영향을 미치는 조건절을 의미합니다.

아래는 인덱스 스캔 범위를 결정하는 데 전혀 영향을 미치지 않으므로 Access Predicate에서 제외됩니다.

- 좌변 컬럼을 가공한 조건절

- 왼쪽 % 또는 양쪽 % 기호를 사용한 like 조건절

- 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 다른 조건절

위의 경우를 제외하면 수직적 탐색과정에서 모든 인덱스 컬럼을 비교 조건으로 사용합니다.

 

2번은 테이블로의 액세스 여부를 결정짓는 조건절을 의미합니다. 첫 번째 나타나는 범위검색 조건부터 이후 모든 조건절 컬럼들이 여기에 포함되며, 조건절에서 누란된 컬럼 뒤쪽에 놓인 인덱스 컬럼들도 포함합니다.

 

3번은 테이블을 액세스하고 나서 최종 결과집합으로의 포함여부를 결정짓는 조건절을 의미합니다.

 

(10) Index Fragmentation

오라클은 B*Tree 인덱스 때문에 루트에서 리프 블록까지 어떤 값으로 탐색하더라도 읽는 블록 수가 같습니다.

불균형은 생길 수 없지만 Index Fragmentation에 의한 index skew또는 sparse현상이 생길 수 는 있습니다. 이것은 인덱스 스캔 효율에 나쁜 영향을 미칠 수 있습니다.

 

Index Skew

인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상을 말합니다. 대량의 delete 작업을 마치고 나면 한쪽의 리프 블록들을 텅비는 반면 반대는 꽉 찬 상태가 됩니다.

commit 시 텅 빈 인덱스 블록은 freelist로 반환되지만 인덱스 구조 상에는 그대로 남습니다. 상위 브랜치에서 해당 리프 블록을 가리키는 엔트리가 그대로 남아 있어 인덱스 정렬 순서상 그 곳에 입력될 새로운 값이 들어오면 언제든 재사용될 수 있습니다.(재사용되더라도 이 경우에는 곧바로 freelist에서 제거되지 않으며 나중에 빈 블록을 찾기 위해 freelist를 스캔하는 프로세스에 의해 정리됩니다.)

새로운 값이 하나라도 입력되기 전 다른 노드에 인덱스 분할이 발생하면 그것을 위해서도 이들 블록이 재사용됩니다. 이때 상위 브랜치에서 해당 리프 블록을 가리키는 엔트리가 제거돼 다른쪽 브랜치의 자식노드로 이동하고 freelist에서도 제거됩니다. 레코드가 모두 삭제된 블록은 언제든 재사용 가능하지만 문제는 다시 채워질 때까지 인덱스 스캔 효율이 낮다는 것입니다.

 

Index Sparse

인덱스 블록 전반에 걸쳐 민도가 떨어지는 현상을 말합니다.

지워진 자리에 인덱스 정렬 순서에 따라 새로운 값이 입력되면 그 공간은 재사용되지만 대량의 delete 작업 후 한동안 인덱스 스캔 효율이 낮다는것이 문제가 될 수 있습니다.

Index Skew처럼 블록이 아예 텅비면 곧바로 freelist로 반한돼 언제든 재사용되지만 Index Sparse는 지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용되지 않을 수도 있습니다. 총 레코드 건수가 일정한데 인덱스 공간 사용량이 계속 커지는 것은 대개 이런 현상 때문입니다.

 

Index Rebuild

인덱스 크기가 계속 증가하고 스캔 효율이 나쁠 때는 coalesce명령을 수행해주면 됩니다.

alter index [인덱스명] colaesce;

해당 명령을 수행하면 인덱스 분할과 반대작업이 일어납니다. 여러 인덱스 블록을 하나로 병합하고  그 결과로써 생긴 빈 블록들은 freelist에 반환합니다.

coalesce명령을 수행해도 인덱스 세그먼트에 할당된 미사용공간(HWM 아래쪽에서 freelist에 등록된 블록과 HWM 위쪽 미사용 블록)은 반환되지 않습니다. Index Fragmentation을 해소하면서 공간까지 반환하려면 shrink명령을 수행해야 합니다. 단 ASSM에서만 작동합니다.

alter index [인덱스명 ]shrink space;

compact 옵션을 지정하면 공간은 반환하지 않으므로 coalesce와 같은 명령어가 됩니다.

 

coalesce나 shrink는 레코드를 건건이 지웠다가 다시 입력하는 방식을 사용하므로 작업량이 많을 때는 rebuild명령이 더 나을 수 잇습니다.

alter index [인덱스명] rebuild;

alter index [인덱스명] rebuild online;

 

위와 같은 방식으로 인덱스 구조를 슬림화하면 저장 효율이나 스캔 효율을 좋아지지만 인반적으로 인덱스 블록에는 어느정도 공간을 남겨두는 것이 좋습니다. 공간이 전혀 없으면 인덱스 분할이 자주 발생해 DML 성능을 떨어뜨리기 때문입니다.

인덱스에서의 pctfree는 인덱스를 처음 생성하거나 rebuild할 때만 적용되기 때문에 pctfree를 높이고 나서 인덱스를 rebuild해야 합니다.

 

-인덱스 분할에 의한 경합이 현저히 높을 때

- 자주 사용되는 인덱스 스캔 효율을 높이고자 할때, 특히 NL조인에서 반복 액세스되는 인덱스 높이가 증가 했을 때

- 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때

- 총 레코드 수가 일정한대도 인덱스가 계속 커질 때

 

위와 같은 상황일 때는 rebuild를 고려하는 것이 좋습니다.

 

인덱스 freelist

테이블에서늬 pctfree와 pctused는 각각 freelist에서 제외되는 시점과 다시 등록되는 시점을 지정하는 파라미터입니다. 테이블은 heap방식으로 데이터를 입력하므로 매번 freelist를 참조해 데이터를 삽입이 가능한 블록을 찾아야 합니다.

반면, 인덱스는 정렬된 구조로 자료를 삽입하므로 값이 입력될 때마다 freelist를 참조하지 않아도 됩니다. 인덱스 구조를 탐색해 정렬 순서에 따라 정해진 곳에 레코드를 삽입하기 때문이며, 인덱스 freelist는 인덱스 분할로 빈 블록이 필요할 때만 참조합니다. 인덱스에 pctused가 없는 것도 특징입니다.

PCTFREE는 테이블에서는 블록의 남은 공간이 나중에 update를 위해 사용됩니다.

인덱스에서는 인덱스 블록의 공간이 없으면 인덱스 분할이 빈번하게 발생되므로 이를 방지하려고 pctfree가 필요합니다. 이 옵션은 인덱스 최초 생성 또는 재생성 시점에만 적용되며, 나중에 Insert를 위해 사용됩니다.

PCTUSED는 freelist에서 제거된 테이블 블록에 빈 공간이 일정 수준 이상 확보됐을 때만 다시 freelist에 등록되도록 하기위해 사용되는 파라미터입니다.

인덱스에는 pctused파라미터가 없는데 인덱스에서 빈 공간은 항상 재사용 가능하기 때문입니다.

인덱스에서의 freelist는 인덱스 분할에 사용가능한 빈 블록들을 관리합니다. delete에 의해 비워진 인덱스 블록은 커밋 시점에 freelist에 반환되지만 insert시점에 다시 값이 입력되더라도 곧바로 freelist에서 제거되지 않는다는 사실입니다. freelist에 그대로 두었다가 인덱스 분할 때문에 freelist를 스캔하는 프로세스에 의해 정리됩니다. 즉, freelist에서 얻은 블록이 비어있지 않으면 다른 블록을 재요청하기 전에 일단 해당 블록을 freelist에서 제거하는 방식이며, 이는 커밋 시점에 수행해야 할 일량을 최소화 하기 위함입니다. 만약 값을 입력하고 커밋할 때마다 freelist를 관리해 주어야 한다면 fast commit 매커니즘에 문제가 생길지도 모릅니다.