[Database] 인덱스 파해치기

 

안녕하세요:)

프로젝트를 진행하며 DB설계를 하는 과정에서

아직 온전하지 못한 개념들이 많다고 느꼈는데요

오늘은 인덱스에 대해 이야기 해보려 합니다!

 

글을 작성하는데 다른 블로그 및 유튜브 영상을 참고하였음을 미리 알려드립니다.

(글 속 자료사진 출처: 유튜브 '쉬운 코드' 채널 "DB 인덱스(DB index) !! 핵심만 모아서 설명합니다 !!" )

 

---

 

인덱스

데이터베이스 테이블에 대한 검색 성능 속도를 높여주는 자료구조.

특정 열(또는 열의 조합)에 대한 정렬된 데이터 구조.

 

 인덱스의 개념을 들었을 때 where나 order by 등과 어떤 차이가 있는지 궁금했다.

 

결론부터 말하면 관련은 있으나 완전 다른 개념이라는 것!

인덱스는 특정 튜플들을 빠르게 조회하기 위한 것이고, 'WHERE' , 'ORDER BY'절은 쿼리의 필터링 혹은 정렬을 위한 것이다.

 

즉, 적절한 인덱스를 설정할 경우 ('WHERE'절(예시)의 조건이 인덱스된 열과 일치한다면) 인덱스를 통해 특정 조건( 'WHERE'절)을 충족하는 행을 더 빠르게 찾을 수 있으므로 쿼리의 성능도 향상이 된다고 보면 된다.

 

따라서 Index를 쓰는 이유는

✅조건을 만족하는 튜플을 빠르게 조회하기 위해

✅빠르게 정렬(order by)하거나 그룹핑(group by)하기 위해

 

 

● 실제 예시

(왼쪽부터) 테이블 생성 후 인덱스 추가하는 예시 / 테이블과 인덱스 생성 동시에 하는 예시

 

왼쪽에 나와있는 예시는 where절에 조건을 걸어두었다.

하나는 name칼럼, 하나는 team_id와 backnumber 두개의 칼럼!

 

이때 미리 이 조건을 빨리 찾기 위해 작업하는 것이? 바로 인덱스❗

각각의 조건에서 쓰이는 칼럼을 인덱스로 설정한 것을 확인할 수 있다.

두개의 칼럼이 인덱스로 들어가면 이는 유니크한 값을 찾는 것이기에 UNIQUE INDEX로 들어간다고 한다.

 

참고로 대부분의 RDBMS(Relational Database Management System)는 기본적으로 PK는 인덱스로 생성된다고 하니 참고!

 

이렇게 인덱스를 설정하고 실제로 동작하는 데에는 몇가지 방식이 있다. (B트리, Hash 등)

먼저 B트리를 살펴보도록 하자.

 

---

B트리 기반 인덱스 동작 방식 

B tree는 간단히 보아 위와 같이 생겼다. (자녀 노드 2개 이상 가능)

(이번 파트는 인덱스가 핵심이기 때문에 자세하게는 설명하지 않고 핵심만 말하고 넘어가려 한다.)

B 트리의 핵심은 범위로 나누어진다는 것! 이것만 알고 넘어가겠다. 

 

 

B tree 기반으로 인덱스를 생성하게 되면 형태는 이렇다.

 

인덱스 칼럼(a)에 대해 정렬된 데이터, 오른쪽 ptr 데이터로 구성이 되어 있다.

(ptr은 pointer로 실제 테이블의 어떤 튜플과 연관이 있는 것인지 해당 정보를 가지고 있다.)

 

그리고 조건에 대해 이진 탐색(Binary Search)을 하게 되는데 반복적으로 중간 요소를 선택해 검색 범위를 반으로 줄여가며 작동하는 것이다.

 

아래 작동예시를 살펴보자.

 

먼저 위 예시는 주어진 조건이 a와 b모두를 봐야하는 상황이다.

> a먼저 확인! 그 후 b 확인!

이때 B트리는 중간을 확인(a=5)하고 조건은 5보다 커야하니 그 전에 있는 튜플은 탐색하지 않아도 된다.

 

 

그 후에 다시 중간 튜플을 찾아 조건을 확인하고 a=7에 부합하니 b도 확인!

