[DB] Index(색인)란? 특징, 종류, 주의할 점 등을 쉽게 알아보자.

인덱스라는 말을 정말 많이 들어봤지만, 그냥 걸면 빠르고 좋겠지... 라고만 생각했을 뿐

어떠한 구조인지 어떠한 식으로 동작하는지에 대해서는 생각해본적이 없다.

 

인덱스를 걸면 빠르다는 데 대체 왜 그런건지? 또 항상 그런건지?

또 인덱스 종류들은 무엇이 있는지 한번 알아보자.

Index(색인)란?

인덱스란 인덱싱 된 데이터의 추가적인 인덱스용 데이터 저장 공간을 활용해

DB에 저장된 인덱싱 된 데이터의 검색 속도를 향상시키는 자료구조이다.

 

select 문을 활용해 SCOUNTER 테이블에 있는 AIRPORT 컬럼의 LCY를 조회한다고 해보자.

현재 AIRPORT 컬럼은 인덱싱이 되어 있는 상태로 인덱스용 저장 공간이 할당되어 있다.

 

인덱싱이 걸려 있지 않다면, select 문은 테이블에 있는 데이터를 모두 조회한 후 최종적으로 LCY가 포함된 값을 반환하는 순서로 작동할 것이다.

데이터베이스는 LCY 값의 마지막 위치가 어디인지 모르기 때문에 전체를 탐색하기 때문이다. (Full Table Scan)

 

하지만 수 많은 데이터가 있고 위 조회 쿼리가 자주 실행된다면 수 많은 데이터를 처음부터 끝까지 조회하면서 값을 반환하기 때문에 속도에 저하가 생길 수 밖에 없다.

그래서 위와 같은 상황에서 INDEX(색인)을 활용한다면  AIRPORT에 INDEX를 걸 경우 다음과 같은 과정으로 더 빠르게 결과 값을 조회 해 올 수 있게 된다.

1. key-value 형태의 AIRPORT INDEX 테이블에서 where 조건에 포함된 값을 찾는다.
2. 해당 값의 PK(기본키)를 가져온다.
3. 가져온 PK(기본키)로 원본 테이블에서 값을 조회한다.

 

인덱스를 적용하기 좋은 상황

• ORDER BY, GROUP BY, JOIN, WHERE 등의 조건 절에 자주 사용되는 컬럼
• CUD(CREATE, UPDATE, DELETE)가 자주 발생하지 않는 컬럼
• 중복도가 낮은(카디널리티가 높은) 컬럼

 

인덱스가 사용하는 B-Tree 구조

RDBMS에서 가장 많이 사용되는 인덱스 자료구조 중 하나로, O(logN)의 시간 복잡도를 가지는 자료 구조이다.

B-Tree는 이진 검색 (Binary Search) 방식으로 검색 속도를 높이기 위해 사용된다.

 

일반적으로 많이 사용하는 MySQL은 InnoDB 스토리지 엔진을 사용하며

InnoDB를 사용하는 DB들은 B-Tree 구조를 사용한다고 한다.

MySQL 공식 문서 내용

 

B-Tree 구조에 대해 자세히 알고 싶다면 아래 포스팅을 참고하자.

2024.08.26 - [◼ CS 기초 지식/[데이터베이스]] - B-Tree란? 구조와 연산 과정을 살펴보자

B-Tree란? 구조와 연산 과정을 살펴보자

---

Index의 특징

인덱스는 항상 최신 데이터를 정렬된 상태로 유지한다.

추가적인 인덱스 테이블을 만들기 때문에 데이터베이스 크기의 약 10%정도의 저장공간이 필요하다.

 

위와 같은 특징으로 인해 Index는 아래와 같은 장단점을 가진다.

인덱스 장점

• 검색 대상의 범위를 줄여나가 빠른 검색 속도를 보장한다.
• ORDER BY, GROUP BY, WHERE 절 등이 사용되는 작업이 더욱 효율적으로 처리된다.

인덱스 단점

• 인덱스 생성을 위한 추가적인 저장 공간이 필요하다.
• CUD(CREATE, UPDATE, DELETE) 작업 시 인덱스 테이블을 업데이트로 오버헤드가 발생한다.

 

InnoDB의 Index 저장 위치

기본적으로 Index를 생성하면 해당 데이터는 디스크에 저장된다.

