[MS SQL]7.인덱스 분석

지금까지 기본적인 문법을 배웠고 이제 인덱스를 분석해보고자 한다.

 

새로운 DB인 Northwind 정보를 가져와 주고 임시 테이블을 만들어준다.

USE Northwind;

--DB정보
EXEC sp_helpdb '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

 

DB정보를 볼 수있는 코드

--DB정보
EXEC sp_helpdb 'Northwind';

 

 

LastName으로 인덱스를 만들어주고 여러가지 설정을해주는데 이것은 나중에 다른거와 같이 설명하겠다.

-- FILLFACTOR (리프 페이지 공간 1%만 사용)
-- PAD_INDEX(FILLFACTOR 중간 페이지 적용)
CREATE INDEX Test_Index ON Test(LastName)
WITH (FILLFACTOR = 1, PAD_INDEX = ON)
GO

 

이제 인덱스 번호를 sys.indexes에서 찾아보면 

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

 

id가 2라는것을 확인이 가능하다.

 

이걸로 인덱스 정보를 확인해보자

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

 

여러가지 정보가 출력되는데 여기서 우리에게 필요한 정보는

PageFID : 페이지 파일 ID

PagePID : 페이지 ID

indexLevel : 트리 구조로 되어있는 페이지의 Level 이다. Root에 가까울수록 레벨이 높다.

 

여기서 Root에 해당하는 페이지ID는 Level이 가장 큰 1033이다.

 

트리 구조

                                        1033

                    1056                           1032

   1016                            1024                           1025

아마 이렇게 구성되어있을 것이다. 여기서 또 페이지 정보를 열람 할 수있는데, 리프 페이지인 1016, 1024, 1025를 열어보면 Test에 들어있는 총 9명의 직원 이름정보가 들어있다.

DBCC PAGE('Northwind',1,1016,3);
DBCC PAGE('Northwind',1,1024,3);
DBCC PAGE('Northwind',1,1025,3);

 

 

리프 페이지를 제외한 루트 or 중간 페이지들은 리프 페이지로 갈 수있는 경로를 제공해준다.(Key값을 이용한 비교)

 

그럼 리프 페이지가 아닌 1056, 1032,1033를 실행해 본다면?

DBCC PAGE('Northwind',1,1056,3);
DBCC PAGE('Northwind',1,1032,3);

DBCC PAGE('Northwind',1,1033,3);

리프 페이지를 실행시켰을때와는 다른 텍스트가 나온다. ChildPageId라는 정보가 보인다. 이걸로 중간 페이지에서 리프 페이지로 가는 경로를 제공한다.

 

HEAP RID(Row Identifier)는 테이블에 우리가 원하는 행의 위치가 담겨있는 값이다. 이처럼 RID로  실제 테이블의 행을 찾는걸 "Bookmark LookUp"이라 한다.

 

 

 

추가적으로 조사해보니 이와 같은 구조는 B-Tree라고 한다.

B-Tree : 트리에서 노드가 가질수있는 자식의 수가 정해져있지 않은트리 구조로 최대 M개의 자식을 가질때 M차 B-Tree라고 한다. 자세하게 다루지 않겠지만 여기서 인덱스를 설명하는데 필요한 정보는

1. 노드는 최소M/2개, 최대 M개의 자식을 가질수 있음

2.루트 노드에서 하향식으로 Key값을 비교하며 값을 찾을때까지 내려간다. 리프노드까지 내려왔으나 찾지 못하면 검색 실패

3.여러 글을 찾아봤는데 중간노드또한 데이터를 가질수있다고 하는 글도있고 없다는 글도 있다. 당장 위의 스크린샷을 봐도 첫번째 LastName값과 RID가 NULL로 되어있는 행이있고 아닌행도있는걸 보니 가능한걸로 보인다.

실제로 동작할때 리프노드까지 가지않아도 중간노드에서 key값이 일치하면 탐색이 종료된다고 한다.

 

학교에서 자료구조에 대해 배웠을때 B - Tree구조를 배우진 않았지만 AVL트리를 배운적이 있. 결국에 이러한 트리구조의 탄생 배경에는 트리의 균형을 잡으려는데서 출발했을 것이다.(삽입,삭제로 인해 데이터가 편향되어 버리는 현상을 막기 위해) 

 

 

