SQL (18) - INDEX 분석

이번 포스트에서는 SQL에서 검색 속도를 높이기 위한 방법인 INDEX에 대해 알아보겠습니다.

INDEX는 책의 색인과 같이 동작하여 데이터 테이블에서 특정한 데이터를 찾을 때 사용합니다.

따라서 이번 포스트에서는 인덱스의 동작 원리와 구성을 확인해보겠습니다.

 

1) 예제

-- 인덱스 테스트용 임시 테이블 생성
CREATE TABLE Test
(
	EmployID	INT NOT NULL,
	LastName	NVARCHAR(20) NULL,
	FirstName	NVARCHAR(20) NULL,
	HireData	DATETIME NULL
);

GO

-- 임시 테이블에 데이터를 추가
INSERT INTO Test
SELECT EmployeeID, LastName, FirstName, HireDate
FROM Employees;

SELECT *
FROM Test;

-- 임시 테이블 Test에 인덱스를 생성
-- FILLFACTOR(공간의 몇 %를 사용할지를 지정)
-- PAD_INDEX(FILLFACTOR 중간 페이지 적용)
CREATE INDEX Test_Index ON Test(LastName)
WITH (FILLFACTOR = 1, PAD_INDEX = ON)
GO

 

위와 같이

임시 테이블 - 임시 테이블에 데이터 추가 - 임시 테이블에서 인덱스 생성의 3가지 절차를 수행했습니다.

 

1 - 1) 인덱스 접근

-- 인덱스 번호 찾기
SELECT index_id, name
FROM sys.indexes
WHERE object_id = object_id('Test');

 

Test 임시 테이블을 대상으로 인덱스의 번호를 가져와보겠습니다.

실행 결과는 다음과 같습니다.

 

1 - 2) 인덱스 탐색

-- 2번 인덱스의 정보 살펴보기
DBCC IND('Northwind', 'Test', 2)

 

위와 같이 쿼리를 구성하여 2번 인덱스의 정보를 알아보겠습니다.

실행 결과는 다음과 같습니다.

 

실행 결과에서 주목할 것은 IndexLevel입니다.

IndexLevel은 값이 높을수록 위에 위치하고, 값이 낮을수록 아래에 위치합니다.

따라서 그림으로 표현하자면 다음과 같이 데이터들이 위치하게 됩니다.

 

위와 같은 트리 구조로 데이터들이 인덱스 안에서 정리되어 있습니다.

인덱스는 이진 트리 알고리즘을 사용하여 특정 데이터를 검색할 수 있습니다.

 

1 - 3) Heap RID

인덱스 안에는 Heap RID라는 개념이 존재하여 정보를 검색하는데 사용합니다.

Heap RID는 4바이트의 페이지 주소 + 2 바이트의 파일 ID + 2 바이트의 슬롯번호로 구성되어 있으며

총 8바이트로 사용하는 행 식별자로 동작합니다.

(여기서의 페이지는 데이터 파일을 구성하는 논리 단위를 의미합니다.)

 

인덱스에 저장된 데이터의 Heap RID를 예제를 통해 알아보겠습니다.

DBCC PAGE('Northwind', 1/*파일번호*/, 992/*페이지번호*/, 3/*출력옵션*/)
DBCC PAGE('Northwind', 1/*파일번호*/, 1000/*페이지번호*/, 3/*출력옵션*/)
DBCC PAGE('Northwind', 1/*파일번호*/, 1001/*페이지번호*/, 3/*출력옵션*/)

DBCC PAGE('Northwind', 1/*파일번호*/, 1008/*페이지번호*/, 3/*출력옵션*/)
DBCC PAGE('Northwind', 1/*파일번호*/, 1032/*페이지번호*/, 3/*출력옵션*/)
DBCC PAGE('Northwind', 1/*파일번호*/, 1009/*페이지번호*/, 3/*출력옵션*/)

 

위와 같이 구성하여 인덱스에 저장된 각 페이지 별 Heap RID를 확인해보겠습니다.

실행 결과는 다음과 같습니다.

 

 

인덱스의 트리 구조에서 맨 아래에 위치한 페이지들은 총 3개의 Heap RID를 갖고 있고,

중간 단계의 페이지들은 2개의 Heap RID를,

최상단에 위치한 페이지는 1개의 Heap RID를 갖고 있는 것을 확인할 수 있습니다.