2장. 인덱스 기본

오라클/튜닝

2장. 인덱스 기본

dbavayne 2022. 6. 18. 21:41

2.1 인덱스 구조 및 탐색

테이블에서 데이터를 찾는 두 가지 방법

1. 테이블 전체 스캔

2. 인덱스 이용

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

1. 인덱스 스캔과정에서의 비효율 줄이기(인덱스 스캔 효율화, 소량만 스캔)

2. 테이블 액세스 횟수 줄이기(랜덤 액세스 최소화)

랜덤 액세스 최소화 > 인덱스 스캔 효율화

인덱스 구조(B+Tree)

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

- 테이블에서 인덱스 없이 데이터를 검색하려면, 테이블 전체 스캔해야하는데 인덱스 이용시 일부만 스캔하고 멈출 수 있다.

- 인덱스를 사용하면 Range Scan이 가능하다.

- Root 블록과 Branch 블록의 각 레코드는 하위 블록에 대한 주소 값을 가진다.

- Leaf 블록은 인덱스를 구성하는 컬럼의 데이터와 해당 데이터를 가지는 행의 위치를 가리키는 레코드 식별자로 구성

( 생성 컬럼의 데이터 값 + Table Row의 Row ID )

- 인덱스 데이터는 인덱스를 구성하는 컬럼의 값으로 정렬된다.
- 리프 블록은 양방향 링크를 가지고 있으며 이를 통해 오름차순 및 내림차순 검색을 쉽게 할 수 있다.
- B Tree 인덱스는 "=", "BETWEEN", ">", "<" 등과 같은 연산자로 검색구조에 적합한 구조이다.

수직적 탐색 > 수평적 탐색 > Row ID로 테이블 접근

수직적 탐색

- 인덱스 스캔의 시작점을 찾는 과정(Root > Branch > Leaf)

- 조건을 만족하는 첫 번째 레코드를 찾는 과정

수평적 탐색

- 데이터를 찾는 과정(... Leaf <> Leaf <> Leaf ...)

- 조건절을 만족하는 모든 데이터 찾기

- Row ID 얻기

+ Row ID ( 데이터마다 주소를 의미하는 개념 )

ROWID 의 구조

오브젝트 번호
- 해당 데이터가 속하는 오브젝트 번호이다. 오브젝트 별로 유일한 값을 가지고 있다.
상대 파일 번호
- 오라클의 테이블 스페이스는 여러 개의 DATAFILE을 생성할 수 있다. 오라클 8i 부터는 파일 번호가 10비트이기 때문에 테이블 스페이스당 1023개의 DATAFILE을 추가할 수 있다. 여기서 DATAFILE은 해당 테이블 스페이스의 상대 파일 번호를 의미하며, 각 데이터별로 유일한 값을 가지고 있다.
블록 번호
- 파일 안에 어느 블록인지 의미한다.
데이터 번호
- 블록의 Header에서 해당 데이터의 위치 값을 저장하고 DATA Directory Slot을 가리킨다. 오브젝트 번호, 상대 파일 번호, 블록 번호가 같으면 데이터 번호는 블록 별로 데이터가 저장되어 순서를 의미한다.

SQL> SELECT ENAME, SAL, ROWID FROM emp;

ENAME SAL ROWID

-------------------- ---------- ------------------

SMITH 800 AAAFJTAABAAAMF5AAA

ALLEN 1600 AAAFJTAABAAAMF5AAB

WARD 1250 AAAFJTAABAAAMF5AAC

JONES 2975 AAAFJTAABAAAMF5AAD

MARTIN 1250 AAAFJTAABAAAMF5AAE

BLAKE 2850 AAAFJTAABAAAMF5AAF

CLARK 2450 AAAFJTAABAAAMF5AAG

SCOTT 3000 AAAFJTAABAAAMF5AAH

KING 5000 AAAFJTAABAAAMF5AAI

TURNER 1500 AAAFJTAABAAAMF5AAJ

ADAMS 1100 AAAFJTAABAAAMF5AAK

JAMES 950 AAAFJTAABAAAMF5AAL

