Index란?

안녕하세요. Kernel 360 백엔드 2기 크루 송해덕입니다.

DB를 사용할 때 "Index를 잘 활용해야한다.", "Index를 걸어라" 라는 이야기들을 듣지만 이 Index가 무엇이고 어떻게 동작하는지 명확하게 아시나요? 오늘은 Index란 무엇이며 Index를 어떻게 사용해야 하는지 알아보는 시간을 가지도록 하겠습니다.

Index란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조를 말합니다.

  • 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아 보는 것은 오랜 시간이 걸린다. 그렇기 때문에 책의 저자들은 책의 맨 앞에 색인을 추가하는데, 데이터베이스에서는 이 색인이 Index와 같습니다.
  • 만약 Index가 없다면, 특정 데이터를 찾기 위해 모든 데이터를 처음부터 끝까지 하나하나 확인해야 합니다.
    예를 들어, 도서관에서 특정한 책을 찾기 위해 정리된 목록이 없다면, 도서관 전체를 뒤져서 책을 찾아야 합니다.
    데이터가 많아질수록 이런 방식은 시간이 오래 걸리게 됩니다.

Index Image

Index는 데이터베이스 내의 특정 컬럼이나 컬럼들의 조합에 대한 값과 해당 값이 저장된 레코드의 위치(Pointer)를 매핑하여 데이터베이스 쿼리의 성능을 최적화하는 데 중요한 역할을 합니다.

Index를 생성하게 되면 위 사진과 같이 Index 테이블을 생성하고 기존의 테이블에 저장된 위치를 가리키는 포인터 정보가 들어가게 됩니다.

사진처럼 compony_id를 Index의 기준으로 걸었다면 compony_id로 정렬이 된 Index 테이블에서 18번의 compony_id를 가진 데이터들의 포인터 값으로 실제 데이터들을 조회하여 반환하는 방식으로 동작하게 됩니다.

  • DB 옵티마이저란?
    • SQL 명령을 수행할 때 최적 경로를 찾아주는 역할을 하는 DBMS의 핵심 엔진입니다.
    • Index가 생성된 컬럼을 Where 조건으로 거는 등의 행위를 하면 옵티마이저에서 판단하여 생성된 Index를 활용하게 됩니다.
    • 만약 index가 걸려있다 하더라도 옵티마이저의 판단 하에 index를 통해 scan하지 않고 full table scan을 진행할 수도 있습니다.

Index의 장단점

Index의 장점

  • 테이블의 검색 속도와 성능이 향상된다.
    • Index는 데이터를 정렬하고 특정 열에서만 검색이 가능하도록 도와줌. 이를 통해 검색, 필터링, JOIN이 포함된 쿼리의 실행 속도를 향상시킬 수 있습니다.
    • Where 조건문 뿐만 아니라 정렬이 되어있기 때문에 OrderBy, Min/Max에 대한 것들도 빠르게 수행할 수 있습니다.
  • 시스템의 전반적인 부하를 줄일 수 있다.
    • Index를 통해 데이터베이스 서버가 더 적은 리소스로 동작할 수 있기 때문에 자원의 사용량이 줄어들어 서버가 더 많은 작업을 처리할 수 있습니다.

Index의 단점

  • Index를 저장하기 위한 추가 저장 공간이 필요하다.
    • Index 테이블을 별도로 관리하기 때문에 이를 위한 추가 저장 공간이 필요합니다.
    • 통상적으로 약 10%가 필요하며, 인덱스가 많아질 수록 더 많은 저장 공간을 필요로 합니다.
  • Insert, Update, Remove가 발생할 경우 추가적인 작업이 필요하다.
    • 기존의 테이블 뿐만 아니라 index 테이블에도 추가적인 정렬 및 갱신이 필요하기 때문에 성능에 영향을 미칠 수 있습니다.
    • 따라서 위와 같은 작업들이 빈번한 테이블의 경우 인덱스를 관리하는것이 중요합니다.

Index의 관리

그렇다면 Index를 어떻게 관리해야 할까요?

  • 필요한 Index만 생성한다.
    • 조회 성능을 올리겠다고 무분별하게 Index를 생성하는 것은 성능 저하를 일으킬 수 있습니다.
    • 자주 조회가 이루어지는 컬럼에만 Index를 사용하는 것이 중요합니다.
  • 불필요한 Index는 제거한다.
    • 위와 동일한 맥락으로 불필요한 Index는 확인하여 제거하는 작업이 필수적입니다.
    • 불필요한 Index는 저장 공간을 차지하고 쓰기 작업에 예상치 못한 성능 저하가 발생할 수 있습니다.
  • 복합 Index를 고려한다.
    • 함께 자주 사용하는 컬럼의 경우 여러개의 단일 Index보다 복합 Index를 사용하는 것이 성능 상 더 효율적일 수 있습니다.
    • 단, 복합 Index의 경우 Index를 선언하는 순서가 중요합니다.
      • index는 첫 번째 컬럼부터 탐색을 시작하기 때문에 첫 번째에 어떤 컬럼을 선언하는지가 중요합니다.
      • 또한, 쿼리의 조건에 첫 번째 컬럼이 사용되지 않는다면 해당 복합 Index는 사용되지 않습니다.

