메뉴 건너뛰기

bysql.net

이화식 선생님의 새로쓴 대용량 데이터 베이스 1의 온라인 스터디 입니다.
매주 각 스터디 팀원들이 담당 분량을 정리해서 올리고 토론식으로 진행합니다.
스터디 포스팅은 팀원들에의해 이루어지지만 스터디 참여는 사이트 회원 모두가 가능합니다.

진행기간: 2009.07 ~ 2009.10. 종료

4.1.6. 인덱스 선정 절차

2009.08.24 06:58

휘휘 조회 수:102471

4.1.6. 인덱스 선정 절차

인덱스 선정을 위한 전체적인 접근 절차
ㅁ관계형 DB를 사용하는 애플리케이션은 옵티마이져 액세스 경로 판단 기준으로 수행속도를 향상
 (인덱스,클러스터의 조정, 통계정보 관리등으로 최적의 실행계획을 생성)
ㅁ인덱스는 특정 애플리케이션이나 특정 액세스를 위해서만 생성하는것이 아님
ㅁ현재나 향후의 예상되는 액세스 형태를 종합적으로 감안하는 전략이 필요

테이블의 액세스 형태를 최대한으로 수집
인덱스 대상 컬럼의 선정 및 분포도 조사
특수한 액세스 형태에 대한 인덱스 선정
클러스터링 검토
결합 인덱스 구성 및 순서의 결정
시험 생성 및 테스트
수정이 필요한 애플리케이션 조사 및 수정
일괄 적용


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

개발단계의 액세스 형태수집

[그림 1-4-7] 표 참조

ㅁ 반복 수행되는 액세스 형태를 찾는다
- 자신의 수행속도에 반복 횟수를 곱한 만큼의 부하를 가져옴(속도에 많은영향이 있음)

ㅁ 분포도가 아주 양호한 컬럼드을 발췌하여 액세스 유형을 조사한다
- 하나의 컬럼만을 대상으로 하지말고 경우에 따려 여러개의 컬럼을 검토

ㅁ 자주 넓은 범위의 조건이 부여되는 경우를 찾는다
- 인덱스의 경우 넓은 범위를 처리할때 부담을 많이줌
- 처리범위의 최대크기와 평균 예상범위, 처리범위(order by,group by 등) 을 표시

ㅁ 조건에 자주 사용되는 주요컬럼들을 추출하여 액세스 유형을 조사한다

ㅁ 자주 결합되어 사용되는 컬럼들의 조합형태 및 정렬순서를 조사한다
- 각 컬럼간의 상호관계를 파악후 조사

ㅁ 역순으로 정렬하여 추출되는 경우를 찾는다
- 최근 자료를 필요로 하는 데이터등

ㅁ 통계자료 추출을 위한 액세스 유형을 조사한다
- 통계처리는 범위가 넓기 때문에 수행속도에 영향을 줄수 있음


운영단계의 액세스 형태수집

- 애플리 케이션이 작성되어 있는단계 (테스트시점, 가동중인시스템등)

ㅁ애플리케이션 소스코드에서 SQL을 추출하여 분석용 테이블에 보관한다
- 애플리케이션 명칭, SQL에 속한 서브루틴과 위치, SQL유형, SQL 원문, 사용 테이블, 사용컬럼, ORDER BY 컬럼, GROUP BY 컬럼, 반복사용여부등 저장
- SQL 추출 (소스크드에서 직접추출, SW 이용 - TOAD 의 경우 SQL MONITOR 제공)

ㅁSQL-Trace 파일을 파싱
- SQL문장뿐만 아니라 실행계획,현재 적용되고 있는 인덱스,실행횟수, 처리범위 등 매우 상세한 정보록 획득

ㅁ공유 SQL영역에서 직접 SQL을 찾음
- SQL수집을 보다 간편하게 할수 있도록 도와줌 , 언어의 영향을 받지 않으며, TRACE보다 장기간 수집이 가능


수집된SQL을 테이블 별로 출력하여 액세스 형태기록


예- [그림 1-4-8] 표
- 액세스 형태를 좀더 통합된 형태로 모을수 있다면 유형도 줄어들고 전략 수립도 단순해짐
- 조인의 연결고리부분은 무조건 추출하고 연결고리로 알수 있도록 표시 (EX) (J)등)


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

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

조건절이라고 무조건 인덱스 구성에 참여할 필요는 없으며
예상되는 액세스 유형을 조사하여 인덱스 대상 컬럼 선정과 분포도록 파악하여 효율적인 구조를 연구
현행인덱스가 있다면 조사한 액세스 형태가 어떤 형태를 사용하는 등의 파악이 필요


다)특수한 액세스 형태에 대한 인덱스 선정
반복해서 액세스되는 형태(Critical Access Path) 찾기
-  자신의 속도 * 횟수  이므로 횟수나 속도가 늘어남에 따라 최종결과도 영향을 받음

혼자만으로 확실한 수행속도를 보장받을수 있다면 우선적으로 결정

라)클러스터링 검토
넓은 범위의 처리를 향상시킬수 있는 방법을 찾기
- 인덱스는 여러개를 동원해야 하지만 클러스터로 액세스 형태를 해결할수 있다면 적용가능 여부 판단 필요
- 투자대비 효과(ROI; Return On Investment) 를 확인해야함

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

윗단계에서 처리한 컬럼들을 제외한 나머지 컬럼들을 대상으로 액세스 유형들을 다시 한번 확인하여
두 컬럼이상의 결합으로 인덱스 생성의 부담보다 생성시 이점이 발견될 경우
새로운 결합 인덱스 생성

바)시험생성 및 테스트
인덱스의 변경은 기존 실행계획에 변화를 가져오므로 운영중인 시스템에 적용시키지 말고 테스트 과정을 거쳐야함
테스트 결과 예상했던 실행계획이 나타나지 않거나 한다면 액세스 형태에 필요한 힌트등을 기록

사)수정이 필요한 애플리케이션 조사 및 수정
특정한 액세스 경로로 유도하기 위한 힌트나 인덱스 사용제한, 해제등의 수정

아)일괄적용
애플리케이션이 실행된되는 순간 파싱이 수행(Runtime parsing)
하지만 액세스 형태를 수집할때 잘못된 SQL을 발견하면 SQL의 수정사항등을 같이 기록하여야하며
필요시 애플리케이션의 수정을 요구할때도 있다.