FORD 3000 AAAFJTAABAAAMF5AAM

MILLER 1300 AAAFJTAABAAAMF5AAN

그럼 앞서 추출된 ROWID의 18자리를 분석하자.

- ROWID의 처음 6자리, AAAFJTAABAAAMF5AAA

AAAFJT 는 오브젝트 번호이다. 이는 오브젝트 별로 유일한 값을 가지므로 EMP 테이블의 모든 데이터의 ROWID는 AAAFJT로 시작한다.

- ROWID의 7자리 ~ 9자리, AAAFJTAABAAAMF5AAA

EMP 테이블이 저장되어 있는 테이블 스페이스의 상대 파일 번호이다. EMP 테이블의 데이터일지라도 테이블의 익스텐트(EXTENT : 정도, 범위)가 다른 데이터 파일에 할당될 수 있다. 이 경우 해당 값은 서로 다르게 할당된다.

- ROWID의 10자리 ~ 15자리, AAAFJTAABAAAMF5AAA

블록 번호로 해당 데이터가 저장되어 있는 블록을 뜻한다.

- ROWID의 16자리 ~ 18자리, AAAFJTAABAAAMF5AAA

오브젝트 번호, 파일 번호, 블록 번호가 동일하면 해당 데이터 번호는 해당 데이터의 저장 순서를 의미한다.

결합 인덱스 구조와 탐색

- 두개 이상의 컬럼으로 생성한 인덱스

- 중복이 적은 고유값을 가진 컬럼부터 앞에 배치하는 것이 일반적으로 성능이 좋다고 생각할 수 있으나, B*Tree 인덱스는 평면 구조가 아니기 때문에, 컬럼순서가 어떻든 바로 찾아갈 수 있다.

즉, 컬럼 순서가 어떻든 성능에는 지장이 없다

2.1 인덱스 기본 사용법

인덱스 컬럼은 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.

인덱스를 정상적으로 사용

리프 블록에서 스캔 시작점을 찾아 스캔을 시작해서 중간에 멈출 수 있음을 의미
Index Range Scan을 의미

인덱스를 가공하면 중간에 스캔을 멈출 수 없어서 일부가 아닌 전체를 스캔하게 됨. (Index Full Scan)

인덱스 가공하면 Range Scan을 못 쓰는 이유

인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다.

일정 범위를 스캔하려면 ‘시작지점’과 ‘끝지점’이 있어야 한다.

인덱스 가공

-- 생년월일 인덱스로 생성한 경우 

(Index Range Scan O) 
where 생년월일 between '20070101' and '20070131'

(Index Range Scan X) 
where substr(생년월일, 5, 2) = '05'


위와 같이 인덱스컬럼을 가공하면 년도 순으로 정렬된 인덱스에 5월을 기준으로 찾아야 하니 띄엄띄엄 찾아야한다.

이것은 시작지점과 끝지점을 찾을수 없게된다.

그래서 Index Full Scan을 하게 된다.

Like 문을 사용

-- 업체명 인덱스로 생성한 경우

(Index Range Scan O) 
where 업체명 like '대한%'

(Index Range Scan X) 
where 업체명 like '%대한%'

LIKE로 검색해도 위와 같이 ‘대한’이라는 글자를 가진 인덱스를 중간 글씨에서 찾아야 하기 때문에
Range Scan이 불가능하다.

OR 조건

-- OR Expansion (Index Range Scan X)
where (전화번호 = '01012345678' OR 고객명 = '홍길동')

OR 조건으로 검색할 때
수직적 탐색을 통해 전화번호 ‘01012345678’이거나 고객명이 ‘홍길동’인 시작지점을 찾을 수 없다.


-- union all Expansion (Index Range Scan O)
select * from 고객 
where 고객명 = '홍길동'
union all
select * from 고객
where 전화번호 = '01012345678' and (고객명 <> '홍길동' OR 고객명 is null)

하지만 union all을 사용하면 각 쿼리당 다른 브랜치 블록을 타기 때문에, Index Range Scan 가능

IN 조건

