[DB] 인덱스 기본 개념과 클러스터링, 논클러스터링 인덱스

 

들어가면서

프로젝트의 기본적인 성능 최적화를 위해 사용중인 데이터베이스에 인덱스를 적용해야 할 일이 생겼습니다.

그 전에 인덱스의 간단한 기본 개념과 클러스터링, 논클러스터링 인덱스에 대해 정리해보려합니다.

 

 

 

인덱스 기본 개념 및 용어 정리

 

인덱스란 데이터베이스에서 원하는 값을 빠르게 찾기 위해 사용하는 자료구조입니다. 인덱스를 이용하면 데이터베이스 테이블에 대한 검색 성능을 향상시킬 수 있으며, where 절 등을 통해 활용됩니다. 

 

인덱스의 특징

• 인덱스는 항상 최신의 정렬상태를 유지합니다.
• 인덱스도 하나의 데이터베이스 객체입니다.
• 데이터베이스 크기의 약 10% 정도의 저장 공간을 필요로 합니다.

 

자주 사용되는 용어 정리

• 페이지 : 데이터가 저장되는 단위 (MySQL 에서는 16KB)

 

• Full Table Scan
  • 순차적으로 전체 데이터를 탐색하는 것
  •  따로 검색을 위한 추가적인 작업을 수행하지 않고 모든 데이터를 탐색하기 때문에 접근 비용이 감소합니다.
  • 언제 Full Table Scan이 발생하는가?
    • 적용 가능한 인덱스가 존재하지 않을 때
    • 인덱스를 적용했다고 하더라도 인덱스의 처리범위가 너무 넓은 경우
    • 인덱스를 적용했다고 하더라도 크기가 작은 테이블에 액세스 하는 경우

 

• Binary Search Tree
  • 이진 탐색 방식과 링크드리스트의 장점을 합쳐 만들어진 자료구조입니다.
  • 이진 탐색 트리는 균형이 맞춰져 있는 경우 O(logn)이지만 균형이 깨진 최악의 경우 O(n)에 수렴합니다.
  • 이러한 이진 탐색 트리의 단점을 해결하기 위해 등장한 자료구조가 바로 B-Tree입니다.

 

• B-Tree (Balanced Tree)
  • 트리의 높이가 일정하게 유지되며 균형을 유지합니다.
  • 자식 노드를 2개 이상 가질 수 있습니다.
  • 기본 데이터베이스 인덱스 구조로 사용됩니다.
  • 이 경우 어떤 데이터를 조회하더라도 O(logn)을 보장하기 때문에 B-Tree 구조를 사용하여 인덱스를 구성하면 select 명령의 성능을 향상시킬 수 있습니다.

 

 

 

인덱스는 항상 성능 향상에 도움을 주는가?

 

인덱스를 통해 일반적으로 Select 명령의 성능을 향상시킬 수 있습니다. 하지만 Insert, Update, Delete 명령의 경우에는 오히려 인덱스를 적용하는 것이 최선이 아닐 수 있음을 유의해야 합니다.

• Insert 쿼리 수행 시
  • Insert 명령을 통해 페이지 내 새로운 데이터를 추가하게 됩니다.
  • 이 때 페이지 내에서만 작업이 수행되면 괜찮은데, 페이지에 새로운 데이터를 추가할 여유 공간이 없는 상황에서는 새로운 페이지를 만들고 데이터를 균등하게 나눠주는 작업이 발생합니다.
  • 이러한 작업으로 인해 DB 성능에 영향을 주고 전체적인 작업 속도가 느려지게 될 수 있습니다.
• Delete 쿼리 수행 시
  • Delete 명령을 통해 페이지 내 기존 데이터를 삭제합니다.
  • 하지만 삭제한 데이터에 할당되어 있던 인덱스를 실제로 지우지 않고 사용안함 마킹만 하게 됩니다.
  • 이런 동작방식으로 인해 잦은 Delete 작업 수행 시 인덱스 조각화 현상을 야기할 수 있습니다.
• Update 쿼리 수행 시
  • Update 명령을 통해 페이지 내 기존 데이터가 수정됩니다.
  • 하지만 인덱스에는 update라는 개념이 없어서 위에서 설명한 Delete + Insert 명령을 수행하게 됩니다.
• Update, Delete 명령 수행 시 Where 절을 사용하면 성능이 향상되지 않을까?
  • Where 절로 처리할 대상을 찾기 위한 조회 성능은 향상됩니다.
  • 하지만 사용하지 않는 인덱스가 적용되었다면 Update, Delete 명령으로 변경된 데이터 정보를 반영하기 위한 불필요한 작업 처리량이 늘어난다는 단점이 발생합니다.
  • 또한 인덱스 사용안함(delete) 표시로 인해 페이지 낭비 및 인덱스 조각화가 심해질 수 있습니다.

 

 

 

클러스터링 인덱스와 논클러스터링 인덱스

인덱스는 크게 클러스터링 인덱스와 논클러스터링 인덱스로 종류가 나뉩니다. 그 중 논클러스터링 인덱스는 보조 인덱스, 세컨더리 인덱스라고 불리기도 합니다.

