출처 : KACHISORI
전회의「 색인을 작성했음에도 퍼포먼스가 나쁜 케이스」에서는 색인을 사용하기 위한SQL의 기술 방법이나 색인을 사용한 검색시의 주의점에 대해 설명했습니다. 이번회 에서는 색인을 이용한 검색 속도를 한층 더 향상시키는 테크닉으로서 복합 색인(콤퍼짓(composite) 색인) 을 이용하는 방법을 설명합니다.
■복합 색인(콤퍼짓(composite) 색인)의 사용
복합 색인이란 복수의 열을 지정한 색인으로 SQL 그리고 WHERE 조건으로 지정하는 열이 정해져 있는 경우나 선택성이 낮은 복수의 열을 조합하는 것으로 선택성을 높일 수 있는 경우에 사용합니다.
여기서 B*Tree 색인의 구조를 생각해야 합니다. 제2 회 「SQL튜닝의 필수 지식을 총점검(전편)」의 「그림6 B*Tree 색인 스캔」에서 설명한 것처럼 B*Tree 색인의 리프 블록에는 색인열의 데이터가 격납되어 있습니다. 복합 색인의 경우도 지정한 모든 색인열의 데이터가 리프 블록에 격납됩니다.
그 때문에 Oracle에서는 SQL에서 필요한 모든 열(WHERE 조건열, 참조열)이 색인에 포함되어 있는 경우, 테이블에 액세스 하지않고 색인에의 액세스만으로 처리를 완료할 수 있습니다. 즉 복합 색인을 잘 이용하면 테이블에 액세스하지 않으므로 처리 속도를 큰폭으로 향상할 수 있는 경우가 있습니다.
 |
| 그림1 색인만에의 액세스에 의한 데이터 취득 |
그러면 실제로 이하와 같은SQL을 실행해 풀테이블 스캔, 단일열색인에 의한 색인 스캔, 그리고 복합 색인에 의한 색인 스캔시의SQL 트레이스,TKPROF 유틸리티의 결과를 비교하여 어떠한 차이가 나오는지를 봅시다.
 |
| 그림2 풀테이블 스캔의 경우 |
 |
| 그림3 단일열색인에 의한 색인 스캔의 경우 |
그림2 , 그림3 의 실행 통계를 비교하면 색인 스캔을 실행했음에도 불구하고 풀테이블 스캔보다 실행 시간, 액세스 블록수가 많은 것을 알수 있습니다. 이것은 전회에 설명한 것처럼 검색 대상이 되는 레코드의 비율이 너무 많기 때문입니다 (자세한 것은, 제7 회 「 색인을 작성했는데 퍼포먼스가 나쁜 케이스 」(을)를 참조).
또 그림3 의 실행 계획을 보면 「idx1_lineitem 」색인 뿐만이 아니라 「lineitem 」테이블에의 액세스도 발생하고 있는 것을 알수 있습니다. 이러한 경우에는, 실행하고 있는 SQL의 참조열에 주목해 그 모든 열을 포함한 복합 색인 주1 의 작성을 검토해 보겠습니다.
| 주1 ： 복합 색인으로 지정할 수 있는 열수 색인으로 지정할 수 있는 렬수는 최대32 열로, 비트 맵 색인에서는30 열이 됩니다 |
그림2 , 그림3 에서 실행한고 있는 SQL 의 참조열은, 「l_shipmode 」, 「l_shipinstruct 」, 「l_quantity 」, 「l_extendedprice 」, 「l_tax 」이므로 이것들 모든 열을 포함한 복합 색인을 작성하는 것으로, 테이블 액세스를 배제할 수 있습니다.
그림 4 에서는 힌트문으로 신규로 작성한 복합 색인을 사용하도록 옵티마이져에 지시해 SQL 트레이스, TKPROF 유틸리티를 사용해 실행 계획을 취득하고 있습니다.
 |
| 그림4 복합 색인에 의한 색인 스캔의 경우 |
그림3 과 그림4 의 실행 통계를 비교하면 참조열을 모두 색인열로 하여 테이블에의 액세스가 배제되었으므로 실행 시간, 액세스 블록수가 크게 감소하고 있는 것을 확인할 수 있습니다. 또, 「idx2_lineitem 」색인만에의 액세스로 「GROUP BY 구」로의 소트 처리도 배제되고 있습니다.
 |
| 그림5 전표 스캔대 색인 스캔대 복합 색인 스캔의 성능 비교 |
■복합 색인(콤퍼짓(composite) 색인)을 한층 더 효과적으로 이용하기
그림4에서 나타난 복합 색인을 사용한 실행 계획에서 모든 색인 블록에1 개씩 액세스 하는 풀 인덱스 스캔(INDEX FULL SCAN )이 실행되고 있는 것을 확인할 수 있었습니다. 테이블 데이터에의 액세스를 배제시킴으로 크게 퍼포먼스를 개선할 수 있었습니다만, 이와 같이 모든 색인 블록을 읽어들일 필요가 있는 경우에는 고속전색인 스캔(INDEX FAST FULL SCAN ) 을 사용하여 복수의 색인 블록을 패러렐 처리로 읽어들여 보다 퍼포먼스를 향상할 수 있을 가능성이 있습니다.
다만 복수의 색인 블록을 정리하여 읽어들이기 때문에 꺼내진 데이터의 순서가 보증되지 않습니다. 고속풀인덱스 스캔으로 액세스 했을 경우에는 그 후에 소트 처리가 필요한 점에 주의가 필요합니다.
각각의 특징을 정리하면 표1 과 같습니다.
| 풀색인 스캔 | 고속풀색인 스캔 | |
| 액세스 방법 | 싱글 블록 액세스 | 멀티 블록 액세스 |
| 취득 데이터의 순서 | 보증된다 | 보증되지 않기 때문에 ORDER BY 구 등이 지정되어 있는 경우 데이터 취득 후에SORT 처리가 실행된다 |
| 힌트문 | INDEX | INDEX_FFS |
| 그 외 | 패러렐로의 스캔이 불가능 | ・지정한 열중 적어도1 개는NOT NULL 제약이 필요 ・풀테이블스캔과 같이패러렐로스캔가능 |
| 표1 풀색인 스캔과 고속풀색인 스캔의 특징 | ||
그림6는 힌트문으로 고속전색인 스캔을 사용하고 또한 패러렐도4 색인 스캔 하도록 옵티마이져에 지시해 SQL 트레이스 TKPROF 유틸리티를 사용해 실행 계획을 취득하고 있습니다.
 |
