CS/DB

인덱스 동작 방식과 clustered Index vs non-clustered Index (MYSQL기준)

GongCho 2024. 2. 21. 09:32

인덱스란?

인덱스는 DB에서 데이터를 조회할 때 결과를 빠르게 추출하도록 도와주는 ‘데이터베이스 개체’이다. 마치 사전의 ‘찾아보기’와 같은 역할을 한다고 생각하면 된다.

  • 장점
    • 적절한 인덱스를 생성하고 조회하면 조회 성능을 획기적으로 개선할 수 있다.
  • 단점
    • 인덱스는 대략 테이블 크기의 10%공간이 추가로 필요 (인덱스 페이지 때문)
    • SELECT가 아닌 데이터의 변경작업(INSERT, UPDATE, DELETE)이 자주 일어나면 성능에 악영향
    • → 페이지 분할 작업 때문

인덱스 종류

인덱스의 종류는 크게 클러스터형 인덱스와 보조 인덱스로 나뉜다.

클러스터형 인덱스 (Clustered Index)

  • 테이블당 1개만 존재할 수 있다.
  • 기본키로 지정된 컬럼은 자동으로 클러스터링 인덱스가 생성된다.
  • 실제 저장된 데이터와 같은 무리의 페이지 구조를 가진다.
  • 클러스터링 인덱스를 기준으로 데이터가 자동 정렬된다.
    • 기본키를 변경하면 클러스터링 인덱스가 변경되기 때문에 변경된 기본키를 기준으로 자동 정렬

보조 인덱스 (Secondary Index)

  • 한 테이블에 여러개 설정 가능
  • UNIQUE키워드로 고유 컬럼 지정시 자동으로 보조 인덱스가 생성됨 → (모니카 서비스의 경우, mdn이 해당됨.)
  • 실제 저장된 데이터와 다른 무리의 별도의 페이지 구조를 가짐
  • 클러스터링 인덱스와 달리 데이터를 정렬하지 않음

인덱스 동작원리

클러스터링 인덱스와 보조 인덱스 모두 내부적으로 균형트리(B-tree)로 만들어진다.

균형 트리 구조에서는 데이터가 저장되는 공간을 ‘노드’라고 한다.

노드는 위치에 따라 루프노드, 중간노드, 리프노드로 구성된다.

MYSQL에서는 이러한 노드들을 ‘페이지’라고 부른다.

MYSQL에서는 페이지를 나누어 데이터를 저장한다. 페이지는 MYSQL 기준 최소 16KB 크기를 가지며 페이지 데이터 공간이 추가적으로 필요할 경우 페이지 분할 작업을 통해 페이지를 생성하고 각 페이지에 데이터를 분할하는 작업을 거친다.

 

페이지 분할

 

인덱스를 구성하면 데이터 변경작업(INSERT, UPDATE, DELETE)시 성능이 나빠질 수 있다.

특히 INSERT에 영향이 큰데, 그 이유는 페이지 분할 작업이 발생하기 때문이다.

페이지 분할이란, 데이터를 페이지 단위로 저장하는 기본구조에서 추가적인 페이지가 필요할때 새로운 페이지를 준비하며 데이터를 나누는 작업을 말한다. 페이지 분할 작업이 자주 일어나면 DB성능에 큰 영향을 미친다.

클러스터형 인덱스 페이지 구조

클러스터형 인덱스 페이지는 위와 같이, 루트 - (중간) - 리프페이지로 이루어져있다.

클러스터형 인덱스가 걸린 기본키를 기준으로 실제 데이터가 정렬된다.

클러스터형 인덱스는 리프 노드가 곧 데이터 페이지(실제 데이터가 저장된 위치)이다.

클러스터형 인덱스를 통해 검색시, 먼저 루프노드에서 탐색할 페이지를 찾고 해당 페이지에서 검색할 데이터를 찾게 됨으로써 검색시간을 줄이게 된다.

보조 인덱스 페이지 구조

보조 인덱스 페이지는 실제 데이터를 정렬하지 않는다.

따라서, 보조인덱스를 설정해도 데이터 페이지에는 아무런 영향이 없고 별도의 장소에 인덱스 페이지를 생성한다.

리프 페이지는 실제 데이터가 저장된 위치를 가리킨다. 클러스터링 인덱스와 같이 사용하게 될 경우, 클러스터링 인덱스로 인해 정렬된 데이터 페이지를 가리키게 된다.

인덱스 사용시 유의 사항

  1. 카디널리티가 높은 (중복도가 낮은) 열에 인덱스를 생성해야한다.
    • SQL에서 카디널리티라는 용어는 DB테이블의 특정 열에 포함된 데이터 값의 고유성을 나타낸다.
    • 인덱스는 중복도가 높은 컬럼에 설정할 경우, 별다른 성능 향상의 결과를 얻지 못하기 때문에 중복도가 낮은 컬럼에 설정해야한다.
  2. WHERE절에 자주 사용되는 열에 인덱스를 만들어야한다.
    • WHERE절에 사용되는 열에 따라 테이블 스캔 방식이 결정된다. WHERE절에 자주 사용되지 않는 열을 기준으로 인덱스를 설정하였다면, 인덱스를 실제로 활용할 일이 적다는 뜻으로, 다른 열에 다른 열에 인덱스를 설정하는 것이 좋다.
  3. 잘 사용되지 않는 인덱스는 과감히 제거한다.
  4. INSERT, UPDATE, DELETE등의 데이터 작업이 빈번한 테이블에서는 인덱스 사용을 고려해야한다.

참고자료

 

[MySQL] - 인덱스 (INDEX) 정리 (동작 방식, 생성, 삭제, 설계)

인덱스란? 인덱스는 데이터베이스에서 데이터를 조회할 때 결과를 빠르게 추출하도록 도와주는 하나의 '데이터베이스 개체'입니다. 마치 사전의 '찾아보기'와 같은 역할을 한다고 생각하시면

rachel0115.tistory.com

 

다음시간에는 왜 인덱스는 BTree자료구조를 사용하게 되었는지 이해해보자.