[DB] 인덱스(Index)로 DB 검색 속도를 높여보자

Index란

 인덱스는 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조입니다. SELECT 구문을 통해 데이터를 조회하려는 테이블이 너무 거대한 경우, 정렬되지 않은 모든 데이터를 순차적으로 검색(Full Scan)하면 조회 결과를 구하기까지 오랜 시간이 걸립니다. 이러한 경우, 어떤 데이터가 어디에 위치해있는지에 대한 정보를 책의 목차처럼 인덱스라는 것으로 저장해 검색 시에 활용하면 검색 성능을 높일 수 있습니다. 

 

 특정 컬럼에 인덱스를 걸면 해당 컬럼의 데이터들은 정렬되어 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장됩니다. 인덱스를 사용하면 DB를 순차 검색하기에 앞서 인덱스에 조회하고자 하는 데이터가 있는지 먼저 확인하고, 있다면 데이터의 물리적 주소로 이동해 데이터를 가져오며 그렇지 않다면 DB를 순차 검색하는 방식으로 동작합니다. 정렬된 데이터들로부터 데이터를 검색하기 때문에 일반적인 조회 속도보다 빠릅니다.

 

 

출처: https://mangkyu.tistory.com/96

 

 

 

인덱스의 장점

• 테이블의 조회 속도를 향상시킬 수 있습니다.
• 전반적인 시스템의 부하를 줄일 수 있습니다.

 

 

인덱스의 단점

• 인덱스를 저장하기 위해 DB의 약 10%에 해당하는 저장공간을 사용해야 합니다.
• 수정이 빈번한 속성에 인덱스를 사용할 경우 성능이 오히려 저하될 수 있습니다.
• 인덱스를 관리하기 위한 추가 작업이 필요합니다.

 

 

인덱스는 항상 데이터를 빠르게 검색하기 위해 가장 최근 버전 데이터들이 정렬된 상태로 유지되어야 합니다. 즉, 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELTE가 수행된다면 원본 테이블 뿐만 아니라 인덱스에도 다음과 같이 추가 작업을 수행해줘야 합니다.

 

• INSERT: 새로운 데이터를 인덱스에 추가합니다.
• DELETE: 삭제할 데이터의 인덱스를 사용하지 않는다고 처리합니다.
• UPDATE: 기존 인덱스를 사용하지 않는다고 처리하고, 갱신된 데이터에 대한 인덱스를 추가합니다.

 

그러므로 INSERT, DELETE, UPDATE가 빈번한 컬럼에 인덱스를 사용하면 실제 데이터보다 훨씬 더 많은 데이터가 인덱스에 저장될 수 있으므로 사용을 피해야만 합니다.

 

 

 

 

그렇다면 인덱스를 사용하기 좋은 컬럼은?

1. INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼

2. JOIN, WHERE, ORDER BY에 자주 사용되는 컬럼

3. 데이터의 중복도가 낮은 컬럼

4. 전체 데이터 중 10 ~ 15% 이하의 데이터를 처리하는 경우

 

 

인덱스를 사용하면 조건 검색 쿼리의 효율성 뿐만 아니라 정렬에서도 다른 이점이 존재합니다. 기본적으로 데이터 정렬은 굉장히 부하가 걸리는 작업인데, 인덱스는 미리 데이터를 정렬해 관리하기 때문에 Order by에 의한 정렬 과정을 생략할 수 있습니다. 또한 데이터가 이미 정렬되어 있으므로 최대값과 최소값을 인덱스 레코드의 시작값과 끝값으로 쉽게 가져올 수 있다는 부수적인 장점도 따라옵니다.

 

 

 3번에서도 언급했듯 인덱스에는 데이터의 카디널리티가 높은 컬럼을 선정하는 것이 좋습니다. 가령 남자와 여자라는 도메인만 있는 성별 컬럼에 인덱스를 걸었다고 가정해봅시다. 탐색할 수 있는 값이 2개 밖에 없으므로 검색할 값이 중복되고 그만큼 검색할 대상이 증가하게 됩니다. 그러므로 가능한 데이터의 중복도가 낮아 분포도가 높은 컬럼에 인덱스를 사용하는 것이 좋습니다. 같은 이유로 다중 컬럼을 인덱싱할 때에는 카디널리티가 높은 컬럼에서 낮은 컬럼 순으로 인덱싱해야 합니다.

 

 

 

 

 

