데이터베이스 인덱스 설계와 실행 계획 최적화

검색 경로를 만드는 데이터 구조

인덱스는 테이블의 데이터를 빠르게 찾기 위해 사용하는 데이터 구조다. 책의 색인처럼 원본 데이터와 별도로 정렬된 복사본을 보관하고, 원하는 데이터에 도달하는 경로를 줄인다.

인덱스 엔트리의 기본 구조는 <키값, 주소> 쌍이다. 특정 컬럼 값과 해당 행이 저장된 위치를 연결하며, 엔트리에는 보통 Column Value와 저장된 행의 물리적 주소인 RowID가 포함된다. 일반적인 B-Tree 인덱스는 루트 노드에서 리프 노드까지 탐색한 뒤, 리프에서 찾은 RowID로 실제 데이터 블록에 접근한다.

대용량 테이블에 적절한 인덱스가 없으면 전체 테이블 스캔(Full Table Scan)이 발생해 검색 성능이 떨어질 수 있다. 쿼리 최적화기는 인덱스의 존재와 비용을 바탕으로 데이터 접근 방식을 선택한다.

⤢✕

조회 특성에 따라 달라지는 인덱스 선택

B-Tree 인덱스

B-Tree는 가장 일반적으로 쓰이는 인덱스 유형이다. 균형 트리 구조를 이용해 검색, 삽입, 삭제를 효율적으로 처리하며, 대부분의 RDBMS가 기본 인덱스 형태로 채택한다.

=, <, >, BETWEEN, LIKE 같은 일반적인 검색 조건과 범위 검색에 적합하다.

⤢✕

저장 순서와 엔트리 밀도

클러스터드 인덱스는 물리적 데이터 저장 순서와 인덱스 엔트리 순서가 같다. 테이블당 하나만 만들 수 있으며 데이터 검색 속도가 빠르다. SQL Server의 Primary Key 인덱스가 예다.

논클러스터드 인덱스는 데이터의 물리적 순서와 무관하게 구성된다. 테이블당 여러 개를 생성할 수 있고, 추가 포인터를 통해 실제 데이터에 접근한다. 일반적인 보조 인덱스가 이 방식이다.

밀집 인덱스는 테이블의 모든 레코드에 인덱스 엔트리를 만든다. 레코드 하나당 엔트리 하나가 매핑되므로 저장 공간은 많이 쓰지만 검색에는 유리하다. 반대로 희소 인덱스는 데이터 블록 또는 일정 범위마다 하나의 엔트리를 둔다. 공간은 효율적으로 쓰지만 검색 과정에서 추가 탐색이 필요하며, 하나의 데이터 블록에 엔트리 하나가 매핑된다.

해시 인덱스

해시 인덱스는 해시 함수로 키를 버킷에 매핑한다. 등호(=) 조건 조회에는 매우 빠르지만, 범위 검색과 정렬에는 맞지 않으며 해시 충돌이 발생할 수 있다. Redis, Memcached 같은 메모리 기반 데이터베이스에서 주로 활용된다.

비트맵 인덱스

비트맵 인덱스는 각 키 값에 비트 벡터를 사용한다. 낮은 카디널리티(Cardinality)를 가진 컬럼과 복합 조건 검색에 효율적이어서 데이터 웨어하우스와 OLAP 시스템에서 자주 사용된다. 반면 데이터 갱신 비용이 높고, 높은 카디널리티 컬럼에는 비효율적이다.

전문 검색 인덱스

전문 검색 인덱스(Full-Text Index)는 문서 안의 단어와 구문을 찾는 데 맞춘 구조다. 역색인(Inverted Index)을 사용하며 검색 엔진이나 문서 관리 시스템에 활용된다.

인덱스 설계는 쿼리에서 시작한다

인덱스를 만들 때는 고유값 비율이 높은, 즉 선택도(Selectivity)가 높은 컬럼을 우선 검토한다. 자주 실행되는 WHERE 절의 조건 필드를 살피고, 복합 인덱스는 선택도가 높은 컬럼을 앞쪽에 배치한다.

인덱스는 읽기 비용을 줄이는 대신 삽입·수정·삭제 시 유지 비용을 더한다. 따라서 모든 후보 컬럼에 인덱스를 추가하기보다 실제 쿼리 패턴과 변경 빈도를 함께 봐야 한다.

⤢✕

운영 중에는 단편화(Fragmentation)를 정기적으로 재구성하거나 재빌드해 관리한다. 실행 계획이 적절하게 생성되도록 통계 정보도 갱신해야 하며, 사용되지 않는 인덱스는 제거해 오버헤드를 줄인다.

주문 조회와 로그 분석에서의 적용

일일 1백만 건 이상의 주문을 처리하는 온라인 쇼핑몰 주문 시스템에서는 평균 3초까지 늘어진 주문 조회 API 응답 시간을 줄이기 위해 주문번호, 고객ID, 주문일자에 복합 인덱스를 만들고 자주 쓰는 상태 필드에는 별도 인덱스를 추가했다. 그 결과 쿼리 응답 시간은 200ms 이하가 되었고, 93% 성능 향상을 얻었다.

