II. 쿼리 금기사항

1. SARG란?

Search Argument, 검색인수라고 하며 Predicate(조건식)에서 검색 대상 및 범위를 제한할 수 있는 식으로 

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

쿼리 성능 최적화에 중요한 역할을 하며, 인덱스를 제대로 활용하게 해준다. 이러한 활용이 DB 검색 속도를 향상시킨다.

 

2. Non-SARG란?

비검색인수라고 하며, SARG의 반대말로 검색을 제한하지 않는 식이다.

인덱스 사용이나 쿼리 최적화의 방해요소이다.

 

1) 불필요한 열 참조

아래와 같이 * 모든컬럼을 다 부르는것도 속도를 저해한다. 

SELECT * FROM dbo.Categories

 

아래 행의 결과는 50만건이 나오는데, 이 많은 데이터를 한꺼번에 처리하여 작성일자를 sorting까지 하게 된다면 많은 메모리가 요구될 수 있다. 서버에 부담이 될 수 있으니  날짜 조건을 세분화 하거나 LIMIT, TOP을 통해 선택적으로 조회해야 한다.

select * from 웹사이트오류로그 
where 작성일자 between '20201001' and '20201001 23:59:59' 
order by 작성일자 desc

 

2) index 열에 부정형 사용 주의

조건은 = 이 가장 빠르고 효율적이다. <, > between like과 같은 범위 조건은 인덱스 효율이 떨어진다.

--1) NOT EQUAL (!=, <>)
-- 나쁜 예 (인덱스를 제대로 활용 못함)
SELECT * FROM 사원 WHERE 부서번호 != 10

-- 좋은 예 (인덱스 활용 가능)
SELECT * FROM 사원 WHERE 부서번호 < 10 OR 부서번호 > 10

--2) NOT IN
-- 나쁜 예 (인덱스 활용도 낮음)
SELECT * FROM 상품 
WHERE 카테고리_ID NOT IN (1, 2, 3)

-- 좋은 예
SELECT * FROM 상품 
WHERE 카테고리_ID > 3 OR 카테고리_ID < 1

--3) NOT LIKE

-- 나쁜 예 (인덱스 활용 못함)
SELECT * FROM 고객 
WHERE 이름 NOT LIKE 'Kim%'

-- 좋은 예 (필요한 데이터만 찾기)
SELECT * FROM 고객 
WHERE 이름 LIKE 'Lee%' OR 이름 LIKE 'Park%'

--4) IS NOT NULL

-- 나쁜 예 (인덱스 활용도 낮음)
SELECT * FROM 주문 
WHERE 배송일자 IS NOT NULL

-- 좋은 예 (특정 날짜 범위로 조건 변경)
SELECT * FROM 주문 
WHERE 배송일자 >= '2024-01-01'

 

검색 범위(행 수)가 적다면 긍정 조건, 가능하면 = 를 사용한다.

 

3. Non-SARG, index 열 값 변형

1)  index 열에 사칙연산 적용

첫번째 나쁜예는 급여ID의 인덱스를 활용할 수 없기 때문에 SARG 위반에 해당한다.

-- 나쁜 예 (인덱스를 활용할 수 없음)
SELECT * 
FROM 직원정보
WHERE 급여ID * 12 = 60000000
AND 부서코드 = 'D001'

-- 좋은 예 (인덱스 활용 가능)
SELECT * 
FROM 직원정보
WHERE 급여ID = 60000000 / 12
AND 부서코드 = 'D001'

 

2) index 열에 함수로 가공되지 않도록 구현

- substring 대신 like를 쓴다

-- 나쁜 예 (인덱스 활용 못함)
SELECT * FROM 고객테이블
WHERE SUBSTRING(주민번호, 1, 2) = '85'

-- 좋은 예 (인덱스 활용 가능)
SELECT * FROM 고객테이블
WHERE 주민번호 LIKE '85%'

- convert 대신 날짜범위를 직접 비교한다.

-- 나쁜 예 (인덱스 활용 못함)
SELECT * FROM 주문테이블
WHERE CONVERT(VARCHAR(10), 주문일자, 112) = '20240101'

