[ 데이터베이스 ] 24. Index

특정 컬럼에 대한 데이터를 찾으려고 할 때

 

● [ 해당 컬럼에 Index가 걸려있지 않다면 ]

 

테이블에 있는 모든 정보를 돌아보며 찾아야 한다.

이렇게 테이블을 전체 도는 것을 full scan이라고 한다.

= O ( N )

 

● [ 해당 컬럼에 Index가 걸려있다면 ]

B-tree index 로 되어있다면 

= O ( log N ) 

 

 

= Index를 쓰는 이유

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

2) 빠르게 정렬 / 그룹핑 하기 위해

3) Join 조건도 빠르게 찾기 위해

 

 

▶ CREATE INDEX player_name_idx On player (name);

    로 play에 대한 index를 걸어준다.

 

형태 : CREATE Index ( idx 를 카운트하려는 변수명 ) On  ( 원하는 테이블 ) ( 해당 테이블의 컬럼 이름 )

 

player 테이블에서 name attribute에 Index를 걸어준다. 

 

 

▶ CREATE UNIQUE INDEX team_id_backnumber_idx On player (team_id , backnumber);

 

 

name attribute를 Index할 때 사용하기 위해 테이블을 생성할 때 Index를 걸어준다.

name은 같은 값이 올 수 있기 때문에 UNIQUE를 붙이지 않는다.

 

사실 중간에 들어가는 Index는 생략해도 된다.

INDEX (name)해도 됨.

 

 

team_Id와 backnumber로 만드는 경우에는 그 두 개 세트의 값이 Unique하므로

UNIQUE INDEX를 붙여준다.

이와 같이 두 개 이상으로 구성된 attribute로 index를 거는 것을

Multicolumn index 혹은 composite index라고 한다.

 

하지만 요새 RDBMS에서는 PK에 index가 자동 생성된다.

 

 

한 테이블에는 여러 개의 인덱스가 있을 수 있다.

해당 인덱스들에 대한 설정을 보고싶을 때는 위와 같이 하면 된다.

 

 

 

이거 읽는 법 (왼쪽부터)

 

- Table : 어느 테이블에 대한 Index 정보인지

 

- Non_unique : 해당 Index가 고유한 값을 가지는지

 

- Key_Name : Index로 만들어둔 이름 
   = 위와 같이 같은 이름이 여러 개 있을 수 있다.

      이건 Multicolumn index라는 뜻이다.

 

그럼 이게 뭘 나타내는지 어떻게 알 수 있을까?

그 뒤에를 보면 된다.

 

- seq_in_index

 : Multicolumn index에서 해당 attribute가 몇 번째로 설정되어 있는지

 

- Column_name

: 어느 컬럼에 대해 Index를 한 건지 알려준다.

 

---

 

B - Tree 기반 index 동작 방식

 

오른쪽 같이 Members table이 있을 때,  a 컬럼에 index를 걸면 오른쪽과 같이 나온다.

 

오른쪽 테이블을 보면 a를 순서대로 나열한 뒤, 그 튜플에 포인터도 같이 둔다.

따라서 그 모양을 시각적으로 나타낸다면 다음과 같을 것이다.

 

Where a = 9 를 수행할 때는?

순서대로 나열되어 있다는 특징을 이용하여 Binary searching 으로 간다.

 

 

[ Where a = 9; 찾기 ]

 

중간 Index (= @ )를 들고온다.

> 해당 @ 주소값이 찾으려는 주소값보다 작으면 @보다 앞에 있는 튜플들은 제외시킨다.

> 그 제외된 상태에서 또 중간 Index를 가져온다..

...반복 

 

그렇게 해서 찾은 뒤에 해야하는 동작은

a 컬럼의 값이 중복인 경우가 있을 수도 있기 때문에 한 칸씩 앞으로 가면서 a가 9인지 아닌지를 판단한다.

 

매 시행마다 찾을 양이 절반이 된다.

 

 

---

---

[ Where a = 7 AND b=95 ; 찾기 ]

 

 

 

이렇게 7에 도착했을 때, 첫번째 조건에 맞는 a=7을 찾기 위해 한 칸씩 확인한다.

뒤에 있는 값들은 a = 9 , 13 이므로 제외시킨다. 

 

그 위에 있는 3개의 a=7 들 모두를 둘러보면서 b값을 비교하여 선택한다. 

 

 

이러면 Binary tree + Full scan 이 된다. 

따라서 a와 b를 하나로 묶어주는 Index를 만든다.

 

 

Create Index (a,b)했을 땐

a를 기준으로 먼저 정렬하고

a의 값이 같다면 그 안에서 b를 기준으로 정렬한다.

 

다시 [ a = 7 and b = 95 ] 를 찾는다면

가운데인 a=5를 기준으로 a=7이 더 크므로 그보다 작은 값들은 삭제한다.

 

그 남은 것들 중에 가운데인 a=7 와 b=95를 가리키므로 그걸 선택한다.

 

이 외에도  [ a = 7 and b = 95 ] 이걸 만족하는 튜플이 더 있을 수 있으므로

위쪽과 아래쪽을 더 탐색해보아야 한다.

 

하지만, 위와 같은 인덱스를 나누고 b=95인 것을 찾는다면 효율성이 높아지진 않는다.

▶B는 정렬이 되어있지 않기 때문이다.

 

 

---

예제

 

위 테이블에서 인덱스를 생성해 둔 것으로

상단에 있는 2개의 쿼리는 효율적으로 수행할 수 있다.

 

하지만 3번째 쿼리문에서는 backnumber가 정렬되어있지 않기 때문에 효율적이지 않다.

4번째 쿼리문은 team_id를 빨리 찾지만, backnumber는 빨리 찾을 수 없다. 둘이 and로 묶이는 것이 아닌 or로 묶이기 때문에

 

따라서 backnumber 하나만 커버하는 Index가 필요하다.

 

---

 

쿼리가 어떤 Index를 쓰는지 알고싶다면

 

명령어 앞에 Explain 을 적어준다.

 

DB에서 알아서 적절한 index를 선택해줌!

 

- 직접 설정해줄 수 있긴 하다.  

 

 Use = 권고

 Force = 이거 꼭 써라

 

---

Hash Index

특징

● Hash table을 이용하여 index 구현

● 시간복잡도  = O(1) 

 

 

단점

● rehashing에 대한 부담

● equailty 비교만 가능하다. range 비교 불가능 

 ex ) a=5인 것을 가져와라  (가능)

        a > 5인 것을 가져와라 (  X  )

● Multicolumn index 의 경우에는, ( Create(a,b) 로 사용한다면 ) a,b 두 개 다 들어가는 것에 대한 조회를 해야한다.

 

 

---

Full scan이 더 좋은 경우

1. table에 데이터가 많지 않을 때

2. 조회하려는 데이터가 테이블의 상당 부분을 차지할 때

 

 

(출처) 

유튜브 쉬운코드