[MSSQL][advanced] 성능 향상을 위한 query 작성과 tuning

쿼리 구문 작성

필요한 column만 명시한다.

SELECT * 을 사용하는 것은 피한다.
사용하지 않는 데이터를 호출하는 것만으로도 이미 많은 부하가 생긴다.
특히 text 타입의 데이터를 호출하는 경우는 그 정도가 심해진다.
data type의 byte가 적은 컬럼을 주로 사용하는 것이 좋다.

COUNT(*)을 사용하라.

COUNT(특정column) 으로 호출하는 경우가 있다.
이 경우 해당 컬럼의 NULL값을 제외한 COUNT를 가져오게 된다.
NULL값을 일일이 체크하면 호출 속도가 저하되게 된다.
NULL을 체크해야 하는 경우가 아닌 대부분의 경우 COUNT(*)을 체크한다.
COUNT(*)는 NULL값의 경우도 모두 count에 추가하지만 그로 인해 성능의 저하가 많이 줄어든다.

List 호출이 아닌 1 row 호출을 하는 경우 TOP 1을 사용한다.

성능상의 이슈로 이에 대해 권고하는 것은 아니다.
WHERE 조건절이 동적으로 변하는 경우를 고려한 작성을 하라는 것이다.
물론 QUERY를 호출하기 전 단계(비즈니스 로직 단계)에서 Validation 체크를 충분히 고려하겠지만 개발의 빈틈은 호출하는 쪽과 db쪽 양쪽 모두 없어야 한다.

where조건문의 왼쪽은 되도록 변형되지 않은 순수한 column만을 선언하라.

where name + '' = '조건' 과 같이 왼쪽 조건을 변형하지 말라.
where name = '' + '조건' 과 같이 오른쪽에 조건선언을 하라.
조건 일치를 매 로우마다 확인할 때 왼쪽 조건을 변형하게 된다.
그만큼 부하가 눈에 띄게 증가한다.

커서 및 임시테이블의 내용을 최대한 자제하라.

커서보다는 임시 테이블이 , 임시테이블 보다는 테이블 변수를 사용하는 것이 성능에 좋다.
커서의 경우 내부적으로는 임시 테이블을 사용하지만 임시 테이블을 쓴다고 부하가 더 발생하는 것이 아니다.
오히려 커서의 부가적 기능 때문에 서버 자원을 더 낭비하게 된다.
커서로 처리할 수 있는 것은 모두 임시 테이블이나 테이블 변수로도 처리가 가능하므로 되도록 커서를 쓰지 않는다.

JOIN을 사용하는 경우 INNER JOIN을 되도록 사용하라.

동일한 효과를 가지는 쿼리를 작성할 경우 INNER JOIN이 아닌 LEFT OUTER JOIN을 쓰는 경우가 있다. (습관적으로?)
확연히 속도가 차이가 나므로 INNER JOIN을 사용하는 것이 좋다.

하위쿼리의 사용시 불필요한 SELECT 구문은 줄여라.

아래와 같은 경우를 보자.

SELECT
	kind,
	SELECT name FROM kind_descriptoin WITH (READUNCOMMITTED) WHERE kind_id = kind
	...
FROM list WITH (READUNCOMMITTED)WHERE kind = 1

kind라는 값은 WHERE 조건을 통해 하나의 값을 호출하게 되어있다.
kind가 1에 대한 kind_name의 값을 호출 하기 위해 서브쿼리를 사용하였지만 이는 올바른 사용이 아니다
SELECT가 해당 줄을 호출 할 때마다 서브쿼리에 있는 name을 구하는 쿼리를 호출하기 때문이다.
출력하는 줄이 많으면 많을 수록 서브쿼리의 실행 횟수또한 증가하게 되며 불필요한 부하를 가져온다.
따라서 위의 쿼리는 아래와 같이 사용하여야 옳다.

SELECT 
	A.kind,
	B.name
	...
FROM list AS A WITH (READUNCOMMITTED)
INNER JOIN kind_descriptoin AS B WITH (READUNCOMMITTED) ON A.kind = B.kind_idWHERE kind = 1

view의 총 사용을 줄여라.

view를 사용할 바엔 바로 사용하는 것이 단계 수를 줄이므로 더 낫다.

저장 프로시저를 사용하라.

저장 프로시저는 복잡한 SQL문을 단순화 시켜주고, 보안문제를 해결해주며 더 나아가 빠른 성능의 매개변수, 출력 매개변수, 리턴 값을 사용할 수 있다.

쿼리 구문 튜닝

MSSQL을 사용하는 경우 예상 실행 계획을 자주 확인하라.

MSSQL은 쿼리분석기에서 쿼리를 테스트 하기 편하다.
좋은 기능 중 하나가 예상 실행 계획인데 해당 쿼리가 성능상 어떤 장, 단점을 가지고 있는지 보기 쉽게 아이콘으로 표시해준다. (그래픽 실행 계획 아이콘)
실행계획의 내용은 버릴것이없으므로 꼼꼼히 따져봐야 한다.
튜닝의 시작은 성능 분석이다.

Index를 타는지 항상 체크하라.

