DB Index 에 대해 알아보자

DB Index-ing 란?

"RDBMS에서 검색속도를 높이기 위해 사용하는 기술" 이라고 생각하면 편해요. 테이블 컬럼에 대해 색인(Index)를 부여하여 빠르게 검색이 가능합니다.

기본적으로 우리가 DB에 대해 SELECT 절을 수행할 때 Full Scan(테이블 데이터의 처음 부터 끝까지 모두 검색하는 방법) 을 수행하게 됩니다. 하지만 Index를 선언해 놓은 경우 작성된 Index파일을 통해 좀 더 빠르게 검색이 가능해 집니다. 

기본적으로 B-Tree구조를 통해 데이터를 Index를 수행하며, 각 DB Engine에 따라 사용할 수 있는 Indexing 방법이 다릅니다.

쉽게말해!

우리는 한권의 책에서 필요한 정보만 얻고자 할 때, 앞 혹은 뒤에있는 책의 목차를 찾아보면. 색인이란 이름으로 정렬된 목록을 확인 할 수 있습니다. 이 정리된 자료를 통해 우리는 원하는 정보를 빠르게 획득 가능합니다. 결론적으로 Index란 정보의 위치를 알려주는 "지표" 입니다.

더 알아보자

우리가 테이블을 생성할때 기본적으로 테이블명 .frm .myd .myi 세 가지의 파일이 생성합니다.

.frm : 파일의 경우 테이블 구조가 저장되어있다.

.myd : 실제 데이터가 저장되어 있는 파일이다.

.myi : index정보가 들어가 있는 파일이다.

제 컴퓨터에서는 이렇게 버티고 있습니다.

모든 사용자들이 같은 디렉터리 경로를 가지는지는 잘 모르겠어요.

저늑 osX 사용자이며 Homebrew를 통해 mysql을 설치 했습니다.

우리가 index명령을 실행할 경우 대상 테이블에 해당하는 .myi 파일에 우리의 Index 정보를 기록하게 됩니다.(아마 버퍼의 형태로 데이터를 저장하고 있는 것 같다. 열어보고 화들짝 놀랐다.)  Select 쿼리를 요청 받았을때 Tree 구조로 정리해 둔 데이터를 .myi파일에서 찾게 되고 Index를 사용하지 않은 쿼리라면 full scan을 통해 원하는 데이터를 긁어 가져갑니다.

Index-ing 의 장점

1. 검색, 정렬 속도의 향상 <-가장 중요한거 같음

2. 그룹화 작업의 속도를 향상 시켜준다. <- 수업시간에 몇번 해본거 같은데 그게 맞는지 모르겠다.

3. 테이블 행의 고유성 강화

4. 테이블의 Primary key는 기본적으로 Index 됩니다.

5. 다중 필드 인덱스를 통해 첫 필드값이 같은 레코드를 구분 할 수 있습니다.

* 최대 10개의 다중 필드를 포함 할 수 있습니다. 하지만 5개 이상의 다중 필드는 권장하지 않습니다. (이화식님의 데이터 베이스 설계 중)

Index-ing 의 단점

1. 많은 수의 Index를 생성할 경우 디스크의 용량이 줄어듭니다.(.mdb 파일의 용량이 커지기 때문)

2. 테이블에서 CUD(create, update, delete)작업이 빈번하다면 성능이 떨어집니다.

3. Index를 생성하는 시간이 길어질 수 있습니다.

4. CUD 작업에 대해서는 Index를 재작성 해야 하기 때문에 성능저하를 초래합니다.

Index를 언제 만들어야 하지?

Index를 생성하는 경우는 물론 검색 속도를 빠르게 해야하는 경우에 사용합니다. 검색을 하는 경우에도 다양한 상황이 존재하니까 이 기회에 한번 짚어보겠습니다.

1. 기본적으로 Index는 열 단위로 생성됩니다.

2. where 절에서 사용되는 컬럼을 인덱스로 만들면 효율이 높습니다.

3. 외래키가 사용되는 열에는 인덱스를 생성 해주는 것이 좋습니다.

4. JOIN에 사용되는 컬럼에도 인덱스를 생성 해주는 것이 좋습니다.

