Real MySQL 인덱스 개인적인 정리

Introduction

인덱스란?
- 인덱스가 필요한 이유
인덱스의 종류
인덱스의 관리
인덱스 스캔 방식

데이터베이스를 다루다 보면 속도와 성능에 대한 고민이 빠질 수 없다.

수백만 개의 데이터가 저장된 테이블에서 원하는 데이터를 빠르게 찾기 위해서는 어떻게 해야 하지?

이 문제를 해결하기 위해 나온 것이 인덱스(Index)이다.

인덱스의 개념과 종류, 그리고 성능 최적화를 위한 인덱스 활용법을 알아보자.|

인덱스란?

인덱스는 테이블의 특정 컬럼 값을 정렬하여 저장하고, 해당 값이 위치한 레코드 주소를 함께 기록한 자료구조이다.
책에서 특정 단어를 찾으려면 처음부터 끝까지 읽는 것보다 색인을 활용해 빠르게 찾는 것이 효율적이다.
- 데이터베이스에서도 인덱스를 활용하면 원하는 데이터를 빠르게 검색할 수 있다.

인덱스가 필요한 이유

테이블에 저장된 데이터를 검색하는 방식은 두 가지이다.

Full Table Scan (전체 검색): 테이블의 모든 데이터를 하나씩 조회하여 원하는 값을 찾는다.
Index Scan (인덱스 검색): 특정 컬럼의 인덱스를 활용하여 필요한 데이터가 저장된 위치를 빠르게 찾는다.

인덱스를 사용하면 전체 데이터를 탐색하는 비효율적인 검색을 피하고, 
최소한의 조회만으로 원하는 데이터를 가져올 수 있다.

인덱스의 종류

프라이머리 키 인덱스

테이블에서 각 레코드를 고유하게 식별하는 컬럼으로 생성된다.

중복이 허용되지 않으며, NULL 값을 가질 수 없다.
InnoDB 엔진에서는 프라이머리 키가 클러스터형 인덱스로 동작하여 레코드가 프라이머리 키 순서대로 정렬된다.

보조 인덱스

프라이머리 키 이외의 컬럼에 대해 생성하는 인덱스이다.

검색 속도를 높이기 위한 용도로 사용되며, 여러 개의 보조 인덱스를 추가할 수 있다.
MySQL의 InnoDB 스토리지 엔진에서는 보조 인덱스가 프라이머리 키를 저장하고 있기 때문에, 보조 인덱스를 사용한 검색 시 프라이머리 키를 추가 조회해야 하는 인덱스 더블 루킹이 발생한다.

유니크 인덱스 (Unique Index)

중복된 값을 허용하지 않는 인덱스이다.

프라이머리 키와 유사하지만, 테이블 내에서 하나 이상의 유니크 인덱스를 가질 수 있다.

다중 컬럼 인덱스

여러 개의 컬럼을 조합하여 생성하는 인덱스이다.

다중 컬럼 인덱스를 만들면 첫 번째 컬럼을 기준으로 정렬되며, 두 번째 컬럼은 첫 번째 컬럼이 동일한 경우에만 정렬된다.
적절하게 사용하면 여러 조건을 포함하는 검색에서 성능을 크게 향상시킬 수 있다.
주의할 점은 컬럼 순서가 중요하다는 것이다.
- 예를 들어 (A, B) 순서로 인덱스를 생성하면, A를 조건으로 검색할 때는 인덱스를 사용할 수 있지만 B만 단독으로 검색할 경우 인덱스를 활용할 수 없다.

해시 인덱스

컬럼 값을 해시(Hash) 값으로 변환하여 저장하는 방식이다.

값의 정확한 일치 검색(= 연산)에 최적화되어 있으며, 범위 검색에는 적합하지 않다.
메모리 기반의 데이터베이스에서 많이 사용된다.

B-Tree 인덱스

가장 일반적으로 사용되는 인덱스 방식으로, 다양한 관계형 데이터베이스에서 기본 인덱스 방식으로 활용된다.

Balanced Tree 구조로 유지되며, 모든 노드가 동일한 깊이를 가지도록 자동으로 조정된다.
- 검색, 삽입, 삭제 연산의 성능을 일정하게 유지하는 중요한 특성이다.
B-Tree는 루트 노드, 브랜치 노드, 리프 노드로 구성되며, 루트 노드에서 시작해 브랜치 노드를 거쳐 리프 노드까지 탐색하여 데이터를 찾는다.
- 검색, 삽입, 삭제 연산의 시간 복잡도는 O(log N)로 일정하며, 대량의 데이터에서도 안정적인 성능을 제공한다.
- 범위 검색과 정렬 작업에 유리하며, <, <=, >, >=, BETWEEN과 같은 범위 연산에서 효율적으로 동작한다.
B-Tree 인덱스의 키 값들은 항상 정렬된 상태로 유지되므로, 인덱스를 활용한 데이터 검색이 빠르게 이루어진다.
삽입 및 삭제 시에는 노드 분할 및 병합이 발생할 수 있으며, 이를 통해 트리의 균형이 유지된다.
루트 노드에서 검색을 시작하여 최하단의 리프 노드까지 이동하면서 데이터를 탐색한다.
- 이 과정에서 불필요한 탐색을 최소화하기 위해 B-Tree 구조는 노드 하나에 여러 개의 키를 저장한다.
B-Tree 인덱스는 데이터 페이지와 인덱스 페이지가 연결된 구조를 가지며, 리프 노드에서 실제 데이터의 위치를 참조하여 검색을 수행한다.

