그동안 SQL에 있어 인덱스가 중요하다는 것은 알고 있었지만 깊게 공부해본 적은 없었다. 마침 <이것이 MySQL이다>라는 책에 인덱스의 개념이 잘 나와 있어서 공부한 내용을 바탕으로 포스팅을 정리해본다.

인덱스란 무엇인가?

인덱스(index)의 정의

인덱스는 검색 성능을 높이는 자료구조다. 일상 생활에서 예시를 찾아본다면 책의 <찾아보기>와 같은 페이지라고 할 수 있다. 어떤 단어가 포함된 페이지를 찾을 때 책이 두껍다면 처음부터 끝까지 찾는 것은 오랜 시간이 걸리기 때문에 <찾아보기>에서 먼저 해당하는 단어를 찾은 후 단어가 포함된 페이지로 가면 시간을 절약할 수 있다. 인덱스는 이와 같은 원리의 자료구조다.

인덱스의 종류

인덱스의 종류는 크게 클러스터 인덱스(clustered index)와 보조 인덱스(secondary index)로 나눌 수 있다. (일부 DBMS에서는 보조 인덱스를 비 클러스터 인덱스라고 부르기도 한다.)

클러스터형 인덱스는 테이블 당 하나만 생성된다. Primary Key 또는 Unique 제약조건을 걸면 해당 키를 기준으로 자동으로 인덱스가 생성되는 것이다.

보조 인덱스는 테이블 당 여러 개를 생성할 수 있다. Unique 제약 조건을 걸면 된다. 만약 Primary Key 없이 Unique Key만 지정하고 이때 Not Null이 포함하면 클러스터형 인덱스로 지정할 수 있다. (Primary Key가 있다면 해당 키를 기준으로 클러스터 인덱스가 생성된다.)

정리하면 다음과 같다.

• PRIMARY KEY로 지정한 열은 클러스터형 인덱스가 생성된다.
• UNIQUE로 지정한 열은 보조 인덱스가 생성된다.
• PRIMARY KEY가 없다면 UNIQUE NOT NULL로 지정한 열은 클러스터형 인덱스가 생성된다.
• PRIMARY KEY와 UNIQUE NOT NULL이 있으면 PRIMARY KEY로 지정한 열에 클러스터형 인덱스가 생성된다.
• PRIMARY KEY로 지정한 열로 데이터가 오름차순으로 정렬된다. (한글이라면 ㄱ부터, 알파벳이라면 A부터)

인덱스의 자료 구조

B-Tree 구조의 장점

그렇다면 인덱스는 어떻게 컬럼을 정렬할까? 이때 사용되는 알고리즘에는 여러 가지가 있는데, 대표적으로 B-Tree(Balanced Tree, 균형 트리) 알고리즘이 있다.

출처: 위키백과

트리 구조에는 노드라는 데이터가 존재하는 공간이 있다. 가장 상위에 존재하는 노드를 루트 노드라고 하고, 가장 하위에 있는 노드를 리프 노드라고 한다. 이를 MySQL에서 사용할 때는 페이지라고 한다. 페이지는 16Kbyte 크기의 최소한의 저장 단위다.

데이터를 검색할 때 B-Tree 구조는 뛰어난 성능을 발휘한다. 만약 B-Tree 구조가 아니라면 리프 페이지만 있을텐데 이 경우에는 전체 테이블 검색(Full Table Search)가 일어난다. B-Tree 구조인 경우에는 데이터가 정렬되어 있으므로 먼저 루트 노드에서 검색한 후 해당 데이터가 있는 리프 페이지로 직접 이동하면 된다. 따라서 데이터가 많아질수록 인덱스를 활용하면 검색 성능이 훨씬 빨라진다.

B-Tree 구조의 단점

그렇다면 인덱스의 단점은 없을까? 검색에 최적화된 자료 구조이기 때문에 삽입, 수정, 삭제를 하는 경우에는 성능이 나빠지는 단점이 있다. 특히 삽입(INSERT)을 할 때는 페이지 분할이 일어나기 때문에 성능에 큰 영향을 주게 된다.

따라서 인덱스를 사용한다고 장점만 있는 것은 아니다. 삽입, 수정, 삭제가 빈번하게 일어나는 데이터의 경우 성능을 저하시킬 수도 있다. 또한 별도의 공간을 마련해서 인덱스를 생성하게 되므로 데이터의 양이 적은 경우 인덱스로 인한 불필요한 공간이 낭비될 수 있다.

클러스터형 인덱스와 보조 인덱스의 차이

B-Tree 구조의 장단점까지 알아봤다면 클러스터형 인덱스와 보조 인덱스의 차이도 이해하는 데 수월하다.

클러스터형 인덱스는 데이터 자체를 정렬해서 인덱스를 생성한다. 반면 보조 인덱스는 기존의 테이블이 아니라 별도의 테이블을 만들고 데이터를 순서대로 정렬하지 않은 인덱스를 생성한다. 따라서 검색 속도는 클러스터형 인덱스가 더 빠르며, 보조 인덱스는 클러스터형에 비해서는 검색 속도가 느리지만 대신 삽입, 수정, 삭제에 있어서는 덜 느리다.

따라서 이러한 장단점 때문에 실무에서는 보통 클러스터형 인덱스와 보조 인덱스를 함께 사용하는 혼합 인덱스를 생성한다고 한다.

결합 인덱스(복합 인덱스)란 무엇인가?

인덱스를 공부해보니 결합 인덱스(Composite Index, 복합 인덱스)에 대해서도 알게 되었는데, 혼합 인덱스와 어감이 비슷해 명확한 구분이 필요하다 싶었다.

결합 인덱스는 2개 이상의 컬럼을 인덱스로 만드는 방법이다. 예를 들어 where절에서 and 조건을 2개 이상 가지는 경우 조건에 해당하는 컬럼을 하나의 인덱스로 생성할 수 있다. 이때 컬럼의 순서가 중요하다. 첫 번째 인덱스부터 검색이 시작되기 때문에 성능이 최적화될 수 있도록 인덱스 순서를 정하는 것이 좋다.

반면, 앞서 설명한 혼합 인덱스는 클러스터형 인덱스, 그리고 보조 인덱스를 따로 생성하는 것이다. 따라서 결합 인덱스와는 다른 개념이라고 할 수 있다.

참고 자료

이것이 MySQL이다 (책)