지금까지 포스팅을 통해 SQL의 기초를 다뤄봤다.
SQL을 사용하여 데이터베이스를 설계할 때 문제는 바로 성능이다. 수천명 수만명의 유저들이 사용하기 때문에 이 성능이 절대적으로 좋아야만 한다. 그래서 SQL의 성능을 분석하고 개선하는 튜닝에 대해 알아보자.
인덱스 분석
시작하기 앞서 구글에 northwind database를 검색해 깃헙에서 다운받아주자. sql을 처음할때 사용하는 아주 대표적인 데이터베이스이다.
여기에 나오는 명령어는 정말 몰라도 된다. 단순히 분석히가 위한 코드로 매우 비효율적이게 바꾸는 부분도 있다.
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
);
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);
제일 중요한건 indexlevel이다. 여기서 숫자 2는 Root 를 의미하고 1은 Branch, 0은 Leaf를 의미한다.
각 노드마다 키 값을 가지고 정렬이 되어있고 우리가 어떤 특정 데이터를 찾는다고 했을때, 각 노드의 키 값과 비교하여(이진트리) 데이터를 찾게된다. 데이터를 찾게되면 HEAP RID를 반환하는데 그 값을 통해 실제 TABLE에 접근해 데이터를 가져오는 것이다.
즉 우리가 기존에 설정하고 사용했던 PK가 이런식으로 동작한다고 볼 수 있다.
복합 인덱스
Order table에 인덱스(orderid, productid)를 두개 이상 사용하게 되면 우리한테 어떤 이점이 있을까?
OrderId를 서치했을때와 ProductID를 서치했을때, 그리고 두 개를 동시에 서치하는 경우에 대해 알아보자.
USE Northwind;
-- 임시 테스트 테이블 만들고 데이터 복사
SELECT *
INTO TestOrderDetails
FROM [Order Details];
-- 복합 인덱스 추가
CREATE INDEX Index_TestOrderDetails
ON TestOrderDetails(OrderID, ProductID);
-- 인덱스 정보 살펴보기
EXEC sp_helpindex 'TestOrderDetails';
-- 인덱스 적용 테스트 1
SELECT*
FROM TestOrderDetails
WHERE OrderID = 10248 AND ProductID = 11;
보면 index seek가 나온것을 확인할 수 있다.
index seek가 나왔다는건 인덱스를 정삭적으로 잘 사용하고 있다는 의미이고
만약 index scan이 나왔다면 인덱스를 잘 못 사용하고 있다는 것이다.
SELECT*
FROM TestOrderDetails
WHERE OrderID = 10248;
이 경우에도 index seek가 나왔다.
SELECT*
FROM TestOrderDetails
WHERE ProductID = 11;
이 경우에는 index scan이 나왔다.
SELECT를 했을 때 모든 정보를 다 확인했다는 의미이다.
왜 orderid는 정상적으로 작동하고 productid는 정상적으로 작동하지 않을까?
이유는 간단하다. 우리가 인덱스를 만들어 줄 때, orderid, productid를 적용했다. 그렇기 때문에 테이블이 orderid 순으로 정렬을 하고 겹쳤을 경우에 productid로 정렬하는 것이지 productid를 그냥 가져다 사용할 수는 없기 때문이다.
결론은
- 복합 인덱스를 사용할 때 인덱스 순서를 주의해야한다
- 인덱스 사용 시 데이터 추가로 인해 페이지 여유공간이 없으면 자동으로 SPLIT 된다.
- 키를 가공하면 인덱스의 역할을 잃어버릴 수도 있다.
Clustered vs NonClustered
Clustered 는 데이터가 Leaf Page에 실질적으로 들어간다. 당연히 데이터는 Clustered Index 키 순서로 정렬된다.
Non-Clustered는 크게 두가지 경우로 나뉘게 된다.
1. Clustered index가 없는 경우
데이터가 Heap Table이라는 곳에 저장된다.
Heap Rid를 통해 heap table에 접근해 데이터를 추출하는 방식이다.
2. Clustered index가 있는 경우
Heap Table이 없고 Leaf Table에 실제 데이터가 있다.
Clustered Index의 실제 키 값을 들고 있는다.
'Unity > 온라인 RPG' 카테고리의 다른 글
[Unity 2D] 컨텐츠 준비 - 세팅, MapTool, 플레이어 이동 (4) | 2024.02.28 |
---|---|
[데이터베이스] SQL 튜닝 - 북마크 룩업, Join, Sorting (1) | 2024.02.23 |
[데이터베이스] SQL 입문 - 정규화, INDEX, UNION, JOIN... (0) | 2024.02.20 |
[데이터베이스] SQL 입문 - Group, 추가 삭제 갱신, subquery (0) | 2024.02.16 |
[데이터베이스] SQL 입문 - DATETIME, CASE, 집계 함수 (0) | 2024.02.14 |