대용량 DB 9강 정리 (인덱스 선정)
1. 추가된 인덱스 컬럼의 역할
l Select * from tab1 where A = ‘2’ and C = 51
i. A컬럼만 인덱스가 있다면
: A의 조건에 만족하는 rowid 여러 건 확인 후 table access 함
ii. A, B, C 컬럼 결합 인덱스가 있다면
: A, C 스캔해서 나오는 1건만 조회
è 불필요한 table access를 줄일 수 있다.(성능을 좌우하는 single block I/O를 줄임)
2. 결합인덱스 컬럼 순서 결정 (위-아래 순으로 우선순위)
l 조건이 항상 나오는 컬럼인 경우 그 컬럼이 앞으로 나와야 한다.
l 항상 ‘=’로 조건이 나오는가? Like나 between 조건이 들어가는 경우 (선분조건) 는 앞에 나와서는 안 된다. (뒤의 컬럼이 아무리 좋아도 테이블을 스캔해버림)
l 분포도가 좋은 컬럼인가?, SORT순서는?
: 업무시스템에 따라 우선순위가 달라질 수 있다.
분포도 우선 – 트랜잭션 시스템
Sort 우선 : DW 시스템
l 어떤 컬럼을 추가하나?
3. 인덱스 선정 절차
l 해당 테이블 액세스 유형 조사
l 반복 수행되는 액세스 경로의 해결
l 클러스터링 검토
l 인덱스 컬럼의 조합 및 순서의 결정
l 시험생성 및 테스트
l 수정이 필요한 애플리케이션 조사 및 수정
4. 액세스 유형의 조사 (설계단계)
l 반복 수행되는 액세스 형태를 찾는다
i. PK, FK, 자주찾는 이름 등
l 분포도가 아주 양호한 컬럼을 찾아 액세스 유형을 찾는다.
l 자주 조건 절에 사용되는 컬럼들의 액세스 유형을 찾는다.
l 자주 결합되어 사용되는 경우를 찾는다.
l SORT의 유형을 조사한다.
l 일련번호를 부여하는 경우를 찾는다.
l 통계자료 추출을 위한 액세스 유형을 조사한다.
5. 인덱스의 활용 (선정기준)
l 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도 향상
l 자주 조합되어 사용되는 결합인덱스 생성
l 각종 액세스의 경우의수를 만족할 수 있도록 인덱스간의 역할 분담
i. 어떤 인덱스를 타야하는지 헷갈리게 만들면 안된다.
l 기본키 및 외부키 (조인의 연결고리가 되는 컬럼 ) à 양 컬럼에 인덱스 생성하도록 함.
6. 인덱스의 활용 (고려사항)
l 새로 추가된 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음.
i. 옵티마이저는 환경에 따라서 실행계획이 바뀐다.
l 지나치게 많은 인덱스는 오버헤드를 생성
l 넓은 범위를 인덱스로 처리 시 많은 오버헤드 발생 (SINGLE BLOCK I/O)
l 옵티마이저를 위한 통계데이터를 주시적으로 갱신
l 인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성
l 분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음
i. 삼성멀티캠퍼스 수강생의 90%가 SDS 소속이다.
ii. 이 경우에는 소속ID가 SDS인 경우에는 FULL SCAN을 하고 아닌경우에는 INDEX타게 하면된다
è SELECT *
FROM XX
WHERE :V1 = ‘100’
AND CORP_ID||’’ = :V1
UNION ALL
SELECT *
FROM XX
WHERE :V1 <> ‘100’
AND CORP_ID = :V1 (좋은 예)
è SELECT *
FROM XX
WHERE CORP_ID LIKE :V1||’%’
AND STDT_ID LIKE :V1||’%’ (나쁜 예, 전체 값을 RANGE SCAN한 것)
l 인덱스 사용원칙을 준수해야 인덱스가 사용되어 짐
i. 좌변을 절대 가공하지 말 것.
7. 추가된 인덱스가 미치는 영향
l SELECT *
FROM TAB1
WHERE A = ‘10’
AND B = ‘941005’
AND C = ‘123’
IDX1 : AB , IDX2 : C 가 있다면, 결합인덱스인 IDX1를 탄다.
여기서, IDX1에 ABD로 변경하면 다른 부분에 영향을 미침