1. 인덱스구조 및 탐색
🔎 DB 테이블에서 데이터를 찾는 방법
- 테이블 전체를 스캔함 → 튜닝요소가 많지 않음
- 인덱스를 이용함 → 튜닝 요소가 많고, 기법도 매우 다양
찾으려는 데이터가 중복이 많다면 전자가 좋을 것이고, 별로 없다면 후자가 좋을 것이다.
인덱스는 큰 테이블에서 소량 데이터를 검색할때 사용 → 온라인 트랜잭션 처리 ( Online Transaction Processing 'OLTP' ) 시스템에서 소량 데이터를 주로 검색하므로 인덱스 튜닝이 중요 !
🔎 인덱스 튜닝의 두 가지 핵심 요소
1. 인덱스 스캔 효율화 튜닝
- 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것
예 ) 학생명부에서 시력이 1.0 ~ 1.5인 홍길동 학생을 찾는 경우
학생명부를 이름과 시력순으로 정렬해 두었다면, 소량만 스캔이 가능 → 2개의 로우 스캔
| 이름 | 시력 | 학년-반-번호 |
| 김수지 | 1.5 | 4학년 3반 37번 |
| 김철수 | 0.5 | 3학년 3반 23번 |
| .... | ..... | .... |
| 홍길동 | 1.0 | 2학년 4반 22번 |
| 홍길동 | 1.5 | 1학년 2반 39번 |
| 홍길동 | 2.0 | 3학년 9반 2번 |
반면, 학생명를 시력과 이름순으로 정렬해 두었다면, 많은 양을 스캔해야함 → 3개의 로우 스캔
| 시력 | 시력 | 학년-반-번호 |
| 0.5 | 김철수 | 4학년 3반 37번 |
| 1.0 | 홍길동 | 3학년 3반 23번 |
| .... | ..... | .... |
| 1.5 | 이영희 | 2학년 7반 17번 |
| 1.5 | 홍길동 | 2학년 4반 22번 |
| 2.0 | 홍길동 | 1학년 2반 39번 |
2. 랜덤 액세스 최소화 튜닝
- 테이블 액세스 횟수를 줄이는 것
- 인덱스 스캔 후 테이블 레코드를 액세스할 때 랜덤 I/O방식을 사용함
예 ) 학생명부에서 시력이 1.0 ~ 1.5인 홍길동 학생을 찾는 경우
- 시력이 1.0~1.5인 학생은 총 50명
- 이름이 '홍길동'인 학생은 총 5명
시력이 1.0 ~ 1.5인 홍길동 학생은 총 2명일때 이름순으로만 정렬한 명부 vs 시력순으로만 정렬한 명부 중 무엇이 더 효과적일까?
→ 이름순으로 정렬한 명부가 더 효과적 ! 이름순은 홍길동인 학생의 교실을 5번만 찾아가면 되고, 시력순은 총 50명의 학생의 교실을 찾아가야 하기 때문
인덱스 스캔 효율화 튜닝과 랜덤 액세스 최소화 튜닝 중 뭐가 더 중요할까?
: 랜덤 액세스 최소화 튜닝 ! 레코드 하나를 읽기위해 하나의 블럭에 접근을 최소화 (성능에 미치는 영향이 더 크기 때문)
데이터베이스는 읽어야할 데이터량이 많고, 그 과정에서 수많은 디스크 I/O가 발생하기에 느리기에 SQL 튜닝은 랜덤 I/O최소화가 중요하다 → SQL 튜닝은 랜덤 I/O와의 전쟁이다.
🔎 인덱스의 구조
- Index (색인) : 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트
인덱스를 사용하는 이유
- 인덱스 없이 데이터를 검색하면, 테이블을 처음부터 끝까지 모두 읽어야하지만,
인덱스를 사용하면 일부만 읽고 멈출 수 있음 → 범위스캔(Range Scan) 가능 !
( 범위스캔이 가능한 이유 : 인덱스가 정렬되어 있기 때문 )
B+Tree 이란?
- 인덱스를 이루고 있는 자료구조 중 하나이고,데이터가 정렬된 상태로 유지가 가능
- 나무(Tree)를 거꾸로 뒤집은 모양이라서 뿌리(Root)가 위쪽에 있고, 가지(Branch)를 거쳐 맨 아래 잎사귀(Leaf)가 있음
- DBMS에서 일반적으로 B+Tree 인덱스를 사용함(검색, 삭제, 삽입 등 여러 작업을 효율적으로 처리하면서 트리의 균형을 유지)
- B+Tree 인덱스의 'B'가 Balanced의 약자
Balanced : 어떤 값을 탐색하더라도 인덱스 루트에서 리프 블록에 도달하기 까지 읽는 블록 수가 같음을 의미
즉, Root 로부터 모든 Leaf 블록까지의 높이는 항상 같음.
- Root와 Branch 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 가짐
- Key값은 하위 블록에 저장된 Key값의 범위를 나타냄
LMC(Leftmost Child)
- 가장 왼쪽 첫번째 레코드이며, 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킴
- Root와 Branch 블록에는 Key값을 갖지 않는 특별한 레코드
- LMC가 가리키는 주소로 찾아간 블록에는 Key값을 가진 첫번째 레코드보다 작거나 같은 레코드가 저장
- Leaf 블록에 저장된 각 레코드는 Key값 순으로 정렬되어 있으며, 테이블 레코드를 가리키는 주소값(ROWID)을 가짐
- 인덱스 Key값이 같으면 ROWID순으로 정렬됨
- 인덱스를 스캔하는 이유는 검색 조건을 만족하는 소량의 데이터를 빠르게 찾고 거기서 ROWID를 얻기 위해
ROWID 관련 참고 : [http://www.gurubee.net/lecture/2927]
테이블 레코드를 찾을 수 있는 값
- ROWID = 데이터 블록 주소(DBA Data Block Address) + 로우 번호
- 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
- 블록 번호 : 데이터파일 내에서 부여한 상대적 순번
- 로우 번호 : 블록 내 순번
🔎 인덱스 탐색 과정 ( 수직적 탐색, 수평적 탐색 )
- 인덱스 수식적 탐색
- 인덱스 스캔 시작지점을 찾는 과정
- 정렬된 인덱스 레코드 중 조건을 만족하는 레코드를 찾는 과정이 아닌 조건을 만족하는 첫번째 레코드를 찾는 과정
- 인덱스 수직적 탐색은 Root 블록에서부터 시작 ( Root - Branch - Leaf )
- Root를 포함해 Branch 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 가지기 때문에 Root부터 시작이 가능
- 수식적 탐색 과정에 찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동
ex) [그림2]에서 '이재희' 찾기
1. Root 블록에는 크거나 같은 값이 없음 → 맨 마지막 '서' 레코드가 가리키는 하위블록으로 이동
2. Branch블록에는 '이재희'보다 큰 레코드 '정재우'를 찾음
3. 바로 직전 레코드 '이재룡'가 가리키는 하위 블록으로 이동하여 Leaf 블록에 도달하여 '이재희' 찾음
※ 인덱스를 수식적으로 탐색 할때 Root 를 포함한 Branch 블록은 '푯말' 같은 역할을 함
조건을 만족하는 첫 번째 레코드가 목표 지점이며, 푯말을 만날때마다 어느쪽으로 가면 목표 레코드를 만날 수 있는지 확인하면서 이동하고, 푯말이 알려주는 대로 따라가다보면 조건을 만족하는 첫 번째 레코드를 만날 수 있다!
2. 인덱스 수평적 탐색
- 인덱스에서 본격적으로 데이터를 찾는 과정
- 수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더이상 안나타날때까지 인덱스 Leaf 블록을 수평적으로 스캔 (수직 탐색으로 도달한 Leaf 노드에서 데이터를 한 방향으로 순차적으로 스캔)
- 양방향 연결 리스트 구조(double lineked list) 이므로 좌 → 우 우 → 좌 수평적 탐색이 가능
인덱스를 수평적으로 탐색하는 이유
1. 조건적을 만족하는 데이터를 모두 찾기 위해
2. ROWID를 찾기 위해
※ 필요한 컬럼을 인덱스가 모두 갖고 있어 인덱스만 스캔하고 끝나는 경우도 있지만,
인덱스를 스캔하고 테이블도 ROWID를 통해 액세스함(필요한 컬럼을 인덱스가 모두 갖지 않는 경우가 있기 때문)
수직적 탐색은 Root에서 Leaf로 이동 , 특정 키나 범위를 찾기 위해 사용됨
수평적 탐색은 Leaf에서 옆으로 이동, 수직 탐색 후 Leaf에서 데이터의 조건을 충족하는 범위를 처리함
🔎 결합 인덱스 구조와 탐색
- 두 개 이상 컬럼을 결합해서 인덱스를 만들 수 있음
결합 인덱스 생성 시 컬럼 배치 순서
1. 인덱스를 성별 + 이름 순으로 구성한 경우 → 성별찾고, 이름 검사
2. 인덱스를 이름 + 성별 순으로 구성한 경우 → 이름 찾고, 성별 검사
select 이름, 성별
from 사원
where 성별 = '여자'
and 이름 = '유관순'
→ 선택도가 낮은 컬럼을 앞에 두고 결합인덱스를 생성해야 검사 횟수를 줄일 수 있어 성능에 유리? ❌
- 인덱스 선두 컬럼을 모두 "=" 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 블록 I/O 개수가 같으므로 성능도 같음
- DBMS의 인덱스는 B*Tree 방식을 사용하고 있기 때문에 컬럼의 순서와 상관없이 일량은 일정함 → 수직적 탐색 장점
2. 인덱스 기본 사용법
🔎 인덱스를 사용한다는 것
- 선두컬럼을 가공하지 않아야 인덱스를 정상적으로 사용 가능
- "인덱스를 정상적으로 사용한다" = Index Range Scan
- Index Range Scan : Leaf 블록 일부만 스캔, Leaf 블록에서 스캔 시작점을 찾고 거기서부터 스캔하다가 중간에 멈추는 것을 의미
더보기
※ 인덱스 컬럼을 가공하면 인덱스를 사용할 수 없나요?
결론 : 사용할수 있음! 단, 스캔 시작점을 찾을 수 없고, 멈출 수도 없어 리프 블록 전체를 스캔해야함
(일부가 아닌 전체를 스캔하는 Index Full Scan 방식)
🔎 인덱스를 Range Scan 할 수 없는 이유
인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용(Range Scan) 할 수 없다.
● 인덱스 컬럼을 가공했을때 인덱스를 정상적으로 사용할 수 없는 이유 → 인덱스 스캔 시작점을 찾을 수 없기 때문
- Index Range Scan(인덱스에서 일정 범위를 스캔) → 일정 범위를 스캔하려면 시작시점과 끝지점이 있어야 함!
where 업체명 like '%대한%'- LIKE로 중간 값을 검색 할 때도 Index Range Scan을 사용할 수 없음 → '대한'로 시작하는 값은 특정 구간에 모여 있어 Range Scan이 가능하지만, '대한'을 포함하는 값은 전체 구간에 있기 때문에 Range Scan이 불가능
- OR 조건 = IN () 으로 검색할 때도 Range Scan 방식을 사용할 수 없기 때문에 UNION ALL을 사용해서 개별적으로 조회하면 Range Scan이 가능
- IN 조건절에 대해서는 SQL 옵티마이저가 IN-List Iterator 방식을 사용( IN-List 개수 만큼 Index Range Sacn을 반복 )
조건절에 인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다.
※ 인덱스를 Range Scan 하기 위한 조건
가장 첫 번째 조건은 인덱스 선두 컬럼이 가공되지 않는 상태로 조건절에 있어야 함
다른 말로, 인덱스 선두 컬럼이 가공되지 않는 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능
But, 인덱스를 Range Scan한다고 해서 항상 성능이 좋은 건 아님!
⚙️ 지금까지의 정리
1. 인덱스는 데이터가 정렬되어 있기 때문에 Range Scan(범위 스캔)이 가능
2. 조건절에 인덱스를 가공하면 인덱스를 정상적으로 사용 할 수 없음
3. 인덱스 컬럼을 가공해도 인덱스를 사용할 순 있지만, 찾고자 하는 데이터가 전체에 흩어져 있기 때문에 Range Scan이 불가능하거나 비효율이 발생
🔎 인덱스를 이용한 소트 연산 생략
- 인덱스는 Range Scan이 가능하기 때문에, 소트연산 생략 효과도 부수적으로 얻을 수 있음
- (오름차순) ASC정렬일 때는 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적으로 탐색 후 우측으로 수평 탐색 진행
- (내림차순) DESC정렬일 때는 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 탐색을 진행
🔎 Order By 절에서 컬럼 가공
- 조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공해서 인덱스를 사용할 수 없는 경우도 존재
- ORDER BY 변경일자 || 변경순번으로 컬럼을 가공한다면 정렬 연산 생략을 할 수 없음
select *
from 상태변경이력
where 장비번호 = '1
order by 변경일자::변경순번
- SELECT문에서 가공한 컬럼을 ALIAS로 지정 후 ORDER BY에서 ALIAS로 사용하는 경우
SELECT *
FROM (
SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호
FROM 주문 A
WEHRE A.주문일자 = :dt
AND A.주문번호 > NVL(:next_ord_no, 0)
ORDER BY A.주문번호
)
WHERE ROWNUM <= 30
🔎 SELECT-LIST 에서 컬럼 가공
- 최소값: 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번째 읽는 레코드 (정렬 연산 수행하지 않음)
- 최대값 : 수직적 탐색을 통해 오른쪽 지점으로 내려가서 첫 번째 읽는 레코드
🔎 테이블 자동 형변환
- 자동 형변환 기능에 의존하지 말고, 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변화해주는 것을 추천
3. 인덱스 확장기능 사용법
🔎 Index Range Scan
- B+Tree인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식
- 인덱스 루트에서 Leaf 블록까지 수직적으로 탐색한 후에 필요한 범위만 스캔
- 인덱스를 Range Scan 하려면 선두 컬럼을 가공하지 않는 상태로 조건절에 사용해야하고, 반대로, 선두 컬럼을 가공하지 않는 상태로 조건절에 사용하면 Index Range Scan은 무조건 사용 가능함
- 성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄일 수 있냐로 결정됨
🔎 Index Full Scan
- 수직적 탐색 없이 인덱스 Leaf 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
- 데이터 검색을 위한 최적의 인덱스가 없을때 차선으로 선택됨
🔎 Index Full Scan의 효용성
- 인덱스 선두 컬럼이 조건절에 없으면 옵티마이저는 먼저 Table Full Sacn을 고려함.
- 인덱스를 Range Scan 할 수 없을 때, 테이블 전체를 스캔하는 것보다 인덱스 전체를 스캔하면 어떨까?
: 인덱스 스캔 단계에서 대부분 레코드를 필터링하고, 아주 일부만 테이블을 액세스하는 상황이라면, 면적이 큰 테이블보다 인덱스를 스캔하는 쪽이 유리하다. 이때, 옵티마이저는 Index Full Scan 방식을 선택함
//sal > 900인 사원이 전체 중 극히 일부라면 Table Full Scan 보다 Index Full Scan이 효과적 !
select *
from emp
where sal > 9000
order by ename;
🔎 인덱스를 이용한 소트연산 생략
- 인덱스를 Full Scan하면 Range Scan과 동일하게 결과집합이 인덱스 컬럼순으로 정렬되므로, Sort Order By 연산을 생략할 목적으로 사용가능
ex) SAL > 1000인 사원을 찾을때
select /*+ first_rows */ *
from emp
where sal > 1000
order by ename;
Execution Plan
----------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
2 1 INDEX (FULL SCAN) OF 'EMP_ENAME_SAL_IDX' (INDEX)
> 소트 연산을 생략함으로써 전체 집합 중 빠르게 출력할 목적으로 옵티마이저가 Index Full Scan 방식을 선택한 것
🔎 Index Unique Scan
- 수직적 탐색으로만 데이터를 찾는 스캔방식
- Unique인덱스를 '=' 조건으로 탐색하는 경우에 작동
- Unique 인덱스는 범위검색 조건(between, 부등호, like)인 경우 Index Range Scan으로 처리됨
🔎 Index Skip Scan
- Root 또는 Branch 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는' Leaf 블록만 골라서 액세스하는 스캔 방식 (선두 컬럼이 조건절이 없는 경우에도 인덱스를 활용하는 방법)
- Distinct Value(고유값) 개수가 적은 선두 컬럼이 조건절에 없고, 후행 컬럼의 Distinct Value 개수가 많을 때 효과적!
※ Index Skip Scan이 작동하기 위한 조건
더보기
1. 결합 인덱스가 존재→ Index Skip Scan은 복합 인덱스에서만 작동
2. 선두 컬럼에 조건이 없거나 일부 값만 사용할 때
3. 선두컬럼이 유일한 값 또는 상대적으로 적은 Distinct Value값을 가질때
4 . 후속 컬럼에 조건이 있을때
5. 테이블의 다른 실행계획보다 Index Skip Scan이 더 효율적일때
🔎 Index Fast Full Scan
- Index Full Scan 보다 빠름
- 논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식
- Multiblock I/O 방식을 사용하므로 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과냄
- 속도는 빠르나, 인덱스 Leaf 노드가 갖는 연결리스크 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 Key 순서대로 정렬되지 않음
- 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용가능
- Index Range Scan 또는 Index Full Scan과 다르게, 인덱스가 파티션 되어 있지 않더라도 병렬 쿼리가 가능
- 병렬 쿼리 시에는 Direct Path I/O 방식을 사용하기 때문에 I/O 속도가 더 빨라짐
| Index Full Scan | Index Fast Full Scan |
| 1.인덱스 구조를 따라 스캔 2.결과집합 순서 보장 3.Single Block I/O 4.(파티션 돼 있지 않다면) 병렬스캔 불가 5.인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능 |
1.세그먼트 전체를 스캔 2.결과집합 순서 보장 안 됨 3.Mutiblock I/O 4.병렬스캔 가능 5.인덱스에 포함된 컬럼으로만 조회할 때 사용 가능 |
🔎 Index Range Scan Descending
- Index Range Scan과 기본적으로 동일한 스캔 방식
- Index Range Scan과의 차이점: 인덱스를 뒤 → 앞으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻음
'Study > 친절한 SQL 튜닝' 카테고리의 다른 글
| [DB] 5장. 소트 튜닝 (0) | 2025.03.17 |
|---|---|
| [DB] 4장. NL조인 (0) | 2025.02.25 |
| [DB] 3장. 인덱스 튜닝(2) (0) | 2025.01.31 |
| [DB] 3장. 인덱스 튜닝(1) (0) | 2025.01.31 |
| [DB] 1장.SQL 처리과정과 I/O (0) | 2025.01.25 |