번외 : 조회만을 위한 테이블을 만들어서 batch 작업을 통해 해당 데이블에 데이터를 옮기는 작업을 하기도 합니다.
⇒ index는 batch 작업을 통해 데이터를 생성하는 테이블에만 걸어놓고 해당 테이블을 통해서만 조회합니다. (통계 테이블의 경우 주로 이렇게 쓰임)

Index의 자료구조

HashTable

Index Image

  • HashTable은 (key, value)로 데이터를 저장하는 자료구조로 빠른 데이터 검색이 필요한 경우 유용합니다.
  • 하지만, Hash는 정렬이 되어있지 않고 등호(=) 연산에 특화되어 있기 때문에 정렬이 필요한 범위(>, <)를 통해 접근하는 경우가 많은 Index에는 사용이 적합하지 않습니다.
  • 따라서 O(1)의 속도를 가진 HashTable을 Index에서 사용하지 않고 BTree 자료구조를 채택하여 사용하고 있습니다.

B-Tree

Index Image

  • B-Tree 자료구조는 Balanced Tree 라고 불리며 Tree의 노드가 한 방향으로 쏠리지 않는 Tree형 자료구조를 말합니다.
  • B-Tree 자료구조는 하나의 노드에 여러개의 값을 가질 수 있습니다.
  • 항상 양쪽의 밸런스를 유지하므로 평균적으로 O(log N)의 시간복잡도를 가집니다.

B+Tree

Index Image

  • B+Tree는 B-Tree를 개선시킨 자료구조입니다.
  • 모든 노드에 데이터(value)를 저장했던 B-Tree와 달리 B+Tree는 leaf node에만 데이터(value)를 가지고 있고 나머지 node들은 데이터를 위한 인덱스(key)만을 갖습니다.
  • 또한 leaf node들은 LinkedList로 연결되어 있기 때문에 순차검색을 용이하게 하는 등 B-Tree를 인덱스에 맞게 최적화 했습니다.
  • 현재 여러 DBMS에서 B+Tree를 Index의 기본 자료구조로 채택하여 사용하고 있습니다.

Index 생성 전략

Clustered Index & Non-Clustered Index

Index에는 크게 2가지 종류가 있습니다.

  • Clustered Index
  • Non-Clustered Index

각각을 책에 비유하자면 아래와 같습니다.

  • Clustered Index는 책의 페이지를 알기 때문에(정렬된 순서대로) 바로 그 페이지는 펴는 것과 같다.
  • Non-Clustered Index는 목차에서 찾고자 하는 내용의 페이지를 찾고 그 페이지로 이동하는 것과 같다.

Clustered Index란?

Index Image

  • 기본적으로 별도의 설정이 없다면 PK 컬럼에 Clustered Index가 설정됩니다.
  • 테이블의 데이터를 지정된 컬럼에 따라 물리적으로 재배열 시킵니다.
    • Clustered Index의 경우 leaf page가 모두 차있는데 새로운 데이터가 추가된다면 페이지 분할이 발생합니다. [ B-Tree 구조이기 때문에 ]
    • 데이터의 생성, 수정, 삭제 시 테이블의 데이터 정렬이 발생합니다.
    • 물리적으로 실제 데이터의 이동이 이루어지기 때문에 이는 리소스 소모가 큽니다.
  • 테이블 당 한 개만 존재합니다.

Non-Clustered Index란?

Index Image

  • 물리적으로 레코드를 정렬하지 않은 상태로 페이지가 구성됩니다.
  • 데이터 페이지를 건드리지 않으며 별도의 장소에 index page를 생성합니다.

  • 해당 index page의 leaf page에 index로 구성한 열을 정렬하고 위치 포인터(RID)를 생성합니다.

    포인터(RID): ‘파일그룹번호+데이터페이지 번호 + 페이지 내의 로우 번호’으로 구성되는 포인팅 정보

  • Clustered Index의 페이지 분할에 비해 비교적 적은 리소스의 소모가 발생합니다. ⇒ 물리적으로 실제 데이터의 이동이 이루어지지 않기 때문에
  • Clustered Index에 비해 더 많은 저장공간을 필요로 합니다. ⇒ 별도의 index page가 필요하기 때문에