5. CUD의 발생 빈도와 Index의 효용 가치를 생각하여 Index를 생성해 줍니다.

6. 사용하지 않는 Index는 디스크 용량과 성능을 저하 시키므로 제거해 줍니다.

7. 일반적으로 인덱스로 지정하기에 적정한 분포도는 10% ~ 15% 입니다. 분포도를 구하는 공식은 <1/해당 컬럼의 distinct 데이터 개수(중복을 제외한 데이터 종류) * 100> 으로 백분율을 구할 수 있습니다.

Index의 종류

Index는 총 3가지로 분류됩니다. (제가 아는 수준에서 입니다.) 

첫번째, 인덱스의 저장 방법에 따른 분류

두번째, 인덱스로 설정되는 컬럼의 개수에 따른 분류 

세번째, 인덱스가 가지는 성격에 따른 분류

지금 부터는 3가지 분류와 각 분류의 종류에 따라 알아보겠습니다.

인덱스 저장 방법에 따른 분류

1. Clustered Index

물리적으로 테이블의 데이터를 재구성 한다.( 테이블이 처음 구성 될 때 정렬된 상태로 페이지가 구성된다)

한 테이블의 오직 하나만 가질 수 있다.( *여러개의 클러스터 인덱스를 구성하면 데이터가 꼬여 오류가 발생한다.)

B-tree 자료 구조를 이용한다. (B-tree는 루트노드 + 브랜치노드 + 리프노드로 구성되어 있다.)

각 노드에는 key:value 형태의 페이지를 가지고 있으며 value는 RID(group number + page number + offset)를 가지고 있다.

리프(Leaf)노드에는 레코드 정보가 담겨 있다.

2. Non Clustered Index

- 정렬을 하지 않은 상태로 페이지를 작성하며, 빈 공간이 존재하면 바로 공백을 채운다.

- Clustered Index와 같은 B-tree 자료를 이용하며 value에는 PK정보를 갖는다.

- 총 두번의 I/O 작업이 이루어 진다. (Clustered Index는 한번 이루어 진다.)

- 검색 하고자 하는 데이터의 키 값(PK)을 루트레벨에서 부터 검색하여 리프노드를 통해 실제 데이터의 주소를 얻는다.

( *여기서 첫번째 I/O 작업이 이루어 진다.)

- 리프노드를 통해 얻은 PK의 정보를 가지고 다시 한번 탐색한다 (두번째 I/O 작업)

말로 설명들으니 이해가 1도 안된다. 그래서 그림을 가져 왔습니다.

위의 사진을 보면 좌측(Clustered Index)과 우측(Non Clustered Index)의 트리 구조를 확인 할 수 있습니다.

 좌측 트리의 경우 루트 노드에서 탐색을 시작하여 Actual Data가 있는 Leaf Node 까지 탐색을 하여 레코드를 찾습니다.  Clustered Index에서는 이렇게 총 1번의 I/O작업이 이루어 집니다.

 우측 트리의 경우 루트 노드에서 탐색을 시작하여 Leaf node에 도착하지만 원하는 데이터를 얻을 수 없습니다. 여기에서는 위에서 제가 말했던 레코드의  PK를 얻을 수 있습니다.(1차 I/O 작업) 얻어낸 PK를 통해 정렬된 상태인 좌측의 Clustered Index의 트리를 방문하여 실제 레코드의 데이터를 추출해 낼 수 있습니다.(2차 I/O 작업) 이렇게 

Non Clustered Index는 총 2번의 I/O 작업이 이루어 집니다.

위에서 정신없이 얘기했던 내용이 사진을 통해 설명하였는데, 이해가 잘 되셨으면 좋겠습니다.

인덱스로 설정되는 컬럼수에 따른 분류

1. 단일 인덱스

- 하나의 컬럼만으로 인덱스를 설정하는 경우이다

2. 복합 인덱스

- 하나 이상의 컬럼에 대해 인덱스를 설정하는 경우이며 where 절에서 자주 사용되는 컬럼들을 복합 컬럼 인덱스로 지정하면 쿼리 성능 

향상에 큰 효과를 볼 수 있다.

- Index로 설정한 컬럼들의 데이터 타입이 3072byte를 넘게되면 오류가 발생한다.

