[SQL 레벨업] Ch.5 반복문

Manx·2022년 8월 9일

SQL 레벨업

목록 보기

6/11

SQL의 반복문

SQL은 일부러 반복문을 언어 설계에서 제외했다.

일반적으로 SQL에서 반복문이 지원되지 않아, 다음과 같은 방식으로 프로그래밍을 하는 경우가 많다.

레코드에 하나씩 접근하는 SELECT 구문을 반복해서 사용한다.
호스트 언어에서 반복문을 처리한 뒤 테이블에 갱신한다.

그렇지만, 반복이 없을 경우 생기는 장점들이 많다.

반복계의 단점

1. 성능

반복계로 구현한 코드는 포장계(반복을 사용하지 않은)로 구현한 코드에 성능적으로 완벽하게 진다.
레코드 수가 적을 때에는 반복계가 빠른 경우도 있지만, 레코드 수가 많아질수록 성능 차이가 더욱 벌어진다.

SQL 실행의 오버헤드

전처리
1. SQL 구문을 네트워크로 전송
2. 데이터베이스 연결
3. SQL 구문 파스
4. SQL 구문의 실행 계획 생성 / 평가
후처리
1. 결과 집합을 네트워크로 전송

1번과 5번의 과정의 경우 내부의 동일 LAN 위에 있으므로 전송 속도 자체는 거의 밀리 sec이다. -> 오버헤드가 딱히 일어나지 않음

2번의 경우 커넥션 풀이라는 기술을 사용해 거의 문제되지 않는다.

문제가 되는 경우는 3번과 4번 과정이다.

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

2. 병렬 분산의 어려움

반복계는 반복 1회마다의 처리를 굉장히 단순화한다. 따라서 리소스를 분산해서 병렬 처리하는 최적화가 되지 않는다. 데이터베이스는 대부분 RAID 디스크로 구성되어 I/O 부하를 분산화할 수 있게 되어있지만, 반복계에서 실행하는 SQL 구문은 너무 단순해 1회의 접근하는 데이터양이 적다.

I/O를 병렬화하기 힘들다.

3. 데이터베이스 업그레이드

DBMS의 버전이 오를수록 옵티마이저는 보다 효율적으로 실행 계획을 세우며, 데이터에 고속으로 접근할 수 있는 아키텍처를 구현한다.
그러나, 이러한 업데이트의 중심은 대규모 데이터를 다루는 복잡한 SQL구문을 빠르게 만들기 위해서이다.

반복계는 미들웨어의 진화의 혜택을 거의 받을 수 없다.

포장계가 반복계보다 성능이 좋다는 가정은 포장계의 SQL이 충분히 튜닝되어 있다는 가정이 있어야한다.
일반적으로 굉장히 단순한 반복계의 SQL은 튜닝 가능성이 거의 없지만, 포장계의 SQL은 매우 복잡하기 때문에 튜닝의 가능성이 매우 크다. (이것은 포장계의 단점이기도 함
-복잡하니까)

반복계의 장점

1. 실행 계획의 안정성

반복문의 SQL 구문은 지나치게 단순하기 때문에 실행 계획도 엄청나게 단순하다.
이를 통해 실행 계획에 변동 위험이 거의 없음을 알 수 있다.
실제 운용 중에 갑자기 실행 계획이 바뀌어 느려지는 현상이 일어나지 않는다.
-> 이는 포장계의 단점이기도 하다.

2. 트랜잭션 제어의 편리

트랜잭션의 정밀도를 미세하게 제어할 수 있다.
갱신 처리를 반복계에서, 특정반복 횟수마다 커밋한다고 가정했을 때, 중간에 오류가 발생했다고 해도 중간한 커밋을 이용해 해당 지점 근처에서 다시 처리할 수 있다.

SQL에서의 반복 표현

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

작년도와 매출액을 비교해 +, -, = 을 표시하는 SQL

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, 0일 경우 0을 반환
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING : 현재 레코드에서 1개 이전부터 1개 이전까지

같은 회사의 직전 매상을 리턴해 SIGN 함수로 차이를 계산한 뒤 var로 출력한다.

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

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

이사를 갈 때마다 필드를 추가해야 하는 경우가 있다고 가정.
제일 오래전에 주소를 찾고 싶다.
우편번호를 키로 삼아 이전 주소의 데이터를 줄줄이 연결해 놓았음.
-> 포인터 체인이라고 한다.
-> 포인터 체인을 사용하는 테이블 형식을 '인접 리스트 모델'이라고 부른다.

SQL에서 계층 구조를 찾는 방법 중 하나는 재귀 공통 테이블 식을 사용하는 방법이다.

WITH RECURSIVE Explosion (name, pcdoe, 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 구문
SELECT name, pcode, new_pcode
	FROM Explosion
  WHERE depth = (SELECT MAX(depth)
  					FROM Explostion);

RECURSIVE가 재귀인건 알지만, 이해하기 힘들어서 다시 공부하고 이해했다..

WITH RECURSIVE [VIEWNAME]
AS
초기식
UNION
SELECT ~ FROM ~
WHERE RECURSIVE 종료 조건

Parent.pcode = Child.new_pcode AND Parent.name = Child.name
즉 Parent의 자식이 있는지 검사해서 있으면 depth를 +1 해준다고 생각하면 된다.
depth의 MAX를 구하면, 그곳이 가장 오래 전에 살은 곳이다.

실행 계획 (PostgreSQL)

'Recursive Union' -> 재귀 연산 (몇 번을 이사해도 대응할 수 있다는 점에서 굉장히 유연한 쿼리이다.)
'WorkTable' -> Explosion 뷰에 여러 번 접근하므로 임시 테이블로 만듦
이렇게 만들어진 일시 테이블과 원래 PostalHistory 테이블은, 인덱스를 사용해
Nested Loops(이중 for문) 를 수행하므로 꽤 효율적인 계획이다.

RDB에서 고성능을 실현하고 싶다면, 절차 지향적인 바이어스를 떼어내고 자유로워질 필요가 있다.

동시에 반복계와 포장계의 장점과 단점을 고려하고, 어느 것을 채택할지 냉정하게 판단해야 한다.

SQL이 가진 강력한 도구와 튜닝 방법을 활용하려면 반드시 집합 지향의 사고방식을 가져야한다.

5장까지의 후기 : 점점 쿼리를 이해하기 힘들어지며, 내용이 급격하게 어려워졌다.