Database Index, 제대로 알아보기

안녕하세요. 오늘은 데이터베이스의 주요 개념인 인덱스에 대해 깊이 알아보고 쓰는 포스팅입니다.

데이터베이스를 사용하면서도 유용하고 면접을 볼 때에도 자주 나오니 한 번 다뤄봐야겠더라구요.

혹시 빠진 부분이나, 추가했으면 하는 부분은 댓글로 남겨주시면 정말정말 감사하겠습니다 👀✨

 

***************** INDEX *****************

 

🔑 Index?

🍪 Index의 구조

🏷 Index의 종류

Clustered Index

Non-Clustered Index

 

 

********************************************

 

 

📕 Index ? 

데이터베이스에서의 인덱스란 무엇일까요? 또, 어떤 역할을 할지 알아보도록 합시다. 

 

인덱스는 아주 대표적인 비유로 책에서의 목차가 있는데요.

예를 들어 데이터베이스를 다룬 책을 봤을 때, 가장 먼저 책의 목차 부분이 보입니다.

데이터베이스 개념의 RDB를 알고 싶다면 목차에서 RDB 부분의 페이지를 찾아 바로 확인할 수 있습니다.

그런데, 이 책에 목차가 없어진다면 어떨까요?

페이지를 한 장씩 확인하면서 RDB에 해당하는 내용을 찾아야 하겠죠.

속도 측면에서 목차를 통해 해당하는 내용을 찾는게 훨씬 빠르다는 것을 알 수 있습니다.

 

데이터베이스의 인덱스도 동일합니다.

데이터베이스의 조회 성능을 높이기 위해서 인덱스를 설정합니다. 

이때, 인덱스를 사용하여 조회하는 것을 Index Scan이라고 하며, 인덱스없이 전체를 조회하는 것은 Full Scan이라고 합니다.

인덱스 스캔은 예를 들어 유저 테이블에 유저번호, 이름, 주소 등이 있을 때 유저 번호에 인덱스를 걸어 유저를 번호로 빠르게 조회하는 방식입니다.

인덱스는 한 테이블에 한 개 이상의 컬럼에 적용할 수 있습니다.

 

그렇다면 인덱스는 무조건적으로 많으면 좋을까요?

위의 예시로 이름, 주소에도 인덱스를 걸어도 성능이 좋아질까요?

아닙니다. 인덱스는 조회 성능을 높이기 위해 특정 자료 구조로 추가 공간을 사용하여 관리합니다. 

이 자료구조의 정확한 내용은 아래에서 확인하겠습니다.

추가 공간을 사용할 뿐만 아니라 Insert, Update, delete 등의 명령이 자주 발생한다면 성능이 떨어질 수도 있게 됩니다.

따라서, 인덱스를 설정할 때에는 위와 같은 트레이드 오프 관계를 적절히 고려하여 설정해야 합니다.

 

위의 내용을 정리하자면,

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

장점 👉🏻 조회 성능을 높혀줌.

단점 👉🏻 추가 공간이 필요하며 조회 이외의 명령에 의해 성능이 떨어질 수도 있으므로 적절한 경우에 설정해야 함. 인덱스를 관리하는 추가 작업이 필요함.

 

 

🏗  인덱스의 구조

인덱스의 일반적인 구조로는 B-Tree 구조입니다. (공식문서 참고 - MySQL Optimization, Oracle Indexes : B-Tree)

B-Tree는 Balanced Trees인데요. 정렬을 통한 구조로 검색이 항상 동일한 시간을 갖게끔 만들어줍니다.

 

Oracle : Overview of Indexes - Figure 3-1 Internal Structure of a B-tree Index

 

✔️ B-Tree

B-Tree Index는 범위를 기반으로 나뉘어진 정렬된 리스트입니다. 

이 구조는 두 가지 블록을 갖는데요, 바로 검색을 위한 Branch Blocks와 값을 저장하는 Leaf Blocks입니다.

 

✔️ Branch Blocks

상위 수준의 Branch Blocks에는 하위 수준의 Branch Blocks을 가리키는 인덱스 데이터를 포함합니다. 

예를 들어, 1 level(root - level 0)의 가장 왼쪽 Branch Blocks 에는 0~40라는 범위를 가리키는 인덱스 데이터를 포함하고 있습니다.

또, 그 내부에는 0~10, 11 ~19... 등의 데이터를 가지고 있는데, 각 항목을 통해 Leaf Blocks을 범위하여금 찾을 수 있게 됩니다.

 

✔️ Leaf Blocks

Leaf Block에는 모든 인덱스의 데이터 값과 실제 행을 찾는데 사용되는 rowid를 포함합니다.

그래서 그림을 보시면 (key, rowid)의 형태를 볼 수 있으며, 각 항목은 (key, rowid) 별로 정렬됩니다.

그래서 인덱싱의 값만 접근하는 경우에는 인덱스 블록에만 접근을 하고,

다른 데이터를 검색할 때에는 추가적으로 rowid를 이용하여 테이블 행을 찾습니다.

덕분에 Min()이나 Max()를 사용할 때 양 끝의 데이터를 불러오면 되기 때문에 성능에 긍정적인 효과를 미칠 수도 있습니다.

 

이 때, 그림을 자세히 보시면 Leaf Blocks 들 사이에 화살표가 보이는데요.

