[DB] 친절한 SQL 튜닝 - 인덱스

인덱스란

사전의 앞을 보면 가,나,다,라...등으로 대분류한 페이지 같이 단어를 찾기 위해서 모든 책을 보지 않고 분류된 페이지를 토대로 원하는 단어를 쉽게 찾는 방법과 같다.

 

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

학생명부를 갖고 사람을 찾는데 시력이 1.0~1.5이고 이름이 홍길동인 일부 사람만 찾고싶다.

여기서 인덱스를 사용한 경우와 테이블 풀 스캔과 같이 학생부를 처음부터 전체 찾아보는 경우를 본다.

 

1. 인덱스 스캔

1. 이름과 시력을 순서로 분류한 학생명부 ( 인덱스 활용 )

이미 분류를 해둔 상태기 때문에 홍길동 + 2.0인 경우 까지 로우를 읽고 이전까지의 정보만 리턴한다.

이름	시력	학년-반-번호
강수지	1.5	4-2-27
김철수	0.5	3-2-13
	...	...
홍길동	1.0	2-6-17
홍길동	1.5	3-6-30
홍길동	2.0	1-7-3

 

2. 처음부터 찾아보기 학생명부 ( 테이블 풀 스캔 )

분류가 되어 있지 않음으로 처음부터 끝까지 전부 확인한 다음 리턴한다.

시력	이름	학년-반-번호
1.5	홍길동	4-2-27
2.0	강수지	3-2-13
1.2	김명환	2-6-17
...	...	...
1.1	홍길동	3-6-30
...	...	...

 

2. 랜덤 액세스 최소화

예를 들어 학생명부가 이름과 시력으로 분류되어있으면 좋겠지만 이름으로 분류된 명부와 시력으로 분류된 명부가 나눠졌다면 이름으로 분류된 명부가 중복이 적은 쪽이 더 유리하다.

시력이 1.0 ~ 1.5가 많은 경우 해당 학생의 이름을 찾으러 반을 가야한다.

홍길동이라는 이름의 빈도수가 많은 경우도 마찬가지다.

어떤 값이 학생을 직접 찾으러가는 빈도 수가 적은지를 판단하는 것이 랜덤 액세스를 줄이는 방법이다.

위 그림과 같이 인덱스 스캔 중 학생명부에 시력이나 이름이 없는 경우 해당 학생을 찾기 위해 테이블에 가서 확인 해야 한다.

 

인덱스의 구조

인덱스는 B*Tree 형태의 구조를 사용한다.

나무를 거꾸로 뒤집은 모양으로 뿌리(root)가 위에있고 가지를 거쳐 잎사귀가 있다.

솔직하게 구조 설명에서 이 책에 설명이 요점이 좀 흐려져있다고 생각해서 별로 내용이 와 닿지 않는다.

B*Tree 형식으로 위 그림에서 숫자 65를 찾는다고 했을때 순서이다.

수평적 탐색이기때문에 노드당 키값이 여러개가 될 수 있다.

1번째로 30과 50을 비교했을때 65가 들어올 수 없기 때문에 50과 70사이에 자식 노드를 살펴본다. (수평적 탐색)

루트 노드의 자식노드 중 3번째인 60 | 65를 찾는다 그 다음 자식 노드는 75 | 80 임으로 60 | 65노드를 타고 내려간다. (수직적 탐색)

60보다 크기 때문에 다음 노드인 65를 찾는다 그 다음 키값이 없음으로 이전 값인 65리턴 한다. (수평적 탐색)

항상 노드를 스캔할때 찾고자하는 값의 범위를 벗어나는 위치까지 가고 그리고 직전 값으로 타고 들어가는 방법으로 진행된다.

딱 맞는 값이 나왔다고 멈추고 리턴하지 않는다.

 

정상적인 인덱스 스캔

1. 명확한 시작 지점 설정

*Index Range Scan : 정상적으로 인덱스의 시작점을 찾아서 중간에 멈추는 경우

*Index Full Scane : 인덱스의 시작점과 멈추는 지점을 찾을 수 없는경우

 

인덱스를 사용하려면 마땅히 정확히 시작하는 위치를 잡을 수 있어야 한다.

시작기준 명확 : 학생명부에서 2000년 1월 1일 ~ 2000년 2월 1일까지 태어난 학생을 찾고자한다면 수직적탐색으로 그 일자를 기준으로 학생을 한명 찾고 1월  31일 이후 일자가 나오면 스캔을 멈추면 된다.

 

시작 기준 불명확 : 5월에 태어난 학생 찾기 -> 모든 연도가 포함되기 때문에 이경우는 시작 지점을 찾을 수 없어서 Index Full Scan이 되어 버린다.

또한 인덱스값을 조건절에서 가공하거나 Like 절 사용시 마찬가지로 인덱스의 시작지점을 잡을 수가 없다.

 

or 절또한 불가능하고 In절의 경우도 사실 or절과 다를바가 없어서 불가능한데 가능한 방법이 있다.

// 인덱스 생성
CREATE INDEX idx_employee_salary ON employees(salary);

// In절을 통해서 실행한 경우 (사실 옵티마이저가 IN-List 형태로 in의 갯수만큼 반복하여 Range Scan 적용)
EXPLAIN PLAN FOR
select *
from employees
where salary in (5000, 6000);

