[친절한 SQL 튜닝] 5. 소트 튜닝 [3]

 

5.3 인덱스를 이용한 소트 연산 생략

• 인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지
• 이를 활용하면 SQL에 Order By 또는 Group By 절이 있어도 소트 연산 생략 가능
• 더하여 Top N 쿼리 특성을 결합하면, 온라인 트랜잭션 처리 시스템에서 대량 데이터를 조회할 때 매우 빠른 응답 속도를 낼 수 있음
• 또한 특정 조건을 만족하는 최소값 또는 최대값도 빨리 찾을 수 있어 이력 데이터를 조회할 때 매우 유용

 

5.3.1 Sort Order By 생략

 

인덱스 선두 컬럼을 [종목코드+거래일시] 순으로 구성하지 않으면 아래 SQL에서는 소트 연산을 생략할 수 없다.

 

SELECT 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목코드 = 'KR123456'
 ORDER BY 거래일시

 

위 SQL은 종목코드 = 'KR123456' 조건을 만족하는 레코드를 인덱스에서 모두 읽어야 하고, 그만큼 많은 테이블 랜덤 액세스가 발생한다. 모든 데이터를 다 읽어 거래일시 순으로 정렬을 마치고 나서 출력을 시작하므로 OLTP 환경에서 빠른 응답 속도를 내기 어렵다.

 

하지만, 인덱스 선두 컬럼을 [종목코드+거래일시] 순으로 구성한다면 소트 연산을 생략할 수 있다.

소트 연산을 생략함으로써 종목코드 = 'KR123456' 조건을 만족하는 전체 레코드를 읽지 않고도 바로 결과집합을 출력할 수 있다.

즉, 부분범위 처리 가능한 상태가 되었다.

 

소트 연산을 생략한다면 레코드가 무수히 많은 상황에서 성능 개선 효과를 얻을 수 있다.

 

 

부분범위 처리

부분범위 처리는 쿼리 수행 결과 중 앞쪽 일부를 우선 전송하고 멈추었다가 클라이언트가 추가 전송을 요청(Fetch Call)할 때마다 남은 데이터를 조금씩 나눠 전송하는 방식을 말한다.

 

5.3.2 Top N 쿼리

Top N 쿼리는 전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리이다.

-- SQL Server나 Sybase에서 사용
SELECT TOP 10 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목코드 = 'KR123456'
   AND 거래일시 >= '20180304'
 ORDER BY 거래일시
 
 -- 오라클 사용(인라인 뷰로 한 번 감싸야 함)
 SELECT *
   FROM (SELECT 거래일시, 체결건수, 체결수량, 거래대금
           FROM 종목거래
          WHERE 종목코드 = 'KR123456'
            AND 거래일시 >= '20180304'
          ORDER BY 거래일시
        )
  WHERE ROWNUM <= 10

 

위 SQL에서 [종목코드+거래일시] 순으로 구성된 인덱스를 이용하면, 옵티마이저는 소트 연산을 생략하며, 열 개 레코드를 읽는 순간 바로 멈추게 된다. [아래 그림 참고]

 

 

실행계획에 COUNT(STOPKEY)로 표기되며 Top N Stopkey 알고리즘으로도 불린다.

 

* COUNT(STOPKEY) : 조건절에 부합하는 레코드가 아무리 많아도 그 중 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 바로 멈춤

 

 

페이징 처리

3-Tier 환경에서는 대량의 결과집합을 조회할 때 페이징 처리 기법을 활용한다.

 

SELECT *
  FROM (SELECT ROWNUM NO, A.*
          FROM (
          		/* SQL Body */
               ) A
         WHERE ROWNUM <= (:page * 10)
       )
 WHERE NO >= (:page-1)*10+1

 

뒤쪽 페이지로 이동할수록 읽는 데이터량도 많아지는 단점이 있지만, 보통 앞쪽 일부 데이터만 확인하므로 문제가 되지 않는다.

 

예를 들어, 인터넷 카페 게시글 목록을 조회하거나 은행 사이트에서 입출금 내역을 조회할 때 일반적으로 앞쪽 페이지만 확인한다.

 

 

○ 3 Tier 환경에서 부분범위 처리를 활용하기 위해 할 일

• 부분범위 처리 가능하도록 SQL을 작성한다. 부분범위 처리가 잘 작동하는지 토드, 오렌지 같은 쿼리 툴에서 테스트한다.
• 작성한 SQL 문을 페이징 처리용 표준 패턴 SQL Body 부분에 붙여 넣는다.

* "부분범위 처리 가능하도록 SQL을 작성한다"란 인덱스 사용 가능하도록 조건절을 구사하고, 조인은 NL 조인 위주로 처리하고, Order By 절이 있어도 소트 연산을 생략할 수 있도록 인덱스를 구성해 주는 것

 

 

