인덱스
자료구조 책을 샀다.
스택이라는 단어가 나온 부분을 찾는다면, 어떻게 찾을 것인가?
대부분 책의 목차나, 책의 맨 뒤 찾아보기와 같은 곳을 통해서 찾을 것이다.
데이터베이스에서 데이터를 빠르게 찾아내기 위한 자료구조
종류
Clustered Index 와 Non-Clustered Index
| Clusterd Index | Non-Cluesterd Index |
|---|---|
| 물리적 정렬 | 논리적 정렬 |
| 테이블 당 1개 | 테이블 당 여러개 |
Clustered Index는 데이터가 물리적으로 정렬되어 있다.
따라서 한 테이블에 여러 개를 만들 수 없다.
하지만, NonClusterd Index는 데이터를 물리적으로 정렬하는 것이 아닌, 논리적 정렬이다.
즉 별도의 인덱스 테이블을 두고 키와 데이터의 위치를 매핑하므로 하나의 테이블에도 여러 개를 생성할 수 있다.
책을 기준으로 생각해 보자면,
특정한 기준으로 내용 자체가 순서대로 정렬되어 있다. (물리적)
하지만 책 뒤의 찾아보기를 보면 어떤 내용이 몇 페이지에 있는지 기록되어 있다. (논리적)기본 인덱스(Primary Index)와 보조 인덱스(Secondary Index)
- 기본 인덱스 : 기본 키(pk)에 대해 인덱스를 생성한 것
- 보조 인덱스 : 기본 키(pk)가 아닌 다른 열(column)에 대해 인덱스를 생성한 것
생성, 확인, 삭제
테이블에 PK로 걸려있는 항목의 경우, 자동적으로 생성이 된다.
별도로 생성/확인/삭제 하고자 한다면 다음과 같다.
-- 생성
-- CREATE INDEX 인덱스명 ON 테이블명(칼럼명...)
CREATE INDEX myindex ON city(country_id);
-- 확인
-- SHOW INDEX FROM 테이블명
SHOW INDEX FROM city;
-- 삭제
-- DROP INDEX 인덱스명 ON 테이블명
DROP INDEX myindex ON city생성 시, 여러 칼럼에 대해서 복합인덱스를 구성할 수 있다.
Column1에 대해서 정렬작업이 완료된 후, Column2에 대한 정렬 작업이 시작된다. 순서에 주의 하여야 한다.
인덱스 확인 시 아래처럼 해당 테이블의 인덱스 종류와 인덱스 타입에 대해서 알 수 있다.
MySQL의 경우 기본적으로 BTREE를 활용하고, 타입은 변경할 수 있다.
언제 사용할까?
Index는 많다고 좋은것도 아니며, 없다고 좋은것도 아니다.
아래 항목에 항상 주의하며 인덱스를 사용해야 한다.
1. WHERE 절
WHERE 절에 사용되는 열에 인덱스를 만들어야 한다.
다만, WHERE 절에 사용이 되지만, 자주 사용하지 않는다면 가치가 없다.
그 이유는 인덱스를 생성한다는 것은 별도의 인덱스 페이지를 가지게 되는데, 자주 사용하지 않는다면 인덱스 페이지를 만드는 시간과 메모리적으로 손해이기 때문이다.
2. 중복도가 높다면, 효과가 없다.
만약 고객 테이블에 성별이라는 칼럼에 Index를 생성한다고 하자.
어차피 남성인 M과 여성인 F 두 가지 밖에 없다.
3.Join에 자주 사용되는 칼럼에 만드는것이 좋다.
1번 항목과 비슷한 이치이다.
4. 삽입/갱신/삭제가 얼마나 빈번한지 고려해야 한다.
인덱스는 조회시에만 성능을 향상시킨다.
삽입/갱신/삭제가 빈번하다면, 인덱스 테이블의 변경 또는 물리적 재배열과 같은 작업이 발생하기 때문에 위 작업이 빈번하다면 생성하지 않는것이 좋다.
즉, 요약하자면 인덱스를 걸고자 하는 데이터가 전체의 일부분이어서 잘 특정할 수 있거나, 조건으로 자주 사용되는 Column일 경우 유용하다.
실행계획
Index를 잘 걸기 위해서, 또는 나의 계획대로 수행되는지 확인하기 위한 가장 좋은 방법은 실행 계획을 보는것이다.
실제 쿼리는 이렇게 간단하지 않음에 유의!
index가 아무것도 없는 상태이다.
SELECT * FROM test WHERE country_id > 90;
Full Table Scan, 즉 테이블 전체를 스캔해서 결과를 가져온다.
이제 country_id에 Index를 생성하고 실행계획을 다시보자.
Index Range Scan, 즉 인덱스 범위 스캔을 통해 가져온다.
몇개의 행을 살펴볼지, 예상 비용 등을 알 수 있다.
Sample DB의 사이즈가 크지 않아 index의 유무 차이가 크지 않으나, DB의 사이즈가 커질 수록 극단적으로 차이가 난다.
여기서는 단순하게 확인을 한 것이기 때문에 극단적인 예시일뿐, 실행 계획을 반드시 공부하자.
이어지는 포스팅에서는 Index 구조인 BTree와 실행계획 순으로 작성할까 한다.
