[SQL 레벨업] 5장: 반복문

반복문 의존

1. 내부적으로 반복문 사용

반복문에 익숙한 개발자들은 다음의 예시와 같이 레코드에 하나씩 접근하고 호스트 언어(절차형 언어)에서 반복 처리를 구현하는 경우가 간혹 있음

• 온라인 처리에서 화면에 명세를 출력하조가 레코드 하나씩 접근하는 SELECT 구문 반복 사용
• 배치 처리에서 대량의 데이터 처리할 때 레코드를 하나씩 호스트 언어에서 처리하고 테이블에 갱신

반복계의 공포

1. 반복문에 의존한 코드의 단점

- Sales: 매출 계산하는 테이블

- Sales2: 매출 변화를 나타내는 var 필드를 추가한 테이블

 

- 처리 시간 비교

반복문을 사용할 경우 처리 시간: (처리 횟수) * (한 회에 걸리는 처리 시간)

반복문을 사용하지 않을 경우 처리 시간: SQL 패턴은 다양하지만 인덱스를 사용하고 실행 계획의 변동이 없다면 대부분 완만한 커브형으로 증가할 것

 

SQL 실행의 오버헤드

전처리

1. SQL 구문을 네트워크로 전송

2. 데이터베이스 연결

3. SQL 구문 Parse

4 .SQL 구문의 실행 계획 생성 또는 평가

 

후처리

5. 결과 집합을 네트워크로 전송

 

1, 5번의 경우 일반적으로 같은 데이터센터 내부의 동일 LAN 위에 있으므로 오버헤드가 크게 발생하지 않음

2번의 경우 커넥션 풀을 사용해 미리 애플리케이션에서 연결을 일정 수 확보해 오버헤드 크지 않음

3번의 경우 SQL Parse(구문분석) 할 때 DBMS마다 방법과 종류가 달라 오버헤드가 큼, 데이터베이스가 SQL문을 받을 때마다 실행되어 작은 SQL 문을 여러번 반복할 경우 오버헤드 커짐

병렬 분산이 힘들다

반복 1회마다 처리를 단순화하여 병렬 처리로 최적화 불가능

데이터베이스의 서버 저장소는 대부분 I/O 부하를 분산할 수 있게 되어있으나 반복문에서 실행되는 SQL 구문은 단순하기 때문에 회당 접근하는 데이터 양이 적어 병렬화 하기 힘듦

즉, 리소스 사용 효율이 안좋음

데이터베이스의 진화로 인한 혜택 못받음

대규모 데이터를 다루는 복잡한 SQL 구문을 빠르게 하도록 데이터베이스가 개선되고 있으므로 단순 처리의 반복은 데이터베이스의 장점을 활용할 수 없음

 

반복문 대신 복잡한 SQL문을 사용하면서 데이터베이스를 튜닝한다면 반복문+단순 SQL문 조합보다 성능이 개선될 수 있다.

2. 반복계를 빠르게 만드는 방법

반복문+단순 SQL문 -> 복잡한 SQL문

실제 상황에서는 사용할 수 없는 경우가 많음

각 SQL문을 빠르게 수정

대부분 unique scan, index range scan정도의 간단한 구문이나 INSERT 구문이므로 수정한다고 해도 튜닝, 고속화 가능성이 제한됨

다중화 처리

가장 희망적인 선택지

처음부터 다중도를 설정할 수 있게 애플리케이션을 구성하였다면 코드 변경 없이 확장 가능

순서가 중요하거나 병렬화했을 때 물리적 자원이 부족하면 사용 불가

 

 

수백~수천만번의 반복이 기본인 일괄 처리에서는 반복문 사용을 지양하자

3. 반복계의 장점

실행 계획의 안정성

실행 계획이 단순하여 변동 위험이 없음

SQL 구문 내부에서 결합을 사용하지 않아도 됨 -> 결합 알고리즘의 변경이 없어 실행 계획 변동 적음

예상 처리 시간의 정밀도

실행 계획이 단순하고 성능이 안정적이므로 예상 처리 시간의 정밀도가 높아짐

