4.1.6. 인덱스 선정 절차

가) 테이블의 액세스 형태를 최대한으로 수집

• 개발단계의 액세스 형태 수집 - 100%는 힘들지만 70~80% 정도의 인덱스 생성 필요

• 반복 수행되는 액세스 형태를 찾음
• 반복 수행되는 액세스는 자신의 수행속도에 반복 횟수를 곱한 만큼 부하를 가져오므로 수행속도에 미치는 영향이 아주 큼 → 기본키, 외부키, 서브쿼리의 컬럼, 루프 내 사용되는 컬럼
• 분포도가 아주 양호한 컬럼들을 발췌하여 액세스 유형을 조사
• 테이블에는 아주 양호한 분포도를 가진 컬럼이 있기 마련 → 단, 분포도 뿐만이 아니라 결합도도 체크
• 자주 넓은 범위의 조건이 부여되는 경우를 찾음
• 인덱스는 넓은 범위를 처리하게 될때 많은 부담을 주게 됨 → 처리범위의 최대 크기와 평균 예상범위, 자주 사용되는 정렬의 순서, 처리 유형(Group by, Order by) 등 활용
• 조건에 자주 사용되느 주요 컬럼들을 추출하여 액세스 유형을 조사
• 빈번하게 사용 된다는 것은 시스템 전반에 미치는 영향이 그 만큼 크기 때문에 아주 중요 → 매출 테이블의 '판매일자', '판매부서', '고객번호', '상태', '상품' 등
• 자주 결합되어 사용되는 컬럼들의 조합형태 및 정렬순서 조사
• 업무적인 측면에서 각 컬럼들간의 상호관계를 잘 파악해 보면 특정 컬럼들끼리 자주 조합하여 사용되는 결합형태를 찾을 수 있음 → 모든 컬럼들간 결합유형을 모두 취할 수 없음, 가장 중심되는 컬럼과 그와 연관성이 가장 큰 컬럼만으로 결합인덱스 생성 또는 클러스터링을 함으로 효율 ↑
• 역순으로 정렬하여 추출되는 경우를 찾음
• 일반적으로 최종 사용자는 조건의 범위는 벏게 부여하면서 결과는 가장 최근에 발생된 데이터부터 보기를 원하는 경우가 많음 → 힌트 사용으로 인덱스 역순 액세스
• 통계자료 추출을 위한 액세스 유형 조사
• 통계자료를 추출하는 경우는 대게 범위가 넓음 → 클러스터링이나 잘 조합된 결합 인덱스 이용

  ▼ 위의 제시 방법으로 액세스 형태 작성 (Count : 액세스 형태가 사용된 SQL 수, 가중치 활용으로 백분율로 표시하면 좋음)

• 운영단계의 액세스 형태 수집
• 애플리케이션 소스코드에서 SQL을 추출하여 분석용 테이블에 보관
• SQL-Trace 파일을 파싱하여 SQL 문장뿐만 아니라 실행계획, 현재 적용되고 있는 인덱스, 실행횟수, 처리범위 등 매우 상세한 정보 획득 가능
• 공유 SQL 영역에서 직접 SQL을 찾아오는 방법은 SQL 수집을 보다 간편하게 할 수 있도록 도움, 사용 언어에 영향을 받지 않고, TRACE 보다 장기간 수집이 가능한 큰 장점을 가짐
• 데이터 딕셔너리에서 뷰나, Stored Procedure, Database Trigger 에서 SQL 수집 보충

※ 정확한 진단정보가 있어야 적절한 처방 가능

• 수집된 SQL을 테이블 별로 출력하여 액세스 형태 기록
• 조인의 연결고리 부분 추출 후 연결고리라는 표시 ('J')

나) 인덱스 대상 컬럼의 선정 및 분포도 조사

• 액세스 유형에 자주 등장하는 컬럼
• 인덱스의 앞부분에 지정해야 할 컬럼
• 기타 수행 속도에 영향을 미칠 것으로 예상되는 컬럼

다) 특수한 액세스 형태에 대한 인덱스 선정

• 반복 액세스 되는 형태(Critical Access Path)를 찾음

∵ 반복 수행 액세스는 수행속도에 대하여 반복 수의 배수만큼 증가하기에 0.02초를 0.01초를 줄이는 것이 10초를 1초로 줄이는 것보다 효과적일 수 있음

• 0.02초를 0.01초로 줄이는 방법의 예

          1. 항상 사용되는 것, '='로만 사용되는 것을 최대한 앞에 포진

•  1번 액세스 형태 : 모두(SALENO, ITEM) '=' 사용  ☞  분포도나 정렬순서 고려
•  7번 액세스 형태 : ITEM(=), SALEDATE(like), SALEDEPT(like)
• 11번 액세스 형태 : AGENTNO(=), SALEDATE(between), ITEM(like)




   ※ 1번 액세스 형태만 볼때는 각 분포도나 정렬순서만 고려하면 되지만 

       7번, 11번 액세스 형태까지 보면 항상 사용되는 것은 ITEM

   그러나 복합적으로 생각 필요,

• SALENO는 1번 액세스 형태에만 사용되는 것으로 조사되었지만 업무적, 분포도 측면에서는 단일 사용의 경우도 큼  ☞ 중요 컬럼으로 선두에서 배제하기 쉽지 않음
• ITEM은 좋은 분포도, 업무적으로도 중요하며 자주 사용될 것으로 간주. SALENO 없이도 7번, 11번 액세스 형태로 사용 될 경우가 있기때문에 독립적 인덱스에서 선행 컬럼 가능




    ☞ 복합적 요소를 감안 시 'SALENO + ITEM' 인덱스 구성이 가장 좋음

 2. 컬럼 중 독립적으로도 수행속도를 보장 받을 수 있다면 우선적으로 결정 

    ☞ CUSTNO (좋은 분포도)

라) 클러스터링 검토

• 인덱스의 단점

: 넓은 범위 데이터를 인덱스를 경유해 찾을 경우 대량의 랜덤 액세스 발생으로 수행속도 급감

  ☞ 클러스터링으로 넓은 범위의 데이터 효과적 액세스

• 클러스터링 사용이 유리한 조건

: 분포도가 넓을 때 효과적  ☞  분포도가 좋은 컬럼은 효과가 없음 ∵인덱스 지정만으로도 효과적

• 클러스터링 사용시 불리한 조건

: 하나의 트랜잭션에 대량의 데이터가 처리될 경우

  ∵ 클러스터링은 검색의 속도만 향상 → 입력, 수정, 삭제시 부하 증가

  ▼ 액세스 수행 속도 만족도 표시 (★ : 만족,  √ : 불만족 또는 다른 인덱스 사용 필요 시)
 

마) 결합 인덱스 구성 및 순서의 결정

• 각 컬럼간 상호관계를 파악하고 결합 인덱스 구성 및 순서를 결정하여 액세스 수행 속도가 모두가 만족(★) 되도록 함

  ▼ 액세스 수행 속도 만족도 표시(★ : 만족) 와 주의사항 기록

바) 시험 생성 및 테스트

• 테이블을 복제 후 새로 구성한 인덱스 생성
• 각 역할에 맞게 'TABLESPACE' 파라미터를 사용하여 저장위치 지정 ☞ I/O 분산 효과

사) 수정이 필요한 애플리케이션 조사 및 수정

아) 일괄적용

• 잘못된 SQL의 수정과 인덱스 변경을 동시에 일괄적으로 적용
• SQL의 잘못된 점은 수정하기 쉬우나 인덱스는 쉽지 않음
• 인덱스 수정 시, SQL 수정 발생