클러스터형·비클러스터형 인덱스
클러스터형·비클러스터형 인덱스
SQL Server에서 인덱스는 테이블이나 뷰와 연결된 디스크 구조로, 행을 빠르게 찾아내도록 도와줘요. 인덱스는 테이블의 한 개 이상 컬럼으로 만든 키 값으로 구성되고, 이 값들은 B-트리 구조에 저장되어 SQL Server가 키 값에 해당하는 행을 빠르고 효율적으로 찾을 수 있게 해줘요. 문서에서는 편의상 B-트리라고 부르지만, 로우스토어 인덱스는 실제로 B+ 트리로 구현돼요(이 내용은 컬럼스토어 인덱스나 메모리 최적화 테이블 인덱스에는 해당하지 않아요). 기본이 되는 인덱스 종류는 클러스터형(clustered)과 비클러스터형(nonclustered) 두 가지예요.
클러스터형 인덱스
클러스터형 인덱스는 키 값에 따라 데이터 행을 정렬해서 저장해요. 여기서 키 값이란 인덱스 정의에 포함된 컬럼을 말해요. 테이블당 클러스터형 인덱스는 하나만 만들 수 있는데, 그 이유는 데이터 행 자체가 한 순서로만 저장될 수 있기 때문이에요.
테이블의 데이터 행이 정렬된 순서로 저장되는 유일한 경우가 바로 클러스터형 인덱스가 있을 때예요. 클러스터형 인덱스가 있는 테이블을 클러스터형 테이블이라 하고, 없는 경우 데이터 행은 힙(heap)이라는 정렬되지 않은 구조로 저장돼요.
비클러스터형 인덱스
비클러스터형 인덱스는 데이터 행과는 별도의 구조를 가져요. 인덱스 키 값과 함께, 각 키 값이 가리키는 데이터 행의 포인터를 담고 있어요. 여기서 행 로케이터(row locator)는 인덱스 행에서 데이터 행을 가리키는 포인터예요.
행 로케이터의 구조는 데이터 페이지가 힙에 저장됐는지 클러스터형 테이블에 저장됐는지에 따라 달라져요.
- 힙이라면 행 로케이터는 행을 가리키는 포인터예요.
- 클러스터형 테이블이라면 행 로케이터는 클러스터형 인덱스 키가 돼요.
비클러스터형 인덱스의 리프 레벨에 키가 아닌 컬럼(포함 컬럼)을 추가하면 기존 인덱스 키 제한을 우회하고, 쿼리가 인덱스만으로 해결되는 완전 커버 쿼리를 실행할 수 있어요.
고유 인덱스
클러스터형·비클러스터형 인덱스 모두 고유(unique)하게 만들 수 있어요. 고유 인덱스에서는 두 행이 같은 키 값을 가질 수 없어요. 그렇지 않으면 비고유 인덱스가 되고, 여러 행이 같은 키 값을 공유할 수 있게 돼요.
테이블 데이터가 수정될 때마다 인덱스는 자동으로 유지되므로 따로 관리할 필요가 없어요.
인덱스와 제약 조건
PRIMARY KEY나 UNIQUE 제약 조건이 테이블 컬럼에 정의되면 SQL Server가 자동으로 인덱스를 만들어요.
- UNIQUE 제약 조건 → 비클러스터형 인덱스 자동 생성
- PRIMARY KEY → 클러스터형 인덱스 자동 생성 (이미 클러스터형 인덱스가 존재하면 비클러스터형으로 적용)
기존 테이블에 PRIMARY KEY를 걸려는데 이미 클러스터형 인덱스가 있으면, SQL Server는 기본 키를 비클러스터형 인덱스로 강제해요.
쿼리 최적화 프로그램이 인덱스를 다루는 법
잘 설계된 인덱스는 디스크 I/O를 줄이고 시스템 리소스도 덜 소비해서 쿼리 성능을 높여줘요. SELECT·UPDATE·DELETE·MERGE를 포함한 다양한 쿼리에서 유용하게 쓰여요.
예를 들어 AdventureWorks 데이터베이스에서 다음 쿼리를 실행한다고 해볼게요.
SELECT JobTitle, HireDate
FROM HumanResources.Employee
WHERE BusinessEntityID = 250
쿼리 최적화 프로그램은 데이터를 가져올 수 있는 각 방법을 평가하고 가장 효율적인 방법을 선택해요. 그 방법이 테이블 스캔일 수도, 존재하는 인덱스 하나 이상을 스캔하는 것일 수도 있어요.
- 테이블 스캔: 테이블의 모든 행을 읽고 조건에 맞는 행을 추출해요. 디스크 I/O가 많고 리소스 집약적이지만, 결과 집합이 테이블 행의 높은 비율을 차지하면 오히려 가장 효율적인 방법일 수 있어요.
- 인덱스 탐색: 인덱스 키 컬럼을 검색해서 쿼리가 필요로 하는 행의 저장 위치를 찾고 그 위치에서 일치하는 행을 추출해요. 일반적으로 테이블 검색보다 훨씬 빠른데, 인덱스는 행당 컬럼 수가 적고 행이 정렬돼 있기 때문이에요.
인덱스가 하나도 없으면 최적화 프로그램은 테이블 스캔을 쓸 수밖에 없어요. 그러니 환경에 잘 맞는 인덱스를 직접 설계하고 만들어서, 최적화 프로그램이 고를 수 있는 효율적인 인덱스 후보가 생기도록 해야 해요. SQL Server는 데이터베이스 환경 분석과 적절한 인덱스 선정을 돕는 Database Engine Tuning Advisor도 제공해요.