-- In Expansion (Index Range Scan X)
where 전화번호 IN ('01012345678','0104567891')

-- union all Expansion (Index Range Scan O)
select * from 고객 
where 전화번호 = '01012345678'
union all
select * from 고객 
where 전화번호 = '0104567891'

더 중요한 인덱스 사용 조건

만약 인덱스가 [소속팀 + 사원명 + 연령] 순으로 되어 있는데, 사원명으로 스캔하면 range scan이 될까?

답은 아니다. range scan을 하기 위해선 가장 첫번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다.

반대로 말하면 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 range scan이 무조건 가능하다.

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

인덱스는 데이터가 정렬된 상태로 모여있기 때문에 Range Scan이 가능하고 sort 연산 생략 효과도 얻게된다.

Sort 연산 생략

[장비번호 + 변경일자 + 변경순번] 등으로 구성한 인덱스

select *
from 상태 변경 이력
where 장비번호 = 'C'
and 변경일자 = '20180316'

위 쿼리 실행하면 변경순번 순으로 출력된다.
이미 변경순번 순으로 정렬되어 인덱스가 구성되어 있기 때문에 sort 연산을 하지 않는다.


select *
from 상태 변경 이력
where 장비번호 = 'C'
and 변경일자 = '20180316'
order by 변경순번 desc

내림차순 쿼리일때는 우측으로 수직적 탐색한 후 좌측으로 수평적탐색을 한다.

Order by 절에서 컬럼 가공

조건절 뿐만 아니라 ORDER BY절이나 SELECT-LIST 에서 컬럼을 가공하여 인덱스를 정상적으로 못 사용하는 경우가 있다.

-- Index Range Scan 가능
select * 
from 상태변경이력 
where 장비번호 = 'C'
order by 변경일자, 변경순번

-- Index Range Scan 불가능 (ORDER BY 가공)
select * 
from 상태변경이력 
where 장비번호 = 'C'
order by 변경일자 || 변경순번

두번째 쿼리는 order by가 가공되었기 때문에 인덱스를 정상적으로 사용할 수 없다.

그리고, select 절에서 가공된 인덱스를 서브쿼리로 만들어 사용해도 인덱스를 정상적으로 사용할 수 없다.

SELECT-LIST 에서 컬럼 가공

select NVL(MAX(TO_NUMBER(변경순번)), 0)
from 상태변경이력
where 장비번호 = 'C'
    and 변경일자 = '20180316'

MIN, MAX 값을 수직적탐색을 이용해 정렬연산 없이 데이터를 찾을 수 있다.

하지만 위와 같이 ‘변경순번’의 값을 숫자로 바꾸면 인덱스를 사용할 수 없다.

왜냐면 현재 인덱스는 문자열 기준으로 정렬되어 있는데 숫자값으로 바꾸었기 때문이다.

select NVL(TO_NUMBER(MAX(변경순번)), 0)
from 상태변경이력
where 장비번호 = 'C'
    and 변경일자 = '20180316'

위와 같이 변경하면 인덱스를 사용하여 변경순번의 MAX값을 찾고 NUMBER로 변경하니 성능저하가 없다.

자동 형변환

인덱스 가공도 안했는데 가끔 옵티마이저가 테이블 전체 스캔하는 경우 있다.

그 이유는 자동 형변환 때문이다.

--생년월일이 선두 컬럼 인덱스

select * from 고객 where 생년월일 = 19920920

-- 위의 쿼리를 실행하면 옵티마이저가 이렇게 바꾸며 인덱스 Range Scan 사용 X
select * from 고객 where TO_NUMBER(생년월일) = 19920920

* 생년월일 컬럼이 문자형
* 조건절 비교값은 숫자형으로 표현

형변환 우선순위

오라클에서 숫자형과 문자형이 만나면 숫자형이 이긴다.
LIKE연산의 경우 LIKE연산자 자체가 문자형 비교이므로 문자형 기준으로 숫자형 컬럼이 변환된다.
날짜형과 문자형이 만나면 날짜형이 이긴다.

인덱스 컬럼 기준으로 형변환 잘하자

