느낀점 :

이번 장을 통해서 인덱스에 대해서 조금 더 친해진 것 같아 기쁘다. 인덱스를 잘못 걸었다, 인덱스를 걸어야 한다 이런 말들을 주변에서 많이 들었는데 이제는 어떤 의미로 말하는지 알 것 같다.

거의 대부분의 방법들이 하나의 유일한 해결책은 없겠지만 인덱스 세계에서도 마찬가지였다. 상황에 맞게 적절한 방법을 사용하는 것. 그럼에도 선행조건, 예를 들어, 인덱스를 조작하면 인덱스 범위 탐색을 할 수 없다는 것. 하지만 선행조건에서 조건을 걸고 뒤에서 조작하는 경우에는 사용할 수 있었다. 이처럼 하나의 방법을 사용할 때 조건을 정확하게 알고 사용하는 것이 중요하겠다는 걸 알았다.

이번 장을 배우면서 MySQL과 다른 점들도 있었지만 비슷한 점들도 많아 반가웠다. B*Tree 같은 경우 다시 복습하게 되어 좋았고 이번 기회를 통해서 개념을 명확하게 알게 되었다. 그리고 수직적 탐색, 수평적 탐색에 대해서 자세히 알게 되었고, 이걸 기반으로 인덱스를 탐색하는 방법들에 대해서 배웠는데 유익했고 계속해서 기억하고 싶다.

인덱스 범위 스캔은 수직적 탐색과 수평적 탐색을 같이, 인덱스 풀 스캔은 수평적 탐색만 사용 등등 이런 방법들을 알고 있는 것이 중요할 것 같다. 그리고 LMC에 대해서 처음 알게 되어 부끄럽지만 이번 기회를 통해서 배워간다.

학습 :

CH02 : 인덱스 기본

2.1 인덱스 구조 및 탐색

2.1.1 미리 보는 인덱스 튜닝

인덱스 구조를 설명하는 핵심 원리를 간단하게 살펴보려고 한다.
인덱스 구조를 바라보는 시각을 SQL 튜닝에 맞추기 위해서다.

데이터를 찾는 두 가지 방법

ROWID(오라클 데이터 주소)

어떤 초등학교를 방문해 '홍길동' 학생을 찾는 방법은 두 가지다. 첫째는, 1학년 1반부터 6학년 맨 마지막 반까지 모든 교실을 돌며 홍길동 학생을 찾는 것이다. 둘째는, 교무실에서 학생 명부를 조회해 홍길동 학생이 있는 교실을 찾아가는 것이다. 둘 중 어느 쪽이 빠를까? 홍길동 학생이 많다면 전자가 빠르고, 몇 안 되면 후자가 빠르다.

데이터베이스 테이블에서 데이터를 찾는 방법도 아래 두 가지다. 수십 년에 걸쳐 DBMS가 발전해 왔는데도 이 두 방법에서 크게 벗어나지 못하고 있다.

• 테이블 전체를 스캔한다.
• 인덱스를 이용한다.

모든 교실을 돌며 학생을 찾는 경우가 전자에 속하고, 이름순으로 정렬한 학생명부를 이용하는 경우가 후자에 속한다. 테이블 전체 스캔과 관련해서는 튜닝 요소가 많지 않지만, 인덱스와 관련해서는 튜닝 요소가 매우 많고 기법도 다양하다. 그래서 인덱스는 SQL 튜닝을 공부할 때 가장 먼저 다루어야 할 주제다.

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

인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용한다. 온라인 트랜잭션 처리Online Transcation Processing, 이하 OLTP 시스템에서는 소량 데이터를 주소 검색하므로 인덱스 튜닝이 무엇보다 중요하다.

세부적인 인덱스 튜닝 방법으로 여러 가지가 있지만, 핵심 요소는 크게 두 가지로 나뉜다. 첫 번째는 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것이다. 즉, '인덱스 스캔 효율화 튜닝'이다.

학생명부에서 시력이 1.0 ~ 1.5인 홍길동 학생을 찾는 경우를 예로 들어보자. 학생명부를 이름과 시력순으로 정렬해 두었다면, 소량만 스캔하면 된다. 반면, 학생명부를 시력과 이름순으로 정렬해 두었다면, 똑같이 두 명을 찾는데도 많은 양을 스캔해야 한다.

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

인덱스 스캔 효율화 튜닝과 랜덤 엑세스 최소화 튜닝 둘 다 중요하지만, 더 중요한 하나를 고른다면 랜덤 액세스 최소화 튜닝이다. 성능에 미치는 영향이 더 크기 때문이다.

인덱스 설명을 시작하는 초반에 본서의 가장 중요한 결론부터 전달하려고 한다. SQL 튜닝은 랜덤 I/O와의 전쟁이다.

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

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

