MySQL

MySQL의 인덱스 (1)

Salt-Fn 2025. 4. 11. 00:14

Real MySQL의 인덱스를 읽으며 정리를 해볼려고 한다. MySQL8.x 버전의 InnoDB를 기준으로 작성한다.

디스크 읽기 방식

인덱스는 MySQL이 데이터를 더 효율적으로 검색하도록 도와주는 구조로, 특히 데이터가 많을수록 그 효과가 두드러진다. MySql은 B+Tree 기반 인덱스를 사용하는데, 이 구조의 탐색 과정에서 디스크 접근 방식이 성능에 큰 영향을 주게된다. 그래서 인덱스에 대해 알아보기 전 디스크 접근 방식인 랜덤I/O와 순차I/O에 대해 간단히 알아보자.

랜덤 I/O (Random I/O)

랜덤 I/O는 디스크의 서로 떨어진 위치에서 데이터를 읽거나 쓸 때 발생하는 입출력 방식. 책에서 중간중간 필요한 페이지만 펴서 읽는 것처럼, 디스크 헤드가 여러 위치로 이동해야 해서 속도가 느림.

  • 디스크 헤드의 이동이 많음
  • 응답 시간이 길어질 수 있음

순차 I/O (Sequential I/O)

순차 I/O는 디스크에서 연속된 위치의 데이터를 읽거나 쓸 때 발생하는 입출력 방식. 예를 들어, 책을 한 장씩 차례로 넘기며 읽는 것처럼, 디스크의 헤드가 연속된 블록을 따라 움직이기 때문에 헤드 이동이 최소화되고 속도가 빠름.

  • 디스크의 물리적인 이동이 적음
  • 캐시 활용이 용이함
  • 대용량 데이터 처리에 유리

정리하자면, 랜덤 I/O는 속도가 느리지만 유연한 접근이 가능하고, 순차 I/O는 속도가 빠르지만 연속된 데이터에 적합하다.
MySQL에서 인덱스를 사용할 때 이 두 I/O 방식이 어떻게 발생하고, 성능에 어떤 영향을 주는지 이해하는 것은 인덱스를 효과적으로 활용하는 데 중요한 기반이 된다.

인덱스

이제부터 인덱스에 대해 알아보자.

인덱스란?

인덱스는 MySQL이 테이블에서 데이터를 빠르게 검색할 수 있도록 도와주는 데이터 구조다. 데이터베이스에서는 수많은 행 중 원하는 데이터를 찾기 위해 풀 스캔(전체 탐색)을 할 수도 있지만, 이는 매우 비효율적이다. 그래서 인덱스를 통해 원하는 데이터의 위치를 빠르게 찾을 수 있게 한다. 마치 책의 목차나 전화번호부처럼, 필요한 내용을 빠르게 찾도록 돕는다.

인덱스의 구조

MySQL(InnoDB)은 기본적으로 B+Tree 기반의 인덱스를 사용한다. B+Tree에서 B는 Binary(이진) 가 아니라 Balanced(균형 잡힌)를 의미한다. 이진 트리와 달리, B+Tree는 하나의 노드가 여러 개의 자식 노드를 가질 수 있어 데이터를 효과적으로 관리할 수 있다. B+Tree의 특징 중 하나는 실제 데이터가 리프 노드에만 저장된다는 점이다. 루트 노드와 중간 브랜치 노드에는 리프 노드로 가는 경로를 나타내는 키 값만 저장된다. 또한, 리프 노드들은 Linked List 형태로 연결되어 있어 범위 탐색 시에도 매우 효율적이다.

  • B+Tree는 균형 이진 트리(Binary Tree)보다 더 많은 데이터를 가지면서도 균형을 유지할 수 있는 트리 구조다.
  • 모든 데이터는 리프 노드에 저장되고, 리프 노드는 오름차순으로 연결되어 있어 범위 검색도 효율적이다.
  • 루트 → 내부 노드 → 리프 노드로 탐색하면서 원하는 키를 빠르게 찾을 수 있다. 이 과정은 로그(log N) 시간 복잡도로 매우 효율적이다.

인덱스의 장점과 단점

장점

  • 수백만 건의 데이터 중 원하는 값을 빠르게 찾을 수 있음
  • WHERE 조건, JOIN, ORDER BY, GROUP BY 등에서 성능 향상
  • 커버링 인덱스를 활용하면 테이블에 접근하지 않고도 결과를 반환 가능

단점

  • 데이터를 INSERT, UPDATE, DELETE할 때마다 인덱스도 갱신
  • 인덱스가 많을수록 쓰기 작업의 오버헤드가 증가
  • 디스크에 추가적인 공간이 필요함
  • 정렬 상태를 유지해야 하므로 B+Tree 구조에서 노드 분할 등 추가 연산 발생

왜 저장 비용을 감수하면서 까지 인덱스를 사용할까?

 

데이터를 삽입하거나 수정할 때마다 인덱스도 함께 갱신되어야 하므로,인덱스는 쓰기 작업의 성능을 일부 희생하게 된다. 그럼에도 불구하고 인덱스를 사용하는 이유는 대부분의 시스템에서 읽기 작업의 빈도가 쓰기 작업보다 훨씬 많다. 따라서 읽기 속도를 높이기 위해 일부 저장/쓰기 성능을 희생하는 것이, 전체 시스템의 효율성 면에서 더 유리한 경우가 많다. 이는 특히 검색 중심의 서비스나 사용자 응답 속도가 중요한 애플리케이션에서 더욱 중요하게 작용한다. 결국 인덱스는 저장 비용을 감수하면서도 시스템 전반의 응답성과 효율성을 높이기 위한 선택이라 할 수 있다.

