Ch.5 반복문 - 절차 지향형의 속박

텐저린티·2023년 8월 8일

SQL 레벨업

SQL레벨업

목록 보기

5/10

SQL은 절차 지향적인 사고 방식보다 집합 지향적인 사고가 좋음

14강. 반복문 의존증

문제를 레코드 단위까지 작게 나누어 반복문으로 문제를 해결하려는 태도
응용 레벨 언어로 프로그래밍을 배웠을때의 폐혜
SQL은 반복문이 없음
관계 조작(SQL)은 관계 전체를 모두 조작 대상으로 삼음
목적은 반복을 제외해 응용 프로그래머 생산성에 기여하기 위함

15강. 반복계의 공포

SQL 처리 단순화가 반복계의 장점
구문 복잡이 포장계 SQL 단점

반복계 단점

반복계는 포장계에 성능적으로 이길 수 없음
- 반복계 처리 시간
  - 처리 횟수 * 1회 처리시간
  - 선형 증가
- 포장계 처리 시간
  - log(n) 증가
  - 인덱스 사용 & 실행계획 변동 X ⇒ 더 완만해짐
SQL 실행 오버헤드
- 전처리
  1. SQL 구문 네트워크 전송
  2. 데이터베이스 연결 → 커넥션 풀로 해결 가능
  3. SQL 구문 파스
  4. SQL 구문 실행 계획 생성 및 평가
- 후처리
  1. 결과 집합 네트워크 전송
- 3번, 4번이 오버헤드에 큰 영향
  - 작은 SQL을 여러 번 반복하는 반복계에서는 오버헤드 증가
병렬 분산 힘듬
- 반복계는 처리가 단순
- 병렬 처리 최적화 불가 → 멀티코어 장점 살릴 수 없음
- 저장소 분산 효율 낮음
데이터베이스 진화로 인한 혜택 적용 X
- 데이터베이스 진화는 대규모 데이터 다루는 복잡한 SQL 처리 속도 향상이 목적
- 단순한 반복계 SQL은 적용점 없음
- 포장계 SQL 구문은 튜닝 가능성이 굉장히 높음
- 반복계 SQL 구문은 튜닝 가능성 전무

반복계 빠르게 만드는 방법

반복계를 포장계로 다시 작성 → 현실적으로 무리
각 SQL을 빠르게 수정 → 튜닝 가능성 적음
다중화 처리
- 리소스 여유, 데이터 분할 가능 키 명확한 경우 가능
- 쓸 수 있는 경우 제한적

반복계 장점

반복계 SQL 구문의 단순함에서 출발
반복계 장점 = 포장계 단점
실행계획 안정성
- 실행계획 변동 위험 적음
- 구문 내부에서 결합 사용하지 않음
예상 처리 시간 정밀도
- 처리시간 = 실행횟수 * 1회 처리 시간
- 포장계는 실행계획에 따라 성능 차이 심함
트랜잭션 제어 편리
- 트랜잭션 정밀도 미세하게 제어 가능
- 포장계에서는 갱신 처리 오류 발생 시, 처리를 처음부터 다시 시작
- 반복계는 오류 발생 시, 해당 부근부터 다시 시작 가능

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

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

SQL 반복 = CASE 식 or 윈도우 함수
증감에 따라 +, -, = 로 표현하는 SQL 구문 예

# sign 함수: 숫자 자료형 매개변수
# 음수 -> -1
# 양수 -> 1
# 0   -> 0
# rows between n preceding and n preceding: 대상 범위 레코드를 직전 n개 이전 부터 n개 이전까지의 레코드 범위로 제한
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;

# 윈도우 함수를 정렬 실행
SELECT STATEMENT
	WINDOW SORT
		TABLE ACCESS FULL

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

인접한 우편 번호 찾기

create table postal_code (
	pcode char(7),
	distinct_name varchar(256),

	constraint pk_pcode primary key(pcode)
);

insert into postal_code values ('4130001', '제주특별자치도 서귀포시');

반복계 방법
- 7회 반복을 통해 레코드 select
- 레코드 수 많아질 수록 성능 악화

포장계 방법

select pcode, distinct_name,
			case  when pcode = '410033' 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 postal_code;

case는 when 구를 차례대로 검사 → 조건 맞으면 이후 when 평가 X

min 함수 이용한 구문

select *
from postal_code
where case  when pcode = '410033' 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 = '410033' 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 postal_code);

오라클 실행계획

2회 테이블 접근

