데이터베이스 성능 최적화를 위해 인덱스를 생성했지만, 예상과 달리 Full Table Scan이 발생하는 경우가 있다. "인덱스가 있는데 왜 안 타지?"라는 의문이 든다면, 인덱스 스캔이 항상 풀 스캔보다 빠를 것이라는 오해부터 풀어야 한다.
오늘은 논클러스터형 인덱스(Non-Clustered Index)가 성능을 보장하지 못하는 시점, 즉 '손익분기점'에 대해 정리해 본다.
1. 리프 노드의 '포인터'와 '랜덤 I/O'
논클러스터형 인덱스는 책 맨 뒤에 있는 '색인'과 같다. 인덱스 페이지(리프 노드)에는 실제 데이터가 아닌, 데이터가 위치한 주소값(RID 또는 클러스터형 인덱스 키)인 포인터가 저장되어 있다.
동작 방식: 인덱스에서 조건에 맞는 키를 찾으면, 해당 포인터를 따라 실제 데이터가 저장된 디스크 블록으로 이동한다.
문제점: 인덱스 자체는 정렬되어 있지만, 실제 데이터 레코드는 디스크 여기저기에 흩어져 있을 확률이 높다. 결국 인덱스 1건을 읽을 때마다 디스크의 서로 다른 위치를 찾아가는 랜덤 I/O(Random I/O)가 발생하게 된다.
2. 데이터 양이 많아지면 발생하는 '북마크 룩업' 비용
인덱스의 포인터를 따라 실제 테이블을 참조하는 과정을 테이블 액세스(Table Access) 또는 북마크 룩업(Bookmark Lookup)이라고 부른다. 이 과정이 성능의 핵심 변수다.
적은 데이터 조회 시: 랜덤 I/O가 몇 번 발생하지 않으므로, 인덱스를 타는 것이 전체 테이블을 다 읽는 것보다 압도적으로 빠르다.
많은 데이터 조회 시: 만약 100만 건 중 50만 건을 조회해야 한다면 어떨까? 50만 번의 랜덤 I/O가 발생한다. 디스크 헤더가 50만 번이나 물리적으로 움직이며 여기저기 찌르는 비용은 기하급수적으로 늘어난다.
3. 순차 스캔이 선녀다
반대로 순차 스캔(Full Table Scan)은 인덱스를 무시하고 테이블의 처음부터 끝까지 데이터를 쭉 읽어 내려가는 방식이다.
순차 I/O의 효율성: 순차 스캔은 디스크의 연속된 블록을 한 번에 뭉텅이로 읽어온다. 비록 읽어야 할 양은 많지만, 한 건당 처리 속도는 랜덤 I/O보다 훨씬 빠르다. 마치 책에서 단어 하나를 찾으려고 여기저기 페이지를 넘기는 것보다, 그냥 처음부터 끝까지 훑어 내려가는 게 더 빠른 시점이 오는 것과 같다.
4. 옵티마이저의 "손익분기점" 선택
데이터베이스의 뇌라고 할 수 있는 옵티마이저(Optimizer)는 바보가 아니다. 보통 전체 데이터의 약 15~25% 이상을 읽어야 한다고 판단되면, 인덱스를 버리고 차라리 순차 스캔을 선택한다.
"포인터를 따라 여기저기 들락날락하는(랜덤 I/O) 비용이, 그냥 무식하게 처음부터 끝까지 다 읽는(순차 I/O) 비용보다 커지는 지점이 존재한다."
따라서 인덱스를 만들었음에도 풀 스캔이 발생한다면, 현재 조회하려는 데이터의 양이 너무 많지는 않은지(선택도가 낮은지) 먼저 체크해 봐야 한다. 이럴 때는 인덱스에 필요한 컬럼을 모두 포함시키는 커버링 인덱스(Covering Index)를 고려하는 것이 좋은 대안이 될 수 있다.
이 글은 초안을 gemini로 작성한 글입니다