[개발지식] 인덱스 정의 및 효율적인 인덱스 구성을 위한 전략

Hyo Kyun Lee·2025년 4월 17일

개발지식 인덱스

개발지식

목록 보기

83/84

1. 개요

무작정 인덱스가 좋다, 성능을 개선할 수 있다라는 결과론적 관점이 아닌 문제 상황이 발생하였을때 인덱스를 하나의 전략으로 사용할 수 있겠다는 방법론적 관점에서 접근을 하는 것이 중요하다.

일단 인덱스의 개념과 목적을 명확히 이해한 후에 사용하는 것이 여러모로 더 나을 것이라는 생각에, 먼저 인덱스에 대해 이론적으로 공부한 내용을 기록해보았다.

2. 인덱스의 개념

인덱스는 사전과 같다.

우리가 알고 싶은 영단어를 찾는다고 할 때, 영단어가 아무런 기준이나 정렬없이 무작위로 분산되어 있다면 모든 페이지를 일일이 확인해가면서 찾아내야 할 것이다.

하지만 [단어 순서대로]라는 명확한 정렬 방식으로 데이터가 질서 있게 잡혀있고, 이 기준을 "인덱싱", 즉 "단어를 찾는데 가장 유리한 방법이자 정렬 기준"인 "알파벳 순서"대로 인덱싱하여 우리는 원하는 단어를 해당 인덱싱, 즉 기준을 통해 매우 빠른 시간안에 금방 찾을 수 있다.

인덱스를 어렵게 이해하지 말고 좀 더 쉽게 바라보도록 하자.

인덱스의 체계는 사전과 같다.
인덱스는 기준, 데이터의 정렬 방법을 간접적으로 정의한다.

3. 힌트의 개념

MySQL 기준으로 쿼리를 실행하면 DBMS는 이 쿼리를 실행하기 위한 동작 계획을 세우는데, 사용자는 힌트를 통하여 효율적인 동작 계획을 정립하는데 도움을 줄 수 있다.

DBMS 측에서는 이러한 힌트를 활용하여 최적의 실행 경로를 탐색하는데, 힌트 내용을 직접 작성하거나 쿼리 블록 범위에 공식적으로 제공하는 힌트 구문을 사용할 수 있다.

힌트는 Query Optimizer에 대한 개념과 연결할 수 있는데, 일단 인덱스에 대한 개념을 더 이해하고 살펴보는 것이 좋겠다.

4. 인덱스 동작원리 1 - 설정방법에 따른 인덱스 종류

MySQL 기준으로 설정하는 방법에 따라 인덱스 종류를 나눌 수 있는데, 우리가 흔히 알고있는 [PK는 곧 index이다]라는 개념이 여기서 등장한다.

Clustered Index

MySQL이 자동으로 설정하는 Index.

PK가 있다면 해당 Key 값을 기준으로 MySQL이 자동으로 Clustered Index을 설정하며, 인덱스를 정의하는 여러 상태값 중 하나이자 열(Column)이다.

PK가 없다면 Unique 컬럼을 차선책으로 선택하여 Clustered Index로 선정하고, 그마저도 없다면 Hidden Clustered Index Key를 만들어 Clustered Index를 만들어낸다.

기본적으로 PK, Unique 속성은 중복값이 없고 이에 따라 카디널리티(유일척도)가 높으므로 MySQL이 기본적으로 사용하는 값이며 또한 테이블 당 단 하나만 저장하여, Clustered Index는 테이블 별로 유일한 정렬 기준을 지니게 된다.

Non Clustered Index

MySQL이 아닌 개발자가 인위적으로 생성한 인덱스라면 모두 Non Clustered Index에 해당한다.

Index Key값을 단일로 하느냐, 다중으로 하느냐에 따라 단일 인덱스 및 복합 인덱스로 분류할 수 있다.

MS-SQL의 경우 인덱스 키값에 해당 키에 대한 행 정보(포인터)가 value 값으로 있는데, 인덱스를 지정하면 인덱스를 통해 찾고자 하는 행에 대한 위치 정보를 파악하여 해당하는 정보를 금방 찾을 수 있게 되므로 바로 이 부분이 인덱스의 핵심이라 볼 수 있겠다.

5. 인덱스 동작원리 2 - B Tree

MySQL에서 인덱스를 저장하는 자료구조로 B Tree를 사용한다.

쉽게 이해하면 기존 이진트리 방식에 비해 하위 노드로 접근할 수 있는 경로가 많아지고 그만큼 유연해져 찾고자 하는 데이터를 빠른 시간 안에 찾을 수 있다는 것이 핵심이다.

위와 같이, index로 age 컬럼을 Clustered Index로 저장했다고 하자.

select name from table where age = 22;
select name from table where name = 'LEE HYO KYUN';

