DB Index

지식저장공간·2023년 6월 5일

데이터베이스

DB

목록 보기

16/19

Index

Index가 중요한 이유

Index가 없다면

데이터베이스 조회시 where에 있는 조건에 index가 설정되어있지 않다면, 데이터베이스는 tuple을 하나 하나씩 읽는다.

이러한 행위를 full scan(=table)이라 하며 시간복잡도는 O(N)

Index가 있다면

시간 복잡도 O(logN), full scan보다 더 빨리 찾을 수 있다.

Index를 사용하는 이유

조건을 만족하는 튜플(들)을 빠르게 조회하기 위해!
또는 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해!

Index 생성

테이블 생성 후 인덱스 설정

CREATE INDEX 인덱스명 ON 테이블명 (attribute 명)
-> CREATE INDEX player_name_idx ON player (name)
-> CREATE UNIQUE INDEX player_id_backnumber_idx ON player (id, backnumber)

UNIQUE INDEX : 인덱스로 설정한 attribute에 의해 tuple이 고유하게 존재한다.

테이블 생성 시 인덱스 설정

CREATE TABLE 테이블명 (

...
INDEX 인덱스명 (attribute),
UNIQUE INDEX 인덱스명 (id, backnumber)
-- composite index, multicolumn index라 한다.

);

대부분의 RDBMS에서는 primary key를 생성할 경우 index가 자동 생성된다.

SHOW INDEX FROM 테이블명;
-- 테이블에 설정된 INDEX를 파악할 수 있다.

INDEX 동작 방식(B-tree)

index로 설정된 값들이 B-tree 구조로 정렬되어 있으며, 값과 포인터를 가지고 있다.

binary search

예제1

where 조건에 해당하는 값이 1개인 경우

Index로 설정되어있는 attribute는 정렬되어있기 때문에, binary search, 이진탐색으로 where절 조건을 검색한다.

조건을 만족했다해서 끝이나는게 아닌, 이진탐색을 계속 실행하여 조건을 만족하는 값을 찾고, 조건에 만족하는 값이 없으면 이진탐색을 멈춘다.

예제2

where 조건에 해당하는 값이 여러개인 경우

index로 설정된(a) attribute에서 조건을 만족하는값을 찾은 후 pointer를 이용하여 b의 값을 확인하고, 이진탐색으로 a의 값을 만족하는것이 있는지 계속해서 확인한다.

a에 대해서 index만 설정되어있기 때문에 조건을 만족하는 a는 빠르게 찾을 수 있지만, b는 정렬 및 index가 되어있지 않기 때문에 b의값을 search하는것이 오래걸린다. 즉, b의 값을 찾을때 full scan이 발생한다.

예제3

Index로 a,b를 동시에 사용하는 경우

a의 값이 정렬된 후 b의 값이 정렬된다.

a를 이진탐색한 후, b에 대해 이진탐색을 실행한다. a와 b를 둘다 모두 이진탐색하기 때문에 조회 성능이 향상된다.

예제4

Index로 a,b가 설정되어있지만, b로 검색하는경우

a에 대해 우선정렬이 되어있기 때문에, b에 대해서는 정확하게 정렬이 되어있지 않다. 때문에 인덱스를 사용하지 않거나 성능이 보장되지 않는다.

b에 대해서만 따로 index를 설정하여 두는것이 좋다.

예제5

1번 쿼리, 2번 쿼리 모두 team_id가 우선 정렬되어있기 때문에 조회성능이 높다.

하지만, 3번 쿼리 같은 경우 backnumber에 대해서는 후 순위로 정렬되어있기 때문에 성능이 보장되지 않는다.

4번 쿼리의 경우 조건에 OR가 존재하기 때문에, 모든 경우의 수를 생각한다 때문에 결국 full scan을 한다.

예제 정리

★사용되는 query에 맞게 적절한 index를 걸어줘야 빠른 조회성능을 갖출 수 있다.

해당 쿼리가 어떻게 동작하는지 알고 싶은 경우

EXPLAIN select * from player where ...;

RDBMS에서 해당 쿼리가 어떻게 동작하는지 보여준다.

optimizer가 적절한 index를 선택하여 조호한다.

만약 EXPLAIN 후 optimizer가 적절한 index를 사용하지 않는경우

select * from player use index(인덱스명) where ...;
-- 권장사항느낌. 해당 인덱스를 사용해주세요
-- optimizer가 해당 인덱스를 사용하지 않을 수 도있다.

select * from player force index(인덱스명) where ...;
-- 무조건 해당 인덱스를 사용해라.