25. DB INDEX

DB 쿼리 속도를 높이는데 너무나도 중요한 것이 바로 index입니다

그래서 실무에서 매우 매우 자주 사용되는데요,

이번 시간에는 인덱스가 왜 중요한지, 어떻게 사용되는지, 동작 원리는 무엇인지  핵심만 모아서 아주 알차게 설명합니다. 

그럼 오늘도 고고씽!!!

오늘날 대부분의 DBMS는 B-tree 기반의 index로 원하는 정보를 빨리 찾는다.

(MySQL의 경우 B-tree 이외에도 hash index도 제공을 한다.) 

SELECT,DELETE,UPDATE 그리고 JOIN에서의 조건(condition)을 만족하는 tuple들을 빠르게 찾기 위해서 index를 사용한다. 

아래에서 이미 table에 tuple들이 저장돼 있을 때, 어떻게 index를 생성하는지에 대해 알아 보자. 

PLAYER 테이블에 이미 TUPLE들이 저장돼 있는 경우

 

name은 중복된 값을 허락하고, {team_id,back_number}는 tuple들을 unique하게 식별을 한다.

index를 걸려고 하는 attribute(s)가 unique하게 식별하는지 안 그런지에 따라 index를 생성하는 방법이 조금 다르다.

1. name에 index 걸기

create INDEX player_name_idx ON player(name)

2. {team_id,back_number}에 index 걸기

create UNIQUE INDEX team_id_back_number_idx ON player(team_id,back_number)

 

 

아래에서는 애초에 table의 스키마를 짤 때에 index를 생성하는 법을 알아 보자. 

테이블 schema 정의 시 index 생성법

참고로, table의 스키마를 정의할 때에 index를 생성하는 경우에 player_name_idex와 같은 index명을 생략이 가능하다.

오늘날의 대부분의 RDBMS 같은 경우, primary key, foreign key에 대해서는 자동으로 index를 생성을 해 준다. 

 

테이블에 걸린 index를 확인하는 법

참고로 위 그림에서는 index name 중 team_id_backnumber_idx가 2개가 있고 그 다음 column에는 sequence가 1,2가 돼 있다.

즉, team_id_backnumber_idx는 multicolumn index라는 것을 알 수가 있다.

multicolumn index가 어떤 attribute로 구성돼 있는지는 Comlumn_name이라는 column에서 확인이 가능하다. 

대부분의 RDBMS에서는 INDEX는 B tree 기반으로 동작을 한다.

(MySQL에서는 B tree 이외에도 hash index도 제공을 한다.)

index 테이블은 정렬이 된다. 

index 테이블이 정렬된 뒤,

이진 탐색을 사용하여 조건(condition)에 부합하는 tuple을 특정한다. 

INDEX(a)에서 a=9을 만족시키는 tuple이 또 존재할 수도 있다.

그러므로, a=9를 만족시키는 tuple의 아래 row도 a = 9를 만족시키는지 검사를 해야 한다.

그러나, a = 9를 만족시키는 tuple은 INDEX(a)에서 단 1개이다. 

먼저 INDEX(a)에서 조건(condition) a=7인 row를 이진 탐색으로 찾는다.

INDEX(a)에서 a=7인 tuple을 찾은 다음 Members 테이블에서 b=95인 tuple을 탐색한다. 

INDEX(a)에서 a=7을 만족시키는 tuple이 3개가 존재한다. 

이때, Members 테이블에서 b = 95를 만족시키는 tuple이 여러 개 존재할 수 있으므로

INDEX(7)에 해당하는 모든 tuple에 대해 포인터로 Members 테이블의 b값도 검사를 해 줘야 한다. 

위 예제는 a=7을 만족하는 모~~든 tuple을 검사하여 그 중, b=95를 만족하는 tuple이 존재하는지를 검사해야 한다.

즉, a = 7을 만족하는 tuple들에 대해서 full scan(선형 탐색)을 해야 한다. 

지금이야 a = 7을 만족하는 tuple이 3개라서 full scan을 해도 비용이 얼마 들지 않지만, a= 7을 만족하는 tuple이 10만개 정

