5장 반복문

14. 반복문 의존중

- 관계 조작(SQL)은 관계 전체를 모두 조작의 대상으로 삼는다.

    ㆍ이러한 것의 목적은 반복을 제외하는 것이다.

    ㆍ최종 사용자의 생산성을 생각하면 이러한 조건을 만족해야 한다.

    ㆍ그래야만 응용 프로그래머의 생산성에도 기여할 수 있을 것이다.

 

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

---

15. 반복계의 공포

- 여러 행을 한꺼번에 처리하는 SQL을 포장계라고 부른다.

1. 반복계의 단점

- 한 다미로 설명한다면 '성능'이다.

    ㆍ같은 기능을 구현한다고 가정하면, 반복계로 구현한 코드는 포장계로 구현한 코드에 성능적으로 이길 수가 없다.

- 처리하는 레코드 수가 적을 때는 반복계와 포장계에 큰 차이가 없다.

 

SQL 실행의 오버 헤드

- SQL을 실행할 때는 데이터를 검색하거나 연산하는 실제의 SQL 처리 이외에도 다양한 처리가 이루어진다.

    ㆍ전처리

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

         ▷ 2. 데이터베이스 연결

         ▷ 3. SQL 구문 파스

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

    ㆍ후처리

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

- 1번과 5번은 SQL을 실행하는 애플리케이션과 데이터베이스가 물리적으로 같은 본체에 있다면 발생하지 않을 것이다.

    ㆍ일반적으로 두 가지는 같은 데이터센터 내부의 동일 LAN 위에 있으므로

         전송 속도 자체는 고속인 만큼 오버헤드가 딱히 일어나지 않는다.

- 2번은 데이터베이스에 SQL 구문을 실행하기 위한 작업이다.

    ㆍ일단 데이터베이스에 연결해서 세션을 설정해야 하므로 발생하는 처리이다.

    ㆍ최근에는 애플리케이션에서 미리 연결을 일정 수 확보해서 이런 오버헤드를 감소시키는 커넥션 풀이라는 기술을 사용한다.

    ㆍ그러므로 거의 문제되지 않는다.

- 오버헤드 중에서 가장 영향이 큰 것은 3번 또는 4번이다.

    ㆍ더 성가신 것은 SQL 파스이다. 

    ㆍ파스는 데이터베이스가 SQL을 받을 때마다 실행되므로 작은 SQL을 여러 번 반복하는 반복계에서는 오버헤드가 높아질 수 밖이다.

 

병렬 분산이 힘들다.

- 반복계는 반복 1회마다의 처리를 굉장히 단순화한다.

- 따라서 리소스를 분산해서 병렬 처리하는 최적화가 안된다.

 

데이터베이스의 진화로 인한 혜택을 받을 수 없다.

- 최근 특히 주목을 모으고 있는 것이 기존의 디스크를 초월하는 I/O 성능을 가진 SSD 등의 매체이다.

- SSD가 실용화되면 저장소 넥(storage neck)에 시달리던 데이터베이스 세계에 혁명이 일어날 수 있다.

- 하지만 단순한 SQL 구문과 같은 '가벼운' 처리를 빠르게 만드는 것은 사실 안중에 없다.

    ㆍ따라서 반복계는 미들웨어 또는 하드웨어의 진화에 따른 혜택을 거의 받을 수 없다.

- 반복계는 단지 느리기만 한 것이 아니라 느린 구문을 튜닝할 수 있는 가능성도 거의 없다.

    ㆍ반복계의 정말 무서운 점은 이 단점이다.

 

2. 반복계를 빠르게 만드는 방법은 없을까?

반복계를 포장계로 다시 작성

- 이는 애플리케이션 수정을 의미한다.

 

각각의 SQL을 빠르게 수정

- 반복계에서 사용하는 SQL 구문은 너무 단순하다.

    ㆍ실행 계획을 보아도 유니크 스캔(unique scan) 또는 인덱스 레인지 스캔(index range scan) 정도 뿐이다.

- INSERT 구문을 반복하는 경우도 있다.

    ㆍINSERT 구문은 SELECT 구문보다 고속화가 더 어렵다.

 

다중화 처리

- 지금까지 살펴본 선택지 중에서 가장 희망적인 선택지이다.

- CPU 또는 디스크와 같은 리소스에 여유가 있고, 처리를 나눌 수 있는 키가 명확하게 정해져 있다면, 처리를 다중화해서 성능을 선형에 가깝게 스케일할 수 있다.

- 물론 애플리케이션 수정이 필요하지만, 처음부터 다중도를 설정할 수 있게 애플리케이션을 구성했다면 코드를 변경하지 않고도 확장이 가능하다.

- 하지만 반대로 데이터를 분할 할 수 있는 명확한 키가 없거나, 순서가 중요한 처리, 병렬화 했을 때 물리 리소스가 부족하다면 이러한 방법은 사용할 수 없다.

 

- 이렇게 반복계라는 것은 튜닝의 선택지가 굉장히 한정적이다.

 

3. 반복계의 장점

실행 계획의 안정성

