안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 12-3. 인덱스 생성과 관리 - 인덱스 생성을 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번에 진행할 강좌는 인덱스 생성과 관리 - 인덱스 생성입니다.
TL;DR
인덱스 생성과 활용 방법에 대해 다루며, 인덱스 선택도를 확인하고, 실행 계획을 통해 효율적인 인덱스 생성 및 적용 방법을 강좌로 진행합니다.
인덱스 생성
자 그럼 실제 인덱스를 SQL 쿼리 구문으로 생성하겠습니다. 아무 설정 없는 초기화된 데이터베이스로 테스트하기 위해 빈 데이터베이스를 생성하고 실행합니다.
USE master;
GO
-- 예제 데이터베이스 생성
CREATE DATABASE SQLERTestDB;
GO
USE SQLERTestDB;
GO
-- IndexDummy 테이블이 존재하면 삭제
DROP TABLE IF EXISTS IndexDummy;
-- 인덱스 테스트 테이블 생성
CREATE TABLE IndexDummy(
idx INT,
dummy_dt DATETIME DEFAULT(GETDATE()),
dummy_str NVARCHAR(39) DEFAULT(CONVERT(NVARCHAR(39), NEWID()))
);
GO
-- 1만 건 데이터를 WHILE문으로 생성. 약 10초 소요
SET NOCOUNT ON
DECLARE @Counter INT
SET @Counter = 0
WHILE (@Counter < 10000)
BEGIN
INSERT INTO IndexDummy(idx) VALUES(@Counter)
SET @Counter = @Counter + 1
END;
GO
-- 완료 후 데이터 조회
SELECT top 100 * FROM IndexDummy;
SELECT COUNT(*) FROM IndexDummy;
GO
인덱스를 테스트할 샘플데이터가 잘 생성되었습니다.
우리가 실행할 SQL 쿼리가 인덱스를 타는지 인덱스를 타지 않는지 어떻게 비교하고 눈으로 확인할 수 있을까요? 우선 어느 정도의 IO 비용이 각각 소요되는지 비교해 보겠습니다. 물론 시간 / IO비용 모두가 중요하지만 우선 IO 비용만 보도록 하겠습니다.
--IO통계 표시 설정 SET STATISTICS IO ON; SELECT * FROM IndexDummy WHERE idx = 5000; --IO통계? SELECT * FROM IndexDummy WHERE idx < 10; --IO통계? SELECT * FROM IndexDummy WHERE idx < 40; --IO통계? SELECT * FROM IndexDummy WHERE idx < 1000; --IO통계? --IO통계 표시 끄기 SET STATISTICS IO OFF;
쿼리 결과의 메시지 탭을 보면 IO통계를 확인할 수 있습니다. 아래와 유사할 겁니다.
| Table 'IndexDummy'. Scan count 1, logical reads 125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. |
각 SQL 쿼리를 수행하고 출력된 결과를 기록합니다.
다음은 인덱스를 생성하고 같은 SQL 쿼리를 실행합니다.
--간단한 인덱스 생성 CREATE INDEX idx_id ON IndexDummy (idx); GO
인덱스가 금방 생성되었습니다. 다시 데이터를 조회해 보도록 할까요?
--인덱스 생성 후 같은 쿼리를 다시 수행 --IO통계 표시 설정 SET STATISTICS IO ON; SELECT * FROM IndexDummy WHERE idx = 5000; --IO통계? SELECT * FROM IndexDummy WHERE idx < 10; --IO통계? SELECT * FROM IndexDummy WHERE idx < 40; --IO통계? SELECT * FROM IndexDummy WHERE idx < 1000; --IO통계? --IO통계 표시 끄기 SET STATISTICS IO OFF;
인덱스를 생성하기 전과 후 통계를 비교하면서 체크해 보세요. 위의 두 개 쿼리는 훨씬 적은 IO로 실행이 완료되었습니다. 하지만, 아래 두 쿼리는 큰 차이가 없네요. 왜 그럴까요?
인덱스 선택도
WHERE 조건절의 idx < 40부터 인덱스가 없는 경우와 비슷한 IO 수치가 발생합니다. 이것은 무슨 의미일까요? 왜 힘들게 인덱스를 만들었는데 성능이 안 나오는 거죠?
다음처럼 실제 실행계획 포함을 누르거나 컨트롤 + M를 눌러 쿼리 실행계획과 같이 결과를 보겠습니다.
SELECT * FROM IndexDummy WHERE idx = 5000;
위의 쿼리를 수행해 보면
이미지 - 인덱스 쿼리 실행계획
위의 이미지처럼 SQL Server 실행 계획을 볼 수 있습니다. 실행계획이 복잡하고 어려워 보입니다. 중요한 건 인덱스를 탔고, “Index Seek”를 수행한 내역입니다.
그렇다면, 다음 쿼리를 실행하고 실행 계획을 체크해 볼까요?
SELECT * FROM IndexDummy WHERE idx < 40;
이미지 - 쿼리 실행계획 테이블 스캔
이렇게 WHERE절 조건이 idx < 40을 봐 보시면 “Table Scan”을 하는 것을 알 수 있습니다.
쿼리도 비슷하고 유사해 보이는데 어떤 건 인덱스를 타고 어떤 거는 인덱스를 타지 않습니다. 앞에서 짧게 소개해 드린 “선택도”로 (찾을 데이터수 / 전체 데이터수)로 인덱스를 탈지 안 탈지 판단됩니다. SQL Server 쿼리 최적화기의 대표적인 Index Seek와 Table Scan 조건입니다.
☑️ 챗GPT 활용: 인덱스 선택도(Index Selectivity)에 대해서 알려줘
일반적으로 비클러스터형 인덱스는 선택도 3% 이하, 클러스터형 인덱스는 30~35% 이하에서 Index Seek와 Table Scan을 판단하게 됩니다. 위의 예제는 0.5% 전후에서 Seek를 결정합니다. 다시 말씀드리지만, 테이블 데이터형과 쿼리 패턴 등 다양한 조합에 의해 SQL Server 쿼리 최적화기가 판단해 결정합니다.
인덱스 강좌를 진행하면서 계속 소개해 드리겠습니다.
인덱스 정보 확인
인덱스의 정보를 보려면 어떻게 할까요? 이렇게 sp_helpindex에 테이블명을 파라미터로 주면 됩니다.
--인덱스 정보 조회 EXEC sp_helpindex IndexDummy; 결과 index_name index_description index_keys ---------- -------------------------------- ------------ idx_id nonclustered located on PRIMARY idx
기본 옵션으로 생성한 인덱스는 종류를 지정하지 않으면 nonclustered - 비클러스터형 인덱스로 데이터베이스의 PRIMARY 파일그룹에 생성됩니다.
인덱스 변경은 ALTER 구문을 사용 가능하고, DROP INDEX 구문을 이용해 인덱스를 제거할 수 있습니다.
-- 인덱스 삭제 DROP INDEX IndexDummy.idx_id GO
우선 간단히 데이터와 인덱스를 생성하고 실행했습니다. 이제 SQL서버의 클러스터형 인덱스와 비클러스터형 인덱스에 대해서 좀 더 깊이 살펴보겠습니다.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.
책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022
