22. MySQL - index

DB에서 index를 쓰는 이유

- 조건을 만족하는 튜플(들)을 빠르게 조회하기 위해서

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

 

대부분 B-tree 기반으로 동작하는 index가 많음.

시간복잡도는 O(logn)

 

인덱스 거는 방법

기존 테이블에 index를 사용하고 싶을 때

name 이라는 conttribute는 동명이인이 있을 수 있기 때문에 그냥 INDEX 명령어로 처리하는 것이고, team_id와 backnumber는 중복값이 있을 수 없기 때문에 (같은 팀에 같은 등번호 X) UNIQUE INDEX 명령어로 처리함.

 

테이블을 생성하면서 인덱스를 만들 때

예시의 UNIQUE INDEX처럼, 두 개 이상의 attribute로 구성된 인덱스를 multicolumn index 또는 composite index 라고 함.

primary key에는 index가 자동 생성됨.

 

사용하는 query에 맞춰서 적절하게 index를 걸어줘야 query가 빠르게 처리될 수 있음.

 

어떤 인덱스를 사용하는지 알아보고 싶으면, sql문 맨 앞에 EXPLAIN 키워드를 사용해서 볼 수 있음.

 

인덱스를 직접 선택하지 않으면, DBMS의 optimizer가 알아서 적절하게 인덱스를 선택함.

직접 인덱스를 고르고 싶다면 USE INDEX 라는 키워드를 사용해서 쿼리문에서 명시해줄 수 있음.

단, USE INDEX는 이 인덱스를 쓰라고 권장하는 느낌의 키워드임. 그 인덱스를 사용하지 않으면 full scan으로 동작함.

FORCE INDEX 키워드를 사용하면, 원하는 데이터를 얻지 못하는 경우(optimizer 판단) 이외의 거의 모든 상황에 명시한

인덱스를 사용함.

 

index는 추가적인 저장 공간을 차지하고, table에 write할 때마다 index도 변경이 발생하기 때문에 불필요한 index는 만들지 말아야 함.

 

covering index

 

B tree 기반의 index가 아닌, Hash index

- hash table을 사용하여 index 구현

- 시간복잡도 O(1)의 성능

- rehashing에 대한 부담

- equality 비교만 가능, range 비교 불가능

- multicolumn index의 경우 전체 attributes에 대한 조회만 가능

 

Full scan이 더 좋은 경우

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

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

 

그 외

- order by나 group by에도 index가 사용될 수 있음

- foreign key에는 index가 자동으로 생성되지 않을 수 있음 (join 관련)

- 이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우, 시간이 오래 소요될 수 있고 DB 성능에 안좋은 영향을 줄 수 있음