[Index-4][Optimization] 심화 이론 (2) - 물리적 저장 구조

1. 들어가며

인덱스의 물리적 구현 방식인 B+Tree 구조에서 가장 핵심적인 차이는 '리프 노드(Leaf Node)에 무엇을 저장하는가'에 있다. 데이터베이스는 데이터를 저장하는 물리적 방식에 따라 인덱스를 클러스터드와 논클러스터드로 분류하며, 이 차이를 이해하는 것이 쿼리 성능 최적화의 시작이다.

---

2. 클러스터드 인덱스(Clustered Index): 실제 데이터가 담긴 '책장'

 MySQL의 InnoDB 엔진에서 클러스터드 인덱스는 단순한 보조 수단이 아닌 테이블 그 자체를 의미한다. 데이터가 저장되는 물리적인 순서가 인덱스의 정렬 순서와 일치하며, 테이블당 단 하나만 존재할 수 있다.

• 구조적 특징: B+Tree의 리프 노드에 도달하는 순간, 해당 행(Row)의 모든 컬럼 데이터가 직접 저장되어 있다.
• 비유: 도서관의 책장에 책들이 특정 순서(주로 ID)에 맞춰 실물 그대로 꽂혀 있는 것과 같다. 3번 칸을 열면 3번 책의 내용 전체를 즉시 확인할 수 있다.
• 장점: 데이터를 찾아가는 추가 탐색 과정이 없으므로 조회 성능이 매우 우수하다.
• 제약: 물리적인 데이터 정렬 순서를 결정하기 때문에 기본키(Primary Key)가 이 역할을 수행하며, 테이블당 단 하나만 생성 가능하다.

 즉, 우리가 흔히 말하는 ‘테이블’은 물리적으로 클러스터드 인덱스 구조로 저장된 데이터 집합이다. 예를 들어, 아래와 같은 book 테이블을 살펴보자.

id	name	category	price	is_adult
1	진격의 거인	만화	3000	No
2	오뎅도 먹고 싶다	에세이	7000	Yes
3	파워 오브 데이터베이스	개발	20000	Yes
...	...	...	...	...

 이 테이블은 물리적으로 id 순서에 따라 정렬되어 책장에 꽂혀 있는 책들과 같다. 만약 우리가 id = 3인 데이터를 찾고자 한다면, 옵티마이저는 B+ Tree의 루트 노드부터 탐색하여 id = 3이 있는 리프 노드(책장의 3번 칸)에 도달한다. 이때 리프 노드에는 책 번호뿐만 아니라 '파워 오브 데이터베이스', '개발', '20000' 등 해당 행의 모든 데이터가 함께 들어있다.

 따라서 클러스터드 인덱스는 한 번의 탐색만으로 모든 데이터를 얻을 수 있어 조회 성능이 매우 우수하다. 다만, 책장의 칸을 물리적으로 단 한 가지 순서로만 배치할 수 있듯이, 클러스터드 인덱스 역시 테이블당 단 하나(주로 PK)만 존재할 수 있다.

---

3. 논클러스터드 인덱스(Non-Clustered Index): 데이터 주소를 알려주는 '검색기'

우리가 특정 컬럼(예: category)에 대해 추가로 생성하는 모든 인덱스는 논클러스터드 인덱스(보조 인덱스)가 된다. 이는 실제 데이터가 담긴 책장이 아니라, 책이 어디 있는지 알려주는 '도서 검색기' 혹은 '별도의 카탈로그'와 같다. 아래는 category 컬럼으로 만든 논클러스터드 인덱스의 모습이다.

category	id
개발	3
개발	5
에세이	2
...	...

 논클러스터드 인덱스의 리프 노드에는 책의 전체 내용이 들어있지 않다. 오직 인덱스로 설정한 값(category)과 실제 데이터를 찾아가기 위한 주소 값인 기본키(id)만 저장되어 있다. 만약 '개발' 카테고리의 책 제목을 알고 싶다면 다음과 같은 두 단계를 거쳐야 한다.

1. 인덱스 탐색: 검색기에서 '개발'을 찾아 해당 책의 번호가 3번과 5번임을 확인한다.
2. 테이블 접근(Key Lookup): 확인한 번호를 들고 실제 책장(클러스터드 인덱스)으로 이동하여 3번 칸과 5번 칸에서 책을 꺼내 제목을 확인한다.

 이처럼 논클러스터드 인덱스는 리프 노드 도달 후 실제 데이터를 가져오기 위해 다시 한번 클러스터드 인덱스를 탐색해야 하므로, 개발 현장에서는 이를 두고 "오버헤드(추가 비용)가 크다"고 표현한다.

---

4. 성능 최적화의 꽃: 커버링 인덱스(Covering Index)

 커버링 인덱스는 쿼리를 실행하기 위해 필요한 모든 컬럼이 인덱스 자체에 포함되어 있어, 실제 데이터 블록(테이블)에 접근할 필요가 없는 상태를 의미한다. 위에서 Non-Clustered Index의 가장 큰 단점은 원본 데이터를 찾기 위해 테이블에 다시 접근해야 한다는 점이었는데, 이 한계를 극복할 수 있는 개념인것이다.

