[DataBase] 인덱스의 원리 & 스캔 & 최적화

** 데이터 진흥원 - SQL 전문가 가이드의 [과목3-3장 : 인덱스 튜닝] 내용

---

1) 인덱스 기본 원리

◼︎ 구조 : Tree 구조 / 양방향 리스트(Double Linked List)

◼︎ 탐색 : (1) 루트부터 리프까지 조건을 따라 내려간다. (2) 리프에서 조건에 맞는 ROWID의 obj, file, block, 블록 내 위치를 참조

              (2-1) 조건에 맞는 ROWID가 있는지 주변 탐색, 블록을 넘어갈 경우 다음 블록을 탐색 (3) 데이터 블록에서 레코드를 탐색

 

◼︎ 스캔 방식

    (1) Index Range Scan (Index Seek) : 가장 일반적으로 트리를 타고 내려가는 방식 (잘 활용하면 sort, min/max를 생략 가능)

    (2) Index Full Scan (Index Scan) : 최적 인덱스가 없을 때 차선으로 선택됨 : 인덱스 조정 고려

    (3) Index Unique Scan (Index Seek) : 조건에 등호가 사용된 경우, 인덱스를 수직으로만 탐색한 경우

    (4) Index Skip Scan : 루트 블록의 고윳값 개수가 작을 때, 조건이 특정 루트에만 해당될 것 같은 경우 수행된다. (명시 x, 확률추론)

    (5) Index Fast Full Scan : Index Full Scan을 Multiblock Read 방식으로 진행 (세그먼트 전체를 읽으며, 병렬 처리가 가능하다)

    (6) Index Range Scan Descending : 인덱스가 지정된 열을 내림차순으로 읽으면 인덱스를 역순으로 읽는 계획을 사용한다.

 

◼︎ 인덱스 종류

     (1) B*Tree : Balanced Tree 구조 - Update, Delete로 Skew/Sparse 문제가 발생할 수 있고, 자체 기능 or 재생성으로 해결한다.

     (2) 비트맵 : 0,1의 테이블로 인코딩을 해둔 구조 (카디널리티가 높지 않은 등호 조건 조회에 강력하다)

     (3) 클러스터 : 클러스트 테이블 이용 - 모든 데이터를 인덱스 리프 카테고리 단위로 저장한다. (느리지만 대용량에 강하다)

     + 함수 활용 : 값을 미리 전처리하여 index에 포함시키는 방법이다. (nvl, left 등이 사용될 수 있다.)

     + 리버스 활용 : 주문번호, 상담번호 등으로 인덱스를 지정하면 오른쪽 트리로만 추가가 진행된다. 이 경우 리버스된 값을 활용 가능하다

 

◼︎ 인덱스 튜닝 방법

    튜닝 규칙 - 일반적인 경우, 인덱스 선두 컬럼(루트 노드)를 탐색할 수 있어야 인덱스 범위 스캔이 수행된다.

      (1) 인덱스 선두 컬럼에는 변형(함수처리)이 일어나지 않아야 한다.

            -> 인덱스 컬럼의 비교항에다 변경을 적용한다 ( substr(idx_col,1,2) = '대한' -> idx_col like '대한%')

      (2) 인덱스 선두 컬럼에는 부정형 비교(<> 또는 is not null)를 사용하지 않아야 한다.

      (3) 인덱스 컬럼과 비교항이 타입이 다른 경우, 자동 Cast가 발생하면서 인덱스 스캔을 방해할 수 있다.

            비교항이 인덱스 컬럼의 타입과 동일하도록 명시해주는 습관이 좋다.

      (4) 인덱스에 컬럼이 부족하면 추가해주기 (조회 컬럼을 모두 소유한 인덱스를 Covered Index이라고 한다)

            Index 외 특정 컬럼을 리프에 함께 저장하는 SQL Server만의 인덱스 : Include Index (루트, 브랜치에는 빠지고 리프에만 저장)

      (5) 인덱스 선행 컬럼은 등호 연산이 이뤄지는 컬럼으로 지정한다. (루트에서 범위를 확 줄여야 낭비가 줄어든다)

      (6) 선행 컬럼에 범위 검색을 해야하는데, 범위 값이 많지 않다면 비교 연산자를 In-List로 변경한다

            (그러면, 범위가 아닌 특정 영역을 분리하여 여러 번 접근하는 방식으로 변경된다. / EX_ col in (1,2) = 1탐색 union 2탐색)

      (7) 인덱스를 통해 order by, group by 소트 연산을 대체할 수 있다. (이 경우, 조건절에 포함되지 않더라도 후행 컬럼으로 포함시킴)

  

