DataBaser.Net: Index Tuning Wizard와Index Design

1 인덱스 튜닝 마법사 #

MSSQL Server는 “인덱스 튜닝 마법사” 라는 Performance Tuning Tool을 제공한다. 이놈은 Profiler라는 툴로 수집된 자료를 이용하여 분석한 다음 Index에 대한 조언을 해 준다. 이 툴이 경력 2~3년 차 정도의 수준이라고 할 만큼 뛰어나다. 하지만 맹신해서는 안 된다. 인덱스 튜닝 마법사가 조언해주는 결과는 군더더기들이 붙어 있기 때문에 필요한 부분은 사람이 수정을 해주어야 한다. 역시 가장 좋은 것은 사람이 판단하는 것이 좋다.

필자는 인덱스 튜닝 마법사가 어떤 로직에 의해 결과를 내는지는 모르지만 많은 경험을 통해 인덱스 튜닝 마법사가 결과를 만드는 패턴을 어느 정도는 알고 있다. Index Tuning Wizard는 주로 결합인덱스를 생성하려고 많은 노력을 기울인다. 조건절을 해석하여 열심히 결합인덱스를 만들려고 한다. 그러므로 필요 없는 인덱스 만들어질 수도 있다. (소스로 제공되는 Trace자료에 따라서 결과가 틀려지기도 한다.)

예를 들어, 병원의 경우 ChartNo(차트번호)라는 컬럼이 대부분의 Query 조건에 속하게 된다. 경우에 따라 틀리지만 하나의 테이블에 같은 ChartNo는 1~3건 정도다. 그런데 Index Tuning Wizard를 통해 만들어진 Index는 다음과 비슷하다.

ChartNo + ColA + ColB
ChartNo + ColB + ColC + ColD
ChartNo + ColZ

대부분의 결과가 이렇다. Where에 명시한 조건을 모두 결합인덱스로 만들어진 격이다. 소스를 보지는 않았지만 경험상 Index Tuning Wizard는 조건절에 명시한 컬럼과 선택도를 이용하여 Index를 생성하는 것으로 파악된다.

위의 경우 WHERE ChartNo = 1234 는 많아야 3건, 만아야 몇 십건임에도 불구하고 ChartNo로 시작한 결합인덱스가 많이 만들어진 것을볼 수 있다. 이러한 것은 Index Tuning Wizard 뿐만 아니라 개발자도 이러한 경향을 보이는 경우가 있다. 그래서 필자와 같은 경우 Tuning 작업을 할 때 환경파악 이후 Index 정리작업부터 한다. 필요 없는 인덱스를 지워주거나 다시 작성하는 것이다. 위와 같은 경우는 모두 삭제 대상이다. ChartNo 컬럼에 단일 인덱스로 구성하는 것이 가장 최적이다. 예제를 보자.

[edit]

2 인덱스 정리 예제 #

한 테이블에 다음과 같은 인덱스가 생성되어 있다.

마치 인덱스 튜닝 마법사를 사용한 것과 비슷한 인덱스를 가졌다. 분명히 여기에 생성된 인덱스 몇 개는 실제로 사용하지 않는다. 만약 사용한다면 그것도 문제다. 다음의 위 인덱스 중 사용되지 않는 인덱스이다.

IDX_YEAR_MONTH_DAY
IDX_YEAR_MONTH_DAY_HOUR

왜 사용되지 않는지는 이미 이전 글에서 언급하였으므로 생략하도록 하겠다. 더 살펴보아야 할 것은 선택도이다. 선택도의 차이가 많이 나지 않는다면 인덱스를 추가로 정리해 주어야 한다. 또한 쿼리의 패턴도 보아야 한다. 선택도와 쿼리의 패턴을 조사해본 결과 이 테이블의 쿼리나 SP는 “년월일” 까지만을 필요로 한다. 그러므로 시간 이하의 단위는 필요 없다. 그러므로 다음의 인덱스도 제거되어야 한다.

IDX_YEAR_MONTH_DAY_HOUR_SERVER

또한 YEAR, MONTH, DAY, LEVEL은 액세스에 큰 영향을 미치지만 LEVEL은 영향을 미치지 않으므로 IDX_YEAR_MONTH_DAY_LEVEL_TYPE의 인덱스에서 LEVEL은 빠져도 된다. 이런 식으로 보면 YEAR, MONTH, DAY로 구성되는 Non-Clustered Index는 모두 제거되어야 함을 알 수 있다. 다른 쿼리에도 전혀 무리가 없을 것이다. 쿼리의 예제를 보자.

SELECT 
	YEAR
, 	MONTH
, 	DAY
, 	HOUR
, 	MINUTE
, 	TYPE
,	SUM([COUNT]) AS COUNT   
FROM T_CURRENT_CHARACTER_INFO
WHERE  YEAR BETWEEN DATEPART(yy, '2004-05-25 00:00:00') 
AND DATEPART(yy, '2004-05-26 00:00:00') 
AND  MONTH BETWEEN DATEPART(mm, '2004-05-25 00:00:00') 
AND DATEPART(mm, '2004-05-26 00:00:00') 
AND  DAY  BETWEEN DATEPART(dd, '2004-05-25 00:00:00') 
AND DATEPART(dd, '2004-05-26 00:00:00') 
AND  TYPE = 30
AND LEVEL BETWEEN 100 AND 400  
GROUP BY YEAR, MONTH, DAY, HOUR, MINUTE, TYPE  
ORDER BY YEAR, MONTH, DAY, HOUR, MINUTE, TYPE   
GO

