친절한 SQL 튜닝_2장.인덱스 기본

 

2장. 인덱스 기본

 

2.1 인덱스 구조 및 탐색

 

2.1.1 미리 보는 인덱스 튜닝

 

데이터를 찾는 두 가지 방법

 

1) 테이블 전체를 스캔한다.

 

2) 인덱스를 이용한다.

 

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

 

첫 번째는 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것이다. 즉. ‘인덱스 스캔 효율화 튜닝’이다.

 

두 번째 핵심요소는 테이블 액세스 횟수를 줄이는 것이다. 인덱스 스캔 후 테이블 레코드를 액세스할 때 랜덤 I/O 방식을 사용하므로 이를 ‘랜덤 액세스 최소화 튜닝’이라고 한다.

 

SQL 튜닝은 랜덤 I/O와의 전쟁

 

데이터베이스 성능이 느린 이유는 디스크 I/O 때문이다. 읽어야 할 데이터량이 많고, 그 과정에 디스크 I/O가 많이 발생할 때 느리다. 인덱스를 많이 사용하는 OLTP 시스템이라면 디스크 I/O 중에서도 랜덤 I/O가 특히 중요하다.

 

 

2.1.2 인덱스 구조

 

DBMS는 일반적으로 B*Tree 인덱스를 사용한다. 나무(Tree)를 거꾸로 뒤집은 모양이어서 뿌리(루트, Root)가 위쪽에 있고, 가지(브랜치, Branch)를 거쳐 맨 아래에 잎사귀(리프, Leaf)가 있다.

 

루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖는다. 키값은 하위 블록에 저장된 키값의 범위를 나타낸다.

 

루트와 브랜치 블록에는 키값을 갖지 않는 특별한 레코드가 하나 있다. 이를 ‘LMC’라고 하며 ‘Leftmost Child’의 줄임말이다. LMC는 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킨다.

 

리프 블록에 저장된 각 레코드는 키값 순으로 정렬돼 있을 뿐만 아니라 테이블 레코드를 가리키는 주소값, 즉 ROWID를 갖는다. 인덱스 키값이 같으면 ROWID 순으로 정렬된다. 인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위해서다. ROWID는 아래와 같이 데이터 블록(DBA, Data Block Address)와 로우 번호로 구성되므로 이 값을 알면 테이블 레코드를 찾아갈 수 있다.

 

- ROWID = 테이블 블록 주소 + 로우 번호

 

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

 

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

 

- 로우 번호 : 블록 내 순번

 

인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나눌 수 있다.

 

- 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정

 

- 수평적 탐색 : 데이터를 찾는 과정

 

 

2.1.3 인덱스 수직적 탐색

 

정렬된 익덱스 레코드 중 조건에 만족하는 첫 번째 레코드를 찾는 과정이다. 즉, 인덱스 스캔 시작지점을 찾는 과정이다.

 

인덱스 수직점 탐색은 루트(Root) 블록에서부터 시작한다. 루트를 포함해 브랜치(Branch) 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 갖는다. 루트에서 시작해 리프(Leaf) 블록까지 수직적 탐색이 가능한 이유다.

 

수직적 탐색은 ‘조건을 만족하는 레코드’를 찾는 과정이 아니라 ‘조건을 만족하는 첫 번째 레코드’를 찾는 과정임을 반드시 기억하자.

 

 

2.1.4 인덱스 수평적 탐색

 

수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔한다. 인덱스에서 본격적으로 데이터를 찾는 과정이다. 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖는다. 즉, 양방향 연결 리스트(double linked list) 구조다. 좌에서 우로, 또는 우에서 좌로 수평적 탐색이 가능한 이유다.

 

인덱스를 수평적으로 탐색하는 이유는 첫째, 조건절에 만족하는 데이터를 모두 찾기 위해서고 둘째, ROWID를 얻기 위해서다. 필요한 컬럼을 인덱스가 모두 갖고 있어 인덱스만 스캔하고 끝나는 경우도 있지만, 일반적으로 인덱스를 스캔하고서 테이블도 액세스한다. 이 때 ROWID가 필요하다.

 

 

2.1.5 결합 인덱스 구조와 탐색

 

두 개 이상 컬럼을 결합해서 인덱스를 만들 수도 있다. 고객 테이블에 성별과 고객명 기준으로 만든 인덱스 구조에서 인덱스를 [고객명 + 성별]로 구성하든, [성별 + 고객명]으로 구성하든 읽는 인덱스 블록 개수가 똑같다는 사실이다. 인덱스를 어떻게 구성하든 블록 I/O 개수가 같으므로 성능도 똑같다.

 

 

 

2.2 인덱스 기본 사용법

 

2.2.1 인덱스를 사용한다는 것

 