2.1.2 인덱스 구조

인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 엑세스하기 위해 사용하는 오브젝트다.
데이터베이스에서도 인덱스 없이 데이터를 검색하려면, 테이블을 처음부터 끝까지 모두 읽어야 한다. 반면, 인덱스를 이용하면 일부만 읽고 멈출 수 있다. 즉, 범위 스캔Range Scan이 가능하다. 범위 스캔이 가능한 이유는 인덱스가 정렬돼 있기 때문이다.

DBMS는 일반적으로 B * Tree 인덱스를 사용한다. 루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖는다. 키값은 하위 블록에 저장된 키값의 범위를 나타낸다. 루트와 브랜치 블록에는 키값을 갖지 않는 특별한 레코드가 하나 있다. 이를 'LMC'라고 하며 'Leftmost Child'의 줄임말이다. LMC는 자식 노드 중 가장 왼쪽 끝에 위치한 블록을 가리킨다. 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 개수가 같으므로 성능도 똑같다.

Balanced의 의미

delete 작업 대문에 인덱스가 불균형unbalanceed 상태에 놓일 수 있다고 설명한 자료들을 볼 수 있다. 리프 노드에 비해 루트 블록과의 거리가 더 멀거나 가까운 리프 노드가 생길 수 있다는 설명인데, B Tree 인덱스에서 이런 현상을 절대 발새하지 않는다. B Tree 인덱스의 'B'가 'Balanced'의 약자임을 기억하기 바란다.

'Balanced'는 어떤 값으로 탐색하더라도 인덱스 루트에서 리프 블록에 도달하기까지 읽는 블록 수가 같음을 의미한다. 즉, 루르로부터 모든 리프 블록까지의 높이height는 항상 같다.

2.2 인덱스 기본 사용법

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

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

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

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

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

인덱스를 정상적으로 사용한다는 표현은 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미한다. 단, OR 또는 IN 조건절은 옵티마이저의 쿼리변환 기능을 통해 Index Range Scan으로 처리되기도 한다.

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

조건절에서 인덱스 칼럼을 가공하면 인덱스를 정상적으로 사용할 수 없다는 사실을 이해했다. 그런데 인덱스를 정상적으로 사용하는 데 있어 더 중요한 선행조건을 모르는 분이 의외로 많다.

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

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

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

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

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

2.2.5 OBER BY 절에서 컬럼 가공

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

2.3 인덱스 확장기능 사용법

2.3.1 Index Range Scan

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

2.3.2 Index Full Scan

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

Index Full Scan의 효용성

인덱스 선두 컬럼이 조건절에 없으면 옵티마이저는 먼저 Table Full Scan을 고려한다. 그런데 대용량 테이블이어서 Table Full Scan에 따른 부담이 크다면, 옵티마이저는 인덱스 활용을 다시 고려하지 않을 수 없다.

데이터 저장공간은 '가로 X 세로'즉, '컬럼 길이 X 레코드 수'에 의해 결정되므로 인덱스가 차지하는 면적은 테이블보다 훨씬 적다. 인덱스를 Range Scan 할 수 없을 때, 테이블 전체를 스캔하기보다 인덱스 전체를 스캔하면 어떨까? 만약 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 아주 일부만 테이블을 엑세스하는 상황이라면, 면적이 큰 테이블보다 인덱스를 스캔하는 쪽이 유리하다.

그럴 때 옵티마이저는 Index Full Scan 방식을 선택한다.

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

인덱스를 Full Scan하면 Range Scan 과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬된다. 따라서 Sort Order By 연산을 생략할 목적으로 사용할 수도 있다. 이때는 차선책이 아니라 옵티마이저가 전력적으로 선택한 경우에 해당한다.

2.3.3 Index Unique Scan

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

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

또한 Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan이 나타난다.

2.3.4 Index Skip Scan

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

이 스캔 방식은 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다.

index range scan이 불가능하거나 효율적이지 못한 상황에서 index skip scan이 종종 빛을 발한다. 부분범위 처리가 가능하다면 index full scan이 도움이 되기도 한다. 하지만 이들 스캔 방식이 최선책일 수는 없다. 인덱스는 기본적으로 최적의 index range scan을 목표로 설계해야 하며, 수행 횟수가 적은 sql을 위해 인덱스를 추가하는 것이 비효율적일 때 이들 스캔 방식을 차선책으로 활용하는 전략이 바람직하다.

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 fast full scan은 multiblock io 방식을 사용하므로 디스크로부터 대량의 인덱스 블록을 읽어야 할 대 큰 효과를 발휘한다. 속도는 빠르지만, 인덱스 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과집합이 인덱스 키 순서대로 정렬되지 않는다. 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 대만 사용할 수 있다는 점도 기억할 필요가 있다.

2.3.6 Index Range Scan Descending

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