B-Tree부터 커버링 인덱스까지: RDBMS 인덱스(Index) 가이드

🚀 왜 인덱스와 실행 계획 최적화가 필수적일까?

개발한 서비스가 트래픽 폭주를 맞이하는 상황을 상상해 봅시다.

이벤트 오픈 직후 사용자들이 몰려들 때, 애플리케이션 서버의 CPU는 널널한데 데이터베이스 서버의 CPU 사용률이 100%를 치솟으며 서비스가 멈추는 경험, 한 번쯤 겪어보셨거나 들어보셨을 것입니다.

이런 병목 현상의 90% 이상은 잘못된 쿼리 작성과 인덱스 설계의 부재에서 시작됩니다. 데이터가 수만 건일 때는 인덱스 없이 풀 스캔을 통해 결과를 반환합니다.

하지만 데이터가 수천만 건, 수억 건으로 늘어나면 이야기가 달라집니다. 인덱스 없이 수천만 건의 데이터를 뒤지는 작업은 막대한 디스크 I/O를 발생시키고, 이는 데이터베이스 커넥션 풀을 고갈시켜 결국 전체 시스템의 장애로 이어집니다.

단순히 서버의 스펙을 올리는 스케일 업(Scale-up)은 비용이 기하급수적으로 증가할 뿐만 아니라 근본적인 해결책이 될 수 없습니다.

진정한 개발자라면 원하는 데이터만 정확히 찾아낼 수 있도록 길을 만들어 주어야 합니다. 그 길이 바로 인덱스이며, 데이터베이스가 그 길을 제대로 찾아가고 있는지 확인하는 지도가 바로 실행 계획입니다.

🤦‍♂️ 흔히 하는 인덱스 관련 실수들

핵심 원리를 파악하기 전에, 현업에서 가장 흔하게 저지르는 실수 세 가지를 먼저 짚고 넘어가겠습니다.

1. 무작정 인덱스를 많이 생성하는 실수

조회가 느리다는 이유로 WHERE 절에 등장하는 모든 컬럼에 인덱스를 걸어버리는 경우가 있습니다.

하지만 인덱스는 공짜가 아닙니다. 데이터를 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)할 때마다 인덱스 자료구조도 함께 갱신되어야 합니다.

과도한 인덱스는 쓰기 성능을 급격히 저하시키고, 데이터베이스의 저장 공간을 낭비하며, 옵티마이저가 최적의 실행 계획을 선택하는 데 혼란을 줄 수 있습니다.

2. 복합 인덱스의 순서를 무시하는 실수

A 컬럼과 B 컬럼을 묶어 (A, B) 형태로 복합 인덱스를 생성해 두고, 쿼리에서는 B 컬럼만으로 검색을 시도하는 경우입니다.

인덱스는 설정된 컬럼의 순서대로 정렬됩니다. 첫 번째 컬럼인 A를 건너뛰고 B만으로 검색하면 데이터베이스는 인덱스를 제대로 탈 수 없어 풀 스캔이 발생합니다. 복합 인덱스는 반드시 선행 컬럼이 조건에 포함되어야 제 기능을 발휘합니다.

3. 인덱스 컬럼을 함수나 연산자로 가공하는 실수

날짜 컬럼에 인덱스가 있는데 조회할 때 FN_YEAR(created_at) = '2023' 처럼 컬럼 자체를 함수로 감싸서 조건을 거는 경우가 많습니다.

인덱스는 가공되지 않은 순수한 데이터 상태로 정렬되어 저장됩니다. 컬럼을 가공해 버리면 데이터베이스는 인덱스에 저장된 값과 비교할 수 없게 되어 인덱스 사용을 포기합니다. 반드시 created_at >= '2023-01-01' AND created_at < '2024-01-01' 과 같이 컬럼을 원형 그대로 유지하며 쿼리를 작성해야 합니다.

🧠 인덱스의 원리와 최적화 기법 심층 분석

이제 본격적으로 인덱스의 핵심으로 들어가 보겠습니다. 이곳에서 다루는 개념들은 실무에서 쿼리 성능을 튜닝할 때 가장 중요한 내용들입니다.

🌳1. B-Tree 구조의 이해와 인덱스 탐색 원리

관계형 데이터베이스에서 가장 많이 사용되는 인덱스 알고리즘은 B-Tree (Balanced Tree) 알고리즘입니다. B-Tree는 데이터의 삽입, 삭제, 수정이 일어날 때마다 트리의 균형을 유지하여 어떤 값을 찾든 동일한 시간이 걸리도록 설계된 구조입니다.

B-Tree는 크게 세 가지 종류의 노드로 구성됩니다. 최상단에 위치한 루트 노드(Root Node), 중간에서 브리지 역할을 하는 브랜치 노드(Branch Node), 그리고 가장 최하단에 위치하여 실제 데이터의 위치를 가리키는 리프 노드(Leaf Node)입니다.