-- 좋은 예 (인덱스 활용 가능)
SELECT * FROM 주문테이블
WHERE 주문일자 >= '2024-01-01' 
AND 주문일자 < '2024-01-02'

- DATEDIFF 대신 날짜 범위로 변환하여 비교한다.

-- 나쁜 예 (인덱스 활용 못함)
SELECT * FROM 회원테이블
WHERE DATEDIFF(YEAR, 가입일자, GETDATE()) = 2

-- 좋은 예 (인덱스 활용 가능)
SELECT * FROM 회원테이블
WHERE 가입일자 >= DATEADD(YEAR, -2, CAST(GETDATE() AS DATE))
AND 가입일자 < DATEADD(YEAR, -1, CAST(GETDATE() AS DATE))

 

- isnull

-- 나쁜 예 (인덱스 활용도 낮음)
SELECT * FROM 주문테이블
WHERE ISNULL(배송완료일자, '1900-01-01') = '1900-01-01'

-- 좋은 예 (인덱스 활용 가능)
SELECT * FROM 주문테이블
WHERE 배송완료일자 IS NULL

 

3) index열에 암시적 데이터 형 변환

인덱스 열의 사용되는 식은 그 인덱스 열의 데이터 타입과 동일한 형식으로 작성해야 한다.

문자인지 숫자인지, 문자인지 날짜인지, (var)char 인지 n(var)char 인지 확인이 필요하다.

서로 다른 타입의 조건식이 들어가면 양쪽에서 크기가 더 작은쪽에 암시적 형변환이 일어남

 

- CHAR vs INT (문자 vs 숫자)

-- 나쁜 예 (암시적 형변환 발생, 인덱스 활용 못함)
SELECT * FROM 고객테이블
WHERE 고객id = '1001'  -- 고객번호는 INT 타입인데 문자열로 비교

-- 좋은 예 (인덱스 활용 가능)
SELECT * FROM 고객테이블
WHERE 고객id = 1001    -- 정수형으로 올바르게 비교

-CHAR vs DATETIME (문자 vs 날짜)

-- 테이블 정의
CREATE TABLE 배송(
    배송일자 DATETIME,
    배송상태 CHAR(10)
)

-- 나쁜 예 (암시적 형변환 발생)
SELECT * FROM 배송 
WHERE 배송일자 = '20240101'  -- 문자열로 비교

-- 좋은 예
SELECT * FROM 배송 
WHERE 배송일자 = '2024-01-01 00:00:00'  -- datetime으로 형변환

 

- CHAR vs NCHAR (일반문자 vs 유니코드)

-- 테이블 정의
CREATE TABLE 고객(
    이름 NVARCHAR(10),  -- 유니코드
    코드 VARCHAR(10)    -- 일반 문자
)

-- 나쁜 예 (암시적 형변환 발생)
SELECT * FROM 고객 
WHERE 이름 = '김철수'  -- 일반 문자열로 비교

-- 좋은 예
SELECT * FROM 고객 
WHERE 이름 = N'김철수'  -- 유니코드 문자열로 비교

 

4. index 열에 like

like은 기본적으로 문자열에 사용하는게 좋고, 다른 타입에 사용한다면 암시적 형변환으로 인해 SARG를 위반하게 된다. 

%가 없는 경우에는 무조건 equal을 쓰도록 한다. 

%는 테이블 전체를 다 스캔하기 때문에, 단독 사용보다는 조건을 걸어서 검색범위를 제한하는게 좋다.

-- 정상적인 LIKE 사용 (인덱스 활용 가능)
SELECT * FROM 회원테이블
WHERE 이름 LIKE '김%'

-- 1) 불필요한 LIKE 사용 (= 연산자 사용이 더 효율적)
-- 나쁜 예
SELECT * FROM 회원테이블
WHERE 전화번호 LIKE '01012345678'

-- 좋은 예
SELECT * FROM 회원테이블
WHERE 전화번호 = '01012345678'

-- 2) %로 시작하는 LIKE (인덱스 활용 불가), 
-- 나쁜 예
SELECT * FROM 상품테이블
WHERE 상품명 LIKE '%노트북%'

