친절한 SQL 튜닝_5장.소트 튜닝_3

 

소트 튜닝

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

 

 

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

 

 

5.3.1 Sort Order By 생략

 

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

 

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

 

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

 

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

 

소트 연산을 생략함으로써 종목코드 = ‘KR123456’ 조건을 만족하는 전체 레코드를 읽지 않고도 바로 결과집합 출력을 시작할 수 있게 되었다. 즉, 부분범위 처리 가능한 상태가 되었다. 이 원리를 잘 활용하면, 소트해야 할 대상 레코드가 무수히 많은 상황에서 극적인 성능 개선 효과를 얻을 수 있다.

 

 

 

5.3.2 Top N 쿼리

 

우선 Top N 쿼리가 무엇인지부터 살펴보자. Top N 쿼리는 전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리다. SQL Server나 Sybase는 Top N 쿼리를 아래와 같이 손쉽게 작성할 수 있다.

 

SELECT TOP 10 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목거래 = ‘KR123456’
   AND 거래일시 >= ‘20180304’
 ORDER BY 거래일시

 

 

 

IBM DB2는 아래와 같은 Row Limiting 절을 제공한다.

 

SELECT 거래일시, 체결건수, 체결수량, 거래대금
  FROM 종목거래
 WHERE 종목거래 = ‘KR123456’
   AND 거래일시 >= ‘20180304’
 ORDER BY 거래일시
 FETCH RISRT 10 ROWS ONLY

 

 

오라클에서는 아래처럼 인라인 뷰로 한 번 감싸야 하는 불편함이 있다.

 

 

SELECT *
  FROM ( SELECT 거래일시, 체결건수, 체결수량, 거래대금
           FROM 종목거래
          WHERE 종목거래 = ‘KR123456’
            AND 거래일시 >= ‘20180304’
          ORDER BY 거래일시 )
 WHERE ROWNUM <= 10

SQL 형태만 놓고 보면, 인라인 뷰로 정의한 집합을 모두 읽어 거래일시 순으로 정렬한 중간 집합을 우선 만들고, 거기서 상위 열 개 레코드를 취하는 형태다. 소트를 생략할 수 있도록 인덱스를 구성해 주더라도 중간집합을 만들어야 하므로 부분범위 처리는 불가능해 보인다.

 

하지만, 위 쿼리에 『종목코드 + 거래일시』 순으로 구성된 인덱스를 이용하면, 옵티마이저는 소트 연산을 생략하며, 인덱스를 스캔하다가 열 개 레코드를 읽는 순간 바로 멈춘다.

 

아래 실행계획을 보면, Sort Order By 오퍼레이션이 보이지 않는다. 대신 ‘COUNT (STOPKEY)’가 눈에 뛴다. 이는 조건절에 부합하는 레코드가 아무리 많아도 그 중 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 거기서 바로 멈춘다는 뜻이다.

 

Execution Plan

 

0                         SELECT STATEMENT Optimizer=ALL_ROWS

 

1            0            COUNT(STOPKEY)

 

2            1              VIEW

 

3            2               TABLE ACCESS (BY INDEX ROWID) OF ‘종목거래’ (TABLE)

 

4            3                INDEX (RANGE SCAN) OF ‘종목거래_PK’ (INDEX (UNIQUE))

 

 

 

페이징 처리

 

3-Tier 환경에서는 대량의 결과집합을 조회할 때 페이징 처리 기법을 활용한다. 일반적으로 사용하는 표준 패턴은 아래와 같다.

 

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

 

Top N 쿼리이므로 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 거기서 바로 멈춘다. 뒤쪽 페이지로 이동할수록 읽는 데이터량도 많아지는 단점이 있지만, 보통 앞쪽 일부 데이터만 확인하므로 문제가 되지 않는다.

 

3-Tier 환경에서 부분범위 처리를 활용하기 위해 우리가 할 일은 다음과 같다.

 

1. 부분범위 처리 가능하도록 SQL을 작성한다. 부분범위 처리가 잘 작동하는지 토드, 오렌지 같은 쿼리 툴에서 테스트한다.

 