** Oracle은 Null에 대한 인덱스를 허용하지 않고, SQL Server는 한다.

** 클러스터링 팩터가 나쁜 인덱스를 통해 대량의 데이터를 읽는 경우가 가장 튜닝하기 어렵다.

---

2) 테이블 엑세스 최소화 - 인덱스 ROWID에 의한 랜덤 엑세스

◼︎ ROWID (RID) : 인덱스의 저장 내용 : 해당 인덱스 조건에 맞는 '오프젝트 번호', '데이터 파일번호', '블록 번호' 등을 포함한다.

    -> '물리적 주소정보' 라고 부르지만 사실상 위치 정보일 뿐, 성격은 '논리적 주소정보'이다.

 

◼︎ ROWID (RID) Read 프로세스 (Table Access By Index ROWID or RID(=Bookmark) Lookup 오퍼레이션)

     (1) 인덱스에서 하나의 ROWID를 읽고 DBA(디스크 상의 블록 위치)를 해시 함수 적용하여 해시 값 확인

     (2) 해시 값을 이용해 해시 버킷을 찾아간다.

     (3) 해시 버킷에 연결된 해시 체인을 스캔하면서 블록 헤더를 찾는다.

     (4) 해시 체인에서 블록 헤더를 찾으면 거기 저장된 포인터를 이용해 버퍼 블록을 읽는다.

     (5) 해시 체인을 스캔하고도 블록 헤더를 찾지 못하면, LRU 리스트를 스캔하면서 Free버퍼를 찾는다.
           디스크에서 읽은 블록을 적재하기 위해 빈 캐시 공간을 찾는 것이다.

     (6) LRU 리스트에서 Free 버퍼를 얻지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보한다.

     (7) Free 버퍼를 확보하고 나면 디스크에서 블록을 읽어 캐시에 적재한다.

  

** Oracle은 Null에 대한 인덱스를 허용하지 않고, SQL Server는 한다.     

 

◼︎ ROWID (RID)의 성능 지표와 손익분기점

     (1) 성능지표 = 클러스터링 팩터 (군집 계수) : 데이터 저장 블록이 파편화된 컬럼에 인덱스를 걸면 군집율이 낮다고 표현

                            (RID로 데이터를 읽을 때, 여러 개의 블록을 읽어야 한다.)

                            (쉽지 않은 작업이지만 테이블을 재구성하여 클러스터링 팩터를 높으면 효과는 극적이다)

     (2) 손익분기점 = Index 사용이 Full Scan보다 성능이 떨어지는 지점 (보통 5~20%에서 결정되지만 Case By Case다)

                             Full Scan은 시퀀셜, Multiblock Read를 사용하는 반면, Index 접근은 랜덤, SingleBlock Read를 사용하기 때문   

       

 

◼︎ 설계 고려 요소

 

      (1) 선택도 : 필터를 통해 많은 량을 제외시킬 수 있는 열이 아니라면 Full Scan보다 손익분기점이 낮을 가능성이 크다.

      (2) 쿼리 수행빈도

      (3) 업무 상 중요도

      (4) 클러스터링 팩터

      (5) 데이터량

      (6) DML 부하 (= 기존 인덱스의 개수, 초당 DML 발생량, 자주 갱신되는 컬럼 포함 여부)

      (7) 저장 공간

      (8) 인덱스 관리 비용