Index를 활용하지 않은 검색은 데이터가 많으면 많을수록 성능은 급격히 떨어진다.
게시판 schema를 잘못 짠 경우 이런 현상이 발생하는데 이에 대한 점검을 늘 해야한다.
흔히 오해하기 쉬운 것 중 하나가 WHERE 조건절은 필요한 column만 존재해야 한다는 의식이다.
WHERE 조건절에는 Clustered Index Seek를 타기 위한 column이 우선 존재해야 하고 그 후에 원하는 데이터를 얻기 위한 조건절이 존재해야 한다.
조건자체가 Clustered Index column이면 제일 좋다.
조건절에 Index에 해당하는 columns이 존재하는 경우 우선적으로 해당 조건을 만족하는 행을 호출한 후 나머지 조건에 대해 만족하는 행을 다시 호출하게 된다.

Clustered Index Seek를 항상 체크하라.

Clustered Index Scan을 타는 것 만으로도 속도는 향상이 되지만 완전하진 않다.
Clustered Index column의 일정 구간을 타는 Seek여야 대량으로 증가하는 Data에 대한 부하를 감당할 수 있다.
이를 위해 Index의 구간 체크를 해야 한다.

만약 검색하는 column이 Clustered Index Column인 경우는 단방향 WHERE 조건문으로도 Index Scan이 성립이 된다.
자신의 컬럼에서 그대로 찾아서 시작 지점부터 끝까지 Index를 타면 되기 때문이다.

하지만 일반 Non Clustered Index의 경우는 Clustered를 찾기 위해 해당 column의 Clustered Index 정보를 호출해야 하는 부담이 생긴다.
왜냐하면 결국 호출을 하기 위해서는 해당 데이터의 위치를 찾아야 하고 이 위치를 가장 밀도있게 알고 있는 Clustered Index에서 해당 데이터의 위치를 찾아 가져오기 때문이다. (바로 찾게 되면 Clustered Index보다 범위가 크기 때문에 중간에 Clustered Index를 통해 찾는다.)
결국 구간 체크가 아닌 Non Clustered Index의 단방향 WHERE 조건문은 Clustered 의 전체 스캔을 하게 되는 결과를 가져온다.

Clustered Index와 Non Clustered Index의 Index 구조의 차이는 다음 두 페이지에서 확인할 수 있다.

1. 클러스터형 인덱스 구조
2. 비클러스터형 인덱스 구조
   

 

기타 잡설

Index 설정시 DESC 정렬을 해야 빠르다?

그렇지 않다.
오름차순이건 내림차순이건 Index가 걸려있으면 조건에 따라 Clustered Index를 찾아 가게 된다.
다만 여러번 테스트를 해보았을 때 극소한 차이로 DESC가 걸린 정렬이 좀더 빠른 경우가 있었다.
이는 논리적인 검색으로 인한 빠르기 차이가 아닌 Data의 실제 물리적인 위치를 Index하는 과정에서 DESC가 좀더 효율적이기 때문이 아닐까 싶다. (이는 추측일 뿐이며 논리적으로 속도의 차이는 없다고 봐야 한다.)

Clustered Index Seek를 타면 무조건 빠르다?

그렇지 않다.
검색 조건이 모두 Index를 만족하지 않는 경우 WHERE 조건절에 Clustered Index column의 조건을 추가한 다음의 경우를 보자.

SELECT  * FROM test_query WITH (READUNCOMMITTED)
WHERE idx > 0 AND content = 'testValue300000'

Clustered Index column이 검색 조건에 존재하므로 Clusterd Index Seek를 타게 된다.
하지만 성능상의 이슈는 없다.
왜냐하면 Clustered 전체 절을 검색하는 결과가 되기 때문이다.

Non Clustered Index column은 무조건 마지막엔 Clustered Index Column을 조회한다?

그렇지 않다.
Primary Key가 없는 (Clustered Index 설정이 없는) 테이블의 경우 결국 Non Clustered Index의 주소값을 이용해 데이터를 검색하게 된다.
다만 데이터 검색이 데이터의 순서와 index의 순서가 일치하는 Clustered Index가 더 빠르기 때문에 Clustered Index가 있는 경우 해당 Column의 주소값을 참조하는 것이다.

Clustered Index 검색이 무조건 Non Clustered Index보다 빠르다?

그렇지 않다.
단일 열 검색의 경우 두 검색의 속도는 거의 동일하다고 보아야 한다.
다만 검색한 내용이 범위대상과 같은 여러줄의 리스트가 되면 페이지의 단편화 현상과 연관하여 Non Clustered Index의 속도가 느려지게 된다.
이유는 여러열을 찾게 되는 경우. 이중 범위 검색의 경우 데이터가 서로 밀집되어 있으므로 Clustered Index에서는 바로 시작 지점에서 끝 지점까지의 데이터를 호출하면 되지만 Non Clustered Index는 각 열에 대해 포인트 점프를 통해 리스트를 호출해야 한다. 단편화가 되지 않은 경우 이 부분의 부하가 그리 크지 않지면 단편화가 심할 수록 그 속도는 저하되게 된다.