[MSSQL] 서브쿼리의 선택도에 따른 쿼리 읽는 순서 변경이 되는 문제

서브쿼리에서 옵티마이저의 실행계획

---

아래와 같은 쿼리가 있다고 가정해 봅니다.

SELECT TOP 20 *
  FROM T1
 WHERE C2 IN ( SELECT C2 
                 FROM T2
                WHERE C3 = 'ABC' )
ORDER BY C1

보통의 경우 옵티마이저는 아래와 같은 실행계획을 세우게 됩니다.

1. T2 테이블 C3='ABC' 인 C2 컬럼 데이터를 가져온다.

2. T1 테이블의 C2 컬럼에 가져온 데이터가 존재하는 데이터를 가져온다.

3. 이렇게 최종적으로 나온 데이터를 C1 컬럼으로 정렬하여 20 건만 출력한다.

이번 포스팅은 조금 특수한 경우로 아래 쿼리의 경우,

부모쿼리부터 데이터를 가져오면서 서브쿼리와 비교하는 실행계획을 세우게 된 케이스 입니다.

SELECT TOP 20
       T1C1
  FROM dbo.T1 AS A WITH (NOLOCK)
 WHERE T1C2 IN (SELECT T2C2
                             FROM dbo.T2 WITH (NOLOCK)
                            WHERE T2C3 IN ('ABC', 'ABC_Reserved'))
ORDER BY T1C1 DESC

전제 조건

---

1. 서브쿼리의 테이블에서 출력되는 데이터는 테이블의 전체 건수와 동일합니다.

SELECT C3,
       count(*) AS Cnt
  FROM dbo.T2 WITH(NOLOCK)
 GROUP BY C2

strstoragetable	Cnt
ABC	3872
ABC_Reserved	9

2. 테이블의 인덱스는 아래와 같이 걸려 있습니다.

sp_helpindex 'T2'

인덱스 종류	인덱스 순서
Non Clustered Index	C3, C2
Clustered Index + PK	C2

sp_helpindex 'T1'

인덱스 종류	인덱스 순서
Non Clustered Index	C2, C1
Clustered Index + PK	C1

3. 부모 쿼리 테이블은 많은 건수를 가진 테이블 입니다.

sp_spaceused 'T1'

건수

1,219,880

실행계획 확인

---

1. 서브쿼리 선택도가 나쁜 쿼리 ( 위와 동일한 쿼리 )

SELECT TOP 20       T1C1  FROM dbo.T1 AS A WITH (NOLOCK) WHERE T1C2 IN (SELECT T2C2                             FROM dbo.T2 WITH (NOLOCK)                            WHERE T2C3 IN ('ABC', 'ABC_Reserved'))ORDER BY T1C1 DESC
 

* 실행계획 해석

1. 부모쿼리 T1 테이블의 T1C1 로 Scan 을 진행하면서 T1C2 값을 출력합니다.

2. 출력된 T1C2 값이 서브쿼리 T2 테이블의 T2C2 값과 매칭되는게 있는지 확인합니다.

3. 매칭이 되면 T1 테이블의 T1C1 을 출력합니다.

4. 위의 단계를 T1C1 가 20 개가 출력될 때까지 계속합니다.

- 왜 이렇게 되었을까?

옵티마이저가 서브쿼리부터 접근한다고 가정해 봅시다.

1. 서브쿼리의 선택도는 매우 낮습니다. 즉, 테이블의 모든 건수를 다 출력해야 합니다.

2. 다 출력된 데이터를 부모 테이블의 T1C2 와 다 비교해야 합니다.

3. 이렇게 비교된 데이터를 다시한번 T1C1 로 정렬해야 합니다.

옵티마이저는 계획 유추

서브쿼리 선택도가 매우 낮기 때문에,

부모테이블의 Cluster Index + PK 로 구성된 T1C1 컬럼을 뒤에서부터 순차적으로 읽어가며 T1C2 값을 서브쿼리를 읽어서 나온 값과 비교하는게 더 좋다고 판단했기 때문일 것입니다.

2. 서브쿼리 선택도가 좋은 쿼리 ( 위 쿼리에서 선택도를 좋게 변경 )

서브쿼리에서 출력되는 데이터의 선택도를 좋게 변경해 보았습니다.

서브쿼리 T2 테이블의 T2C3 는 PK 입니다.

SELECT TOP 20
       T1C1
  FROM dbo.T1 AS A WITH (NOLOCK)
 WHERE T1C2 IN (SELECT T2C2
                             FROM dbo.T2 WITH (NOLOCK)
                            WHERE T2C3 IN (31, 229, 249, 615, 11880)
ORDER BY T1C1 DESC
 

* 실행계획 해석

1. 서브쿼리 T2 테이블의 T2C3 값을 가져옵니다.

2. 가져온 T2C3 값을 부모쿼리 T1 테이블의 T1C2 값 Merge Join 으로 비교합니다.

4. 비교를 완료한 뒤에 나온 T1C1 를 정렬하여 20 개를 출력합니다.

옵티마이저는 서브쿼리의 T1C2 로 나오는 값이 5개라는 걸 알기 때문에,

서브쿼리를 먼저 읽고 나온 값으로 비교를 진행하였기 때문입니다.

참고로 force order 옵션을 부여하여 쿼리 실행계획을 부모부터 읽을 수 있도록 강제할 수 있습니다.

SELECT TOP 20
       T1C1
  FROM dbo.T1 AS A WITH (NOLOCK)
 WHERE T1C2 IN (SELECT T2C2
                             FROM dbo.T2 WITH (NOLOCK)
                            WHERE T2C3 IN (31, 229, 249, 615, 11880)
ORDER BY T1C1 DESC
OPTION (FORCE ORDER)
 

결론

---

서브쿼리를 사용한 경우 서브쿼리 선택도에 따라 쿼리를 읽는 순서가 달라지게 됩니다.

쿼리를 구성할 때 읽는 순서를 결정지을 수 없기 때문에, 이후에 선택도가 달라져 장애가 발생할 수도 있게 됩니다.

따라서 IN 절을 사용할 때는 힌트를 주어서 쿼리순서를 강제하도록 진행하거나

IN 절 대신 JOIN 방식으로 변경하여 순서를 지정하는 것도 좋은 방법이 될 것 같습니다.

-- IN 서브쿼리
SELECT TOP 20
       T1C1
  FROM dbo.T1 AS A WITH (NOLOCK)
 WHERE T1C2 IN (SELECT T2C2
                             FROM dbo.T2 WITH (NOLOCK)
                            WHERE T2C3 IN ('ABC', 'ABC_Reserved'))
ORDER BY T1C1 DESC
 
-- JOIN 
SELECT TOP 20
       A.T1C1
  FROM dbo.T1 AS A WITH (NOLOCK)
       INNER JOIN
       dbo.T2 AS B WITH(NOLOCK)
    ON A.T1C2 = B.T2C2
   AND B.T2C3 IN ('ABC', 'ABC_Reserved'))
ORDER BY A.T1C1 DESC