2024-07-25

2. 인덱스 기본

인덱스 구조 및 탐색

미리 보는 인덱스 튜닝

DBMS가 발전해 왔지만 여전히 데이터를 찾는 방법은 두 가지이다.
- 테이블 전체를 스캔한다.
- 인덱스를 사용한다.

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

인덱스는 큰 테이블에서 소량의 데이터를 검색할 때 사용, 즉 온라인 트랜잭션 처리 에서 중요
인덱스 튜닝 핵심요소
- 인덱스 스캔 효율화 튜닝
  - 시력이 1.0 ~ 1.5인 홍길동을 찾는 데에는 이름 순, 시력 순으로 정렬이 시력 순, 이름 순보다 더 좋다.
- 랜덤 엑세스 최소화 튜닝
  - 이름만으로 정렬한 학생부 vs 시력 만으로 정렬된 학생부
- 랜덤 엑세스 최소화 튜닝이 더 성능에 영향이 크다.
- SQL 튜닝은 랜덤 I/O와의 전쟁이다.

인덱스 구조

인덱스를 사용하면 범위 스캔이 가능하다.
DBMS는 일반적으로 B Tree 인덱스를 사용
루트, 브랜치, 리프가 존재
- 루트와 브랜치 블록의 각 레코드는 하위 블록에 대한 주소값을 가진다.
- LMC는 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가르킨다.
- 리프 블록은 키값 순으로 정렬 + 테이블 레코드를 가르키는 주소 값인 ROWID를 갖는다.
- ROWID 순으로 정렬된다
  - ROWID = 데이터 블록 주소 + 로우 번호
  - 데이터 블록주소 = 데이터 파일 번호 + 블록 번호
  - 블록 번호 = 데이터 파일 내에서 부여한 상대적 순번
  - 로우 번호: 블록 내 순번

인덱스 수직적 탐색

인덱스 스캔 시작지점을 찾는 과정
조건을 만족하는 첫 번재 레코드를 찾는 과정
- 탐색 과정에서 크거나 같은 값을 찾으면, 바로 직전 레코드가 가르키는 하위 블록으로 이동
즉, 루트와 브랜치 블록은 등산의 이정표 역할을 한다.

인덱스 수평적 탐색

수직적 탐색으로 스캔 시작점을 찾고, 이제 리프 블록에서 수평적으로 스캔
리프는 양방향 연결 리스트 구조
조건절을 모두 찾기위해, 그리고 ROWID를 얻기 위해 수평적 탐색을 함

결합 인덱스 구조와 탐색

두 개 이상의 컬럼을 결합해서 인덱스를 만들 수도 있음
delete를 해도 인덱스는 불균형 되지 않는다. balanced 이기 때문
루트부터 모든 리프 블록까지의 높이는 항상 같다.

인덱스 기본 사용법

인덱스를 사용한다는 것

인덱스를 정상적으로 사용한다 = 리프 블록 일부만 스캔하는 Index Range Scan을 의미
인덱스 컬럼을 가공하지 않아야 한다

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

인덱스 컬럼을 가공했을 때 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문 이다.
Range를 사용하려면 시작점과 끝지점이 있어야 한다.

where nvl('주문수량', 0) < 100

where ('전화번호' = :tel_no OR '고객명' = :cust_num)

어느 한 시작지점을 바로 찾을 수 없다.
이 두 경우 인덱스 스캔 시작점을 알 수 없다.

where '전화번호' in (:tel_no1, :tel_no2)

IN 조건은 OR 조건을 표현하는 다른 방식이다.
그래서 옵티마이저는 IN의 LIST 개수 만큼 Index Range Scan을 반복한다. 이를 통해 밑과 같은 효과를 낸다

select *
from '고객'
where '전화번호' = :tel_no
union all
select *
from '고객'
where '전화번호' = :tel_no2

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

인덱스를 [소속팀 + 사원명 + 연령] 순으로 구성한다는 것은 데이터를 소속팀이 같으면 사원명으로, 사원명이 같으면 연령순으로 정렬한다는 뜻
즉 where절에 사원명 = 홍길동 이 오게되면 리프 블록 전역에 흩어진 홍길동을 찾아야한다.
Range Scan을 하기위한 첫번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다 이다.

인덱스 잘 타니깐 튜닝 끝?

실행계획을 잘 살펴보고, range scan을 하는지 확인만해서는 안된다.
인덱스 리프 블록에서 스캔하는 양을 따져봐야한다.

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

