2장 인덱스 기본

2.1 인덱스 구조 및 탐색

인덱스 튜닝의 핵심요소

• 인덱스 스캔 효휼화 튜닝 : 정렬을 통한 소량 스캔
• 랜덤 액세스 최소화 튜닝 : 인덱스 스캔 후 테이블 레코드 액세스 최소화

2.1.1 인덱스 구조

B+ 트리

LMC : 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킴

리프 블록에 저장된 레코드는 ROWID(데이터 블록 주소 + 로우 번호)를 가짐.

2.1.2 인덱스 수직적 탐색

인덱스 스캔 시작지점을 찾는 과정

찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동

2.1.3 인덱스 수평적 탐색

조건절에 만족하는 모든 데이터의 ROWID를 얻기 위한 과정

2.1.4 결합 인덱스

인덱스 구성에 따라 성능 차이는 있지만, 어느 컬럼을 앞에 두든 일량의 차이는 없다.

2.2 인덱스 기본 사용법

인덱스 선두 컬럼이 조건절에 있어야 범위 스캔 가능

단, 인덱스 선두 컬럼을 가공하지 않아야 정상적으로 사용 가능 (인덱스 스캔 시작점 탐색)

2.2.1 컬럼 가공

WHERE, ORDER BY, SELECT-LIST에서 가공이 이루어진 경우 인덱스가 정상적으로 사용되지 않을 수 있음.

• SUBSTR()
• NVL()
• LIKE '%...%'
• WHERE ( ... OR ... )
• WHERE IN ( ..., ... )
• TO_CHAR( ..., 'FM...' )

* IN 조건절에 대해서는 SQL 옵티마이저가 IN-List Iterator 방식 사용 : IN_List 개수만큼 Index Range Scan 반복

2.2.2 정렬 연산 생략

인덱스 사용 시 정렬돼 있기 때문에 ORDER BY가 있어도 정렬 연산 수행 생략

2.2.3 자동 형변환

자동 형변환에 의해 인덱스 컬럼이 가공되어 인덱스 스캔을 못하는 경우 존재

숫자형 + 문자형 = 숫자형

날짜형 + 문자형 = 날짜형

에러 )

• 실행 에러 : 숫자형 컬럼과 문자형 컬럼 비교 시 문자형 컬럼에 숫자로 변환할 수 없는 문자열을 입력하는 경우
• 결과 오류 : DECODE(A, B, C, D) 처리 중 데이터 타입이 C에 의해 결정

2.3 인덱스 확장기능 사용법

2.3.1 Index Range Scan

인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 필요한 범위만 스캔

2.3.2 Index Full Scan

수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색

데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택

2.3.3 Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식

Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성 관리

Unique 결합 인덱스에 대해 일부 컬럼만으로 검색 시 Index Range Scan 실행

2.3.4 Index Skip Scan

조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스하는 스캔 방식

조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용

2.3.5 Index Fast Full Scan

논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔

결과집합이 인덱스 키 순서대로 정렬되지 않음.

쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용 가능

인덱스가 파티션 돼 있지 않더라도 병렬 쿼리 가능

2.3.6 Index Range Scan Descending

내림차순으로 정렬된 결과집합

1장 SQL 처리 과정과 I/O

1.1 SQL 파싱과 최적화

1.1.1 SQL

구조적, 집합적, 선언적 질의 언어

1.1.2 SQL 옵티마이저

프로시저를 만들어 내는 DBMS 내부 엔진

1. 후보군 실행계획 탐색
2. 실행계획 예상비용 산정
3. 최저 비용 실행계획 선택

1.1.3 SQL 최적화

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 과정

1. SQL 파싱

   1. 파싱 트리 생성
   2. Syntax 체크
   3. Semantic 체크

2. SQL 최적화 : 옵티마이저가 효율적인 실행경로를 선택

3. 로우 소스 생성 : 옵티마이저가 선택한 실행경로를 실행 가능한 코드로 포맷팅 하는 단계

1.1.4 실행계획

SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리 구조로 표현한 것

1.1.5 비용

쿼리를 수행하는 동안 발생할 것으로 예상 I/O 횟수 또는 예상 소요시간

실측치가 아니므로 실제 수행할 때 발생하는 I/O 또는 시간과 많은 차이

1.1.6 옵티마이저 힌트

SQL데이터 액세스 경로를 정해주어 복잡한 SQL에서의 옵티마이저의 한계 개선

주의사항 )