인덱스의 차이는 age가 키값으로 해당 열에 대한 위치 정보가 포함되어 있다.

이 정보를 활용하여 인덱스의 동작원리를 쉽게 이해해보자.

case1) where 조건 없이 table의 모든 name 데이터를 조회한다.
-> 모든 데이터를 얻기 위해 MySQL의 Optimzer는 full scan을 진행한다.

case2) where 조건을 통해 index 컬럼으로 설정한 age 값을 특정한다.
-> Optimizer가 쿼리를 실행하기 위해 전략을 생각하는데, index로 설정한 age 행 및 이에 대한 위치 정보가 leaf node에 남아있는 것을 알게되었다.
-> 이미 B-Tree 자료구조, 즉 인덱스를 통해 age가 22라는 조건을 이미 알게되었으므로 디스크 I/O를 하지 않은 상태에서 age=22인 name 데이터를 불러올 수 있다.
-> 이 상태에서 디스크에 접근하지 않았으므로 1차 성능 향상, 인덱스 키 값을 기반으로 해당 데이터 정보를 빠르게 불러올 수 있으므로 2차 성능 향상을 기대할 수 있겠다.

case3) where 조건에 age가 아닌 다른 조건을 명시했다.
-> 이 경우 leaf node에 인덱스 및 해당 데이터 위치 정보가 없으므로 Optimizer의 실행계획은 결국 디스크까지 접근하고, 최종적으로 데이터가 있는 디스크까지 이어지는 길고 복잡한 흐름이 이어진다.
-> Where 조건을 통해 쿼리 실행 속도를 빠르게 할 수 있다는 생각보다는, Where 조건을 하기 위해 모든 데이터를 일단 불러와야 하는 full scan이 선행된다는 점을 인지하면서 쿼리를 바라보고 인덱스 전략을 세울 필요가 있다.

6. 인덱스 스캔이 무조건 성능에 유리하다고 볼 수 없다.

인덱스 스캔이 성능적으로 유리할 때는 데이터 양에 유의미하게 차이가 있고, 효율적인 인덱스 전략을 통해 Optimizer가 B-Tree 자료구조와 디스크까지 안이어지고 즉각적으로 데이터를 찾을 수 있는 쿼리 실행이 전제가 되었을 때이다.

인덱스는 하나의 테이블이므로

비효율적인 인덱스 구성은 데이터를 찾는 것을 오히려 방해한다.
기본 테이블의 데이터 수정 및 삭제가 일어날 때마다 인덱스의 위치 정보나 데이터 정보는 최신화가 되어야 하며, 자료구조도 재정렬이 일어나야 한다.

즉, 인덱스는 관리가 필요한 자료구조이므로 자주 사용하는 쿼리, 성능적으로 좋지 않은 쿼리 등 인덱스를 사용하기 위한 전략을 세우고 접근하는 것이 필요하겠다.

7. 인덱스 설정 전략

이제 어떠한 컬럼을 인덱스로 써야 하는지 어느 정도 감이 잡힐 것이다.

일단 기본적으로 중복을 최소화하는 요소를 인덱스로 설정해줘야, 오고 가는 수고가 그만큼 덜 할것이다. 사전에 인덱싱이 되어 있어도 그 안에 중복된 단어가 많다면 그 단어를 다시 세부적으로 탐색해야 하는 과정이 수반되어야 할 것이다.

가장 핵심 기준 : 카디널리티 (Cardinality)

카디널리티가 높으면 → 데이터 중복 최소화 → 인덱스를 통해 필요한 데이터를 그만큼 명확하게 확보할 수 있다.
카디널리티가 높다 = 한 컬럼이 갖고 있는 값의 중복도가 낮고 고유성이 높다 = 테이블 대표성이 높다.
카디널리티가 낮다 = 한 컬럼이 갖고 있는 값의 중복도가 높고 고유성이 낮다 = 테이블 대표성이 낮다.

선택도 (Selectivity)

선택도가 높으면 → 한 컬럼이 갖고 있는 값 하나로 여러 row가 선택된다/찾아진다/데이터 선택에 대한 중복도가 높다.
선택도가 낮으면 → 한 컬럼이 갖고 있는 값 하나로 적은 row가 선택된다/찾아진다/데이터 선택에 대한 중복도가 낮다.

(해당 컬럼 조건을 통해 전체 데이터의 10~15% 탐색이 가능한 정도)

※선택도 계산법※
(= 컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100)
ex) 10개의 데이터에서 고유한 학번(grade) 컬럼, 2명씩 같은 이름(name) 컬럼, 5명씩 같은 나이(age) 컬럼인 경우
① 학번(grade) 컬럼 선택도: 1 / 10 = 10%
② 이름(name) 컬럼 선택도: 2 / 10 = 20%
③ 나이(age) 컬럼 선택도: 5 / 10 = 50%