갱신과 데이터 모델
갱신은 효율적으로
NULL 채우기
- 문제. NULL인 값을 이전 레코드의 값으로 채우기
UPDATE OmitTbl
SET val = (SELECT val
FROM OmitTbl OT1
WHERE OT1.keycol = OmitTbl.keycol
AND OT1.seq = (SELECT MAX(seq)
FROM OmitTbl OT2
WHERE OT2.keycol = OmitTbl.keycol
AND OT2.seq < OmitTbl.seq
AND OT2.val IS NOT NULL))
WHERE val IS NULL
- 다음과 같은 조건을 갖는 레코드 집합을 구하고, 그 중 가장 큰 seq 필드를 가진 레코드를 찾음
- 같은 keycol 필드를 가짐
- 현재 레코드보다 작은 seq 필드를 가짐
- val 필드가 NULL이 아님
반대로 NULL을 작성
- 문제. 위에서 채워진 값을 반대로 NULL로 바꾸기
UPDATE OmitTbl
SET val = CASE WHEN val
= (SELECT val
FROM OmitTbl O1
WHERE O1.keycol = OmitTbl.keycol
AND O1.seq = (SELECT MAX(seq)
FROM OmitTbl O2
WHERE O2.keycol = OmitTbl.keycol
AND O2.seq < OmitTbl.seq
AND O2.val IS NOT NULL))
THEN NULL
ELSE val END
레코드에서 필드로의 갱신
- 문제. 레코드 기반 테이블에서 필드 기반 테이블로 데이터 이동
필드를 하나씩 갱신
UPDATE ScoreCols
SET score_en = (SELECT score
FROM ScoreRows SR
WHERE SR.student_id = ScoreCols.student_id
AND subject = '영어'),
score_nl = (SELECT score
FROM ScoreRows SR
WHERE SR.student_id = ScoreCols.student_id
AND subject = '국어'),
score_mt = (SELECT score
FROM ScoreRows SR
WHERE SR.student_id = ScoreCols.student_id
AND subject = '수학'),
- 3개의 상관 서브쿼리를 실행하므로 성능적으로 좋지 않음
- 과목 수가 늘어날 수록 성능 악화
다중 필드 할당
UPDATE ScoreCols
SET (score_en, score_nl, score_mt)
= (SELECT MAX(CASE WHEN subject = '영어'
THEN score
ELSE NULL END) AS score_en,
MAX(CASE WHEN subject = '국어'
THEN score
ELSE NULL END) AS score_nl,
MAX(CASE WHEN subject = '수학'
THEN score
ELSE NULL END) AS score_mt,
FROM ScoreRows SR
WHERE SR.student_id = ScoreCols.student_id)
- 테이블 접근은 1회로 감소,
INDEX UNIQUE SCAN이 INDEX RANGE SCAN으로 변함, MAX 함수 정렬 추가 - 다중 필드 할당을 모든 SQL에서 지원하지는 않지만 표준 SQL 기능이므로 지원 가능성 높음
MAX 함수를 이용하여 서브쿼리로 단일한 값을 리턴 NOT NULL 제약이 걸려있는 경우
- UPDATE 구문 사용
- 필드를 하나씩 갱신
UPDATE ScoreColsNN
SET score_en = COALESCE((SELECT score
FROM ScoreRows
WHERE student_id = ScoreColsNN.student_id
AND subject = '영어'), 0),
score_nl = COALESCE((SELECT score
FROM ScoreRows
WHERE student_id = ScoreColsNN.student_id
AND subject = '국어'), 0),
score_mt = COALESCE((SELECT score
FROM ScoreRows
WHERE student_id = ScoreColsNN.student_id
AND subject = '수학'), 0)
WHERE EXISTS (SELECT *
FROM ScoreRows
WHERE student_id = ScoreColsNN.student_id)
- 다중 필드 할당
UPDATE ScoreColsNN
SET (score_en, score_nl, score_mt)
= (SELECT COALESCE(MAX(CASE WHEN subject = '영어'
THEN score
ELSE NULL END), 0) AS score_en,
COALESCE(MAX(CASE WHEN subject = '국어'
THEN score
ELSE NULL END), 0) AS score_nl,
COALESCE(MAX(CASE WHEN subject = '수학'
THEN score
ELSE NULL END), 0) AS score_mt,
FROM ScoreRows SR
WHERE SR.student_id = ScoreColsNN.student_id)
WHERE EXISTS (SELECT *
FROM ScoreRows)
WHERE student_id = ScoreColsNN.student_id)
- 처음부터 테이블 사이에 일치하지 않는 레코드가 존재할 경우 제외
- 학생은 존재하지만 과목이 없는 경우, 레코드는 있지만 필드가 없는 경우 제외
COALESCE 함수를 사용하여 NULL을 0으로 변경
- MERGE 구문 사용
MERGE INTO ScoreColsNN
USING (SELECT student_id,
COALESCE(MAX(CASE WHEN subject = '영어'
THEN score
ELSE NULL END), 0) AS score_en,
COALESCE(MAX(CASE WHEN subject = '국어'
THEN score
ELSE NULL END), 0) AS score_nl,
COALESCE(MAX(CASE WHEN subject = '수학'
THEN score
ELSE NULL END), 0) AS score_mt
FROM ScoreRows
GROUP BY student_id) SR
ON (ScoreColsNN.student_id = SR.student_id)
WHEN MATCHED THEN
UPDATE SET ScoreColsNN.score_en = SR.score_en,
ScoreColsNN.score_nl = SR.score_nl,
ScoreColsNN.score_mt = SR.score_mt
UPDATE 사용 시 두 곳에 분산되어 있던 결합조건을 ON 구로 한 번에 처리할 수 있음MERGE 구문은 UPDATE와 INSERT를 한 번에 시행하는 것이 목적이지만 UPDATE 또는 INSERT만 수행해도 문제는 없음 필드에서 레코드로 변경
- 갱신 대상 레코드를
subject 값에 따라 분기UPDATE ScoreRows
SET score = (SELECT CASE ScoreRows.subject
WHEN '영어' THEN score_en
WHEN '국어' THEN score_nl
WHEN '수학' THEN score_mt
ELSE NULL
END
FROM ScoreCols
WHERE student_id = ScoreRows.student_id)
같은 테이블의 다른 레코드로 갱신
- 문제. 이전 종가와 현재 종가를 비교해서 trend값 채우기
상관 서브쿼리 사용
INSERT INTO Stocks2
SELECT brand, sale_date, price,
CASE SIGN(price -
(SELECT price
FROM Stocks S1
WHERE brand = Stocks.brand
AND sale_date =
(SELECT MAX(sale_date)
FROM Stocks S2
WHERE brand = Stocks.brand
AND sale_date < Stocks.sale_date)))
WHEN -1 THEN '↓'
WHEN 0 THEN '→'
WHEN -1 THEN '↑'
ELSE NULL
END
FROM Stocks
SIGN 함수는 매개변수로 받은 숫자가 양수라면 1, 음수라면 -1, 0이라면 0을 반환하는 부호 조사 함수- 상관 서브쿼리 사용으로 인해 테이블 접근 횟수가 많음
윈도우 함수 사용
INSERT INTO Stocks2
SELECT brand, sale_date, price,
CASE SIGN(price -
MAX(price) OVER (PARTITION BY brand
ORDER BY sale_date
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING))
WHEN -1 THEN '↓'
WHEN 0 THEN '→'
WHEN -1 THEN '↑'
ELSE NULL
END
FROM Stocks S2
INSERT와 UPDATE 어떤 것이 좋을까?
INSERT SELECT의 장점UPDATE에 비해 우수한 성능- MySQL처럼 갱신 SQL에서 자기 참조를 허가하지 않는 DB에서도 사용 가능
INSERT SELECT의 단점- 같은 크기와 구조를 가진 데이터를 두 개 만들어야 하므로 저장소 용량을 2배 이상 소비
갱신이 초래하는 트레이드오프
- 문제. 주문마다 주문일(order_date)와 상품의 배송 예정일(delivery_date)의 차이를 구해, 그 차이가 3일 이상인 주문번호를 찾기
SQL을 사용하는 방법
- 주문일과 배송 예정일의 차이
SELECT O.order_id,
O.order_name,
ORC.delivery_date - O.order_date AS diff_days
FROM Orders O
INNER JOIN OrderReceipts ORC
ON O.order_id = ORC.order_id
WHERE ORC.delivery_date - O.order_date >= 3
- 주문 단위로 집약
SELECT O.order_id
MAX(O.order_name),
MAX(ORC.delivery_date - O.order_date) AS diff_days
FROM Orders O
INNER JOIN OrderReceipts ORC
ON O.order_id = ORC.order_id
WHERE ORC.delivery_date - O.order_date >= 3
GROUP BY O.order_id
- order_id와 order_name이 일대일 대응한다면
GROUP BY 구에 order_name을 추가해도 됨 모델 갱신을 사용하는 방법
- SQL을 사용할 경우 검색 처리에 드는 비용이 높음
- 결합은 실행 계획의 변동 리스크가 있어 장기적으로 봤을 때 성능을 불안정하게 만듦
- SQL을 사용하지 않고 Orders 테이블에 배송 지연 플래그를 추가하고 플래그를 갱신
모델 갱신의 주의점
높아지는 갱신비용
- 검색 부하를 갱신 부하로 바꿈
- 플래그 값이 변경되는 경우는 갱신 비용 상
갱신까지의 시간 랙(Time Rag) 발생
- 배송 예정일이 주문 등록 후에 갱신되는 경우 Orders 테이블의 배송 지연 플래그 필드와 OrderReceipts 테이블의 배송 예정일 필드가 실시간으로 동기화되지 않으므로 차이 발생
- 실시간성이 중요한 업무일수록 생신 간격이 짧아져야 함
- 완전한 실시간을 요구하는 경우 동일 트랜잭션으로 처리해야 하는데 성능과 실시간성 사이 트레이드오프 발생
모델 갱신비용 발생
- RDB 데이터 모델 갱신은 코드 기반의 수정에 비해 대대적인 수정이 필요
- 갱신 대상 테이블을 사용하는 다른 처리에 문제가 발생할 가능성 존재
- 실제 운용에 들어가면 모델 변경은 거의 불가능
시야 협착 : 관련 문제
- 문제. 주문번호마다 몇 개의 상품이 주문되었는지 조회
다시 SQL을 사용한다면
- 집약 함수를 사용
SELECT O.order_id,
MAX(O.order_name) AS order_name,
MAX(O.order_date) AS order_date,
COUNT(*) AS item_count
FROM Orders O
INNER JOIN OrderReceipts ORC
ON O.order_id = ORC.order_id
GROUP BY O.order_id
- 윈도우 함수를 사용
SELECT O.order_id,
O.order_name
O.order_date
COUNT(*) OVER (PARTITION BY O.order_id) AS item_count
FROM Orders O
INNER JOIN OrderReceipts ORC
ON O.order_id = ORC.order_id
- 두 방법 모두 실행 비용, 실행 계획은 비슷
- 가독성, 확장성 측면에서는 윈도우 함수가 좋음
다시 모델 갱신을 사용한다면
- Orders 테이블에 상품 수를 추가
- 한 번 등록한 주문을 나중에 변경할 경우 수정 가능성 있음, 동기/비동기 문제 고려 필요
초보자보다 중급자가 경계해야
- 어려운 문제를 어려운 상태인 채 풀면 복잡한 코드 또는 프로그램이 만들어져 시스템 전체 관점에서 비효율이 발생할 수 있음
데이터 모델을 지배하는 자가 시스템을 지배한다
- ‘현명한 데이터 구조와 멍청한 코드의 조합’이 ‘멍청한 데이터 구조와 현명한 코드의 조합’보다 좋음