-- 3) 숫자형 컬럼에 LIKE 사용 (잘못된 사용)
-- 나쁜 예
SELECT * FROM 주문테이블
WHERE 주문금액 LIKE '10000%'

-- 좋은 예
SELECT * FROM 주문테이블
WHERE 주문금액 >= 100000 AND 주문금액 < 200000

-- 4) 날짜형 컬럼에 LIKE 사용 (잘못된 사용)
-- 나쁜 예
SELECT * FROM 주문테이블
WHERE 주문일자 LIKE '2024-01%'

-- 좋은 예
SELECT * FROM 주문테이블
WHERE 주문일자 >= '2024-01-01' 
AND 주문일자 < '2024-02-01'

 

 

5. 열값 비교 사례

열끼리 비교가 발생하게 되면, 결국 둘 다 변수를 비교하게 되어 검색 대상을 제한하지 못하고 전체를 다 비교하게 된다.

아래식은 인덱스를 제대로 활용하지 못한다.

coalesce 함수는 입력된 표현식 중에서 NULL이 아닌 첫 번째 값을 반환하는 함수로, 여러 개의 값을 순서대로 검사하여 첫 번째로 발견되는 NULL이 아닌 값을 리턴한다. 

 

DECLARE @부서 VARCHAR(10) = NULL;
DECLARE @직급 VARCHAR(10) = '과장';
DECLARE @입사일 DATE = '20180101';

SELECT *
FROM 직원테이블
WHERE 부서 = COALESCE(@부서, 부서)
  AND 직급 = COALESCE(@직급, 직급)
  AND 입사일 = COALESCE(@입사일, 입사일)

위의 식 대신 아래처럼 equal을 사용하여 인덱스를 쓸 수 있도록 하는게 좋다.

SELECT *
FROM 직원테이블
WHERE 직급 = '과장'
  AND 입사일 = '20180101'

 

6. 조건절 상수화 이슈

1) index 열 조건에 로컬 변수(!= 매개변수) 비교

아래처럼 로컬변수를 인덱스 열과  비교할 때  declare 한  @로컬변수를 사용하면 인덱스를 사용하지 못한다.

파라미터를 직접 조건식에 쓰는 것이 방법이 될 수 있음.

-- 상수 사용
SELECT * FROM Products WHERE ProductID <= 500;

-- 로컬변수 선언
DECLARE @ProdID int = 500;

-- PK + 동등 조건
SELECT * FROM Products WHERE ProductID = @ProdID;

-- 범위 조건이나 Unique하지 않은 경우
SELECT * FROM Products WHERE ProductID <= @ProdID;

 

 

쿼리가 인덱스를 사용하는 지 알 수 있는 시점은 컴파일을 할 때이다. 

 

• compile 시점: 쿼리의 실행 계획을 만드는 단계 -> 옵티마이저는 로컬변수를 쓸 때 비어있다고 판단함, 그러므로 인덱스를 쓸 수 없게 될 가능성 높음, 무슨 값인지 수식에서 알 수 없기 때문에 열의 검색 대상과 범위 제한함. 
• runtime 시점: 실제로 쿼리가 실행되는 단계

예외 : 컬럼이 primary key 일 때 조건이 equal 이면 인덱스 사용 가능 

 

아래 조건에서는 인덱스 사용이 가능하다.

dbo.uf_OrderNo()는 항상 동일한 값(19020)을 반환하는 결정적 함수로, 옵티마이저는 결정적 함수의 결과를 상수처럼 취급한다. compile 시점에 함수가 상수값으로 대체될 수 있어 인덱스를 사용할 수 있다. 

 

CREATE OR ALTER FUNCTION dbo.uf_OrderNo()
RETURNS int
AS
BEGIN
   RETURN 19020
END;
GO

SELECT * FROM dbo.Orders
WHERE OrderID >= dbo.uf_OrderNo()​

 

반면 아래 경우는 인덱스를 활용하지 못한다.

-- 반면 이 경우는 다름
DECLARE @ID int = 19020;
SELECT * FROM dbo.Orders WHERE OrderID >= @ID;

 

출처 : 인프런 SW 개발자를 위한 성능 좋은 SQL 쿼리 작성법