데이터베이스 Index란?

인덱스(Index)란?

보통 배열에서 많이 썼던 단어로 "색인"이라는 뜻을 가집니다.

 

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

 

우리가 책에서 원하는 내용을 찾을때 모든 페이지를 찾아보는 것은 오랜 시간이 걸립니다.

따라서 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인을 추가합니다.

 

데이터베이스의 인덱스는 책의 색인과 유사합니다.

 

데이터베이스에서도 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있습니다.

 

https://mangkyu.tistory.com/96

인덱스를 사용하는 이유?

인덱스를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 전체를 탐색하는 Full Scan을 수행해야 합니다.

따라서 처리 속도가 떨어지게 됩니다.  O(N)의 시간 복잡도

 

인덱스를 사용하게 되면 데이터들이 정렬되어 있기 때문에 조건 검색이라는 영역에서 굉장한 장점이 됩니다.

 

• Where 절의 효율성

만약 데이터가 뒤죽박죽이라면 Full scan 하면서 검색 조건과 맞는지 비교합니다.

하지만 인덱스 테이블은 데이터가 정렬되어 있기 때문에 해당 조건에 맞는 데이터들을 빠르게 찾아낼 수 있습니다.

만약 age 순으로 정렬되어 있고 'select age from members where age <=30'라는 구문을 사용했을 때 인덱스 테이블의 age가 30을 초과하는 수가 나온다면 더 이상 탐색하지 않을 수 있습니다.

 

• Order by 절의 효율성

인덱스를 사용하면 Order by에 의한 Sort과정을 피할 수 있습니다.

정렬과 동시에 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생됩니다. 따라서 Order by는 굉장히 부하가 많이 걸리는 작업입니다.

인덱스를 사용하면 이미 정렬되어 있기 때문에 이런 자원의 소모를 하지 않습니다.

 

• MIN, MAX의 효율적인 처리

이것 또한 데이터가 정렬되어 있기 때문에 가능한 장점입니다.

MIN값과, MAX값은 인덱스 테이블의 시작 값과 끝 값 한건씩만 가져오면 됩니다.

O(1)의 시간 복잡도

 

 

그러면 모든 데이터에 인덱스를 적용해서 쓰면 좋겠네요?

인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요합니다.

 

만약 CREATE, DELETE , UPDATE 가 빈번한 속성에 인덱스를 걸게 되면 성능이 오히려 저하되는 역효과가 발생할 수 있습니다.

 

인덱스를 관리하기 위해서 추가 작업이 필요한데 INSERT, DELETE, UPDATE가 수행되면 그에 따른 오버헤드가 발생합니다.

 

• INSERT : 새로운 데이터에 대한 인덱스를 추가함.
• DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함
• UPDATE : 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함.

이처럼 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해주기 때문에 UPDATE와 DELETE가 빈번하게 발생한다면 실제 데이터는 10만 건이지만 인덱스는 100만 건이 넘어가게 되며 인덱스의 크기가 비대해져 오히려 성능이 저해되는 역효과가 발생할 수 있습니다.

 

 

그러면 어느 곳에 인덱스를 적용해서 쓰면 좋을까요?

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 칼럼
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 칼럼
• 항상 =으로 비교되는 칼럼
• 중복되는 데이터가 최소한인 칼럼

만약 모든 값이 중복된다면? 인덱스로써의 가치는 전혀 없습니다.

 

인덱스를 사용하는 것만큼이나 생성된 인덱스를 관리하는 것도 중요합니다.

따라서 사용하지 않는 인덱스는 바로 제거해야 합니다.

 

인덱스의 자료구조

B- Tree, B+ Tree라는 자료구조를 사용합니다.

이에 관심이 있으신 분들은 B-Tree, B+Tree를 정리한 글을 쭉 보시면 좋을 것 같습니다.

https://junuuu.tistory.com/13?category=970686 

B-Tree 와 B+Tree

 

Clustered Index

해당 키 값을 기반으로 테이블이나 뷰의 데이터 행을 정렬하고 저장합니다.

 

테이블의 데이터를 지정된 칼럼에 대해 물리적으로 데이터를 재배열합니다.

데이터가 테이블에 삽입되는 순서에 상관없이 Index로 생성되어 있는 특정 칼럼을 기준으로 정렬되어 삽입됩니다.

(데이터 삽입, 수정, 삭제 시 테이블의 데이터 재정렬)

 

예를 들어, id가 클러스터형 인덱스로 지정되어 있으면 테이블은 id 값을 기준으로 데이터들이 정렬되어 있습니다.

https://gwang920.github.io/database/clusterednonclustered/

 

