Skip to content

README.md

Latest commit

 

History

History
237 lines (163 loc) · 10.5 KB

README.md

File metadata and controls

237 lines (163 loc) · 10.5 KB

2장: 인덱스 기본

2.1 인덱스 구조 및 탐색

  • 인덱스 탐색 과정 이해
    • 수직적 탐색
    • 수평적 탐색

2.1.1 미리 보는 인덱스 튜닝

핵심 원리를 간략히 살펴보자

데이터를 찾는 두 가지 방법

초등학교에서 '홍길동'이라는 이름을 가진 학생을 찾는다고 가정하자.

  1. 1학년 1반부터 6학년 마지막 반까지 모든 교실을 돌아다니며 홍길동 학생을 찾기
    • 테이블 전체를 스캔
  2. 교무실에서 학생 명부를 조회해 홍길동 학생이 있는 교실로 바로 찾아가기
    • 인덱스를 이용

홍길동 학생이 많다면 1번 방법이 빠르고 몇 없다면 2번 방법이 빠르다.

이렇게 이름으로 학생을 찾는 경우가 많다면 학생명부를 다음과 같이 정렬해두면 편하다.

이름 학년-반-번호
강수지 4학년 3반 37번
김철수 3학년 2반 13번
... ...
이영희 6학년 4반 19번
... ...
홍길동 1학년 5반 15번
홍길동 2학년 6반 24번
홍길동 5학년 1반 16번
... ...

이 방법이 바로 인덱스이고 '학년-반-번호' 컬럼이 인덱스 ROWID에 해당한다.

인덱스 튜닝의 두 가지 핵심요소

인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용

인덱스 튜닝의 핵심요소 두 가지

  • 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것 (인덱스 스캔 효율화 튜닝)
  • 테이블 액세스 횟수를 줄이는 것 (랜덤 액세스 최소화 튜닝)
이름 시력 학년-반-번호
강수지 1.5 4학년 3반 37번
김철수 0.5 3학년 2반 13번
... ... ...
이영희 1.5 6학년 4반 19번
... ... ...
홍길동 1.0 1학년 5반 15번
홍길동 1.5 2학년 6반 24번
홍길동 2.0 5학년 1반 16번
... ...

위 표에서 시력이 1.0 ~ 1.5인 홍길동 학생을 찾는 경우를 예로 들어본다.

학생 명부를 위 처럼 이름과 시력순으로 정렬해 두었다면 강조되어있는 소량만 체크하면 된다.

시력 이름 학년-반-번호
0.5 김철수 4학년 3반 37번
... ... ...
1.0 홍길동 3학년 2반 13번
1.5 강수지 6학년 4반 19번
1.5 이영희 2학년 5반 15번
1.5 홍길동 1학년 5반 15번
1.5 ... ...
2.0 홍길동 5학년 1반 16번
... ... ...

반면, 시력과 이름순으로 정렬해 두었다면 더 많은 스캔이 발생하게 된다.

SQL 튜닝은 랜덤 I/O와의 전쟁

디스크 I/O 때문에 성능이 느려진다. 디스크 I/O 중에서도 랜덤 I/O가 특히 중요하다.

2.1.2 인덱스 구조

인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트

만약 인덱스를 사용하지 않다면 테이블을 처음부터 끝까지 모두 읽어야 한다. 반면, 인덱스를 이용하면 일부만 읽고 멈출 수 있기 때문에 범위 스캔(Range Scan)이 가능하다.

범위 스캔이 가능한 이유는 인덱스가 정렬돼 있기 때문

DBMS는 일반적으로 B-Tree 인덱스를 사용

리프 블록에 저장된 각 레코드는 키값 순으로 정렬되어있고 레코드를 가리키는 주소값(ROWID)을 갖는다.

  • 인덱스 키값이 같으면 ROWID 순으로 정렬된다.

  • ROWID = 데이터 블록 주소(DBA) + 로우 번호

  • DBA = 데이터 파일 번호 + 블록 번호

  • 블록 번호: 데이터 파일 내에서 부여한 상대적 순번

  • 로우 번호: 블록 내 순번

  • 인덱스 탐색 과정

    • 수직적 탐색: 인덱스 스캔 시작지점을 찾는 과정
    • 수평적 탐색: 데이터를 찾는 과정

2.1.3 인덱스 수직적 탐색

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

  • 조건을 만족하는 레코드를 찾는 과정이 아닌 조건을 만족하는 첫 번째 레코드를 찾는 과정이다.
  • 첫 번째 푯말을 찾는 과정이라고 볼 수 있다.

수직적 탐색은 루트 블록에서부터 시작한다. 루트를 포함해 브랜치 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 갖는다. 이러한 이유로 Root에서 시작해 Leaf 블록까지 수직적 탐색이 가능하다.

2.1.4 인덱스 수평적 탐색

수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 안나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다.

인덱스에서 본격적으로 데이터를 찾는 과정이다.

  • 인덱스 리프 블록끼리는 서로 앞뒤 브록에 대한 주소값을 갖는다.
    • 양방향 연결 리스트(dobule linked list) 구조

