데이터베이스 인덱스

 

인덱스가 필요한 이유 

 

 

사용자가 데이터를 읽을때  디스크에 저장되어있는 데이터를 읽게됨

하드디스크 원형 플레이트로 구성 ,

원형플레이트는 논리적인 트랙으로 나뉨

트랙은 다시 몇개의 섹터로 나뉘게됨

 

하드디스크 ->원형 플레이트 ->트랙 ->섹터

 

하드 디스크에서 저장된 데이터를 읽어오는데  걸리는 시간은 모터(moter)에 의해 분당 회전하는 속도(RPM) 데이터를

읽으때 액세스 암이 이동하는 시간(latency time) 주기억장치로 읽어오는 시간(transfer time)에 영향을 받음 따라서 디스크 입출력 시간을 액세스 시간(access time)이라고 하는데 

액세스시간 = 탐색시간(seek time : 액세스 헤드를 트랙에 이동시킨시간) 

                   +회전지연시간(rotational latency tiem, 섹터가 액세스 헤드에 접근시간)

                    + 데이터 전송시간(data transfer time ,데이터를 주기억장치로 읽어온시간)

 

그러나 디스크에있는 데이터를 읽게 되는경우 느리기때문에 자주 사용하는 데이터는 메모리에 올라가있으니

메모리를 이용합니다.

 

메모리에 올라가있는 데이터를 읽음 매우빠름

 

그렇지만 디스크에 메모리에 없는경우 디스크에서 데이터를 찾아야할때

 

 

Full Table Scan : 원하는 데이터를 찾아 원하는 데이터를 찾을때까지 모든데이터를 확인하게됨

문제점 : 대용량일경우 너무 느림

따라서 인덱스를 좋게설계하여 인덱스를 활용하여 빠르게 데이터를 찾고자함

 

인덱스란 무엇인가?

인덱스 : 인덱스 데이터 레코드를 빠르게 접근하기 위해 <키,포인터> 쌍으로 구성되는 데이터 구조이기에 데이터를 효율적으로 검색할수 있음

 

인덱스의 구조 : 

위의 구조만 보면 인덱스는 해시를 이용할것같지만 실제로는 그렇지않다 HASH를 이용하면

WHERE 절을 이용한 조건절을 이용할대 HASH를 이용할수없음

 

B-트리 인덱스

B-tree는 데이터의 검색시간을 단축하기위한 자료구조 각노드는 키 값과 포인터를 가지며 키값은 오름차순으로 저장되어 있다. B-tre는 키값이 새로 추가되거나 삭제될 경우 동적으로 노드를 분할하거나 통합하여 항상 균형 상태를 유지하기때문에 이점을 기억해야한다.

 

ROOT BLOCK : BRANCH 블록들의 최상위 위치에 존재

BRANCH BLOCK : 수직적으로 탐색 자식노들이 여러개니까 BRANCH DEPTH가 더 많을수있음

LEAF BLOCK:  실제 데이터가 위치, LEAF BLOCK에 위치하게될경우 RANGE SCAN으로 데이터를 찾음

 

1단계, 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동

2단계, 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동

3단계, 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동

예를 들어 37의 값을 찾고 싶다면??

37을 찾고자 한다면 루트블록에서  50보다 작으므로 왼쪽 포인터로 이동한다.

37는 왼쪽 브랜치 블록의 11과 40 사이의 값이므로 가운데 포인터로 이동한다.

이동한 결과 해당 블록이 리프블록이므로 37이 블록 내에서 존재하는지 검색한다. (RANGE SCAN)

출처: https://slenderankle.tistory.com/284 [SLENDER ANKLES's 개발블로그]

따라서 B-tree는 데이터를 검색할 때 특유의 트리구조를 이용하기 때문에 한 번 검색할 때마다 검색 대상이 줄어 접근 시간이 적게 걸린다. 100만개의 튜플을 가진 데이터라도 3~4번의 디스크 블록을 읽으면 찾을수 있다. 이러한 특징 때문에 주요 DBMS에서는 인덱스의 기본 주고로 B-tree를 많이 사용한다. 하지만 데이터의 변경이나 추가가 잦을 경우 B-tree의 모양을 유지하기 위해 노드의 분할 및 이동이 자주 발생하는 문제가 있다.

 

오라클 B-tree 인덱스

 

위와같은 테이블이 있을때 8번 을 찾는경우 key의 1은 1<=8<6 아니므로 리프노드의 두번째 블록을 찾아간후

6<=8이므로  Index Block2에서 찾으려는 값이 rowid가 3-1임을 확인하여 3-1은 세번째 블록의 첫번째 행을 의미 하므로 테이블의 세번째 블록에 접근하여 원하는 데이터를 가져온다.

 인덱스(Index)의 특징 

