1.3 인덱스의 선정
가. 인덱스 대상 테이블의 선정
- 테이블의 크기가 적은 것(약 5~6 블럭이하)는 인덱스를 만들지 않아도 무방하다.
하지만 조인의 연결고리가 되는 컬럼에 인덱스가 없으면 조인의 방향이 달라질 수 있으므로 연결고리가 되는 컬럼은
인덱스를 생성시키는 것이 좋다.
- 같은 값이 적은 컬럼(분포도가10% 이하인 컬럼)
- 랜덤 액세스가 빈번한 경우.
- 특정범위의 데이터나 특정 순서로 스캔이 요구되는 경우.
- 다른 테이블과 순차적 조인(Nested Join)이 발생되는 경우.
- 단순 보관용 이거나 전체조회용일 경우에는 인덱스를 생성하지 않는다.
- 클러스트링이나 해쉬를 통해 보다 양호한 엑세스를 얻을 수 있는 경우엔 인덱스와 역할을 적절히 분할한다.
나. 인덱스 컬럼의 선정
(1) 분포도와 손익분기점
 |
분포도
- 분포도 = 데이터별평균로우수 / 테이블의 총 로우수 X 100 = 1 / 컬럼값의 종류 X 100
=> 인덱스를 생성하려는 컬럼의 분포도는 10~15% 이하여야 한다. |
(2) 인덱스 머지(Merge)
SELECT *
FROM TAB1
WHERE COL1 = 'ABC'
AND COL2 = 123;
<대용량데이터베이스 솔루션1 34page 그림참고>
- B*Tree 방법으로 COL1='ABC' 와 COL=123인 첫 번째 로우를 찾는다.
- 두 개의 로우에 있는 ROWID를 비교하여 적은 값을 가진 쪽을 먼저 스캔 한다. ROWID가 더 커질 때까지 스캔 한 후 멈춘다
(col2 rowid = 7~32)
- COL1 인덱스의 ROWID를 ②의 방법으로 차례로 스캔.
(col1 rowid = 32~67)
- COL2 인덱스를 COL1의 ROWID보다 커질 때까지 스캔.
(col2 rowid = 32~67)
- COL1 인덱스의 다음 로우를 액세스 하려고 할 때 값이 'ABC' 가 아니므로 중단.
위와 같이 인덱스 머지가 발생을 하면,
COL2 인덱스를 사용하지 않고 COL1 인덱스만 사용하는 것이 유리.
<대용량데이터베이스 솔루션1 35page 그림참고>
- COL2인덱스를 사용하지 않고 COL1인덱스만 사용.
1. B*Tree 방법으로 COL1 인덱스에서 값이 'ABC'인 첫 번째 로우를
찾는다.
2. ROWID 정보를 이용하여 테이블의 로우를 엑세스 한다.
3. 테이블 로우에 있는 COL2 값이 123인 것만 취한다.
4. COL1 인덱스를 차례대로 스캔 하여 위의 방법을 반복하여 'ABC'가 아니면 작업을 중단.
특별한 경우를 제외하고는 인덱스 머지를 하는 것보다는 분포도가 좋은 하나의 인덱스만 사용하는 것이 유리하다.
- 분포도가 아주 양호하지 않더라도 인덱스를 생성해야만 하는 경우 결합인덱스를 사용하면 액세스 속도가 향상된다.
- 결합인덱스는 인덱스 머지를 하여 성공한 결과들을 저장하고 있기 때문에 그 만큼 유리하다.
(3) 결합인덱스의 특징
- 결합인덱스의 앞선 컬럼이 '='로 사용되지 않을때 그 뒤의 컬럼은 '='로 사용했더라도 처리할 범위는 줄어들지 않는다.
따라서 앞선 컬럼들의 '=' 비교가 중요하다.
예제)
SELECT *
FROM TAB1
WHERE COL1 = 'A'
AND COL3 = '10';
가정)
인덱스: COL1+COL2+COL3
COL2: 1,2,3 으로 구성
=>
SELECT *
FROM TAB1
WHERE COL1 = 'A'
AND COL3 = '10'
AND COL2 in ('1','2','3');
(4) 결합인덱스의 컬럼순서 결정방법
|
1.항상 사용되는가?
- 결합인덱스의 첫번째 컬럼이 조건에서 사용되지 않으면 그 인덱스는 사용되지 않는다.
- 첫번째 컬럼은 처리범위의 결정에도 영향을 미친다.
|
|
2.항상 '='로 사용되는가?
- 만약 '='로 사용되지 않는다면 설사 두번째 컬럼이 '='로 사용된다 하더라도 처리범위는 줄어들지 않는다.
|
|
3.분포도가 좋은 컬럼 우선
|
|
4.자주사용되는 정렬의 순서 우선
- 인덱스는 결합된 컬럼의 순서대로 저장되므로 정렬순서대로 구성되어 있으면 유리
|
|
5.어떤 컬럼을 추가
- 처리범위를 줄이는데 많은 기여는 못하지만, 랜덤액세스를 줄여주거나, 테이블을 엑세스하지 않고 인덱스만 액세스 하도록 유도하는 등
|
(5) 손익분기점 이하의 분포도를 가진 컬럼의 인덱스 생성
- 분포도가 10~15%인 인덱스를 '=' 로 액세스 할 경우가 전체 테이블을 액세스하는 경우와 손익분기점이 된다.
- 손익분기점 정도의 분포도를 가진 인덱스를 만들어서는 안된다.
- LIKE, BETWEEN, >, < 등으로 처리하는 다양한 범위를 가지는 경우 인덱스를 사용함으로 얻어지는 이익,사용의 빈도, 목표하는 수행속도의 수준에 따라 인덱스 생성여부를 결정해야 한다.
- 하나의 컬럼으로 독립된 인덱스를 생성하는 경우는 손익분기점 훨씬 이내의 양호한 분포도를 가져야 한다.
(6) 손익분기점 이상의 분포도를 가진 컬럼의 인덱스 생성
- 컬럼의 분포도가 10~15% 이상이라면 일반적으로 인덱스가 없는 것이 유리하다.
- 부분범위처리로 유도한다면 손익분기점 이내일 수 있다.
SELECT dept, SUM(sal)
FROM emp
WHERE job='SALESMAN'
GROUP BY dept |
SELECT dept, ename, sal
FROM emp
WHERE job='SALESMAN'
ORDER BY job DESC |
SELECT /*+ INDEX_DESC(a job_index) */
dept, ename, sal
FROM emp a
WHERE job='SALESMAN' |
- 만약 10000개의 로우를 가진 EMP테이블의 JOB컬럼이 1/5의 분포도를 갖는다면...*
- 첫번째 SQL - 인덱스 없이 전체 테이블을 읽는 것이 유리하다.
- 두번째 SQL - 역시 인덱스 없이 전체 테이블을 읽는 것이 유리하다.
- 세번째 SQL - 정렬작업을 INDEX_DESC를 사용하여 대체하였고, 부분범위처리를 유도하였음.
다. 인덱스 선정 절차
선정절차
1) 해당 테이블의 가능한 모든 액세스 유형 조사
2) 대상 컬럼의 선정 및 분포도 분석
3) 반복 수행되는 액세스경로(Critical Access Path)의 해결
4) 클러스터링 검토
5) 인덱스 컬럼의 조합 및 순서의 결정
6) 시험생성 및 테스트
7) 수정이 필요한 애플리케이션 조사 및 수정
8) 일괄 적용
(1)설계단계의 인덱스 선정
1) 해당 테이블의 액세스 형태의 수집
- 해당 테이블을 액세스 하는 가능한 모든 형태를 조사
- 개발완료후 보유할 실제 데이터 양만큼을 가진 테이블과 현재 판단할 수 있는 가능한 최적의 인덱스를 지정해 두고 개발을 진행할 것을 권장.
1 반복 수행되는 액세스 형태를 찾는다.
-조인시 연결고리가 되는 Primary Key, Foreign Key를 찾아 검토한다.
2 분포도가 아주 양호한 컬럼들을 발췌하여 액세스 유형을 조사한다.
-분포도가 아주 양호한 컬럼은 인덱스를 통해 수행속도를 향상시키는데 많은 공헌을 한다.
3 자주 넓은 범위의 조건이 부여되는 경우를 찾는다.
-인덱스는 넓은 범위를 처리 할 때 부담을 주게 된다.
4 조건에 자주 사용되는 주요 컬럼들을 추출하여 액세스 유형을 조사한다.
-빈번하게 사용된다는 것은 시스템 전반에 미치는 영향이 그만큼 크다.
5 자주 결합되어 사용되는 컬럼들의 조합형태 및 정렬순서를 조사한다.
-결합도를 확실히 표현한다. (자주 사용되는 형태별로 우선 순위를 부여한다.)
-결합조건을 같이 표시한다. (LIKE, =)
-정렬 순서를 같이 표시한다.
6 역순으로 정렬하여 추출되는 경우를 찾는다.
7 일련번호를 부여하는 경우를 찾는다.
8 통계자료 추출을 위한 액세스 유형을 조사한다.
-통계자료는 넓은 범위의 액세스가 발생하기 때문에 클러스터링이나 잘 조합된 결합인덱스를 이용 할 수 있게한다.
2) 대상컬럼의 선정 및 분포도 조사
대상 컬럼의 선정 기준
- 엑세스 유형에 자주 등장하는 컬럼
- 인덱스의 첫 번째 컬럼으로 지정해야 할 컬럼
- 수행속도에 영향을 미칠 것으로 예상되는 컬럼
3) 반복 수행되는 액세스경로(Critical Access Path)의 해결
액세스 시간 = 수행속도 X 반복횟수
4) 클러스터링 검토
- 클러스터링을 함으로써 넓은 범위의 데이터를 보다 효과적으로 액세스 할 수 있다. (LIKE, BETWEEN 등..)
- 클러스터링 할 컬럼은 인덱스와 달리 분포도가 넓은 것이 더 유리하다.
(좁은 분포도를 가지는 컬럼은 적절한 인덱스 지정만으로도 가능하므로 클러스트링 할 필요가 없음)
- 클러스터링은 테이블당 하나씩만 가능하므로 가장 자주 범위처리를 하는 컬럼을 선정해야 한다.
- 클러스터링은 오로지 검색의 속도를 향상시켜 주는 것이며 입력, 수정, 삭제시는 부하가 증가한다.
5) 인덱스 컬럼의 조합 및 순서의 결정
=> p66 그림 참조
1번: 한건이 액세스되었으므로 아주 만족
2번: 'SALEDATE+SALEDEPT' 로 구성된 인덱스는 없지만 SALEDATE로 클러스트링 되었으므로 '='로 사용한다면 거의 한건을 액세스 하는 속도를 낼 수 있다.
3번: 'SALEDATE like'의 범위를 1~2개월 이내로 제한한다면 2번의 클러스터로도 수행속도를 보장 받을 수 있음.
만약 범위가 그 이상으로 늘어난다면 'CUSTNO+SALEDATE'로 구성된 인덱스 필요 (일단 보류해 둠)
4번: 'STATUS'에 상관없이 클러스터 만으로 충분함.
5번: Quiz에서 다룸
6번: 3번과 동일한 경우
7번: 6번과 유사한 경우. SALEDEPT like의 범위가 얼마나 될지 모르므로 일단 보류.
8번: 7번과 유사함. 단, 'STATUS'에 따라서 달라질 수 있으므로 'STATUS'인덱스 사용여부를 기록해 놓음.
9번: 6번과 동일한 경우.
=> p68 그림 참조
3번: SALEDATE의 범위를 제한하거나, 'CUSTNO+SALEDATE'로 구성된 인덱스 생성.
10번: 3번에서 'CUSTNO+SALEDATE'로 인덱스를 생성하기로 했으므로 해결.
11번: 'SALETYPE'의 분포도가 50%이므로 인덱스 없는 것이 유리.
6) 시험생성 및 테스트
7) 수정이 필요한 애플리케이션 조사 및 수정
8) 일괄적용
(2)튜닝단계의 인덱스 선정
1) 해당 테이블의 액세스 형태 수집
- 애플리케이션 소스코드에서 SQL을 추출하여 분석용 테이블에 보관한다.
- 분석용 테이블에 저장된 정보를 테이블별, SQL 유형별, 반복사용 여부 별로 정렬시켜 SQL 원문을 출력시킨다.
- 출력물을 이용하여 액세스 형태를 조사하여 양식에 기록한다.
2) 대상 컬럼의 선정 및 분포도의 조사
- 분포도의 조사
- 주요 컬럼을 선정하고 평균, 최대, 최소 로우수를 조사한다.
(주요컬럼: 액세스 유형에 자주 등장하는 컬럼, 인덱스의 첫 번째 컬럼으로 지정해야 할 컬럼, 수행속도에 영향을 미칠 것으로 예상되는 컬럼)
- 분포도를 파악하기 위해서 컬럼 값의 종류를 산출
- 평균, 최대, 최소 로우수 산출
- 평균 로우수에 비해 최대 로우수가 큰 컬럼을 산출
- 넓은 분포도를 가지는 컬럼을 '특기사항'에 기록
(만약 특정 하나의 값이 높은 분포도를 갖는다면 이 값을 Null로 바꾸어도 좋은지 검토한다)
- 사용인덱스의 표시 및 문제점 분석
- EXPLAIN_PLAN 을 실행하여 액세스 경로 및 사용 인덱스를 파악하고, 문제점을 분석
3) 반복수행되는 액세스 경로의 해결
4) 클러스트링 검토
5) 인덱스컬럼의 조합 및 순서의 결정
- 일차확정한 인덱스나 클러스터를 '변경인덱스'란에 기록.
- 양호한 분포도를 가진 컬럼들은 별도의 인덱스로 구성.
- 대다수의 양호하지 않은 분포도를 가진 컬럼들은 다른 컬럼과 적절히 결합.
6) 시험생성 및 테스트, 수정이 필요한 애플리케이션 조사 및 수정, 일괄적용
|
