성능좋은 쿼리 작성법 - 쿼리 금기 사항

김민재·2022년 9월 28일

- 쿼리 금기사항

쿼리 금기사항 1) SARG 2) Non-SARG 3) 조건절 상수화 이슈 4) 테이블 변수 최적화 이슈 5) 튜닝 사례에 대해서 살펴보겠습니다.

C) 쿼리 금기사항

들어가기에 앞서 SARG는 인덱스 유형(클러스터형 VS 비클러스터형), SQL Server 버젼, SQL Server Edition등에 따라 달라질 수 있으며 아래의 내용은 비클러스형 Index스 기준으로 다뤄집니다.

1) SARG (Search ARGument, 검색인수)

조건자(Predicate)에서 검색 대상 및 범위를 제한할 수 있는 식

해당 열에 인덱스 사용 및 쿼리 최적화를 위한 필요 조건

즉, SARG는 인덱스 사용 및 쿼리 최적화를 위한 필요 조건을 만족함으로서 검색 대상 및 범위를 제한하여 테이블 전체를 스캔하지 않도록 만들어준다.

조건자(또는 조건식, Predicates)란?

TRUE, FALSE 또는 UNKNOWN으로 평가되는 표현식으로
조건자는 WHERE 절 및 HAVING 절의 검색 조건, FROM 절의 조인 조건 및 Boolean 값이 필요한 기타 구문에서 사용된다.

Non-SARG(비 검색 인수)?

SARG 위반 시 1> 검색을 제한하지 않는 식이 되어 2> 인덱스 사용이나 쿼리 최적화에 방해 요소가 생긴다.

2) 쿼리 금기 사항 Non_SARG

INDEX 사용을 위해 SQL 쿼리 작성 시 필수 준수 조건

Non_SARG 사례들

불필요한 데이터 (열,행) 요청

부정형 사용 주의

불필요한 열 값 변형
- 열에 연산자 적용
- 열에 함수 적용
- 암시적 형 변환
- 열에 연산자 적용

부적절한 LIKE 연산

열간 비교

2_1) 불필요한 열 참조

반드시 필요한 열만 SELECT로 조회

특히 *와 같은 전체 컬럼 조회는 성능 부하에 원인이 된다.
/*EXAMPLE*/
SELECT * FROM dbo.Categories
--VS
SELECT CategoryID, CategoryName FROM dbo.Categories

2_2) Index열에 '부정형 사용' 주의

조건은 =이 가장 빠르고 효율적

Index 조건에 따라서 긍정형으로 옵티마이저가 자동으로 변환한다.
(조건은 '같다' , '크다', '작다'만 존재 => 조건이 부정이면 자동으로 긍정을 바뀜)
/*
	EXAMPLE
	ID = {1,2,3,4,5,6}
*/
ID <> 3 => ID > 3 OR ID < 3
ID !> 10248 => ID <= 10248
ID NOT IN (1,3,5) => ID <> 1 AND ID <> 3 AND ID <> 5

결과는 항상 범위 검색이므로 불필요한 부정형(범위 검색) 지양 해야한다.

따라서 검색 범위(행수)가 적고 긍정 조건 가능하면 = 조건으로 구현

특히 주의 1> NOT IN 2> 복합 Index 선행 열
/*
	EXAMPLE 
	PK = (사업장 + 주문일자 + 주문 번호)와 같이 컬럼 2개이 상 PK로 구성된경우
*/
사업장 LIKE % AND 주문일자 = '20211011'
-- 선행 열에 불필요한 부정 조건이 들어가면 안된다.
VS
사업장 IN (1, 2, 3) AND 주문일자 = '20211011'
-- 가능한 긍정 조건 또는 = 조건으로 구현해야한다.

2_3) 불필요한 '열 값 변형'

2_3_1) Index 열에 <(사칙) 연산 적용>

인덱스 열에 사칙연산을 적용하면 안된다.
/*
	EXAMPLE
*/
SELECT OrderID, OrderDate, CustomerID 
FROM EPlan.dbo.Orders
WHERE OrderID * 5 = 10500
-- WHERE OrderID = 10500 / 5
=> 좌변에 있는 식을 모두 우변으로 이동하여 연산하면 해결

2_3_2) Index 열에 <함수 적용>

인덱스 열엔 함수를 적용하면 안된다.

