SQLP/오라클 성능 고도화 원리와 해법

인덱스 원리와 활용

개발자 키우기 2024. 1. 11. 18:29

1. 인덱스 구조

  • 인덱스 중에서 가장 일반적으로 사용되는 B*Tree 인덱스 구조는 루트를 포함한 브랜치 블록에 저장된 엔트리에는 하위 노드 블록을 찾아가기 위한 DBA(Data Block Address) 정보를 갖고, 최말단 리프 블록에는 인덱스 키 칼럼과 함께 해당 테이블 레코드를 찾아가기 위한 주소정보(rowid)를 갖는다. 리프 블록은 항상 키 칼럼 순으로 정렬돼 있으며 키 값이 같을 때는 rowid 순으로 정렬된다.
  • LMC(Left Most Child)는 브랜치 노드의 각 첫 번째 앤트리로 명시적인 키 값을 갖지 않더라도 키 값을 가진 첫 번째 엔트리보다 작은 값을 의미한다. 따라서 브랜치 블록의 자식 노드 중 가장 왼쪽 글에 위치한 블록을 가리킨다.
  • 리프 노드상의 인덱스 레코드와 테이블 레코드 간에는 1:1 관계를 가지며 키 값과 테이블 레코드 키 값은 서로 일치한다. 브랜치 노드상의 레코드 개수는 하위 레벨 블록 개수와 일치하며 키 값은 하위 노드가 갖는 값의 범위를 의미한다.
  • 중복 값이 많은 인덱스 탐색 과정에서 브랜치 블록을 따라 수직적 탐색을 진행할 때는 찾고자 하는 값보다 키 값이 작은 엔트리를 따라 내려간다. 
  • rowid에는 데이터 오브젝트 번호, 데이터파일 번호, 블록 번호, 로우 번호를 포함하고 있다.

2. 인덱스 기본 원리

  • 인덱스 칼럼을 조건절에서 가공하거나 부정형 비교, is not null, is null을 사용하면 인덱스 사용이 불가능하거나 범위 스캔이 불가능하다. 또한 묵시적 형변환 사용 시 인덱스 사용을 막을 수 있기 때문에 주의해야 한다. 특히 decode 함수는 세 번째 인자가 null 값이면 varchar2로 인식하기 때문에 주의해야 한다.

