데이터베이스 6: DB 인덱스(DB Index) 정의, 뜻, 개념, 예시

db index

DB 인덱스 (DB Index) 는 효율적인 데이터 검색을 위한 핵심 도구입니다. 이는 추가적인 쓰기 작업과 저장 공간을 활용하여 DB 테이블의 검색 속도를 향상시키는 자료구조로, 주로 B+ Tree를 사용하여 구현됩니다. 이번 글에서는 DB 인덱스 개념, 종류, 동작 방식, 그리고 장단점에 대해 자세히 알아보겠습니다.


DB 인덱스 (DB Index)

db 인덱스

DB 인덱스 설명

DB 인덱스 (DB Index) 는 추가적인 쓰기 작업과 저장 공간을 활용하여 DB 테이블의 검색 속도를 향상시키기 위한 자료구조입니다. 이는 데이터를 빠르게 검색할 수 있게 해주는 객체로, 레코드 검색 시 Full Scan이 아닌 Index 파일을 검색하여 속도를 향상시킵니다. 관계형 데이터베이스의 DBMS (RDBMS) 에서는 주로 B+ Tree 를 사용합니다. (간혹 Hash Table 을 사용하기도 합니다.)

B+ Tree 란?

– B+ Tree 란?

b+ tree

B+ Tree는 B-tree에서 파생된 구조로, 주로 데이터베이스 인덱스에 사용됩니다. B-tree는 브랜치 노드에 key와 data를 함께 담지만, B+ Tree에서는 브랜치 노드에는 key만 담겨 있고, 실제 데이터는 리프 노드에만 저장됩니다. 또한, 리프 노드끼리는 linked list를 사용하여 연결되어 있습니다.

B+ Tree 의 특징과 효과
  1. 메모리 확보와 Cache Hit 향상: B+ Tree에서는 리프 노드에만 데이터가 있기 때문에, 브랜치 노드에서는 key만을 관리합니다. 이로 인해 더 많은 브랜치 노드를 메모리에 확보할 수 있으며, 브랜치 노드에서의 탐색 시에 Cache Hit 확률을 높일 수 있습니다.
  2. 한 번의 선형 탐색으로 데이터 찾기: B+ Tree는 리프 노드에 모든 데이터가 있어 한 번의 선형 탐색으로 원하는 데이터를 찾을 수 있습니다. 이는 효율적인 범위 검색에 기여합니다.
B+ Tree vs. Hash Table

왜 B+ Tree를 사용하며 Hash Table을 사용하지 않을까요?

  • 메모리 활용: B+ Tree는 linked list를 활용하여 리프 노드를 연결하므로, 메모리를 더 효과적으로 활용할 수 있습니다.
  • 동등 연산 이외의 다양한 검색 지원: B+ Tree는 동등(=) 연산 이외에도 범위 검색이나 정렬된 순서로의 탐색을 지원합니다. 이는 데이터베이스에서 다양한 검색 패턴을 처리하는 데 유리합니다.

DB 인덱스를 사용하는 이유

인덱스를 사용하지 않으면 서버의 Heap 영역에 데이터 레코드가 순서 없이 저장되어 특정 데이터를 찾기 위해 Full Scan 방식을 사용해야 합니다. 반면, 인덱스를 사용하면 SELECT 문이 실행될 때 Index 테이블의 값들로 결과 값을 조회하여 검색 연산의 성능을 향상시킵니다.

인덱스 동작 방식

테이블 생성 시에는 FRM (테이블 구조 저장 파일), MYD (실제 데이터 파일), MYI (Index 정보 파일) 이 생성됩니다.

  • FRM( 테이블 구조 저장 파일 )
  • MYD( 실제 데이터 파일 )
  • MYI ( Index 정보 파일 )

위 세 가지 파일을 사용하여 데이터베이스의 내용을 빠르고 효과적으로 조회할 수 있습니다. 다음은 각각의 쿼리 명령어를 수행 시, DB 인덱스가 동작하는 방식은 다음과 같습니다.