MySQL 인덱스 스캔 방식

MySQL 옵티마이저는 쿼리 조건과 인덱스 상태에 따라 다양한 방식으로 인덱스를 스캔한다. 주요 스캔 방식은 다음과 같다.

 

1. 인덱스 레인지 스캔

인덱스의 특정 범위만 탐색하는 방식으로, 가장 자주 사용되는 인덱스 스캔이다. WHERE 절에 범위 조건(>, <, BETWEEN, IN 등)이 있을 때 주로 사용된다. 인덱스 트리를 따라 내려가 조건에 부합하는 시작 지점을 찾고, 이후 인덱스에서 조건이 끝나는 지점까지 연속적으로 읽는다.

SELECT * FROM users WHERE age BETWEEN 20 AND 30;

age 인덱스가 있을 경우, age가 20 이상 30이하인 인덱스 구간만 읽는다.

 

장점

  • 불필요한 인덱스 탐색을 줄이고, 필요한 범위만 빠르게 읽음
  • 효율적인 디스크 I/O 수행

2. 인덱스 풀 스캔

조건 없이 인덱스 전체를 처음부터 끝까지 스캔하는 방식으로, 일종의 인덱스 기반 테이블 풀 스캔이다. 이 방식은 인덱스의 루트 노드부터 리프 노드까지 모든 노드를 순차적으로 탐색하며 데이터를 읽는다. 주로 ORDER BY나 커버링 인덱스를 활용하는 경우에 사용되며,
쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우에도 이 방식이 선택될 수 있다. 예를 들어, 인덱스가 (A, B, C) 순서로 구성되어 있을 때, B 또는 C 컬럼만을 조건으로 사용하는 쿼리에서는 Index Range Scan을 사용할 수 없기 때문에, MySQL은 Index Full Scan을 선택하게 된다.

복합 인덱스 (A, B, C) 는 내부적으로 A → B → C 순서로 정렬되어 있다. 즉, B는 A의 값에 따라 정렬되어 있고, C는 B의 값에 따라 정렬되는 구조다. 따라서 A를 조건절에 사용하지 않고 B 컬럼만으로 Index Range Scan을 시도할 경우, B 값이 원하는 순서로 정렬되어 있다는 보장이 없기 때문에 MySQL은 해당 인덱스를 효율적으로 탐색할 수 없다. 이로 인해 Index Range Scan은 불가능하며, 대신 Index Full Scan 또는 Table Scan이 사용될 수 있다.
SELECT name FROM users ORDER BY name;

name 컬럼에 인덱스가 있다면, 정렬 없이 인덱스 순서대로 출력이 가능하다.

 

장점

  • 정렬 없이도 데이터를 정렬된 순서로 조회 가능
  • 커버링 인덱스면 테이블 접근 없이 인덱스만으로 처리 가능

3. 루스 인덱스 스캔

GROUP BY나 DISTINCT 쿼리에서 중복 값을 건너뛰며 효율적으로 스캔하는 방식. 정렬된 인덱스를 기반으로 중복 제거가 가능할 때 사용된다. 인덱스 리프 노드를 순차 탐색하면서, 중복 값을 건너뛰고 다음 고유 값으로 이동한다. 매번 새 고유 값이 나타나는 위치만 접근 (스킵하면서 진행) 한다.

SELECT DISTINCT category FROM products;

category에 인덱스가 있을 경우, 중복된 값을 건너뛰며 한 번씩만 조회한다.

 

장점

  • 중복 제거 쿼리의 속도가 크게 향상
  • 테이블을 전혀 읽지 않고도 원하는 결과를 만들 수 있음
  • Group by 또는 MAX(), MIN() 함수 최적화 가능

4.인덱스 스킵 스캔

Index Skip Scan은 복합 인덱스에서 선두 컬럼이 조건절에 없어도, MySQL이 내부적으로 선두 컬럼의 가능한 값을 하나씩 고정하면서 후속 컬럼으로 탐색을 반복 수행하는 방식이다. MySQL 8.0 이상부터 옵티마이저가 상황에 따라 자동으로 사용 여부를 판단한다.

 

예를 들어 복합 인덱스가 (gender, age)로 구성되어 있다고 할 때, 아래와 같은 쿼리가 실행되었다고 가정하자.

SELECT * FROM users WHERE age = 30;

일반적으로는 gender 조건이 없기 때문에 이 인덱스를 사용할 수 없지만, Index Skip Scan은 내부적으로 가능한 gender 값을 하나씩 고정하여 다음과 같은 식으로 탐색을 시도한다.

1. gender = 'M' AND age = 30

2. gender = 'F' AND age = 30

...

각 gender 값에 대해 Index Range Scan을 반복 수행하고, 그 결과를 합쳐 최종 결과를 만든다.

 

장점

  • 선두 컬럼 조건이 없어도 복합 인덱스를 재활용 할 수 있음

인덱스 스킵 스캔은 옵티마이저가 선택도를 보고 판단하므로 항상 사용되지는 않는다.

 

지금까지 인덱스의 개념과 MySQL에서 사용되는 네 가지 주요 인덱스 스캔 방식에 대해 알아보았다. 다음 글에서는 다중 컬럼 인덱스, 함수 기반 인덱스, 멀티 밸류 인덱스, 클러스터링 인덱스와 세컨더리 인덱스 등 인덱스의 다양한 종류와 특성에 대해 더 깊이 살펴볼 예정이다.

저작자표시 비영리 변경금지 (새창열림)