형변환 당하지 않게 들어오는 값이 컬럼과 다른 값이 들어오지 않게 형변환을 잘 해야 인덱스를 정상적으로 사용할 수 있다.

인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 TO_DATE, TO_NUMBER, TO_CHAR 등을 사용하여 정확히 형변환하자.

2.3 인덱스 확장기능 사용법

Index Range Scan

Index Range Scan은 B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 엑세스 방식이다.

수직적 탐색 후 필요한 범위(Range)만 수평적 탐색한다.
인덱스 스캔 범위와 테이블 액세스 횟수를 줄이는 것이 성능 향상의 길이다.

Index Full Scan

Index Full Scan은 수직적 탐색 없이 오직 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
최적의 인덱스가 없을때, 차선으로 선택된다.

Index Full Scan 효용성

선두 컬럼이 조건절에 없으면 옵티마이저가 Index Full Scan을 고려한다. (ename이 조건절에 없으므로 index range scan은 불가능하고 차선책 sal 컬럼으로 full scan)
만약 테이블이 대용량이면 인덱스 활용을 고려하지 않을 수 있다.
데이터 저장공간(컬럼길이 * 레코드 수) 인데 인덱스가 차지하는 면적은 테이블보다 훨씬 적고 아주 일부만 테이블을 액세스하는 상황이라면, 면적이 큰 테이블보다 인덱스를 스캔하는 쪽이 유리하다.

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

Index Full Scan 역시 Range Scan과 마찬가지로 인덱스 컬럼순으로 정렬된다.
즉, Sort Order By 연산을 생략할 수 있다. 그러한 목적으로도 사용한다.

Index Unique Scan

Index Unique Scan은 수직적 탐색으로만 데이터를 찾는 방식으로, Unique 인덱스를 = (equal) 조건으로 탐색하는 경우

Unique 인덱스가 존재하는 컬럼은 중복값 없이 입력되지 않게 DBMS가 정합성 관리해준다. 그래서 데이터를 = 조건으로 찾은 후 더이상 탐색이 필요없다.

Unique 인덱스가 존재해도 Between, Like 등 범위 조건으로 검색하면 Index Range Scan을 한다.
Unique 결합 인덱스에 대해 일부 컬럼만 검색 할 때 Index Range Scan을 사용한다.

Index Skip Scan

오라클은 9i버전에서 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 Index Skip Scan을 사용한다.

루트 또는 브랜치 블록에서 읽은 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 액세스
*Index Skip Scan은 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 갯수가 많을때 유용하다.

예시

Index Skip Scan이 동작하는 방식을 예시로 설명한다.

Distinct Value 개수가 가장 적은 컬럼은 '성별'이다.
Distinct Value 개수가 가장 많은 컬럼은 '고객번호'다.

-- 인덱스스캔방식 유도를 위한 index_ss 힌트 사용  (index_ss , no_index_ss)

select /*+ index_ss(사원 사원_IDX) */ * 
from 사원 
where 연봉 between 2000 and 4000

성별 '남'보다 작은 값이 있을까봐 일단 첫번때 리프 블록 액세스
연봉 >= 800인 2번째 리프 블록은 Skip
연봉 >= 1500인 3번째 리프 블록 다음이 연봉 >= 5000이기 때문에 조건을 만족하는 값이 있을 가능성이 있어 3번째 리프 블록 액세스
연봉 >= 5000인 4번째 리프 블록은 애초에 조건에 만족하지 않기 때문에 Skip
5번째 리프 블록 또한 같은 이유로 Skip
6번째 리프 블록은 연봉 >= 10000이라 skip 될것 같지만, 다음 블록에서 성별 조건이 바뀌어서 일단 액세스
7번 블록은 성별 상관없이 연봉 >= 3000 이고, 다음 레코드는 연봉 >= 5000이기 때문에 액세스
8번 ~ 9번 블록은 연봉 >= 5000, 연봉 >= 7000 이기 때문에 조건에 만족하지 않아 Skip
10번 블록 또한 연봉 >= 10000으로 조건에 만족하지 않지만, 다음 성별이 있을수 있으니 일단 액세스

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

