[SQL] 인덱스(Index) - (1) 미리보는 인덱스 튜닝
sql관련 글들은 친절한SQL튜닝 책을 보며 학습중인 내용들이 작성될 것입니다.
최근에 쿼리 과제를 진행한 적이 있었는데, 발표는 하지 않아 피드백을 받지 못했지만 효율성을 따지지 않고 문제해결에만 집중을 하면서 쿼리를 작성해 성능적으로는 좋지 않은 쿼리를 작성했다는 생각이 들었습니다.
인터넷으로 검색해서 공부하는 방법이 있었지만 무엇부터 공부를 해야 할 지 도저히 감이 잡히지 않아 책을 구매했습니다.
저는 책을 읽으면서 작성했던 쿼리를 다시 살펴보는고, 효율적으로 다시 작성해 보는 방식으로 학습하려고 합니다.
1. 들어가기 전에...
어떤 초등학교를 방문해 '홍길동' 학생을 찾는 방법은 두 가지 입니다.
첫째는, 1학년 1반부터 6학년 맨 마지막 반까지 모든 교실을 돌며 '홍길동' 학생을 찾는 것이고,
두번쨰는 교무실에서 학생 명부를 조회해 홍길동 학생이 있는 교실만 찾아가는 것 입니다.
둘 중 어느 쪽이 빠를까요?
홍길동 학생이 많으면 전자가 빠르고, 몇 안된다면 후자가 빠를 것입니다.
이름으로 학생을 찾는 방문객이 많다면, 학생명부를 이름순으로 정렬해 두면 편리할 것입니다.
이것이 바로 인덱스입니다. '학년-반-번호' 컬럼이 인덱스 ROWID에 해당 됩니다.
| 이름 | 학년-반-번호 |
| ... | ... |
| 홍길동 | 1학년 5반 15번 |
| 홍길동 | 2학년 6반 24번 |
| 홍길동 | 6학년 3반 16번 |
데이터베이스 테이블에서 데이터를 찾는 방법도 두 가지 입니다.
- 테이블 전체를 스캔합니다.
- 인덱스를 이용합니다.
앞선 예에서 모든 교실을 돌며 학생을 찾는 경우가 전자에 속하고, 학생명부를 이용하는 경우가 후자에 속합니다.
테이블 전체 스캔과 관련해서는 튜닝 요소가 많지 않지만, 인덱스와 관련해서는 튜닝 요소가 매우 많고 기법도 다양합니다.
그래서 인덱스는 SQL 튜닝을 공부할 때 가장 먼저 다루어야 할 주제입니다.
인덱스 튜닝의 두 가지 핵심 요소
인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용합니다.
온라인 트랜잭션 처리 시스템에서는 소량 데이터를 주로 검색하므로 인덱스 튜닝이 무엇보다 중요합니다.
세부적인 방법으로는 여러 가지가 있지만, 핵심요소는 크게 두가지로 나뉩니다.
첫 번째는 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것 입니다. 즉, '인덱스 스캔 효율화 튜닝' 입니다.
학생 명부에서 시력이 1.0~1.5인 홍길동 학생을 찾는 경우를 예로 들어보겠습니다.
만약, 학생명부를 이름과 시력순으로 정렬해 두었다면, 소량만 스캔 하면 될 것 입니다.
반면, 학생명부를 시력과 이름순으로 정렬해 두었다면, 똑같이 두 명을 찾는데도 많은 양을 스캔해야 할 것 입니다.
두 번째는 테이블 액세스 횟수를 줄이는 것 입니다. 인덱스 스캔 후 테이블 레코드를 엑세스 할 때 랜덤 I/O방식을 사용하므로 이를 '랜덤 액세스 최소화 튜닝' 이라고 합니다.
위의 예시를 다시 생각해 봅시다.
시력이 1.0~ 1.5인 학생이 50명이고, 이름이 '홍길동'인 학생은 5명 입니다. 시력이 1.0~1.5인 홍길동 학생은 두 명 입니다.
이름과 시력순으로 정렬한 명부가 있다면 가장 좋겠지만, 만약 이름만으로 정렬한 학생명부와 시력만으로 정렬한 학생명부가 따로 하나씩 있다면 둘 중 어느 쪽을 사용해야 효과적일까요?
당연히, 이름으로 정렬한 학생명부입니다. 교실 찾아가는 횟수를 줄일 수 있기 때문입니다.
시력이 1.0~1.5인 홍길동 학생은 두 명이지만, 어떤 학생명부를 사용하느냐에 따라 교실 방문 횟수는 다릅니다.
인덱스 스캔과 랜덤 액세스를 화살표로 표시하였습니다.
인덱스 스캔 효율화 튜닝과 랜덤 액세스 최소화 튜닝 둘 다 중요하지만, 더 중요한 하나를 고른다면 랜덤 액세스 최소화 튜닝입니다.
성능에 미치는 영향이 더 크기 때문입니다.
인덱스 구조를 설명하기 전에, 인덱스 튜닝 핵심요소 부터 간단하게 살펴보았습니다.
이는 튜닝의 핵심에 대해서 설명하기 위함이었습니다.
이 책의 가장 중요한 결론이라고 소개된 부분은 'SQL 튜닝은 랜덤 I/O와의 전쟁'입니다.
SQL 튜닝은 랜덤 I/O와의 전쟁
데이터베이스 성능이 느린 이유는 디스크 I/O 때문입니다. 읽어야 할 데이터량이 많고, 그 과정에 디스크 I/O가 많이 발생할 때 느립니다.
인덱스를 많이 사용하는 OLTP 시스템이라면 디스크 I/O 중에서도 랜덤 I/O가 특히 중요합니다.
성능을 위해 DBMS가 제공하는 많은 기능이 느린 랜덤 I/O를 극복하기 위해 개발됐다는 것을 앞으로 작성할 글들을 읽은동안 확인할 수 있을 것 입니다.
IOT, 클러스터, 파티션에서부터 테이블 Prefetch, Batch I/O 처럼 겉으로 잘 드러나지 않는 숨은 기능들까지 모두가 본질적으로 랜덤 I/O를 줄이는 데에 있습니다.
조인 메소드 중 가장 일반적으로 사용하는 NL 조인이 대량 데이터 조인할 때 느린 이유도 랜덤 I/O 때문입니다.
그래서 소트머지 조인과 해시 조인이 개발됐으므로 이들 조인 메소드도 결국 느린 랜덤 I/O를 극복하기 위해서 개발된 기능입니다.
(NL Join, NL조인은 프로그램에서 사용하는 중첩된 반복문과 유사한 방식으로 조인을 수행합니다.)
2. 인덱스 구조
인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트입니다.
모든 책 뒤쪽에 있는 색인과 같은 역할을 합니다.
책에서 색인 없이 '인덱스'를 학습하려고 한다면, 첫 페이지부터 마지막 페이지까지 다 뒤져야 합니다.
만약, 색인을 이용한다면, 인덱스가 가리키는 페이지들만 찾으면 됩니다.
데이터베이스에서도 인덱스 없이 데이터를 검색하려면, 테이블을 처음부터 끝까지 모두 읽어야 합니다.
반면, 인덱스를 이용하면 일부만 읽고 멈출 수 있습니다. 즉, 범위 스캔(Range Scan)이 가능합니다. 범위 스캔이 가능한 이유는 인덱스가 정렬돼 있기 때문입니다.
DBMS는 일반적으로 B*Tree인덱스를 사용합니다.
(B*Tree : Balanced Tree
binary search 에서 생긴 한계( 한쪽에만 (branch) 몰릴 수 있다는 점)를 극복하고자 나온 자료구조
=> 스스로 균형을 찾는다!
Balanced Tree는 구조적으로 Binary search Tree 와 비슷하지만, 데이터 높이(층)을 자동으로 바로잡아주는 기능이 있습니다.
이는, insert, delete의 시간을 희생하면서 search 시간을 줄일 수 있습니다.)
고객 테이블에 고객명 컬럼 기준으로 만든 B*Tree 인덱스 구조는 다음과 같습니다.
나무(Tree)를 거꾸로 뒤집은 모양이어서 뿌리(루트, Root)가 위쪽에 있고, 가지(브랜치, Branch)를 거쳐 맨 아래에 잎사귀(리프, Reaf)가 있습니다.
루트와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소값을 갖습니다.
키값은 하위 블록에 저장된 키값의 범위를 나타냅니다.
루트와 브랜치 블록에는 키값을 갖지 않는 특별한 레코드가 하나 있습니다. 이를 'LMC(Leftmost Child)'의 줄임말입니다. LMC는 자식 노드 중 가작 왼쪽 끝에 위치한 블록을 가리킵니다.
LMC가 가리키는 주소로 찾아간 블록에는 키 값을 가진 첫 번째 레코드보다 작거나 같은 레코드가 저장돼 있습니다.
리프 블록에 저장된 각 레코드는 키값 순으로 정렬돼 있을 뿐만 아니라 테이블 레코드를 가지키는 주소값, 즉 ROWID를 가집니다.
인덱스 키값이 같으면 ROWID순으로 정렬됩니다. 인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위함입니다.
- ROWID = 데이터 블록 주소 + 로우 번호
- 데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
- 블록 번호 : 데이터파일 내에서 부여한 상대적 순번
- 로우 번호 : 블록 내 순번
인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나눌 수 있습니다.
- 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정
- 수평적 탐색 : 데이터를 찾는 과정
3. 인덱스 수직적 탐색
정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정입니다.
즉, 인덱스 스캔 시작지점을 찾는 과정입니다.
인덱스 수직적 탐색은 루트(Root) 블록에서부터 시작합니다. 루트를 포함해 브랜치(Branch) 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 갖습니다. 루트에서 시작하여 리프(Leaf) 블록까지 수직적 탐색이 가능한 이유입니다.
수직적 탐색 과정에 찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 블록으로 이동합니다.
(위의 이재희 를 찾는 과정과 같습니다.)
인덱스를 수직적으로 탐색할 때, 루트를 포함한 브랜치 블록은 등산 푯말과 같은 역할을 합니다.
'조건을 만족하는 첫 번째 레코드'가 목표지점입니다.
푯말을 만날 때 마다 어느쪽으로 가면 목표 레코드를 만날 수 있는지 확인하면서 이동합니다.
푯말이 알려주는 대로 따라가다 보면 '조건을 만족하는 첫 번째 레코드'를 만날 수 있을 것 입니다.
4. 인덱스 수평적 탐색
수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 안 나타날 때까지 인덱스 리프 블록을 수평적으로 스캔합니다.
인덱스에서 본격적으로 데이터를 찾는 과정입니다.
인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖습니다. 즉, 양방향 연결 리스트 구조입니다.
좌에서 우로, 또는 우에서 좌로 수평적 탐색이 가능한 이유입니다.
인덱스를 수평적으로 탐색하는 이유는 첫 째, 조건절을 만족하는 데이터를 모두 찾기 위해서고 둘째, ROWID를 얻기 위해서입니다.
필요한 컬럼을 인덱스가 모두 갖고 있어 인덱스만 스캔하고 끝나는 경우도 있지만, 일반적으로 인덱스를 스캔하고서 테이블도 액세스합니다.
이 때 ROWID가 필요합니다.
5. 결합 인덱스 구조와 탐색
두 개 이상 컬럼을 결합해서 인덱스를 만들 수도 있습니다.
고객 테이블에 성별과 고객명 기준으로 만든 인덱스 구조는 아래와 같습니다.
남자 '이재희' 고객을 찾아보겠습니다.
루트 블록을 스캔하다 보면 찾고자 하는 값보다 큰 첫 번째 레코드를 만나게 됩니다. 성별이 '남' 이면서 '최'씨 성을 가진 레코드입니다.
그 레코드가 가리키는 하위 블록으로 내려가면 첫 번째 남자 '이재희'고객이 없으므로 하위블록, 즉 왼쪽 브랜치 블록으로 이동하게 됩니다.
같은 방식으로 계속 내려가다 보면 리프 블록에 도달하면, 거기서부터 남자 '이재희'고객을 찾으면 됩니다.
리프 블록은 인덱스 키값 순으로 정렬돼 있으므로 스캔하다가 '남' & '이재희' 보다 큰 값을 만나면 거기서 멈춥니다.
수직적 탐색을 거쳐서 찾은 인덱스 스캔 시작점이 성별 = '남' 인 첫 번째 레코드가 아니라, 성별 = '남' 이면서 고객명 = '이재희' 인 레코드라는 사실을 기억해야 한다.
인덱스 컬럼 순서를 바꿔보면 다음과 같다.
중요한 점은, 인덱스를 [고객명 +성별] 로 구성하든, [성별 + 고객명] 으로 구성하든 간에 읽은 인덱스 블록 개수가 똑같다는 것입니다.
인덱스 선두 컬럼을 모두 '=' 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 블록 I/O 개수가 같으므로 성능도 똑같습니다.
다음 글은 인덱스 기본 사용법에 대해 작성하겠습니다.