3. 다양한 인덱스 스캔 방식

  • Index Range Scan은 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식이다. 인덱스를 스캔하는 범위를 얼마냐 줄이고 테이블로 액세스 하는 횟수를 줄일수록 성능이 좋다. Index Range Scan이 가능하게 하려면 인덱스를 구성하는 선두 칼럼이 조건절에 사용되어야 한다. 또한 결과집합은 인덱스 칼럼 순으로 정렬된 상태이기 때문에 sort order by 연산과 min/max 값을 빠르게 추출할 수 있다.
  • Index Full Scan은 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다. 인덱스 선두 칼럼이 조건절에 없을 때 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 일부에 대해서만 테이블 액세스가 발생할 수 있다면 ndex Full Scan을 사용하고 아니면 Full Table Scan을 수행한다. 또한 결과집합은 인덱스 칼럼 순으로 정렬된 상태이기 때문에 sort order by 연산과 min/max 값을 빠르게 추출할 수 있기 때문에 옵티마이저가 전략적으로 선택할 수도 있다.
  • Index Unique Scan은 수직적 탐색만으로 데이터를 찾는 스캔 방식이다. Unique 인덱스를 = 조건으로 탐색하는 경우이다.
  • Index Skip Scan은 루트 또는 브랜치 블록에서 읽은 칼럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스 하는 방식이다. 브랜치 블록 버퍼를 Pinning 한 채로 리프 블록을 방문했다가 다시 브랜치 블록으로 되돌아와 다음 방문할 리프 블록을 찾는 과정을 반복한다. 인덱스 선두 칼럼이 조건절로 사용되지 않고 인덱스 선두 칼럼의 중복 개수가 적고 후행 칼럼의 중복 개수가 많을 때 사용된다. 또한 인덱스 선두 칼럼이 = 조건절로 사용되고 중간 칼럼에 대한 조건절이 누락된 경우에도 사용될 수 있다. 선두 칼럼이 범위검색 조건일 때도 사용된다. In-List가 가능하다면 변경을 고려해봐야 한다.
  • Index Fast Full Scan은 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하며 병렬 쿼리가 가능하다. 쿼리에 사용되는 모든 칼럼이 인덱스 칼럼에 포함돼 있을 때만 사용가능하며 결과집합이 인덱스 키 순서대로 정렬되지는 않는다. Index Fast Full Scan은 버퍼 캐시 히트율이 낮아 디스크 I/O가 많이 발생할 때 유리하다.
  • Index Range Scan Descending은 Index Range Scan과 기본적으로 동일한 스캔 방식이지만 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다.
  • And-Equal은 두 개 이상 인덱스를 함께 사용하는 방법 중 하나지만 현제는 잘 사용하지 않는다. 단일 칼럼의 Non-Unique 인덱스여야 함과 동시에 인덱스 칼럼에 대한 조건절이 = 이어야 한다. 단일 칼럼 인덱스를 두 개 이상 결합해 테이블 액세스량을 줄이는데 목적이 있다.
  • Index combine은 두 개 이상 인덱스를 함께 사용하는 방법 중 하나로 일반 B*Tree 인덱스를 스캔하면서 각 조건을 만족하는 레코드의 rowid 목록을 가지고 비트맵 인덱스 구조를 하나씩 만들어 Bit-Wise 오퍼레이션을 수행한다. 참인 비트 값들을 rowid 값으로 환산해 rowid 목록을 얻어 테이블을 액세스 하는 구조이다. 데이터 분포도가 좋지 않은 두 개 이상의 인덱스를 결합해 테이블 Random 액세스량을 줄이는 목적이 있다. 
  • Index Join은 크기가 비교적 작은 인덱스에서 키 값과 rowid를 읽어 PGA 메모리에 해시 맵을 생성하고 다른 쪽 인덱스를 스켄하면서 같은 rowid끼리 조인하여 성공한 레코드만 결과집합에 포함시키는 구조이다. 

4. 테이블 Random 액세스 부하

  • 클러스터링 팩터(CF)는 특정 칼럼을 기준으로 같은 값을 같은 데이터가 서로 모여있는 정도를 의미한다. 클러스터링 팩터가 높으면 인덱스를 스캔하면서 읽는 테이블 블록들의 캐시 히트율이 높아지고 퍼버 Pinning에 의해 논리적/물리적인 디스크 I/O 횟수가 감소한다. 
  • 인덱스의 손익분기점은 일반적으로 5~20%의 낮은 수준에서 결정도지만 CF에 따라 편차가 크다.
  • 손익분기점을 극복하기 위해서는 IOT, 클러스터 테이블, 파티셔닝 등을 고려해 볼 수 있다.

5. 테이블 Random 액세스 최소화 튜닝

  • 실 운영 환경에서는 인덱스 구성을 함부로 바꾸기가 쉽지 않기 때문에 상황에 따라 새로운 인덱스를 구성하거나 기존 인덱스에 칼럼을 추가하는 것이 좋을 방안이 될 수 있다.
  • NL 조인할 때 Inner 쪽에서 액세스 될 때 Random 액세스가 일어나게 되고 필터 조건에 의해 버려지는 레코드가 많다면 PK 인덱스에 칼럼을 추가해 볼 수 있다. 단 컬럼 추가로 인해 클러스터링 팩터가 안 좋아질 수 있기 때문에 확인이 필요하다.
  • 사용빈도가 높은 칼럼이라면 모든 필요한 칼럼을 인덱스에 포함시키는 방법도 대안이 될 수 있다. 그만큼 DML 속도가 느려질 수도 있지만 Randow 액세스가 없어지므로 더 효율적일 수 있다.
  • 테이블 rowid 값을 이용해 레코드를 조회하면 버퍼 Pinning 효과로 인해 Random 액세스 비효율을 줄일 수 있다.
  • 수동으로 클러스터링 팩터를 높이기 위해 테이블을 재생성하여 CF를 인위적으로 좋게 만들 수도 있다. 가장 자주 사용되는 인덱스를 기준으로 삼아야 하며 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크해야 한다.
  • 데이터 이관 시 ASIS 대비 TOBE 시스템의 CF가 나빠지지 않는지 조사하고 조치를 취해야 한다.