SELECT 문 수행 시

  1. Buffer Cache 확인 : 서버 프로세스는 DB Buffer Cache에 해당 정보가 있는지 확인합니다. 이는 메모리 상에 캐싱된 데이터를 검사하는 것으로, 만약 검색하려는 데이터가 메모리에 이미 존재한다면 디스크에서 읽어올 필요가 없어집니다.
  2. 디스크에서 데이터 읽기 : Buffer에 해당 데이터가 없다면, 하드디스크에서 해당 데이터를 가진 블록을 찾아서 Buffer Cache로 복사한 후, 원하는 데이터를 출력합니다.
  3. 인덱스 사용 여부 확인 : 만약 테이블에 인덱스가 존재한다면, 해당 인덱스를 활용하여 더 빠른 검색을 수행합니다. where 절에 사용된 컬럼이 인덱스에 있다면, 인덱스에서 해당 데이터의 ‘ROW ID’를 찾아서 해당 블록만을 Buffer Cache로 복사합니다. 이는 전체 테이블을 검색하는 것보다 훨씬 빠른 방식입니다.

INSERT 문 수행 시

INSERT 문을 수행할 때는 새로운 데이터를 테이블에 추가하는데, 이 과정에서도 몇 가지 고려해야 할 사항이 있습니다. 다음은 INSERT 문을 수행할 때 일어나는 과정입니다.

  1. 기존 Block 여유 여부 확인 : 새로운 데이터를 추가할 때, 기존의 블록에 여유가 없다면 새로운 블록을 할당 받아야 합니다.
  2. 새로운 Block 할당 및 Key 이동 : 여유가 없는 경우에는 새로운 블록을 할당 받은 후, 새로운 데이터의 Key를 해당 블록으로 옮겨야 합니다. 이 과정에서 해당 키 값을 찾아 이동하는 작업이 수행되며, 이 때 해당 키값들은 DML(데이터 조작 언어)이 블로킹되고, 락이 걸릴 수 있습니다.

DELETE 문 수행 시

DELETE 문을 수행하면 데이터는 삭제되어 해당 공간에 다른 데이터가 사용 가능해집니다. 그러나 인덱스는 데이터가 실제로 지워지지 않고 “사용 안됨” 표시만을 생성합니다.

UPDATE 문 수행 시

UPDATE 문을 수행할 때는 실제로는 DELETEINSERT 작업이 이루어집니다. 즉, 기존의 데이터를 삭제한 후, 변경된 데이터를 추가하는 방식으로 진행됩니다. 이 과정에서 인덱스도 마찬가지로 삭제 후 추가 작업이 수행됩니다.

인덱스 관리

데이터베이스에서 인덱스는 데이터의 빠른 검색을 가능케 하지만, 이를 유지하고 최신의 상태로 유지하는 것은 DBMS 에게 추가적인 작업을 요구합니다. 여러 데이터 조작 작업이 수행되면서 발생하는 추가적인 오버헤드에 대해 알아보겠습니다. (데이터의 양이 많고 조작이 빈번하게 일어나는 경우, 이러한 작업들은 성능에 영향을 미칠 수 있습니다.)

INSERT 연산

새로운 데이터가 테이블에 추가될 때, 해당 데이터에 대한 인덱스를 추가하는 과정이 필요합니다. 새로운 데이터는 정렬된 상태를 유지하기 위해 인덱스 트리에 맞게 삽입되어야 합니다. 이는 인덱스 키의 순서를 고려하여 정확한 위치에 데이터를 추가하는 것을 의미합니다. 즉, 데이터의 추가 작업이 수행될 때마다 인덱스도 함께 업데이트되어야 합니다.

DELETE 연산

삭제하는 데이터의 경우에도 인덱스에서 해당 데이터를 찾아 삭제 작업을 진행해야 합니다. 삭제된 데이터의 공간을 다른 데이터가 차지하게 되므로, 인덱스도 이에 맞게 조정되어야 합니다. 이때, 실제 데이터가 삭제되지 않고 ‘사용 안됨’ 표시만 생성되기 때문에, 인덱스도 데이터의 상태를 반영하기 위한 추가 작업이 필요합니다.

UPDATE 연산

기존의 데이터를 갱신하는 경우, 해당 데이터의 인덱스를 사용하지 않음을 처리하고, 갱신된 데이터에 대한 인덱스를 추가해야 합니다. 데이터의 갱신은 일종의 삭제와 삽입 작업을 동시에 수행하는 것으로 이해할 수 있으며, 이에 따라 인덱스도 두 가지 작업을 수행해야 합니다.

DB 인덱스 장단점

장점