인덱스를 수평적으로 탐색하는 이유 2가지

  1. 조건절을 만족하는 데이터를 모두 찾기 위해서
  2. 테이블에 액세스하기 위한 ROWID를 얻기 위해서

2.1.5 결합 인덱스 구조와 탐색

두 개 이상 컬럼을 결합해서 인덱스를 만들 수 있다.

Balanced-Tree는 어떤 값으로 탐색하더라도 인덱스 루트에서 리프 블록에 도달하기까지 읽는 블록 수가 같다. 즉, 루트로부터 모든 리프 블록까지의 높이는 항상 같다.

2.2 인덱스 기본 사용법

  • 인덱스 기본 사용법은 Range Scan 하는 방법을 의미한다.

2.2.1 인덱스를 사용한다는 것

  • 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미
    • 리프 블록 일부만 스캔하는 Index Range Scan
  • 반대로 일부가 아닌 전체를 스캔하는 Index Full Scan 방식이 있음

2.2.2 인덱스를 Range Scan 할 수 없는 이유

"인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용(Range Scan)할 수 없다."

인덱스에는 가공되지 않은 값이 저장되어 있는데, 가공된 값을 기준으로 검색하려면 어디서 스캔을 시작해야 할까?

  • 예시1) where nvl(주문수량, 0) < 100 값이 NULL이면 0으로 치환한 값 기준으로 100보다 작은 레코드 찾기
  • 예시2) where 업체명 like '%대한%' 처럼 '대한'을 포함하는 값을 찾기
  • 예시3) where (전화번호 = :tel_no or 고객명 = :cust_nm)
  • 예시4) where 전화번호 in (:tel_no1, :tel_no2)

위 예시 모두 옵티마이저의 쿼리변환 기능을

OR 조건은 옵티마이저의 OR Expansion 사용

OR Expansion: 사용자가 직접 쿼리를 바꿔주지 않아도 옵티마이저가 작업을 대신해 주는 경우

IN 조건은 옵티마이저의 IN-List Iterator 방식 사용

List 개수만큼 Index Range Scan을 반복해 UNION ALL로 변환

2.2.3 더 중요한 인덱스 사용 조건

2.2.4 인덱스를 이용한 소트 연산 생략

Range Scan을 할 수 있는 이유는 데이터가 정렬돼 있기 때문이다. 인덱스가 구성되어 있다면 SQL에 ORDER BY가 있어도 실행 계획에는 SORT ORDER BY 연산이 없다.

  • 오름차순 정렬일 때
    • 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색한 후 우측으로 수평적 탐색
  • 내림차순 정렬일 때
    • 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 수평적 탐색

2.2.5 ORDER BY 절에서 컬럼 가공

2.2.6 SELECT-LIST 컬럼 가공

2.2.7 자동 형변환

테이블의 컬럼이 문자형인데 조건절 비교값을 숫자형으로 표현한 경우 인덱스 컬럼이 가공됐기 때문에 인덱스를 Range Scan 할 수 없다.

이러한 경우 실행계획에서는 테이블 전체 스캔이 선택된다.

  • 타입 체크를 엄격히해 에러를 내는 DBMS
  • 자동으로 형변환 처리해주는 DBMS
    • 오라클

자동 형변환 주의

형변환 함수(TO_CHAR, TO_DATE, TO_NUMBER)를 생략하면 연산횟수가 줄어 성능이 더 좋지 않을까라고 생각되기 때문에 의도적으로 생략하곤 한다.

하지만 SQL 성능은 블록 I/O를 줄일수 있느냐 없느냐에서 결정된다.

2.3 인덱스 확장기능 사용법

  • Index Full Scan
  • Index Unique Scan
  • Index Skip Scan
  • Index Fast Full Scan

2.3.1 Index Range Scan

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

인덱스를 Range Scan 하기 위해서는 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야만 한다.

2.3.2 Index Full Scan

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

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

Index Full Scan의 효용성

인덱스 선두 컬럼이 조건절에 없으면 옵티마이저는 먼저 Table Full Scan을 고려한다.

그런데 대용량 테이블이어서 Full Scan이 부담된다면 인덱스 활용을 고려한다.

인덱스를 Range Scan 할 수 없는 경우 + 대부분 레코드가 필터링되고 아주 일부만 테이블을 액세스하는 경우

옵티마이저는 Index Full Scan 방식을 선택한다.

인덱스를 이용한 소트 연산 생략

2.3.3 Index Unique Scan

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

  • Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동
  • Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않도록 함

2.3.4 Index Skip Scan

인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택.

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

Index Skip Scan이 작동하기 위한 조건

Distinct Value 개수가 적은 선두 컬럼이 조건절에 없고 후행 컬럼의 Distinct Value 개수가 많을 때 효과적이다.

2.3.5 Index Fast Full Scan

Index Full Scan보다 빠른 방식이다.

논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 MultiBlock I/O 방식으로 스캔하기 때문이다.

  • 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과를 발휘
  • 속도는 빠르지만 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않음
  • 병렬쿼리 시 Direct Path I/O 방식 사용

2.3.6 Index Range Scan Descending

Index Range Scan과 기본적으로 동일한 방식이지만 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내맃마순으로 정렬된 결과집합을 얻는다.