[mssql]Top N 쿼리의 숨겨진 기능

정재우 | ㈜엔코아컨설팅 선임 컨설턴트

SQL 서버 2000에서 더욱 강화된 기능 가운데 하나가 바로 Top N 쿼리이다. 이미 알려진 대로 Top N 쿼리는 order by와 함께 사용할 경우 조건에 해당하는 데이터 중에서 상위 또는 하위의 일부분만을 추출할 수 있는 기능을 제공한다.
아마 이 기능을 가장 유용하게 사용할 수 있는 곳은 웹 게시판일 것이다. 강력한 Top N 쿼리의 기능을 활용하여 효율적인 웹 게시판을 구현하는 솔루션에 대한 내용은 다음호에서 소개하기로 하겠다.
SQL 서버 2000은 'Top N Engine'을 통해서 Top N 쿼리를 효율적으로 처리한다. 이 엔진의 개선된 알고리즘은 다음과 같다.
SQL 서버 7.0에서는 조건에 해당하는 데이터를 모두 읽어 들인 후 full sort 방식을 통해서 결과 집합을 추출했지만 SQL 서버 2000의 'Top N Engine'은 N개의 데이터를 임시로 저장할 수 있는 개체(여기서는 '버퍼'라는 용어로 대체)를 메모리에 생성한 후 이 버퍼에 저장된 가장 크거나 작은 값만을 비교 대상으로 처리한다.
여러분이 'select top 5 * from Northwind.dbo.[Order Details] order by Quantity desc'와 같은 SQL을 실행하면 다음의 알고리즘에 의해서 처리된다. 우선 5개의 데이터를 저장할 수 있는 버퍼를 생성한 후, [Order Details] 테이블의 데이터를 순차적으로 읽어 들여서 이 버퍼를 채우기 시작한다. 5개의 데이터가 버퍼에 채워지고 나면 5번째 이후에 읽혀진 데이터는 이 버퍼에 저장된 데이터의 가장 작은 Quantity 값과 비교된다. 만약 6번째로 읽어 들인 데이터의 Quantity 값이 버퍼의 최소 Quantity 값보다 크다면 이 6번째의 데이터가 버퍼에 존재하는 데이터(최소 Quantity 값을 가진)를 밀어내고 그 자리를 차지하게 된다.
Top N 쿼리는 결과 집합의 행 수를 제한하는 기본적인 기능 외에 옵티마이저의 실행 계획을 제어하거나 OR 연산자와 함께 사용시 STOP KEY의 역할 등 알려지지 않은 유용한 기능을 제공한다. 9월호에서 소개했던 '멀티 캐시의 효과'도 상관 하위쿼리에 'Top 1' 키워드를 기술하지 않으면 전혀 다른 실행 계획을 수립하게 된다.
간단한 예제를 통해서 'Top 1' 키워드가 옵티마이저의 실행 계획에 어떠한 영향을 미치는지 확인해 보자.

[리스트 1] 예제 수행을 위한 테이블 및 인덱스를 생성하는 스크립트 1
create table 지역 (
지역번호 varchar(6) not null,
지역명 varchar(30) not null,
지역설명 varchar(1000) null
)

create table 판매 (
판매번호 int identity(1,1) not null,
판매일자 varchar(8) not null,
판매지역 varchar(6) not null,
판매금액 numeric(12,0) not null,
비고 varchar(500) null
)

alter table 지역 add constraint pk_지역 primary key (지역번호)

create clustered index 판매_idx2 on 판매 (판매일자)
alter table 판매 add constraint pk_판매 primary key (판매번호)

[리스트 1]의 DDL을 살펴보자. '지역' 테이블과 '판매' 테이블이 존재하며 두 테이블은 1:M의 관계를 가지고 있다. '지역' 테이블의 데이터는 겨우 10 건에 불과하며, '판매' 테이블의 데이터는 매일 수천 건 이상 발생한다고 가정하자.
이런 상황에서 고객이 다음과 같은 요청을 한다면 고려해야 할 사항은 무엇일까?
"2003년 12월 1일부터 12월 10일 사이에 발생한 판매내역을 조회하고자 한다. 조회 시 각 판매지역에 해당하는 지역명도 같이 보여줘야 한다. 또한 '판매' 테이블의 '판매지역' 컬럼의 값이 프로그램의 버그로 인해서 가끔 공백이나 NULL 값이 들어가는 경우가 있으므로 이런 데이터는 지역명을 무시하고 '판매' 테이블의 데이터만 보여주면 된다."
'판매지역' 컬럼의 값이 '지역' 테이블에 존재하지 않더라도 '판매' 테이블의 데이터를 보여줘야 하므로 당연히 외부(Outer) 조인을 사용해야 한다. 물론 스칼라 서브쿼리를 사용해도 외부 조인과 동일한 결과를 얻을 수 있으며 내부적으로 Outer Join 방식으로 실행된다.
명시적인 외부 조인 대신 스칼라 서브쿼리를 사용하여 SQL을 작성해 보자. 필자가 작성한 SQL과 실행 계획은 [그림 1]과 같다.

