안녕하세요. 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;

 

위의 쿼리를 수행해 보면

 

132-1 쿼리 실행계획.png

이미지 - 인덱스 쿼리 실행계획


위의 이미지처럼 SQL Server 실행 계획을 볼 수 있습니다. 실행계획이 복잡하고 어려워 보입니다. 중요한 건 인덱스를 탔고, “Index Seek”를 수행한 내역입니다.


그렇다면, 다음 쿼리를 실행하고 실행 계획을 체크해 볼까요?

SELECT * FROM IndexDummy WHERE idx < 40;

132-2 쿼리 실행계획 테이블 스캔.png

이미지 - 쿼리 실행계획 테이블 스캔


이렇게 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 

책구매링크.png

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 36183
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 18705
2314 SQL강좌: 14-7. 트랜잭션과 잠금처리 - 교착상태(데드락-DeadLock) 관리 [1] 코난(김대우) 2023.08.18 237
2313 SQL강좌: 14-6. 트랜잭션과 잠금처리 - 잠금 관리 file 코난(김대우) 2023.08.18 108
2312 SQL강좌: 14-5. 트랜잭션과 잠금처리 - 잠금과 트랜잭션 격리 수준 코난(김대우) 2023.08.18 52
2311 SQL강좌: 14-4. 트랜잭션과 잠금처리 - 잠금(Lock)과 블로킹 코난(김대우) 2023.08.18 82
2310 SQL강좌: 14-3. 트랜잭션과 잠금처리 - 트랜잭션과 체크포인트 [1] file 코난(김대우) 2023.08.18 138
2309 SQL강좌: 14-2. 트랜잭션과 잠금처리 - 트랜잭션 종류 코난(김대우) 2023.08.18 103
2308 SQL강좌: 14-1. 트랜잭션과 잠금처리 - 트랜잭션 이해 코난(김대우) 2023.08.18 110
2307 SQL강좌: 13-5. 백업과 복원 - 로그 전달, Always On 고가용성과 재해 복구 구현 [1] file 코난(김대우) 2023.08.18 108
2306 SQL강좌: 13-4. 백업과 복원 - 유지 관리 계획 수립 file 코난(김대우) 2023.08.18 79
2305 SQL강좌: 13-3. 백업과 복원 - 백업과 복원 전략 실행 file 코난(김대우) 2023.08.18 67
2304 SQL강좌: 13-2. 백업과 복원 - 백업과 복원 전략 file 코난(김대우) 2023.08.18 78
2303 SQL강좌: 13-1. 백업과 복원 - 백업과 복원 이해 file 코난(김대우) 2023.08.18 128
2302 SQL강좌: 12-9. 인덱스 생성과 관리 - DTA(데이터베이스 엔진 튜닝 관리자) file 코난(김대우) 2023.08.18 75
2301 SQL강좌: 12-8. 인덱스 생성과 관리 - 인덱스 재구성/재구축 [1] 코난(김대우) 2023.08.18 96
2300 SQL강좌: 12-7. 인덱스 생성과 관리 - 인덱스 옵션 코난(김대우) 2023.08.18 90
2299 SQL강좌: 12-6. 인덱스 생성과 관리 - 클러스터형 vs 비클러스터형 인덱스 file 코난(김대우) 2023.08.18 86
2298 SQL강좌: 12-5. 인덱스 생성과 관리 - 비클러스터형 인덱스 file 코난(김대우) 2023.08.18 72
2297 SQL강좌: 12-4. 인덱스 생성과 관리 - 클러스터형 인덱스 file 코난(김대우) 2023.08.18 93
» SQL강좌: 12-3. 인덱스 생성과 관리 - 인덱스 생성 file 코난(김대우) 2023.08.18 86
2295 SQL강좌: 12-2. 인덱스 생성과 관리 - 인덱스 종류 코난(김대우) 2023.08.18 98





XE Login