도가 됐을 때, full scan을 하게 되면 성능이 안 좋아 진다.

Solution : a와 b에 대한 INDEX를 생성!

Multicolumn Index의 경우, 왼쪽의 attribute부터 순차적으로 정렬이 된다.

ex) create index(a,b) : a를 기준으로 1차적으로 tuple들이 정렬이 되고, 그 다음 b를 기준으로 2차적으로 정렬이 된다. 

고로, MultiColumn INDEX를 생성 시에는, attribute의 순서를 고려하여 생성을 해야 한다. 

Multicolumn index의 경우, 왼쪽의 attribute(여기서는 a = 7 )를 만족하는 tuple들을 1차적으로 먼저 찾고, 

그 tuple들 중에서 나머지 attribute(s)(여기서는 b=95)를 만족하는 tuple들을 찾아 낸다.

(물론, 여기서도 이진 탐색으로 찾음)

INDEX(a,b)를 가지고, b=95를 만족하는 tuple들을 찾으려고 했을 때, INDEX를 사용하였으니 성능이 좋을까???

nope!! 

INDEX는 정렬이 되어 있기 때문에 이진탐색을 사용하여 빠른 탐색이 가능하다.

그러나 b는 정렬이 되지 않은 상태이기에 이진 탐색이 불가능하며, 선형 탐색으로 tuple을 찾게 된다.

고로, 성능이 안 좋다. 

PLAYER 테이블에 걸려 있는 INDEX는 위와 같다.

이때에 아래의 4개의 SELECT문은 실행 시 어떤 INDEX를 사용을 할까??

1번째 SELECT문 : {team_id,back_number} INDEX를 사용하며 이진탐색으로 TUPLE들을 빠르게 탐색!

2번째 SELECT문 : 이것 또한 {team_id,back_number} INDEX를 사용하며 이진탐색으로 TUPLE들을 빠르게 탐색!

3번째 SELECT문 : 사용할 수 있는 INDEX가 없기 때문에 PLAYER table을 full scan을 한다.(고로, 탐색 속도가 느리다.)

4번째 SELECT문 : 이것 또한 사용할 수 있는 INDEX가 없기 때문에 PLAYER table을 full scan을 한다. 

(만약, 3,4번째 select문을 빠르게 실행하기 위해서는 back_number에 대한 INDEX를 생성하면 된다.)

참고로, 어떤 INDEX를 쓸지는 개발자가 정하는 것이 아니라 DBMS안의 optimizer가 적절하게 index를 선택한다. 

(아래에서는 개발자가 직접 index를 설정하는 방법에 대해서 배운다.)

USE INDEX :  "가급적"이면 해당 index를 사용해주세요 와 같은 늬앙스로, optimizer가 이 요청을 무시하고, full scan을 할 수가 있다.

FORCE INDEX : optimizer가 해당 index를 사용하는 경우 원하는 데이터를 찾을 수 없다고 판단을 하면 full scan으로 작동을 하고, 그게 아니면 해당 index를 사용할 것을 강요(force)한다. 

IGNORE INDEX : optimizer에게 SQL문 실행 시, 해당 INDEX는 사용하지 말라고 설정하는 것!

위 그림에서 말하는 write는 INSERT,UPDATE,DELETE를 말하는 거임. 

 team_id를 INDEX로 생성하는 것은 불필요하다.

왜냐하면, 이미 INDEX(team_id,back_number)가 존재하기 때문에 이 INDEX를 사용을 하면 된다. 

PLAYER 테이블은 탐색할 필요가 없고 INDEX(team_id,back_number)를 탐색만 해도, 원하는 정보를 얻을 수가 있다. 

이 부분은 내가 잘 모르겠다. ㅠㅠㅠ

참고로, hash index는 MySQL에서 제공하는 INDEX라고 한다.( B tree 기반이 아님)

MySQL의 경우, TABLE의 SCHEMA를 정의할 때, foreign key,primary key를 지정해 주면 index가 자동으로 생성이 되지

만, 다른 RDBMS에서도 자동으로 생성을 해주는지는 보장을 못한다.