Leaf Blocks 자체도 이중으로 연결되어있습니다.

수평적 탐색을 위한 장치로, 조건절을 만족하는 데이터를 모두 찾거나 rowid를 얻기 위해 사용됩니다.

 

 

🗂  인덱스의 종류

인덱스는 직접 설정할 수도 있고 자동적으로 생성되기도 합니다.

자동적으로 생성되는 경우는 두 가지가 있는데요.

 

특정 컬럼에 PK(primary Key)를 설정하거나 Unique 제약조건을 설정할 때 생성됩니다.

제약조건에 대해 더 많은 정보를 보려면 이 포스팅을 참고하시는 것도 좋을 것 같습니다 ㅎㅎ

둘 다 인덱스가 생성되기는 하지만, 다른 구조로 생성되는데요.

 

PK를 설정하게 되면 Clustered Index가 자동 생성되고,

Unique 제약조건을 설정하게 되면 Non-Clustered Index가 자동 생성됩니다.

 

간단하게 훑어보자면, 클러스터형 인덱스는 데이터를 직접 포함한다는 점과 저장과 동시에 데이터를 정렬한다는 점이 다릅니다.

마치 클러스터형이 단어가 정렬되어 있는 사전과 같다면, 비클러스터형 인덱스는 찾아보기가 있는 일반 책과 같습니다.

 

✔️ Clustered Index

클러스터형 인덱스는 물리적으로 데이터들을 정리합니다.

데이터가 삽입되는 순서에 상관없이 항상 인덱스의 행을 기준으로 정리된 상태이며,

테이블의 행을 정리하는 기준은 이 인덱스의 정리 기준이 되기 때문에 테이블 당 하나의 클러스터형 인덱스를 포함할 수 있습니다.

 

레코드를 삽입하거나 업데이트할 때마다 클러스터된 인덱스가 순서가 유지되도록 보장하는데요.

그래서 조회를 할 때에는 성능이 굉장히 좋지만, 삽입과 업데이트가 빈번한 경우에는 성능에 문제가 될 수 있습니다.

 

중간 노드를 생략한 클러스터형 인덱스 트리 예시

클러스터형 인덱스는 데이터를 Leaf Block이 직접 가지고 있습니다.

인덱스 페이지를 키값과 데이터 페이지의 번호로 구성하고,

검색하고자하는 데이터의 키 값으로 페이지 번호를 알아내어 데이터를 찾습니다.

 

 

👉🏻 클러스터형 인덱스 생성

클러스터형 인덱스를 생성되는 환경에는 두 가지 방법이 있습니다.

 

1. Primary key 설정하기

2. Clustered 조건 추가하기

 

-- 1. Via primary key constraint
ALTER TABLE 테이블명 ADD CONSTRAINT pk_이름
PRIMARY KEY CLUSTERED ([컬럼명, ...]);

-- 2. Using create index statement
CREATE CLUSTERED INDEX index_이름 ON 테이블명([컬럼명, ...]);

 

 

✔️ Non-Clustered Index

비클러스터형 인덱스는 물리적으로 데이터를 정렬하지 않은 상태로 저장됩니다.

데이터와는 무관하게 데이터가 추가될 때 생성되는 정렬된 인덱스에 의해 저장됩니다.

그래서 클러스터형 인덱스와 비교하면 검색 속도는 느리지만, 입력이나 수정, 삭제의 명령에는 성능이 더 뛰어날 수 있습니다.

 

비클러스터형 인덱스는 데이터를 직접 가지고 있지 않고, 데이터의 위치를 가리킵니다.

ms 공식문서에서는 데이터를 가리키는 포인터를 행 로케이터라고 소개합니다.

이 때, 자세하게 보면 클러스터형 인덱스를 가리키거나 실제 데이터를 저장한 위치를 가리키는 두 가지 형태가 있습니다. 

 

 

클러스터형 인덱스를 가리키는 인덱스 키

데이터 페이지를 가리키는 포인터

 

가리키는 대상이 클러스터형 테이블의 경우 행 로케이터는 클러스터형 인덱스 키이고,

데이터 페이지가 존재하는 힙 영역을 가리키는 경우 행에 대한 포인터를 가지고 있습니다.

 

 

👉🏻 비클러스터형 인덱스 생성

비클러스터형 인덱스를 생성되는 환경에는 두 가지 방법이 있습니다.

 

1. nonclustered 조건 추가하기

2. 기본 Index 설정

 

-- By using the non-clustered index
CREATE NONCLUSTERED INDEX Index_이름 ON 테이블명 ([컬럼명, ...]);


CREATE INDEX Index_이름 ON 테이블명 ([컬럼명, ...]);

 

 

클러스터형 인덱스와 비클러스터형 인덱스를 잘 나타낸 그림도 같이 첨부합니다.

https://www.red-gate.com/simple-talk/sql/learn-sql-server/effective-clustered-indexes/

 

 

 

지금까지 데이터베이스의 인덱스에 대해 자세하게 다뤄보는 시간을 가졌습니다.

생각보다 시간이 꽤 걸리는 포스팅이었네요 🤣

그래도 포스팅하면서 정리가 되니 좋네요,,ㅎㅎㅎㅎ

 

 

참고 - https://medium.com/fintechexplained/clustered-vs-non-clustered-index-8efed55ed7b9