DB에서는 B-Tree구조를 아는것이 매우 중요하기때문에 잘 기억해놔야겠다.

 

 

복합 인덱스

그렇다면 인덱스를 하나의 Column이 아닌 복수의 Column으로 걸어놨다면 어떤식으로 동작을 할까?

 

OrderId와 ProductID를 복합 인덱스로 설정하고 여러 케이스에서 인덱스가 어떻게 동작하는지 알아보자.

SELECT *
INTO TestOrderDetails
FROM [Order Details]

SELECT *
FROM TestOrderDetails

--복합 인덱스 추가
CREATE INDEX Index_TestOrderDetails
ON TestOrderDetails(OrderID,ProductID);

 

 

코드 1

SELECT *
FROM TestOrderDetails
WHERE OrderID = 10248 AND ProductID = 11;

 

코드 2

SELECT *
FROM TestOrderDetails
WHERE ProductID = 11 AND OrderID = 10248

 

코드 3

SELECT *
FROM TestOrderDetails
WHERE OrderID = 10248

 

 

코드 4

SELECT *
FROM TestOrderDetails
WHERE ProductID = 11

 

 

코드1: OrderID와 ProductID

코드2: ProductID와 OrderID

코드3: OrderID

코드4:ProductID

 

코드 1~3을 보면 Index Seek와 RID LookUp으로 데이터를 찾고있고

코드4만 Table (full) Scan, 즉 테이블을 전부 다 뒤져서 데이터를 찾고있다고 나와있다.

 

코드1,2로 알수있는 점은 OrderID와 ProductID로 데이터를 찾을때 입력 순서는 중요하지 않다는 것이고

코드 3로 알수있는점은 인덱스로 2개를 었음에도 OrderID하나만 이용해서 데이터를 찾을 수 있다는 점이다.

반면에 코드 4에서는 이상하게도 ProductID 하나만으로는 효율적으로 찾지못하고 있다.

 

이러한 차이가 발생하는 이유는 처음 인덱스를 걸어줄때 입력한순서가 OrderID와 ProductID 순서였기 때문에 우선순위가 높은  OrderID를 이용해서 먼저 트리를 구성하고 동일한 값이 존재할때 ProductID를 이용해서 조정한다. 그러므로 OrderID를 기반으로 데이터를 찾는것은 가능하나 ProductID를 이용해서 탐색을 할 수없는것이다.

 

즉, "A,B를 이용해서 인덱스를 구성했다면 A는 자동으로 효율적인 데이터 탐색에 사용될 수 있는 속성이되고 B는 불가능하다"

 

한 가지 더 주의 해야할 점을 소개하기 위해 LastName으로 인덱스를 만들고 키 값을 가공해서 사용하려고 하면 어떻게 되는지 알아보자

SELECT LastName
INTO TestEmployees
FROM Employees

SELECT * FROM TestEmployees

CREATE INDEX Index_TestEmployees
ON TestEmployees(LastName);

 

아래 코드의 의도는 LastName이 'Bu'로 시작하는 데이터를 찾아 달라고하고 있다.

SELECT *
FROM TestEmployees
WHERE SUBSTRING(LastName,1,2) = 'Bu';

하지만 Index Scan 방식으로 동작하게 되는데, 여기서 알 수있는점은 경우에 따라서 키 값을 가공시켰을때 인덱스를 활용 못하는 경우게 발생할 수있다는 점이다.

 

그래서 위의 코드보단 아래 코드 방식으로 사용해야지 인덱스를 잘 활용 할 수있으므로 왠만해선 키값을 가공하는건 피하도록 하자.

SELECT *
FROM TestEmployees
WHERE LastName LIKE  'Bu%'

 

 

Clustered Index

1.리프 페이지 == 데이터 페이지

2.데이터는 Clustered Index 키 순서로 물리적 정렬

 

 

Non - Clustered Index 

1.Clustered Index 유무에 따라서 다르게 동작

1)Clustered Index가 없는 경우 데이터는 Heap Table이라는 곳에 저장

Heap RID -> Heap Table에 접근해서 데이터 추출

 

2.Cluster Index가 이미 존재하는 경우

1)Heap Table이 없음, Leaf Table에 실제 데이터가 들어있다. Clustered Index의 실제 키 값을 들고 있다.