[mssql] 인덱스를 타지 않는 쿼리

Mssql을 사용하며 Index를 타지 않는 Query를 정리해 보자

 

도움 된 블로그!

hckcksrl

isstory83

 

 

1. where 컬럼의 가공, 함수, 연산, 형변환 금지

SELECT
	[buy_id]
    , [product_id]
    , [create_date]
FROM
	[dbo].[pmt_product_buy]
WHERE 
	CONVERT(DATE, [create_date]) < '2022-01-01'

 

해당 쿼리는 create_date 컬럼에 대한 Index를 타지 않는다.

 

convert 함수를 통하여 변형이 되었기 때문에 create_date와는 다른 컬럼이 된다. 

 

개선 방법은 

 

SELECT
	[buy_id]
    , [product_id]
    , [create_date]
FROM 
	[dbo].[pmt_product_buy]
WHERE 
	[create_date] BETWEEN '2022-01-01' AND '2022-03-01'

 

다른 함수를 사용하여 데이터를 조회해야 한다.

 

2. Like 검색 시 문자열 앞에 % 사용 금지

SELECT
	t1.*
FROM
	[dbo].[pmt_product_info] t1
WHERE
	[product_price] LIKE '%3%'

 

문자열 앞에 %가 붙게 될 경우 product_price 컬럼의 인덱스와는 무관하게 전체 풀스캔을 타게 된다.

 

부득이하게 대용량의 모든 테이블을 조회할 경우에는 차라리 where을 통하여 한번 데이터를 필터링한 후 조회를 하거나 

 

카프카와 검색 엔진을 사용하여 조회하면 좋다.

 

3. Null 조건의 사용

SELECT
	t1.*
FROM
	[dbo].[pmt_product_info] t1
WHERE
	[product_price] is null

 

where절에 null을 사용할 경우 인덱스와 무관하게 풀 스캔을 하게 된다.

 

4. not(부정문)을 사용할 경우

SELECT
	t1.*
FROM
	[dbo].[pmt_product_info] t1
WHERE
	[product_price] != 3000

 

부정문을 사용할 경우 index를 타지 않는다. 

 

한 값에 대한 부정이 아닌 범위를 지정하여 검색하는 게 좋다.

 

SELECT
	t1.*
FROM
	[dbo].[pmt_product_info] t1
WHERE
	[product_price] < 3000 and [product_price] > 3000

 

5. in, or 연산

SELECT
	t1.*
FROM
	[dbo].[pmt_product_info] t1
WHERE
	[product_price] in (3000, 1500, 1000)

 

in 조건을 사용할 경우 항상 인덱스를 타지 않는 것이 아니라 in 조건 안에 포함되는 데이터가 많을 경우 데이터베이스가 판단하여 테이블을 풀스캔을 한다고 한다..;; 

 

이 부분은 매우 흥미로운 부분이었다. 

 

or 조건의 사용은 

 

SELECT
	t1.*
FROM
	[dbo].[pmt_product_info] t1
WHERE
	([product_price] = 3000 or [product_price] = 1500 or [product_price] = 1000)

 

or의 경우 인덱스를 사용하지 못한다. 참이 하나라도 있으면 참이기 때문에 풀 스캔을 타게 된다.

 

2023.09.25 추가

DISTINCT가 낮은 경우 인덱스를 만드는게 아니라 전체에서 찾는게 더 빠르기 때문에 인덱스를 안만드는게 좋다.

(ex 사전을 열었는데 남성과 여성의 확률이 50%일 경우 굳이 찾을 필요가 없어서 전체 테이블을 스캔)

 

6. 복합인덱스일 경우의 인덱스 호출 순서

SELECT
	[buy_id]
    , [product_id]
    , [create_date]
FROM 
	[dbo].[pmt_product_buy]
WHERE 
	[product_price] = 3000
	and [create_date] BETWEEN '2022-01-01' AND '2022-03-01'

 

복합 인덱스의 경우 인덱스의 순서가 product_price, create_date일 경우 순서대로 조건을 걸어야 한다.

 

이 부분도 되게 흥미로웠다. 

 

참고 : tothek님의 블로그

 

해당 블로그를 보면서 더 공부를 해야겠다.

 

7. 올바르지 않은 데이터 타입 조회

SELECT
	[buy_id]
    , [product_id]
    , [create_date]
FROM 
	[dbo].[pmt_product_buy]
WHERE 
	[product_price] = '3000'

product_price 컬럼의 경우 Int 타입이다. 

 

하지만 String 타입의 값을 넣어 조회를 할 경우 인덱스를 타지 않을 수 있다. 

 

---

 

어떤 분야든 파고들면 끝이 없는 것 같다. 

 

그래도 다행인 건 모르는 지식을 알아가는 게 재미있어서 다행이다. 

 

부족한 부분을 채우기 위해 GPT에게 물어봐서 팩폭도 맞았다. https://integer-ji.tistory.com/404