2. 작성한 SQL문을 페이징 처리용 표준 패턴 SQL Body 부분에 붙여 넣는다.

 

‘부분범위 처리 가능하도록 SQL을 작성한다’는 의미는 무엇일까? 인덱스 사용 가능하도록 조건절을 구사하고, 조인은 NL 조인 위주로 처리(룩업을 위한 작은 테이블은 해시 조인 Build Input으로 처리해도 됨)하고, Order By 절이 있어도 소트 연산을 생략할 수 있도록 인덱스를 구성해 주는 것을 의미한다.

 

 

 

아래는 완성된 페이징 처리 SQL이다. 실행계획에 소트 연산이 없고 세 번째 라인(ID=2) Count 옆에 Stopkey 라고 표시된 부분을 주목하기 바란다.

 

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

 

Execution Plan

 

0                         SELECT STATEMENT Optimizer=ALL_ROWS

 

1            0            VIEW

 

2            1              COUNT(STOPKEY) -> NO STOP + STOPKEY

 

3            2               VIEW

 

4            3                TABLE ACCESS (BY INDEX ROWID) OF ‘종목거래’ (TABLE)

 

5            4                 INDEX (RANGE SCAN) OF ‘종목거래_PK’ (INDEX)

 

5.3.3 최소값/최대값 구하기

최소값(MIN) 또는 최대값(MAX)을 구하는 SQL 실행계획을 보면, 아래와 같이 Sort Aggregate 오퍼레이션이 나타난다. Sort Aggregate를 위해 전체 데이터를 정렬하지 않지만, 전체 데이터를 읽으면서 값을 비교한다고 앞에서 설명하였다.

 

SELECT MAX(SAL) FROM EMP;

Execution Plan

0                         SELECT STATEMENT Optimizer==ALL_ROWS

1            0            SORT (AGGREGATE)

2            1              TABLE ACCESS (FULL) OF ‘EMP’ (TABLE)

인덱스는 정렬돼 있으므로 이를 이용하면 전체 데이터를 읽지 않고도 최소 또는 최대값을 쉽게 찾을 수 있다. 인덱스 맨 왼쪽으로 내려가서 첫 번째 읽는 값이 최소값이고, 맨 오른쪽으로 내려가서 첫 번째 읽는 값이 최대값이다. 아래는 인덱스를 이용해 최대값을 찾을 때의 실행계획이다.

 

CREATE INDEX EMP_X1 ON EMP(SAL);
 
SELECT MAX(SAL) FROM EMP;

Execution Plan

0                         SELECT STATEMENT Optimizer==ALL_ROWS

1            0            SORT (AGGREGATE)

2            1              INDEX (FULL SCAN (MIN/MAX)) OF ‘EMP_X1’ (INDEX)

참고로, 위와 같은 실행계획이 나타나기 시작한 것은 오라클 8i부터다. 8 버전까지는 인덱스가 있어도 아래와 같이 전체 데이터를 읽었다.

Execution Plan

0                         SELECT STATEMENT Optimizer==ALL_ROWS

1            0            SORT (AGGREGATE)

2            1              INDEX (FULL SCAN) OF ‘EMP_X1’ (INDEX)

 

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

전체 데이터를 읽지 않고 인덱스를 이용해 최소 또는 최대값을 구하려면, 조건절 컬럼과 MIN/MAX 함수 인자 컬럼이 모두 인덱스에 포함돼 있어야 한다. 즉, 테이블 액세스가 발생하지 않아야 한다. 아래는 인덱스를 『DEPTNO + MGR + SAL』 순으로 구성한 경우다.

 

CREATE INDEX EMP_X1 ON EMP(DEPTNO, MGR, SAL);
 
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;

Execution Plan

0                         SELECT STATEMENT Optimizer==ALL_ROWS

1            0            SORT (AGGREGATE)

2            1              FIRST ROW

3            2               INDEX (RANGE SCAN(MIN/MAX)) OF ‘EMP_X1’ (INDEX)