인덱스 탐색은 루트 노드에서 시작하여 찾고자 하는 값의 범위를 좁혀가며 브랜치 노드를 거쳐 최종적으로 리프 노드에 도달하는 과정입니다. 리프 노드에는 인덱스로 설정된 컬럼의 값과 그 데이터가 저장된 디스크 상의 주소(ROWID 또는 Primary Key 값)가 매핑되어 있습니다.

만약 인덱스가 없다면 데이터베이스는 테이블의 처음부터 끝까지 모든 데이터를 읽어야 하는 풀 테이블 스캔(O(N) 시간 복잡도)을 수행해야 합니다. 하지만 B-Tree 인덱스를 타게 되면 트리의 깊이만큼만 탐색을 수행하는 인덱스 레인지 스캔(O(log N) 시간 복잡도)이 가능해집니다. 책의 색인을 보고 원하는 페이지를 바로 펴는 것과 똑같은 원리입니다.

여기서 중요한 점은 B-Tree 인덱스는 부등호(<, >)나 범위 검색(BETWEEN, LIKE '검색어%')에는 효과적이지만, 문자열 중간이나 끝을 검색하는 LIKE '%검색어' 조건에서는 인덱스를 사용할 수 없다는 것입니다. 인덱스는 왼쪽부터 오른쪽으로 정렬되어 있기 때문에 앞부분이 와일드카드로 시작하면 트리를 타고 내려갈 방향을 결정할 수 없기 때문입니다.

🏢 2. 클러스터링 인덱스와 논클러스터링 인덱스의 차이

데이터베이스 인덱스는 데이터를 물리적으로 어떻게 저장하느냐에 따라 크게 클러스터링 인덱스와 논클러스터링 인덱스로 나뉩니다. 이 둘의 차이를 이해하는 것은 디스크 I/O 최적화의 핵심입니다.

클러스터링 인덱스(Clustered Index)는 테이블의 레코드 자체가 인덱스 키를 기준으로 정렬되어 디스크에 저장되는 방식입니다. 영어 사전에서 단어가 알파벳 순서대로 쭉 인쇄되어 있는 것과 같습니다.

대부분의 RDBMS에서 프라이머리 키(Primary Key)가 바로 클러스터링 인덱스가 됩니다.

클러스터링 인덱스의 가장 큰 특징은 리프 노드에 인덱스 키뿐만 아니라 테이블의 모든 컬럼 데이터가 함께 저장되어 있다는 것입니다. 따라서 프라이머리 키를 조건으로 데이터를 검색하면, 리프 노드에 도달하는 즉시 모든 데이터를 얻을 수 있어 조회 속도가 압도적으로 빠릅니다. 단, 테이블당 단 하나만 존재할 수 있습니다.

논클러스터링 인덱스(Non-clustered Index)는 프라이머리 키를 제외한 나머지 모든 일반 인덱스를 의미합니다. 책 맨 뒤에 있는 찾아보기(색인) 페이지와 같습니다.

논클러스터링 인덱스의 리프 노드에는 해당 인덱스 컬럼의 값과 함께 실제 데이터가 있는 곳을 찾아가기 위한 포인터가 저장되어 있습니다.

따라서 일반 인덱스(논클러스터링 인덱스)를 통해 데이터를 조회하면 두 번의 검색이 일어납니다.

1. 논클러스터링 인덱스 트리를 탐색하여 프라이머리 키 값을 찾는다.
2. 찾아낸 프라이머리 키 값을 가지고 다시 클러스터링 인덱스 트리를 탐색하여 실제 전체 데이터를 읽어온다.

이 두 번째 단계를 데이터베이스 용어로 랜덤 I/O를 동반한 북마크 룩업(Bookmark Lookup)이라고 부릅니다. 인덱스를 탔음에도 불구하고 이 북마크 룩업 비용이 너무 크다면, 옵티마이저는 차라리 전체 테이블을 뒤지는 풀 테이블 스캔이 더 빠르다고 판단해 버립니다. 보통 전체 데이터의 20% 이상을 조회해야 할 때 이런 현상이 발생합니다.

🛡️ 커버링 인덱스와 인덱스 컨디션 푸시다운(ICP)

방금 설명한 북마크 룩업의 치명적인 랜덤 I/O 성능 저하를 극복하기 위한 두 가지 강력한 무기가 있습니다.

1. 커버링 인덱스(Covering Index)

쿼리의 SELECT 절에 있는 모든 컬럼과 WHERE 절에 있는 모든 컬럼이 단 하나의 인덱스에 모두 포함되어 있는 경우를 말합니다.

