sql Index 고려사항

1. 인덱스 대상 후보컬럼 선정 기준

• 분포도가 좋은 컬럼인가?
• • 분포도의 기준은 1%이내
  • ex) 주문 테이블의 ‘배송여부’ 컬럼은 Y/N으로 분포도는 50%이다. 논리적식별은 50%이지만 대부분의 실데이터는 Y가 될것이고 분포도가 한쪽으로 극단적으로 쏠리기 때문에 인덱스 후보로 해도된다.
  • ex) 고객 테이블의 성별은 남자/여자 로 50%이다. 하지만 이는 변하지 않는 데이터 이며, 50%의 분포도를 가지므로 인덱스가 딱히 필요치 않다
  • 결합 인덱스의 경우도 해당 결합된 데이터의 분포도를 추산하여 인덱스를 선정한다.
• 갱신이 자주 발생하지 않는 컬럼인가?
• • update가 발생하면 index를 갱신하기 때문에 성능에 좋지 않지만! 꼭 필요한 경우에는 복합인덱스로 해야 한다. ( 검색 조건에 필요한 경우에 )
• 조건절에서 자주 사용되는 컬럼인가?
• • 조건절에 있다고 무조건 인덱스를 설정해선 안된다. 쿼리의 사용 빈도를 측정후 해당 쿼리에 사용되는 조건절의 컬럼에 대해서만 인덱스를 추가해줘야 한다. 
  • 만일 하루 10번 실행 / 1000번 실행되는 두개의 쿼리에서 포함된 조인절이 있다면 해당 조건절의 인덱스, 1000번 실행되는 쿼리의 인덱스만 추가해줘도 된다. (과도한 인덱스 추가는 성능의 저하를 가져온다.)
• 조인의 연결고리에 사용되는 컬럼인가?
• 소트 발생을 제거하는 컬럼인가?
• ex) select * from 주문 
  where 상품코드 = ‘’ and 배송여주  = ‘’
  order by 주문일자 desc

 일 경우 두가지 인덱스를 고민해야 한다. 

       인덱스1 : 상품코드 + 배송여부

       인덱스2 : 상품코드 + 배송여부 + 주문일자

소트부하가 크다면 2번으로 인덱스부하가 크다면 1번으로 해야 한다. ( 통계적으로 알수 없으니 대부분2번으로 한다. )

2. 인덱스 생성시 유의사항

• 인덱스 생성시 유사 인덱스가 있는지 확인
• • 유사 인덱스끼리의 경합에 의해 더 느린 인덱스를 실행하여 성능문제 발생
• 미사용 인덱스 일 경우에만 삭제하기

3. RBO / CBO

• RBO(Rule Based Optimizer) - 현재는 사용되지 않음
• CBO ( Cost Based Optimizer) - 주기적으로 통계정보(AO : Analyze Object)를 갱신하여 가장 적게드는 실행계획을 결정

4.  쿼리와 인덱스

• select * from 주문, 고객
  where 주문.고객번호 = 고객.고객번호
  and 주문.주문일자 = ?
  and 고객.고객명 = ?

• 위와 같은 쿼리에선 인덱스를 주는 방법은 
• • 주문일자로 검색할 경우 : 주문일자 + 고객.고객번호 
  • 고객명으로 검색할 경우 : 고객명 + 주문.고객번호
  • 주문일자+고객명으로 검색할 경우 : 주문일자 + 고객.고객번호 + 주문.고객번호 + 고객명
  • 위의 세가지 경우 3번은 optimizer가 최소 비용으로 선택하지만, 1, 2의 검색일 경우 타지 않을 수 있다
  • 검색의 조건에 따라 인덱스가 필요하며, 필요한 경우 힌트를 이용해 강제로 인덱스를 지정해야 한다

5. 테이블 수정과 인덱스

select * 

from 사원, 교육계획, 과목, 강사, 교육평가

where 사원.사원번호 = 교육계획.사원번호

and 교육계획.과목번호 = 과목.과목번호

and 교육계획.강사번호 = 강사.강사번호(+)

and 교육계획.교육번호 = 교육평가.교육번호

and 사원.성명 = ‘홍길동’

and 과목.과목명 = ‘수학’

and 교육평가.평정 = ‘A+’

위의 쿼리를 수정및 인덱스 추가 일 경우

1. 최초 진입형 테이블 우선
1. join의 첫번째 사원 -> 교육계획 -> ? -> ? -> ?
2. outer 도바 inner가 먼저 
1. 강사 테이블의 경우 outer이므로 사원 -> 교육계획 -> ? -> ? -> 강사
3. where절에서 데이터를 축소 할수 있는것 부터
1. 과목.과목명 = ‘수학’ 은 몇개의 과목명 분포가 있는지 알수 없음 반면 교육평가.평점 = ‘A+’일 경우 F~A까지의 분포가 예상되므로 먼저 불러들어 데이터 축소를 할수 있음  사원 -> 교육계획->교육평가->과목->강사 순으로 쿼리 수정
4. 최종적으로 다음과 같이 수정
5. 1. select * from 사원, 교육계획, 교육평가, 과목, 강사
      where 사원.사원번호 = 교육계획.사원번호
      and 교육계획.교육번호 = 교육평가.교육번호
      and 교육계획.과목번호 = 과목.과목번호
      and 교육계획.강사번호 = 강사.강사번호(+)
      and 사원.성명 = ‘홍길동’
      and 교육평가.평점 = ‘A+’
      and 과목.과목명 = ‘수학’

6. 수직분할 / 수평분할

    만약 한 테이블에 수많은 컬럼이 존재 한다면 디스크의 여러 블록에 데이터가 저장되므로 I/O 성능 저하를 불러온다. 

- 로우체이닝 : 길이가 너무 커서 하나의 블록에 저장되지 못하고 다수의 블록에 나누어져 저장

- 로우마이그레이션 : 수정된 데이터를 해당 테이터 블록에 저장하지 못하고 다른 블록의 빈 공간에 저장

- 수직분할 : 컬럼을 기준으로 분할하는 것으로 일반적으로 정규화에 속한다 

- 수평분할 : 대용량 테이블을 같은 테이블을 여러개 생성하여 일정한 구분에 따라 데이블을 나누는 것으로 보통 시간적(일별 / 월별 / 연도별 데이터), 지역별(서울, 경기 등 ) 으로 나누어 테이블을 관리하여 성능을 향상시킨다.

+ 하나의 테이블에 대량의 데이터가 있다면 파티션 분할을 통해 성능 개선에 도움을 줄수 있다.