[MSSQL]복합(결합) 인덱스 생성규칙 - 순서에 따른 I/O 비용차이(랜덤액세스)

복합(결합) 인덱스의 컬럼 순서는 4개의 우선 순위에 의해 생성되어야 한다.

이를 무시하고 인덱스를 생성하면 복합(결합) 인덱스를 이용하여 성능 향상을 기대하기는 어렵다.

 

- 1순위 : 컬럼이 사용한 연산자에 의한 인덱스 컬럼 선정

- 2순위 : 랜덤 액세스를 고려한 인덱스 컬럼 선정

- 3순위 : 정렬 제거를 위한 인덱스 컬럼 선정

- 4순위 : 단일 컬럼의 분포도를 고려한 인덱스 컬럼 선정

 

이번 시간에는 2순위인 랜덤 '액세스를 고려한 인덱스 컬럼 선정' 에 대해서 알아보고자 한다.

---

랜덤액세스란?

랜덤 액세스는 데이터를 저장하는 블록을 한번에 여러 개 액세스하는 것이 아니라 한 번에 하나의 블록만을 액세스하는 방식이다.

한 번에 여러 개의 블록을 액세스한다면 같은 양의 데이터에 대해 적은 횟수의 디스크 I/O가 발생하기 때문에 성능이 향상될 수 있다.

 

테이블을 처음부터 끝까지 액세스하는 테이블 전체스캔(Table Full Scan)의 경우에는 한 번에 여러 개의 블록을 액세스할 수 있기 때문에 한 번에 여러 블록을 액세스하는 다중 블록 I/O를 수행하게 된다.

그렇다면 과연 어떤 작업에서 랜덤 액세스가 발생하는 것일까? 바로 인덱스를 액세스하여 확인한 ROWID를 이용하여 테이블을 액세스하는 경우 랜덤액세스가 발생하게 된다.

 

ROWID는 해당 데이터를 찾아가는 유일한 주소 값이며 우리가 인덱스를 이용한다는 것은 인덱스로부터 조건을 만족하는 인덱스 값을 액세스한 후 ROWID를 확인하여 ROWID 값으로 테이블을 액세스하는 것을 의미한다.

이와 같이 인덱스 액세스 후 테이블을 액세스하는 경우에 발생하는 I/O는 한 번에 하나의 블록만 액세스하는 랜덤 액세스가 발생하게 된다.

 

랜덤액세스의 종류는 아래와 같다.

- 확인 랜덤 액세스 (WHERE 절)

- 추출 랜덤 액세스 (SELECT 절)

- 정렬 랜덤 액세스 (ORDER BY 절)

 

그 중 랜덤 액세스 부하를 가장 많이 발생 시키는 것은 "확인 랜덤 액세스"이다

아래에서 "확인 랜덤 액세스"에 대한 부하를 줄이는 방법에 대해서 실습해보자.

 

 

예제실습

use INDEX_TEST
GO

--테이블 생성
--DROP TABLE OrderT
GO

CREATE TABLE OrderT(
주문번호 int
,상품 varchar(100)
,주문일자 datetime,
 CONSTRAINT PK_OrderT_주문번호_상품 PRIMARY KEY CLUSTERED
(
주문번호 ASC,
상품 ASC
)
)

--추가 인덱스 생성
실행계획 및 I/O 부하 비교를 위해서 추가적인 복합 인덱스를 생성한다.
create nonclustered index IX_주문번호_상품_주문일자 on OrderT(주문번호,상품,주문일자)
create nonclustered index IX_주문번호_주문일자 on OrderT(주문번호,주문일자)
create nonclustered index IX_주문번호_상품 on OrderT(주문번호,상품)
GO

--인덱스 정보 확인
인덱스 정보를 확인해 보면 4가지 순서로 정리된 복합(결합)인덱스가 생성 된 것을 볼 수 있다.
1. 주문번호, 상품
2. 주문번호, 상품, 주문일자
3. 주문번호, 주문일자
4. 주문번호, 상품(PK)

sp_helpindex OrderT

--데이터 입력
9999개의 데이터를 입력
DECLARE @i INT
DECLARE @Lastnum INT
DECLARE @date datetime

SET @i = 1
SET @Lastnum = 10000
SET @date = '2023-03-01 00:00:00.000'