위의 경우는 거의 최악이다. 날짜를 년, 월, 일로 쪼개는 것은 의미가 있지는 않다. 실제로 위 쿼리를 실행하면 YEAR에 걸린 인덱스 만을 사용한다. Clustered Index를 사용할 수도 있으며 사용하지 않을 수도 있다. 만약 Non-Clustered Index를 사용한다면 비효율이 될 것이다. 테스트 결과 이 쿼리는 Non-Clustered Index를 사용했다.

테이블. 스캔 수 1, 논리적 읽기 수 8447, 물리적 읽기 수 0, 미리 읽기 수 0.

Clustered Index를 사용하게 하였더니 다음과 같은 결과를 보였다.

테이블. 스캔 수 1, 논리적 읽기 수 1255, 물리적 읽기 수 0, 미리 읽기 수 0.

만약 위 테이블을 풀스캔했다면 얼마만큼의 I/O가 발생할까?

테이블. 스캔 수 1, 논리적 읽기 수 2026, 물리적 읽기 수 4, 미리 읽기 수 223.

결과로 미루어볼 때 YEAR의 선택도는 약 0.5라는 것을 알 수 있다. 위 결과는 67건이다. 67건을 읽기 위해서 많은 페이지를 읽었다. 테이블 디자인이 그 첫 번째 원인이다. YYYY, MM, DD를 복합속성으로 볼 것인지, 단일속성으로 볼 것인지에 대한 충분한 고려가 있어야 한다. 이와 같은 경우는 YYYYMMDD가 원자값이다. 또한 데이터 타입도 문제가 있다. 데이터 형식의 선행 규칙(선행규칙 참고)을 보면 TinyInt는 Int보다 우선순위가 낮다. 그렇기 때문에 WHERE Month = DATEPART() 는 TinyInt = Int 와 같이 비교가 되므로 내부적으로 좌측 컬럼인 Month는 Covert(Int, Month)와 같이 내부적 변형이 일어난다. 그러므로 인덱스를 사용하지 못한다. 세 번째 문제는 인덱스의 비효율적인 생성이다. MSSQL Server는 데이터에 액세스할 때 더 적은 페이지를 읽으려고 하기 때문에 같은 인덱스가 있다면 상대적으로 크기가 작인 인덱스를 선택한다. 그러므로 위와 같은 경우는 Bookmark Lookup에 의해 더 많은 페이지를 액세스한 것이다. 다음은 인덱스를 정리하고, 수정된 쿼리이다.

--매개변수를 날짜형으로 받는다는 가정
DECLARE @SDate CHAR(8)
DECLARE @EDate CHAR(8)

SET @SDate = CONVERT(CHAR(8), CAST('2004-05-25 00:00:00' AS DATETIME), 112)
SET @EDate = CONVERT(CHAR(8), CAST('2004-05-26 00:00:00' AS DATETIME), 112)

SELECT 
	YEAR
, 	MONTH
, 	DAY
, 	HOUR
, 	MINUTE
, 	TYPE
,	SUM([COUNT]) AS COUNT   
FROM T_CURRENT_CHARACTER_INFO
WHERE  YEAR BETWEEN LEFT(@SDate, 4) AND LEFT(@EDate, 4)
AND  MONTH BETWEEN SUBSTRING(@SDate, 5, 2) AND SUBSTRING(@EDate, 5, 2)
AND  DAY  BETWEEN RIGHT(@SDate, 2) AND RIGHT(@EDate, 2)
AND  TYPE = 30
AND LEVEL BETWEEN 100 AND 400  
GROUP BY YEAR, MONTH, DAY, HOUR, MINUTE, TYPE  
ORDER BY YEAR, MONTH, DAY, HOUR, MINUTE, TYPE   
GO

다음은 정리된 인덱스다.

다음은 수행결과이다.

테이블. 스캔 수 1, 논리적 읽기 수 144, 물리적 읽기 수 0, 미리 읽기 수 0.

쿼리의 수행결과 I/O의 양이 확연히 줄어든 것을 알 수 있다. 검색 뿐만 아니라 입력, 갱신, 삭제의 성능도 인덱스의 수가 줄어들었으므로 더 낳을 것이다. 여기서 더 최적화를 시켜보자. 더 최적화 할 것은 인덱스의 순서를 조절하는 것이다. HOUR, MINUTE는 필요가 없고, LEVEL에 대한 조건은 항상 쓰이고, SERVER조건은 없을 때도 있으므로 YEAR, MONTH, DAY, LEVEL, SERVER, CLASS, TYPE, HOUR, MINUTE 순서로 조절해 보자.

ALTER TABLE T_CURRENT_CHARACTER_INFO
DROP CONSTRAINT PK_T_CURRENT_CHARACTER_INFO
GO 

ALTER TABLE T_CURRENT_CHARACTER_INFO
ADD 
  CONSTRAINT PK_T_CURRENT_CHARACTER_INFO 
  PRIMARY KEY(YEAR, MONTH, DAY, LEVEL, SERVER, CLASS, TYPE, HOUR, MINUTE)
GO

Query 수행 결과는 다음과 같다.

테이블. 스캔 수 1, 논리적 읽기 수 96, 물리적 읽기 수 0, 미리 읽기 수 0.

이제 최적화 되었다. 실제로 이렇게 인덱스를 정리하는 것은 꽤 큰 성능 튜닝효과가 있다. 예제는 단순히 조회연산만을 가지고 설명했으나 인덱스는 삽입, 갱신, 삭제 연산시 내부적으로 유지보수 코드가 실행된다. 그러므로 전체적으로 보았을 때에는 많은 비용이 줄어든다.

Contents

1 인덱스 튜닝 마법사 #

2 인덱스 정리 예제 #

3 Index Design의 조언 #