[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(기본키)로 원본 테이블에서 값을 조회한다.

 

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

조건 절에 자주 사용되는 컬럼

인덱스를 생성하면 인덱스당 원본 테이블의 10 ~ 30%에 해당하는 디스크 용량을 차지하게 된다.

즉, 무분별한 인덱스 생성을 해선 안된다.

따라서 ORDER BY, GROUP BY, JOIN, WHERE 등 조건절에 자주 사용되는 컬럼에 인덱스를 적용하는 것이 적합하다.

 

CUD(CREATE, UPDATE, DELETE)가 자주 발생하지 않는 컬럼

인덱스는 B-Tree 자료 구조를 사용하고 있어 데이터 추가/삭제/변경에 취약하다.

이에 대해선 다음 목차에서 설명한다.

 

카디널리티가 높은 컬럼

인덱스 키 값 가운데 유니크한 값의 수를 의미한다.

전체 인덱스 키 값이 100개라면 그중 유니크한 값이 10개라 가정했을 때 카디널리티는 10이다.

즉, 카디널리티가 낮을 수록 중복도가 높고, 카디널리티가 높을 수록 중복도가 낮다는 것을 알 수 있다.

카디널리티가 높을 수록 검색 대상이 줄어들기 때문에 검색 속도는 더욱 빨라진다.

이 처럼 인덱스를 적용할 때 카디널리티를 고려하는 것 또한 중요하다.

 

인덱스가 사용하는 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 제약조건 적용 시 자동으로 "논 클러스터링 인덱스"가 생성된다고 한다.

논클러스터링 인덱스는 클러스터링 인덱스와 다음과 같은 차이점이 있다.

첫째, 인덱스 페이지와 데이터 페이지가 서로 분리되어 있어 별도의 인덱스 페이지를 저장할 추가 공간이 필요하다.

둘째, 리프 노드에는 실제 데이터가 직접 저장되어 있지 않고, 대신 실제 데이터가 저장된 페이지의 주소를 갖고 있다.

이러한 구조로 인해 데이터를 검색할 때는 다음과 같은 두 단계의 과정이 필요하다

1. 인덱스 페이지에서 리프 노드를 찾는다
2. 리프 노드에 저장된 주소를 통해 실제 데이터 페이지에서 원하는 데이터를 가져온다

따라서 클러스터링 인덱스에 비해 더 많은 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 사용 여부를 결정한다고 한다.

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

 

---

순차 I/O와 랜덤 I/O

디스크 I/O에는 순차 I/O와 랜덤 I/O가 있다.

순차 I/O

데이터를 순서대로 차례차례 읽거나 쓰는 방식이다.

디스크 헤더의 움직임이 최소화되어 처리 속도가 빠르지만

원하는 데이터까지 순차적으로 접근해야해 중간 데이터 삽입/삭제/조회 시 오히려 느릴 수 있다.

(디스크 헤더 : 데이터를 읽고 쓰는 장치)

 

랜덤 I/O

데이터를 임의의 위치에서 읽거나 쓰는 방식이다.

원하는 데이터에 직접 접근 가능해 필요한 데이터만 선택적으로 처리 가능하다는 장점이 있다.

하지만 디스크 헤더가 여러 위치로 이동해야해 순차 I/O보다 속도가 느리다는 단점이 있다.

 

디스크에 데이터를 읽고 쓰는데에는 디스크 헤더를 움직를 옮기는 단계에서 결정된다.

랜덤 I/O는 순차 I/O에 비해 디스크 헤더가 여러 번 움직이기 때문에 느리기 때문에

여러번 읽기 또는 쓰기 요청을 하는 랜덤 I/O 작업이 부하가 훨씬 크다.

(InnoDB는 버퍼 풀을 사용해 메모리에 자주 읽는 데이터를 저장하여 랜덤 I/O 작업을 줄임.)

 

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하고, 풀 테이블 스캔은 순차 I/O를 사용한다.

그래서 큰 테이블의 레코드를 읽는 작업에서는 순차 I/O가 더 빠르기 때문에 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다.

일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적이라 볼수 있다.

여기서 쿼리 튜닝이란 꼭 필요한 데이터를 읽도록 쿼리를 개선하는 것을 의미한다.

---

디스크 장치 HDD와 SDD

DBMS는 항상 디스크 장치가 병목이 된다.

SSD는 기존 HDD에서 데이터 저장용 플래터(원판)을 제거하고 플래시 메모리를 장착한다.

그래서 디스크 원판을 기계적으로 회전시킬 필요가 없어 아주 빨리 데이터를 읽고 쓸 수 있다.

플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다.

그리고 컴퓨터의 RAM 보다는 느리지만 HDD보다는 훨씬 빠르다.

SSD의 장점은 HDD보다 랜덤 I/O가 훨씬 빠르다는 점이다.

 

보통 쿼리를 날릴 때 조건문을 사용해 조회,인덱스를 사용할 때 B-Tree 구조로 탐색, 트랜잭션 처리 시 서로 다른 위치의 데이터를 수정

하는 이유로 DBMS에서 순차 I/O 작업은 그다지 비중이 크지 않고 랜덤 I/O를 통해 읽고 쓰는 작업이 대부분이다.

따라서 SSD는 DBMS용 스토리지에 최적이라 볼 수 있다.

---

Index가 적용되지 않는 경우

 

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

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

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

 

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

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

 

NOT으로 비교된 경우

NOT자체가 인덱스를 타지 않는 것이 아니다.

다음과 같이 NOT과 함께 사용된 비교 연산자로 나온 데이터의 비율이 높은 경우에 인덱스를 타지 않는다고 한다.

IN절의 경우에도, IN에 포함된 데이터들의 비율이 높다면 인덱스를 타지 않는다고 한다.

SELECT * FROM member WHERE age <> 20 // Index 적용 X
SELECT * FROM member WHERE age NOT IN (20, 21, 22) // Index 적용 X
SELECT * FROM member WHERE company IS NOT NULL // Index 적용 X

 

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

다른 DBMS에서는 NULL 값이 인덱스에 저장되지 않지만 MySQL에서는 NULL 값도 인덱스에 저장된다고 한다.

따라서 다음 쿼리도 작업 범위 결정 조건으로 인덱스를 사용한다고 한다.

SELECT * FROM member WHERE address IS NULL

 

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

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

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