/*
	조건식 컬럼 함수 적용 예시
	EXAMPLE
*/
-- 1. Substring, Left, Right
SELECT OrderID, OrderDate, CustomerID
FROM Northwind.dbo.Orders
WHERE SUBSTRING(CustomerID, 1, 3) = 'CEN'
--- 
-- 2. Convert
SELECT OrderID, OrderDate, CustomerID 
FROM Northwind.dbo.Orders
WHERE CONVERT(varchar(8), OrderDate, 112) = '19960704'
-- 3. datediff OR dateadd
SELECT OrderID, ShippedDate, CustomerID
FROM Northwind.dbo.Orders
WHERE DateDiff(dd, ShippedDate, '19980506') <= 1
-- 4. ISNULL
SELECT *
FROM Northwind.dbo.Orders
WHERE ISNULL(OrderDate, '19970702') = '19970702'

2_3_3) Index 열에 <암시적 데이터 형 변환>

식의 데이터 형식은 열과 동일한 형식이 되어야하며 이는 App에서 호출 시에도 동일하다.

주요 문제 대상
- 문자 VS 숫자
- 문자 VS 날짜
- (VAR)CHAR VS N(VAR)CHAR 유니코드 문자의 경우 문제
/*
	EXAMPLE	
	잘못된 데이터 타입(해당 컬럼에 변형 발생)
	명시적/암시적 변환 유발
*/
--  char_column vs 정수형
SELECT stor_id, stor_name
FROM Pubs.dbo.Stores
WHERE stor_id >= 6380	
-- Convert([stores].[stor_id]) = Convert([@1])
=> 데이터 선행 규칙(예> 문자 < 숫자)에 따라, 칼럼의 선행 규칙이 낮으면
칼럼 convert()가 발생하여 명시적/암시적 변환 유발하기에 주의해야한다.

2_4) Index열에 부적절한 LIKE 연산

SARG 적용을 위해선 LIKE 사용 시
WHERE CustomerID LIKE 'CE%' => CustmerID>='CE' & CustmerID<='CF'
LIKE의 첫 문자는 반드시 상수여야 전체 스캔 범위를 조정할 수 있다.
불필요한 Non-SARG

/*
	EXAMPLE	
*/
--  LIKE 대신 = 대체
...
WHERE  CustomerID LIKE 'CE'
--  '% %' 인덱스 사용 불가
...
WHERE  CustomerID LIKE '%CE%'
-- 숫자 열, LIKE는 반드시 문자
...
WHERE OrderID LIKE '1024%'
-- 날자 시간 열
...
WHERE OrderDate LIKE '05% 1998%'

LIKE, Non-SARG 실 사례

/*
	EXAMPLE	
	복합 INDEX로 (사업장 + 주문일자 + 주문 번호), 컬럼 3개개로 PK로 구성
*/
WHERE 사업장 LKE @c AND 주문일자 = @d AND...
--  잘못된 예 Index Seek -> Full Scan
WHERE 사업장 = @c AND 주문일자 @d AND...
--  복합 인덱스라면 선행열은 = 조건 처리
1> 사업장 1개라면 = 처리
2> 사업장 일부 검색해야한다면 IN 또는 BETWEEN 범위 조건 검색 대체
...

2_5) 열 간 비교

열 간 비교되지 않도록 다른 형식으로 구현, 열간 비교시 검색범위를 제한하지 못하고 전체를 스캔하게 됌
열간 비교 사례 1>

/*
	1> EXAMPLE	
*/
DECLARE @OrderID int = 10248;
DECLARE @OrderDate datetime = '19960704';
DECLARE @CustomerID nchar(10) = NULL;
--
SELECT ...
WHERE OrderID = COALESCE(@OrderID,  OrderID)
AND OrderDate = COALESCE(@OrderDate, OrderDate)
AND CustomerID = COALESCE(@CustomerID, CustomerID);
-- VS
SELECT ...
WHERE OrderID = 10248
AND OrderDate = '19960704';
- 열간 비교 사례 2>
/*
	2> EXAMPLE	
*/
WHERE ISNULL(ShipRegion, '') = 'RJ'
WHERE LEN(일련번호) = 8
-- 불필요한 ISNULL()와 LEN() 사용
WHERE A.제품번호 = B.제품번호
AND (CASE WHEN A.코드구분 = 'C' THEN A.C_코드
ELSE A.D_코드 END) = '123456789'
-- VS
WHERE A.제품번호 = B.제품번호
AND ((A.코드구분 = 'C' AND A.C_코드 = '123456789')
OR (A.코드구분 <> 'C' AND A.D_코드 = '123456789'))
-- WHERE 조건 CASE -> OR 대체

