반복계의 단점
성능 성능 성능 성능이다.
반복계의 처리 시간
[처리 횟수] * [한 회에 걸리는 시간] 인데, 한 회에 걸리는 시간은 일정하다 하더라도, 처리 횟수는 처리 대상 레코드에 비례하므로, 반복계의 처리 시간은 레코드 수에 대해 선형으로 증가한다.
SQL 실행의 오버 헤드
SQL을 실행할 때에는, SQL문의 직접적인 실행 외에 다음과 같은 처리가 이루어진다.
- SQL 구문을 네트워크로 전송
- 데이터베이스 연결
- SQL 구문 파스
- SQL 구문의 실행 계획 생성 또는 평가
- 결과 집합을 네트워크로 전송
SQL 구문이나 결과 집합을 네트워크로 전송
만약, 데이터베이스와 애플리케이션이 물리적으로 같은 본체에 있다면 발생하지 않을 일이다.
하지만, 보통 서비스에선 분리해서 사용하므로 네트워크로 전송하는 일은 흔히 일어난다. 일어난다 해도, 일반적으로는 같은 데이터센터 내부의 동일 LAN 위에 있으므로 전송 속도 자체는 고속인 만큼 오버헤드가 딱히 일어나지 않는다.
데이터베이스 연결
원래는 데이터베이스에 연결해, 세션을 설정해야 하므로 발생하는 처리
보통 요즘은 애플리케이션에서 미리 연결을 일정 수 확보해, 오버헤드를 감소시키는 커넥션 풀이라는 기술 사용 → 따라서 오버헤드 문제 X
SQL 구문 파스, 실행 계획 생성 or 평가
가장 오버헤드에서 영향이 큰 단계이다.
SQL 파스는 SQL문을 받을 때마다 실행되므로, SQL구문을 반복하는 반복계는 오버헤드가 높아질 수 밖에 없다.
병렬 분산이 힘들다
데이터베이스 서버는 RAID 디스크로 구성되어 있어, I/O 부하 분산이 가능하다. 하지만 반복계를 사용한다면, SQL구문은 단순해 반복 1회당 접근하는 데이터 양이 적다보니, 분산이 되지 않는다.
데이터베이스 진화의 혜택을 받지 못한다
요즘 데이터베이스는 옵티마이저는 보다 효율적인 실행 계획을 세우며, 하드웨어 벤더도 이에 동참해 SSD가 실용화가 된다면, 저장소 넥(storage neck)에 시달리던 DB 세계에 혁명이 일어날 수 있다.
하지만 이와 같은 DB 발전은 대규모 데이터를 다루는 복잡한 SQL 구문에 관심 있지, 반복계에서 사용되는 간단한 SQL 구문을 실행하는 데에는 관심이 없다.
대부분 반복계의 처리가 문제가 되는 경우, 스케일 업하는 경우도 있는데 이는 병목현상이 아니라면, 스케일 업해도 문제가 해결되지 않는다.
또한 복잡한 하나의 SQL 구문은 쿼리 튜닝 가능성이 높지만, 반복계에서 사용되는 SQL 구문은 사실 튜닝할 쿼리가 없다보니 개선될 가능성도 없다.
반복계를 빠르게 만드는 방법
반복계를 포장계로 다시 작성
애플리케이션을 수정하자!
각각의 SQL을 빠르게 수정
사실 실현 가능성 0이다
다중화 처리
처리를 다중화한다면, 성능을 선형에 가깝게까지는 스케일할 수 있다. 하지만, 데이터를 분할할 수 있는 키가 없거나, 순서가 중요하거나, 물리 리소스가 부족하면 이 또한 하기 어렵다.
반복계의 장점
반복계의 SQL 구문이 지나치게 단순해서 생기는 장점들을 살펴보자
실행 계획 안정성
실행 계획에 변동 위험이 거의 없다.
SQL문이 간단한만큼, 결합을 사용하지 않아도 되기 때문에, 결합 알고리즘이 갑자기 변경되는 등 안정적이지 못한 옵티마이저를 사용하고 있는 시점에서, 실행 계획의 변동이 없다는 것은 사실 안정적인 성능을 확보한 거나 다름없다.
이는 반대로 포장계의 단점이 된다. 포장계는 실행 계획의 변동이 쉽게 일어날 수 있다. 이는 힌트 구문을 사용하거나, 조금 단순한 구문을 사용하도록 방향을 잡는 게 좋다.
예상 처리 시간 정밀도
실행 계획이 단순한 만큼 예상 처리 시간의 변동이 거의 없다. 포장계는 실행 계획에 따라 성능이 전혀 달라져, 정밀도가 낮다.
트랜잭션 제어 편리
반복계는 오류가 발생해도, 해동 지점에서 다시 처리를 실행하면 되지만, 포장계는 하나의 복잡한 구문을 실행하므로, 오류가 발생한다면, 처음부터 다시 실행해야 한다.
SQL에서는 반복을 어떻게 표현할까?
CASE식 윈도우 함수 세트
아래는 반복 대신에 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을 리턴하는 함수이다.
OVER
아래와 같이 작성하면, 단일 칼럼인 category와 집계 칼럼인 SUM(score)를 함께 사용했기 때문에 오류가 발생한다.
SELECT category, SUM(score)
FROM products;이때 OVER를 사용하지 않고 수정을 해본다면 아래와 같이 서브쿼리를 사용해야 한다.
SELECT category, (SELECT SUM(score) FROM products) AS SCORE
FROM products;하지만 OVER를 사용하면, 한 방에 깔끔하게 처리할 수 있다.
SELECT category, SUM(score) OVER (PARTITION BY(category)) AS SCORE
FROM products;ROWS BETWEEN
대상 범위의 레코드를 직전 1개로 제한
예시)
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
현재 행의 이전 행 하나만을 포함하는 윈도우 프레임을 설정하는 SQL문이다. 보통 이전 행의 데이터와 현재 행의 데이터를 비교할 때 유용하게 사용된다.
최대 반복 횟수가 정해진 경우
입력 받은 우편번호와 가장 가까이 일치하는 우편번호를 찾자
| pcode(우편번호) | district_name(지역 이름) |
|---|---|
| 4130001 | 시즈오카 아타미 이즈미 |
| 413002 | 시즈오카 아타미 이즈산 |
순위를 매겨보자
가장 가까이 일치하는 우편번호를 찾으려면 순위를 매기면 더욱 빠르게 찾을 수 있다.
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 rank
FROM PostalCode;이 코드를 더 발전시켜서 순위만 매기는 것이 아니라, 가장 가깝게 일치하는 우편번호를 찾는 SQL문을 작성해보자.
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);위 쿼리문의 실행 계획을 보면, 일단 table full scan이 두 번 동작하는 것을 알 수 있다.
Seq Scan on postalcode (cost=1.19..2.37 rows=1 width=41)
Filter: (CASE WHEN (pcode = '4130033'::bpchar) THEN 0 WHEN (pcode ~~ '413003%'::text) THEN 1 WHEN (pcode ~~ '41300%'::text) THEN 2 WHEN (pcode ~~ '4130%'::text) THEN 3 WHEN (pcode ~~ '413%'::text) THEN
4 WHEN (pcode ~~ '41%'::text) THEN 5 WHEN (pcode ~~ '4%'::text) THEN 6 ELSE NULL::integer END = $0)
InitPlan 1 (returns $0)
-> Aggregate (cost=1.18..1.19 rows=1 width=4)
-> Seq Scan on postalcode postalcode_1 (cost=0.00..1.06 rows=6 width=8)스캔 횟수를 줄여보자
테이블 스캔이 2회 발생하는 이유는 바로, 순위의 최솟값을 서브쿼리에서 찾기 때문이다.
서브 쿼리를 윈도우 함수인 OVER를 이용해 없애보자.
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;왜 ORDER BY를 사용했을까?
실행계획을 다시 살펴보면, 테이블 접근은 1회로 감소된 것을 볼 수 있다.
하지만 정렬이 추가로 사용되어 여기에 비용이 추가되지만, 테이블 접근보다는 더 적은 비용이라 괜찮다.
Subquery Scan on foo (cost=1.24..1.63 rows=1 width=41)
Filter: (foo.hit_code = foo.min_code)
-> WindowAgg (cost=1.24..1.56 rows=6 width=49)
-> Sort (cost=1.24..1.26 rows=6 width=45)
Sort Key: (CASE WHEN (postalcode.pcode = '4130033'::bpchar) THEN 0 WHEN (postalcode.pcode ~~ '413003%'::text) THEN 1 WHEN (postalcode.pcode ~~ '41300%'::text) THEN 2 WHEN (postalcode.pcode
~~ '4130%'::text) THEN 3 WHEN (postalcode.pcode ~~ '413%'::text) THEN 4 WHEN (postalcode.pcode ~~ '41%'::text) THEN 5 WHEN (postalcode.pcode ~~ '4%'::text) THEN 6 ELSE NULL::integer END)
-> Seq Scan on postalcode (cost=0.00..1.17 rows=6 width=45)반복 횟수가 정해지지 않은 경우
위에서는 반복횟수가 7회로 정해져 있어, 분기를 7개 이용했지만, 만약 반복 횟수가 정해지지 않았다면?
인접 리스트 모델 & 재귀 쿼리
| name(사람 이름) | pcode(우편번호) | new_pcode(이사하는 곳) |
|---|---|---|
| A | 4130001 | 4130002 |
| A | 4130002 | 4130103 |
| A | 4130103 |
A씨가 이사를 한 과정
이는 인접 리스트 모델이다.
4130001 -> 4130002 -> 4130103SQL에서 계층 구조를 찾는 방법 중 하나는 재귀 공통 테이블 식을 사용하는 방법이다.
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);재귀 쿼리
아래 부분은 name, pcode, new_pcode, depth 칼럼을 가지고 있는 테이블, explosion을 만들겠다고 명시되어 있다.
WITH RECURSIVE Explosion (name, pcode, new_pcode, depth) AS ...이제 재귀 탐색의 시작 지점을 정의해주고 있다. name 이 ‘A’이고, new_pcode 가 NULL인 부분이 시작이다. depth는 1이다.
SELECT name, pcode, new_pcode, 1
FROM PostalHistory
WHERE name = 'A'
AND new_pcode IS NULLExplosion(Parent) 테이블과 PostalHistory(Child)를 조인하여, 부모와 자식 관계가 있는 레코드를 선택, depth는 1 증가
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어쨌든 재귀 공통 테이블 식을 이용한 실행 계획은 다음과 같다.
QUERY PLAN
----------------------------------------------------------------------------------------------
CTE Scan on explosion (cost=19.22..19.49 rows=1 width=72)
Filter: (depth = $2)
CTE explosion
-> Recursive Union (cost=0.00..18.94 rows=12 width=22)
-> Seq Scan on postalhistory (cost=0.00..1.07 rows=2 width=22)
Filter: ((new_pcode IS NULL) AND (name = 'A'::bpchar))
-> Hash Join (cost=1.15..1.76 rows=1 width=22)
Hash Cond: ((parent.pcode = child.new_pcode) AND (parent.name = child.name))
-> WorkTable Scan on explosion parent (cost=0.00..0.40 rows=20 width=44)
-> Hash (cost=1.06..1.06 rows=6 width=18)
-> Seq Scan on postalhistory child (cost=0.00..1.06 rows=6 width=18)
InitPlan 2 (returns $2)
-> Aggregate (cost=0.27..0.28 rows=1 width=4)
-> CTE Scan on explosion explosion_1 (cost=0.00..0.24 rows=12 width=4)재귀 공통 테이블은 비교적 최근에 만들어졌기 때문에, 구현이 되지 않았거나, 실행 계획이 최적화되지 않은 DBMS가 많다. 따라서 중첩 집합 모델도 사용한다.
중첩 집합 모델: 각 레코드의 데이터를 집합으로 보고, 계층 구조를 집합의 중첩 관계로 나타내는 것