SELECT STATEMENT
	TABLE ACCESS FULL
		SORT AGGREGATE
			TABLE ACCESS FULL

윈도우 함수 방법

select *
from (select pcode, distinct_name,
			 case when pcode = '410033' 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 = '410033' 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 = '410033' 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 postal_code) foo
where hit_code = min_code;

실행계획
- 테이블 접근 1회
- 윈도우 함수 사용으로 정렬 알고리즘 사용
- 테이블 크기가 클 때, 정렬 알고리즘의 혜택으로 풀 스캔보다 스캔 범위를 줄여줌
```
SELECT STATEMENT
	VIEW
		WINDOW SORT
			TABLE ACCESS FULL
```

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

인접 리스트 모델과 재귀 쿼리
- name, pcode(우편번호), new_pcode(이사하는 곳의 우편번호)
- 맨 처음 : (’a’, ‘1’, null) 추가
- 이사 시
  - (’a’, ‘1’, null) → (’a’, 1, 2) 변경
  - (’a’, 2, null) 추가
- 포인터 체인
  - 우편번호를 키 삼아, 데이터 연결한 것
  - 계층구조 표현하는 고전방법
  - 포인터 체인을 사용하는 테이블 = 인접 리스트 모델

SQL 에서 계층구조 나타내는 방법

재귀 공통 테이블 식

recursive union : 재귀 연산
계층 구조 깊이를 사전에 모르는 경우에도 사용 가능
재귀에 사용하는 explosion 뷰는 일시 테이블로 만듬

일시테이블(explosion) + 원본 테이블(postal_history)는 인덱스를 사용해 nested loops 수행