• 인덱스는 테이블에서 한개 이상의 속성을 이용하여 생성,
• 빠른 검색과 함께 효율적인 레코드 접근이 가능
• 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지
• 저장된 값들은 테이블의 부분집합이 된다.
• 일반적으로 B-tree형태의 구조를 가짐
• 데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요

 

 인덱스(Index)를 사용하는 이유 

테이블에 데이터들이 인덱스의 가장 큰 특징은 데이터들이 정렬이 되어있다는 점입니다. 이 특징으로 인해 조건 검색이라는 영역에서 굉장한 장점이 됩니다.

 

조건 검색 Where 절의 효율성

테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드는 내부적으로 순서가 없이 뒤죽박죽으로 저장됩니다. 이렇게 되면 Where절에 특정 조건에 맞는 데이터들을 찾아낼때도 레코드의 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교해야 합니다. 이것을 풀 테이블 스캔 (Full Table Scan)이라고 합니다. 하지만 인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건 (Where)에 맞는 데이터들을 빠르게 찾아낼 수 있겠죠. 이것이 인덱스(Index)를 사용하는 가장 큰 이유입니다. 

 

정렬 Order by 절의 효율성

인덱스(Index)를 사용하면 Order by에 의한 Sort과정을 피할수가 있습니다. Order by는 굉장히 부하가 많이 걸리는 작업입니다. 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생됩니다. 하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 됩니다. 이미 정렬이 되어 있기 때문에 가져오기만 하면 되니까요.

 

 

 

MIN, MAX의 효율적인 처리가 가능하다.

이것 또한 데이터가 정렬되어 있기에 얻을 수 있는 장점입니다. MIN값과 MAX값을 레코드의 시작값과 끝 값 한건씩만 가져오면 되기에 FULL TABE SCAN으로 테이블을 다 뒤져서 작업하는 것보다 훨씬 효율적으로 찾을 수 있습니다.

 

 인덱스(Index)의 단점 

인덱스가 주는 혜택이 있으면 그에 따른 부작용도 있습니다. 인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지 시켜줘야 한다는 점입니다. 그렇기에 레코드 내에 데이터값이 바뀌는 부분이라면 악영향을 미칩니다. INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 INDEX 테이블 내에 있는 값들을 다시 정렬을 해야겠죠. 그리고 INDEX 테이블, 원본 테이블 이렇게 두 군데에 데이터 수정 작업해줘야 한다는 단점도 있습니다.

 

그리고 검색시에도 인덱스가 무조건 좋은 것이 아닙니다. 인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫습니다. 그리고 인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요합니다. 무턱대고 INDEX를 만들어서는 결코 안 될 말입니다. 

 

인덱스(Index)의 관리

앞서 설명했듯이 인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있습니다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 계속 정렬을 해주어야 하고 그에 따른 부하가 발생합니다. 이런 부하를 최소화하기 위해 인덱스는 데이터 삭제라는 개념에서 인덱스를 사용하지 않는다 라는 작업으로 이를 대신합니다.

• INSERT: 새로운 데이터에 대한 인덱스를 추가합니다.
• DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행합니다.
• UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가합니다.

 

인덱스 생성 전략

생성된 인덱스를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한으로 그리고 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다. 인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 Index 테이블이 생성됩니다. 이 기준 컬럼은 최대한 중복이 되지 않는 값이 좋습니다. 가장 최선은 PK로 인덱스를 거는것이겠죠. 중복된 값이 없는 인덱스 테이블이 최적의 효율을 발생시키겠고. 반대로 모든 값이 같은 컬럼이 인덱스 컬럼이 된다면 인덱스로써의 가치가 없다고 봐야 할 것입니다.

 

1. 조건절에 자주 등장하는 컬럼

2. 항상 = 으로 비교되는 컬럼

3. 중복되는 데이터가 최소한인 컬럼 (분포도가 좋은) 컬럼

4. ORDER BY 절에서 자주 사용되는 컬럼

5. 조인 조건으로 자주 사용되는 컬럼

 

출처 : https://coding-factory.tistory.com/746

 

인덱스 생성

CREATE [REVERSE] [UNIQUE] INDEX [인덱스이름] ON  테이블이름(컬럼 [ASC | DESC] [{,컬럼 [ASC | DESC]} ...])[;]

 

예

Book 테이블의 bookname 열을 대상으로 인덱스 ix_Book을 생성하려고한다면

CREATE INDEX ix_Book ON Book(bookname);

 

BOOK 테이블의 publisher ,price 열을 대상으로 인덱스 ix_Book2 를 생성하려고한다면

CREATE INDEX ix_Book2 ON Book(publisher,price);