B-Tree 인덱스를 사용하면 데이터 검색 속도를 비약적으로 향상시킬 수 있으며, 특히 대량의 데이터가 있는 테이블에서 쿼리 성능을 최적화하는 데 중요한 역할을 한다.

인덱스의 관리

인덱스 키 추가

새로운 레코드를 삽입할 때, 인덱스에 새로운 키 값을 추가해야 한다.

B-Tree에서는 새로운 값이 들어올 때 적절한 위치를 찾아 삽입한다.
삽입 과정에서 특정 노드가 가득 차면, 노드를 분할하는 과정이 발생할 수 있다.

인덱스 키 삭제

특정 레코드가 삭제되면, 인덱스에서도 해당 키를 제거해야 한다.

B-Tree 구조에서는 삭제된 공간을 재활용하거나, 불필요한 노드가 많아질 경우 병합하여 트리 구조를 최적화한다.

인덱스 키 변경

인덱스에서 특정 키 값을 변경할 때는 삭제 후 새로운 값을 삽입하는 방식으로 처리된다.

변경된 값의 위치가 달라질 경우, 인덱스 구조 내에서 다시 정렬되어야 한다.

인덱스 키 검색

인덱스를 활용하면 O(log N) 시간 복잡도로 원하는 데이터를 검색할 수 있다.

B-Tree 인덱스를 사용할 경우, 루트 노드에서부터 시작하여 브랜치 노드를 거쳐 리프 노드까지 탐색하면서 원하는 키 값을 찾는다.

B-Tree 인덱스의 정렬 및 스캔 방향

B-Tree 인덱스는 정렬된 형태로 유지되며, 특정 키의 범위를 조회할 때 빠르게 탐색할 수 있다.

정방향(오름차순) 및 역방향(내림차순) 스캔이 가능하며, 최적화된 쿼리를 작성하면 더 빠른 검색이 가능하다.

B-Tree 깊이 & 선택도(기수성)

B-Tree의 깊이는 검색 성능에 영향을 미친다. 깊이가 낮을수록 검색이 빠르다.

선택도는 인덱스의 효율성을 결정하는 중요한 요소로, 유니크한 값이 많을수록 검색 성능이 향상된다.

B-Tree 인덱스 사용에 영향을 미치는 요소

인덱스 크기, 데이터 중복도, 검색 패턴, 쓰기 작업 빈도 등에 따라 인덱스의 효율성이 결정된다.

인덱스 스캔 방식

인덱스 레인지 스캔 (Index Range Scan)

특정 범위 내에서 데이터를 찾는 방식이다.

비교 연산자(<, <=, >, >=) 또는 BETWEEN, LIKE과 같은 조건에서 사용된다.
인덱스 내에서 정렬된 데이터를 활용하여 검색하기 때문에 빠르게 조회할 수 있다.
- 예를 들어 WHERE age BETWEEN 20 AND 30과 같은 조건을 사용할 경우, B-Tree 인덱스를 이용해 효율적으로 검색할 수 있다.

인덱스 풀 스캔 (Index Full Scan)

인덱스의 처음부터 끝까지 전체를 읽는 방식이다.

특정 조건을 만족하는 값이 인덱스의 첫 번째 컬럼이 아닐 경우 사용된다.
테이블 전체를 검색하는 것보다 효율적일 수 있으나, 최적의 방식은 아니다.

루스 인덱스 스캔 (Loose Index Scan)

인덱스에서 불필요한 키 값을 건너뛰며 필요한 값만 검색하는 방식이다.

GROUP BY 또는 MIN(), MAX()와 같은 함수에 대한 최적화에 주로 사용된다.

인덱스 스킵 스캔 (Index Skip Scan)

다중 컬럼 인덱스에서 첫 번째 컬럼 조건 없이 두 번째 컬럼부터 검색하는 방식이다.

MySQL 8.0부터 지원되며, 기존에는 불가능했던 검색이 가능해진다.

내림차순 인덱스 (Descending Index)

기본적으로 인덱스는 오름차순으로 정렬되지만, 내림차순 정렬이 필요한 경우 내림차순 인덱스를 생성할 수 있다.

ORDER BY DESC 최적화에 활용되며, 검색 성능 향상에 기여한다.