[데이터베이스] 인덱스(index)

사실 많은 데이터를 다룰일이 없어서 인덱스에 대해 크게 신경쓰지 않았는데, 이번에 공부를 하고 나서 인덱스가 굉장히 강력한 기능을 한다는 것을 알았습니다. 거의 필수적인 개념이죠. 이번시간에는 인덱스가 무엇인지 또 어떻게 사용하는지에 대해 공부해 보겠습니다.

---

인덱스

인덱스라는 개념을 우리에게 친숙합니다. 우리가 책에서 원하는 내용을 찾을 때 처음부터 찾지않고 책의 목차를 보고 내가 원하는 내용이 몇페이지에 있는지 확인하고 그 페이지로 바로가죠 ? 이런 개념이 인덱스 입니다.

 

책 속의 내용 중에서 중요한 단어나 항목 등을 쉽게 찾아볼 수 있도록 일정한 순서에 따라 별도로 배열하여 놓은 목록.

 

데이터베이스에 수많은 데이터가 저장되어 있고 "x"라는 데이터를 찾기 위해 처음부터 하나씩 검사해야 한다면 아주 많은 시간이 걸릴 것입니다. 예를 들어 DB에 100만개의 데이터가 저장되어 있고 "whwlsvy12"라는 유저를 검색할 때 마다 최악의 경우(100만 번째)에 걸린다면 사용자는 결과물을 받아보기까지 굉장히 오랜 시간이 걸리겠죠.

 

근데 만약에 유저 이름이 사전순으로 "정렬"되어 있다면 우리는 보다 쉽게 찾을 수 있을 것입니다. "whwlsvy12"라는 유저를 검색한다면 유저이름이 "w"로 시작하는 부분을 부터 보면 되니깐요.

 

이처럼 인덱스는 RDBMS에서 검색속도를 높이기 사용하는 하나의 기술입니다.

 

인덱스는 B-tree구조를 사용하며 데이터가 정렬된 상태를 유지합니다. 때문에 어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있습니다(트리의 높이가 다른 경우 약간의 차이는 있을 수 있지만 O(logN)의 시간을 가집니다). 

 

인덱스를 사용하면서 얻을 수 있는 장/단점은 다음과 같습니다.

 

- 검색 속도가 무척 빨라질 수 있다(단, 항상 그런 것은 아님)

- 그 결과 해당 쿼리의 부하가 줄어들어서, 결국 시스템 전체의 성능이 향상

- 인덱스가 데이터베이스 공간을 차지해서 추가적인 공간이 필요해지는데 대략 데이터베이스 크기의 10% 추가공간이 필요해짐

- 처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다

- 데이터의 변경 작업이 자주 일어날 경우 오히려 성능이 많이 나빠질 수 있다

 

인덱스는 크게 클러스터형 인덱스와 비 클러스터형 인덱스(보조인덱스)로 나눌 수 있습니다.

---

클러스터형 인덱스

클러스터형 인덱스는 단어들이 정렬되어 있는 영어사전과 같은 개념입니다. 클러스터형 인덱스는 테이블당 1개만 생성할 수 있으며 클러스터가 적용된 속성을 기준으로 자동으로 정렬됩니다. 여기서 기존의 데이터를 정렬해야 하기 때문에 시간이 오래 걸립니다.

 

명시적으로 index를 생성하지 않더라도 primary key가 적용된 속성은 클러스터형 인덱스 역할을 하며 거의 동일한 용어로 사용됩니다. 다음에 설명하겠지만 속성에 unique을 추가하면 해당 속성은 보조인덱스 역할을 하는데, unique not null을 하면 클러스터형 인덱스와 같은 역할을 합니다. 근데 만약 a속성에 primary key, b속성에 unique not null을 하면 primary key가 우선순위를 가져 a속성이 클러스터 인덱스가 됩니다.

 

해당 테이블에 클러스터형 인덱스가 적용되면 데이터는 위의 구조로 바뀝니다. 이전에는 AAA ~ OOO의 순서가 없었지만 클러스터형 인덱스가 적용되고 사전순으로 정렬되었으며 각 데이터는 페이지에 들어가게 됩니다. 여기서 mysql 기준 페이지의 크기는 16kbyte입니다. 

 

하지만 만약 "III"와 "GGG"가 추가되면 어떻게 될까요 ? 첫번째 페이지의 용량이 다 차게되어 페이지 분할이 일어나게 됩니다. 만약 더 많은 데이터가 추가되면 그 만큼의 페이지가 추가되고, 페이지 내부에서 다시 정렬하게 됩니다. 또 루트 페이지가 다 차게되면 또 루트 페이지에서도 페이지 분할이 일어나고, 새로운 루트 페이지가 생기고...이렇게 DB에 많은 부하를 작업이 수행됩니다. 

 

그렇기 때문에 클러스터 인덱스의 경우 select를 자주 하는 테이블에서 사용하는 것이 좋으며 insert, update, delete를 자주하는 테이블에 클러스터 인덱스를 넣게 된다면 그 만큼 부하가 생기겠죠.

---

보조 인덱스(비 클러스터형)