- 실행 계획이 단순하다는 것은 해당 실행 계획에 변동 위험이 거의 없다라는 것을 나타낸다.

- 변동이 일어난다고 해봤자 겨우 옵티마이저에서 사용하는 인덱스를 바꾸는 정도이다.

 

- 포장계는 SQL 구문이 복잡한 만큼 실행 계획의 변동 가능성이 굉장히 크다

 

예상 처리 시간의 정밀도

- 실행 계획이 단순하고 성능이 안정적이라는 것은 추가적인 장점을 가져온다.

- 바로 예상 처리 시간의 정밀도가 높다는 것이다.

 

트랜잭션 제어가 편리

- 반복계의 또 하나의 장점은 바로 기능적 측면이다.

- 즉, 트랜잭션의 정밀도를 미세하게 제어할 수 있다는 것이다.

- 예를 들어, 갱신 처리를 반복계에서, 특정 반복 횟수마다 커밋한다고 하면, 중간에 오류가 발생했다고 해도 중간에 커밋을 했으므로 해당 지점 근처에서 다시 처리를 실행하면 된다.

 

---

16. SQL에서는 반복을 어떻게 표현할까?

1. 포인트는 CASE 식과 윈도우 함수

- SQL에서 반복을 대신하는 수단은 바로 CASE 식과 윈도우 함수이다.

INSERT INTO Sales2
SELECT company, year, sale,
       CASE SIGN(sale - MAX(sale) OVER (PATITION 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을 리턴하는 함수이다.

- CASE 식의 조건 부분에 윈도우 함수를 몇 번씩 사용하지 않도록 해주는 기술이기도 하다.

- 실행계획을 살펴보면 Sales 테이블을 풀 스캔하고 윈도우 함수를 정렬로 실행한다.

    ㆍ현재 SELECT 구문은 결합을 사용하지 않는다.

    ㆍ따라서 테이블의 레코드 수가 증가해도 실행 계획에 별다른 영향을 주지 않으므로 안정적이다.

- 이 코드에서 중요한 기술은 윈도우 함수에 ROWS BETWEEN 옵션을 사용한 것이다.

    ㆍ이는 대상 범위의 레코드를 직전의 1개로 제한하는 것이다.

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

# 윈도우 함수로 '직전 회사명'과 '직전 매상' 검색
SELECT company, year, sale,
       MAX(company) OVER (PARTITION BY company ORDER BY year
                          ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pre_company,
       MAX(sale) OVER (PARTITION BY company ORDER BY year
                          ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pre_sale,
  FROM Sales;

 

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

CREATE TABLe PostalCode
(pcode CHAR(7),
 district_name VARCHAR(256),
 CONSTRAINT pk_pcode PRIMARY KEY(pcode));
 
INSERT INTO PostalCode VALUES ('4130001', '시즈오카 아타미 이즈미');
INSERT INTO PostalCode VALUES ('4130002', '시즈오카 아타미 이즈산');
INSERT INTO PostalCode VALUES ('4130003', '시즈오카 아타미 아지로');
INSERT INTO PostalCode VALUES ('4130041', '시즈오카 아타미 아오바초');
INSERT INTO PostalCode VALUES ('4103213', '시즈오카 이즈 아오바네');
INSERT INTO PostalCode VALUES ('4380824', '시즈오카 이와타 아카');

 

결국, 순위 붙이기 문제

# 우편번호 순위를 매기는 쿼리
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
     FROM PostalCode;

# 가까운 우편번호를 구하는 쿼리
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 = '413003%' THEN 1
                                   WHEN pcode = '41300%'  THEN 2
                                   WHEN pcode = '4130%'   THEN 3
                                   WHEN pcode = '413%'    THEN 4
                                   WHEN pcode = '41%'     THEN 5
                                   WHEN pcode = '4%'      THEN 6
                       FROM PostalCode);

- 순위의 최솟값을 서브쿼리에서 찾기 떄문에 테이블 스캔이 2회 발생한다.

 

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

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

 

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

- 어떤 키로 데이터를 줄줄이 연결한 것을 포인터 체인이라고 부른다.

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

    ㆍ포인터 체인을 사용하는 History 같은 테이블 형식을 '인접 리스트 모델' 이라고 부른다.

 

중첩 집합 모델

- SQL에서 계층 구조를 나타내는 방법 3가지

    ㆍ1. 인접 리스트 모델

    ㆍ2. 중첩 집합 모델

    ㆍ3. 경로 열거 모델

- 중첩 집합 모델은 각 레코드의 데이터를 집합으로 보고, 계층 구조를 집합의 중첩 관계로 나타낸다는 것이다.

 

17. 바이어스의 공죄

 

---

마치며..

- 우리는 모두 반복문 의존증에 걸려있다

- SQL은 의도적으로 반복문을 설계에서 제외했음

- 반복계는 성능적으로 큰 결점을 가지고 있지만, 몇 가지 장점도 있다

- 하지만 반복계는 성능 튜닝 가능성이 거의 없으므로 사용할 때 주의가 필요

- 여기에서도 트레이드오프를 고려해서, 반복계와 포장계 중에 어떤 것을 채용할지 판단할 필요가 있음