서두
DB에 인덱스를 걸어서.. 라는 말과 백엔드분이 이를 통해 성능최적화를 했단 말도 듣고,
최근에 스키마를 작성할 일이 생겨 인덱스를 건다는건 뭘까? 하고
궁금해져 공부한 내용을 정리하려고 한다!
DB에서 인덱스를 건다는게 뭘까 ?
인덱스를 건다는 건,
데이터베이스에서 특정 값을 빠르게 찾을 수 있게
기존 데이터를 정렬해놓아 효율적으로 검색할 수 있도록 하는것을 말한다.
인덱스를 걸지 않았을 때
SELECT 문으로 데이터를 찾을 때 index가 걸려있지 않다면
DB 데이터 전체를 확인하며(full scan) 데이터를 찾아야 한다 O(M)
인덱스가 걸려 있을 때
B-tree based index로 걸려있다면 O(logN)
사용하는 이유
- 조건을 만족하는 데이터를 빠르게 조회
- 빠르게 정렬(order by), 그룹핑(group by)
B tree 기반 인덱스 동작 방식
B Tree 구조
- 트리 높이가 같음
- 자식 노드를 2개 이상 가질 수 있음
- 기본 데이터베이스 인덱스 구조 (InnoDB)
WHERE a = 7 AND b = 95위와 같은 조건이 있다면,
INDEX가 걸려있는 A에 관해서 a = 7 과 일치하는 값을 찾고
b에 대한건 full scan을 통해 값을 찾는다.
이 방법을 해결하려면 a와 b를 하나로 묶은 인덱스가 존재해야 한다.
하나로 묶은 인덱스를 생성하게된다면 CREATE INDEX(a,b)
- a 기준으로 정렬되는 인덱스가 생성이 된다.
- a의 값이 같다면 그 안에서 b를 기준으로 정렬이 된다.
멀티플 컬럼 인덱스를 만들게 된다면 attribute의 순서가 중요하다 !!
조건이 두개라면 두개를 섞은 인덱스를 생성하는게 성능이 훨씬 좋다는 점
Index 거는법
// mysql
CREATE INDEX {index_name} ON {table} {(attribute)};조건이 유니크 한 경우 (and)
조건이 2개일 경우 2개의 조건을 하나로 합쳐서 Index를 걸어준다
테이블에서 하나의 값만 유니크하게 식별할 수 있을 때
CREATE UNIQUE INDEX {index name} ON {table} {(attribute)}테이블을 생성하면서 Index 걸 때
CREATE TABLE ____
INDEX {index_name}{(attribute)} // 동일한 값을 찾을 수 있는 경우에는 UNIQUE X
UNIQUE INDEX {index_name} (attribute)테이블을 생성하면서 index 걸 때는 index_name 생략 가능 => 자동으로 만들어줌
💡
- 두개 이상의 attribute로 구성 된 Index를
multicolumn index또는composite index라고 부른다
- 인덱스에서 첫 번째 열의 값을 기반으로 정렬한 후, 동일한 값이 있는 경우 두 번째 열의 값을 기준으로 정렬
- 이를 통해 다중 조건으로 검색할 때 효율적인 인덱스를 생성
- primary key에는 자동으로 index가 생성된다
Index 조회
SHOW INDEX FROM {table}쿼리가 어떤 인덱스를 쓰는지 조회하는 방법
EXPLAIN
SELECT * FROM {table} WHERE {condition}결과 값으로 사용가능한 키와 실제로 사용ㅎ나 키를 알려준다.
optimizer가 알아서 적절하게 index를 선택함
간혹 잘못 index가 걸려있다면, 직접 index를 고를 수 있다.
EXPLAIN
1. SELECT * FROM {table} USE INDEX {(index_name)}
2. SELECT * FROM {table} FORCE INDEX {(index_name)}
WHERE {condition}-
USE INDEX : 가급적 이 인덱스를 써주세요
-
FORCE INDEX : 이 인덱스 써주세요.
optimizer가 FORCE INDEX를 사용해서 데이터를 못가지고 올 경우에는 full scan으로 데이터를 가져옴
Ohters
Covering index
- 조회하는 attribute를 index가 모두 cover할 때
- 조회성능이 더 빠름
Full scan이 더 좋은 경우
- table에 데이터가 조금 있을 때 (XX~XXX)
- 조회하려는 데이터가 테이블의 상당 부분을 차지할 때
🚨 DB성능
이미 데이터가 몇 백만건 이상 있는 테이블에 인덱스를 생성하는 경우
시간이 몇 분이상 소요될 수 있고 DB 성능에 안좋은 영향을 줄 수 있다.
인덱스를 많이 만들면 안되는 이유
실제 테이블 데이터 외에도 index를 위한 부가적인 데이터들도 생성이 된다.
불필요한 index를 만들면 안됨
염두해야 할 사항
- 실제 테이블에 write를 할때마다 index도 변경이 발생
- 추가적인 저장 공간 차지
결론
사용되는 query에 맞춰서 적절하게 index를 걸어줘야 query가 빠르게 처리될 수 있다.
attribute 순서에 따라서 index 정렬이 달라지니 필요한 조건에 맞추어서
index를 알맞게 걸어주어야 한다!
