게시판을 구현할 때, 체크 박스를 선택하고 삭제 버튼을 누를 경우, 선택한 데이터가 전부 삭제되는 기능을 구현한다 해보자.
보통 id를 가지고 하나의 데이터를 삭제하는 api를 구현한 뒤, 반복문을 돌며 삭제 메서드 or 삭제 api를 실행시키는 경우가 많을 것이다.
이렇게 우리는 흔히, 문제를 작게 나누어 레코드라는 단위에 이를 때까지 자르고, 그러한 레코드에 반복문을 적용해서 문제를 해결하려고 한다.
"문제가 생기면 일단 분할하고 보자"
"상향식으로 접근하자"
이런 접근방식을 절자 지향적인 사고방식이라 한다.
SQL에 반복이 없는 이유는 뭘까?
반복계: 한 행씩 반복문을 돌려 처리하는 SQL
포장계: 여러 행을 한꺼번에 처리하는 SQL
반복계의 단점
1. 성능
반복계의 처리 시간 = 처리횟수 * 한 회에 걸리는 처리 시간
한 회에 걸리는 시간이 일정하다고 가정하면, 반복계의 처리시간은 대상 레코드 수에 비례하게 커진다.
반면, 포장계는 인덱스를 사용한 접근이고, 실행 계획 변동이 없다고 한다면 완만한 커브를 그리며 커지게 된다.
2. SQL 실행의 오버헤드
SQL을 실행할 때는 데이터를 검색하거나 연산하는 실제의 SQL 처리 이외에도 다양한 처리가 이루어진다.
* 전처리1. SQL 구문을 네트워크로 전송2. 데이터베이스 연결(-> 최근에는 애플리케이션에서 커넥션 풀이라는 기술 사용)
3. SQL 구문 파스 (구문 분석)
4. SQL 구문의 실행 계획 생성 or 평가
* 후처리1. SQL 결과 집합을 네트워크로 전송
구문 파스는 데이터베이스가 SQL을 받을 때마다 실행되므로 작은 SQL을 여러번 반복하는 반복계에서는 오버헤드가 높아질 수 밖에 없다.
SQL구문을 실행하는데 드는 이런 오버헤드라는 품은 일반적으로 SQL 구문의 복잡성이나 길이 등에 비례하지 않기 때문에 최종적으로 같은 규모의 일을 한다면 단순한 SQL을 여러개 처리하는 것보다 복잡한 하나를 처리하는 것이 낫다.
3. 병렬 분산이 힘들다
데이터베이스 서버 저장소는 대부분 RAID 디스크로 구성되어 I/O 부하를 분산할 수 있게 되어있다.
하지만 반복계는 반복 1회마다의 처리를 굉장히 단순화하므로, 리소스를 분산해서 병렬 처리하는 최적화가 안된다.
예를 들어, cpu의 멀티 코어로 분산 처리를 할 수 없고, 저장소의 분산 효율도 낮다.
-> 애플리케이션 측에서 루프를 다중화하면 되지 않을까?
4. 데이터베이스의 진화로 인한 혜택을 받을 수 없다
( = 느린 구문을 튜닝할 수 있는 가능성이 거의 없다 )
DBMS의 버전이 오를수록 옵티마이저는 보다 효율적으로 실행 게획을 세우며, 데이터에 고속으로 접근할 수 있는 아키텍처를 구현한다. 이는, '대규모 데이터를 다루는 복잡한 SQL 구문'을 빠르게 하려는 데 있다.
반복계의 SQL구문을 대충 스케일업한다고 해서, (성능 저하의 원인이 물리 소스가 바틀넥이 걸리는 경우여서가 아닌 이상) 속도가 빨라지지 않는 경우가 많다.
* 스케일업이란? (단일)서버의 성능을 향상시키기 위해 더 좋은 하드웨어를 사용하는 방식
ex) 더 많은 CPU 코어, 더 많은 RAM, 더 빠른 디스크를 추가
<-> 스케일아웃 : 서버 개수를 추가
* 바틀넥이란? 시스템에서 성능을 제한하는 지점 ;시스템의 전체 처리 성능이 한 지점의 성능에 의해 제한되는 상황
ex)
cpu: 프로세서의 처리 능력이 더 이상 작업을 처리할 수 없을 때
메모리: 데이터가 메모리에 적재되거나, 메모리가 부족해서 처리 속도가 느려질 때
디스크 I/O : 데이터가 디스크에서 읽고 쓰는 속도가 처리 속도를 제한할 때
네트워크: 데이터가 네트워크를 통해 전송될 때 네트워크 속도가 느려 병목이 되는 경우
이러한 비교는 포장계의 SQL이 충분히 튜닝되어 있다는 것이 전제되어야 한다.
일반적으로 포장계의 SQL은 반복계에 비해 굉장히 복잡하다.
따라서 튜닝되지 않은 상태에서는 반복계에 질 수 있다.
다만 포장계의 SQL 구문은 튜닝 가능성이 굉장히 높을 뿐이다.
반복계의 장점 ( = 단순한 SQL 구문의 장점)
1. sql 처리를 단순화 할 수 있다.
2. 실행 계획의 안정성
SQL 구문 내부에서 결합을 사용하지 않으면 실행 계획 변동에 가장 치명적인 결합 알고리즘 변경이 발생하지 않아 실행 계획의 변동 가능성이 작다.
이에 따라 실제 운용 중에 갑자기 실행 계획이 바뀌어 느려지는 현상 등이 일어나지 않고 안정적인 성능을 확보할 수 있다.
실행 계획 변동이 쉬운 SQL 구문에 대해서는 조금 단순한 구문을 사용하는 것이 좋다
3. 예상 처리 시간의 정밀도
4. 트랜잭션 제어가 편리
트랜잭션의 정밀도를 미세하게 제어할 수 있다.
예를 들어, UPDATE 구문을 처리하는 반복계에서 특정 반복 횟수마다 커밋을 할 경우, 중간에 오류가 발생한다 해도 커밋 이 이후의 실패 근처 지점에서 다시 처리를 실행하면 된다.
이러한 이유로 처리를 도중에 중단하는 것도 수월하다.
반면, 포장계에서는 갱신 처리 중간에 오류가 발생하면, 처리를 처음부터 다시 실행해야 한다.
반복계를 빠르게 만드는 방법은 없을까?
1. 반복계를 포장계로 다시 작성
2. 각각의 SQL을 빠르게 수정
절차 지향적 사고를 가진 나 같은 개발자들이 가장 많이 채택하는 방식이다.
다만, 반복계에서 사용하는 SQL 구문은 너무 단순하기 때문에 튜닝이 어렵다는 문제가 있다.
(실행 계획을 보아도 unique scan 또는 index range scan 정도 뿐이다.)
또한, INSERT 구문을 반복하는 경우엔, SELECT 구문보다 고속화가 어려워 튜닝 가능성이 더욱 제한된다.
* INSERT 구문의 고속화가 어려운 이유:
실행계획이 너무 단순해서 실행 계획을 조작하는 튜닝을 할 수 없다. 조금 더 빠르게 만든다 해도 겨우 커밋을 확장하는 정도 뿐이다.
* BULK INSERT (포장계):
- 네트워크 오버헤드 절감
- 디스크 I/O 최적화 : 여러 개의 INSERT를 하나의 트랜잭션으로 묶어서 실행하면 디스크에서 데이터를 읽고 쓰는 횟수를 줄일 수 있다.
다만, 트랜잭션 크기가 커질 수록 DB의 부하가 증가할 수 있으므로 잘 조정해야 한다.
3. 다중화 처리
CPU 또는 디스크와 같은 리소스에 여유가 있고, 처리를 나눌 수 있는 키가 명확하게 정해져 있다면
처리를 다중화해서 성능을 선형에 가깝게 스케일할 수 있다.
처음부터 다중도를 설정할 수 있게 애플리케이션을 구성했다면 코드를 변경하지 않고도 확장이 가능하다.
하지만,
- 데이터를 분할할 수 있는 명확한 키가 없거나
- 순서가 중요한 처리
- 병렬화했을 때 물리 리소스가 부족하다면
사용할 수 없는 방법이다.
그렇다면 SQL에서는 반복계를 어떻게 처리할까? 3가지 예시를 통해, 포장계로 처리하는 방법에 대해 알아보자
반복계를 포장계로 처리하는 방법
상황1 :
각 기업의 매해 직전 매출을 전부 조회하려는 경우
1-1. 반복계
코드 상으로 각 기업별 (기업id, 직전 매출)을 조회하려면
- 기업id, year을 기준으로 오름차순 정렬
- 테이블 전체 조회
- row를 하나씩 순회
- 현재 레코드 sale을 변수명에 저장
- 다음 레코드의 company_id와 동일하다면, 변수명에 저장된 sale을 추출하고, 이번 레코드의 sale로 변수 재할당
- 마지막 row 까지 진행
→ 애플리케이션이 하나씩 데이터를 읽어 처리하는 것과는 관계 없이 데이터는 데이터베이스에서 전체 테이블을 풀 스캔하여 가져오므로 조회 성능은 테이블 풀 스캔으로 보면 될 것 같다.
1-2. 윈도우 함수
select COMPANY_ID,
year,
sale,
MAX(COMPANY_ID)
over (partition by COMPANY_ID order by year rows between 1 preceding and 1 preceding) as pre_company_id,
max(sale)
over (partition by COMPANY_ID order by year rows between 1 preceding and 1 preceding ) as pre_sale
from SALES;
- index full scan 을 사용하여 인덱스 전체 스캔 (모든 데이터가 아니라 인덱스에 저장된 검색 키 값만 빠르게 스캔)
- 인덱스에서 반환된 rowid 를 사용하여 실제 테이블에서 해당 데이터 조회
테이블 스캔이 한번밖에 이뤄지지 않는 이유를 알려면 윈도우 함수의 작동방식을 알아야 한다.
- 전체 테이블 데이터를 메모리에 올린다.
- company_id 별로 데이터를 그룹화한다.
- 각 company_id 그룹 내에서 year 순으로 데이터를 정렬한다.
- 윈도우 함수 계산을 수행한다.
- company_id 별 그룹 내에서
- year 순으로 정렬된 상태에서
- 현재 행의 바로 이전 행의 company_id 를 가져온다.
1-3. 상관 서브쿼리
(: select 절에 서브쿼리를 활용하면 윈도우 함수를 사용했을 때와 같은 결과를 얻을 수 있다)
SELECT company_id,
year,
sale,
-- 현재 행의 company_id와 같은 company_id를 가지면서, 현재 행의 year보다 작은
-- 최대 year를 가진 행의 company_id를 선택
(SELECT company_id
FROM Sales S2
WHERE S1.company_id = S2.company_id
AND S2.year = (SELECT MAX(S3.year)
FROM Sales S3
WHERE S1.company_id = S3.company_id
AND S1.year > S3.year)) AS pre_company_id,
(SELECT sale
FROM Sales S2 -- index unique scan; (company_id, year)이 index
WHERE S1.company_id = S2.company_id
AND S2.year = (SELECT MAX(S3.year) -- index range scan
FROM Sales S3
WHERE S1.company_id = S3.company_id
AND S1.year > S3.year)) AS pre_sale
FROM Sales S1;
1. 한번의 테이블 풀 스캔과 한번의 index rowid 를 기반으로 한 테이블 스캔이 일어났다.
→ MAX, MIN 함수를 사용해야하므로 두번쨰, 세번째의 데이터를 구하는 것은 어렵다
→ 윈도우 함수의 경우 테이블을 1회 스캔하지만, 서브쿼리를 사용할 경우 2회 스캔 ;
→ 윈도우 함수를 활용한 쿼리가 훨씬 보기 간편하고, 작성하기 편리하다.
상황2 : 최대 반복 횟수가 정해진 경우
7자리의 우편번호가 있을 때, 하위 자릿수까지 일치할 수록 가까운 지역임을 나타낸다.
입력받은 우편번호와 가장 가까운 우편번호를 검색하려면??
* 여러 개의 우편번호가 답이 될 수 있다.
2-1. 반복계
select * from POSTALCODE where PCODE like '1%';- 일치하는 우편번호 있는지 검색
- 413003* 데이터 검색
- 41300** 데이터 검색
- 반복
- 테이블 전체에 like 조건을 수정한 쿼리를 최대 7번 실행하여 답을 찾을 수 있다.
→ 패턴이 앞에 나와서 index를 탈 수 있는 경우에는 range scan으로 테이블 스캔이 가능하지만 '%1%', '%1' 과 같은 조건을 사용해야 할 경우 테이블 풀 스캔을 해야 한다
→ PCODE의 길이가 길어질 수록 반복횟수가 증가하여 성능이 저하된다.
2-2. CASE 식 (WHERE 절)
가장 가까운 우편번호 순위를 0, 멀수록 큰 수를 가지는 순위를 붙여서 해결한다
7회 반복 -> 7회 CASE 식 분기로 변환
SELECT pcode,
district_name
FROM POSTALCODE
WHERE -- case when 으로 평가된 값이 서브쿼리에서 반환된 가장 작은 값과 같은지를 비교
-- 일치하는 레코드 반환
CASE -- pcode를 점점 일반적인 패턴으로 확장하며 우선순위를 부여
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
);
→ 순위의 최솟값을 서브쿼리에서 찾기 때문에 테이블 full scan이외에도, MIN값을 찾기 위한 index fast full scan이 1번 일어난다.
2-3. 윈도우 함수 (FROM 절)
* 인덱스 온리 스캔 (10장 참고)
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 '48%' 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 '48%' 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 '48%' THEN 6
ELSE NULL
END) AS min_code
FROM PostalCode
) Foo
WHERE hit_code = min_code;
→ 윈도우 함수를 사용하면 테이블 스캔 횟수를 줄일 수 있다
→ 다만, 정렬이 추가로 사용되게 되어 비용이 추가된다.
* 정렬 vs. 테이블 full scan
윈도우 함수는 행과 행 간의 관계를 다루는 함수이기 때문에 윈도우 함수를 사용하면 기본적으로 정렬(order by , partition by)의 과정이 생기게 된다.
정렬 역시도 비용이 발생하기 때문에 테이블 스캔 횟수가 적다는 이유로 무조건 윈도우 함수를 선택하면 안된다.
윈도우 함수를 잘 사용하기 위해서는 불필요한 정렬을 줄이는 것이 좋고 (스캔을 해야 할 횟수를 줄이고)
테이블 크기가 큰 경우 테이블 full scan 비용 > 정렬 비용 이기 때문에 테이블 full scan을 줄이는 방식을 선택하는 것이 좋다.
상황3 : 반복 횟수가 정해지지 않은 경우 (반복을 분기로 변경할 수 없는 상황)
우편번호 이력을 관리하는 테이블이 있을 때, A씨가 가장 오래전에 살았던 주소를 검색한다면?
3-1. 반복계
- 코드
- 현재 주소(NEW_PCODE IS NULL)의 레코드를 찾아 해당 레코드의 PCODE를 변수에 담고
- NEW_PCODE 가 변수에 담긴 값과 같은 레코드 탐색
- 찾으면 1~2과정 반복
- NEW_PCODE가 변수에 담긴 값과 같은 레코드가 없으면 반복 종료
- NAME 개수만큼 반복문 진행
→ 중첩 반복문으로 레코드 수 커질수록 성능 악화
--1
select PCODE
from POSTALHISTORY
where name='A' and new_pcode is null;
--2
select pcode
from POSTALHISTORY
where name='A' and new_pcode = '4130002';
3-2. 재귀 공통 테이블 식 ( Recursive Common Table Expression, CTE )
재귀 공통 테이블 식 Explosion은, A씨에 대해서 현재 주소(new_pcode 필드가 NULL)부터 출발해서 주소 변경의 연결 고리를 따라가며 과거의 주소를 모두 찾는다.
이때 가장 오래된 주소는 재귀 수준이 가장 깊은 레코드이므로, 이를 depth 필드로 찾는다.
(dept 필드가 가장 큰것이 가장 재귀 수준이 깊다는 것)
-- Explosion이라는 가상 테이블 생성
-- 2개의 쿼리 블록으로 구성: 앵커(Anchor), 재귀(Recursive)
WITH Explosion (name, pcode, new_pcode, depth) AS (
-- 기본 조건(앵커 멤버): A씨의 주소, new_pcode가 NULL인 주소부터 시작
SELECT name, pcode, new_pcode, 1 AS depth
FROM PostalHistory
WHERE name = 'A' AND new_pcode IS NULL
UNION ALL
-- 재귀 조건(재귀 멤버): pcode와 new_pcode가 연결되는 주소를 계속 찾아가며 depth를 증가시킴
SELECT Child.name, Child.pcode, Child.new_pcode, Parent.depth + 1
FROM Explosion Parent
JOIN PostalHistory Child ON Parent.pcode = Child.new_pcode
WHERE Parent.name = Child.name
)
-- 가장 깊은(depth가 가장 큰) 주소를 찾음
SELECT name, pcode, new_pcode
FROM Explosion
WHERE depth = (SELECT MAX(depth) FROM Explosion);
- ID 0: 전체 쿼리의 시작.
- ID 1: 임시 테이블을 사용하여 중간 결과를 저장.
- ID 2: 메모리 내 임시 테이블 생성.
- ID 3: 재귀적 쿼리에서 너비 우선 탐색.
- ID 4: 테이블을 전체 스캔하여 초기 데이터를 찾기.
- ID 5-9: 재귀적으로 연결된 주소들을 찾는 과정.
- ID 10: 임시 테이블을 처리하는 뷰 생성.
- ID 11: 임시 테이블에서 데이터 읽기.
- ID 12: 집계 연산 (최대 깊이 계산).
- ID 13: 뷰를 사용하여 최종 데이터를 결합.
- ID 14: 다시 임시 테이블을 풀 스캔.
* Temp Table Transformation :
복잡한 쿼리를 실행할 때, 오라클이 효율적으로 데이터를 처리하기 위해 임시 테이블을 사용하는 과정에서 발생한다.
임시 테이블을 생성하여 데이터를 저장하고, 나중에 그 데이터를 다시 조회하는 방식으로 사용된다.
이를 통해 메모리 효율성을 높이고, I/O 작업을 줄이는 등 최적화를 할 수 있다.
→ Explosion View에 여러 번 접근하므로 임시 테이블로 생성
* Cursor Duration Memory:
테이블이 메모리에만 존재한다는 의미; 쿼리 실행동안만 사용되고 종료되면 사라진다.
* Nested Loops (중첩 루프 조인 방식):
하나의 테이블에서 행을 가져오고, 그 행에 대해 다른 테이블에서 일치하는 행을 찾는 방식
→ 표준 SQL에 포함되어 있는 내용이므로 구현에 의존적이지 않다
→ 비교적 최근에 만들어진 기능이므로 실행 계획이 최적화되지 않은 DBMS가 있을 수 있다
3-3. 중첩 집합 모델
→ 조회 성능을 위해 테이블에 불필요한 컬럼을 추가한다는 점과 쿼리 및 코드를 보고 어떤 로직인지 알기 어렵다는 점에서 지양해야할 방식이라고 생각된다.
장단점, 트레이드 오프에 대한 명확한 인지를 통해, 상황에 따라 적절한 방식을 채택할 수 있도록 해야겠다
'Database' 카테고리의 다른 글
| [ 8장 ] SQL의 순서 (0) | 2024.12.17 |
|---|---|
| Rollback, UNDO (0) | 2024.12.06 |