6. IOT, 클러스터 테이블 활용

  • IOT(Index Organized Table)는 테이블을 인덱스 구조로 생성하는 것이다. PK 칼럼 순으로 정렬하며 정렬된 상태로 데이터를 삽입한다. 따라서 데이터를 삽입할 때 성능이 느려지는데 인덱스 분할로 인하여 부하가 가해지기 때문이다. 하지만 Sequential 방식으로 데이터를 액세스 하고 넓은 범위를 액세스 할 때 유리하며 PK 인덱스를 위한 공간이 필요 없기 때문에 장점 또한 뛰어나다. 
  • IOT는 크기가 작고 NL조인으로 반복 룩업하는 테이블이거나 폭이 좁고 긴 테이블, 넓은 범위를 주로 검색하는 테이블, 데이터 입력과 조회 패턴이 서로 다른 테이블일 때 사용하면 좋다. 하지만 PK 이외 칼럼이 많은 테이블일수록 부적합하다.
  • 수억 건에 이르는 테이블을 단일 IOT로 구성하기는 어렵기 때문에 Partitioned IOT를 고려해 보자.
  • IOT 테이블을 조회할 때 시스템 관리 속성과 같은 칼럼들을 다른 주요 칼럼과 분리 저장할 수 있는 Overflow 기능을 사용할 수도 있다.
  • 오라클은 secondary 인덱스로부터 IOT 레코드를 가리킬 때 물리적 주소 대신 logical rowid(PK+physical guess)를 사용한다. physical guess는 secondary 인덱스를 최소 생성하거나 재생성한 시점에 IOT 레코드가 위치했던 데이터 블록 주소다. 따라서 오라클은 pct_direct_access 값이 100 퍼 미만이면 PK를 이용해 IOT를 액세스 하고 100일 때만 physical guess를 사용한다. 물론 physical guess로 액세스를 못했을 경우 PK를 이용해 액세스 한다.
  • 비휘발성 IOT 테이블이라면  pct_direct_access 값이 100을 가리키도록 유지하면서 데이터가 쌓이는 양에 따라 physical guess를 갱신해 주면 된다. 휘발성 IOT 테이블일 경우 secondary 인덱스 크기가 작으면 주기적으로 physical guess를 주기적으로 갱신해 주고 차라리 physical guess가 사용되지 못하도록 pct_direct_access 값을 100 미만으로 유지시키는 것이다. 
  • 인덱스 클러스터 테이블은 클러스터 키 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조로 한 블록에 담을 수 없을 때는 새로운 블록을 할당해 클러스터 체인으로 연결한다. 인덱스 클러스터 테이블은 인덱스를 스캔하면서 값을 찾을 때 Random 액세스가 값 하나당 한 번씩밖에 발생하지 않고 넓은 범위를 검색할 때 유리하다. 정해진 블록을 찾아서 값을 입력해야 하기 때문에 DML 성능이 다소 떨어지지만 크게 차이는 없다. 대신 수정이 자주 발생하는 칼럼은 클러스터 키로 선정하면 안 되며 Truncate Table, Direct Path Loading, 파티셔닝 기능을 사용할 수 없으며 다중 테이블 클러스터를 Full Scan 할 때는 다른 테이블 데이터까지 스켄하기 때문에 불리하다.
  • 해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조다. 조건절 검색 시 = 만 가능하기에 = 조건으로만 검색되는 칼럼을 해시 키로 선정해야 한다. 

7. 인덱스 스캔효율

  • 인덱스 레코드의 특징으로 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속되게 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다. 따라서 인덱스 선행 칼럼이 모두 = 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은 조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다. 
  • Between 조건을 In-List로 바꾸면 In-List 개수만큼 union all 브랜치가 생성되고 각 브랜치마다 모든 칼럼을 = 조건으로 검색하여 비효율이 사라진다. 단 In-List 개수가 많지 않아야 한다. 옵티마이저가 Index Skip Scan을 수행할 때도 있다. 
  • Like 조건보다는 Between을 Between보다는 <=, >을 사용하여 비효율을 줄이자.
  • rowid를 = 조건으로 이용한 액세스에는 비효율이 없지만 필터링하면서 액세스는 비효율이 존재한다.
