인덱스 구조 및 탐색

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

미리 보는 인덱스 튜닝

• OLTP 에서는 소량의 데이터를 검색하므로 인덱스 튜닝이 중요

데이터를 찾는 두 가지 방법

• 테이블 전체를 스캔
• 인덱스를 이용

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

• 인덱스 스캔 효율화 튜닝: 인덱스 스캔 과정에서 발생하는 비효율 줄이기
• 랜덤 액세스 최소화 튜닝: 테이블 액세스 횟수를 줄이기. 성능 영향이 더 커서 중요

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

• 인덱스를 많이 사용하는 OLTP 시스템은 디스크 I/O 중에서도 랜덤 I/O 가 특히 중요
• 성능을 위한 DBMS 의 기능의 대부분이 랜덤 I/O 를 극복하기 위함

인덱스 구조

• Range Scan: 일부만 읽고 멈춤
• 인덱스는 정렬되어 있기에 Range Scan 이 가능하다
• B*Tree
  • 루트 블록 - 브랜치 블록 - 리프 블록
  • 루트 블록, 브랜치 블록: 엔트리 키, 자식 블록 주소
  • 리프 블록: 키 값, ROWID
  • ROWID = 데이터 블록 주소 + 로우 번호
  • 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
  • 블록 번호 = 데이터 파일 내 순번
  • 로우 번호 = 블록 내 순번
• 인덱스 탐색 과정
  • 수직적 탐색: 인덱스 스캔 시작점을 찾는 과정
  • 수평적 탐색: 데이터를 찾는 과정

인덱스 수직적 탐색

• 정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정

인덱스 수평적 탐색

• 찾고자 하는 데이터가 더 안 나타날 때까지 리프 블록을 수평적으로 스캔
• 리프 블록은 양방향 연결 리스트
• 필요한 컬럼을 인덱스가 모두 가지고 있으면 ROWID 필요 없음
• 그 외에는 테이블 액세스를 위해 ROWID 가 필요

결합 인텍스 구조와 탐색

• 두 개 이상의 컬럼을 결합해 만든 인덱스
• ⚠️ 결합 인덱스는 단순히 조건을 순차적으로 필터링하는 구조가 아니다.
  '=' 조건이 있을 경우, 인덱스는 루트에서 리프까지 직접 수직 탐색하여 곧바로 원하는 위치로 이동
  이후 리프 노드에서 수평 탐색(범위 탐색)을 함
  즉, 앞선 조건 결과를 기반으로 다시 탐색하는 방식이 아니라, 조건이 모두 만족되는 리프 노드를 곧장 찾는 구조
  따라서 '=' 조건이 여러 개여도 순서에 따른 탐색 비용 차이는 거의 없다
  반면, range 조건(BETWEEN, <, >)이 포함되면 수직 탐색이 끊기므로, 인덱스 컬럼 순서가 성능에 큰 영향을 줌

인덱스 기본 사용법

• Index Range Scan 하는 방법

인덱스를 사용한다는 것

• Index Range Scan: 리프 블록까지 곧바로 찾아가서, 시작점부터 일부만 스캔하는 것
• 수직 탐색을 통해 조건에 맞는 스캔의 시작지점을 곧바로 찾아감
• (리프 블록 어딘가의)스캔 시작지점부터 조건에 맞지 않는 값이 나올 때 까지 수평 탐색

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

• 인덱스 컬럼을 가공했다면 인덱스 스캔 시작점을 찾을 수 없어 Range Scan 을 할 수 없다.
• 인덱스를 못쓰는 경우: 변형, 부정, NULL, OR
• Index Full Scan: 모든 리프 블록을 스캔하는 것

OR Expansion

• OR 조건 쿼리에서 인덱스를 활용할 수 있도록 옵티마이저가 최적화

CREATE INDEX S_CUSTOMER_ADDRESS_IDX ON S_CUSTOMER(ADDRESS);
CREATE INDEX S_CUSTOMER_SALES_REP_ID_IDX ON S_CUSTOMER(SALES_REP_ID);

-- 힌트로 OR Expansion 을 사용하도록 강제했지만, 옵티마이저가 자동 최적화하는 경우가 있다
SELECT /*+ USE_CONCAT
           INDEX(S_CUSTOMER S_CUSTOMER_ADDRESS_IDX)
           INDEX(S_CUSTOMER S_CUSTOMER_SALES_REP_ID_IDX) */ * FROM S_CUSTOMER