반복문을 사용하지 않을 경우 실행 계획에 따라 성능이 천차만별이므로 예상하기 어려움

트랜잭션 제어가 편리

트랜잭션의 정밀도 제어 가능

갱신 처리나 특정 이유로 배치를 잠시 중단해야 할 때 해당 지점 근처에서 다시 처리를 실행할 수 있음

SQL에서 반복 표현하는 방법

1. CASE 식과 윈도우 함수

INSERT INTO Sales2
SELECT company, year, sale,
       CASE SIGN(sale-MAX(sale) OVER (PARTITION BY company
                                      ORDER BY year
                                      ROWS BETWEEN 1 PRECEDING
                                               AND 1 PRECEDING) )
       WHEN 0 THEN '='
       WHEN 1 THEN '+'
       WHEN -1 THEN '-'
       ELSE NULL END AS var
FROM Sales;

SIGN 함수: 숫자 자료형이 음수이면 -1, 양수이면 1, 0이면 0 반환

ROWS BETWEEN (개수) PRECEDING AND (개수) PRECEDING: 현재 레코드에서 1개 이전부터 1개이전까지의 레코드 범위

현재 레코드의 sale - 1개 이전의 sale(max 함수 처리로  같은 회사의 직전 매상을 가져옴)

 

상관 서브쿼리: 서브쿼리 내부에서 외부 쿼리와의 결합 조건을 사용하고, 해당 결합 키로 잘라진 부분 집합을 조작하는 기술
직전, 직후를 구할 때 MAX 함수를 사용하지만 실행 계획 복잡해 성능적 리스크 발생, 2~3번째 전후를 구하기도 어려움

SELECT company, year, sale,
       (SELECT company FROM Sales S2
              WHERE S1.company=S2.company
              AND year=(SELECT MAX(year)
                        FROM Sales S3
                        WHERE S1.company=S3.company
                        AND S1.year>S3.year)) AS pre_company,
       (SELECT sale FROM Sales S2
              WHERE S1.company=S2.company
              AND year=(SELECT MAX(year)
                        FROM Sales S3
                        WHERE S1.company=S3.company
                        AND S1.year>S3.year)) AS pre_sale
FROM Sales S1;

2. 최대 반복 횟수가 정해진 경우

우편번호 '4130033'과 가장 가까운 우편번호를 찾아 출력하는 문제

 

순위 붙이기 문제로 풀기

SELECT pcode, district_name
FROM PostalCode
WHERE CASE WHEN pcode='4130033' THEN 0
        WHEN pcode LIKE '413003%' THEN 1
        WHEN pcode LIKE '41300%' THEN 2
        WHEN pcode LIKE '4130%' THEN 3
        WHEN pcode LIKE '413%' THEN 4
        WHEN pcode LIKE '41%' THEN 5
        WHEN pcode LIKE '4%' THEN 6
        ELSE NULL END = 
             (SELECT MIN(CASE WHEN pcode='4130033' THEN 0
                              WHEN pcode LIKE '413003%' THEN 1
                              WHEN pcode LIKE '41300%' THEN 2
                              WHEN pcode LIKE '4130%' THEN 3
                              WHEN pcode LIKE '413%' THEN 4
                              WHEN pcode LIKE '41%' THEN 5
                              WHEN pcode LIKE '4%' THEN 6
                              ELSE NULL END)
              FROM PostalCode);

순위가 가장 높은(RANK값이 작은) 우편번호를 선택

윈도우 함수를 사용한 스캔 횟수 감소

순위의 최솟값을 서브쿼리에서 찾는 기존 방식 -> 윈도우 함수 처리하여 테이블 스캔 횟수 줄임