선분이력 조회

# 기본 패턴
and :dt between 시작일 and 종료일  
# 현제 시점 1
and 종료일 = '99991231'
# 현제 시점 2
and to_char(sysdate, 'yyyymmdd') btween 시작일 and 종료일
------------------------------------------------------
[고객번호+시작일+종료일] 인덱스 구성
/*+ index_desc */
where 고객번호 = :id
and 시작일 <= :dt
and 종료일 >= :dt
rownum <= 1
[고객번호+종료일+시작일] 인덱스 구성
where 고객번호 = :id
and 시작일 <= :dt
and 종료일 >= :dt
rownum <= 1
  • 최근 데이터를 주로 조회한다면 [고객번호+종료일+시작일] 인덱스 구성이 좋고 과거 데이터를 주로 조회한하면 [고객번호+시작일+종료일] 인덱스 구성이 좋다. 
  • Index Skew는 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상을 말한다. delete 대상의 인덱스가 한쪽에 많이 치우쳐져 있을 때 발생하는 현상으로 값이 재 할당되기 전까지 인덱스 스캔 효율이 낮아진다. 커밋을 자주 수행하는 것은 바람직하지 않지만 delete문 직후에 커밋을 수행하고 Insert과정에서 재사용되게끔 하여 비효율을 줄일 수 있다. 
  • Index Sparse는 인덱스 블록 전반에 걸쳐 밀도가 떨어지는 현상이다. Index Skew는 빈블록이 freelist로 반환돼 언제든지 재사용되지만 Index Sparse는 영영 재사용되지 않을 수도 있다. 만약 총 레코드 건수가 일정한데 인덱스 공간 사용량이 커진다면 Index Sparse일 확률이 높다.
  • Index Fragmentation 때문에 스캔 효율이 나빠짐다면 coalesce 명령을 수행해 주면 된다. 여러 인덱스 블록을 하나로 병합하고 빈블록 들은 freelist에 반환하게 된다. 이로써 인덱스 블록 전반에 밀도가 높아져 스캔 효율이 올라가지만 데이터 삽입시 인덱스 분할이 자주 발생하기 때문에 DML 성능을 떨어뜨리기도 한다. pctfree를 높이고 인덱스를 rebuild 할 수도 있지만 그 효과는 일시적이다.

8. 인덱스 설계

  • 기본적으로 조건절에 항상 사용되거나 자주 등장하는 컬럼을 선정하고 = 조건으로 자주 조회되는 칼럼들을 앞쪽에 두어야 한다. 하지만 쿼리 수행 빈도, 업무상 중요도, DML 부하, 저장 공간, 관리 비용 등을 고려해 평가해야 되기 때문에 정답은 없다.
  • 선택도가 높은 컬럼을칼럼을 선두에 두면 나중에 범위검색 조건이 사용되거나 조건절에서 누락되더라도 중복값 개수가 적다면 Index Skip Scan이나 In-List를 활용할 수 있기에 유리하다. 선택도가 낮은 칼럼을 선두에 두면 입력 값의 범위가 좁다면 비효율이 크지 않기 때문에 Index Skip Scan이나 In-List를 사용하지 않더라도 효율적일 수 있다.
  • 결합 인덱스 컬럼 간 순서를 정할 때는 개별 칼럼의 선택도보다는 조건절에서 어떤 형태로 자주 사용되는지, 사용빈도는 어느 쪽이 높은지, 데이터를 빠르게 검색하는 데에 어느 쪽 효용성이 높은지 등을 따져야 한다.

9. 비트맵 인덱스

  • 비트맵 인덱스는 각 고유한 값에 대해 하나의 비트맵이 생성되며 각 비트는 해당 값을 가지는 행을 나타낸다. 중복 값 개수가 적을 때 저장 효율이 뛰어나다. 여러 비트맵 인덱스를 동시에 사용해서 대용량 데이터 검색 성능을 향상하는데 도움을 준다. 단 lock에 의한 DML부하가 심하기 때문에 OLTP성 환경에서는 사용하기 불리하다. 

 

- 참고 서적 : 오라클 성능 고도화 원리와 해법 2 -