InnoDB는 버퍼 풀을 관리하는데 자주 사용되는 데이터를 메모리에서 직접 액세스할 수 있도록 허용하여 처리 속도를 높인다고 한다.

참고자료 : https://dev.mysql.com/doc/refman/9.0/en/innodb-buffer-pool.html

---

Index 종류

지금까지는 단일 인덱스에 대한 내용이였다면, 여러 종류의 인덱스 중 대표적인 인덱스들에 대해 설명해보려 한다.

클러스터링 인덱스(Clustered Index)와 논 클러스터링 인덱스(Non-Clustered Index)

클러스터링에 대해 설명하기 앞서 Index Page에 대해 짚고 넘어가자.

Index Page란 인덱스 트리의 각 노드에 해당되며, Key - Value로 키 값에 대한 데이터의 위치 정보를 갖고 있어

DBMS가 테이블의 특정 행을 찾도록 도와준다.

 

클러스터링 인덱스

클러스터링 인덱스는 기본적으로 테이블 당 1개만 생성가능하며, 실제 데이터 페이지가 정렬된 형태이다.

PK가 클러스터링 인덱스로 구성되어 있고 UNIQUE 제약 조건과 NOT NULL 제약 조건을 가진 컬럼도 클러스터링 인덱스로 구분되지만 PK가 있다면 PK가 우선순위를 가진다.

클러스터링 인덱스는 위와 같이 "루트 노드 = 인덱스 페이지", "리프 노드 = 데이터 페이지"로 구성되어 있다.

루트 노드에서 부터 하위 노드를 거쳐 리프 노드까지 이동하면서 원하는 조건의 데이터를 찾다가

Key 값으로 데이터 위치를 갖는 값을 얻어 실제 데이터가 위치한 곳을 찾아가 데이터를 가져온다.

 

논 클러스터링 인덱스 (보조 인덱스, 세컨더리 인덱스)

테이블당 여러개 존재 가능하며, 실제 데이터 페이지는 정렬되지 않은 그대로이다.

CREATE INDEX로 인덱스를 만들면 기본적으로 "논 클러스터링 인덱스"이다.

또한 UNIQUE 제약조건 적용 시 자동으로 "논 클러스터링 인덱스"가 생성된다고 한다.

ㅇ

클러스터링 인덱스와 다르게

인덱스 페이지와 데이터 페이지가 분리되어 있어 별도의 인덱스 페이지를 생성하기 위한 추가 공간이 필요하다.

또한 리프 노드가 실제 데이터 페이지 주소와 같지 않고, 실제 데이터 페이지의 주소를 갖고 있다.

그렇기 때문에 인덱스 페이지에서 리프 노드를 먼저 찾고, 실제 데이터가 저장된 주소를 사용해 데이터 페이지에서

찾고자하는 데이터를 가져와야해 클러스터링 인덱스에 비해 I/O 작업이 더 많이 필요하다.

 

정리

클러스터링 인덱스는 Root 노드에서 바로 leaf 노드를 찾아 그 값을 통해 실제 데이터를 찾아가고

논 클러스터링 검색하려는 데이터 위치를 찾기 위해 leaf 노드 까지 탐색해야 한다.

그렇기 때문에 논 클러스터링 인덱스는 클러스터링 인덱스에 비해 I/O 작업이 상대적으로 오래걸리지만

그렇다고 Index를 걸지 않고 테이블 풀 스캔하는 것 보단 훨씬 성능이 좋다.

 

복합 인덱스(Composite Index)

데이터베이스에서 여러 개의 컬럼(열)들을 조합하여 인덱스를 생성하는 것을 말한다.

단일 인덱스(Single Index)가 한 개의 컬럼에 대해 생성되는 것과는 달리, 복합 인덱스는 여러 개의 컬럼을 함께 사용하여 인덱스를 생성한다.

복합 인덱스는 두 개 이상의 테이블에서 조인 조건으로 사용될 수도 있다.

 

단일 인덱스를 여러개 사용하면 되지 않나? 라고 할 수 있다.

하지만 RDBMS에서는 한 테이블에 대한 쿼리를 실행할 때 여러 단일 인덱스가 있다하더라도 1개의 인덱스가 적용된다.

SELECT * FROM member WHERE name = '짱구' AND age = 9

