💡 SQL 에서의 순서

• SQL 은 순번을 다루기 위한 기능 없음
• 관계 모델 이론 때문
• 테이블 레코드를 순서 없게 정의
• 하지만 순번은 필요한 경우가 많기 때문에 윈도우 함수를 이용해 절차지향의 순번을 활용

23강. 레코드에 순번 붙이기

기본키가 필드 1개

윈도우 함수 방법

• 테이블 스캔 횟수 1회
• 인덱스 온리 스캔 사용하므로 테이블 직접 접근 회피

select student_id,
			 row_number() over (order by student_id) as seq
from weights;

상관 서브쿼리 방법

• row_number 함수 사용 불가한 시스템에서의 방법 → aka MySQL
• 성능적으로 지양해야 함

select student_id,
			(select count(*)
					from weights w2
				where w2.student_id <= w1.student_id) as seq
from weights w1;

기본키가 필드 여러 개

윈도우 함수 방법

• 키가 하나일 때의 쿼리에서 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 weights w2
				where ***(w2.class, w2.student_id) <= (w1.class, w1.student_id)*** as seq
from weights w1;

그룹마다 순번 붙이는 경우

• 테이블을 그룹으로 나누고, 그룹마다 내부 레코드에 순번 할당

윈도우 함수 방법

• partition by 로 클래스 기준으로 테이블 그룹 분할
• 분할된 그룹에 student_id 를 이용해 순번 할당

select class, student_id,
			row_number() over (***partition by*** ***class*** order by student_id) as seq 
from weights;

상관 서브쿼리 방법

select class, student_id,
			(select count(*)
					from weights w2
				where ***w2.class = w1.class*** and w2.student_id <= w1.student_id) as seq
from weights w1;

순번과 갱신

• 검색이 아니라 갱신에서 순번 매기는 방법

윈도우 함수 방법

• row_number 쓸 경우에는 서브쿼리 필요

update weights
		set seq = (select seq
									from (select class, student_id,
														row_number() over (partition by class order by student_id) as seq
														from weights) seqtbl
								where weights.class = seqtbl.class and weights.student_id = seqtbl.student_id);

상관 서브쿼리 방법

update weights
		set seq = (select count(*)
									from weights w2
								where w2.class = weights.class and w2.student_id <= weights.student_id);

24강. 레코드에 순번 붙이기 응용

💡 자연 수열 (순번) 성질

• 연속성 : 건너뛰는 일이 없는 것
• 유일성 : 수열 내부에 있는 하나의 숫자는 한 번만 나타나는 것

중앙값 구하기

• 숫자 정렬해 양쪽 끝부터 수를 세는 경우 정중에 오는 값
• 홀수, 짝수에 따라 중앙값 구하는 방법이 다름

집합 지향적 방법

• 테이블을 상위집합, 하위집합으로 분할하고 공통 부분 검색
• 단점
  • 복잡한 코드
  • 나쁜 성능
    • 자기결합 때문에 결합 수행

select avg(weight)
		from (select w1.weight
						from weights w1, weights w2
					group by w1.weight
					-- 하위 집합 조건
					having sum(case when w2.weight >= w1.weight then 1 else 0 end) >= count(*) / 2
					-- 상위 집합 조건
					and sum(case when w2.weight <= w1.weight then 1 else 0 end) >= count(*) / 2) tmp;

절차 지향적 방법1

• lo와 hi 가 만나는 지점이 결국 중앙값이다.
• 짝수의 경우
  • hi = lo - 1 or lo + 1
• 홀수의 경우
  • hi = lo
• 정렬 2회 사용
• row_number 함수만 사용 가능
  • rank, dense_rank 함수는 유일성 혹은 연속성이 깨지기 때문
• 반드시 order by 정렬 조건에 student_id 를 포함해야 함
  • 그렇지 않으면 null 값이 나올 수 있음
  • 항상 같은 결과를 도출할 수 없음

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

절차 지향적 방법2

• 성능적으로 개선한 방법
  • 표준 SQL 에서 중앙값 구하는 가장 빠른 방법
• 정렬 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;

순번을 사용한 테이블 분할

