인덱스 분석

코다람쥐 2022. 4. 13. 11:50

참고, SQL 튜닝이란 데이터베이스의 성능을 분석해보는 것이다.

1. 인덱스 분석

임시로 만든 Test테이블에 다음과 같은 정보를 저장해놨다.

USE Northwind; -- 구글검색으로 다운받을 수 있음

CREATE TABLE Test
(
	EmployeeID	 INT NOT NULL,
	LastName	 NVARCHAR(20) NULL,
	FirstName	 NVARCHAR(20) NULL,
	HireDate	 DATETIME NULL
);
GO

INSERT INTO Test
SELECT EmployeeID, LastName, FirstName, HireDate
FROM Employees;

SELECT *
FROM Test;

다음으로 새로운 인덱스를 Test_Index라는 이름으로 만들었다.

CREATE INDEX Test_Index ON Test(LastName)
WITH (FILLFACTOR = 1, PAD_INDEX = ON)
GO

-- 인덱스 번호 찾기
SELECT index_id, name
FROM sys.indexes -- 인덱스 정보를 갖고 있는 테이블
WHERE object_id = object_id('Test');

여기서 index_id가 2인걸 보아 인덱스 번호는 2번인걸 알 수 있다.

2번 인덱스를 살펴보기 위해 다음의 구문을 작성한다.

DBCC IND('Northwind', 'Test', 2);

(화질을 위해 PagePID부분과 IndexLevel부분을 잘라서 2개의 사진으로 나눠서 찍었다. 실제로는 둘다 합쳐져있다.)

IndexLevel은 트리구조와 비슷하다고 생각하면된다.

Root(2) -> Branch(1) -> Leaf(0)로 맨 위가 2레벨이고 아래로 내려가면서 레벨이 점점 낮아지다가 마지막은 0레벨이 된다. 그리고 NextPagePID와 PrevPagePID를 통해 PagePID가 어떤 트리 구조로 저장되어 있는지 추리해볼수 있다.

857
880 856
840 848 849

그리고 추리를 해보면 PagePID가 위의 결과로 저장되어 있음을 알 수 있다.

다음의 구문을 통해 PagePID를 참조하여 페이지 안에 어떻게 저장되어 있는지 살펴보면

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

위와 같이 저장되 있음을 알 수 있는데 LastName(key)를 보면 Buchanan, Callahan, Davolio가 840페이지에 차례대로 저장되어 있음을 알 수 있다.

맨 위에 저장된 Buchanan이 840 페이지를 대표하는데, DBCC PAGE 명령어를 통해 모든 페이지를 참조하여 트리구조를 다시 살펴보면

857(Leverling)
880(Dodsworth) 856(Leverling)
840(Buchanan) 848(Dodsworth) 849(Leverling)

위와 같이 저장되어 있음을 알 수 있다.

여기서 만약 Callahan의 정보를 찾는다고 했을 때,

857페이지에서 Leverling와 Callahan의 문자열 크기를 비교한다.

C는 L보다 작으므로 왼쪽 페이지로 들어간다.

880페이지에서 Dodsworth와 Callahan의 문자열 크기를 비교한다.

C는 D보다 작으므로 왼쪽 페이지로 들어간다.

840페이지는 마지막(Leaf) 페이지 이므로 페이지 안으로 들어가서 정보를 살펴본다.

실제 데이터도 840페이지 2행에 Callahan이 저장되어 있는 걸 볼 수 있다.

참고, HEAP RID는 실제 데이터가 저장되어 있는 주소이다.

HEAP RID의 구조는 ([페이지 주소(4바이트)][파일ID(2바이트)][슬롯번호(2바이트)])를 조합한 8바이트의 ROW의 식별자.인데, ROW 식별자를 이용해 테이블 정보 추출가능하다.

참고2, Random Access 한 건을 읽기위해 한 페이지씩 접근하는 건데 우리가 트리구조를 접근하는 과정을 표현한 것이다.

참고3, Bookmark Lookup이란 RID를 통해 행을 찾는다는 것이다.