• 클러스터링 인덱스 : Primary Key 값이 비슷한 레코드끼리 묶어서 저장하는 방식의 인덱스
  • 예시 - 실제 데이터가 정렬된 사전
• 논클러스터링 인덱스 : Primary Key를 사용하지 않는 나머지 모든 인덱스
  • 예시 - 실제 데이터 탐색에 도움을 주는 별도의 찾아보기 페이지

create table member(
	id		int				primary key,
    name	varchar(255),
    email	varchar(255)	unique
);

위와 같은 테이블을 생성했을 때 DB에서는 자동으로 인덱스가 2개 생성됩니다.

primary key와 unique 제약조건을 걸음으로써 각각 id와 email 컬럼을 클러스터링 인덱스와 논클러스터링 인덱스로 설정할 수 있습니다.

 

 

클러스터링 인덱스

클러스터링 인덱스를 걸어줄 수 있는 방법으로는 다음과 같이 2가지가 있습니다.

primary key로 적용한 클러스터링 인덱스

unique & not null 제약조건으로 적용한 클러스터링 인덱스

• 클러스터링 인덱스를 사용하려는 데이터를 primary key로 지정해주거나
• 하나의 데이터 컬럼에 Not Null, UNIQUE 제약 조건을 한 번에 같이 걸어주면 됩니다.

 

 

 

 

클러스터링 인덱스를 적용하면 발생하는 작업들

• 먼저 클러스터링 인덱스를 적용한 id 컬럼(예시)을 기준으로 데이터를 정렬합니다.
• 그 뒤 정렬된 데이터들이 저장된 각 페이지 주소 값을 모아 루트 페이지를 생성합니다.
• 리프 페이지(데이터 페이지)에는 실제 데이터들이 저장되어 있으며 클러스터링 인덱스 순서대로 정렬되어 관리됩니다.

 

 

클러스터링 인덱스 특징

• 실제 데이터 자체가 클러스터링 인덱스 순으로 정렬됩니다.
  • Primary Key에 의해 실제 데이터 레코드가 저장될 위치가 결정되므로 Primary Key 값이 변경된다면 레코드 저장 위치도 함께 변경되어야 합니다.
  • 만약 다음과 같은 명령으로 Primary Key가 업데이트 되는 경우 그림과 같이 페이지 구성이 변경되어야 하므로 Primary Key를 지정할 때는 변경이 거의 이뤄지지 않는 컬럼으로 정하는 것이 중요합니다.

• 테이블 당 1개의 클러스터링 인덱스만 지정해줄 수 있습니다.
• 리프 페이지가 실제 데이터가 저장된 페이지입니다.
• 아래의 제약조건을 걸어줄 시 자동으로 클러스터링 인덱스가 생성됩니다.
  • primary key(우선순위)
  • unique + not null
  • 여기에서 만약 primary key로 지정해준 컬럼과 unique not null 제약조건이 걸린 컬럼이 한 테이블에 동시에 존재하는 경우 primary key가 우선순위를 가지고 클러스터링 키로 동작하며 unique not null 제약조건 컬럼은 논클러스터링 인덱스로 동작하게 됩니다.

 

 

 

논클러스터링 인덱스

논클러스터링 인덱스를 걸어주는 방법으로는 다음과 같이 3가지 방법이 있습니다.

• 특정 컬럼을 대상으로 Unique 제약조건을 걸어줍니다.
• 특정 컬럼에 대한 Unique Index를 직접 생성해준다.
  • 인덱스 간 중복을 허용하지 않음
• 특정 컬럼에 대한 Index를 직접 생성해준다.
  • 인덱스 간 중복을 허용

 

 

 

인덱스 구성 과정

• 클러스터링 인덱스는 실제 데이터가 정렬되어 리프 페이지가 곧 데이터 페이지와 동일하게 관리되었습니다.
• 반면 논클러스터링 인덱스는 데이터 페이지에 저장된 실제 데이터들은 그대로 유지되고 특정 컬럼에 대한 별도의 인덱스 페이지가 생성되어 관리되게 됩니다. 이 때 논클러스터링 인덱스의 리프 페이지 데이터에는 실제 데이터가 저장된 페이지 주소와 몇번째 컬럼에 데이터가 존재하는지 등의 정보가 담기게 됩니다.
• 정리하면, 논-클러스터링 인덱스를 걸어둔 컬럼의 값을 key 값으로, 실제 데이터 페이지 주소 값을 value 값으로 인덱스 테이블이 만들어집니다.

 

 

 

논클러스터링 인덱스를 통한 조회 과정

논클러스터링 인덱스만 적용된 조회 과정

 

select 명령에서 where 절에 논클러스터링 인덱스가 적용된 컬럼이 사용되면 다음과 같은 과정을 통해 조회가 수행됩니다.

• 먼저 루트 페이지에서 질의된 조건보다 작은 값 중 가장 큰 인덱스 값을 찾습니다.
• 해당 인덱스가 저장하는 리프 페이지 주소를 조회하여 접근한 뒤 찾고자 하는 데이터 페이지 주소를 찾습니다. (1번 과정)
• 실제 데이터 페이지 주소를 참조한 뒤 실제 데이터를 찾습니다. (2번 과정)

 

 

 

