프로그래밍을 할 때, 특정 순서를 가진 데이터를 다루는 일이 자주 있습니다.
SQL 에서는 어떻게 순번을 구하는 지 살펴보겠습니다.
SQL로 레코드에 순번 붙이는 방법
1. 기본 키가 한 개의 필드일 경우
다음과 같이 학생의 체중을 저장하는 테이블이 있습니다.
체중을 기준으로 순번을 붙이려면 어떻게 할까요?
1-1. 윈도우 함수
윈도우 함수의 row_number() 함수를 사용하면 다음과 같이 쉽게 순번을 붙일 수 있습니다.
select STUDENT_ID,
row_number() over (order by STUDENT_ID) as seq
from WEIGHTS;
student_id에 index가 설정되어 있기 때문에, 따로 정렬작업 없이 한번의 index full scan으로 순번을 매길 수 있습니다.
1-2. 상관 서브 쿼리
row_number() 메서드를 제공하지 않는 경우, 상관 서브 쿼리를 사용해야 하는데요
select STUDENT_ID,
(select count(*)
from WEIGHTS w2
where w2.STUDENT_ID <= w1.student_id) as seq
from weights w1;
이 경우, count(*)를 구하기 위한 1번의 index range scan 과정이 실행계획에 추가됩니다.
즉, 서브쿼리에서 범위 조건을 만족하는 데이터를 찾기 위해 index range scan이 사용되고,
대신 메인 쿼리에서는 해당하는 데이터의 index 만 읽고, count(*)를 계산하면 되기 때문에 index fast full scan 이 사용됩니다.
* row_number()은 표준 문법입니다.
* 참고:
SELECT STATEMENT의 비용은 전체 쿼리의 최종 실행 비용을 나타내는 것 같지만, 실제로는 쿼리의 최상위 연산에 대한 비용만 포함되어 있습니다.
즉, 서브쿼리나 중간 연산 단계(예: INDEX RANGE SCAN, SORT AGGREGATE 등)에서 발생하는 비용은 각각 개별적으로 표시되며, SELECT STATEMENT는 이들의 합이 아니라 최상위 연산의 비용을 나타냅니다.
다중 연산이 이루어지는 방식:
쿼리는 여러 연산을 거쳐 실행됩니다. 예를 들어, 서브쿼리에서 INDEX RANGE SCAN이 실행되고, 그 결과를 기반으로 SORT AGGREGATE가 이루어지며, 최종적으로 SELECT 연산이 수행됩니다.
각 연산의 비용은 독립적으로 계산되며, 이들의 비용을 합산한 전체 비용은 표시되지 않습니다. 대신 각 연산의 비용을 따로따로 보여주고, SELECT STATEMENT에는 최상위 연산의 비용만 나타냅니다.
2. 기본 키가 여러 개의 필드로 구성되는 경우
이번에는 기본 키가 두 개인 경우를 예로 들겠습니다.
반별 학생들의 체중을 저장하는 테이블입니다.
학급(class)과 학생ID(student_id)가 복합키입니다.
2-1. 윈도우 함수
정렬 기준이 2가지로 바뀌었으나, 인덱스 또한 복합인덱스이기 때문에 기본키가 하나의 필드일 때와 거의 유사하다고 볼 수 있습니다.
select class,
STUDENT_ID,
row_number() over (order by class, STUDENT_ID) as seq
from WEIGHTS2;
2-2. 상관 서브쿼리
오라클에서는 첫번째처럼 두 개의 컬럼을 동시에 비교하는 방식을 지원하지 않기 때문에, 상관 서브쿼리를 사용한다면 2번째와 같이 작성할 수 있습니다.
--Oracle SQL에서는 두 개의 컬럼을 동시에 비교하는 방식을 지원하지 않습니다
select class,
student_id,
(select count(*)
from WEIGHTS2 w2
where (w2.class, w2.STUDENT_ID) < (w1.class, w1.STUDENT_ID)) as seq
from WEIGHTS2 w1;--대체
SELECT class,
student_id,
(SELECT count(*)+1
FROM WEIGHTS2 w2
WHERE w2.class < w1.class
OR (w2.class = w1.class AND w2.STUDENT_ID < w1.STUDENT_ID)) AS seq
FROM WEIGHTS2 w1;
다만, 이 경우 조건이 복잡해지면서 상관 서브쿼리에서는 index range scan이 아닌 index full scan이 사용된 것을 확인할 수 있습니다.
3. 테이블을 그룹으로 분할했을 때, 그룹 내부의 레코드에 순번을 붙이는 경우
반별 학생들의 체중을 나타낸 테이블에서, 반마다 순번을 붙이는 경우엔 어떨까요?
테이블을 그룹으로 나누고 그룹마다 내부 레코드에 순번을 붙여야 합니다.
3-1. 윈도우 함수
윈도우 함수를 사용하면, 요구사항 그대로 반 별로 그룹을 나눈 후, student_id에 따라 정렬을 하여 순위를 매겨주면 됩니다.
select class,
STUDENT_ID,
row_number() over (partition by class order by STUDENT_ID) as seq
from WEIGHTS2;
윈도우 함수는 정렬이 대부분 사용되지만, 정렬 조건이 인덱스라면 추가로 정렬 비용이 들지 않는다는 점을 알 수 있습니다.
3-2. 상관 서브쿼리
1번 예시와 비슷한 결과입니다.
select class,
STUDENT_ID,
(select count(*)
from WEIGHTS2 w2
where w2.class = w1.class
and w2.STUDENT_ID <= w1.student_id) as seq
from WEIGHTS2 w1;
4. 순위를 계산한 후, 저장하는 경우
지금까지는 순위를 "검색"하는 방법이었다면, 갱신에서 순번을 매기는 방법은 어떨까요?
순번 검색에서 그치는 것이 아니라 테이블에 저장까지 해야하는 경우, 실행계획에 어떤 변화가 있는지 살펴보겠습니다.
4-1. 윈도우 함수
윈도우 함수로 조회한 결과를 set 구에 넣어주면 됩니다.
다만, class 와 student_id 조건에 맞는 곳에 seq값을 넣어줘야하기 때문에, SeqTb1이라는 서브쿼리를 만들어야 where 조건을 작성할 수 있습니다.
update WEIGHTS2
set seq = (select seq
from (select class, STUDENT_ID, row_number() over (partition by class order by STUDENT_ID) as seq
from WEIGHTS2) SeqTb1 -- SeqTb1라는 서브쿼리를 만들어야 함
where WEIGHTS2.class = SeqTb1.CLASS
and WEIGHTS2.student_id = SeqTb1.STUDENT_ID);
서브 쿼리로 생성된 SeqTb1 과 WEIGHTS2 테이블을 class와 student_id를 기준으로 병합하여 각 학생의 seq 값을 업데이트 한다는 내용입니다.
실행계획을 한눈에 비교해보면 다음과 같습니다.
| 조회 | 갱신 |
|
|
순위를 매기는 과정이 동일하다 하여도,
row_number() 함수 계산을 위한 데이터를 준비하기 위해, 전체 테이블 스캔이 발생하고,
row_number() 함수의 계산 결과를 update 쿼리에 사용하기 위해 view 가 사용된다는 점을 알 수 있습니다.
또한 순번을 매기기 위해, window buffer도 사용됩니다.
즉, select 쿼리에서는 순위를 출력하는 것만 중요하고, 실제로 데이터를 수정하지 않기 때문에 정렬된 상태로 출력할 필요가 없었습니다. (순위를 메기는 기준인 student_id가 이미 정렬된 상태였기 때문에 내부적으로 정렬이 필요하지 않았음)
하지만, 갱신 과정에서는 순위를 계산하고, 각 행에 대해 업데이트를 해야 하므로, 정렬된 상태로 데이터를 처리하고 그 값을 업데이트 하기 위해 정렬이 필수적으로 필요한 것 같습니다.
* Window Buffer:
오라클이 내부적으로 사용하는 메모리 공간으로, 주로 윈도우 함수 계산에 필요한 데이터를 임시로 저장하는 역할을 한다.
버퍼는 메모리 상에서 동작하며, 디스크에 저장되는 데이터가 아니므로 상대적으로 빠르다.
row_number()은 이전 행들을 참조하며 순번을 매기므로 이러한 계산을 위한 중간 데이터 저장공간으로 window buffer가 사용된다.
버퍼는 쿼리 성능에 영향을 줄 수 있으므로, 데이터 양이 많은 경우 메모리 사용량에 유의할 필요가 있다.
4-2. 상관 서브쿼리
상관 서브쿼리를 사용하는 경우에는 그대로 set 구에 넣어주면 됩니다.
update WEIGHTS2
set seq = (select count(*)
from WEIGHTS2 w2
where w2.class = WEIGHTS2.class
and w2.STUDENT_ID <= WEIGHTS2.STUDENT_ID);
실행계획을 한눈에 비교해보면 다음과 같습니다.
| 조회 | 갱신 |
|
|
조회과정에서는 index를 이용하여 조회가 가능했으나,
update 과정에선 seq에 index 가 걸려있지 않기 때문에 table full scan이 이루어지고 있습니다.
이 점을 빼면, 순위를 구하는 실행계획은 동일합니다.
레코드에 순번 붙이기 응용
레코드에 순번을 붙일 수 있다면, 이를 활용하여 다양한 테크닉을 사용할 수도 있습니다.
1. 중앙값 구하기
1-1. 집합 지향적 방법 (모집합을 상위와 하위로 분할)
테이블을 상위 집합과 하위 집합으로 분할하고 그 공통 부분을 검색하여 구할 수 있습니다.
하나의 테이블을 두 개의 집합으로 나눈 후,
데이터가 홀수라면, 교집합이 하나,
데이터가 짝수라면, 교집합이 2개가 나오도록 구합니다.
-- 공통 부분을 검색하기 위함 (w2는 기준, w1가 대상)
select avg(weight) -- 레코드 수가 짝수일 경우 대비
from (select w1.WEIGHT
from WEIGHTS w1,
WEIGHTS w2
group by w1.WEIGHT
-- weight가 작은 집합의 조건(S1)
having sum(
case when w2.WEIGHT >= w1.WEIGHT then 1 else 0 end --작을 수록 높은 순위
) >= count(*) / 2
-- weight가 큰 집합의 조건(S2)
and sum(
case when w2.weight <= w1.weight then 1 else 0 end --클 수록 높은 순위
) >= count(*) / 2) tmp;
→ 문제점:
- 코드가 복잡해서 무엇을 하고 있는지 한번에 이해하기 힘들다
- 성능이 나쁘다: 중앙값을 구하기 위해 결합을 수행한다 (비용 높고, 불안정)
1-2. 절차 지향적 방법 (양쪽 끝에서 레코드 하나씩 세어 중간을 찾기)
집합 지향적인 방법과 논리는 유사합니다. 단, 윈도우 함수로 쿼리를 단순하고 가독성 좋게 구현할 수 있습니다.
select avg(weight) as median
from (select weight,
row_number() over (order by weight asc, STUDENT_ID asc) as hi,
row_number() over (order by weight desc, STUDENT_ID desc) as lo
from WEIGHTS) TMP
where hi IN (lo, lo + 1, lo - 1);
집합 지향적 코드와 비교하면, 결합을 제거한 대신 정렬이 1회 늘어난 것을 확인할 수 있습니다.
가독성을 제외하고도, 성능을 개선할 수 있지만 이 방법 역시 문제가 있습니다.
→ 문제점:
- 순번을 붙일 때, row_number 만 사용해야 한다.
비슷한 기능을 하는 rank, dense_rank 함수는 '7위 다음에 9위'라든지, ''11위가 2명'이 되는 경우가 발생할 수 있어서 사용할 수 없다.
- order by 정렬 키에 weight 필드 뿐만 아니라 기본 키인 student_id도 포함해야 한다.
1-3. 절차 지향적 방법 (반환점 발견)
순번에 2배를 곱한 값에 최대순번을 빼도, 중앙값을 구할 수 있습니다.
select weight, 2 * row_number() over (order by weight) - count(*) over ( ) as diff
from WEIGHTS;
select avg(weight)
from (select weight, 2 * row_number() over (order by weight) - count(*) over ( ) as diff
from WEIGHTS) tmp
where diff between 0 and 2;
→ 문제점:
- 풀 스캔 한번으로 중앙값을 구할 수 있지만, 로직을 알아보기 어렵다
2. 순번을 사용한 테이블 분할
데이터를 파티셔닝해야할 때, 순번을 사용하여 테이블을 분할해야할 수도 있습니다.
순번을 통해, 단절된 구간을 SQL로 구하는 방법을 살펴보겠습니다.
2-1. 집합 지향적 방법 (집합의 경계선)
단절 구간을 찾으려면, NUM을 순서대로 정렬한 후, NUM의 다음 값이 NUM에 없다면(NUM에 있는 수-1)까지의 범위를 구하면 되는데요.
이에 따라 집합 지향적인 방법에서는, 자기 결합을 통해, 자신보다 큰 값들을 모두 조회한 후
select *
from Numbers N1
inner join NUMBERS N2
on N1.num < N2.num;
그렇게 조인된 데이터들을 N1.num으로 group by 하고 조건에 맞는 num값을 구하면,
아래와 같이 단절구간을 구할 수 있습니다.
select (N1.num + 1) AS gap_start, (MIN(N2.num) - 1) AS gap_end
from Numbers N1
inner join NUMBERS N2
on N1.num < N2.num
group by N1.num
having (N1.num + 1) =< MIN(N2.num)-1
;
실행계획을 보면, NUMBERS 테이블에 대해 2번의 index full scan과 정렬된 데이터를 기반으로 한 merge join을 통해 결과를 구한 것을 확인할 수 있습니다.
구체적으로 살펴보면,
merge join 은 두 테이블을 num 값 기준으로 비교하면서 조인할 때 선택되었으며
sort join은 n2 테이블을 정렬하여 효율적으로 n1.num < n2.num 조건을 만족하는 데이터를 찾기 위해 사용되었습니다.
index full scan이 두 번 발생하는 이유는 n1.num 과 n2.num 각각에 대해 인덱스를 사용하여 데이터를 읽어오기 때문입니다.
범위기반 조건(n1.num < n2.num)이 있고, num에 인덱스가 걸려있기 때문에 옵티마이저가 merge join을 선택한 것으로 보여집니다.
2-2. 절차 지향적 방법 ('다음 레코드'와 비교)
반복문을 사용하듯, 현재 레코드와 다음 레코드의 숫자 차이를 비교하고 차이가 1이 아닌지 확인하여 단절구간을 구할 수도 있습니다.
-- 현재 레코드와 다음 레코드의 숫자 차이를 비교하고 차이가 1이 아닌지 확인
select num + 1 as gap_start,
(num + diff - 1) as gap_end
from (select num,
-- 현재 레코드와 다음 레코드 간의 값의 차이
MAX(num) over ( order by num rows between 1 following and 1 following ) - num as diff
from Numbers) TMP
where diff <> 1; -- diff가 1이 아닌 경우
num값을 메모리에 올리기 위해 index full scan이 일어나고,
윈도우 함수 계산을 위해 window buffer 가 사용되었으며,
상관 서브쿼리로 인해 view 가 사용된 것을 확인할 수 있습니다.
표준 SQL에는 순번을 다루는 기능으로 시퀀셜 객체와 IDENTITY 필드가 존재하는데요,
각각의 특징에 대해서도 알아보겠습니다.
시퀀스 객체 vs IDENTITY 필드 vs 채번 테이블
1. 시퀀스 객체
시퀀스 객체는 SQL 구문 내부에 접근해 수열을 생성합니다.
이름 그대로, 테이블, 뷰처럼 스키마 내부에 존재하는 객체의 한 종류이기 때문에, create 구문으로 생성할 수 있습니다.
시퀀스명.next_val 과 같은 방식으로 만들어진 순번을 기본 키로 사용해 레코드를 INSERT 할 때 자주 사용됩니다.
그렇다면 이런 시퀀스 객체의 문제점은 무엇일까요?
1. 표준화가 늦어서, 구현에 따라 구문이 달라 이식성이 없고, 사용할 수 없는 구현도 있다.
2. 시스템에서 자동으로 생성되는 값이므로 실제 엔티티 속성이 아니다.
3. 성능적인 문제를 일으킨다.
저희는 성능과 관련한 문제에 주목하고 있으므로, 성능 문제를 자세히 살펴보겠습니다.
시퀀스 객체로 발생하는 성능 문제
1. 시퀀스 객체의 로직에 의존하게 된다.
시퀀스 객체가 생성하는 순번은
- 유일성
- 연속성
- 순서성
이라는 특징을 갖습니다.
그래서 시퀀스 객체는 이런 성질을 만족하는 순번을 생성하기 위해, 동시 실행 제어를 위한 락 메커니즘이 필요합니다.
만약 어떤 사용자가 시퀀스 객체를 사용하고 있다면, 시퀀스 객체를 lock 해서 다른 사용자로부터의 접근을 블록하는 배타 제어를 수행해야 하는 것이죠.
이때문에 동시에 여러 사용자가 시퀀스 객체에 접근하는 경우 락 충돌로 인한 성능 저하 문제가 발생하게 됩니다.
또한 어떤 사용자가 연속적으로 시퀀스 객체를 사용하는 경우에는 불필요한 오버헤드가 발생합니다.
2. 순번을 키로 사용할 때의 성능 문제
사실, 이 문제는 dbms의 물리적인 저장 방식 때문에 발생합니다. 따라서 순번 또는 시간처럼 연속된 데이터를 다룰 경우 공통적으로 발생하는 문제점이라고 할 수 있습니다.
시퀀스 객체를 사용할 경우, 저장소의 특정 물리적 블록에만 I/O 부하가 커지므로 성능 악화가 발생할 수 있는데요,
이러한 부분을 핫 스팟 or 핫 블록이라고 부릅니다.
하지만 다행히도, DBMS는 시퀀스 객체의 이러한 성능 문제를 보완하는 방법 역시 제공합니다.
시퀀스 객체로 발생하는 성능 문제를 보완하는 법 (성능 튜닝 방법)
1. CACHE
새로운 값이 필요할 때마다, 메모리에 읽어들일 필요가 있는 값의 수(한번에 읽어들일 값의 수)를 설정하는 옵션을 통해, 접근 비용을 줄일 수 있습니다.
다만 부작용으론, 시스템 장애가 발생할 때 연속성을 담보할 수 없습니다.
2. NOORDER 객체
순서성을 담보하지 않아서 오버 헤드를 줄이는 효과가 있습니다. (해당 객체를 한명이 여러번 사용할 경우)
다만, 순서성을 담보하고 싶을 경우에는 사용할 수 없습니다.
3. DBMS 내부에서 변화를 주어 제대로 분산할 수 있는 구조 ( = 일종의 해시 ) 를 사용
= 역 키 인덱스
insert 구문 자체는 빨라지나, 범위 검색 등에서 I/O 양이 늘어나 select 구문의 성능이 나빠질 위험이 있습니다.
조회가 중요한 서비스에선 사용하기 어려운 방법입니다.
4. 인덱스에 일부러 복잡한 필드를 추가해서 데이터의 분산도 높이기
seq 뿐 아니라 emp_id 등을 같이 묶어 복합 인덱스로 만드는 방법도 있습니다.
다만, 구현 의존적인 방법이어서 지양하는 것이 좋을 것 같습니다. 일단, 인덱스에 불필요한 필드를 키로 추가한다는 것은 좋은 설계가 아니고, 나중에 해당 설계를 보는 다른 엔지니어들이 이해하기 어렵기 때문입니다.
2. IDENTITY 필드
일종의 "자동 순번 필드"로,
테이블의 필드로 정의하고, 테이블에 insert가 발생할 때마다 자동으로 순번을 붙여주는 기능입니다.
즉, 고유한 식별 값을 생성하는 과정을 데이터베이스에게 일임합니다.
기능적으로 시퀀스 객체는 테이블과 독립적이기 때문에 여러 테이블에서 사용 가능한 반면,
IDENTITY 필드는 특정한 테이블과 연결된다는 점이 특징입니다.
필자는 성능적으로도 CACHE, NOOREDER 과 같은 성능 튜닝 방법이 없어서 IDENTITY 보다는 시퀀스 객체의 사용을 추천합니다.
3. 채번 테이블
순번을 생성하는 전용 테이블입니다.
테이블에서 유사적으로 시퀀스 객체를 구현한 것으로, 새퀀스 객체의 락 매커니즘을 테이블을 활용해 직접 구현하는 오래된 방식입니다.
성능이 제대로 나오지 않고, IDENTITY와 마찬가지로 시퀀스 객체의 문제가 그대로 존재한다는 점이 문제입니다.
바틀 넥이 걸려도 튜닝할 수 있는 방법이 전혀 없어, 지양하는 것이 좋습니다.
'Database' 카테고리의 다른 글
| Rollback, UNDO (0) | 2024.12.06 |
|---|---|
| 5장 반복문 (0) | 2024.12.04 |
