인덱스에 대해서 알아보자!

이전에 [DB] PK (Primary Key) ID 길이가 성능에 영향을 미칠까? 라는 글을 쓸 때 인덱스에 대해서 간단히 알아보았다.

또한, 데이터베이스 튜닝에 대하여 라는 글을 쓸 때에도 인덱스를 설정하여 DB의 성능을 높이는 방법에 대해서도 알아보았다.

 

해당 글에서는 인덱스에 대해서 더 자세히 알아보겠다.

 

인덱스란?

인덱스란 CUD 의 성능을 희생하고 그 대신에 테이블의 조회 속도를 높여주는 자료구조이다. 

어떤 항목을 조회하는데 60초 이상의 시간이 걸리던 것이 인덱스를 통해서 조회하면 한자리수의 초 단위로 조회할 수도 있을 정도로 데이터베이스 속도를 크게 좌우하는 요소이다. 하지만 마구잡이로 인덱스를 사용하면 사용하기 전보다 성능이 더 떨어질 수도 있다.

그렇기 때문에 인덱스라는 것을 잘 알고 써야 한다.

인덱스의 큰 특징은 데이터들을 정렬한다는 것이다. 이를 통해서 조건 검색에 큰 영향을 끼칠 수 있다.

인덱스 동작 과정부터 보자면, 특정 컬럼에 인덱스를 생성하면 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터 물리적 주소와 함께 저장한다. (주의해야할 점은 인덱스조차도 데이터이기 때문에 데이터베이스 전체 크기의 10% 정도의 추가적인 공간을 할당해주어야하며 생성할 때 시간이 걸리는 것이다.)

인덱스가 생성되면 쿼리문에 해당 인덱스 생성 컬럼을 where 조건으로 건다면 옵티마이저에서 판단하여 해당 인덱스를 타게 된다. 그러면 먼저 인덱스에 저장되어 있는 데이터의 물리적 주소로 가서 데이터를 가져와 검색 속도가 향상될 수 있다.

만약 테이블안에 데이터가 쌓이는데 정렬되지 않은 상태로 저장된다면 where 절에 특정 조건에 맞는 데이터들을 찾아낼 때 레코드의 처음부터 끝까지 찾아보면서 검색 조건에 해당하는지 확인해야 하기 때문에 오랜 시간이 걸릴 수 있다. (-> 이 과정을 full table scan 이라고 한다.)

 

그래서 인덱스를 사용한다면 

1. where 절의 효율 up

• full table scan 없이 인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건에 맞는 데이터들을 빠르게 찾아낼 수 있다.

2. order by 절의 효율 up

• 굉장히 부하가 많이 걸리는 작업인 Order by는 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생된다. 하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 되고, 이미 정렬이 되어 있기 때문에 가져오기만 하면 된다.

3. MIN, MAX 효율 up

• MIN값과 MAX값을 레코드의 시작값과 끝 값 한건씩만 가져오면 되기에 FULL TABE SCAN으로 테이블을 다 뒤져서 작업하는 것보다 훨씬 효율적으로 찾을 수 있다.

위의 3가지의 장점을 얻을 수 있다.

하지만 인덱스는 CUD를 희생하고 조회 성능을 높이는 자료구조이다. INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 INDEX 테이블 내에 있는 값들을 다시 정렬을 해야하고, INDEX 테이블, 원본 테이블 두 군데에서 데이터 수정 작업을 해야 한다.

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

 

그렇기에 Insert, update, delete 연산이 잦은 경우에 paging이 빈번하게 일어나 성능이 악화될 수 있다. 그래서 데이터 변경이 자주 일어난다면 인덱스 사용을 고민해봐야 한다.

인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 현명한 선택일 수 있으니 꼭 주의하여야 한다.

인덱스 생성 전략

1. 조건절에 자주 등장하는 컬럼
2. 항상 = 으로 비교되는 컬럼
3. 중복되는 데이터가 최소한인 컬럼 (분포도가 좋은) 컬럼
4. ORDER BY 절에서 자주 사용되는 컬럼
5. 조인 조건으로 자주 사용되는 컬럼

 

---

 

인덱스는 클러스터형 인덱스와 비클러스터형 인덱스로 나눌 수 있다.

 

클러스터형 인덱스 vs 보조 인덱스

특징	클러스터 인덱스 (Primary Index)	보조 인덱스 (Secondary Index)
저장 방식	데이터가 인덱스 순서에 따라 물리적으로 정렬됨	데이터는 원래 테이블에 저장되고, 인덱스는 별도의 페이지에 위치
리프 노드	리프 노드 자체가 실제 데이터를 저장	리프 노드에 데이터의 위치(RID)와 참조 포인터를 저장
속도	검색 속도가 빠름	검색 속도가 클러스터 인덱스보다 느림
데이터 입력/수정/삭제	입력/수정/삭제가 느림 (데이터 정렬로 인해 페이지 분할이 발생 가능)	입력/수정/삭제가 비교적 빠름
인덱스 개수	테이블당 하나	테이블당 여러 개 생성 가능 (최대 약 250개)
저장 공간	테이블 자체를 활용하여 추가 저장 공간이 적게 듦	별도의 저장 공간이 필요
정렬	인덱스 순서와 데이터의 물리적 순서가 일치	인덱스 순서와 데이터의 물리적 순서가 불일치
사용 예	Primary Key, 테이블의 주요 데이터로 검색	UNIQUE Key, 자주 검색되는 컬럼 (예: 이름, 이메일)
데이터 삽입/삭제 시 부하	정렬된 상태를 유지해야 하므로 높은 부하 발생	정렬 작업이 필요 없어 부하가 적음

 

---

 

인덱스 유형 중 가장 많이 사용하는 인덱스의 구조는 밸런스드 트리 인덱스 구조이다.

B+Tree 인덱스

가장 일반적으로 사용되는 인덱스 유형으로 데이터를 정렬된 형태로 저장하며, 검색, 삽입, 삭제 작업에서 효율적이다.

• MySQL, PostgreSQL 등 대부분의 데이터베이스 시스템에서 기본 인덱스 유형으로 사용됩니다.

B+tree의 구조는 아래 3가지로 이루어져 있다.

1. 루트 노드 :트리의 최상단에 위치하며, 데이터 검색의 시작점이 됩니다.
2. 내부 노드: 검색 범위를 좁히기 위한 경로를 제공합니다.
3. 리프 노드: 실제 데이터나 데이터의 참조 정보를 저장합니다.

B+ Tree의 동작 원리를 살펴보자!

1. 검색 시, 루트 노드에서 시작하여 조건에 맞는 경로를 따라 내려간다.
2. 리프 노드에 도달하면, 해당 데이터나 참조된 데이터가 반환된다.
3. 삽입 및 삭제 시, 트리의 균형을 유지하기 위해 노드 분할(Split) 또는 병합(Merge)이 발생할 수 있다.

 

 

참고

https://coding-factory.tistory.com/746

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%9D%B8%EB%8D%B1%EC%8A%A4index-%ED%95%B5%EC%8B%AC-%EC%84%A4%EA%B3%84-%EC%82%AC%EC%9A%A9-%EB%AC%B8%EB%B2%95-%F0%9F%92%AF-%EC%B4%9D%EC%A0%95%EB%A6%AC