[DB] DB 인덱스 기초

 

 

인덱스를 쓰는이유

1. 조건에 맞는 데이터를 O(N)에서 O(logN)의 시간복잡도로 빠르게 검색할 수 있다.

2. 데이터 정렬(order by)하거나 그룹화(group by)를 더 빨리 하기위해

 

 

 

 

b-tree 기반 인덱스 동작방식

이진탐색을 통해 적은 데이터 탐색범위로 빠르게 찾는다.

 

table(a,b,c)

 

 

a 속성에만 인덱스 있는경우)

where a = 9;

-> 인덱스 스캔으로 빨리 조회

 

 

where a=9 AND b = 45;

-> a조건에 대해서는 빠른 인덱스 스캔, b조건에 대해서는 원본 데이터 테이블로 이동하여 b값비교, b값에 대해서는 full scan

 

 

 

(a,b) 복합 인덱스 생성한 경우

where a = 9;

-> 인덱스 스캔으로 빨리 조회

 

where a=9 AND b = 45;

-> a조건과 b조건 둘다 인덱스 스캔으로 빠른조회

 

 

where b = 17;

->  a를 기준으로 먼저 정렬된 뒤 b로 정렬된 것이라 b에 대해서는 정렬되어있지 않다. 따라서 단독 b조건에서는 인덱스 사용 불가.

 

where a=9 OR b = 17;

->  a에 대해서는 빠른 인덱스 스캔. but 마찬가지로 b에 대해 정렬되어있지 않아서 b조건은 풀스캔

 

 

 

 

 

 

 

 

 

인덱스 생성하기

CREATE INDEX [인덱스명] [테이블명](속성명)

테이블 새성시 index생성한다면 index이름 생략가능

복합키로 인덱스를 만들때 그 복합키가 UNIQUE하다면 UNIQUE INDEX로 생성한다

CREATE TABLE(
...


INDEX [인덱스명](생략가능) [속성명]
INDEX name
UNIQUE INDEX (team_id, backnumber)
)

 

나의쿼리가 사용하고있는 인덱스 확인하기

EXPLAIN [SQL]

특정인덱스를 사용하도록 명시하기

인덱스 추천

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

인덱스 강제

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

 

특정인덱스 사용못하게 하기

~  IGNORE INDEX ~

 

인덱스 사용시 주의할 점(불필요한 인덱스를 만들면 안되는 이유)

1.쓰기 작업에서 인덱스에도 반영해줘야하기 때문에 성능저하

 

2.인덱스용 테이블이 별도의 생성되어 디스크 용량을 더 많이 쓰게된다

 

커버링 인덱스

조회하는 속성을 인덱스가 모두 커버할때
원본 테이블을 거치지않고 인덱스만으로 데이터를 가져오기 때문에 속도가 더 빠르다.

 

그외 인덱스: 해쉬인덱스

-Hash 테이블 사용해서 인덱스 구현
-시간복잡도 O(1)
-Rehashing에 대한 부담
-Equality 비교만 가능 Range비교불가능

-복합인덱스의 경우 전체 속성에 대한 조회만 가능
-> 원래(a,b) 복합인덱스 있으면 a로만 쓸 수 있었지만 hash인덱스에서는 (a,b)로만 쓸 수 있음.

 

인덱스 보다 Full scan이 더 좋은 경우

1. table에 데이터가 적을때(10, 100개 이하)

2. 조회하려는 데이터가 전체 테이블의 상당부분(25%이상) 차지할 때