인덱스 튜닝

1. 조화 조건이 걸리는데마다 인덱스를 건다고 전부 타는것이 아니다. 각 조인방식을 참고하도록 하자.

   1. 인덱스를 어떻게 타는지는 원리를 알고 약간의 훈련과 논리적인 생각으로 대충 알 수 있다.
   2. 대부분 개발자가 접하는 SQL최적화는 인덱스에서 결정난다.

   3. 경고!!

      1. 성급하게 인덱싱을 예상하고, 이 작업에 시간을 투자하지 말것. 아래 많이 써놨지만 실제 돌아가는 어플은 아주 쉽게 작업 가능하다.

      2. 인덱싱이나 튜닝 같은거 보다 DB설계와 어플리케이션 매핑이 훨~씬 더 중요하다.

         1. 별거 아닌 튜닝 / 인덱싱질로 시간낭비하지 말것!
         2. 인덱스 추가로 얻는 속도가 크지 않다면 유지보수성을 위해 최소한의 인덱스만 가지는것이 더 좋다.
         3. DB의 메모리와 CPU값이 인건비보다는 싸다는것을 항상 염두하자.
      3. 추가한 인덱스 때문에 전반적인 DB성능 저하 또는 기존 잘 작동하던 SQL이 느려지는 현상이 있을 수 있다.

2. 기본지식

   1.  인덱스 컬럼은 짧을 수록 좋다. 특히 PK나 코드성 데이터를 적절히 맞춰주자.
   2. 인덱스가 많으면 옵티마이저가 최적의 인덱스를 고르지 못할 수 있으며 변경시 많은 부하를 일으킨다.

   3. 대용량의 데이터에서 랜덤억세스 할 경우 인덱스의 손익분기점(일반적으로 15%)가 훨씬 떨어진다.

      1. 대용량에서 인덱스로 억세스를 할 경우 하드디스크의 암이 움직여야 하는 비용이 상대적으로 크다.
      2. 따라서 인덱스를 걸 경우 아주 적은 자료를 필터링할 수 있게 세심하게 전략을 짜야한다.
   4. 클러스터링 팩터(유사한 로우들이 밀집된 정도)가 액세스 효율에 많은 영향을 미친다.

   5. 인덱스를 걸기 전에 억세스 형태 및 빈도/분포도/중요도를 먼저 수집해야 한다.

      1. 빈도가 높고 중요할수록 / '='로 조인될수록 / 분포가 넓을수록 인덱싱에 유리하다.
      2. ex) =로 필터되것이 1순위, between으로 검색되는것이 2순위 등등

   6. 간혹 인덱스만 검색해도 되는 SQL을 만들겠다고 인덱스를 추가할 수 있다. -> 금지!

      1. 별도의 최적화가 가능하더라도 기존 인덱스를 사용할 수 있는 방향으로 가는것이 좋을때도 있다.

   7. 인덱스만 검색해도 order by가 되도록 만들겠다고 인덱스를 추가할 수 있다. -> 최적화에 따라 신중히 고려

      1. 인덱스 만들때 인덱스의 순서(desc등)도 중요하다. (힌트로 약간의 조정은 가능)

      2. 특정 인덱스를 태울수만 있다면 자동 정렬의 효과를 볼 수 있다.

         1. 정렬 인덱스 구성법칙 : 점조건 +  점조건 + ... + 1번 order by컬럼 + 2번 order by컬럼 + ... + 선분(between)조건

         2. 선분 조건 사용시 group by비용과 랜덤억세스 비용간에 선택을 해야한다. 정답은 데이터 양에따라 그때그때 다르다.

            1. 즉 group by 컬럼이 먼저 들어가면 뒤의 조건들은 인덱스를 타지 못한다. 반대인 경우 인덱스는 타지만 따로 group by해야 한다.

      3. 이러한 방법은 SQL이 의도한 인덱스를 반드시 탄다고 할 수 없음으로 매우 불안하고 신중히 고려해야 하는 방법이다.

         1. 이 방법은 조인 시에도 동일하게 적용된다. 단지 조인방법에 따라 차이가 날 뿐이다.