인덱스의 분류

1. 클러스터 인덱스: 테이블의 PK에 적용되는 인덱스

2. 비클러스터 인덱스: 테이블의 PK 이외의 컬럼에 적용되는 인덱스

3. 유니크 인덱스: PK는 아니지만 Unique 속성을 가진 컬럼에 적용되는 인덱스 (Nullable할 수도 있다.)

 

대부분의 경우에는 PK에 인덱스를 걸어주는 것이 좋습니다.

 

 

 

 

 

인덱스의 자료 구조

B+Tree 인덱스

가장 많이 사용되는 인덱스의 구조로 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조입니다. B+Tree는 다음과 같은 요소로 구성되어 있습니다.

• Root node: 검색 경로의 출발점
• Leaf node: 실제 검색할 데이터가 저장된 노드
• Non-leaf node: Leaf node까지 가기 위해 다음 경로 노드의 Key 값을 가지고 있는 노드
• LinkedList: 부등호를 이용한 순차 검색 연산이 용이하도록 Leaf node들을 LinkedList로 연결

 

SELECT 쿼리를 날리면 Root node에서 시작하여 Non-Leaf nodes를 거쳐 Leaf node를 검색합니다.

 

 

 

 

 

 

 

 

해시테이블 인덱스

해시 테이블은 (Key, Value) 구조로 데이터를 관리하며 해시 함수를 기반으로 Key 값을 생성하는 자료구조입니다. Key에 해당하는 Value를 찾기 위해 해시 함수를 한 번만 실행하면 된다는 점에서 빠른 연산을 보장합니다.

 

 

해시 인덱스의 장점

• 트리 내에서 여러 노드를 읽어야만 데이터를 찾을 수 있는 B+Tree보다 검색 속도가 빠르다.
• 데이터에 해시 함수를 적용하여 저장하기 때문에 키 값의 크기가 줄어들어 가용성이 좋다.

 

해시 인덱스의 단점

• 부등호를 이용한 조건 조회가 불가능하다.

 

해시 인덱스는 해시 함수를 기반으로 데이터를 검색하기 때문에 데이터를 정렬하지 않습니다. 그러므로 부등호를 사용한 검색 조건에 있어서는 어떤 방법으로도 해시 인덱스를 활용할 수 없습니다. 

 

 

출처: https://goodgid.github.io/Hash-Table/

 

 

 

 

인덱스 사용 명령어

인덱스 생성

# CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3, ...);
CREATE INDEX A_INDEX ON A(AID, TITLE);

 

 

인덱스 조회

SELECT * FROM A_INDEX WHERE AID = 3L;

 

 

인덱스 삭제

DROP INDEX A_INDEX;

 

 

너무 많은 인덱스를 생성하는 것은 INSERT, UPDATE, DELETE 시 부하가 발생해 전체적인 DB 성능을 저하시키는 요인이 될 수 있습니다. 그러므로 안 쓰는 인덱스는 즉시 삭제하는 것이 좋습니다.

 

 

 

 

 

 

 

 

 

참고:

 

[Database] 인덱스(index)란?

[DB] 데이터베이스 인덱스(Index) 란 무엇인가?

[데이터베이스] 인덱스란? (실습 포함)

데이터베이스 INDEX 개념과 INDEX 사용방법 알아보자. – by.SC

[DB] 인덱스(Index) 사용 예시 (생성, 조회, 삭제, 리빌드)