| 그림6 병렬도4로 복합 색인에 의한 고속풀색인 스캔의 경우 |
그림4 의 실행 통계와 비교하면 실행 시간이 단축되고 있는 것을 알수 있습니다. (패러렐 처리의 경우,disk ,query ,current 의 값은 부정확하므로, 시간과 비교합니다). 이것은 고속풀색인 스캔의 특징인 멀티 블록 읽을 효과라고 할 수 있습니다. 그러나, 표1에도 기술한 것처럼 취득한 데이터는 소트 되어 있지 않기 때문에 「ORDER BY 구」가 지정되어 있는 경우에는, 풀색인 스캔에서는 발생하지 않는 소트 처리가 발생합니다. 그 때문에 색인의 사이즈, 꺼내지는 행수(데이터량)에 따라서는, 전색인 스캔으로 소트 처리를 배제하는 것이 퍼포먼스가 좋은 케이스도 있으므로 실제로SQL을 실행해 실행 시간을 측정, 적절한 실행 계획을 선택하도록 해야합니다.
COUNT(*)의 고속화
표시 건수를 제한하고 있는 어플리케이션에서는 처음에 검색 조건에 합치하는 건수를COUNT 하고 있는 경우가 꽤 있습니다. 그러한 어플리케이션에 대해서
SELECT COUNT(*) FROM ORDERS; |
과 같이 특정 열명을 지정하지 않는 일이 있습니다. 그러나 검색 대상이 되는 표에 주키가 설정되어 있는 경우에는 그 주키 색인을 사용한 색인전스캔을 실행하면 실행 시간을 크게 단축할 수 있습니다.
다음과 같은SQL을 실행해 SQL 트레이스,TKPROF 유틸리티를 사용해 실행 계획을 취득합니다
 |
| 그림7 COUNT(*) (을)를 사용한 전표 스캔의 경우 |
 |
| 그림8 주키 색인을 사용한 고속풀색인 스캔의 경우 |
그림8과 같이 「COUNT( 주키열) 」로 고속전색인 스캔을 사용함으로써 액세스 블록수가 큰폭으로 감소, 실행 시간이 단축되고 있는 것을 확인할 수 있습니다.
■색인 스킵·스캔의 사용
Oracle8i까지는 복합 색인을 이용시키기 위해서는 복합 색인의 선두열이 WHERE 구의 조건열에 포함되어 있을 필요가 있었습니다. 그 때문에 다양한 검색 패턴이 존재하는 경우에는 색인 스캔을 실시하게 하기 위해서 복수의 복합 색인을 작성, 그 결과 갱신등의 퍼포먼스에 악영향을 미쳐 버리는 일이 있었습니다.
Oracle9i 에서는 색인 스킵·스캔 기능 에 의해 색인내의 선두열이 WHERE 구의 조건열에 포함되지 않아도 그 복합 색인을 사용하는 것이 가능하게 되었습니다. 색인 스킵·스캔 기능을 이용하는 것으로, 테이블에 작성하는 복합 색인의 개수를 줄일 수 있기 때문에 테이블에 대한 갱신 처리의 퍼포먼스 열화를 억제하는 것이 가능하게 됩니다.
그러면, 이하와 같이 「l_shipmode 」열이 선두인 복합 색인을 사용하고, 색인 스킵·스캔의 동작을 확인해 봅시다
 |
| 그림9 복합 색인의 열위치의 확인 방법 |
「INDEX_SS 」힌트를 사용해 색인 스킵·스캔을 옵티마이져에 지시해 WHERE 구의 조건열에는 색인의 선두열이 포함되지 않은 것에 주목하고 SQL 트레이스,TKPROF 유틸리티를 사용해 실행 계획을 취득합니다.
 |
| 그림10 색인 스킵·스캔에 의한 선두열을 포함하지 않는 복합 색인의 사용 |
그림10 의 실행 계획을 보면 복합 색인의 선두 「l_shipmode 」열이 포함되지 않았음에도 불구하고 「idx2_lineitem 」색인이 색인 스킵·스캔에 의해서 사용되고 있는 것을 확인할 수 있습니다.
◇
복합 색인의 유효 이용은 검색 성능을 향상시키는데 이용할 수 있을 기회가 많이 있는 매우 중요한 테크닉이므로, 꼭 도전해 보시길
댓글 없음:
댓글 쓰기