[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 종류

지금까지는 단일 인덱스에 대한 내용이였다.

이 외에도 여러 종류의 인덱스를 알아보고 싶다면 아래 포스팅을 참고하자.

2024.12.31 - [◼ DB] - MySQL의 인덱스 스캔에 대해 알아보자 (feat. 커버링 인덱스)

---

순차 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