데이터베이스
인덱스 튜닝
- 인덱스 튜닝 방법에는 여러 가지가 있지만, 핵심 요소는 크게 2가지로 나뉜다.
- 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것(인덱스 스캔 효율화 튜닝)
- 테이블 액세스 횟수를 줄이는 것(랜덤 액세스 최소화 튜닝)
인덱스 구조
-
인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트이다.
-
인덱스를 이용하여 일부만 탐색하는 범위 스캔이 가능하다.
-
범위 스캔이 가능한 이유는 인덱스가 정렬되어 있기 때문이다.
-
DBMS는 일반적으로 B-Tree(균형트리) 인덱스를 사용한다.
- 루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖는다.
- 키값은 하위 블록에 저장된 키값의 범위를 나타낸다.
- 루트와 브랜치 블록에는 키값을 갖지 않는 특별한 레코드가 있는데 이를 LMC(Leftmost Child)라고 한다.
- LMC는 자식 노드 중 가장 왼쪽 끝에 위치한 블록으로 블록에서 키값을 가진 첫번째 레코드보다 작거나 같은 레코드가 저장되어 있다.
- 리프블록에 저장된 각 레코드는 키값 순으로 정렬돼 있을 뿐만 아니라 테이블 레코드를 가리키는 주소값(ROWID)를 갖는다.
- 인덱스 스캔을 통해 검색 조건을 만족하는 데이터를 찾아 ROWID를 얻어 이를 통해 테이블 레코드를 찾을 수 있다.
※ROWID = 데이터 블록 주소 + 로우 번호
※데이터블록주소 = 데이터 파일 번호 + 블록 번호
인덱스 탐색
- 인덱스를 탬색하는 과정은 수직적 탐색과 수평적 탐색으로 나눌 수 있다.
수직적 탐색
- 인덱스 스캔 시작지점을 찾는 과정
- 루트를 포함한 브랜치 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 갖는다. 이를 통해 루트에서 시작해 리프 블록까지 수직적 탐색이 가능하다.
- 수직적 탐색 예시) "강덕숭" 찾기
- 루트 블록에 "강덕숭"보다 큰 값("서")이 존재하므로 바로 직전 레코드인 LMC가 가리키는 하위 블록으로 이동
- 이동한 브랜치 블록에서 "강덕숭"과 일치하는 레코드 발견
- "강덕숭" 레코드의 직전 레코드인 LMC가 가리키는 하위 블록으로 이동
- 이동한 리프 블록 맨 마지막에 저장된 "강덕숭" 발견
- 수직적 탐색의 목적은 "조건을 만족하는 첫 번째 레코드 찾기"이다.
수평적 탐색
-
수직적 탐색을 통해 스캔 시작점을 차았으면, 찾고자하는 데이터가 더 나타나지 않을 때까지 인덱스 리프 블록을 수평적으로 스캔한다.
-
인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖는다(양방향 연결 리스트)
-
수평적 탐색을 통해 조건절을 만족하는 데이터를 모두 찾을 수 있다.
-
수평적 탐색을 통해 ROWID를 얻을 수 있다. 필요한 컬럼을 인덱스가 모두 갖고 있어 인덱스만 스캔하고 끝나는 경우도 있지만, 일반적으로 인덱스를 스캔하고 난 후 테이블도 액세스한다. 이때 ROWID를 통해 테이블에 액세스할 수 있다.
결합 인덱스 구조와 탐색
-
2개 이상 컬럼을 결합하여 인덱스를 만들 수도 있다.
-
위 그림에서 인덱스를 [고객명 + 성별]로 구성하든, [성별 + 고객명]으로 구성하는 읽는 인덱스 블록 개수는 똑같다.
-
따라서 인덱스 선두 컬럼을 모두 "=" 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 블록 I/O 수가 같으므로 성능이 동일하다.
인덱스 기본 사용법
-
인덱스 기본 사용법으로 인덱스 범위 스캔 방법을 알아야 한다.
-
인덱스 범위 스캔을 사용하려면 인덱스 컬럼(선두 컬럼)을 가공하지 않아야 한다. 즉, 시작지점과 끝지점이 있어야 한다.
-
인덱스 컬럼을 가공했다면 스캔 시작점을 찾을 수 없고, 멈출 수도 없어 인덱스 전체를 스캔하는 인덱스 Full Scan 방식으로 작동한다.
-
위와 같이 조건절에서 인덱스 컬럼을 가공할 경우 인덱스 Full Scan으로 처리된다.
-
IN 조건과 OR 조건의 경우 UNION ALL 방식으로 인덱스 범위 스캔할 수 있다.
-
인덱스 범위 스캔을 한다고 해서 항상 성능이 좋은 것은 아니다. 인덱스를 타더라도 인덱스 리프 블록에서 스캔하는 양이 많다면 성능 개선을 기대하기 어렵다.
인덱스 사용 조건
-
인덱스 범위 스캔 하기 위해서는 인덱스 선두 컬럼이 조건절에 있어야 한다.
ex)
select 사원번호, 소속팀, 연령, 입사일자, 전화번호 from 사원 where 사원명 = '홍길동'그림과 같이 인덱스가 구성되었을 때 위 쿼리는 인덱스를 정상적으로 범위 스캔할 수 없다.
인덱스가 [소속팀+사원명+연령] 순으로 구성되어 있기 때문에사원명='홍길동'을 만족하는 데이터를 찾기 위해서는 인덱스 Full Scan해야만 한다.
인덱스를 이용한 소트 연산 생략
-
인덱스는 이미 정렬되어 있기 때문에 소트 연산을 생략할 수 있다.
ex)
그림과 같이 [장비번호 + 변경일자 + 변경 순번]으로 정렬된 인덱스가 있다고 해보자.
이때 장비 번호와 변경일자를 모두 "="조건 으로 검색할 때 인덱스를 사용하면 결과 집합은 변경순번 순으로 출력된다.
옵티마이저는 이런 속성을 활용해 SQL에 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않는다.
이렇게 ORDER BY가 생략되어 실행 계획에도 SORT ORDER BY 연산이 나타나지 않는 것을 확인할 수 있다. -
인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다에서 말하는인덱스 칼럼은 대개 조건절에 사용한 컬럼을 말한다. -
하지만 조건절이 아닌
ORDER BY또는SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 있다.
ORDER BY절에서 컬럼 가공
- ex)
[장비번호 + 변경일자 + 변경 순번]으로 정렬된 인덱스가 있다고 해보자.
위와 같이 SQL에 정렬 연산을 생략하여도 수직적 탐색을 통해 장비번호가 'C'인 첫 번째 레커드를 찾아 인덱스 리프 블록을 스캔하면 자동으로 [변경일자+변경순번] 순으로 정렬된다.
하지만
위와 같이 SQL을 작성했다면 정렬 연산을 생략할 수 없다. 왜냐하면 인덱스에는 가공하지 않은 상태로 값을 저장했는데 가공한 값 기준으로 정렬해 달라고 요청했기 때문이다.
※order by A || B: A와 B를 결합하여 정렬 ex) A='12'이고 B='34'라면 A||B는 '1234'
SELECT-LIST에서 컬럼 가공
ex)
[장비번호 + 변경일자 + 변경 순번]으로 정렬된 인덱스가 있다고 해보자.
위와 같이 SQL을 작성했다면 정렬 연산을 생략할 수 없다. 인덱스에는 변경순번이 문자열 기준으로 정렬되어 있는데, 이를 숫자값으로 가공하였기 때문이다.
이럴 경우
위와 같이 변경한다면 정렬 연산 없이 최종 변경순번을 찾을 수 있다. 물론 애초에 변경순번 데이터타입을 숫자형으로 설계했다면 이렇게 튜닝할 일이 생기지 않았을 것이다.
자동 형변환
- 자동 형변환 또한 옵티마이저에서는 인덱스 가공이 발생하기 때문에 인덱스 범위 스캔을 사용할 수 없게 된다.
인덱스 확장 기능 사용법
- 인덱스 범위 스캔 방식 이외에도 Index Full Scan, Index Unique Scan, Index Skip Scan, Index Fast Full Scan 등 여러 방식들이 있다.
- 그럼엥도 인덱스는 기본적으로 최적의 Index Range Scan을 목표로 설계해야 하며, 수행 횟수가 적은 SQL을 위해 인덱스를 추가하는 것이 비효율적일 때 다른 스캔 방식을 차선택으로 활용하는 전략이 바람직하다.
Index Range Scan
- 인덱스 범위 스캔
- B-Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식
- 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 필요한 범위만 스캔
- 선두 컬럼을 가공하지 않은 상태로 조건절에 사용하여야 한다.
Index Full Scan
- 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
- 인덱스 선두 컬럼을 조건절에 사용하여야 한다.
- 대개 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.
- Range Scan과 마찬가지로 인덱스 컬럼 순으로 정렬되기 때문에
Sort Order By연산을 생략할 목적으로 사용할 수 있다. - 만약 찾아야할 데이터가 많다면 Table Full Scan보다 불리할 수도 있다.
Index Unique Scan
- 수직적 탐색만으로 데이터를 찾는 스캔 방식
- Unique 인덱스를 "="조건으로 탐색하는 경우에 작동한다.
- Unique 인덱스는 중복값이 존재하지 않기 때문에 해당 인덱스 칼럼을 모두 "="조건으로 검색할 때는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다.
- 만약 Unique 결합 인덱스에 대해 일부 컬럼만으로 검색해도 Index Range Scan이 나타난다.
Index Skip Scan
- 오라클 9i 버전부터 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용할 수 있는 Index Skip Scan 방식을 사용할 수 있다.
- Index Skip Scan은 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고, 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다
Distinct Value: 특정 컬럼에서 중복을 제거한 고유한 값의 개수
- ex)
위와 같이 인덱스가 구성되었을 때
선두 컬럼인 "성별"을 뺀 SQL의 경우 Index Skip Scan이 동작한다.
이 스캔 방식을 유도하거나 방지하고자 할 때 index_ss, no_index_ss 힌트를 사용한다.
첫 번째 리프노드는 남&800<=이다. 해당 블록에는 "남"보다 작은 성별값이 존재할 수도 있기 때문에 액세스한다.
두 번째 리프노드는 남&800<=연봉<=남&1500이다. 이 또한 조건절에 부합하지 않기 때문에 Skip 한다.
세 번째 리프노드는 남&1500<=연봉<=남&5000이다. 이는 조건절에 부합하기 때문에 액세스한다.
네 번째 리프노드는 남&5000<=연봉<=남&8000이다. 이 또한 조건절에 부합하지 않기 때문에 Skip 한다.
....
이런 식으로 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스한다.
- Index Skip Scan은 선두 컬럼에 대한 조건절은 있고, 중간 컬럼에 대한 조건절이 없는 경우에도 사용할 수 있다.
ex)
[업종유형코드+업종코드+기준일자]로 인덱스가 구성되었다고 하자.
선두 컬럼인 업종유형코드에 대한 조건절은 있고 중간 컬럼인 업종코드에 대한 조건절이 없는 SQL을 작성하였다.
이때 Index Range Scan을 사용한다면 업종유형코드='01'인 인덱스 구간을 모두 스캔해야 한다. 하지만 Index Skip Scan을 사용한다면 업종유형코드="01"인 구간에서 '20080501'<=기준일자<='20080531'인 레코드를 포함할 가능성이 있는 리프노드만 골라서 액세스할 수 있다.
Index Fast Full Scan
-
Index Full Scan보다 빠른 스캔 방식
-
논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔
-
관련 힌트는 index_ffs와 no_index_ffs
-
디스크로부터 대량의 인덱스 블록을 읽어야 할 때 효과 발휘
-
속도는 빠르지만, 인덱스 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 정렬되지 않음
-
쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용할 수 있다.
-
Index Range Scan 또는 Index Full Scan과 달리, 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리가 가능
-
Index Full Scan VS Index Fast Full Scan
Index Full Scan의 경우 위와 같은 구조에서 1->2->3->4->5->6->7->8->9->10 리프 블록 순으로 탐색한다.
반면 Index Fast Full Scan은 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 탐색한다.
Multiblock I/O 방식으로 1번 익스텐트에서 1->2->10->3->9 순으로 읽고, 그 다음 2번 익스텐트에서 8->7->4->5->6 순으로 읽는다. -
정리
Index Range Scan Descending
- Index Range Scan과 기본적으로 동일한 스캔 방식
- 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다.
- 내림차순으로 정렬할 때, MAX값을 구할 때 옵티마이저가 알아서 인덱스를 거꾸로 읽는 실행계획을 수립
- index_desc 힌트로 유도 가능
