데이터베이스 세부 인덱스 확인 및 복합 인덱스

인덱스 확인

 

USE Northwind;

--DB 정보 살펴보기.

--데이터에 대한 정보 (이름, 용량, 경뢰 등등)
EXEC sp_helpdb 'Northwind';

-- 임시 테이블 만들기 (인덱스 테스트용)
CREATE TABLE Test 
(
	EmployeeID	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;

-- FILLFACTOR (리프 페이지 공간 1%만 사용)
-- PAD_INDEX (FILLFACTOR 중간 페이지 적용)
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');


--2번 인덱스 정보 살펴보기
-- 트리노드의 정보의 깊이와 종속값을 알 수 있다.
DBCC IND('Northwind', 'Test', 2);

--HEAP RID([페이지 주소(4 Byte)][파일ID(2 Byte)][슬롯번호 (2)] 조합한 ROW 식별자, 테이블에서 정보 추출)
-- 트리노드의 리프에 대한 세부정보를 확인 할 수 있다.
DBCC PAGE('Northwind', 1, 832,3);
DBCC PAGE('Northwind', 1, 840,3);
DBCC PAGE('Northwind', 1, 841,3);

--Random Access (한 건 읽기 위해 한 페이지씩 접근)
--Bookmark Lookup (RID를 통해 행을 찾는다.)

 

 

복합 인덱스 

 

USE Northwind;

-- 주문 상세 정보를 살펴보자
SELECT*
FROM [Order Details]
ORDER BY OrderID;

-- 임시 테스트 테이블을 만들고 테이터 복사한다.
SELECT*
INTO TestOrderDetails
FROM [Order Details];

SELECT*
FROM TestOrderDetails;


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

-- 인덱스 정보 살펴보기
EXEC sp_helpindex 'TestOrderDetails';

-- OrderID, ProductID ? OrderID? ProductID?
-- INDEX SCAN (INDEX FULL SCAN) -> BAD
-- INDEX SEEK -> GOOD

-- 인덱스 적용 테스트1 결과 INDEX SEEK
SELECT*
FROM TestOrderDetails
WHERE OrderId = 10248 AND ProductID = 11;


-- 인덱스 적용 테스트2 결과 INDEX SEEK
SELECT*
FROM TestOrderDetails
WHERE ProductID = 11 AND OrderId = 10248;

-- 인덱스 적용 테스트3 결과 INDEX SEEK
SELECT*
FROM TestOrderDetails
WHERE OrderId = 10248;

-- 인덱스 적용 테스트4 결과 INDEX FULL SCAN
-- ON TestOrderDetails(OrderID, ProductID); 
-- 생성하였기 때문에 OrderID가 1순위 인덱스로 적용이 되고, 2순위로 ProduceID로 되기 때문이다.
SELECT*
FROM TestOrderDetails
WHERE ProductID = 11;


-- 인덱스 분석 

--정보
--					775
-- 856 888 889 890 891 892 920 921 
DBCC IND('Northwind', 'TestOrderDetails',2);

-- 인덱스 트리 구조 보기 -> OrderID를 기준으로 테이블 데이터 구조가 구성되어 있다.
-- >그러므로 ProduceID만 Search할 경우 정렬이 되어 있지 않아, 비효율적인 서칭이 된다.
DBCC PAGE('Northwind', 1, 856, 3);


-- 인덱스는 데이터가 추가 / 갱신 / 삭제 유지되어야 함.
-- test 데이터 50개를 강제로 넣어보자.

DECLARE @i INT = 0;
WHILE @i < 50
BEGIN
	INSERT INTO TestOrderDetails
	VALUES (10248, 100+@i, 10,1,0);
	SET @i = @i+1;
END

--> 만약 데이터가 많아져 페이지 여유 공간이 없다면, Page를 쪼개어 분산한다.



--가공 테스트
SELECT LastName
INTO TestEmployees
FROM Employees;

SELECT* FROM TestEmployees;

--인덱스 추가
CREATE INDEX Index_TestEmployees
ON TestEmployees(LastName)

-- INDEX SCAN -> INDEX FULL SCAN
--> 데이터를 가공처리하여, 찾을 경우 최적화된 INDEX를 찾는게 아니므로 최적화 Search가 일어나지 않는다.
SELECT *
FROM TestEmployees
WHERE SUBSTRING(LastName, 1,2) = 'Bu';

-- INDEX SCAN의 최적화 방법
SELECT*
FROM TestEmployees
WHERE LastName LIKE 'Bu%';

-- 결론
-- 복합 익덱스를 사용할 떄, 순서 주의하자 (1순위 A, 2순위 B)
-- 인덱스 사용시, 데이터 추가로 인해 페이지 여유 공간이 없으면 페이지를 분산한다.
-- 키 가공할 때, 주의가 필요하다.