• 힌트와 힌트 사이에 콤마 사용 불가

  /*+ INDEX(A A_X01) INDEX(B, B_X03) */ --모두 유효
  /*+ INDEX(C), FULL(D) */ --첫 번째 힌트만 유효
  

• FROM절 테이블명 별칭 사용 시 힌트에도 별칭 사용

  SELECT \*+ FULL(EMP) *\ --무효
    FROM EMP E
  

1.2 SQL 공유 및 재사용

1.2.1 소프트 파싱 vs 하드 파싱

SGA(System Global Area) : 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간

• DB Buffer Cache

• Redo Log Buffer

• Shared Pool

  • Library Cache : 내부 프로시저를 반복 사용할 수 있도록 캐싱해 두는 메모리 공간
  • Data Dictionary Cache

실행과정 )

1. 사용자

2. SQL 파싱

3. 캐시 힛

   • O (소프트 파싱) : 실행
   • X (하드 파싱) : 최적화 > 로우 소스 생성 > 실행

옵티마이저가 사용하는 정보 목록

• 오브젝트(테이블, 컬럼, 인덱스) 구조 기본 정보
• 오브젝트 통계
• 시스템 통계
• 옵티마이저 관련 파라미터

1.2.2 바인드 변수

SQL은 자체가 이름이기 때문에 텍스트 중 작은 부분이라도 수정되면 다른 객체 생성 (Case Sensitive)

SQL은 딕셔너리에 저장하지 않는다.

파라미터 Driven 방식으로 SQL을 작성하여 하나의 프로시저를 공유하면서 재사용 (하드파싱 1회)

1.3 데이터 저장 구조 및 I/O 메커니즘

디스크 I/O가 SQL 성능을 좌우

1.3.1 데이터베이스 저장 구조

테이블스페이스 > 세그먼트 > 익스텐트 > 데이터 블록 > 로우

• 데이터 블록 : 데이터를 읽고 쓰는 단위
• 익스텐트 : 공간 확장 단위, 연속된 블록 집합
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트
• 테이블스페이스 : 세그먼트를 담는 컨테이너
• 데이터파일 : 디스크 상의 물리적인 OS 파일

세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하는 것은 아니다.

익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아니다.

DBA(Data Block Address) : 데이터 블록의 고유 주소값

1.3.2 시퀀셜 액세스 vs 랜덤 액세스

• 시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
• 랜덤 액세스 : 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식

익스텐트 맵 : 세그먼트에 할당된 익스텐트 목록을 관리하기 위해 세그먼트 헤더에 있는 맵

1.3.3 논리적 I/O vs 물리적 I/O

자주 읽는 블록에 대한 디스크 접근 시간을 줄이기 데이터 캐싱 사용

데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색

• 논리적 I/O : 메모리 I/O와 유사
• 물리적 I/O : 디스크 I/O와 유사

논리적 I/O > 물리적 I/O

버퍼캐시 히트율(BCHR) = ( 1 - 물리적I/O / 논리적I/O) * 100

SQL 성능을 향상하려면 논리적 I/O를 줄여야 한다.

BCHR이 높다고 효율적인  SQL은 아니다.

1.3.4 Table Full Scan vs Index Range Scan

• Single Block I/O : 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
• Multiblock I/O : 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식

Multiblock I/O 방식으로 읽더라도 익슨텐트 경계를 넘지 못한다.

• Table Full Scan : 테이블 전체를 스캔해서 읽는 방식

  • 시퀀셜 액세스 + Multiblock I/O

• Index Range Scan : 인덱스를 이용해서 ROWID로 테이블 레코드를 찾아가는 방식

  • 랜덤 액세스 + Single Block I/O
  • 큰 테이블에서 소량의 데이터를 검색할 때 필수
  • 많은 데이터를 읽을 때는 Table Full Scan보다 불리

인덱스가 SQL 성능을 떨어뜨리는 경우도 상당히 많다.

1.3.5 캐시 탐색 매커니즘

1. 해시 알고리즘으로 버퍼 헤더 찾기
2. 포인터로 버퍼 블록 액세스

해시 체인 내에서의 정렬 보장 X

액세스 직렬화 매커니즘의 필요성 : 하나의 버퍼 블록을 두 개 이상의 프로세스가 동시에 접근하려고 할 때 블록 정합성 문제 위험

• 캐시버퍼 캐시 래치 : 해시 체인 앞에 래치(걸쇠)를 두고, Key를 획득한 프로세스만이 체인에 진입
• 버퍼 Lock : 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더에 Lock을 설정함으로써 버퍼블록 자체에 대한 직렬화 문제 해결