[SQL] 인덱스(INDEX)

 인덱스는 개발자분들이라면 한번 쯤은 보셨거나 면접에서 질문을 받으신 적이 있는 주제이실겁니다. 저 역시 SQLD를 준비할 때 인덱스 관련 문제를 풀었던 기억이 있습니다. 인덱스가 얼마나 중요하길래 이렇게 자주 언급되지는 알아보도록 하겠습니다.

※ 제 블로그의 포스트들은 제가 부족하여 공부하기 위해서 작성하는 것입니다. 

잘못된 부분이나 추가해야할 내용이 있다면 언제든지 댓글 부탁드리겠습니다.

인덱스(INDEX) 란

인덱스 = 색인 = 정렬 = 목차

RDBMS의 검색 속도를 높이기 위해 사용하는 기술이다. 

인덱스 키를 기준으로 정렬된 데이터를 메모리에 파일 형태로 생성한다. 이후 우선적으로 인덱스 파일을 검색하기 때문에, 테이블 풀스캔을 하지 않아서 검색 성능이 향상된다.

- B-Tree(Balanced Tree) 구조로써 [Root -> Branch -> Leaf -> 디스크 저장소] 순서로 탐색한다.

- 즉, 인덱스 성능이 좋다는 것은

디스크 접근(I/O)이 줄어든다 = Root~leaf 까지의 왕복 횟수가 적다 = 검색 속도가 빠르다

이다.

- 인덱스는 메모리에 있는 파일로써, 페이지 단위(Page =16KB)로 관리된다.    

＊페이지(Page) : 메모리에 데이터를 저장하는 가장 기본 단위

- 인덱스의 키값은 작게, 개수는 3~4개가 적당하다. 

인덱스 키 값이 크면 메모리 공간이 낭비되고, 개수가 너무 많으면 옵티마이저가 오동작 할 수 있다.

- SELECT 시에는 성능이 향상되지만, INSERT/UPDATE/DELETE 시에는 인덱스 파일도 추가/수정/삭제가 되므로 성능이 저하될 수 있다.

- PK는 인덱스가 자동 생성 된다.

- 인덱스가 여러 컬럼일 경우, 두번째 컬럼 부터는 상위 컬럼에 의해서 종속된다.

(첫번째 인덱스를 우선 적용한 이후에, 그 안에서 같은 값인 열에서만 두번째 인덱스가 적용됨)

인덱스 사용 장점

 

- 데이터 검색 속도 향상

미리 정렬된 데이터 내에서 탐색하므로 SELECT 쿼리 속도가 향상된다

- 전반적인 시스템 성능 향상

인덱스를 사용하면, 디스크 I/O가 줄어들어 시스템 부하가 감소한다. 

인덱스 사용 단점

  

- 레코드 삽입/수정/삭제 성능 감소

데이터 변경 작업이 빈번한 경우, 인덱스 파일도 같이 수정해주어야 하므로 부하가 증가한다.

- 추가적인 공간 필요

인덱스를 생성하기 위해서 DB의 약 10% 내외의 저장 공간이 필요하다. (저장 성능을 희생하고 읽기 속도를 높이는 기능이다.)

- 인덱스 생성 시 많은 시간 소요

인덱스 선정 기준

  

카디널리티(Cardinality)가 가장 높은 것

인덱스 키가 여러개일 경우, 카디널리티 높음 -> 낮음 순서로 구성한다.

카디널리티(Cardinality) 란?

집합원의 개수. 해당 컬럼의 중복된 수치.

: 데이터를 어떤 기준으로 나눴을 때, 그 기준 내에서 분류되는 집합의 수

카디널리티가 낮다 : 성별, 부서, 지역처럼 포함되는 값이 많은 포괄적인 기준

카디널리티가 높다 : 주민번호, 계좌번호, 사원번호처럼 비교적 고유한 값을 가지는 기준 (Unique)

[이미지 설명]

ProductID(PID)의 경우는 상품의 고유값이므로 분류 개수가 많으므로 카디널리티가 높다.

컬러나 카테고리는 겹치는 값이 많아서 분류 수가 적으므로 카디널리티가 낮다.

인덱스의 종류

(1) 클러스터형 인덱스 (Clustered Index)

물리적으로 행을 재배열한다. 인덱스로 정렬된 순서가 데이터 조회 시의 실제 순서와 일치한다.

실제 DB의 데이터 파일에 정렬이 된 상태가 디스크에 저장이 된다. (인덱스 자체가 데이터)

- 테이블 당 1 개만 생성 가능하다.

- 인덱스 페이지 용량이 논 클러스터보다 작다. 

leaf 레벨의 인덱스 테이블이 별도로 필요 없기 때문이다.

- 물리적으로 정렬되어 있으므로, 논 클러스터에 비해 IO가 빠르다.

- 영어사전과 비슷한 개념이다. 

- 행 데이터를 인덱스로 지정한 열에 맞춰 자동정렬한다.

2) 논클러스터형 인덱스(Non-clustered Index)

물리적으로 재배열 하지 않는다. 인덱스 테이블을 별도로 생성하여 데이터를 정렬한다.

- 테이블 당 여러개를 생성 가능하다.

- 클러스터 페이지보다 용량이 크다.

- 일반 책의 찾아보기와 비슷한 개념

- 인덱스 자체가 데이터가 아니라 인덱스가 가리키는 것이 데이터

- 범위 당 3% 이내일 때 효율적이다.

참고

http://asfirstalways.tistory.com/333

https://jojoldu.tistory.com/243

https://lalwr.blogspot.com/2016/02/db-index.html

http://www.dbguide.net/knowledge.db?cmd=view&boardConfigUid=30&boardUid=142699&boardStep=1

https://lng1982.tistory.com/144