논클러스터링 인덱스 특징

• 실제 데이터 페이지는 그대로 유지됩니다. (논클러스터링 인덱스에 따른 별도의 정렬을 수행하지 않습니다.)
• 별도의 인덱스 페이지가 생성됩니다. ⇒ 추가 공간이 필요합니다.
• 테이블 당 여러 개 존재할 수 있습니다.
• 리프 페이지에 실제 데이터 페이지의 주소를 담고 있습니다.
• unique 제약 조건을 적용하면 자동으로 논클러스터링 인덱스가 생성됩니다.
• 직접 index 생성 시 논클러스터링 인덱스가 생성됩니다.

 

 

 

다수의 인덱스

 

클러스터링 인덱스와 논클러스터링 인덱스를 함께 적용하면 어떻게 될까?

논클러스터링 - 클러스터링 인덱스가 함께 적용되었을 때 조회 과정

 

위에서 배웠던 대로 인덱스 테이블이 구성된다고 한다면 논클러스터링 인덱스 페이지에서 우리가 원하는 데이터 페이지의 주소를 찾고 실제 데이터 페이지를 참조해서 데이터를 조회한다고 예상할 수 있습니다. 하지만 논클러스터링 인덱스 테이블에는 데이터 페이지 주소값이 아니라 클러스터링 인덱스가 적용된 컬럼의 실제 인덱스 값이 저장되어 있습니다. 그래서 논클러스터링 테이블에서 원하는 데이터의 클러스터링 인덱스를 찾고 다시 클러스터링 인덱스 테이블에서 실제 데이터를 찾는 방식으로 진행됩니다.

 

 

 

그러면 왜 논클러스터링 인덱스에서는 데이터 페이지 주소값을 참조하지 않고 클러스터링 인덱스를 갖고 있는걸까?

 

논-클러스터링 인덱스 페이지에서 클러스터링 인덱스가 아닌 데이터 페이지의 주소값을 갖고 있다고 가정해봅시다. 만약 첫번째 페이지에서 새로운 튜플이 추가되어야 한다면 페이지 분할이 발생하게 됩니다. 그리고 연쇄적으로 논클러스터링 인덱스 테이블에 매핑되어 있던 주소값들도 각각 수정해줘야 하는 문제가 발생합니다.

따라서 MySQL에서는 ID가 직접 변경되지 않는 한 인덱스 페이지에 영향을 주지 않도록 설계되었습니다.

 

 

 

인덱스 적용 기준

카디널리티 : 그룹 내 요소의 개수 (the number of elements in a set or group)

어떤 컬럼에 인덱스를 적용해야 할까? 이 질문에 대한 답은 테이블 컬럼 데이터의 카디널리티를 확인함으로써 결정해볼 수 있습니다. 여기에서 카디널리티란 (그룹 내 요소의 개수)가 높은 것, 즉 컬럼 내 데이터 간 중복 수치가 낮은 것을 얘기합니다.

 

인덱스를 사용하면 좋은 대표적인 경우들은 다음과 같습니다.

• 카디널리티가 높은 컬럼
• WHERE, JOIN, ORDER BY 절에 자주 사용되는 컬럼
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
• 규모가 작지 않은 테이블

 

 

 

인덱스 사용 시 주의사항

1. 잘 활용되지 않는 인덱스는 과감하게 제거하자

• Where 절에 사용되더라도 실제로 자주 사용해야만 인덱스를 설정한 가치가 있습니다.
• 불필요한 인덱스 설정은 오히려 성능저하가 발생시킬 수 있습니다. (insert, delete, update 명령 등으로 인한 성능 저하)

2. 데이터 중복도가 높은 컬럼은 인덱스 효과가 적다. (카디널리티가 낮은 테이블 컬럼)

3. 자주 사용되더라도 Insert / Update / Delete가 자주 일어나는지 고려해야 한다.
    1. 일반적인 웹 서비스와 같은 온라인 트랜잭션 환경에서 쓰기와 읽기 비율은 2:8 또는 1:9입니다.
    2. 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 하나의 방법입니다.

 

 

 

마치며

 

지금까지 인덱스의 기본적인 개념과 클러스터링, 논클러스터링 인덱스에 대해 알아보았습니다. 인덱스를 적용하면 무조건적으로 DB 운영에 있어서 좋은 것이라고 생각했었는데 조회 이외의 작업들로 인해 인덱스가 적용된 테이블의 성능이 오히려 저하될 수 있다는 것을 알 수 있었습니다. 인덱스를 테이블에 적용하기 전에 정말 생각한대로 인덱스가 적용되어 동작하는지, 오히려 성능 저하의 원인이 되지는 않을지 잘 고려하여 적용하는 습관을 들여야겠습니다.

 

---

Reference

• https://www.youtube.com/watch?v=edpYzFgHbqs&t=435
• Real MySQL 8.0 (1권)