WHERE (ADDRESS = '서울' OR SALES_REP_ID = 14);
/* 실행 계획
SELECT STATEMENT
- CONCATENATION
- - TABLE ACCESS(S_CUSTOMER)
- - - INDEX(S_CUSTOMER_SALES_REP_ID_IDX)
- - - - Filter Predicates
- - - - - SALES_REP_ID=14
- - TABLE ACCESS(S_CUSTOMER)
- - - Filter Predicates
- - - - LNNVL(SALES_REP_ID=14)
- - - INDEX(S_CUSTOMER_ADDRESS_IDX)
- - - Filter Predicates
- - - - ADDRESS='서울'
*/

IN-List Iterator

• IN 조건은 OR 조건을 표현하는 다른 방식, 인덱스를 사용하지 못한다
• OR Expansion 처럼 UNION ALL 을 통해 IN-List 개수만큼 Index Range Scan
• OR expansion, IN-List Iterator 모두 옵티마이저의 쿼리변환 기능을 통한 최적화

더 중요한 인덱스 사용 조건

• 인덱스 선두 컬럼이 조건절에 있어야 함
• 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan 언제나 가능

인덱스 잘 타니까 튜닝 끝

• 인덱스를 잘 타는지를 확인하려면 리프 블록에서의 스캔 양을 봐야 함
• ⚠️ 중간값 검색, 가공된 컬럼 등은 스캔 범위를 줄이는데 영향을 주지 않음

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

• 인덱스는 정렬되어 있기 때문에, 소트 연산 생략이 가능함
• ORDER BY 절이 없어도 결과가 같게끔 인덱스가 구성되어 있으면 소트 연산 안함
• 리프 블록은 양방향 연결 리스트이기에 ASC, DESC 둘다 가능

ORDER BY 절에서 컬럼 가공

• 조건절이 아닌 ORDER BY 나 SELECT-LIST 에서 컬럼을 가공해도 인덱스를 못쓸 수 있다
• ORDER BY 절에서 문자열 CONCAT 같이 논리적으로는 같은 정렬 조건이라도 가공되었기에 안됨
• SELECT-LIST 에서 가공하고 ORDER BY 절에서 1 처럼 지정하면 당연히 다른 정렬 조건이라 안됨

CREATE INDEX PK_IDX1 ON 상태변경이력(장비번호, 변경일자, 변경순번);
SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
ORDER BY 변경일자 || 변경순번; -- 가공된 정렬 조건

CREATE INDEX PK+IDX2 ON 주문(주문일자, 주문번호);
SELECT *
FROM (
    SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
    FROM 주문 A
    WHERE A.주문일자 = :dt
    AND A.주문번호 ? NVL(:next_ord_no, 0)
    ORDER BY 1 -- 가공된 정렬 조건
)
WHERE ROWNUM <= 30;

SELECT-LIST 에서 컬럼 가공

• 인덱스를 이용해 정렬 없이 최소값/최대값을 빠르게 찾을 수 있음

CREATE INDEX EMP_IDX_DEPTNO_HIREDATE ON EMP(DEPTNO, HIREDATE);
SELECT MIN(HIREDATE)
FROM EMP
WHERE DEPTNO = 30;
/* 실행계획
SELECT STATEMENT
- SORT AGGREGATE
- - FIRST ROW
- - - INDEX(EMP_IDX_DEPTNO_HIREDATE) RANGE SCAN (MIN/MAX)
- - - - Access Predicates
- - - - - DEPTNO=30
*/

자동 형변환

• ORACLE 자동 형변환 규칙

1. VARCHAR2 -> NUMBER
2. VARCHAR2 -> DATE
3. CHAR -> VARCHAR2
4. DATE -> VARCHAR2

• 자동 형변환 또한 가공이기 때문에 인덱스 사용 불가능
• 자동 형변환 기능이 있더라도, 수동으로 하는 방법을 권장
• 직관적으로 인덱스가 사용되지 않는다는 것을 알 수 있음

자동 형변환 주의

• DECODE(A, B, C, D)
  • A = B 이면 C 반환, 아니면 D 반환
  • 반환 타입은 C 가 결정. D 는 C 의 타입으로 자동 형변환
  • C 가 NULL 이면 반환 타입은 VARCHAR2
• ⚠️ 형변환 함수를 생략하는 것은 SQL 성능에 영향을 주지 않는다

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

