DB Index 이해하기

인덱스란 무엇인가

인덱스란, 별도의 저장 공간을 사용하여, 데이터베이스의 검색 속도 높여 주는 장치이다.

인덱스가 어떻게 생성되는 지, 왜 검색 속도를 높여주는 지 예시를 통해서 알아보자.

오른쪽 테이블에는 물리적 주소와 이름과 email 값이 저장되어 있다. 인덱스가 존재 하지 않는 상황에서, 이름이 SMITH인 사람의 Email을 모두 알아낸다고 가정하자. 인덱스가 없다면, 맨 위부터 100000번째 아래 데이터를 모두 훑으며 이름이 SMITH인 데이터를 일일이 발견해야 한다.

하지만 인덱스를 만들어 이름이 SMITH인 Eamil을 찾는 과정을 알아보자. 먼저 인덱스 값으로 사용할 이름과 실제 데이터 위치를 나타내주는 물리적 주소 값을 가져온다. 그리고 이름 값들을 알파벳 순으로 정렬한다. 정렬되어 있는 이름 목록에서 이름이 SMITH인 물리적 주소 값을 빠르게 찾아낸다. 찾아낸 주소 값과 오른쪽 테이블의 주소 값이 일치하는 데이터에 직접 접근하여 빠르게 eamil 값을 찾아낼 수 있다.

→

인덱스의 가장 큰 장점은 인덱스로 설정된 값이 정렬되어 저장되는 것이다. WHERE 절 특정 조건에 맞는 데이터를 찾아낼 때, 정렬되어 있기 때문에 조건에 맞는 데이터를 훨씬 빠르게 찾아낼 수 있는 것이다.

인덱스 자료구조 B-Tree 알아보기

빠른 검색을 도와주는 인덱스가 사용한 자료구조를 결론부터 말하면,

균형 잡힌 B-Tree 자료구조로 구축되어있어서 탐색에 평균 O(logN) 시간이 걸리며 트리 생성 시 대수 확장성이란 특징으로 인해 더 빠른 시간 안에 많은 양의 데이터를 빠르게 찾을 수 있다.

B-Tree(balanced Tree)란

• B Tree는 이진 탐색 트리에서 발전되어 모든 리프 노드들이 같은 레벨에 위치할 수 있도록 자동으로 균형을 맞추는 트리이다.
• 이진 트리와 다르게, 하나의 노드는 2개 보다 큰 많은 자식 노드를 가실 수 있다.
• 노드의 데이터는 반드시 정렬된 상태이다. 자식 노드의 데이터는 부모 노드 데이터 기준으로 작은 쪽은 왼쪽 서브 트리에 큰 값은 오른쪽 서브 트리에 위치한다.

그래서 57이라는 값을 찾을 때, 정렬된 값을 기반으로 57 값을 가지고 있는 리프 노드까지 빠르게 찾을 수 있다.

대수확장성으로 짧은 검색 가능

대수 확장성이란, 트리 깊이가 리프 노드 수에 비해 매우 느리게 성장하는 것을 의미한다.

• 트리 깊이는 루트 노드부터 특정 노드까지 최단 거리로 갔을 때의 거리를 의미하고, 깊이가 짧을 수록, 트리의 검색 속도 빨라진다.

그래서 적은 깊이 수(빠른 검색)에 많은 인덱스를 저장할 수 있는 장점이 있다.

인덱스 주의 사항

인덱스를 사용하면 장점만 있는 것이 아니다. 인덱스를 사용하려면, 인덱스를 항상 정렬된 상태를 유지해줘야 한다는 단점이 있다. 

INSERT, UPDATE, DELETE를 사용해서, 인덱스 데이터가 변경되면, 인덱스 자료구조에 있는 값들도 다시 정렬해줘야 한다. 그렇기 때문에, 빈번히 INSERT, UPDATE, DELETE를 사용하는 테이블이라면 인덱스 정렬 문제를 고려해봐야 한다.

또한 인덱스를 많이 생성하게 되면 다양한 조건에서 빠른 검색은 할 수 있지만, 인덱스가 쌓이게 되면 전체적인 데이터베이스 성능 부하를 초래한다.

인덱스 생성 전략

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생되지 않는 컬럼
• JOIN, WHRER그리고 ORDER BY(정렬)에 자주 사용되는 컬럼
• 데이터 중복도가 낮은 컬럼
• 항상 = 으로 비교되는 컬럼

MySql 인덱스 생성 방법

1 인덱스 추가로 생성

CREATE INDEX <인덱스명> ON <테이블명> ( 칼럼명1, 칼럼명2, ... )

ex)CREATE INDEX idx_name ON books (writer);

2 테이블 생성시 인덱스 생성

INDEX <인덱스명> ( 칼럼명1, 칼럼명2 ) 

UNIQUE INDEX <인덱스명> ( 칼럼명 )  --> 항상 유일해야 한다.

CREATE TABLE `books` (
       `id` varchar(5) NOT NULL,
       `name` varchar(20) NOT NULL,
       `writer` varchar(20) NOT NULL,
       `price` decimal(10,2) DEFAULT '0.00',
       PRIMARY KEY (`id`),
       INDEX `idx_test` (`writer`)
     );

3 인덱스 조회

SHOW INDEX FROM <테이블명>

ex) SHOW INDEX FROM books

4 인덱스 삭제

ALTER TABLE <테이블명> DROP INDEX <인덱스명>

---

Reference

https://choicode.tistory.com/27

https://coding-factory.tistory.com/746

https://spiderwebcoding.tistory.com/6