즉, 쿼리를 충족시키기 위해 필요한 모든 데이터가 이미 인덱스의 리프 노드에 존재하기 때문에, 실제 테이블 데이터 블록으로 찾아가는 북마크 룩업 과정을 생략할 수 있습니다. 디스크 I/O가 획기적으로 줄어들어 엄청난 성능 향상을 가져옵니다.

-- 테이블: users
-- 인덱스: idx_department_salary (department_id, salary)

-- 커버링 인덱스가 적용되는 쿼리
SELECT department_id, salary 
FROM users 
WHERE department_id = 10;

위 쿼리는 실행 결과로 가져와야 할 데이터가 department_id와 salary뿐인데, 두 컬럼 모두 idx_department_salary 인덱스 안에 들어있습니다.

따라서 옵티마이저는 테이블에 접근하지 않고 인덱스만 스캔하여 결과를 반환합니다. 실행 계획을 보면 Extra 항목에 Using index라는 문구가 표시되는데, 이것이 바로 커버링 인덱스가 성공적으로 적용되었다는 뜻입니다. 페이징 처리 최적화 등에서 매우 유용하게 쓰이는 기법입니다.

2. 인덱스 컨디션 푸시다운(Index Condition Pushdown, ICP)

이 기능은 MySQL 5.6 버전부터 도입된 획기적인 최적화 기술입니다.

복합 인덱스에서 첫 번째 컬럼으로 인덱스를 타서 데이터를 찾았지만, 두 번째 컬럼이 인덱스를 타지 못하는 조건(예: LIKE '%검색어')일 때 발생하던 비효율을 개선했습니다.

ICP가 도입되기 전에는 스토리지 엔진이 첫 번째 컬럼 조건으로 찾은 레코드들을 무조건 MySQL 엔진으로 올려보냈고, MySQL 엔진이 나머지 WHERE 조건을 검사하여 데이터를 버렸습니다. 이 과정에서 불필요한 테이블 읽기(랜덤 I/O)가 대량으로 발생했습니다.

하지만 ICP가 적용되면, 인덱스에 포함된 컬럼이라면 비록 그 조건이 인덱스 탐색에는 쓰이지 못하더라도 스토리지 엔진 레벨에서 미리 필터링을 수행합니다. 인덱스만 보고도 조건에 맞지 않음을 알 수 있는 데이터는 아예 테이블 조회를 시도하지 않는 것입니다.

-- 테이블: employees
-- 복합 인덱스: idx_last_first (last_name, first_name)

SELECT * FROM employees 
WHERE last_name = 'Kim' AND first_name LIKE '%sung%';

위 쿼리에서 first_name은 앞부분 와일드카드 때문에 인덱스 탐색용으로는 쓰이지 못합니다. 하지만 ICP가 작동하면, last_name이 Kim인 데이터들의 인덱스 리프 노드를 읽으면서, 그 안에 있는 first_name 정보가 sung을 포함하는지 테이블로 가기 전에 먼저 검사합니다.

조건에 맞는 레코드에 대해서만 테이블 접근을 수행하므로 디스크 읽기가 획기적으로 줄어듭니다. 실행 계획의 Extra 항목에 Using index condition이라고 표시되면 ICP가 동작한 것입니다.

🕵️ 옵티마이저의 실행 계획 분석 및 힌트 사용법

완벽하게 쿼리를 작성하고 인덱스를 구성해도, 최종적으로 어떤 방법으로 데이터를 가져올지 결정하는 두뇌는 데이터베이스의 옵티마이저(Optimizer)입니다. 옵티마이저는 각 쿼리의 예상 비용(Cost)을 계산하여 가장 저렴한 경로를 선택하는 비용 기반 최적화를 수행합니다.

하지만 옵티마이저도 완벽하지 않아 가끔 엉뚱한 인덱스를 선택하거나 풀 테이블 스캔을 고집하는 실수를 저지릅니다. 이때 옵티마이저의 생각을 엿볼 수 있는 도구가 EXPLAIN (실행 계획) 명령어이며, 옵티마이저의 행동을 강제할 수 있는 수단이 힌트(Hint)입니다.

쿼리 앞에 EXPLAIN을 붙여 실행하면 여러 컬럼의 결과가 나옵니다. 가장 주의 깊게 봐야 할 컬럼은 다음과 같습니다.

• type: 데이터를 테이블에서 어떻게 읽을 것인지를 나타냅니다. (성능 좋은 순서대로 나열)
  • system / const: 프라이머리 키나 유니크 키로 1건만 조회할 때. 최고의 성능입니다.
  • eq_ref / ref: 인덱스를 사용하여 동등(=) 조건으로 검색할 때. 훌륭한 성능입니다.
  • range: 인덱스 레인지 스캔. 부등호나 BETWEEN 등으로 범위 검색을 할 때 나타나며, 일반적인 최적화의 목표가 됩니다.
  • index: 인덱스 풀 스캔. 테이블을 다 읽지는 않지만 인덱스 전체를 다 읽습니다. 상황에 따라 개선이 필요합니다.
  • ALL: 테이블 풀 스캔. 인덱스를 전혀 타지 못하고 테이블을 처음부터 끝까지 다 읽습니다. 반드시 튜닝해야 하는 경고등입니다.