3. 복합(결합)인덱스를 타는 조건 (left most prefix)

   1. A+B+C+D로 구성된 복합 인덱스를 가정시 첫번때 인자가 있다면 인덱스를  탄다.

      1. 결합인덱스에서 첫번째 컬럼은 조직의 팀장과도 같다.

      2. 가장 자주 사용되며 카디널리티가 높고 '=' 조건으로 많이 사용되는것을 1st로 골라야 한다.

         1. 추가로 분포도가 좋아야 하며 정렬 순서로 지목되는 컬럼이어야 한다.
      3. 만약 위의 인덱스에서  A+B+D로 조회하는 건이 매우 많다면 따로 인덱스를 추가하는게 더 낫다.

   2. ex) where A and B and C and D  또는   where A and C

   3. 인덱스 스킵 스캔 (Index Skip Scan)

      1. 위의 예제에서 선행 인자가 없이 B,C로만 조건이 주어졌을 경우 A를 강제로 추가해서 인덱스를 타게 만드는 방식.

      2. ex) 위의 A가 낮은 카디널리타(1,2밖에 들어갈 수 없음)일때 a=1 ~~ union all  a=2  ~~ 이런 조건이 추가된다.

4. 조인시 인덱스 사용 / 자동정렬

   1. Nested Loops

      1. 드라이빙 테이블은 조회조건으로 집합 구성 => 이 집합으로  이너 테이블이 조인키+조회조건의 인덱스를 탄다.

      2. 두 테이블에 적용되는 인덱스에 따라 데이터는 자동 정렬된다.

   2. Hash 조인

      1. 빌드 테이블은 조회조건으로 해시 테이블 구성

      2. 프로브 테이블도 조회조건으로 집합을 만든 후 이것으로 해시를 참조해서 결과 구성

         1. 결국 결과 정렬은 자동으로 프로브 테이블의 인덱스에 맞추어짐.

   3. Sort Merge 조인

      1. 인덱스로 정렬을 자동화하는건 불가능 하다. 당 선행테이블의 소트를 없앨수는 있다.

   4. 복합 인덱스 : 조인시 인덱스를 타게 하기 위해 함수기반 인덱스 등으로 최적화된 인덱스를 꾸릴 수 있다.

      1. 이 방법으로 복잡한 조인관계의 페이징처리에서 group by절을 삭제해 성능향상을 가져올 수 있게된다.

5. 인덱싱 전략

   1. 인덱스의 수를 줄이는것이 중요하다. 결합 인덱스로 인덱스의 수는 줄일 수 있지만 선행컬럼이 사용되지 않으면 쓸 수 없다.

      1. 따라서 모든 조회조건들을 살펴본후 최대한 중첩되도록 종합적으로 인덱스를 설정해야 한다.

   2. 인덱스를 타도록 만드는것도 중요하지만 자주/많이 사용되는 SQL중심으로 구성해야 한다.

      1. 거의 사용되지 않는 문장을 위해 인덱스를 추가한다면 어느쪽이 이득인지 투자가치를 따져봐야 한다.

   3. 예측하는데 힘쓰지 마라. (특히 SI사업시)

      1. 분포도 같은건 다 만들고 나중에 생각하라. 인덱스는 언제나 추가/수정 가능하다.

6. 인덱스 종류

   1. 정렬에 따라

      1. Clustered Index : 범위 처리에 유리

      2. Non-Clustered Index : 여러개를 생성할 수 있기에 point처리에 유리

      3. 키값에 Clustered Index를 사용하면 I/O가 몰리면서 병목현상(Hot Spot Problem)이 일어날 수 있다. 주의

   2. 구조에 따라.

      1. 일반 Tree인덱스 : 디폴트

      2. 역 인덱스. => 최근 데이타가 많이 읽히는 곳 

      3. 비트맵 인덱스

         1. 캐 많은 데이터가 있고 분포가 좋지 않으며 수정의 거의 일어나지 않는 테이블

         2. DW처럼 카디널리티가 낮고 다수의 대맨전들이 다양한 구조로 결합해야 하는 경우 (B-Tree인덱스로는 매우 낮은 효울)

         3. 데이터의 수정이 일어날 경우 비트맵을 재생성해야하는 부하 발생

         4. 오라클 9i부터 비트맵 조인 인덱스 기능 제공.       

7. 인덱스 추가하기

   1.  FK에 인덱스가 모두 걸려있는지 확인.

   2. Uniq값이 들어가야 하는 곳은(사업자 등록번호 등) 반드시 uniq(index)를 설정한다.

   3. 만들어진 모든 인덱스가 사용되는게 아니다. 자주 사용되는 패턴을 참조하여 (복합)인덱스 추가(링크참조)하자.