선두 컬럼이 조건절에 있고, 중간 컬럼이 조건절에 없어도 Index Skip Scan을 사용할 수 있다.
Distinct Value 개수가 적은 두 개의 선두컬럼이 다 조건절에 없어도 Index Skip Scan 사용할 수 있다.
선두 컬럼이 부등호, BETWEEN, LIKE 같은 범위 검색 조건일때도 Index Skip Scan 사용할 수 있다.

일별업종별거래_x01 : 기준일자 + 업종유형코드

select /*+ index_ss(A 일별업종별거래_x01) */ 
	기준일자, 업종코드, 체결건수, 체결수량, 거래대금
from 일별업종별거래 A 
where 기준일자 between '20080501' and '20080531'
and 업종유형코드 = '01'

--기준일자 조건을 만족하는 인덱스 구간에서 
  업종유형코드 '01'인 레코드를 포함할 가능성이 있는 리프 블록만 액세스한다.

Index Fast Full Scan

Index Fast Full Scan은 Index Full Scan보다 빠르다.
그 이유는, 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하기 때문이다.

인덱스의 논리적 구조와 물리적 순서에 따라 재배치된 구조의 차이

인덱스의 논리적 구조

Index Full Scan은 인덱스의 논리적 구조를 따라 루트 → 브랜치1 → 1 → 2 → 3 → 4 → 5 → 6 → 7 → 8 → 9 → 10번 순으로 블록을 읽어들인다.

물리적 순서에 따라 재배치된 구조

Index Fast Full Scan은 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 읽어들인다.

Multiblock I/O 방식으로 왼쪽 익스텐트에서 1 → 2 → 10 → 3 → 9번 순으로 읽고,
그 다음 오른쪽 익스텐트에서 8 → 7 → 4 → 5 → 6번 순으로 읽는다.

루트와 두 개의 브랜치 블록도 읽지만 필요 없는 블록이므로 버린다.

Index Fast Full Scan 특징

Multiblock I/O 방식을 사용한다.
디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과 발휘한다.
인덱스 키 순서대로 정렬되지 않다. (연결 리스트 구조를 무시해서 결과집합이 인덱스 키 순서대로 정렬되지 않음)
쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때 사용 가능함.
인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능
병렬 쿼리 시에는 Direct I/O방식 사용하기 때문에 I/O 속도가 더 빨라짐

Index Full Scan

1. 인덱스 구조를 따라 스캔
2. 결과집합 순서 보장
3. Single Block I/O
4. (파티션 돼 있지 않다면) 병렬스캔 불가
5. 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능

Index Fast Full Scan

1. 세그먼트 전체를 스캔
2. 결과집합 순서 보장 안 됨
3. Multiblock I/O
4. 병렬스캔 가능
5. 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능

Index Range Scan Descending

Index Range Scan과 기본적으로 동일 스캔방식이나, 인덱스를 뒤에서 앞으로 스캔하기 때문에 내림차순으로 결과집합을 얻는다.

Order By Desc 가 있으면 옵티마이저는 Index Range Scan Descending을 실행
"index_desc 힌트"를 이용하여 Index Range Scan Descending 실행 가능

해당컬럼에 인덱스가 있고 MAX 함수를 사용시 Index Range Scan Descending 사용

'오라클 > 튜닝' 카테고리의 다른 글

6장. DML 튜닝 (0)	2022.06.26
5장. 소트 튜닝 (0)	2022.06.26
4장. 조인 튜닝 (0)	2022.06.26
3장. 인덱스 튜닝 (0)	2022.06.19
1장. SQL 처리 과정과 I/O (0)	2022.06.18

현재글2장. 인덱스 기본

toDBA

SQL, aurora, Oracle 1Z0-908, ec2, table, using filesort, AWS, MySQL 8.0, SAA, clickhouse, Oracle MySQL 8.0 Database Administraton, mysql모니터링, 티스토리챌린지, MySQL, 모니터링, Maria, pt-table-checksum, db모니터링, RDS, mariadb,

Today :
Yesterday :

toDBA