• possible_keys: 옵티마이저가 이 쿼리를 실행하기 위해 후보로 올려둔 인덱스 목록입니다.
• key: 옵티마이저가 최종적으로 선택하여 사용한 인덱스입니다.
• Extra: 쿼리 실행의 부가적인 정보입니다.
  • Using index: 커버링 인덱스 사용됨 (최고).
  • Using index condition: ICP 사용됨 (아주 좋음).
  • Using filesort: ORDER BY 처리를 위해 인덱스를 쓰지 못하고 메모리나 디스크에 데이터를 올려 강제로 정렬함 (성능 저하의 주범, 인덱스를 활용한 정렬로 튜닝 필요).
  • Using temporary: 쿼리 처리를 위해 임시 테이블을 생성함 (주로 GROUP BY 등에서 발생하며, 성능 이슈를 일으킬 수 있음).

만약 EXPLAIN을 분석했는데 옵티마이저가 명백히 잘못된 판단을 하고 있다면, 쿼리에 힌트를 부여하여 올바른 길을 지시할 수 있습니다.

MySQL 힌트 사용법

MySQL은 쿼리의 테이블명 바로 뒤에 힌트를 명시합니다.

-- 특정 인덱스를 사용하도록 권장 (옵티마이저가 무시할 수도 있음)
SELECT * FROM orders USE INDEX (idx_order_date) WHERE order_date > '2023-01-01';

-- 특정 인덱스를 강제로 사용하도록 지시 (테이블 풀 스캔이 압도적으로 유리하지 않은 이상 강제함)
SELECT * FROM orders FORCE INDEX (idx_order_date) WHERE order_date > '2023-01-01';

-- 특정 인덱스를 절대 사용하지 못하도록 배제
SELECT * FROM orders IGNORE INDEX (idx_status) WHERE status = 'COMPLETED';

Oracle 힌트 사용법

오라클은 SELECT 명령어 바로 뒤에 주석 기호와 플러스 기호 (/+ ... */)를 결합하여 힌트를 작성합니다. *주의할 점은 테이블에 별칭(Alias)을 주었다면 힌트 내부에서도 반드시 테이블명 대신 별칭을 사용해야 정상 작동한다는 것입니다.**

-- 특정 인덱스를 사용하도록 지시 (가장 범용적으로 사용됨)
SELECT /*+ INDEX(o idx_order_date) */ * FROM orders o 
WHERE order_date > '2023-01-01';

-- 특정 인덱스를 역순으로 읽도록 지시 (최신순 정렬 등 페이징 처리에 매우 유용함)
SELECT /*+ INDEX_DESC(o idx_order_date) */ * FROM orders o 
WHERE order_date > '2023-01-01';

-- 특정 인덱스를 절대 사용하지 못하도록 배제
SELECT /*+ NO_INDEX(o idx_status) */ * FROM orders o 
WHERE status = 'COMPLETED';

실무에서는 데이터의 분포도(Cardinality)가 변함에 따라 어제까지 잘 작동하던 쿼리가 오늘 갑자기 느려지는 경우가 발생합니다.

통계 정보가 갱신되지 않아 옵티마이저가 착각한 것입니다. 이때 FORCE INDEX 같은 힌트를 적절히 사용하면 쿼리의 실행 계획을 고정시켜 시스템의 안정성을 확보할 수 있습니다. 단, 힌트는 최후의 수단이어야 하며, 우선은 쿼리 자체를 인덱스 친화적으로 재작성하는 것이 올바른 접근 방법입니다.

📝 마무리: 오늘 내용의 3줄 요약

복잡한 내용이었지만, 백엔드 개발자로서 성능 최적화를 위해 이 세 가지만큼은 꼭 기억해 주시길 바랍니다.

1. 인덱스는 만병통치약이 아닙니다. B-Tree의 정렬 원리를 이해하고, 조건절과 복합 인덱스의 컬럼 순서를 정확히 일치시켜야만 제대로 된 성능이 나옵니다.
2. 디스크 I/O를 줄이는 것이 성능 튜닝의 핵심입니다. 커버링 인덱스와 인덱스 컨디션 푸시다운(ICP) 원리를 활용하여 데이터베이스가 물리적 테이블(디스크)에 접근하는 횟수를 극적으로 줄이세요.
3. 옵티마이저를 맹신하지 마세요. 항상 EXPLAIN 명령어로 실행 계획(type, key, Extra 등)을 눈으로 확인하고, 필요하다면 힌트를 통해 데이터베이스에게 올바른 길을 직접 제시할 수 있어야 합니다.