• 테이블을 여러 그룹으로 분할하는 문제

단절 구간 찾기

• 중간중간 비어있는 숫자를 이렇게 출력하고 싶음

gap_start	gap_end
2	2
5	6
10	11

집합 지향적 방법

• 집합의 경계선을 구하는 방법
• 단절 지점
  • n1.num+1 ≠ min(n2.num) 인 지점
  • gap_start = 비어있는 숫자 시작값 = n1.num 다음 숫자
  • gap_end = 비어있는 숫자 종료값 = n2.num-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이 아니라면 사이에 빈 숫자가 있다.
• 윈도우 함수로 현재 레코드의 다음 레코드 구하고, 두 레코드 숫자 차이를 diff 필드에 저장
• 성능
  • 결합 사용하지 않음
  • 테이블 접근 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;

테이블에 존재하는 시퀀스 구하기

집합 지향적 방법

• 집합 경계선 구하는 방법
  • max / min 함수 이용해 시퀀스 경계를 직접적으로 구하기
  • 자기 결합으로 num 필드 조합 만들고, 최댓값 최솟값으로 집합 경계 구하는 방식

select min(num) as low,
			max(num) as high
		from (select n1.num,
								count(n2.num) - n1.num
							from numbers n1 inner join numbers n2 on n2.num <= n1.num
					group by n1.num) n(num, gp)
group by gp;

절차 지향적 방법

• 다음 레코드와 비교하는 방법
• 정렬 2회 발생 → tmp1, tmp3 임시 테이블
• 서브쿼리 스캔 2회 발생 → tmp1, tmp2 임시 테이블
• 중간 결과를 메모리에 유지하고, 모자르면 저장소를 사용하므로 성능 측면에서 안정성이 조금 부족함

25강. 시퀀스 객체, IDENTITY 필드, 채번 테이블

💡 세 개 모두 최대한 사용하지 않기 굳이 써야한다면 그나마 나은 시퀀스 객체 쓰기 Oracle에선 IDENTITY, MySQL에선 시퀀스 객체 지원 안 함

시퀀스 객체

• 테이블이나 뷰처럼 스키마 내부에 존재하는 객체 → Create 구문으로 정의

create sequence testseq -- 시퀀스 객체 이름
start with 1    -- 초기값
increment by 1  -- 증가값
maxvalue 10000  -- 최댓값
minvalue 1      -- 최솟값
cycle;          -- 최댓값에 도달했을 때 순환 유무

insert into tbl values(next value for nextval, 'a', 'b', ...);

시퀀스 객체의 문제점

• 표준화가 늦어, 이식성 없고 사용할 수 없는 구현 존재함
• 실제 엔티티 속성이 아님
• 성능적인 문제 일으킴

시퀀스 객체로 발생하는 성능 문제

• 시퀀스 객체가 생성하는 순번 특성
  • 유일성
    • 중복값 생성되지 않음
    • 중복값이 있으면 기본키 불가
  • 연속성
    • 생성된 값에 비어있는 부분이 없음
    • 기본키 필수 제약 아니므로, 옵션 설정 가능
  • 순서성
    • 순번의 대소 관계가 역전되지 않음
    • 기본키 필수 제약 아니므로, 옵션 설정 가능
• 순번 특성 만족하기 위해 락 매커니즘 사용
  • 시퀀스 객체 사용 시 배타 락
  • 락 매커니즘의 성능 문제
  1. 시퀀스 객체에 배타 락 적용
  2. next value 검색
  3. current value를 1만큼 증가
  4. 시퀀스 객체에 배타 락 해제
• 핫 스팟 문제
  • 순번처럼 비슷한 데이터를 연속으로 insert 시 물리적으로 인접한 영역에 과부하 몰림.
  • 특정 구역에 I/O 부하가 몰리는 핫 스팟 (핫 블록) 문제 발생
  • 연속된 값 도입할 때 DBMS 내부에서 해시 등을 이용해서 분산할 수 있는 구조로 해결
  • 인덱스에 필드를 추가해 데이터 분산도 올리기

문제점 대처

• CACHE
  • 새로운 값이 필요할 때마다 메모리에 읽을 필요 있는 값의 숫자 설정
  • 장애 시 연속성 담보 불가
