I/O효율화의 원리, 인덱스 스캔 방식

Ilhoon·2022년 2월 28일
0
post-thumbnail

DB버퍼캐시에 해당 테이블의 데이터 있는지 확인

  • 있으면 캐시에서 데이터 접근 (메모리 I/O)
  • 없으면 디스크에 접근해서 해당 테이블 데이터를 DB버퍼캐시로 복사 (디스크 I/O)

DB버퍼캐시의 데이터 중 원하는 결과만 추출하여 유저프로세스로 전송 (블록 단위)

SELECT문 실행시 다음과정을 거친다고 했었다. 오라클은 데이터를 블록 단위로 읽어오고, 결국 모든 블록은 DB버퍼캐시를 통해 접근하게된다. (direct path 접근을 제외하고) 이때, 캐시에 없는 데이터들은 디스크에서 읽어오는 과정이 필요하다.

이 디스크 I/O 과정에서 프로세스는 CPU를 OS에 반환하고 I/O가 끝날 때 까지 대기해야하기 때문에 캐시에서 읽는 메모리 I/O에 비해 10000배 정도 느리다. 그러나 DB버퍼캐시는 한정적이기 때문에 읽어올 블록의 양이 많다면 디스크 I/O가 어느정도 발생할 수 밖에 없다.

이러한 과정을 생각해보면 결국 I/O 효율화를 위해 우리가 해야하는 것은 다음 2가지로 요약할 수 있다.

  1. 접근하는 총 메모리 블록 I/O를 줄이는 것.
  2. 그 중에서 디스크 I/O를 줄이는 것

BCHR (Buffer Cache Hit Ratio)

  • 버퍼캐시의 효율을 측정하는 지표

  • (캐시에서 곧바로 찾은 블록수 / 총 읽은 블록수 ) * 100


싱글블록I/O와 멀티블록I/O

  • 싱글블록I/O : 한번에 하나의 블록만 읽어오는방식, 소량의 데이터를 탐색할 때 좋다
  • 멀티블록I/O : 한번에 여러 개의 블록을 읽어오는 방식, 대용량 테이블 전체를 읽을 때 좋다.

인덱스의 특징

인덱스가 빠른 이유는 B-Tree구조로 데이터가 저장되어있기 때문이다. (이진 탐색의 원리)

인덱스는 많은 데이터에서 소량의 데이터를 추출할 때 가장 효율적이다. (추출 건수가 많을수록 느려진다)

인덱스는 정렬된 상태로 저장되어 인덱스로 찾은 데이터는 자동으로 정렬된 상태이다.

  • 양방향 연결리스트로 리프블록이 구성되어있어 양방향 정렬이 모두 가능하다.

인덱스 칼럼에 저장된 값은 ROWID

  • ROWID : 디스크 상에서 테이블 레코드를 찾아가기 위한 논리적인 주소 정보 (데이터 파일 번호 + 데이터 파일내 블록번호 + 데이터 파일 블록내 로우 번호)

인덱스 개수가 늘어날수록 DML 부하에 따른 트랜잭션 성능이 저하되고 관리 비용이 증가해서 인덱스를 추가할 때는 주의해야한다.


인덱스 수직적 탐색

  • 루트블록 -> 브랜치블록 -> 리프블록을 찾는 과정
  • 어떤 값으로 탐색하더라도 루트블록 -> 리프블록까지 읽는 블록의 수는 항상 같다(높이가 같다)

인덱스 수평적 탐색

  • 리프블록을 순차적으로 차례대로 탐색

인덱스 스캔 방식

INDEX RANGE SCAN

인덱스를 수직 탐색하여 조건의 시작 지점을 찾고, 조건의 종료지점까지 순차적으로 탐색하여 테이블에 접근한다.

기본적인 인덱스 스캔 방식


INDEX RANGE SCAN사용하지 못하는 경우

인덱스의 시작과 끝 지점을 정확히 찾을 수 없을 때 INDEX RANGE SCAN을 사용할 수 없다.

  • 인덱스의 선두 칼럼을 가공한 경우

    • 자동 형변환을 통해 의도치 않게 선두 칼럼이 가공되지 않도록 주의해야 한다.
  • 인덱스의 선두 칼럼이 조건절에 없는 경우

  • 인덱스의 선두 칼럼 조건이 OR 혹은 IN으로 되어있는 경우

    • /*+ use_concat */을 사용해 OR-Expansion을 유도하거나 직접 UNION ALL 사용하여 실행계획 분리

    • 옵티마이저가 INLIST ITERATOR방식으로 자동 최적화 혹은 직접 UNION ALL 사용하여 실행계획 분리


INDEX FULL SCAN

인덱스 선두칼럼이 조건절에 없는 경우 전체 인덱스를 모두 탐색하여 테이블에 접근한다.

테이블에 액세스하는 데이터의 양이 아주 적다면 TABLE FULL SCAN보다 좋다.

SORT 연산을 생략하고 인덱스에서 처음 일부 데이터만 찾고 싶은 경우 효율적일 수 있다. (first_rows힌트 사용)


INDEX UNIQUE SCAN

인덱스 수직탐색만으로 모든 탐색이 종료되는 경우

유일한 1건의 데이터만 찾는다.


INDEX SKIP SCAN

오라클 9i 버전부터 등장

인덱스 선두 컬럼이 조건절이 없어야한다. (있어도 범위 조건)

인덱스 선두 컬럼의 Distinct value가 적고 후행칼럼의 Distinct value가 많을 때 효과적이다.


INDEX FAST FULL SCAN

인덱스 스캔 방식 중 유일하게 멀티블록 I/O방식으로 스캔한다.

정렬 순서가 보장되지 않는다.

  • 인덱스 리프노드의 연결리스트 구조를 무시하고 물리적으로 저장된 순서에 따라 읽기 때문에

쿼리에 포함된 칼럼이 모두 인덱스에 있을 때만 사용할 수 있다.

profile
꾸준하게!

0개의 댓글