인덱스 확장기능 사용법

인덱스는 기본적으로 최적의 Index Range Scan 을 목표로 설계해야 하며,
수행 횟수가 적은 SQL 을 위해 인덱스를 추가하는 것이 비효율적일 때
확장기능의 스캔 방식을 차선책으로 활용하는 것이 바람직하다.

Index Range Scan

• B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 엑세스 방식
• 인덱스 루트에서 리프 블록까지 수직 탐색 -> 필요한 범위만 스캔
• 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 함
• 성능은 인덱스 스캔 범위, 테이블 엑세스 횟수를 얼마나 줄이느냐가 결정

Index Full Scan

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

CREATE INDEX EMP_ENAME_SAL_IDX ON EMP(ENAME, SAL);
SELECT * FROM EMP
WHERE SAL > 2000
ORDER BY ENAME;
/* 실행계획
SELECT STATEMENT
- TABLE ACCESS(EMP) BY INDEX ROWID
- - INDEX(EMP_ENAME_SAL_IDX) FULL SCAN
- - - Access Predicates
- - - - SAL>2000
- - - Filter Predicates
- - - - SAL>2000
*/

Index Full Scan 의 효용성

• 옵티마이저
  • 인덱스 선두 컬럼이 조건절에 없으면 Table Full Scan 고려
  • 대용량 테이블이라 Table Full Scan 이 부담이 크면 Index Full Scan 고려
• 인덱스가 차지하는 면적은 테이블보다 훨씬 적음
• 결과 집합이 테이블중 극히 일부라면, 인덱스를 전체 스캔하는 것이 유리
• ⚠️ Index Full Scan 은 Index Range Scan 의 차선책이다. 수행 빈도를 고려해 Index 를 만들자

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

• Range Scan 과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬되어 있음
• Sort 연산을 생략하고 Index Full Scan 후에 다른 필터링 조건 적용 가능
• 부분 범위 처리가 가능한 상황에서 극적인 성능 개션 효과

-- 대용량 테이블에서 대부분의 사원이 SAL>1000 조건을 만족하는 경우
CREATE INDEX EMP_ENAME_SAL_IDX ON EMP(ENAME, SAL);
SELECT /*+ FIRST_ROWS */ *
FROM EMP
WHERE SAL > 1000
ORDER BY ENAME;
/* 실행계획
SELECT STATEMENT
- TABLE ACCESS(EMP) BY INDEX ROWID
- - INDEX(EMP_ENAME_SAL_IDX) FULL SCAN
- - - Access Predicates
- - - - SAL>1000
- - - Filter Predicates
- - - - SAL>1000
*/

Index Unique Scan

• 수직 탐색만으로 데이터를 찾는 스캔 방식
• Unique 인덱스를 '=' 조건으로 탐색하는 경우 작동
• Unique 인덱스가 존재하는 컬럼은 DBMS 정합성 검사로 중복 값이 안들어옴
• 인덱스 키 컬럼을 모두 '=' 조건으로 검색하면 한 건 이상 탐색랑 필요 없음
• ⚠️ 범위 검색 조건, 결합 인덱스에 대해 일부 컬럼만 검색 하는 경우 Index Range Scan 작동

Index Skip Scan

• 인덱스 선두 컬럼이 조건절에 없어도 작동가능
• 인덱스 선두 컬럼의 Distinct Value(컬럼 값의 종류) 개수가 적고,
  후행 컬럼의 Distinct Value 개수가 많을 때 유용
• 하위 인덱스 컬럼의 Range Scan 이 끝나면 상위 인덱스 컬럼의 시작지점으로 Skip 가능

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

• 기본
  • Distinct Value 개수가 적은 선두 컬럼이 조건절에 없고,
    후행 컬럼의 Distinct Value 개수가 많을 때 효과적
• 추가
  • 선두 컬럼에 대한 조건절은 있고, 중간 컬럼에 대한 조건절이 없는 경우도 사용 가능
  • Distinct Value 가 적은 선두 컬럼이 모두 조건절에 없는 경우에도 사용 가능

Index Fast Full Scan

• 인덱스 리프 블록은 양방향 연결리스트.
• 논리적으로는 연결되어 있으나, 물리적으로는 뒤죽박죽
• Index Fast Full Scan 은 논리적 연결을 무시하고,
  인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔

Index Range Scan Descending

• Index Range Scan 과 동일한 방식
• 내림차순으로 정렬된 결과집합