페이징 처리 ANTI 패턴

SELECT *
 FROM (SELECT ROWNUM NO, A.*
         FROM (SELECT 거래일시, 체결건수, 체결수량, 거래대금
                 FROM 종목거래
                WHERE 종목코드 = 'KR123456'
                  AND 거래일시 >= '20180304'
                ORDER BY 거래일시
              ) A
       )
WHERE NO BETWEEN (:page-1)*10 + 1 AND (:page*10)

 

위 SQL의 실행계획에서 StopKey 실행계획이 없어진다. 소트 생략 가능하도록 인덱스를 구성했으므로 소트 생략은 가능하지만, StopKey가 작동하지 않아 전체범위를 처리한다.

 

 

5.3.3 최소값/최대값 구하기

 

최소값(MIN) 또는 최대값(MAX)을 구하는 SQL 실행계획을 보면, Sort Aggregate 오퍼레이션이 나타난다.

(Sort Aggregate를 위해 전체 데이터를 정렬하진 않지만, 전체 데이터를 읽으면서 값을 비교)

 

인덱스는 정렬돼 있으므로 전체 데이터를 읽지 않고도 최소 또는 최대값을 쉽게 찾을 수 있다.

 

 

인덱스 이용해 최소/최대값 구하기 위한 조건

전체 데이터를 읽지 않고 인덱스를 이용해 최소 또는 최대값을 구하려면, 조건절 컬럼과 MIN/MAX 함수 인자 컬럼이 모두 인덱스에 포함돼 있어야 한다.(테이블 액세스가 발생하지 않아야 함)

 

CREATE INDEX EMP_X1 ON EMP(DEPTNO, MGR, SAL);

SELECT MAX(SAL)
  FROM EMP
 WHERE DEPTNO = 30
   AND MGR = 7698;

 

위 SQL에서 조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함돼 있고, 인덱스 선두 컬럼 DEPTNO, MGR이 모두 '=' 조건이므로 아래 그림에서 조건을 만족하는 범위 가장 오른쪽에 있는 값 하나를 얻는다.

조건을 만족하는 레코드 하나를 찾았을 때 바로 멈춘다는 의미로 "First Row StopKey 알고리즘"이라 부른다.

 

 

인덱스가 [DEPTNO+SAL+MGR] 순으로 구성되어 있다면, DEPTNO는 액세스 조건, MGR은 필터 조건이다.

조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함돼 있으므로 First Row StopKey 알고리즘이 작동한다.

 

다른 예로 인덱스를 [SAL+DEPTNO+MGR]로 구성한다면 조건절 컬럼이 둘 다 인덱스 선두 컬럼이 아니므로 Index Range Scan이 불가능하다. 이 경우엔 DEPTNO, MGR 모두 필터 조건이다. 이 경우도 조건절 컬럼과 MAX 컬럼이 인덱스에 포함돼 있으므로 First Row StopKey 알고리즘이 작동한다.

 

만약 조건절 컬럼과 MAX 컬럼 중 어느 하나가 인덱스에 포함돼 있지 않는 경우엔 전체 레코드를 읽어 MAX 값을 구한다. 이때는 First Row StopKey 알고리즘이 작동하지 않는다.

 

 

Top N 쿼리 이용해 최소/최대값 구하기

 

CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL);

-- ROWNUM을 사용해 TOP 1 레코드 찾기
SELECT *
  FROM (SELECT SAL
          FROM EMP
         WHERE DEPTNO = 30
           AND MGR = 7698
         ORDER BY SAL DESC
       )
 WHERE ROWNUM <= 1;

 

 

Top N StopKey 알고리즘은 모든 컬럼이 인덱스에 포함돼 있지 않아도 잘 작동한다. DEPTNO = 30인 조건을 만족하는 전체 레코드를 읽지 않고, 가장 오른쪽에서부터 역순으로 스캔하면서 테이블을 액세스 하다가 MGR = 7698 조건을 만족하는 레코드 하나를 찾았을 때 바로 멈춘다.

 

 

 

5.3.4 이력 조회

 

과거 이력을 조회할 필요가 있다면, 이력 테이블을 따로 관리해야 한다.

 

 

가장 단순한 이력 조회

이력 데이터를 조회할 때 "First Row StopKey" 또는 "Top N Stopkey" 알고리즘이 작동할 수 있게 인덱스 설계 및 SQL 구현하는 일이 필수이다.

 

이력 데이터 중 최신 데이터를 조회하는 방법

• Top N StopKey
• EXISTS
• INDEX_DESC 힌트+ROWNUM 사용

 

상황에 따라 달라져야 하는 이력 조회 패턴