데이터 행 자체는 한 가지 순서로만 저장될 수 있으므로 테이블당 클러스터형 인덱스는 하나만 존재할 수 있습니다.

 

Database에서 primary key의 제약조건은 클러스터 된 인덱스를 자동으로 생성하기 때문에 우리가 일반적으로 테이블을 생성할 때 특정 칼럼에 primary key를 지정했다면 자료가 자동으로 정렬됩니다.

 

위의 테이블에 만약  Name : 넬, Birth : 1980 데이터를 ID : 2에 추가한다면 테이블의 상태는 다음과 같이 변합니다.

https://gwang920.github.io/database/clusterednonclustered/

데이터의 순서는 ID값으로 결정되기 때문에 중간에 새로운 데이터가 삽입된다면 이후에 모든 칼럼을 한 칸씩 이동시켜 주어야 합니다.

만약 ID : 2 이후에 현재는 3개밖에 존재하지 않지만 만개/백만 개의 데이터가 존재한다면 Insert에 소모되는 비용이 어마어마하게 될 것입니다.

따라서 PK의 값을 어떤 칼럼으로 선택하는가에 따라 DB의 성능이 좌우됩니다.

 

Clustered 인덱스의 구조

https://gwang920.github.io/database/clusterednonclustered/

만약 id값이 6인 데이터에 접근하고자 한다면 6은 4~7 사이에 존재하기 때문에 102 페이지에 있음을 알 수 있습니다.

Data Page의 데이터들이 순차적으로 정렬되어 있으며 Leaf Level과 DataPage가 동일한 구조를 가집니다.

 

사용하기 유용한 상황

• 테이블 데이터가 자주 업데이트되지 않는 경우
• 항상 정렬된 방식으로 데이터를 반환해야 하는 경우(Order by절이 적용되어있음)
• 읽기 작업이 월등하게 많은 경우

[참고]

Clustered Index는 pk 값으로 자동 생성되는 것과 별개로 설정을 토해 테이블 내에서 원하는 대로 생성할 수 있습니다.

위의 Table을 Birth를 기준으로 Clustered Index를 생성한다면 다음과 같은 테이블이 생성됩니다.

https://gwang920.github.io/database/clusterednonclustered/

Non Clustered Index

데이터 행으로부터 독립적인 인덱스 구조를 가집니다.

각 키 값 항목에는 해당 키 값이 포함된 데이터 행에 대한 포인터가 있습니다.

 

https://gwang920.github.io/database/clusterednonclustered/

마치 책에서 index 페이지를 따로 나눈 것처럼 인덱스는 테이블 데이터화 함께 저장되는 것이 아니라 따로 저장됩니다.

 

하나의 테이블에 여러 개의 Non Clustered Index가 존재할 수 있습니다.

 

Non Clustered Index의 예시를 보겠습니다.

https://gwang920.github.io/database/clusterednonclustered/

테이블의 ID 키 값과 포인터인 Address를 통해 실제 데이터에 접근합니다.

 

ID : 4에 해당하는 가수의 이름을 알고 싶다면 120번지로 이동하고 Name을 확인할 수 있습니다.

그림에서 볼 수 있듯이 Clustered 와의 차이는 순차적으로 Index가 정렬되어 있지 않습니다.

 

Non Clustered 인덱스의 구조

https://gwang920.github.io/database/clusterednonclustered/

Clustered 구조와는 다르게 Leaf Level와 Data Page가 구분되어 있습니다.

Leaf Level에서는 키값에 대한 포인터 Address를 가지고 있으며 Address를 통해 실제 Data Page에 접근합니다.

또한 Data Page는 정렬되어 있지 않습니다.

 

사용하기 유용한 상황

• where 절이나 Join 절과 같이 조건 물을 활용하여 테이블을 필터링하고자 할 때
• 데이터가 자주 업데이트될 때
• 특정 칼럼이 쿼리에서 자주 사용될 때

Clustered Index와 Non Clustered Index 정리

Clustered		Non Clustered
항상 순서를 유지한다		순서와 상관 없다
한 테이블당 하나만 존재한다 (테이블 인덱스)		한 테이블에 여러개 생성할 수 있다
범위 검색에 유리하다 (군집화!)		index를 저장할 추가적인 공간이 필요하다
데이터가 많아 질수록 Insert 성능이 나빠진다		Insert시 추가 작업 (인덱스 생성) 필요하다

 

 

출처

https://mangkyu.tistory.com/96

[Database] 인덱스(index)란?

https://docs.microsoft.com/ko-kr/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15 

클러스터형 및 비클러스터형 인덱스 소개 - SQL Server

https://gwang920.github.io/database/clusterednonclustered​/

Clustered vs NonClustered (index 개념)