레코드 순번
PK 값이 [1, 2, 3, …] 이렇게 늘어나는 sequence가 아닌 경우에 레코드에 순번을 붙일 때 어떻게 하는 지 살펴보자.
기본 키가 한 개의 필드일 경우
| student_id | weight(체중) |
|---|---|
| A100 | 60 |
| A101 | 55 |
| B343 | 72 |
윈도우 함수 사용
SELECT student_id,
ROW_NUMBER() OVER (ORDER BY student_id) AS seq
FROM Weights;성능은 아래와 같다. Index only scan으로 지정해 테이블의 직접적인 접근도 피할 수 있다.
QUERY PLAN
-------------------------------------------------------------------
WindowAgg (cost=1.17..1.29 rows=7 width=13)
-> Sort (cost=1.17..1.19 rows=7 width=5)
Sort Key: student_id
-> Seq Scan on weights (cost=0.00..1.07 rows=7 width=5)상관 서브쿼리
MySQL은 ROW_NUMBER 함수를 사용할 수 없다. 아래와 같은 방법을 사용하자!
SELECT student_id,
(SELECT COUNT(*)
FROM Weights W2
WHERE W2.student_id <= W1.student_id) AS seq
FROM Weights W1성능은 아래처럼 테이블 접근이 2회 발생한다. 윈도우 함수를 사용하는 것이 성능에는 더 좋다.
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on weights w1 (cost=0.00..8.79 rows=7 width=13)
SubPlan 1
-> Aggregate (cost=1.09..1.10 rows=1 width=8)
-> Seq Scan on weights w2 (cost=0.00..1.09 rows=2 width=0)
Filter: (student_id <= w1.student_id)기본 키가 여러 개라면?
| class(학급) | student_id | weight(체중) |
|---|---|---|
| 1 | A100 | 60 |
| 1 | A101 | 55 |
| 2 | B343 | 72 |
윈도우 함수는 ORDER BY에 필드만 추가하면 된다.
SELECT class, student_id,
ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
FROM Weights2;하지만 상관 서브쿼리를 사용하는 경우엔 살짝 다르다.
다중 필드 비교
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;그룹마다 순번을 붙이는 경우
# 에시
class | student_id | seq
-------+------------+-----
1 | 100 | 1
1 | 101 | 2
1 | 102 | 3
2 | 100 | 1
2 | 101 | 2
2 | 102 | 3
2 | 103 | 4윈도우 함수 사용
SELECT class, student_id,
ROW_NUMBER() OVER (PARTITION BY class ORDER BY student_id) AS seq
FROM Weights2;상관 서브쿼리 사용
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;
순번을 update 하는 방법
윈도우 함수 사용
UPDATE Weights3
SET seq = (SELECT seq
FROM (SELECT class, student_id,
ROW_NUMBER()
OVER (PARTITION BY class
ORDER BY student_id) AS seq
FROM Weights3) SeqTbl
-- SeqTbl라는 서브쿼리를 만들어야 함
WHERE Weights3.class = SeqTbl.class
AND Weights3.student_id = SeqTbl.student_id);상관 서브쿼리 사용
UPDATE Weights3
SET seq = (SELECT COUNT(*)
FROM Weights3 W2
WHERE W2.class = Weights3.class
AND W2.student_id <= Weights3.student_id);레코드 순번 응용
중앙값
집합 지향적 방법
일단 CASE 식을 이용해, Weights를 상위 집합과 하위 집합으로 분할한 후, 레코드 개수가 짝수면 중앙값은 중앙 2개의 값의 평균으로 나타내야 하니, 외측의 AVG 함수를 활용한다.
SELECT AVG(weight)
FROM (SELECT W1.weight
FROM Weights W1, Weights W2
GROUP BY W1.weight
-- S1(하위 집합)의 조건
HAVING SUM(CASE WHEN W2.weight >= W1.weight THEN 1 ELSE 0 END)
>= COUNT(*) / 2
-- S2(상위 집합)의 조건
AND SUM(CASE WHEN W2.weight <= W1.weight THEN 1 ELSE 0 END)
>= COUNT(*) / 2 ) TMP;단점
- 가독성이 떨어진다
- 자기 결합을 사용하고, 결합은 불안정하고 비용이 높다.
절차 지향적 방법1
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);이 방법은 가장 작은 값부터 가장 큰 값 방향으로 증가하는 hi와 가장 큰 값부터 가장 작은 값 방향으로 증가하는 lo 값을 이용한다.
hi와 lo가 중간에 만나는 값을 이용해 중앙값을 구한다.
주의할 점
- ROW_NUMBER를 사용해야 한다. RANK나 DENSE_RANK 함수는 공동 순위가 있기 때문이다.
- ORDER_BY 정렬 키에 weight 값 뿐만 아니라 student_id 값도 포함해야 한다. 이건 나도 의아했던 점인데, student_id를 포함하지 않으면 NULL이 될 수도 있다.
성능
테이블 접근은 1회로 줄고, 결합을 제거한 대신 정렬이 2회로 늘게 된다. 그래도 Weights 테이블이 충분히 클 경우엔, 이런 트레이드 오프는 이득이라고 한다.
절차 지향적 방법2
성능적으로 더 개선해보자.
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;일단 ROW_NUMBER 함수로 구한 순번을 2배 해서, diff를 구한다.
정렬했을 때, 중앙 쯤에 있는 값의 2 ROW_NUMBER() 값은 COUNT()의 근처값이 된다.
이때 COUNT(*) 값을 뺀 값이 0~2 사이에 있다면 그것이 바로 중앙값이다.
성능
정렬이 1회로 줄어들어 정렬 1회, 테이블 접근 1회 → 가장 빠른 쿼리이다.
순번을 이용한 테이블 분할
일련의 비어 있는 숫자를 출력하는 쿼리를 만들어보자.
예를 들어, 레코드의 어느 필드의 값이 [1, 3, 4, 7, 8, 9, 12] 이렇게 있다면 출력해야 할 결과는
[2~2, 5~6, 10~11] 와 같다.
집합 지향적 방법1
SELECT (N1.num + 1) AS gap_start,
'～',
(MIN(N2.num) - 1) AS gap_end
FROM Numbers N1 INNER JOIN Numbers N2
ON N2.num > N1.num
GROUP BY N1.num
HAVING (N1.num + 1) < MIN(N2.num);절차 지향적 방법
현재 레코드와 다음 레코드의 값을 비교하고, 차이가 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
FROM Numbers) TMP(num, diff)
WHERE diff <> 1;성능
일단 테이블에 한 번만 접근이 발생하고, 윈도우 함수에서 정렬이 실행된다.
join을 사용하지 않아 매우 안정적이다.
시퀀스 객체
시퀀스 객체는 아래처럼 정의할 수 있다.
CREATE SEQUENCE testseq
START WITH 1
INCREMENT BY 1
MAXVALUE 100000
MINVALUE 1
CYCLE;시퀀스 객체가 사용되는 건 바로 기본 키로 사용하는 경우가 많다. 하지만 문제점이 있다.
문제점
- 표준화가 늦어, 구문이 달라 이식성이 없고, 사용할 수 없는 구현도 있다.
- 시스템에 자동으로 생성되는 값이므로 실제 엔티티 속성이 아니다
- 성능적인 문제 발생
성능 문제
일단 시퀀스 객체는 유일성, 연속성, 순서성을 만족하는 순번을 생성해준다. 따라서 동시 실행 제어를 위해 lock이 필요한데, 사용자 A가 NEXT VALUE 값을 검색할 때 처리를 살펴보자
- 시퀀스 객체에 배타 락 적용
- NEXT VALUE 검색
- CURRENT VALUE를 1만큼 증가
- 시퀀스 객체에 배타 락 해제
위 단계를 진행할 때 다른 사용자는 시퀀스 객체에 접근할 수 없다. 따라서, 사용자가 연속적으로 시퀀스 객체를 사용할 때 오버헤드가 발생할 수 있다. (오버헤드 평가는 정랴적인 문제로, 무조건 나쁘다고 할 수 X)
성능 문제 대처 - CACHE 객체와 NOORDER 객체
CACHE: 새로운 값이 필요할 때마다 메모리에 읽어들일 필요가 있는 값의 수를 설정
NOORDER: 순서성을 담보하지 않아 오버 헤드를 줄이는 효과
순번을 키로 사용할 때의 문제 - 핫 스팟
저장소의 특정 물리적 블록에만 I/O 부하가 커지므로 성능 악화가 발생하는 것을 핫 스팟, 핫 블록 이라고 부른다.
시퀀스 객체를 사용한 INSERT를 대량의 레코드를 생성하는 경우에 주로 발생한다. 이 문제는 대처가 거의 불가능하기 때문에 할 수 있는 방법이 없다.
완화할 수 있는 방법
Oracle의 역 키 인덱스처럼 연속된 값을 도입하는 경우라도, DBMS 내부에서 변화를 주어 제대로 분산할 수 잇는 구조를 사용하자.
INSERT 구문 자체는 빨라지지만, 범위 검색 등에서 I/O 양이 늘어나기 때문에 트레이드 오프가 있다. 또한 구현 의존적인 방법이다.
또는 인덱스에 일부러 복잡한 필드를 추가해, 데이터의 분산도를 높이는 법도 있다. 이것도 썩 좋은 방법은 아니다.
IDENTITY 필드
‘자동 순번 필드’ 라고 불리는 이 필드는 테이블에 INSERT가 발생할 때마다 자동으로 순번을 붙여주는 기능이다.
시퀀스 객체보다 더욱 심각한 문제를 가진다. 특정 테이블과 연결되다보니, CACHE, NOORDER를 지정할 수 있지만 아예 사용할 수 없거나 제한적으로 사용할 수 있다.