인덱스 컬럼(정확히 말하면, 선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다. ‘인덱스를 정상적으로 사용한다’는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미한다. 즉 리프 블록 일부만 스캔하는 Index Range Scan을 의미한다.

 

인덱스 컬럼을 가공해도 인덱스를 사용할 수는 있지만, 스캔 시작점을 찾을 수 없고 멈출 수도 없어 리프 블록 전체를 스캔해야만 한다. 즉, 일부가 아닌 전체를 스캔하는 Index Full Scan 방식으로 작동한다.

 

 

2.2.2 인덱스를 Range Scan 할 수 없는 이유

 

“인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용(Range Scan)할 수 없다.”

 

인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다. Index Range Scan에서 ‘Range’는 범위를 의미한다. 즉, Index Range Scan은 인덱스에서 일정 범위를 스캔한다는 뜻이다. 일정 범위를 스캔하려면 ‘시작점’과 ‘끝지점‘이 있어야 한다.

 

 

2.2.3 더 중요한 인덱스 사용 조건

 

인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다는 사실이다. 가공하지 않은 상태로 말이다.

 

인덱스를 Range Scan 하려면 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 한다. 반대로 말해, 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능하다. 문제는, 인덱스 Range Scan 한다고 해서 항상 성능이 좋은 건 아니라는 사실이다.

 

 

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

 

인덱스를 Range Scan 할 수 있는 이유는 데이터가 정령돼 있기 때문이다. 찾고자 하는 데이터가 정렬된 상태로 서로 모여있기 때문에 전체가 아닌 일정 부분만 읽다가 멈출 수 있다. 인덱스 컬럼을 가공해도 인덱스를 사용할 수 있지만. 찾고자 하는 데이터가 전체 구간(테이블 전체 레코드 또는 가공하지 않은 인덱스 선두 컬럼에 의해 선택된 전체 레코드)에 흩어져 있기 때문에 Range Scan이 불가능하거나 비효율이 발생한다.

 

테이블과 달리 인덱스는 정렬돼 있다. 우리가 인덱스를 사용하는 이유다. 인덱스가 정령돼 있기 때문에 Range Scan이 가능하고, 지금부터 설명하고자 하는 소트 연산 생략 효과도 부수적으로 얻게 된다.

 

PK를 [장비번호 + 변경일자 + 변경순번] 순으로 구성한 상태변경이력 테이블에 경우 PK 인덱스에서 장비번호, 변경일자가 같은 레코드는 변경순번 순으로 정렬돼있다. 아래와 같이 장비번호와 변경일자를 모두 ‘=’ 조건으로 검색할 대 PK 인덱스를 사용하면 결과집합은 변경순번 순으로 출력된다.

 

SELECT *
  FROM 상태변경이력
 WHERE 장비번호 = ‘C’
   AND 변경일자 = ‘20180316’

 

옵티마이저는 이런 속성을 활용해 아래와 같이 SQL에 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않는다. PK 인덱스를 스캔하면서 출력한 결과집합은 어차피 변경순번 순으로 정렬되기 대문이다.

 

SELECT *
  FROM 상태변경이력
 WHERE 장비번호 = ‘C’
   AND 변경일자 = ‘20180316’
 ORDER BY 변경순번

 

만약 정렬 연산을 생략할 수 있게 인덱스가 구성돼 있지 않다면, SORT ORDER BY 연산 단계가 추가된다.

 

내림차순(DESC) 정렬에도 인덱스를 활용할 수 있다. 오름차순(ASC) 정렬일 때는 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색한 후 우측으로 수평적 탐색을 한다. 내림차순 정렬일 때는 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 수평적 탐색을 한다.

 

 

2.2.5 ORDER BY 절에서 컬럼 가공

 

모든 SQL 튜닝 책이 다루는 명제 “인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다”에서 말하는 “인덱스 컬럼”은 대게 조건절에 사용한 컬럼을 말한다. 그런데 조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 종종 있다.

 

개발자가 SQL을 아래와 같이 작성했다면, 정렬 연산을 생략할 수 있을까? 당연히 생략할 수 없다. 인덱스에는 가공하지 않은 상태로 값을 저장했는데, 가공한 값 기준으로 정렬해 달라고 요청했기 때문이다.

 

SELECT *
  FROM 상태변경이력
 WHERE 장비번호 = ‘C’
 ORDER BY 변경일자 || 변경수번

2.2.6 SELECT-LIST에서 칼럼 가공

 

인덱스를 [장비번호 + 변경일자 + 변경순번] 순으로 구성하면, 아래와 같이 변경순번 최소값을 구할 때도 옵티마이저는 정렬 연산을 따로 수행하지 않는다. 수직적 탐색을 통해 조건을 만족하는 가장 왼쪽 지점으로 내려가서 첫 번째 읽는 레코드가 바로 최소값이기 때문이다.

 

SELECT MIN(변경순번)
  FROM 상태변경이력
 WHERE 장비번호 = ‘C’
   AND 변경일자 = ‘20180316’

 

아래와 같이 변경순번 최대값을 구할 때는 어떨까? 마찬가지로 정렬 연산을 수행하지 않는다. 최소값을 찾아 수직적 탐색할 때 왼쪽으로 내려갔다면, 최대값을 찾을 때는 오른쪽으로 내려가는 점만 다르다. 수직적 탐색을 통해 조건을 만족하는 가장 오른쪽 지점으로 내려가서 첫 번째 읽는 레코드가 바로 최대값이다.

 

SELECT MAX(변경순번)
  FROM 상태변경이력
 WHERE 장비번호 = ‘C’
   AND 변경일자 = ‘20180316’

 

인덱스를 이용해 이처럼 정렬 연산 없이 최소 또는 최대값을 빠르게 찾을 때 아래와 같은 실행계획이 나타난다. 실행방식은 실행계획에 표현돼 있는 그대로다. 인덱스 리프 블록의 왼쪽(MIN) 또는 오른쪽(MAX)에서 레코드 하나 (FIRST ROW)만 읽고 멈춘다.

 

 

2.2.7 자동 형변환

 

고객 테이블에 생년월일이 선두 컬럼인 인덱스가 있다고 하자. 아래 SQL은 생년월일 컬럼을 조건절에서 가공하지 않았는데도 옵티마이저는 테이블 전체 스캔을 선택했다. 실행계획 아래쪽 조건절 정보를 보면 그 이유를 쉽게 알 수 있다.

 

SELECT *
  FROM 고객
 WHERE 생년월일 = 19821225

 

옵티마이저가 SQL을 아래와 같이 변환했고, 결과적으로 인덱스 컬럼이 가공됐기 때문에 인덱스를 Range Scan 할 수 없게 된 것이다.

 

SELECT *
  FROM 고객
 WHERE TO_NUMBER(생년월일) = 19821225

 

이는 고객 테이블 생년월일 컬럼이 문자형인데 조건절 비교값을 숫자형으로 표현했기 때문에 나타난 현상이다. 각 조건절에서 양쪽 값의 데이터 타입이 서로 다르면 값을 비교할 수 없다. 그럴 때 타입 체크를 엄격히 함으로써 컴파일 시점에 에러를 내는 DBMS가 있는가 하면, 자동으로 형변환 처리해주는 DBMS도 있다. 오라클은 후자에 속한다.

 

 

 

2.3 인덱스 확장기능 사용법

 

2.3.1 Index Range Scan

 

Index Range Scan은 B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식이다. 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 ‘필요한 범위(Range)만’ 스캔한다.

 

인덱스를 Range Scan 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 한다. 반대로, 선두 컬럼을 가공하지 않은 상태로 조건절에 사용하면 Index Range Scan은 무조건 가능하다. 실행계획을 보고 ‘인덱스 잘 타니깐 성능도 OK’라고 생각하면 안 되는 이유가 바로 여기에 있다. 성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄일 수 있느냐로 결정된다.

 

 

2.3.2 Index Full Scan

 

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

 

CREATE INDEX EMP_ENAME_SAL_IDX ON EMP(ENAME, SAL);
 
SELECT *
  FROM EMP
 WHERE SAL > 2000
 ORDER BY BY ENAME;

 

Index Full Scan은 대개 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다. 위 SQL에서 인덱스 선두 컬럼인 ENAME이 조건절에 없으므로 Index Range Scan은 불가능하다. 뒤쪽이긴 하지만 SAL 컬럼이 인덱스에 있으므로 Index Full Scan을 통해 SAL이 2000보다 큰 레코드를 찾을 수 있다.

 

 

2.3.3 Index Unique Scan

 

Index Unique Scan은 수직적 탐색만으로 데이터를 찾는 스캔 방식으로서, Unique 인덱스를 ‘=’ 조건으로 탐색하는 경우에 작동한다.

 

Unique 인덱스가 존재하는 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성을 관리해 준다. 따라서 해당 인덱스 키 컬럼을 모두 ‘=’ 조건으로 검색할 때는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다.

 

 

2.3.4 Index Skip Scan

 

인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다. Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면, Index Full Scan을 사용하기도 한다.

 

오라클은 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식을 9i 버전에서 선보였는데, Index Skip Scan이 바로 그것이다. 이 스캔 방식은 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼이 Distinct Value 개수가 많을 때 유용하다.

 

Index Skip Scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 ‘가능성이 있는’ 리프 블록만 골라서 액세스하는 스캔 방식이다.

 

 

2.3.5 Index Fast Full Scan

 

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

 

Index Full Scan

 

1.인덱스 구조를 따라 스캔

 

2.결과집합 순서 보장

 

3.Single Block I/O

 

4.(파티션 돼 있지 않다면) 병렬스캔 불가

 

5.인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능

 

Index Fast Full Scan

 

1.세그먼트 전체를 스캔

 

2.결과집합 순서 보장 안됨

 

3.Multiblock I/O

 

4.병렬스캔 가능

 

5.인덱스에 포함된 컬럼으로만 조회할 때 사용 가능

 

 

2.3.6 Index Range Scan Descending

 

Index Range Scan과 기본적으로 동일한 스캔 방식이다. 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.

 

출처 : 친절한 SQL 튜닝 - 조시형 지음