[그림 1] 수정 전 실행 계획




[그림 1]의 실행 계획을 살펴보자. 밑에서 3번째 줄을 보면 Nested Loops(Left Outer Join, ...)로 기술된 부분이 있다. 괄호 안의 Left Outer Join 연산자는 이 SQL이 예상한대로 외부 조인 방식으로 수행되었음을 알려준다.
그런데 실행 계획을 보면 뭔가 아쉬운 부분이 있다. Outer 측의 '판매' 테이블에서 추출된 2000건의 데이터에 대해서 Inner 측의 '지역' 테이블이 에누리없이 2000번 액세스 되었다. 지난 회에 소개한 스칼라 서브쿼리에서의 '멀티 캐시의 효과'는 어디로 사라진 것일까?
바로 이 부분에서 Top 1 키워드가 옵티마이저의 실행 계획에 큰 영향을 미칠 수 있음을 소개하고자 한다. 스칼라 서브쿼리의 결과 집합은 항상 1건이어야 하므로 서브쿼리의 SQL에 'Top 1'을 추가해도 논리적으로 [그림 1]의 SQL과 동일하다. 수정 후 실행 결과는 [그림 2]와 같다.

[그림 2] 수정 후 실행 계획




[그림 2]의 실행 계획을 보면 '지역' 테이블의 스캔 수 및 논리적 읽기 수가 [그림 1]의 실행 계획에 비해서 100분의 1 이하로 줄어들었으며 실행 계획 자체도 달라졌음을 알 수 있다. [그림 2] 실행계획의 밑에서 4번째 줄에 '멀티 캐시의 효과'가 있었음을 나타내는 Hash Match(Cache...) 연산자가 추가되었다.
이상의 테스트를 통해서 Top N 쿼리가 옵티마이저의 실행 계획에 큰 영향을 미칠 수 있음을 확인하였다. 이번에는 Top N 쿼리를 OR 연산자와 함께 사용할 경우 옵티마이저가 얼마나 효율적인 실행 계획을 만드는지 알아보자. 참고로, OR 연산자는 대상 집합을 줄이는 것이 아니라 더 확장시키므로 옵티마이저가 최적의 실행 계획을 세우지 못하면 예상치 못한 비효율이 발생할 수 있다.

[리스트 2] 예제 수행을 위한 테이블 및 인덱스를 생성하는 스크립트 2
create table 게시판 (
bbs_id smallint not null,
글번호 int not null,
제목 varchar(200) not null,
작성자번호 int null,
조회수 int null,
작성일자 varchar(8) not null,
내용 varchar(2000) null
)

alter table 게시판 add constraint pk_게시판
　　　　　　　　　　primary key nonclustered(글번호)
create unique clustered index 게시판_uk on 게시판(bbs_id, 작성일자, 글번호)

[리스트 2]의 DDL을 살펴보자. 공지 사항이나 뉴스 등을 게시하는 간단한 형태의 게시판이다. 이 경우 각 게시판 별로 가장 최근에 작성된 글 순서대로 20 건씩 조회하고자 하는 경우 어떻게 SQL을 작성해야 할까? 단, 화면에는 이전(◀) 및 다음(▶) 버튼만 존재한다.
다양한 아이디어가 있겠지만 필자는 [리스트 3]과 같은 형태로 작성하였다. [리스트 3]의 SQL에서 사용된 4개의 변수 값은 실제로는 클라이언트 프로그램에서 넘겨주는 값이다. 지면 관계상 이전(◀) 버튼을 클릭하는 경우는 생략하고 다음(▶) 버튼을 클릭하는 경우에 대해서만 설명하도록 하겠다.

[리스트 3] 웹 게시판의 다음 버튼 클릭시 작동하는 SQL
declare @조회구분 varchar(4), @bbs_id smallint,
@작성일자 varchar(8), @글번호 int

set @조회구분 = 'NEXT'
set @bbs_id = 1
set @작성일자 = '20040630'
set @글번호 = 2000

SELECT TOP 21 BBS_ID, 작성일자, 글번호, 내용
FROM 게시판 WITH(INDEX(게시판_UK))
WHERE @조회구분 = 'NEXT'
AND BBS_ID = @BBS_ID
AND ((작성일자 < @작성일자)
OR (작성일자 = @작성일자 AND 글번호 <= @글번호))
ORDER BY 작성일자 DESC, 글번호 DESC