대용량 로그 데이터를 실시간 분석하는 플랫폼에서는 시간대별 이벤트 조회가 느려지는 문제가 있었다. 타임스탬프 인덱스와 이벤트 타입·타임스탬프 복합 인덱스를 추가하고 시간 기반 파티셔닝을 적용해 분석 쿼리 처리 시간을 75% 단축했다.

실시간 거래 처리와 조회 지연이 발생한 금융 거래 시스템에서는 계좌번호에 클러스터드 인덱스를 생성하고, 거래일시와 거래유형에는 결합 인덱스를 구성한다. 자주 쓰이지 않는 인덱스를 제거해 입력과 수정 성능도 개선한다. 거래 조회 속도는 8배 향상됐고 거래 처리 속도는 20% 개선됐다.

실행 계획에서 확인할 접근 방식

실행 계획은 인덱스가 실제로 사용됐는지, 어떤 방식으로 읽혔는지를 확인하는 근거다.

• Index Seek는 인덱스를 통해 특정 데이터에 직접 접근하는 방식으로 가장 효율적이다.
• Index Scan은 인덱스를 순차적으로 읽으며 범위 검색에 사용된다.
• Table Scan은 테이블 전체를 읽는 방식으로, 인덱스가 없거나 활용되지 않을 때 발생한다.
• Nested Loop Join은 인덱스가 있는 테이블 조인에 효과적이다.
• Hash Join은 대용량 데이터 조인에 사용되며 인덱스 활용도는 낮다.

다음 SQL Server 예시는 CustomerID 인덱스 생성 전후의 실행 계획 변화를 보여 준다.

-- 인덱스 생성 전
SELECT * FROM Orders WHERE CustomerID = 'ALFKI'
-- 실행 계획: Table Scan (비용: 3.85)

-- 인덱스 생성
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID)

-- 인덱스 생성 후
SELECT * FROM Orders WHERE CustomerID = 'ALFKI'
-- 실행 계획: Index Seek (비용: 0.05)

인덱스를 타지 못하는 조건과 유지 비용

컬럼에 함수를 적용하면 인덱스 사용이 제한될 수 있다. WHERE YEAR(OrderDate) = 2023 대신 WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31'처럼 조건을 바꿀 수 있다.

WHERE ProductName LIKE '%Apple%'도 인덱스를 사용하지 못할 수 있다. 전문 검색 인덱스를 사용하거나 WHERE ProductName LIKE 'Apple%' 조건을 고려할 수 있다. OR 조건에서는 각 컬럼에 개별 인덱스만 있으면 효율이 떨어질 수 있으므로 복합 인덱스 또는 UNION ALL 활용이 대안이 된다.

테이블당 4-5개 이상의 인덱스는 삽입·수정·삭제 성능을 떨어뜨릴 수 있다. 데이터가 바뀔 때마다 인덱스도 갱신해야 하고, 인덱스는 원본 데이터의 10-20% 수준의 추가 저장 공간을 필요로 한다.

분석·공간·인메모리 워크로드의 인덱스

컬럼 저장 인덱스(Columnstore Index)는 데이터를 컬럼 단위로 저장하고 압축한다. 분석 쿼리에서 10-100배 성능 향상과 저장 공간 효율화를 제공하며, 데이터 웨어하우스·빅데이터 분석·HTAP 시스템에 활용된다.

공간 인덱스(Spatial Index)는 지리적 위치 데이터 검색을 최적화한다. R-Tree와 Quadtree 같은 자료구조를 활용하며 GIS 시스템, 위치 기반 서비스, 경로 탐색에 쓰인다.

메모리 최적화 인덱스(Memory-Optimized Index)는 인메모리 데이터베이스용 구조다. 점 조회에는 해시 인덱스, 범위 조회에는 Bw-Tree를 사용하며, 락(Lock) 없는 구현으로 동시성을 높인다. 고성능 OLTP 워크로드와 실시간 처리 시스템이 대상이다.

관측 지표를 유지보수 작업으로 연결하기

인덱스 사용률을 통해 쿼리의 인덱스 활용도를 확인하고, 인덱스 없이 실행되는 고비용 쿼리도 식별해야 한다. 단편화 수준이 20% 이상이면 재구성을 검토한다.

단편화 수준에 따라 REORGANIZE 또는 REBUILD를 수행하고, 데이터 분포가 바뀌면 자동 또는 수동으로 통계를 갱신한다. DMV(Dynamic Management Views)를 이용하면 미사용 인덱스와 실제 사용 패턴을 함께 점검할 수 있다.

인덱스는 쿼리 패턴 분석, 워크로드에 맞는 유형 선택, 실행 계획 확인, 지속적인 유지보수를 묶어서 다룰 때 효과를 낸다.