1. 시작하며: 인덱스, 디스크 위에는 어떻게 저장될까?
지난 1편에서는 인덱스의 논리적인 자료구조인 B+Tree에 대해 알아보았습니다. 하지만 이 B+Tree가 실제 하드 디스크(물리적 공간) 위에 어떻게 배치되는지에 따라 데이터베이스의 아키텍처는 완전히 두 갈래로 나뉘게 됩니다.
오늘은 인덱스의 두 가지 핵심 물리적 구현체인 Clustered Index(클러스터드 인덱스)와 Non-Clustered Index(논클러스터드 인덱스)의 결정적인 차이를 알아보고, 실제 쿼리를 날릴 때 어떤 인덱스가 유리한지 성능을 비교해 보겠습니다.
2. 영어 사전 vs 찾아보기 색인 (Clustered vs Non-Clustered)
데이터베이스 테이블에 인덱스를 거는 방식은 크게 두 가지 비유로 완벽하게 설명할 수 있습니다.
📕 Clustered Index (영어 사전 방식)
- 테이블의 실제 데이터 레코드들을 물리적으로 재배열하여 정렬합니다.
- 마치 영어 사전이 A부터 Z까지 순서대로 인쇄되어 있는 것과 같습니다. (리프 노드 = 실제 데이터)
- 데이터 자체가 정렬되어 있으므로 테이블당 딱 1개만 생성할 수 있습니다. (주로 PK 설정 시 자동 생성)
- 장점: 이미 정렬되어 있어 범위 검색 등 조회 속도가 압도적으로 빠릅니다.
- 단점: 중간에 새로운 데이터가
INSERT되면, 물리적인 순서를 맞추기 위해 뒤쪽 데이터들을 전부 뒤로 밀어내야 하는 엄청난 오버헤드가 발생합니다.
📘 Non-Clustered Index (책 맨 뒤의 색인 방식)
- 실제 데이터는 원래 있던 자리에 그대로 두고, 정렬된 별도의 인덱스 페이지(색인)를 따로 만듭니다.
- 일반 전공 서적 맨 뒤에 있는 '찾아보기' 페이지와 같습니다.
- 물리적인 제약이 없으므로 테이블당 여러 개를 생성할 수 있습니다. (Secondary Index라고도 부름)
- 장점: 데이터를 물리적으로 이동시킬 필요가 없으므로
INSERT시의 오버헤드가 상대적으로 적습니다. - 단점: 인덱스를 찾은 뒤, 다시 실제 데이터가 있는 곳으로 포인터를 타고 점프해서 가야 하므로 조회 속도는 Clustered보다 약간 느립니다.
💡 면접관의 단골 꼬리 질문: "조회 패턴에 따른 두 인덱스의 성능 차이는?"
1) 단건 조회 (Point Query, WHERE id = 5): 딱 하나의 데이터만 찾을 때는 둘 다 트리 구조를 한 번만 타고 내려가면 되므로 성능 차이가 거의 없습니다. 이럴 때는 삽입/수정 오버헤드가 적은 논클러스터드 인덱스를 우선 고려하는 것이 좋습니다.
2) 범위 조회 (Range Query, WHERE id BETWEEN 10 AND 100): 여기서 압도적인 차이가 발생합니다! 클러스터드 인덱스는 데이터가 이미 물리적으로 정렬되어 있어 시작점부터 쭉 연속해서 읽어오면 끝입니다. 반면 논클러스터드 인덱스는 정렬되지 않은 원본 데이터를 찾기 위해 디스크 이곳저곳을 찌르는 랜덤 I/O가 발생하여 성능이 크게 떨어집니다.
3. MySQL InnoDB만의 독특한 인덱스 아키텍처
여기서 아주 중요한 실무 지식이 등장합니다. 오라클(Oracle) 등 일반적인 DB는 Non-Clustered Index의 리프 노드에 '실제 데이터가 있는 디스크 물리적 주소(ROWID)'를 저장합니다. 색인을 보고 몇 페이지 몇 번째 줄인지 바로 찾아가는 상식적인 방식입니다.
하지만 MySQL의 핵심 엔진인 InnoDB는 리프 노드에 '물리적 주소' 대신 'Primary Key(PK) 값'을 저장합니다. 도대체 왜 한 번에 갈 수 있는 주소를 놔두고, 다시 PK를 검색하게 만드는 구조를 택했을까요?
⚠️ 데이터 이동의 나비효과를 막아라!
앞서 Clustered Index(PK)에 새로운 데이터가 끼어들면, 물리적 순서를 맞추기 위해 뒤쪽 데이터들이 밀려나면서 '물리적 주소'가 전부 바뀐다고 했습니다. 만약 Non-Clustered Index가 물리적 주소를 들고 있었다면, 데이터가 이동할 때마다 수많은 Non-Clustered Index들을 싹 다 뒤져서 주소를 업데이트해야 하는 대참사가 벌어집니다.
하지만 PK 값을 들고 있으면, 데이터의 물리적 위치가 이사를 가더라도 PK 값 자체는 변하지 않으므로 Non-Clustered Index를 수정할 필요가 전혀 없어집니다. 즉, 조회 시 한 번 더 트리를 타야 하는 약간의 비용을 지불하고, 안정성과 유지보수성을 획득한 천재적인 아키텍처 설계입니다.
4. 궁극의 최적화 스킬: 커버링 인덱스 (Covering Index)
InnoDB의 구조를 배웠다면, 이제 쿼리 튜닝의 마법인 커버링 인덱스(Covered Query)를 완벽하게 이해할 수 있습니다. 이를 이해하기 위해서는 먼저 '데이터 룩업'이라는 성능 저하의 주범을 알아야 합니다.
🚨 데이터 룩업 (Data Lookup) 이란?
우리가 나이(age) 컬럼에 일반적인 단일 인덱스를 걸었다고 가정해 봅시다. SELECT 이름(name) FROM USER WHERE 나이 = 23; 이라는 쿼리를 날리면, DB는 먼저 나이 인덱스를 뒤져서 '23'을 찾고 그곳에 적힌 PK 값(id)을 얻어냅니다.
하지만 쿼리에서 요구한 '이름' 데이터는 나이 인덱스 페이지에 존재하지 않습니다. 결국 DB는 방금 얻은 PK 값을 들고, 실제 데이터가 있는 원본 테이블(클러스터드 인덱스)로 다시 한번 무거운 발걸음을 옮겨야 합니다. 이처럼 인덱스를 탄 후 원본 데이터를 찾으러 점프하는 과정을 '데이터 룩업'이라 부르며, 디스크 랜덤 I/O를 발생시켜 쿼리를 느리게 만드는 주범이 됩니다.
그렇다면, 논클러스터드 인덱스 자체에 내가 찾고자 하는 데이터들을 처음부터 같이 넣어둘 순 없을까요? 이것이 바로 복합 인덱스를 활용한 커버링 인덱스의 핵심 아이디어입니다.
-- 💡 나이(age)와 이름(name)을 묶어서 복합 인덱스로 생성한 상태
SELECT 나이, 이름 FROM USER WHERE 나이 = 23;
✨ 커버링 인덱스의 마법: 원본 테이블 방문 생략!
위 복합 인덱스 구조에서는 인덱스 리프 노드에 [ 나이 | 이름 | id(PK) ]가 한 세트로 묶여서 정렬되어 저장됩니다.
이 상태로 쿼리를 날리면, DB는 인덱스 노드 안에서 나이가 23인 레코드를 찾음과 동시에 그 옆에 나란히 적혀있는 '홍길동'이라는 이름까지 통째로 읽어 들입니다. 쿼리가 필요로 하는 모든 컬럼이 인덱스에 다 있으니, 굳이 무거운 원본 테이블(클러스터드 인덱스)로 2차 방문(Data Lookup)을 하지 않고 인덱스 안에서 상황을 종료해 버립니다. 이를 커버링 인덱스라고 부르며, 디스크 I/O를 극적으로 줄여 성능을 몇 배나 끌어올리는 튜닝 기법입니다.
🛠️ 한 걸음 더: 복합 인덱스 확장과 내부 동작의 디테일
여기서 한 단계 더 깊은 시스템 내부의 동작 원리를 짚고 넘어가야 면접에서 확실한 차별점을 가져갈 수 있습니다.
1) 3개 이상의 컬럼 확장: (나이, 이름, 생일)
복합 인덱스는 2개뿐만 아니라 3개, 4개 이상의 컬럼도 하나로 묶을 수 있습니다. 만약 (나이, 이름, 생일)로 인덱스를 생성하면 리프 노드는 [ 나이 | 이름 | 생일 | id(PK) ] 구조를 갖게 됩니다. 이 상태에서 아래와 같이 쿼리를 날리면 완벽한 커버링 인덱스가 작동합니다.
SELECT 나이, 이름, 생일 FROM USER WHERE 나이 = 23;
2) 인덱스 컬럼의 부분 집합(Subset) 조회
그렇다면 3개 컬럼이 묶인 인덱스 환경에서 딱 2개만 조회하면 어떻게 될까요? SELECT 나이, 이름 FROM USER WHERE 나이 = 23; 쿼리를 실행하더라도 커버링 인덱스는 아주 잘 작동합니다. DB 엔진은 인덱스 노드 내부에서 필요한 데이터가 전부 포함되어 있다면(부분 집합 관계라면), 쓰이지 않는 '생일' 데이터가 존재하더라도 원본 테이블을 찾지 않고 인덱스 내부에서 조회를 끝내기 때문입니다.
3) 쿼리 내비게이션 AI: 옵티마이저(Optimizer)의 역할
쿼리를 보면 FROM USER라고만 적혀있을 뿐, 어떤 인덱스를 사용하라고 명시하지 않았습니다. 그런데 DB는 어떻게 알고 인덱스로 바로 찾아가는 걸까요? 바로 데이터베이스 내부에 존재하는 옵티마이저(Optimizer) 덕분입니다. 옵티마이저는 쿼리문과 테이블 통계 정보를 분석하여 "이 쿼리는 원본 테이블 전체를 읽는 것보다 우리가 만들어 둔 복합 인덱스를 타는 게 비용이 가장 적게 들겠구나!"라고 판단하여 가장 최적의 인덱스 경로를 자동으로 선택해 줍니다.
🔥 면접관을 감동시키는 결정적 팁
MySQL InnoDB의 논클러스터드 인덱스는 구조상 리프 노드에 항상 PK 값을 기본적으로 포함하고 있습니다. 따라서 복합 인덱스를 따로 설계하지 않고 (나이) 단일 인덱스만 걸려있더라도, SELECT id, 나이 FROM USER WHERE 나이 = 23; 과 같이 PK와 인덱스 컬럼만 조회하는 쿼리는 추가적인 데이터 룩업이 일어나지 않는 커버링 인덱스로 자동 작동합니다. 이 물리적 저장 구조의 특징과 옵티마이저의 탐색 흐름을 명확히 인지하고 쿼리를 설계하는 것이 시니어와 주니어를 가르는 핵심 기준입니다.