SELECT pcode, district_name,
FROM (SELECT pcode, district_name, 
             CASE WHEN pcode='4130033' THEN 0
                  WHEN pcode LIKE '413003%' THEN 1
                  WHEN pcode LIKE '41300%' THEN 2
                  WHEN pcode LIKE '4130%' THEN 3
                  WHEN pcode LIKE '413%' THEN 4
                  WHEN pcode LIKE '41%' THEN 5
                  WHEN pcode LIKE '4%' THEN 6
                  ELSE NULL END AS hit_code,
             MIN(CASE WHEN pcode='4130033' THEN 0
                      WHEN pcode LIKE '413003%' THEN 1
                      WHEN pcode LIKE '41300%' THEN 2
                      WHEN pcode LIKE '4130%' THEN 3
                      WHEN pcode LIKE '413%' THEN 4
                      WHEN pcode LIKE '41%' THEN 5
                      WHEN pcode LIKE '4%' THEN 6
                      ELSE NULL END)
              OVER(ORDER BY CASE WHEN pcode='4130033' THEN 0
                  WHEN pcode LIKE '413003%' THEN 1
                  WHEN pcode LIKE '41300%' THEN 2
                  WHEN pcode LIKE '4130%' THEN 3
                  WHEN pcode LIKE '413%' THEN 4
                  WHEN pcode LIKE '41%' THEN 5
                  WHEN pcode LIKE '4%' THEN 6
                  ELSE NULL END) AS min_code
              FROM PostalCode) Foo
WHERE hit_code=min_Code;

윈도우 함수를 사용해 정렬하는 비용이 추가되었으나 테이블 크기가 클 경우 테이블 풀 스캔을 줄이는 효과가 더 큼

3. 반복 횟수가 정해지지 않은 경우

인접 리스트 모델과 재귀 쿼리

포인터 체인: 우편번호를 키로 삼아 데이터를 줄줄이 연결한 것

인접 리스트 모델: 포인터 체인을 사용한 테이블 형식

 

재귀 공통 테이블 식(recursion common table expression)으로 계층 구조를 찾을 수 있음

 

- PostgreSQL 쿼리

WITH RECURSIVE Explosion (name, pcode, new_pcode, depth)
AS (SELECT name, pcode, new_pcode, 1
    FROM PostalHistory
    WHERE name='A'
          AND new_pcode IS NULL
    UNION
    SELECT Child.name, Child.pcode, Child.new_pcode, depth+1
    FROM Explosion AS Parent, PostalHistory AS Child
    WHERE Parent.pcode=Child.new_pcode
    AND Parent.name=Child.name)
SELECT name, pcode, new_pcode
FROM Explosion
WHERE depth = (SELECT MAX(depth) FROM Explosion);

재귀 공통 테이블 식 Explosion이 포인터 체인을 타고 올라가 과거의 주소를 찾음

재귀 수준이 가장 깊은 레코드 -> depth 값이 가장 큰 레코드

 

중첩 집합 모델

1) 인접 리스트 모델

계층 구조를 표현하는 전통적인 방법

2) 중첩 집합 모델

각 레코드의 데이터를 집합으로 보고 계층 구조를 집합의 중첩 관계로 나타냄

3) 경로 열거 모델

갱신이 거의 발생하지 않은 경우 유용한 방법

 

중첩 집합 모델 예시

추가되는 노드의 왼쪽 끝 좌표(lft): (plft*2+prgt) / 3

추가되는 노드의 왼쪽 끝 좌표(rgt): (plft+prgt*2) / 3

가장 깊은 레코드를 찾기 위해 가장 바깥쪽의 원을 찾으면 됨

가장 바깥쪽의 원은 다른 어떠한 원에도 포함되지 않음

 

SELECT name, pcode
FROM PostalHistory2 PH1
WHERE name='A'
AND NOT EXISTS
    (SELECT * 
     FROM PostalHistory2 PH2
     WHERE PH2.name='A'
     AND Ph1.lft>Ph2.lft);

NOT EXISTS를 사용해 왼쪽 끝의 좌표가 다른 모든 왼쪽 끝 좌표보다 작도록 조건 명시

재귀 연산 대신 중첩 코드 사용

바이어스의 공죄

반복문을 사용하는 것과 사용하지 않는 것에는 각각의 장단점 존재

-> 집합 지향 사고방식이 SQL의 강력한 도구와 튜닝 방법 활용에 반드시 필요함