7.6 인덱스 설계

7.6.1 인덱스 대상 선정

대상 테이블 선정

• 1.인덱스의 필요 판단기준은 DBMS가 테이블을 읽어 들일 때 수행하는 MULTI BLOCK READ수에 의해 판단한다.
• 1)MULTI BLOCK READ 란 테이블 엑세스시 한꺼번에 메모리에 읽어 들이는 블럭의 수이다.
• 2)MULTI BLOCK READ 가 16일 경우 테이블 크기가 16블럭 이상이면 인덱스를 설정하도록 한다.
• ->오라클의 경우 1블럭이 8K인 경우 8블럭을,4K인 경우 16블럭을 지정할 것을 권유하고 있슴
• ->8*8=64,4*16=64이므로 결국 64K 이내의 테이블은 인덱스를 설정하지 않고 테이블 풀스캔을 해도 별 지장이 없다는 결론
• ->그러나 다른 테이블에 의해 참조되는 관계이거나 조인에 의해 처리되는 경우 PK,FK에는 인덱스를 생성해 주는 것이 좋다.

PK칼럼 인덱스

• 1.아무리 작은 테이블일지라도 PK칼럼은 반드시 인덱스를 사용해야 한다.
• 2.<부서>,<사원> 테이블이 있고 <사원>테이블에 소속부서 칼럼이 있는데 만일 <부서> 테이블의 데이타가 적다고 하여 PK인덱스를 사용하지 않는다고 가정해 보자.

SELECT A.사원번호,A.사원명,B.부서명
FROM 사원 A,부서 B
WHERE A.부서코드=B.부서코드
AND A.사원번호 BETWEEN '1' AND '100'

• 1)사원번호가 PK로 경합성에서 우수하므로 <사원> 테이블이 드라이빙 테이블이 된다.
• 2)<사원>에서 하나의 로우가 추출된 후 부서명을 가져오기 위해 <부서> 테이블과 조인이 일어나는데 <부서> 테이블에는 PK인덱스가 없기에 결국 해당 사원이 속한 부서명을 찾기 위해 <부서> 테이블을 풀스캔해야 한다.
• 3)즉, <사원> 테이블에서 추출된 로우 수 만큼 <부서> 테이블에 풀스캔 엑세스가 일어나 성능에 심각한 문제점을 초래하게 된다. 그러나 만일 <부서>테이블의 부서코드에 PK인덱스가 걸려 있다면 <사원>에서 추출된 부서코드와 조인하여 단 한번의 테이블 엑세스로 부서명을 가져올 수 있게 될 것이다.

3.PK와 유니크인덱스

2)유니크 인덱스만을 사용할 경우

<장점>

• DBA가 데이타베이스를 관리하기가 쉽다. (?)
• 개발 시점에 데이타 제약이 없으므로 개발이 용이하다.
• PK,FK를 이용하지 않으므로 성능이 다소 좋아질 수 있다. (DBMS 내부적으로 데이타딕셔내리에 관리해야할 항목이 줄어든다 (?))

<단점>

• 데이타의 무결성이 깨질 수 있다.
• 데이타의 무결성이 깨진 경우 데이타 전환작업시 데이타 정리작업이 추가적으로 필요하다.
• 데이타 모델과 테이블의 관계가 일치하지 않다.
• 유니크 인덱스는 한개의 테이블이 여려개를 만들 수 있기에 PK가 무엇인지 구분할 수가 없다.

3)결론

• 데이타베이스 구축과 운영시 중요도는 데이타정합성>성능>관리의 용이성>개발편의성이다.
• 유니크 인덱스만을 사용할 경우 성능,관리면에서는 어느 정도 더 효율적일지 모르나 가장 중요한 데이타의 정합성이 깨질 우려가 있다는 점에서 반드시 PK를 사용하는 것이 좋다.

FK칼럼 인덱스

• 1.FK칼럼에 인덱스가 걸려 있지 않다면 FK제약때문에 테이블 풀 스캔이 일어난다. (?)
• 2.또한 인덱스가 없다면 데이타의 입력,수정,삭제가 발생할 때 테이블 블럭을 유발한다. (?)
• 3.그러므로 FK칼럼에 반드시 인덱스를 걸어 주도록 한다.

인덱스 대상 칼럼 선정

• 1.테이블 내에서 자주 사용되며 분포도가 10~15% 정도인 칼럼을 대상으로 한다.
  • 분포도(%)=데이타별 평균로우수/테이블의 총 로우수*100
• 2.여러개의 컬럼이 항상 같이 사용되는 경우도 인덱스를 걸어주는게 좋다.

7.6.2 인데스 최적화

인덱스 효율검토

• 1.인덱스 칼럼은 자주 수정이 발생되지 않는 칼럼을 선정하는 것이 좋다.
• 2.평균 분포도가 10~15% 이내일지라도 분포가 기형적이면 인덱스를 설정하지 않는게 좋다. 한 칼럼의 값이 A,B,C,D 네가지 종류인데 이들의 평균분포도가 비록 15이내일지라도 어떤것은 1,어떤것은 30 이럴경우 테이블 풀 스캔이 더 좋을 수도 있는데 불필요하게 인덱스를 거쳐 테이블에 엑세스하여 성능이 저하될 수 있다.
• 3.한 테이블에 인덱스가 5개가 넘어가는 경우 진정으로 필요한 인덱스인지를 검토후 삭제하여 그 수를 줄일 필요가 있다.

인덱스 데이타타입 적용

• 1.데이타 길이가 변하는 칼럼의 경우 VARCHAR타입을 사용한다.
• 2.날짜타입의 경우 시,분,초까지 저장할 필요가 없다면 DATE형보다 VARCHAR형이 더 좋다.
• 3.가급적 NUMBER형 보단 VARCHAR형의 인덱스가 더 효율적이다.

인덱스 정렬

• 1.인덱스를 사용하는 유형이 대부분 역정렬 형태라면 인덱스를 역정렬순서로 생성하여 INDEX_DESC 힌트를 추가적으로 사용하지 않도록 한다.
• 2.결합인덱스를 생성할시 첫번째 칼럼은 분포도가 좋고,항상 사용되고,=연산자로 비교되는(검색범위를 획기적으로 줄여줄 수 있다) 칼럼을 첫번째 칼럼으로 삼도록 한다.

클러스터링 검토

• 1.분포도가 넓은 경우,즉 동일한 값들이 많이 분포하는 경우는 인덱스를 사용하는 것이 적합지 않다. 이럴경우 클러스터링을 사용하도록한다.
• 2.클러스터링은 칼럼값이 동일한 로우들을 한곳에 모아서 저장함으로써 대량의 조회시 획기적인 성능향상을 기할 수 있는 방법이다.
• 3.그러나 이 역시 조회속도는 향상시키지만 입력,수정,삭제시는 성능저하를 야기한다.

7.6.3 인덱스 정의서 작성

• 1.엔티티타입명:
• 2.인덱스 스페이스:인덱스가 물리적으로 생성할 테이블 스페이스 이름 (대용량의 DB라면 데이타가 저장되는 테이블 스페이스와 별로도 인덱스만의 테이블 스페이스를 지정하여 사용하기도 한다)
• 3.인덱스 유형:유니크,NON-유니크,클러스터
• 4.정렬:오름차순,내림차순
• 5.구분:PK인덱스,FK인덱스