클러스터형 인덱스가 책의 인덱스와 같은 역할이라면 보조 인덱스는 책 뒤의 찾아보기와 같은 역할을 합니다. 찾고 싶은 키워드를 알고 있으면 찾아보기에서 해당 키워드가 몇페이지에 있는지 알 수 있죠. 클러스터형 인덱스는 테이블 당 1개만 생성할 수 있지만 보조 인덱스는 테이블 당 여러개 생성할 수 있습니다. 

 

보조 인덱스는 해당 열에 unique 또는 unique null 을 추가하여 만들 수 있습니다. 

 

클러스터형 인덱스와는 다르게 보조 인덱스는 해당 속성을 보조인덱스로 만들었다고 해서 데이터가 정렬되거나 하는 일은 없습니다. 우리가 책 뒤에 찾아보기를 만들었다고 해서 책의 내용이 바뀌는건 아니니깐요(반대로 클러스터형 인덱스는 목차이기 때문에 책의 내용이 정렬되어 있어야 그 의미가 퇴색되지 않죠). 대신에 아래처럼 새로운 페이지가 생깁니다. 이렇게 생긴 페이지는 크기는 대략 전체 크기의 10%정도 입니다. 

 

 

중요한 부분은 데이터 페이지는 정렬되지 않습니다. 대신 리프 페이지가 새로 만들어 졌고, 여기 있는 데이터들이 정렬되었습니다. 특이한 점은 각 데이터마다 실제 데이터 영역의 주소값이 저장되어 있습니다. 찾아보기에서 해당 키워드가 몇번째 페이지에 있는지와 비슷하네요.

---

검색 vs 삽입, 삭제, 수정

- 검색

1) 클러스터 인덱스

클러스터 인덱스의 경우 검색에서 좋은 성능을 보입니다. 리프 노드들이 이미 정렬되어 있기 때문에 DDD라는 데이터를 찾을 때 루트 페이지 ㅡ> 리프 페이지, 총 2번의 페이지 참조가 일어나게 됩니다.

 

2) 보조 인덱스

반면 보조 인덱스의 경우 BBK라는 데이터를 찾고 싶을 때 루트 페이지 ㅡ> 리프 페이지 ㅡ> 데이터 페이지, 총 3번의 페이지 참조가 일어나기 때문에 데이터 검색의 경우 클러스가 더 좋은 성능을 보입니다.

 

 

- 삽입, 삭제, 수정

 

1) 클러스터 인덱스

클러스터 인덱스의 경우 III라는 새로운 데이터를 입력하게 하게 되면 해당 리프 페이지의 공간이 부족하게 되어 새로운 페이지가 생성됩니다. 또한 새로운 데이터가 들어왔기 때문에 페이지 내부적으로 정렬하는 작업도 수행되어야 하죠. 이는 루트 페이지도 마찬가지 입니다. 

 

2) 보조 인덱스

 

반면에 보조 인덱스의 경우 새로운 데이터가 들어왔을 경우 페이지 분할이 일어나지 않았고 내부적으로 정렬작업도 하지 않았습니다. 때문에 삽입, 삭제, 수정 하는 경우라면 보조 인덱스가 클러스터 인덱스보다 더 좋은 성능을 보여줍니다.

 

ㅡ> 검색의 경우 클러스터 인덱스가 보조 인덱스보다 더 좋은 성능을 보여주고, 삽입,삭제,수정의 경우 보조 인덱스가 클러스터 인덱스보다 더 좋은 성능을 보여줍니다.

---

클러스터 인덱스와 보조 인덱스 같이 쓰기

사실 대부분의 경우 클러스터 인덱스와 보조 인덱스를 같이 사용합니다. 데이터 테이블에 테이터를 넣어야 검색할 수 있기 때문에 무조건 검색만 하는 데이터, 무조건 삽입,수정,삭제만 하는 데이터는 절대 존재하지 않겠죠. 그렇기 때문에 대부분의 경우 하나의 테이블에 클러스터형 인덱스, 보조 인덱스를 같이 사용하며 그 구조는 아래와 같습니다.

 

크게 그림을 보면 위에는 보조 인덱스, 아래쪽은 클러스터형 인덱스가 있습니다. 

 

보조 인덱스 페이지의 리프 페이지의 데이터 value값에는 원래 데이터의 주소가 들어가야 하지만 다른 데이터가 들어가 있습니다. 이 데이터는 인덱스 페이지의 루트 페이지의 key값을 사용하여 실제 데이터가 존재해 있는 데이터 페이지로 이동할 것입니다.

 

이러한 구조의 장점은 데이터 삽입,삭제,수정 때문입니다. 만약 보조 인덱스 페이지의 리프 페이지의 value값이 데이터 페이지를 그대로 참조하고 있으면 만약, 데이터 페이지의 데이터가 수정되는 경우 그 주소값도 바뀔 수 있기 때문에 보조 인덱스의 리프 페이지의 value값이 계속 수정되어야 합니다.

 

하지만 위의 구조라면 데이터 페이지의 마지막에 새로운 데이터가 들어왔다고 하여도 단순히 클러스터형 인덱스 페이지의 루트페이지만을 참조하고 있기 때문에 데이터를 따로 수정할 필요가 없습니다. 

 

이렇게 되면 데이터를 검색하는데는 약간의 성능저하가 있을 수 있지만 삽입,삭제,수정하는데 큰 성능상의 이점을 보입니다.