[리스트 3]의 SQL을 보면 'TOP 21' 구문을 사용하여 데이터를 21건씩 조회하고 있다. 이것은 21번째의 데이터가 존재하지 않으면 다음 페이지의 데이터가 없다는 의미이므로 다음(▶) 버튼을 비활성화하기 위함이다. 또한 가장 최근에 작성된 글 순서대로 조회해야 하므로 'ORDER BY 작성일자 DESC, 글번호 DESC' 구문을 사용하였다.
이 SQL의 실행 계획은 [그림 3]과 같다.

[그림 3] 다음(▶) 버튼 클릭시 SQL의 실행 계획




[그림 3]의 실행 계획을 보면 '게시판' 테이블의 스캔 수가 2이다. 이것은 WHERE 조건의OR 연산자에 의해서 내부적으로 두 개의 조건이 분리되어 실행되었음을 의미한다. 즉, (작성일자 < @작성일자) 조건과 (작성일자 = @작성일자 AND 글번호 <= @글번호) 조건이 따로 분리되어 실행된 것이다.
[리스트 3]의 SQL을 논리적으로 풀어서 재작성하면 [리스트 4]의 SQL과 논리적으로 동일하다.

[리스트 4] 재작성한 SQL
SELECT TOP 21 B.BBS_ID, B.작성일자, B.글번호, B.내용
FROM (SELECT BBS_ID, 작성일자, 글번호, 내용
FROM (SELECT TOP 21 BBS_ID, 작성일자, 글번호, 내용
FROM 게시판 WITH(INDEX(게시판_UK))
WHERE @조회구분 = 'NEXT'
AND BBS_ID = @BBS_ID
AND ((작성일자 = @작성일자 AND 글번호 <= @글번호))
ORDER BY 작성일자 DESC, 글번호 DESC
) A
UNION ALL
SELECT BBS_ID, 작성일자, 글번호, 내용
FROM (SELECT TOP 21 BBS_ID, 작성일자, 글번호, 내용
FROM 게시판 WITH(INDEX(게시판_UK))
WHERE @조회구분 = 'NEXT'
AND BBS_ID = @BBS_ID
AND ((작성일자 < @작성일자))
ORDER BY 작성일자 DESC, 글번호 DESC
) A
) B
ORDER BY 작성일자 DESC, 글번호 DESC

[리스트 4]의 SQL을 보면 약간 의아한 생각이 들 수도 있다. 인라인 뷰(파생 테이블)를 살펴 보면 서로 배타적인 조건으로 21 건씩을 추출한 후 UNION ALL을 통해서 두 결과 집합을 결합시킨다. 잘못하면 불필요하게 42 건의 중간 집합을 만든 후 절반을 버리는 비효율이 발생하지 않을까?
이 질문에 대한 대답은 [그림 4]의 실행 계획에 나와 있다.

[그림 4] 재작성한 SQL의 실행 계획




[그림 4]의 실행 계획을 살펴보자. 우려했던 것처럼 불필요한 데이터를 읽어 들이는 비효율은 발생하지 않았다. 실행 계획의 Rows를 보면 UNION ALL의 상위에서 10건이 추출되고 하위에서 11 건이 추출되었음을 알 수 있다. 기특하게도 옵티마이저는 UNION ALL의 위쪽 SQL에서 추출한 건수를 제외한 나머지 개수의 데이터만을 아래쪽 SQL에서 추출하였다. 즉, Top N 쿼리는 STOP KEY의 역할까지 훌륭하게 수행한 것이다.
이러한 Top N 쿼리의 기능을 잘 활용하면 웹 게시판을 효율적으로 구현할 수 있다. 이전 및 다음 버튼만 존재하는 간단한 게시판에서부터 응답글이 존재하는 복잡한 게시판에 이르기까지 다양한 형태의 응용이 가능하다.
지금까지 두 개의 예제를 통해서 Top N 쿼리의 숨겨진 유용한 기능에 대해서 알아보았다.
첫 번째는 스칼라 서브쿼리에 'Top 1' 구문을 추가하여 실행 계획을 변경하는 방법에 대해서 설명했다. 'Top 1' 구문을 잘 활용하면 '멀티 캐시의 효과'를 볼 수 있음을 기억하기 바란다.
두 번째는 Top N 쿼리가 STOP KEY의 기능을 포함하고 있음을 예제를 통해 설명했다. 이 기능은 효율적인 웹 게시판의 구현을 위한 필수 요소이므로 정확히 이해하기 바란다.
다음 호에서에는 Top N 쿼리의 강력한 기능을 활용하여 웹 게시판의 어느 페이지로 이동하더라도 동일한 조회 속도를 보장받을 수 있으며 불필요한 데이터는 전혀 액세스하지 않는 효율적인 웹 게시판 솔루션에 대해서 설명하도록 하겠다.

제공 : DB포탈사이트 DBguide.net

[출처] [mssql]Top N 쿼리의 숨겨진 기능|작성자 필립박