[친절한 SQL 튜닝] 인덱스 기본

데이터를 찾는 방법은 두가지가 존재한다.

1. 테이블 전체 스캔
2. 인덱스 이용

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

1. 인덱스 스캔 효율화 튜닝
   - 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것
2. 랜덤 액세스 최소화 튜닝⭐️
   - 테이블 액세스 횟수를 줄이는 것

SQL 튜닝은 랜덤 1/0와의 전쟁

데이터베이스 성능이 느린 이유는 디스크 I/O 때문!

 

OLTP(OnLine Transaction Processing)에서는 디스크 I/O 중에서도 랜덤 I/O가 특히 중요

 

인덱스는 정렬되어있기 때문에 범위 스캔(Range Scan) 가능 하다

DBMS 의 인덱스는 일반적으로 B*Tree(Balanced Tree) 인덱스를 사용한다

 

인덱스 내에는 가장 왼쪽 첫 번째 레코드를 가리키는 LMC(LeftMost Child) 레코드가 존재한다

LMC가 가리키는 주소의 블록에는 키 값을 가진 첫 번째 레코드 보다 작거나 같은 레코드가 저장되어있다

리프 블록에 저장된 각 레코드는 키 값 순으로 정렬돼 있을 뿐 아니라 테이블 레코드를 가리키는 주소값(ROWID)을 갖는다

ROWID = 데이터 블록 주소 + 로우 번호

ROWID를 알면 테이블 레코드를 찾아갈 수 있다

 

ROWID = 데이터 블록 주소 + 로우 번호

데이터 블록 주소 = 데이터 파일 번호 + 블록 번호

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

로우 번호 = 블록 내 순번

 

인덱스 탐색 : 인덱스 탐색은 두 가지로 나뉜다

1. 수직적 탐색
   - 인덱스 스캔 시작지점을 찾는 과정
   - 수직적 탐색 과정에서 찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동 한다
2. 수평적 탐색
   - 데이터를 찾는 과정
   - 스캔 시작점으로 부터 찾고자 하는 데이터가 더 안 나타날 때까지 인텍스 리프 블록을 수평적으로 스캔

결합 인덱스 : 두 개 이상의 컬럼을 결합해서 만든 인덱스

 

인덱스는 B*Tree(Balanced Tree, 루트에서 리프 블록까지 읽는 블록 수 가 같음) 이므로 컬럼 순서에 따라 일량의 차이가 나지는 않는다

 

인덱스의 기본 사용 방법은 인덱스를 Range Scan 하는 방법을 의미

• 인덱스 컬럼을 가공하지 않아야 인덱스를 정상적으로 사용(Range Scan)할 수 있다
• Like를 이용한 중간 값 검색을 할 때도 Range Scan할 수 없다
• OR 으로 검색하는 경우 Range Scan 불가능 → Or Expansion을 사용하면 Range Scan 가능(use_concat 힌트) or 를 union all sql로 변환 하여 실행하기 때문

* 이유: 인덱스 스캔 시작점을 찾을 수 없기 때문!

 

IN 조건절의 경우 OR 조건을 표현하는 다른 방식으로 SQL 옵티마이저가 IN 개수 만큼 Index Range Scan을 반복한다.(IN-List Iterator)

 

인덱스를 정상적으로 사용한다 = 리프 블록에서 스캔 시작점을 찾아서 스캔하다가 중간에 멈추는 것을 의미

인덱스 Range Scan을 위한 첫 번째 조건 : 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 한다

 

인덱스는 데이터가 정렬되어있기 때문에 인덱스를 이용하여 소팅 연산을 생략하는 효과를 볼 수 있다

 

ORDER BY 의 정렬 요소를 가공하면 정렬 연산을 생략할 수 없다

 

ORDER BY 에 인덱스 요소를 가공하여 사용하였기 때문에 별도 정렬 연산이 발생한다

 

ORDER BY 에 인덱스인 주문번호가 들어간 것 같지만 실제로는 TO_CHAR로 변환된 주문번호가 들어가기 때문에 소팅 연산이 발생 한다 이 경우 테이블 alias 인 A를 ORDER BY 주문번호에 붙여주면 정렬 연산을 생략할 수 있다

 

인덱스는 정렬되어 있기 때문에 인덱스 구성요소에 대한 MIN ,MAX를 구하는 경우 인덱스 리프블록의 왼쪽(MIN), 오른쪽(MAX)에서 레코드 하나(FIRST ROW)만 읽고 멈춘다

 

가공 후 MIN, MAX를 사용하는 경우에 대해서는 정상적으로 동작하지 못한다

 

스칼라 서브쿼리의 수 만큼 테이블을 여러번 읽어야하기 때문에 비효율적이고 SQL도 복잡해진다

 

인덱스를 사용하기 위해 조건절을 지정하는 경우 자동 형변환을 조심해야한다

 

이 경우 인덱스를 정상적으로 사용하지 못하는데, 그 이유는 생년월일 데이터가 TO_NUMBER 로 자동 형변환이 일어났고 이에 따라서 인덱스 컬럼이 가공되어 인덱스를 정상적으로 사용하지 못한 것이다

 

자동 형변환

문자 < 숫자

문자 < 날짜

LIKE를 사용하는 경에는 문자형 기준으로 숫자형 컬럼이 변환

 

자동 형변화 시 주의사항

숫자형 컬럼과 문자형 컬럼을 비교하면 문자형 컬럼이 숫자로 변환된다

이때 문자형이 숫자로 변환이 불가능하는 경우에는 에러가 발생한다

 

Index Range Scan

B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식

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

* 인덱스의 선두 컬럼을 가공하지 않은 상태로 조건절에 사용!

 

Index Full Scan

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

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

인덱스 선두 컬럼이 조건절에 없는 상황에서 조회 대상 테이블이 대용량 테이블인 경우 Full Scan에 따른 부담이 크다면 옵티마이저가 Index Full Scan을 고려한다

 

Index Full Scan을 하면 Range Scan과 마친가지고 결과 집합이 인덱스 컬럼 순으로 정렬되어있다. 따라서 Sort 연산을 생략할 목적으로 사용할 수 있다

 

Index Full Scan을 사용할 때 아래와 같이 대부분의 레코드에 대해 테이블 엑세스가 발생하는 경우 Full Scan 보다 오히려 불리할 수 있다 다만 아래의 경우는 결과 집합의 일부만 빠르게 출력할 목적으로 first_rows 힌트를 사용했기 때문에 Index Full Scan을 사용했다

* 부분범위 처리가 가능한 상황에서는 극적인 성능 개선 효과를 가져다 줄 수 있다

 

Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식으로 Unique 인덱스를 = 조건으로 탐색하는 경우 동작(인덱스에 포함된 모든 컬럼에 대해 조건절에 = 조건으로 검색할 때)

 

Index Skip Scan

인덱스 선두 컬럼이 조건절에 없어도 사용할 수 있는 방법(9i에 등장) 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많은 때 유용 Distinct Value가 적은 두 개의 선두컬럼이 모두 조건절에 없는 경우, 중간 컬럼이 없는 경우도 사용 가능

 

Index Fast Full Scan

논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하는 방법(물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 읽는다.)

- 결과 집합이 정렬되지 않음.

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

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

병렬 쿼리 시 에는 Direct Path I/O 방식 사용으로 I/O 속도가 더 빨라진다

 

Index Range Scan Descending

Index Range Scan과 동일 방식으로 스캔하나 인덱스 뒤에서 부터 앞쪽으로 스캔하기 때문에 결과 집합이 내림차 순으로 된 결과를 얻을 수 있다.(index_desc 힌트로 유도 가능)