SQL 인덱스(Index) 정리

1. 개념

인덱스란?
말그대로 INDEX = 색인을 의미한다.
책에서 단어의 위치를 찾으려면 책을 전부 다 읽어야 하지만 가나다 순으로 색인을 만들어놓으면 쉽게 찾을 수 있다.
그러나 색인을 위한 별도 페이지를 만들어야 하므로, 책이 두꺼워진다. ( DB에서 차지하는 용량이 커짐 )

2. 사용

테이블에 인덱스가 있으면
어떤 데이터를 찾을 때 미리 만들어둔 인덱스를 먼저 탐색한다.
인덱스가 없으면 디스크 저장소에 바로 접근한다.
인덱스는  특정 컬럼을 키 값으로 메모리 영역에 트리 구조로 저장된다.
인덱스가 있으면 메모리 저장소에 있는 인덱스를 먼저 조회해서 빠르게 해당 데이터를 가져올 수 있다.
이는 SELECT 성능을 향상시킨다. (검색 속도 빨라짐)
인덱스는 항상 정렬된 상태를 유지하기 때문이다.
그러나 데이터를 추가/삭제할때마다 자료구조가 정렬되기 때문에 INSERT, UPDATE, DELETE 성능은 떨어진다.
 

3. 종류

1) 데이터 저장 방식에 따른 구분

클러스터드 인덱스	넌-클러스터드 (non-clustered) 인덱스
인접한 데이터들을 한곳으로 모았다는 뜻 pk 설정 시 자동으로 클러스터드 인덱스로 만들어진다 테이블 당 1개씩만 허용한다 물리적인 데이터를 가진다 항상 정렬된 상태를 유지하고 노드 내에서도 정렬되어 있다 넌 클러스터드 인덱스에 비해 SELECT 속도가 빠르지만 INSERT/UPDATE/DELETE 속도는 더 느림	UNIQUE로 설정된 컬럼에 자동으로 생성된다 인덱스 페이지는 로그 파일에 저장된다 레코드의 원본은 정렬되지 않고 인덱스 페이지만 정렬된다.

 

Primary Index	Secondary Index
기본 키를 기반으로 만들어진 인덱스 기본키는 (pk) 하나만 존재할 수있기 때문에 primary index도 하나만 존재함	기본 키는 아니지만 성능 향상을 목적으로 임의의 컬럼을 지정해서 만든 인덱스 여러개가 존재할 수 있다

 
2) 자료구조에 따른 분류

B - Tree 인덱스	Hash 인덱스	Fractal - Tree 인덱스
가장 많이 사용되는 구조 컬럼의 값을 바꾸지 않고 구조체 내에서 항상 정렬된 상태를 유지 최상위에 루트 노드가 존재하고 하위에 브랜치 노드, 마지막에 리프 노드로 되어있다 부모 노드를 기준으로 왼쪽 자식 노드는 오른쪽 자식 노드보다 더 작은 값을 가짐	컬럼 값을 해싱해서 사용하며 검색기능이 매우 빠름 컬럼 값과 인덱스 값이 일치하지 않기 떄문에 문자열 검색과 같은 일부 일치에 대해 검색 못함	B - Tree의 단점을 보완하기 위해 만듬 컬럼 값을 바꾸지 않고 데이터의 저장/삭제 처리 비용을 많이 줄임

 

4. 인덱스 사용 시 고려사항

 1) 인덱스의 갯수는 3~4개가 적당하다. 
인덱스가 너무 많으면 생기는 이슈들이다.

• 데이터를 삽입/수정/삭제할때마다 인덱스도 같이 해줘야해서 성능이 떨어질 수 있다.
• 데이터 insert할때 마다 인덱스가 같이 추가되어 더 많은 메모리를 차지한다.

2)  인덱스는 일반적으로 단 하나의 데이터를 구할 때 가장 효율적이다.
  여러 개의 데이터를 구한다면 인덱스를 통해 레코드의 주소를 찾아 데이터의 레코드를 읽는 작업을 반복해야 한다.
그래서 만약 많은 레코드를 한번에 조회한다면 오히려 인덱스를 사용하지 않고 직접 테이블을 읽는 것이 더 효율적일 수 있다.
ex) 테이블에 10 개의 데이터가 존재하고, 5건의 데이터를 읽는 경우 인덱스를 통하는 것보다 테이블을 직접 읽는 게 효율적..
일반적으로 DBMS 의 옵티마이저는 인덱스를 사용해 레코드 1건을 읽는 것이 테이블에서 직접 읽는 것보다 4 ~ 5배 정도 비용이 더 많이 든다고 예측함
그러므로 인덱스를 통해 읽어야 할 레코드가 전체 테이블의 20 ~ 25% 이상이라면 직접 테이블을 읽는 것이 효율적이다.
 

5. 인덱스 만들기 적절한 컬럼은?

 - 인덱스는 카디날리티가(Cardinality) 높은 컬럼에 지정하는것이 좋다.
- 카디날리티가 높다 => 데이터의 중복이 적다는 뜻
- 대표적 예시 : 주민번호, ID (절대 중복 안됨)
- 반대의 예시 : 성별 (중복됨)