3) 조건절 상수화 이슈

3_1) Index 열 조건에 로컬 변수(≠ 매개변수) 비교

로컬 변수는 Non-SARG이므로 대채하여 사용

대채 방법
1> 매개변수를 직접 사용
2> 해당 쿼리를 직접 사용
- JOIN 혹은 Subquery 형태로
3> 그 외 쿼리 튜닝

옵티마이저가 쿼리의 인덱스를 사용 여부 판단은 Compile 시점이지만 로컬 변수에 값을 알 수 있는 시점은 Runtime 시점이므로 상수화 불가능하다.
⇒ 따라서 가급적 인덱스 열에 로컬 변수 사용해선 안된다.

CREATE PROC dbo.up_Orders
	@OrderID
AS 
	DECLARE @ID int
    SET @ID = @orderID
SELECT * FROM dbo.Orders WHERE OrderID <= @ID;
VS
-- 상수인 경우 => Index Seek
SELECT * FROM dbo.Orders WHERE OrderID <= 10248;
-- 로컬변수인데 범위 조건 또는 유니크 하지 않은 경우 => Table Scan
DECLARE @ID int = 10248;
SELECT * FROM dbo.Orders WHERE OrderID <= @ID; 
-- 로컬변수인데 PK 컬럼이 동등비교인 경우 => Index Seek
SELECT * FROM dbo.Orders WHERE OrderID = @ID;

3_2) Index 열 조건에 (사용자 정의) 함수 비교

사용자 정의 함수 역시 Non-SARG
대채 방법
- 상수나 해당 식을 직접 사용
- 해당 쿼리 직접 사용
- 그 외 쿼리 튜닝
-참고로 SQL Server 2019부터 Scalar UDF Inline 자동 튜닝 지원(그러나 선행 조건이 복잡)

4) 테이블 변수 최적화 이슈 (참고)

테이블 변수를 사용하면 행 수 예측이 안되기 때문쿼리 최적화 어려움이 있다(VS 임시테이블).

따라서 성능 상 중요 쿼리인 경우
- 쿼리 튜닝
- 필요시 임시테이블 사용

일반 테이블의 경우 실행계획을 살펴보면 Order Details를 먼저 조회 후 Orders와 결합
SELECT TOP(5) *
FROM dbo.Orders AS o 
INNER JOIN dbo.[Order Details] AS d
   ON o.OrderID = d.OrderID
WHERE d.ProductID < 2
테이블 변수는 @Orders를 먼저 처리한 후 Order Details와 결합
DECLARE @Orders table (
   OrderID int PRIMARY KEY, OrderDate datetime 
);
INSERT @Orders
SELECT OrderID, OrderDate FROM Orders

SELECT TOP(5) *
FROM @Orders AS o
INNER JOIN [Order Details] AS d
ON o.OrderID = d.OrderID
WHERE d.ProductID < 2

*💡핵심 요약

'좋은 쿼리' 작성 위한 가본 지침

1>지침 첫 번째

WHERE, JOIN 등의 검색조건은 SARG를 만족(중요)
- 불필요한 열 참조 X
- 불필요한 부정 조건 쓰지 X
- 검색 대상 열 변형 X
- 비교 대상 데이터 형식 다르게 X
- LIKE 첫 문자에 불필요한 wildcard 문자 쓰지 X
- 모호한 검색조건 쓰지 X
- 복잡한 검색 조건 사용 X

뷰, 함수 등
- 외부 쿼리에서 SARG만족하도록 설계

2> 지침 두 번째

반드시 필요한 데이터(열, 행)를 필요한 시점에만 요구
- 필요시엔 TOP 연산자 적절히 활용

동일 데이터를 두 번 이상 읽지 X

불필요하게 범위 조건이나 LIKE 조건을 사용 X
- 특히 복합 인덱스 선행 열에 대해

불필요한 연산(쿼리 자체 or 내부 연산자)을 줄이기

함수 호출을 최소화

NOLOCK 힌트나 잠금 세션 옵션을 적절히 활용

김민재

자기 신뢰의 힘을 믿고 실천하는 개발자가 되고자합니다.

이전 포스트

성능좋은 쿼리 작성법 - 기본 고려 사항

다음 포스트