Clustered Index와 Non-Clustered Index의 동작 방식

  • Clustered Index의 경우 바로 실제 데이터에 접근할 수 있습니다.
    • 그렇기 때문에 검색 속도가 빠르다
  • Non-Clustered Index의 경우 Clustered Index를 통해 실제 데이터에 접근합니다.
    • Non-Clustered Index가 매우 많고 실제 데이터를 바라보고 있다고 생각해보자
    • 이 경우 실제 데이터의 변동이 일어난다면 모든 Non-Clustered Index는 해당 데이터에 맞게 변경이 필요합니다.
    • 하지만, Non-Clustered Index가 Clustered Index를 바라보고 있다면 Non-Clustered Index는 포인터(RID)만 갱신해주면 됩니다.

왜 PK에 UUID를 안쓰고 Auto-Increment를 쓰는가?

우리는 보통 테이블 설계 시(혹은 엔티티 설계 시) PK로 id를 두고 이를 Auto-Increment를 사용합니다.

하지만 이렇게 사용하는 이유를 명확히 모르는 경우가 많습니다.

Index Image

만약 면접에서 왜 PK로 id를 Auto-Increment로 두고 사용했나요? 라는 질문이 들어온다면 어떻게 대답하시겠나요?

  • UUID는 중복이 생길 수 도 있으니까…?
  • UUID의 값보다 Auto-Increment를 사용하는 것이 데이터 사이즈가 더 적기 때문에…?

위와 같은 이유도 맞는 말이지만 Member 테이블의 경우 회원의 ID는 중복이 될 수 없다는 조건으로 해당 컬럼을 PK로 사용할 수도 있을 겁니다.

하지만 그렇게 하지 않는 이유를 Index 관점에서 보면 아래와 같습니다.

  • Auto-Increment를 사용할 경우 PK를 설정하며 생성되는 Index(Clustered Index)에서 성능상 우위가 있다.
  • Index는 보통 B-Tree, B+Tree 자료구조를 사용하고 있다.
  • 그렇기 때문에 Auto-Increment를 사용한다면 데이터의 정렬상태를 유지하는데 유리하고 삽입 성능을 최적화 할 수 있다.
  • 또한 많은 DBMS에서는 PK가 기본적으로 Clustered Index로 설정된다. Clustered Index는 데이터가 Index 정렬 순서에 따라 디스크에 저장되므로 연속된 Auto-Increment 값이 디스크의 물리적 순서와 일치하게 되어 디스크 I/O를 최소화할 수 있다.
  • 이는 읽기와 범위 조회 성능에 매우 유리한 장점을 가지고 있다.

Index 생성시 고려 사항

  • 카디널리티가 높은 컬럼
    • 특정 컬럼에 대해 유일한 값들의 개수를 나타냅니다. 이는 컬럼에 유니크한 값들이 많다는 이야기고 불필요한 전체 스캔을 피할 수 있습니다.
    • 즉, Index를 생성할 때 카디널리티가 높은 컬럼을 선택하는 것이 좋습니다.
  • 선택도가 낮은 컬럼
    • 인덱스의 특정 값이 전체 값 중에서 얼마나 자주 등장하는지를 나타내는 비율을 나타냅니다.
    • 일반적으로 선택도는 (1 / 카디널리티 * 100)으로 계산됩니다.
    • 이는 데이터의 집합에서 특정한 값을 얼마나 잘 골라낼 수 있는지에 대한 지표입니다.
    • 즉, Index를 생성할 때 선택도가 낮은 컬럼을 선택하는 것이 좋습니다. (통상적으로 5~10%의 선택도를 추천한다.)
  • Where절, Join 조건에 자주 사용되는 컬럼
    • Index는 조회의 성능을 위해 사용됩니다. 그렇기에 조회의 조건에 자주 이용되는 컬럼을 사용하는 것이 중요합니다.
  • Order By절에 자주 사용되는 경우
    • Index는 기본적으로 정렬이 되어있기 때문에 orderby를 따로 수행할 필요가 없습니다.
  • 데이터의 갯수가 많은 테이블
    • 데이터의 갯수가 적다면 Index를 생성하여 사용하는 것이 성능상에 크게 도움이 되지 않을 수 있습니다.
  • 또한 불필요한 비용이 발생하게 되어 이는 득보다 실이 많은 상황으로 이어집니다.

결론

우리는 테이블을 잘 설계하고 코드를 잘 작성하는 것도 중요하지만 정해진 자원 안에서 이를 얼마나 효율적으로 활용하는지도 중요합니다.

DB가 느리다고 인스턴스를 비용이 더 비싸고 메모리가 많은 것으로 늘린다면 물론 성능 향상이 있을 수 있겠지만, 이는 유지비용만 늘어나는 상황이 발생할 수 있습니다.

이러한 부분을 해결하기 위해 우리는 간단하게 Index를 활용하여 조회의 성능을 끌어올릴 수 있습니다.

이는 곧 비용의 절약을 의미하게 되는거죠.

개발자에게 중요한 것은 곧 가성비가 좋은 프로그램을 어떻게 만드느냐가 아닐까 싶습니다.

다들 DB 지식에도 더욱 관심을 가지는 개발자들이 되길 바라며 긴 포스트를 읽어주셔서 감사합니다.