4.1. 동작 원리: "데이터 페이지로의 점프를 생략하라"

 일반적인 논클러스터드 인덱스 조회는 다음과 같은 2단계 과정을 거친다.

1. Index Seek/Scan: 인덱스 리프 노드에서 조건에 맞는 데이터를 찾는다. (이때 리프 노드에는 인덱스 키와 PK 값이 들어 있다.)
2. Key Lookup (Bookmark Lookup): 인덱스에 없는 나머지 컬럼을 가져오기 위해, 리프 노드에 저장된 PK 값을 들고 클러스티드 인덱스(실제 데이터 페이지)로 이동한다.

 커버링 인덱스는 SELECT, WHERE, GROUP BY, ORDER BY 등 쿼리 전체에서 사용하는 모든 컬럼을 인덱스에 포함시켜 2번 과정을 완전히 제거한다. 디스크 I/O 중 가장 비용이 비싼 '랜덤 액세스(Key Lookup)'가 사라지므로 성능이 비약적으로 향상되는 것이다.

4.2. Spring Data JPA 실무 예시

다음과 같은 Member 엔티티와 인덱스가 설정되어 있다고 가정한다.

@Entity
@Table(indexes = @Index(name = "idx_email_nickname", columnList = "email, nickname"))
public class Member {
    @Id @GeneratedValue
    private Long id; // PK (Clustered Index)

    private String email;    // Non-Clustered Index (Composite)
    private String nickname; // Non-Clustered Index (Composite)
    
    private String address;  // 인덱스에 포함되지 않은 필드
    private String phoneNumber;
}

Case 1: 커버링 인덱스가 적용되지 않는 경우 (Entity 조회)

Spring Data JPA에서 단순히 엔티티 전체를 조회하면 커버링 인덱스가 작동하기 어렵다.

// SQL: SELECT * FROM member WHERE email = ?
// address, phoneNumber 등 인덱스에 없는 필드 때문에 Key Lookup 발생
Optional<Member> findByEmail(String email);

Case 2: 커버링 인덱스가 적용되는 경우 (DTO Projection)

필요한 필드만 인덱스에 포함된 컬럼들로 구성하여 조회하면 커버링 인덱스가 작동한다.

// SQL: SELECT m.email, m.nickname FROM member m WHERE m.email = ?
// 쿼리에 필요한 (email, nickname, id)가 모두 인덱스에 들어 있음
@Query("SELECT m.email as email, m.nickname as nickname FROM Member m WHERE m.email = :email")
List<MemberDto> findDtoByEmail(@Param("email") String email);

실행 계획(Execution Plan) 확인 방법

지표	의미	상태
Using index	커버링 인덱스 적용됨. 인덱스만으로 쿼리 처리 완료.	최적
Using index condition	인덱스 조건으로 필터링은 했으나, 실제 데이터 접근(Lookup)이 발생함.	보통
Using where	인덱스를 타지 못하고 테이블 스캔 후 WHERE 조건으로 필터링함.	주의

4.3.실무적인 도입 및 과남용 판단 기준

 커버링 인덱스는 강력하지만, 인덱스 크기를 키우고 쓰기 성능을 저하시키는 비용이 따른다. 따라서 다음과 같은 기준에 따라 전략적으로 설계해야 한다.

판단 요소	도입 권장 (Go)	도입 재검토 (Stop)
조회 빈도	초당 수천 번 호출되는 핵심 API	가끔 확인하는 통계 및 관리자용 쿼리
컬럼 개수	1~3개 내외의 핵심 필드 위주	SELECT * 수준의 방대한 컬럼 요구
데이터 변경	거의 변하지 않는 고정값/날짜 데이터	상태값, 가격 등 수시로 변경되는 데이터
성능 병목	수천만 건 테이블의 랜덤 I/O 발생 지점	인덱스 없이도 충분히 빠른 소규모 테이블

---

5. 성능 최적화의 본질: 랜덤 I/O와 순차 I/O

 데이터베이스 성능을 결정짓는 가장 큰 요인은 결국 디스크 접근 방식이다.

• 랜덤 I/O (Random I/O): 논클러스터드 인덱스 탐색 후 실제 데이터를 찾기 위해 디스크 헤더를 이곳저곳으로 옮기며 책장을 뒤지는 방식이다. 속도가 매우 느리며 검색 성능 저하의 주범이 된다.
• 순차 I/O (Sequential I/O): 디스크 헤더가 이동할 필요 없이 연속된 데이터를 한 방향으로 읽는 방식이다. 커버링 인덱스를 활용하거나 클러스터드 인덱스에서 범위 데이터를 읽을 때 발생하며, 압도적으로 빠르다.

 결국 인덱스 설계의 최종 목적은 불필요한 랜덤 I/O를 줄이고 순차 I/O를 유도하는 것이다. 무분별한 인덱스 생성은 오히려 독이 될 수 있으므로, 서비스의 병목이 되는 핵심 쿼리를 중심으로 '가장 효율적인 길'을 뚫어주는 것이 개발자의 진정한 실력이라 할 수 있다.