WHILE @i<@Lastnum
BEGIN
insert into OrderT values (4,'꿀'+convert(varchar(100), @i),@date+@i)
SET @i = @i+1 --숫자 더하기
END;
GO

--데이터 확인
select * from OrderT
GO

--I/O 비용 확인 활성화
SET STATISTICS IO ON

--비용비교
실행 계획을 활성화 하고(Ctrl + M) 클러스터 인덱스를 제외한 비클러스터 인덱스를 3개를 모두 활용하여 조회 후 비용을 비교해 보자
(비교를 위해 강제로 인덱스 힌트를 추가 하였다)

--1번 쿼리
select 주문번호 from OrderT with(index(IX_주문번호_상품))
where 주문번호=4 and 주문일자 between '20230301' and '20230302'

--2번 쿼리
select 주문번호 from OrderT with(index(IX_주문번호_상품_주문일자))
where 주문번호=4 and 주문일자 between '20230301' and '20230302'

--3번 쿼리
select 주문번호 from OrderT with(index(IX_주문번호_주문일자))
where 주문번호=4 and 주문일자 between '20230301' and '20230302'

 

 

(1개 행 적용됨)

테이블 'OrderT'. 스캔 수 1, 논리적 읽기 20658, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

 

(1개 행 적용됨)

테이블 'OrderT'. 스캔 수 1, 논리적 읽기 68, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

 

(1개 행 적용됨)

테이블 'OrderT'. 스캔 수 1, 논리적 읽기 2, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

 

쿼리문의 WHERE 조건이 주문번호(점 조건)와 주문일자(선분조건) 순서로 되어있다.

- 점 조건 : IN, = 연산자를 이용한 조건을 의미하며 해당 연산자는 하나의 점만을 의미하게 된다.

- 선분 조건 : LIKE, BETWEEN, <, > 등과 같이 점 조건을 제외한연산자를 사용한 조건을 의미한다. 선분 조건은 하나의 점만을 의미하는 것이 아니면 해당 조건을 만족하는 모든 실수를 의미하게 된다.

 

WHERE 절에 사용하는 조건은 점 조건과 선분 조건으로 구분된다.

이와 같이 조건에 사용된 연산자에 의해 액세스해야 하는 처리 범위의 차이가 발생한다.

- 점 조건 + 점 조건 : 두 조건에 의해 처리 범위 감소

- 점 조건 + 선분 조건 : 두 조건에 의해 처리 범위 감소

- 선분 조건 + 선분 조건 : 앞의 선분 조건에 의해 처리 범위 감소

- 선분 조건 + 점 조건 : 앞의 선분 조건에 의해서만 처리 범위 감소

---

성능분석

--1번 쿼리

인덱스 'IX_주문번호_상품'를 사용하여 주문번호가 4인 9999개를 필터링 했지만

주문일자가 인덱스에 포함되어 있지 않기 때문에 '확인랜덤 액세스'가 발생한다(인덱스에 없는 데이터를 ROWID를 통해 테이블에서 가져오는 현상으로 불필요한 I/O 발생시켜 성능저하를 유발한다)

9999개를 읽어서 확인랜덤 액세스를 거쳐 1개의 행을 리턴한다

 

--2번 쿼리

인덱스 'IX_주문번호_상품_주문일자'를 사용하여 주문번호가 4인 9999개를 필터링 했지만

주문번호,주문일자 순서로 생성되어 있지 않기 때문에 주문일자 별로 정리된 정보를 찾기 힘들다(하지만 테이블까지 가지 않고 인덱스 안에서 데이터를 찾기 때문에 1번 쿼리보다 I/O가 많이 줄어든다)

9999개를 읽어서 인덱스데이터를 거쳐 1개의 행을 리턴한다

 

--3번 쿼리

인덱스 'IX_주문번호_주문일자'를 사용하여 주문번호가 4인 9999개를 필터링 후 주문일자로 추가 필터링이 진행된다.

1개를 읽어서 인덱스데이터를 거쳐 1개의 행을 리턴한다. 성능이 가장 좋다

---

참고사이트

http://www.gurubee.net/lecture/2230

http://www.gurubee.net/lecture/2229

http://www.gurubee.net/lecture/2228