전체 데이터 대상으로 조회할 땐 StopKey 기능을의 작동 여부는 튜닝의 핵심요소가 아니다.

인덱스 활용 패턴은 랜덤 I/O 발생량만큼 성능도 비례해서 느려지므로 대량 데이터를 조회할 때 좋은 솔루션이 되지 못한다.

 

전체 데이터의 이력을 조회할 때는 윈도우 함수를 이용하는 것이 효율적이다.

Full Scan과 해시 조인을 이용하기 때문에 오랜 과거 이력까지 모두 읽지만, 인덱스를 이용하는 방식보다 빠르다.

-- KEEP 절 활용 가능
MAX(변경순번) KEEP (DENSE_RANK LAST ORDER BY 변경일자, 변경순번) 변경순번
MAX(상태코드) KEEP (DENSE_RANK LAST ORDER BY 변경일자, 변경순번) 상태코드

 

 

5.3.5 Sort Group By 생략

 

그룹핑 연산에도 인덱스를 활용할 수 있다.

SELECT REGION, AVG(AGE), COUNT(*)
  FROM CUSTOMER
 GROUP BY REGION

 

위 SQL에서 REGION이 선두 컬럼인 인덱스를 이용하면, Sort Group By 연산을 생략할 수 있다.

실행계획에는 "Sort Group By Nosort"로 표시된다.

 

 

수행 순서(Array Size = 3)

• 인덱스에서 "A" 구간을 스캔하면서 테이블을 액세스 하다가 "B"를 만나는 순간, 그때까지 집계한 값을 운반단위에 저장
• 계속해서 "B" 구간을 스캔하다가 "C"를 만나는 순간, 그때까지 집계한 값을 운반단위에 저장
• 계속해서 "C" 구간을 스캔하다가 "D"를 만나는 순간 그때까지 집계한 값을 운반단위에 저장함. Array Size가 3이므로 , B, C에 대한 집계결과를 클라이언트에 전송하고 다음 Fetch Call이 올 때까지 기다림
• 다음 Fetch Call이 오면 위 과정을 반복함. 두 번째 Fetch Call은 "D"부터 시작

이처럼 인덱스를 이용해 NoSort 방식으로 Group By를 처리하면 부분범위 처리가 가능해진다.

 

 

---

5.4 Sort Area를 적게 사용하도록 SQL 작성

소트 연산이 불가피하다면 메모리 내에서 처리를 완료할 수 있도록 노력해야 한다. Sort Area 크기를 늘리는 방법도 있지만, Sort Area를 적게 사용할 방법부터 찾아야 한다.

 

5.4.1 소트 데이터 줄이기

---- 1번
SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 10)
      || LPAD(고객명, 20) || TO_CHAR(주문일시, 'YYYYMMDD HH24:MI:SS')
  FROM 주문상품
 WHERE 주문일시 BETWEEN :start and :end
 ORDER BY 상품번호
 
 
 ---- 2번
 SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 10)
      || LPAD(고객명, 20) || TO_CHAR(주문일시, 'YYYYMMDD HH24:MI:SS')
   FROM (SELECT 상품번호, 상품명, 고객ID, 고객명, 주문일시
           FROM 주문상품
          WHERE 주문일시 BETWEEN :start and :end
          ORDER BY 상품번호
        )

 

1번 SQL은 레코드당 107(30+30+10+20+17) 바이트로 가공한 결과집합을 Sort Area에 담는다. 반면 2번 SQL은 가공하지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공한다. 따라서 2번 SQL이 Sort Area를 훨씬 더 적게 사용한다.

 

또한 전체 컬럼을 조회하는 것보다 필요한 컬럼만 조회하는 것이 Sort Area를 더 적게 사용한다.

 

 

5.4.1 Top N 쿼리의 소트 부하 경감 원리

전교생 1,000명 중 가장 큰 학생 열 명을 선발하려고 할 때, 어떤 방법이 있을까?

• 전교생을 키 순서대로 정렬한 학생명부가 있다면 가장 위쪽에 있는 열 명을 선발하면 된다.(Top N StopKey 알고리즘)
• 학생명부가 없다면 1,000명을 나란히 줄 세운 후 비교하면서 세봐야 한다.

Top N Sort 알고리즘이 작동하면, 소트 연산(=값 비교) 횟수와 Sort Area 사용량을 최소화해 준다.

이 방식대로 처리하면 대상 집합이 아무리 커도 메모리 공간이 필요하지 않다.

 

하지만 Top N Sort 알고리즘이 작동하지 않다면 Physical Read, Physical Write가 발생한다.

 

5.4.4 분석함수에서의 Top N 소트

 

윈도우 함수 중 RANK나 ROW_NUMBER 함수는 MAX 함수보다 소트 부하가 적다.(Top N Sort 알고리즘이 작동하기 때문