예를 들어 위와 같은 쿼리가 있고 name과 age에 각각 단일 인덱스가 적용되어 있다고 해보자.

이 경우 두 인덱스를 모두 사용하는 것이 아닌 하나의 인덱스만 적용되어 조회된다.

그리고 위 상황에서 name 인덱스만 활용해 조회한다해도 데이터베이스는 name을 인덱스 테이블에서 조회하려 하지만

다음 조건인 age는 인덱싱이 안되있기 때문에 모든 데이터를 스캔하게 될 것이다.

 

이럴 경우 name과 age를 묶는 복합 인덱스를 적용해 사용할 수 있다.

즉, 여러 개의 조건을 가진 쿼리를 실행할 때 훨씬 효율적인 쿼리를 실행할 수 있다.

 

주의점

복합 인덱스를 생성할 때는 생성 순서를 고려해야 한다.

만약 조건 절의 순서와 복합 인덱스를 생성했을 때의 컬럼 순서가 맞지 않으면 복합 인덱스를 검색할 수 없게 된다.

(위 경우 Index Skip Scan으로 스캔이 되는 것 같은데 항상 좋은 상황은 아닌 것 같다. 궁금하다면 해당 키워드에 대해 더 알아보자)

 

복합 인덱스가 적용되는 상황과 적용되지 않는 상황을 아래 복합 인덱스로 살펴보자.

(복합 인덱스의 생성 순서는 WHERE 절에서 먼저 사용되는 컬럼을 앞쪽에 위치시키는 것이 좋다.)

CREATE INDEX composite_member_idx ON member (name, age, address)

 

아래 쿼리는 복합 인덱스를 적용한 컬럼 순서대로 조건문이 선언되어 있어 복합 인덱스가 적용된다.

SELECT * FROM member WHERE name = '짱구'
SELECT * FROM member WHERE name = '짱구' AND age = 9
SELECT * FROM member WHERE name = '짱구' AND age = 9 AND address = '떡잎마을'

 

아래 쿼리는 복합 인덱스의 첫 번째 컬럼인 "name"인데 where 조건문의 첫 번째 조건인 address이기 때문에 복합인덱스를 검색할 수 없다.

SELECT * FROM member WHERE address = '떡잎마을'
SELECT * FROM member WHERE address = '떡잎마을' AND name = '짱구'  
SELECT * FROM member WHERE address = '떡잎마을' AND name = '짱구' AND age = 9

 

하지만 첫 번째 컬럼의 고유값이 적다면 옵티마이저가 자동으로 판단해 Index Skip Scan 사용 여부를 결정한다고 한다.

따라서 무조건 위 상황에서 복합 인덱스가 적용되지 않는다는 것은 아니다.

 

---

Index가 적용되지 않는 경우

 

조회되는 데이터가 전체 데이터의 20 ~ 30% 이상일 경우

InnoDB의 옵티마이저 통계 정보에 따라 다르지만

보통 조회되는 데이터가 전체 데이터의 20 ~ 30%이상이라면 옵티마이저가 풀 테이블 스캔을 고려한다고 한다.

 

Like를 조건문에서 사용할 때 앞에 %를 붙인 경우 인덱스가 적용되지 않는다.

SELECT * FROM member WHERE address LIKE '%떡' // Index 적용 X
SELECT * FROM member WHERE address LIKE '마을%' // Index 적용 O

 

부정문을 조건에서 사용한 경우 인덱스가 적용되지 않는다.

SELECT * FROM member WHERE age != 9 // Index 적용 X
SELECT * FROM member WHERE age = 9 // Index 적용 O

 

Null을 조건에서 사용한 경우 인덱스가 적용되지 않는다.

SELECT * FROM member WHERE address IS NULL // Index 적용 X
SELECT * FROM member WHERE address IS NOT NULL // Index 적용 X

 

인덱스가 적용된 컬럼을 수정하면 인덱스가 적용되지 않는다.

연산, 함수 등 인덱스를 적용할 컬럼에 변경을 가하면 적용되지 않는다.

SELECT * FROM member WHERE age * 10 = 9 // Index 적용 X
SELECT * FROM member WHERE age = 9 * 10 // Index 적용 O

 

IN절을 조건문에서 사용한 경우 인덱스가 적용되지 않는다.

SELECT * FROM member WHERE age IN (8, 9, 10) // Index 적용 X