// In절의 조건을 명확하게 Union all을 사용해서 equal로 변경한경우
EXPLAIN PLAN FOR
select *
from employees
where salary = 5000
union all
select *
from employees
where salary = 6000;

// 실행계획 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

In 절을 사용한 결과

Union All을 사용한 결과

2. 인덱스 선두 컬럼이 조건절에 존재 (가공X)

가공되지않은 조건절이 인덱스의 선두컬럼에 존재해야한다.

예를 들어 인덱스에 학생명부의 이름 + 생년월일 + 학년으로 분류 되어있다고 할때 만약에 조건절에 생년월일만 넣고 조회한다면

인덱스를 타지 않는다. B*Tree 전반에 걸쳐서 이미 생성된 인덱스는 이름 + 생년월일 + 학년으로 작업이 진행되어있어서 생년월일로만 조회를 한다면 리프 블록 전 구간에 흩어진 정보를 찾아야한다.

인덱스의 조건절에 이름이 정상적으로 선두컬럼으로 되어있고 그다음에 생년월일이 가공된 조건절로 들어있다면 인덱스는 탈 수 있다.

다만 꼭 Range Scan이 들어간다고 해서 항상 성능이 좋지 않다.

 

인덱스 소트 연산

인덱스는 특정 컬럼들을 통해 이미 분류를 해놓은 상태이다.

따라서 Order By를 생략하고 사용할 수 있는경우가 많다.

예를 들어 인덱스를 장비번호 + 장비명 + 상태 생성을 한다음

SELECT Min(변경순번)
FROM 상태변경이력
WHERE 장비번호 = 'C'
  AND 변경일자 = '20170316'

위 쿼리로 조회시 실행계획에 FIRST ROW에 INDEX RANGE SCAN (MIN/MAX) 상태변경이력으로 출력된다.
B*Tree 구조 특징상 수직적 탐색후 좌측에 값이 열 하나가 최소값이고(LMC) 우측이 최대값이라서
한번만 읽고 바로 리턴한다. 이미 인덱스 생성시 구조가 만들어지면서 정렬이 되어있기 때문이다.

 

인덱스 스캔 방식 주요 5가지

1. Index Range Scan

앞서본 내용과 같이 B*Tree로 구조로 인덱스는 구성이되며 수직적탐색이후 수평탐색으로 범위만큼 스캔한다.

인덱스는 선두컬럼이 가공되지않은 조건이 수반되어야 정상 동작이 된다.

 

2. Index Full Scan

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

예를 들어 인덱스의 컬럼이 두가지로 이름과, 급여로 지정되었을때

이후 select문에서 where절에 인덱스의 선두 컬럼인 이름없이 급여만 지정한 경우 Index Full Scan으로 조회하게된다.

 

사용하는 예시)

일반적으로 테이블은 가로X세로 (컬럼길이 X 레코드 수)로 결정 되지만 인덱스가 차지하는 면적은 일반적으로 테이블보다 훨씬 적다. 필요한 컬럼만 추려서 따로 작업을 한것이기 때문이다.

따라서 Index Range Scan을 사용하기에 제한이 있는 검색 조건이면서 소량의 데이터를 추출하고자할때 유리하게 동작한다.

 

3. Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식이다. '=' 조건으로 탐색할때 동작한다.

Unique Index 컬럼은 중복 값이 입력되지 않도록 DMS에서 정합성 관리를 해주며 select문에서 찾는 즉시 리턴한다.

유니크 인덱스로 걸어도 like나 between 비교연산자를 where절에 둔다면 수평적 탐색을 진행할 수 밖에 없다. (Index Range Scan)

 

4. Index Skip Scan

오라클에서 인덱스 선두 컬럼 없이 인덱스를 활용하는 Index Skip Scan을 보여줬다.

이 인덱스 방식은 인덱스의 선두컬럼 사용없이 이후 컬럼들을 통해서 스캔하는 방법이다.

예를 들어 도서관에서 역사,과학 등으로 대분류 되어있는 항목을 무시하고 책의 저자만으로 찾고 싶을때 유리하게 동작한다.

여기서 선두 컬럼의 Distinct Value는 적고 후행컬럼의 Distinct Value가 많을때 유용하다.

여기서 Disctinct Value는 열의 고유값을 의미한다 고유값이 유니크할수록 갯수가 많다는 의미다.

 

5. Index Fast Full Scan

Index Full Scan은 인덱스의 논리 구조에 따라서 루트 -> 브랜치 -> 1 -> 2 -> 3 -> 4 -> 5 순서로 블록을 읽는다고 한다면

Index Fast Full Scan의 경우 인덱스의 물리적인 디스크에 저장된 순서대로 1 -> 2 -> 5 -> 4 _> 3 이런식으로 왼쪽 익스텐트 부터 읽는다.

루트와 브랜치는 필요없는 블록이므로 버린다.

속도는 Index Full Scan 보다 빠르지만 정렬되지않은 상태로 읽어드려서 섞인다.

또한 Index Full Scan과 달리 파티션 돼 있지 않아도 병렬 쿼리가 가능하며 병렬 쿼리시 Path I/O 방식으로 사용된다.

또한 Multi Block으로 동작한다.