• sql
  • query-optimization
  • book

SQL 레벨업 Ch.8 — SQL의 순서

윈도우 함수의 순번으로 중앙값과 단절 구간을 구하는 쿼리, 시퀀스 객체와 IDENTITY 필드의 성능 문제를 정리했다.

Manx5분 읽기
시리즈 · BackEnd 서적6 / 17
  1. SQL 레벨업 Ch.1 — DBMS 아키텍처
  2. SQL 레벨업 Ch.3 — SQL의 조건 분기
  3. SQL 레벨업 Ch.4~5 — 집약과 자르기, 반복문
  4. SQL 레벨업 Ch.6 — 결합
  5. SQL 레벨업 Ch.7 — 서브쿼리
  6. SQL 레벨업 Ch.8 — SQL의 순서
  7. SQL 레벨업 Ch.9 — 갱신과 데이터 모델
  8. SQL 레벨업 Ch.10 — 인덱스 사용
  9. 오브젝트 Ch.1 — 객체, 설계
  10. 오브젝트 Ch.2 — 객체지향 프로그래밍
  11. 오브젝트 Ch.3~4 — 역할, 책임, 협력 / 설계 품질과 트레이드오프
  12. 오브젝트 Ch.5 — 책임 할당하기
  13. 오브젝트 Ch.6 — 메시지와 인터페이스
  14. 오브젝트 Ch.8 — 의존성 관리하기
  15. 오브젝트 Ch.9 — 유연한 설계
  16. 오브젝트 Ch.10, 12 — 상속과 코드 재사용, 다형성
  17. 오브젝트 Ch.13 — 서브클래싱과 서브타이핑

레코드에 순번을 붙이는 기본 방법은 앞 장에서 다뤘으므로 응용부터 정리한다.

중앙값 구하기

student_id(학생 ID) weight(체중 kg)
A100 50
A101 55
A124 55
B343 60
B346 72
B378 72
C563 72
C345 72

양쪽 끝에서 세기

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);

오름차순 순번과 내림차순 순번을 양 끝에서 같은 속도로 움직이면 두 순번이 만나는 지점이 중앙값이다. lo + 1, lo - 1이 함께 있는 이유는 레코드가 짝수 개일 때 가운데 두 값의 평균을 내야 하기 때문이고, 그래서 AVG로 감싼다. 홀수와 짝수의 조건 분기를 IN 하나로 처리한 점이 인상 깊었다.

다만 정렬 2회, 테이블 스캔 1회가 필요해 더 개선할 여지가 있다.

2 빼기 1은 1

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배 한 뒤 전체 건수를 빼면, 홀수 개일 때는 diff가 1인 레코드가, 짝수 개일 때는 0과 2인 레코드가 중앙에 해당한다. 알고리즘을 왜 배우는지 알게 되는 순간이었다.

실행 계획은 스캔 1회, 정렬 1회로, SQL 표준으로 중앙값을 구하는 가장 빠른 쿼리다.

순번을 사용한 테이블 분할: 단절 구간 찾기

num 필드에 1, 3, 4, 7이 들어 있는 Numbers 테이블에서 비어 있는 구간(22, 56)을 찾는다.

집합 지향적 방법

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);

N1.num보다 큰 N2.num 중 최솟값이 바로 다음 숫자이고, 그 값이 N1.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;

윈도우 함수의 1 FOLLOWING으로 다음 레코드의 값을 가져와 현재 값과의 차이를 diff에 담는다. diff가 1이 아니면 연속이 끊긴 것이다.

테이블 접근 1회에 윈도우 함수의 정렬만 사용하고, 결합이 없어 성능이 안정적이다.

시퀀스 객체

책의 결론부터 말하면 시퀀스 객체와 IDENTITY 필드는 모두 최대한 사용하지 않는 것이 좋고, 써야 한다면 IDENTITY 필드보다 시퀀스 객체를 쓴다.

CREATE SEQUENCE testseq
 START WITH 1
 INCREMENT BY 1
 MAXVALUE 10000
 MINVALUE 1
 CYCLE;

문제점

  • 표준화가 늦어 구현마다 구문이 달라 이식성이 없다.
  • 시스템이 자동으로 생성하는 값이라 실제 엔티티의 속성이 아니다.
  • 성능 문제를 일으킨다.

시퀀스 객체는 유일성, 연속성, 순서성을 보장해야 하므로 락 메커니즘이 필요하다. 한 사용자가 시퀀스 객체를 사용하는 동안에는 객체를 락해 다른 사용자의 접근을 블록하는 배타 제어가 이루어진다. 따라서 락 충돌로 성능이 떨어지고, 연속으로 사용하면 오버헤드가 누적된다.

성능 문제의 대처

  • CACHE: 미리 메모리에 읽어 둘 값의 개수를 지정한다. 대신 연속성을 담보할 수 없어 장애가 발생하면 빈 번호가 생긴다.
  • NOORDER: 순서성을 담보하지 않는 대신 오버헤드를 줄인다.

순번을 키로 사용할 때: 핫 스팟

  • 순번처럼 비슷한 값을 연속으로 INSERT하면 물리적으로 같은 영역에 저장된다.
  • 저장소의 특정 블록에만 I/O 부하가 몰려 성능이 나빠진다.
  • 이렇게 부하가 몰리는 부분을 핫 스팟(Hot Spot) 또는 핫 블록(Hot Block)이라고 한다.

대처 방법은 두 가지다.

  1. 연속된 값을 넣더라도 DBMS 내부에서 변화를 주어 분산시키는 구조(해시 등)를 사용한다.
  2. 인덱스에 일부러 복잡한 필드를 추가해 데이터의 분산도를 높인다.

두 방법 모두 INSERT는 빨라지지만 I/O 양이 늘어나 SELECT 성능이 나빠질 수 있다.

IDENTITY 필드

테이블에 INSERT가 발생할 때마다 자동으로 순번을 붙여 주는 ’자동 순번 필드’다. 기능과 성능 양쪽에서 시퀀스 객체보다 문제가 크다.

  • 특정 테이블에 연결된다.
  • 구현에 따라 CACHE, NOORDER 같은 옵션을 아예 쓸 수 없거나 제한적으로만 쓸 수 있다.