1. 조회 속도 및 성능 향상

인덱스는 B-Tree와 같은 자료구조를 사용하여 빠르게 데이터를 찾을 수 있어 테이블을 조회하는 속도와 전반적인 시스템 성능을 향상시킬 수 있습니다. 특히, 기본적으로는 순차탐색이 필요한 경우에 비해 훨씬 효율적입니다.

2. 부하 감소

데이터 검색이 빠르기 때문에 시스템 전체적인 부하를 줄일 수 있습니다. 탐색 속도가 빠르면 데이터를 빠르게 찾아낼 수 있으므로, 시스템이 더 효율적으로 동작할 수 있습니다.

단점

1. 저장 공간 소모

인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장 공간이 필요합니다. 이는 인덱스를 생성하고 유지하기 위해 일정한 공간이 필요하기 때문입니다.

2. 추가 작업 필요

데이터 조작 연산(INSERT, DELETE, UPDATE)이 발생할 때마다 인덱스를 관리하기 위한 추가 작업이 필요합니다. 이는 데이터의 변경에 따라 인덱스를 업데이트해야 하기 때문입니다.

3. 성능 저하 가능성

잘못된 상황에서 인덱스를 사용하면 오히려 성능이 저하될 수 있습니다. 특히, CREATE, DELETE, UPDATE 연산이 빈번한 속성에 인덱스를 걸면 인덱스의 크기가 커져서 오히려 성능 저하가 발생할 수 있습니다. DELETE와 UPDATE 연산은 기존의 인덱스를 삭제하지 않고 ‘사용하지 않음’으로 표시하기 때문에, 메모리를 많이 차지하게 됩니다.

DB 인덱스 적용 시 고려사항

적용하기 좋은 곳

  • 규모가 작지 않은 테이블
  • INSERT, UPDATE, DELETE 가 자주 발생하지 않는 컬럼
  • JOIN 이나 WHERE 또는 ORDER BY 에서 자주 사용되는 컬럼
  • 데이터 중복도가 낮은 컬럼

적용하면 안되는 곳

  • 삽입, 수정, 삭제가 빈번한 테이블
  • 삭제 시 인덱스 데이터가 ‘사용 안함’으로 표시되지만, 실제로 삭제되지 않아 메모리를 많이 차지하는 경우

Clustered Index vs Non-Clustered Index

1. Clustered Index

Clustered Index 개념

  • 한 테이블당 1개로, 주로 Primary Key에 적용됩니다.
  • 데이터는 인덱스 키의 순서에 따라 물리적으로 정렬되어 저장됩니다.

Clustered Index 동작 방식

  • 데이터 삽입, 삭제 시에는 순서를 유지하기 위해 데이터를 재정렬해야 합니다.
  • 실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있습니다.

Clustered Index 장단점

  • 장점: 데이터 검색이 빠르며, 특히 범위 검색이 효과적입니다.
  • 단점: 데이터 변경 시 재정렬 필요로 인해 입력, 삭제 성능이 떨어질 수 있습니다.

2. Non-Clustered Index

Non-Clustered Index 개념

  • 한 테이블당 여러 개 가능하며, 테이블 당 3~4개 정도를 권장합니다.
  • 카디널리티(중복도)가 낮은 컬럼을 중심으로 정렬됩니다.

Non-Clustered Index 동작 방식

  • 인덱스 키 값만 정렬되어 있으며, 실제 데이터는 정렬되지 않습니다.
  • 검색 속도는 Clustered Index에 비해 느리지만 입력, 수정, 삭제가 빠릅니다.

Non-Clustered Index 장단점

  • 장점: 입력, 수정, 삭제 성능이 뛰어나며, 검색 속도도 상대적으로 빠릅니다.
  • 단점: 범위 검색이 느리고, 중복된 데이터가 많을 경우 효율이 감소할 수 있습니다.

마치며

DB 인덱스는 데이터베이스 성능을 향상시키는 강력한 도구이지만, 그 사용은 신중해야 합니다. 특히, 어떤 상황에서 인덱스를 적용할지, 어떤 컬럼에 적용할지를 결정하는 것이 중요하며, 테이블의 크기와 특성, 작업의 빈도 등을 고려하여 최적의 성능을 얻을 수 있도록 관리해야 합니다.

함께 읽으면 좋은 글
Reference

Leave a Comment