with recursive explosion (name, pcode, new_pcode, depth)
as
(select name, pcode, new_pcode, 1
	from postal_history
	where name = 'a' and new_pcode is null
union
select child.name, child.pcode, child.new_pcode, depth+1
	from explosion as parant, postal_history 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 lft rgt
  a 413001 0 27
  a 413002 9 18
  b 413103 0 27
  b 413104 8 17
- 데이터를 수치선 상에 존재하는 원으로 사고
- 좌표값은 대소 관계만 만족하면 실수값도 사용 가능
- 이사할 때마다 새로운 우편번호가 이전 우편번호 안에 포함되는 형태로 추가
- 새로 추가되는 우편번호 좌표
  - 왼쪽 좌표 = (plft * 2 + prgt) / 3
  - 오른쪽 좌표 = (plft + prgt * 2) / 3
- 가장 오래된 주소는 가장 바깥쪽에 있는 원 찾으면 됨.
  - 다른 원에 포함되지 않는 원
  - not exist 활용
```
select name, pcode
	from postal_history ph1
	where name = 'a'
		and not exists
			(select * from postal_history ph2
					where ph2.name = 'a'
						and ph1.lft > ph2.lft);
```
```
SELECT STATEMENT
	NESTED LOOPS ANTI
		TABLE ACCESS BY INDEX ROWID # postal_history2
			INDEX RANGE SCAN          # pk_name_pcode2
		TABLE ACCESS BY INDEX ROWID # postal_history2
			INDEX RANGE SCAN          # uq_name_lft
```

name	pcode	lft	rgt
a	413001	0	27
a	413002	9	18
b	413103	0	27
b	413104	8	17

17장. 바이어스의 공죄

DBMS 도 내부적으로는 절차 지향 언어로 동작
- 실제 물리 데이터 접근은 모두 절차 지향적 방법으로 수행
- nested loops도 절차 지향적 실행계획
하지만, 절차적 계층을 은폐한 것이 SQL 이념

애플리케이션 (절차 지향형)
SQL (집합 지향형)
실행계획 (절차 지향형)

정리

SQL 이념에 따라 반복문 의존증에서 벗어나, 집합 지향적 사고를 가져보자.
반복계는 성능, 튜닝 가능성에 단점
하지만, 무조건 나쁜건 아니다.

💡 상관 서브쿼리

서브쿼리 내부에서 외부쿼리와 결합조건 사용
해당 결합 키로 잘라진 부분집합 조작
실행계획 복잡해짐 → 성능 문제

💡 인덱스 온리 스캔

지원하는 DBMS가 따로 있음
select 구문에서 사용하는 필드에 모두 인덱스가 포함되어 있을때, 테이블 스캔 없이 인덱스 사용한 접근만 실행 가능
다만, SQL 구문에서 사용하는 필드가 적을때에만 사용 가능
인덱스 온리 스캔 사용 가능한 경우 드뭄

💡 재귀 공통 테이블 식 MySQL 실행계획

Id 컬럼
- select 쿼리 구분 용도 컬럼
- select 구문 몇 개인지 확인
- join으로 연결되어 있으면 같은 select 문으로 판단 (id 가 같음)
- union 을 사용하는 경우에는 다른 select 문으로 판단
select_type 컬럼
- PRIMARY
  - 하나만 존재
  - select 쿼리 중 메인이 되는 쿼리 (가장 바깥 쿼리)
- SUBQUERY
  - from 절 이외에 사용된 서브쿼리
  - 예제 sql 구문에는 그런 서브쿼리 없으므로 null
- UNION
  - union 사용한 경우 primary 제외한 다른 select
- DERIVED
  - union 사용한 경우 가장 처음 select 쿼리
- UNION RESULT
  - union 사용했을때 임시 테이블 → explosion 뷰
table 컬럼
- select 에 사용한 테이블 정보
- <> : 임시 테이블
  - union 결과 임시 테이블 explosion :
partitions 컬럼
- 테이블 위치한 파티션 정보
- 파티션 안 썼으므로 null
type 컬럼
- 실행계획이 인덱스를 참조했는지 여부
- ref : 동등 조건 사용된 경우 (인덱스 썼다는 의미)
- all : 테이블 풀 스캔 (인덱스 안 썼단 의미)
possible_keys 컬럼
- 후보 인덱스 목록
key 컬럼
- 실제 사용한 인덱스 목록
key_len 컬럼
- 다중 컬럼 인덱스 중에서 사용한 바이트 수
ref 컬럼
- 조건에 사용한 컬럼 번호
rows 컬럼
- 옵티마이저 비용 산정 위해 읽어야 하는 레코드 수
- 실제 값 아님. 예측값임.
filtered 컬럼
- 필터링 후 레코드가 얼마나 남아있는지 예측 값
extra 컬럼
- 실제 쿼리 풀어낸 정보

💡 왜 반복계는 병렬 처리에 부적합할까?

DB 분할 (Partitioning)
- 목적
  - 데이터 비대화에 따라 성능 개선 튜닝 기법 → 관리용이, 성능, 가용성 향상
- 종류
  - 수평 분할
    - 한 테이블의 행을 다른 테이블에 분산
    - 테이블 = 여자 레코드 + 남자 레코드 → 여자 테이블 + 남자 테이블 = 테이블 : 결합 뷰로 조회
  - 수직 분할
    - 테이블 일부 열을 다른 테이블로 분할 ⇒ 정규화
    - 자주 사용하는 열만 남기는 방식으로 많이 사용
      - 분리한 열을 포함한 조회에는 결합 때문에 성능 저하
      - 사용 빈도 높은 열에 대해서는 성능 향상
  - 범위 분할
    - 분할 키 값이 범위 내 있는지 여부로 구분
    - 순차적 데이터인 경우에 유효
    - 반복계에서도 유용하게 사용할 수 있을 것으로 예상
  - 목록 분할
    - 특정 열의 특정 값 기준으로 분할
    - nation 열이 있을 때, 각 대륙 별로 테이블 분할 가능
  - 해시 분할
    - 특정 열 값에 해시 함수 적용해 분할
    - 데이터 관리 측면 < 성능 향상 측면
    - 이후 범위 분할과 같은 동작
      - 다만, 범위 분할 보다 원만한 분포도
  - 합성 분할
    - 여러 분할 알고리즘을 결합해서 사용하는 분할
  - 라운드 로빈 분할
    - 데이터 고루 분할
    - 돌아가면서 여러 테이블에 골고루 데이터 저장
병렬 처리 최적화
- 병렬 실행 계획 선택
  - 쿼리 옵션, 힌트를 이용해 병렬 처리에 대해 옵티마이저에게 훈수를 둘 수 있다.
  - 근데, 반복계는 구문 자체에 튜닝이 불가하므로, 옵티마이저 훈수 못 함
- 병렬화 가능한 작업 식별
  - 병렬화는 대용량의 테이블 스캔, 복잡한 조인, 그룹화, 정렬 처럼 집합 규모 데이터 셋인 경우에 장점
  - 집합 관점 = 포장계
- 쿼리 분할, 작업 할당
  - 반복계는 분할 불가
- 튜닝과 반복
  - 반복된 병렬 처리 진행으로 옵티마이저 진화
  - 포장계인 경우 이러한 옵티마이저의 진화된 실행계획 수혜