[Database] Index

key word : Index

데이터베이스에 저장된 데이터가 많으면 많을수록 특정 데이터를 조회하는데 더 많은 시간이 할애된다. 이를 효율적으로 하기 위해 사용하는 것이 인덱스인데, 흔히 우리가 아는 인덱스(색인)와 같은 개념이라고 생각하면 된다. 데이터베이스에서 사용하는 인덱스에 대해 더 자세히 알아보자. 참고로 이 글은 MySQL을 기준으로 작성되었다.

개념 및 종류

책에서 원하는 내용을 찾을 때 맨 뒤의 인덱스를 이용할 수 있다. 혹은 애초에 책이 사전처럼 순서대로 정렬되어 있고, 중간중간 표시가 되어 있어 바로 원하는 내용이 담긴 페이지를 펼 수 있는 경우도 있다. 데이터베이스에서 이러한 역할을 하는 것이 인덱스이다. 즉 원하는 데이터가 저장되어 있는 위치를 알려주거나, 바로 찾을 수 있게 해주는 지표 같은 것이다. 인덱스를 추가적으로 저장해야 하기 때문에 메모리 공간을 더 사용해야 한다는 단점이 있지만, 데이터 조회에서 굉장한 성능 향상을 가져올 수도 있다. 물론 인덱스를 어떤 데이터에, 어떻게 설정하느냐에 따라 성능은 별로 향상되지 않고, 부하만 늘어나는 경우가 생길 수도 있다.

데이터베이스의 인덱스는 클러스터형 인덱스와 비클러스터형 혹은 보조 인덱스라고 불리는 인덱스로 나누어진다.

1. 클러스터형 인덱스

   • 한 테이블 당 하나밖에 생성할 수 없는 인덱스이다.
   • PRIMARY KEY로 설정하거나 UNIQUE, NOT NULL을 함께 설정하면 해당 열에 자동으로 클러스터형 인덱스가 생성된다.
   • PIRMARY KEY와 UNIQUE, NOT NULL이 설정된 열이 모두 존재할 경우엔 PRIMARY KEY가 설정된 열에 우선해서 클러스터형 인덱스가 생성된다.
   • 클러스터형 인덱스가 생성된 열을 중심으로 자동 오름차순 정렬된다.
   • 영어사전처럼 데이터 자체가 해당 열을 기준으로 정렬되어 있어 인덱스로 접근했을 때 데이터에 바로 접근할 수 있다.

2. 보조 인덱스

   • 한 테이블에 여러 개 생성할 수 있는 인덱스이다.
   • UNIQUE 제약 조건을 설정한 열에 자동으로 보조 인덱스가 생성된다.
   • 클러스터형 인덱스와 함께 사용될 수도, 단독으로 사용될 수도 있다.
   • 책 뒤쪽의 인덱스처럼 인덱스로 접근했을 때 데이터가 위치한 주소를 찾을 수 있다.

작동 원리

인덱스는 주로 B-Tree(Balanced Tree)라는 자료구조를 사용해 구현된다(Hash 등을 사용하기도 한다). 노드로 사용되는 단위는 ‘페이지’로 최소한의 저장 단위를 의미한다. MySQL에서는 16Kbyte가 기본이지만 사이즈는 조절 가능하다.

1. 클러스터형 인덱스

   

   출처 : https://stackoverflow.com/questions/55836641/disk-io-in-mysql-clustered-index

   • 루트 노드와 중간 노드에 인덱스 정보를 갖고 있고, 리프 노드에 데이터를 가지고 있는 형태이다.
   • 데이터를 삽입, 수정, 삭제할 경우 페이지 분할이 일어날 수 있다.
   • 페이지 분할로 인한 성능 저하가 있을 수 있다.

2. 보조 인덱스

   • 보조 인덱스의 경우 리프 노드에 데이터가 위치한 주솟값을 저장한다. (페이지 번호 + #오프셋)의 형식으로 저장되어 있다.
   • 데이터 정렬이 일어나지 않아 데이터 삽입, 수정, 삭제 때 페이지 분할이 덜 일어난다.
   • 클러스터형 인덱스와 함께 사용할 경우 리프 노드에 데이터의 주솟값이 아닌, 클러스터형 인덱스가 생성된 칼럼의 값을 저장한다.
   • 클러스터형 인덱스와 함께 사용할 경우 보조 인덱스에서 한 번 데이터를 조회한 후, 클러스터형 인덱스에서 한 번 더 조회한다.

생성 및 삭제

1. Index 생성

CREATE [UNIQUE | FULLTEXT | SPATITAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part, ...)
    [index_option]
    [algorithm_option | lock_option]

1. Index 삭제

DROP INDEX index_name On tbl_name
    [algorithm_option | lock_option]

인덱스 사용시 주의할 점

1. 인덱스는 where 절에서 주로 사용하는 column에 생성한다.
2. 자주 사용하지 않는 인덱스는 지워야 한다.
3. 조회(select)보다 inser 등 데이터를 수정하는 일이 더 많다면 생성하지 않는 게 좋다.
4. 데이터의 중복이 많은 column에는 인덱스를 생성하지 않는 것이 좋다.
5. 외래 키를 지정하면 자동으로 외래 키 인덱스가 생성된다.
6. JOIN을 자주 하는 column에 인덱스를 생성하는 것이 좋다.
7. 클러스터형 인덱스가 아예 없는 것이 좋을 때도 있다. 그럴 때엔 PRIMARY KEY로 지정하지 않고, UNIQUE만 지정해 주면 된다.

참고

이것이 MySQL이다(개정판), 우재남, 한빛미디어, 2021