해당 튜플은 선택이 되지만 또 부합하는 튜플이 있을 수 있으니 또 확인을 해야한다.

 

아래쪽은 a부터 탈락으로 컷! 위쪽은 a는 7이나 b가 80인 것으로 보아 그 위쪽은 95보다 작은 값들로 구성이 되어있음을 알 수 있으므로(왜냐? 이미 오름차순으로 정렬이 되어 있으니까) 최종적으로 하나의 튜플만 선택이 되는 것을 확인할 수 있다.

 

 

아래는 조금 생각할 부분이 있는 예제이다.

위 player 테이블에는 현재 3개의 인덱스가 존재한다.

1,2번의 쿼리문은 현존하는 인덱스로도 충분히 성능을 높일 수 있다.

 

하지만 3,4번의 쿼리문의 경우 full scan을 해야하는 상황을 발생시켜 backnumber를 index로 추가해주어야 하는 것이다.

이렇듯 사용되는 query에 맞추어 적절히 index를 걸어주어야 query가 빠르게 처리될 수 있다는 것을 기억해야 한다!

 

 

+) 참고로 쿼리 성능이 안나와 확인해보고 싶다면 

 

EXPLAIN을 활용해보기! 

원래 optimizer가 적절한 index를 선택해주지만 그렇지 않을 경우 직접 명시해줄 수도 있음

SELECT * FROM player USE INDEX(backnumber_idx) WHERE backnumber=7;

 

---

 

자, 이렇게만 보면 index를 마구 만들어놓으면 언젠가는 쓸 일이 있을테니 일단 다 만들어놓고 보면 되는 거 아닌가?

싶은 생각이 들 수 있다.

하지만,,,불필요한 index를 만들지 않아야 하는 이유가 있다❗

 

** 추가적인 저장 공간 차지

(인덱스를 생성하면 인덱스를 위한 '부가적인 데이터'도 생성이 되므로)

** table에 write(Insert, Update, Delete)할 때마다 index도 변경 발생(오버헤드 생김)

(테이블에 데이터가 추가되면 인덱스의 테이블에도 해당 데이터가 정렬에 맞게 들어가야 하니 시간 소요)

 

---

 

Hash table 기반 인덱스 동작 방식 

(인덱스에서 중요도가 낮기에 핵심만 보고 넘어가겠다.)

 

Hash table의 경우 동작되는 방식이 위와 같다.

넣을 데이터가 hash function을 거치고

→ 나오는 정수에 대해 배열의 크기를 나누는 모듈러 연산을 진행한다. 

→ 나오는 인덱스 숫자에 해당 데이터를 넣는 것이다.

 

이 해시테이블을 이용해 인덱스를 만들면 시간복잡도 O(1)의 성능을 가지지만 

단점이 존재한다!

○ rehashing에 대한 부담(array로 데이터 저장하면서 어느 순간에는 크기를 늘려줘야하는데 이에 대한 부담o)

○ equality 비교만 가능 (range 비교 불가능)

○ multicolumn index의 경우 전체 attributes에 대한 조회만 가능(a,b칼럼이 모두 조건에 있어야만 해당 index를 사용할 수 있음)

 

따라서 범위검색이 많이 사용되는 DB에서는 해시테이블을 이용해 인덱스를 만드는 게 적합하지 않아 B tree를 이용한다.

 

 

 

---

 

인덱스에 대해 배우고 나면 '그럼 인덱스는 조건에 맞춰 항상 있어야하는 구나'

이러한 생각을 가질 수 있다.

 

하지만 Full scan이 더 좋은 경우도 있다는 것!

● table에 데이터가 조금 있을 때(몇 십, 몇 백건)

● 조회하려는 데이터각 데이블의 상당부분을 차지할 때

이 두가지의 경우 인덱스의 사용이 큰 의미가 없으므로 굳이 사용하지 않아도 된다는 것!

 

---

 

오늘은 인덱스에 대해 간단히 살펴봤습니다.

DB를 설계하고 실제로 구축을 할 때 해당 지식이 굉장히 중요하겠다는 것을

알 수 있는 시간이었고 프로젝트에서도 이를 녹여 DB 구축을 잘 해내고 싶어지네요 :D

 

오늘도 모두 화이팅하세요!!