조건절 컬럼과 MXA 컬럼이 모두 인덱스에 포함돼 있고, 인덱스 선두 컬럼 DEPTNO, MGR이 모두 ‘=’ 조건이므로 이 두 조건을 만족하는 범위(Range) 가장 오른쪽에 있는 값 하나를 읽는다. 실행계획 세 번째 라인(ID=2) ‘FIRST ROW’는 조건을 만족하는 레코드 하나를 찾았을 때 바로 멈춘다는 것을 의미한다. 지금부터 이를 ‘First Row Stopkey’ 알고리즘이라고 부르기로 하자.

 

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

Top N 쿼리를 통해서도 최소 또는 최대값을 쉽게 구할 수 있다. 아래와 같이 ROWNUM <=1 조건을 이용해 Top 1 레코드를 찾으면 도니다.

 

CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL);
 
SELECT *
  FROM ( 
         SELECT SAL
           FROM EMP
          WHERE DEPTNO = 30
            AND MGR = 7698
          ORDER BY SAL DESC 
        )
 WHERE ROWNUM <= 1;

Execution Plan

0                         SELECT STATEMENT Optimizer==ALL_ROWS

1            0            COUNT (STOPKEY)

2            1              VIEW

3            2               TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’ (TABLE)

4            3                INDEX (RANGE SCAN DESCENDING) OF ‘EMP_X1’ (INDEX))

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

인라인 뷰를 사용하므로 쿼리가 약간 더 복잡하긴 하지만, 성능 측면에서는 MIN/MXA 쿼리보다 낫다.

 

5.3.4 이력 조회

가장 단순한 이력 조회

이력 데이터 조회할 때 ‘First Row Stopkey’ 또는 ‘Top N Stopkey’ 알고리즘이 작동할 수 있게 인덱스 설계 및 SQL 구현하는 일은 고급 SQL 튜너가 되기 위해 반드시 정복해야 할 산이다. 가장 단순한 조회 패턴부터 살펴보자.

아래는 장비구분코드가 ‘A001’인 장비 목록을 조회하는 쿼리다. 상태코드가 현재 값으로 변경된 날짜(최종 변경일자)는 상태변경이력에서 조회하고 있다.

 

SELECT 장비번호, 장비명, 상태코드
     , ( SELECT MAX(변경일자)
           FROM 상태변경이력
           WHERE 장비번호 = P.장비번호 ) 최종변경일자
  FROM 장비 P
 WHERE 장비구분코드 = ‘A001’

ID          Operation                                       Name                  Starts      A-Rows   Buffers

0            SELECT STATEMENT                                                     1            10          4

1            SORT AGGREMENT                                                    10          10          22

2              FIRST ROW                                                             10          10          22

3               INDEX RANGE SCAN (MIN/MAX)   상태변경이력_PX    10          10          22

4            TABLE ACCESS BY INDEX ROWID         장비                     1            10          4

5            INDEX RANGE SCAN                       장비_N1                1            10          2

위 SQL 문에서 이력 조회하는 스칼라 서브쿼리 부분에 ‘First Row Stopkey’ 알고리즘이 작동하고 있다. 상태변경이력 _PK 인덱스가 『장비번호 + 변경일자 + 변경순번』 순으로 구성돼 있기 때문에 가능한 일이다.

 

5.3.5 Sort Grop By 생략

인덱스를 이용해 소트 연산을 생략할 수 있다는 사실은 많이 알려졌고 이해하기도 비교적 쉽다. 반면, 그룹핑 연산에도 인덱스를 활용할 수 있다는 사실은 모르는 분이 많다.

아래 SQL에 region이 선두 컬럼인 인덱스를 이용하면, Sort Group By 연산을 생략할 수 있다. 실행계획에 ‘Sort Group By Nosort’라고 표시된 부분을 확인하기 바란다.

 

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

Id           Operation                                       Name                  Rows      Bytes      Cost(%CPU)

0            SELECT STATEMENT                                                    25          725         30142(1)

1            SORT GROUP BY NOSORT                                          25          725         30142(1)

2              TABLE ACCESS BY INDEX ROWID    CUSTOMER           1000K     27M       30142(1)

 

3               INDEX FULL SCAN                        CUSTOMER_X01     1000K                   2337(2)

출처 : 친절한 SQL 튜닝 - 조시형 지음