- 인덱스에 설정된 컬럼 순서대로 쿼리문을 사용하지 않으면 인덱스를  사용하지 않게 된다.

- 액세스 패스 조건에 많이 사용되는 컬럼을 우선시하여 사용한다.

- '=' 조건으로 사용되는 칼럼을 먼저 명시해줍니다.

- 분포도가 좋은 칼럼을 우선 명시합니다.

인덱스가 가지는 성격에 따른 분류

1. Unique Index

- 인덱스 값의 중복과 NULL을 허용하지 않는다.

2. Non Unique Index 

- 인덱스 값의 중복을 허용한다.

⭐주의사항⭐

앞에서 부터 지속적으로 말했던 부분입니다. 왜 테이블에 INDEX를 설정함에 있어서 CUD(Create, Update, Delete)를 고려해야하는지 알아봅시다.

위에서 말씀 드린것 처럼 저희가 테이블을 생성하게 되면 기본적으로 3가지의 파일이 생성 된다고 말씀 드렸었죠?(위에 다 있습니다*^^*)

그 중에서도 Index를 담당하는 .myi 파일에 대해서  CUD작업이 발생할 때 이슈들이 있습니다. 차근차근 설명해보겠습니다.

1. Insert - Index Split

데이터가 추가됨에 따라 우리는 기존에 있던 노드블록(Branch node)이 쪼개지는 현상이 나타난다.

인덱스는 말그대로 순차를 나타내는 지표이기 때문에 새로운 데이터가 추가되고 더 이상 넣을 공간이 없다면 기존의 블록을 쪼개서(Split) 확장 하게된다.(말만 들어도 복잡할 꺼 같지 않은가..?) 각 Node는 Key : Value로 구성되며 각 Value 에는 RID(group number + page number + offset)가 담겨있다고 조~~기 있습니다. 맞습니다. 다 옮겨줘야되고 다 수정해줘야 합니다. (오라클에서 그렇게 만들었다고 합니다. 저희는 힘이 없습니다.)

그리고 값을 옮기고 재정렬하는 동안 추가적인 CUD가 발생하면 안되기 때문에 DML 블로킹이 발생합니다.

위와 같은 상황이 INSERT가 발생할 때 Index에 일어 납니다.

2. Delete

보통의 사람이라면 Delete가 발생했을 경우에 정렬된 Index 정보에서 "삭제된 레코드를 찾아 지울 것"이라고 생각하겠죠? (저만 그런가요...) 무튼 사실 상 테이블에서는 삭제가 발생하지만 Index 파일에서는 "삭제"가 아닌 "사용중지" 명령을 내립니다. 즉, 데이터는 파일에 존재하고 용량을 계속 차지한다는 것이죠.

빈번한 Delete가 발생하게 되면 Index에 저장된 파일의 크기는 계속 증가하고, 사용하는 데이터는 10만개이지만 저장된 데이터는 20만, 30만이 될 수도 있다는 얘기 입니다.

당연히 이렇게 되면 인덱스를 사용을 통한 성능향상을 기대하기 어렵게 되겠죠.

3. Update 

마지막으로 Update입니다. 이놈은 안좋은걸 다 갖추었습니다. 위의 Insert와 Delete의 안좋은 역할(?)을 모두 합니다. Update가 발생하면 Index 파일에서 기존의 데이터는 Delete(사용불가 상태) 되고 새로운 데이터를 Insert하는 과정을 진행합니다. 당연히 Insert + Delete 의 Side effect를 모두 갖추었기 때문에 다른 DML보다 부담이 커집니다.

지금까지 데이터베이스 인덱스(싱)에 대해서 포스팅을 해봤는데, 사실 저도 처음 공부하는 부분이고 나름 많은 자료들을 읽어가면 내용을 정리했지만

정확하지 않은 내용들이 많으니 다른 많은 포스팅들도 더 보시는 것을 추천합니다. 시간이 된다면 추가적인 공부를 통해 업데이트를 진행 하도록 하겠습니다.

긴듯 짧은듯 유용한듯 아닌듯한 글을 읽어주셔서 감사합니다 ( - - ) ( _ _ ) ( - - )