pk 인덱스를 스캔하면서 출력한 결과집함은 어차피 정렬되어 있기 때문에 order by를 해도 실행계획은 똑같다.
DESC도 마찬가지, 리프 블록은 양방향 연결 리스트 구조이기 때문에 가능하다

ORDER BY 절에서 컬럼 가공

조건절이 아닌 ORDER BY 또는 SELECT-LIST 에서 컬럼을 가공해서 인덱스를 정상적으로 사용할 수 없는 경우도 존재한다.

SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
ORDER BY 변경일자 || 변경 순번

장비번호, 변경일자, 변경순번 순으로 구성했어도 가공한 값을 기준으로 정렬해달라 했기 때문에 정렬 연산 생략 불가능

SELECT *
FROM (
    SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호
    FROM 주문 A
    WHERE A.주문일자 = :dt
    AND 주문번호 > NVL(:next_ord_no, 0)
    ORDER BY 주문번호
)
WHERE ROWNUM <= 30

주문일자, 주문번호로 구성했어도 SELECT절에 가공된 주문번호로 정렬을 요청했기 때문에 안됨
그래서 ORDER BY A.주문번호로 바꿔주면 SORT ORDER BY를 생략 가능

SELECT-LIST에서 컬럼 가공

장비번호, 변경일자, 변경순번 순으로 되어있을 때

SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)

인덱스는 문자열 기준으로 되어있는데 숫자로 SELECT이 되기 때문에 정렬 연산 생략 불가능

SELECT NVL(TO_NUMBER(MAX(변경순번)), 0)

이렇게 바꿔준다.

자동 형변환

생년월일 컬럼이 문자형인데 조건절 비교값을 숫자형으로 하면 IN_NUMBER로 자동 형변형이 되어 Range Scan이 불가능
특히 LIKE 절은 TO_CHAR로 자동 형변형을 해주기 때문에 주의
자동 형변환으로 성능과 품질에 종종 문제가 생긴다.
- SQL의 성능은 블록IO를 줄이는 것이기 때문에 형 변환을 생략하지 않아도되고, 오히려 명시하는 것이 더 좋다.
- 연산횟수를 줄인다고 생갹하여도 옵티마이저가 자동으로 생성한다.

인덱스 확장기능 사용법

Index Range Scan

인덱스 루트부터 리프 블록까지 수직적 탐색 -> 이후 필요한 범위만 스캔

Index Full Scan

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

select * from emp
where sal > 2000
order by ename

이 때, 인덱스는 ename, sal이라면 ename이 조건절에 없기 때문에 Range Scan은 불가능하지만 뒤쪽의 Sal칼럼이 Index Full Scan을 가능하게 해준다

Index Full Scan의 효용성

Range scan의 차선책이고, Table Full Scan 보다는 일부의 데이터를 탐색하기 때문에 성능이 좋을 가능성이 크다

Index Unique Scan

수직적 탐색만으로 데이터를 찾는 방식이며 Unique 인덱스를 = 조건으로 탐색하는 경우이다.
Unique 인덱스가 존재하는 컬럼은 중복 값을 DBMS가 데이터 정합성 관리
하지만 범위 검색이라면 Unique 인덱스도 Range Scan
또한 결합 인덱스일 때 일부 컬럼만으로 검색할 때도 범위 검색을 한다

Index Skip Scan

오라클에서 사용, 인덱스 선두 컬럼이 조건절에 없어도 발동
인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용
- 하지만 선두 컬럼에 대한 조건절은 있고, 중간 컬럼이 조건절에 없어도 사용 가능
- Distinct Value가 적은 두 개의 선두 컬럼이 모두 조건절에 없어도 사용 가능
루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함하는 가능성 있는 리프 블록만 골라 엑세스
스캔하다가 가능성이 없으면 skip한다.

Index Fast Full Scan

Index Full Scan보다 빠르다. 트리구조를 무시하고 인덱스 세그먼트를 Multiblock I/O 방식으로 스캔하기 때문
논리적 순서의 블록이 아닌 물리적 순서의 블록을 읽는다.
대량의 블록을 읽을 때 큰 효과를 발휘하나 인덱스 키 순서대로 정렬되지는 않는다.

alt text

Index Range Scan Descending

Index Range Scan과 동일하나 내림차순 정렬된 결과집합을 얻는다
기존 왼쪽 -> 오른쪽 스캔이 아닌 오른쪽 -> 왼쪽 스캔을 진행
max 값을 구하고자 할 때도 인덱스를 뒤에서 한 건만 읽고 멈추는 실행계획 자동 수립