• NOORDER
  • 순서성 담보 하지 않음
  • 오버헤드 감소

IDENTITY 필드

• 자동 순번 필드
• 테이블 필드로 정의하고 테이블에 insert가 발생할 때마다 자동으로 순번 붙여주는 기능
• 기능, 성능적 측면에서 시퀀스 객체보다 심각한 문제
  • 기능적 측면
    • IDENTITY 필드는 특정 테이블과 연결 → 재사용 불가, 여러 테이블 간 공유 불가
  • 성능적 측면
    • 시퀀스 객체에서 성능 튜닝 방법인 CACHE, NOORDER 등 사용 어려움
• 필자 의견에 따르면 무쓸모라는 평

채번 테이블

• 구시대 유물

---

정리

• 절차 지향형이 윈도우 함수라는 형태로 SQL에서 살아감
• 윈도우 함수 사용으로 코드 간결해짐
• 윈도우 함수 사용으로 결합, 테이블 접근 줄어들어 성능 향상
• 시퀀스 객체, IDENTITY 필드는 성능 문제 원인

---

연습문제

💡 중간값을 구하는 SQL 쿼리에서 student_id 필드를 정렬 기준으로 사용하지 않으면 왜 정상 동작하지 않을까?

• 체중이 같은 학생들이 언제나 같은 순서로 정렬된다는 보장이 없기 때문
• 정렬 기준을 유일성을 가진 student_id 필드를 포함하여 언제나 같은 순서를 보장하기 위함

---

JPA 식별자와 순번

• JPA에서 엔티티를 영속상태로 관리하기 위해선, 즉 엔티티를 영속성 컨텍스트에 저장하기 위해선 식별자 값이 필요
• JPA 식별자 (기본 키) 매핑 방법
  • 직접 할당
  • 자동 생성
    • IDENTITY
      • 기본 키 생성을 데이터베이스에 위임
      • 순서대로 순번을 매기는 데이터베이스의 순번 IDENTITY와 의미가 다름에 유의
      • mysql에서는 auto_increment 방식 사용 → 이건 데이터베이스 순번 IDENTITY와 같은 의미
      • 데이터베이스에 엔티티를 insert 한 후에 기본 키 값 조회 가능
        • 따라서 엔티티에 식별자 값 할당하려면 추가 테이블 접근이 필요하지만, 하이버네이트는 JDBC3 getGeneratedKeys() 메소드 활용해 한 번만 테이블 접근
        • 엔티티를 DB에 저장해야만 식별자 값이 생성되므로, 트랜잭션 지원하는 쓰기 지연 동작 안 함
    • SEQUENCE
      • 데이터베이스 시퀀스 사용해서 기본 키 할당
      • SQL CACHE = JPA allocationSize
      • 엔티티 저장 전에 데이터베이스 시퀀스 사용해 식별자 조회하여 엔티티 할당 후 저장
        • 사전에 시퀀스를 조회하므로 테이블 접근 2회 필요
        • 이때 성능 향상을 위해 allocationSize(CACHE) 를 활용
    • TABLE
      • 키 생성 테이블 사용
      • 채번 테이블 방법으로 추정
      • allocationSize 방식으로 성능 개선 가능
    • AUTO
      • 선택한 데이터베이스 방언에 따라 다른 전략 중 하나를 자동으로 선택
      • 기본값

SQL에서는 순번 쓰지 말라는데, 그럼 식별자는 어떻게 선택해?

• 권장하는 식별자 선택 전략
  • 식별자 요구사항
    • not nullable
    • unique
    • not modifiable
  • 선택 전략
    • 자연 키
      • 비즈니스에 의미 있는 키
      • 주민번호, 이메일, 전화번호
    • 대리 키 (인조 키)
      • 임의로 만들어진 비즈니스 연관없는 키
      • 오라클 시퀀스, auto_increment
  • 결론
    • SQL 성능에 하자가 있더라도 대리 키 사용을 권장
      • 비즈니스 환경은 변할 수 있음
      • JPA 는 모든 엔티티에 일관된 방식으로 대리 키 사용을 권장