사용하는 이유
Table에 데이터를 지속적으로 저장하게 되면 내부적으로 순서 없이 쌓이게 되는데 특정 조건을 만족하는 데이터를 찾고자 WHERE절을 사용한다면 Table의 row(record)를 처음부터 끝까지 모두 접근하여 검색조건과 일치하는지 비교하는 과정이 필요(Full Table Scan)
하지만 특정 column에 대한 Index를 생성해 놓은 경우 해당 속성에 대하여 search-key가 정렬되어 저장되어 있기 때문에 조건 검색(SELECT ~ WHERE) 속도가 굉장히 빠름
Index 구조
- Btree, B+tree, Hash, Bitmap으로 구현, 대부분 B+tree 구조
- index 생성 시 특정 column(속성, attribute)의 값을 기준으로 정렬하여 데이터의 물리적 위치와 함께 별도 파일에 저장
- 속성값 : search-key / 물리적 위치 : pointer
- table보다 적은 공간 차지
클러스터형 인덱스 (clustering index)
- 특정 column을 기본키로 지정하면 자동으로 클러스터형 인덱스가 생성되고, 해당 column 기준으로 정렬, Table 자체가 정렬된 하나의 Index인 것 (영어사전 처럼 책의 내용 자체가 정렬된 것)
보조 인덱스 (secondary index)
- 일반 책의 찾아보기와 같이 별도의 공간에 인덱스가 생성
- create index와 같이 index를 생성하거나 고유키로 지정하면 보조 인덱스가 생성
Index의 장단점
장점
- 검색 속도 향상
단점
- 추가 저장공간 필요, 보통 table 크기의 10% 정도의 공간 차지
- 느린 데이터 변경 작업, 검색이 아닌 데이터를 변경 할 때 성능 저하
-> B+tree 구조의 Index는 데이터가 추가 삭제될 때마다 tree의 구조가 변경되기 때문
어느 column에 사용할까?
where 절에서 자주 조회되고, 수정 빈도가 낮으며, 카디널리티는 높고, 선택도가 낮은 column을 선택해서 설정하는 것이 가장 좋음
| 기준 | 적합성 |
|---|---|
| 카디널리티(Cardinality) | 높을수록 적합 (데이터 중복이 적을수록 적합) |
| 선택도(Selectivity) | 낮을수록 적합 |
| 조회 활용도 | 높을수록 적합 (where 절에서 많이 사용되면 적합) |
| 수정 빈도 | 낮을수록 적합 |
효과적으로 사용하는 방법
- SELECT WHERE절에 사용되는 Column에 대해 index를 생성
- 데이터 수정 빈도가 낮을수록 적합. insert / update / delete 작업 시, 데이터 변화가 생기기 때문에 index에서는 매번 정렬을 다시해야 함
-> 부하가 발생하기 때문에 수정 빈도가 낮은 column을 index로 설정 - 데이터의 중복이 높은 column은 index 효과가 없음. 선택도가 낮을 때 유리 (보통 5~10% 이내)
-> ex) 성별처럼 종류가 2가지 밖에 없을 때 index 생성 X - 데이터의 양이 많을 수록 index로 인한 성능향상이 더 큼. 데이터 양이 적다면 index의 혜택보단 손해가 더 클 수 있음
- Join 조건으로 자주 사용되는 column의 경우
- 한 table에 index가 너무 많으면 데이터 수정 시 소요되는 시간이 너무 길어질 수 있음(4~5개 정도 권장)
