데이터베이스 모델링 1-4 PRIMARY KEY & UNIQUE
※ PRIMARY KEY
ㄴ 해당 테이블내 각각의 행을 고유하게 식별할 수 있도록 하는 제약조건이다.
ㄴ 반드시 not null 이어야 하며 clustered(일반적으로 기본값이 clustered) 인 경우 테이블 내 데이터 자체를 정렬한다.
ㄴ 동 테이블내 nonclustered index 에서 seek 후 covered가 안되는 경우 clustered PK 존재시에는 key lookup 형태의 랜덤엑세스가 발생하는 데 만약 clustered PK 가 미존재하는 경우 heap lookup 형태의 랜덤 엑세스가 발생한다.
당연히 비 정렬상태의 RID(오라클의 rowid)값을 찾는 랜덤엑세스인 heap lookup 이 리소스를 많이 잡아먹게 된다.
하나 찾을때마다 래치 획득과 해싱의 과정을 반복해야하므로 래치 경합이 발생할 수 있다.
그만큼 PK는 매우 중요하다.
※ UNIQUE
ㄴ 해당 테이블내 각각의 행을 고유하게 식별할 수 있도록 조건이다.(PK와 동일)
ㄴ null 포함될 수 있으며 기본값이 nonclustered 이다.
※ 설명
ㄴ 여기까지는 아주 상식적이라 나도 알고 당신도 안다.
그러면 예를 들어 주문정보 테이블을 구상한다고 가정하자.
필요한 컬럼은 주문번호, 고객코드, 제품코드, 주문수량, 사용여부, 주문일자.... 정도가 꼭 필요한 정보이다.
조회는 주문일자 기간별, 업체별, 품목별 필터링이 제공되어야 한다.
다른 제약조건은 일단 배제하고 PK와 UQ만 생각해보자.
주문번호를 PK로 줄수도 있고 만약 주무정보의 수정들의 기록들을 히스토리로 보관해야 하는 경우(실제로 데이터의 삭제나 수정은 실전에서 거의 이루어지지 않는다.
정보의 왜곡을 막는것 또한 DBMS의 존재의 이유 중 가장 큰 사유이기 때문이다.)
이러한 일반적인 조건이라면 주문번호만으로 PK를 잡을 수 없다.
datetime과 같은 일시를 만들어 주문번호와 함께 PK를 만들수도 있다.
초당 수천개의 주문이 발생하는 쿠팡의 주문테이블 이라 가정해 보자.
테이블 모델링시 감안할 것들은 알수록 많아진다.(잘 모를때가 용감하다)
물론 가장 중요한건 고객의 needs를 명확하게 인지하고 화면에 확정하는 일
그때 어떠한 조건으로 조회되며 어떤 식으로 저장이 되어야 하는지
8kb인 페이지(오라클이라면 2,4,8kb 단위도 다양하며 블록이라 함)내 CF(Clustering Factor) 예상은 어떨지
이로인한 래치경합과 버퍼 피닝(Buffer Pinning) 중 어떤게 유리할지..
컬럼의 추가될 경우 페이지 분할이 얼마나 될지..
제약조건은 어떤걸 추가해야할지...
seq(identity)로 PK로 잡을지...
nonclustered 형으로 cluster형으로 PK를 잡을지..
난 그래서 모델링이 참~~ 여렵덴데..
아래와 같은 테이블이 있고 테이블에 PK는 ITEM_CD 이다.
| item_cd - CHAR(4) | order_qty - INT |
| G001 | 5 |
| G001 | 7 |
| G002 | 1 |
| G002 | 15 |
| G002 | 25 |
| G002 | 16 |
| G004 | 7 |
| ….. | …. |
| G989 | 34 |
| G999 | 6 |
ROW COUNT는 16,000개정도가 존재한다고 치자.
우선 MS-SQL 기준 하나의 Page(오라클의 block)는 8KB이고
CHAR(4)와 INT 타입은 모두 4byte이다
그럼 하나의 row는 8byte가 된다.(물론 헤더정보를 가질 별도로 공간도 필요하다.)
그럼 1000 row가 하나의 페이지에 저장된다.
그리고 8000 row가 넘어가면 추가로 공간할당을 하게된다.(공간할당 기준은 extent = page x 8)
그러면 만약 select * from dbo.test 라고 날린 후 i/o를 확인해 보면 단순 계산상 2가 나온다.
그리고 select * from dbo.test where item_cd = 'G001' 이건
select * from dbo.test where item_cd < 'G004' 둘다 모두 i/o 를 읽은건 1회로 동일하다.
이처럼 CF(Clustering Factor) 가 조밀하면 i/o 접근횟수를 최소하는데
이때 동일 페이지를 접근하니 당연히 래치(DBMS가 커널단에서 직접 관리하는 소극적 잠금정도로 일단 생각하자) 획득도1회로 고정된다. (이를 Buffer Pinning 이라한다.)
이는 쿼리 전에 SET STATISTICS IO ON 으로 설정값을 변경하고 확인이 가능하다.
헌데 반대로 동일한 쿼리로 다수의 사용자가 접근한다면(동일 페이지에 여러 쓰레드 동시에 접근시) 래치경합으로 대기가 발생할 수 있는 것이다.
또한 위 테이블에 별도의 char(8) 컬럼이 투가된다면 어떻게 될까?
그러면 하나의 row가 16byte로 변경되므로 당연히 페이지 분할이 발생하게 되고
이에 따라 i/o 읽기 횟수도 증가하고 그만큼 속도가 느려진다.
컬럼이 100인 테이블과 pk와 fk로 3개의 테이블로 잘 분할된 테이블을 조인하는 것과 무엇이 유리할까?
출력되는 row count에 따라서나 여러가지 사유로 달라지겠지만 일반적으로는 i/o 접근 회수를 줄이는 쪽이 승자가 된다.
PK와 UQ를 포함하는 모든 인덱스들은 정렬을 진행한다.
위 테이블을 기준으로 아래 쿼리가 날라갈때 DBMS의 프로세스는 아래와 같다.
select * from dbo.test where item_cd = 'G121'
1. 쿼리를 파싱한다.(쿼리 유효성 검사정도? 이것도 리소스를 먹는다. scalar 형 UDF를 수천라인 결과물에 넣을때 시간 중 파싱시간도 무시못한다.)
2. 최적화(옵티마이저가 나름? 최적화된 실행계획을 생성한다.)
3. 실제 실행계획에 따라 정렬된 PK를 기준으로 앞뒤 점프를 반복하며 해당 데이터가 존재하는 페이지를 찾는다.(이 부분은 인덱스 부분에서 상세히 설명하겠다)
4. 해당 페이지의 래치를 획득한다.
5. 해당 데이터에 접근 후 dataset을 취득한다.
6. 해당 페이지의 래치를 해싱한다.
7. dataset을 리턴한다.
이번엔 신규 데이터를 하나 추가한다고 치자
insert into dbo.test values('G521', 43);
이때의 과정도 살펴보자(노드의 개념은 인덱스를 다룰때 상세히 다뤄보자.)
1. 쿼리를 파싱한다.(쿼리 유효성 검사정도? 이것도 리소스를 먹는다. scalar 형 UDF를 수천라인 결과물에 넣을때 시간 중 파싱시간도 무시못한다.)
2. 최적화(옵티마이저가 나름? 최적화된 실행계획을 생성한다.)
3. 트랙잰션 시작한다.
4. 데이터를 레코드에 삽입할때 key값의 정렬 기준으로 key를 분할 후 삽입한다.
5. 페이지 분할시 분할한다 - ROT(Row Offset Table)로 공간을 조정한다.
6. 트랙잰션을 해제한다.
상상해 보자 테이블 하나에 인덱스가 주렁주렁 달려있으면 위 과정을 인덱스 마다 다 진행해야한다.
대량의 데이터 입력시 tablock 힌트와 인덱스 disable후(clustered index는 데이터 페이지이므로 안되고) rebuild 를 하면 좋은지 처리 속도면에서만이 아니라 공간활용면에서도 효과적일 수 있기 때문이다.
어떤 학문을 혹은 기술을 습득하던 기초가 중요하다.
혹 지금까지의 내용을 몰랐다 한들 낙심하지 마라~~
내 주변의 전문 모델러가 아닌 일반 개발자들중에 이런거 아는 사람 100에 하나도 못봤다 ㅋㅋㅋ
결론적으로 정답은 현장에 있고 모델러의 역량과 열정에 달려있다.
시간이 지나면 잘못된 뼈대는 오래 못가고 무너지기 마련이다.
시스템의 뼈대는 단연코 모델링이다.
래치 관련 정보는 아래 쿼리로 확인이 가능하다.
SELECT
'Wait_Stat.' AS [What],
DB_NAME([database_id]) AS [Database],
s.[name] AS [SchemaName], -- 스키마명
o.[name] AS [TableName], -- 테이블명
CASE
WHEN i.[is_unique] = 1 THEN 'UNIQUE '
ELSE ''
END + i.[type_desc] AS [IndexType],
i.[name] AS [IndexName],
page_io_latch_wait_in_ms,
page_latch_wait_in_ms,
page_lock_wait_in_ms,
row_lock_wait_in_ms,
iops.[page_io_latch_wait_count],
page_lock_wait_count,
iops.[page_latch_wait_count],
page_lock_count,
row_lock_count,
row_lock_wait_count,
leaf_allocation_count,
leaf_insert_count,
leaf_delete_count,
leaf_update_count,
nonleaf_insert_count,
nonleaf_delete_count,
nonleaf_update_count,
nonleaf_allocation_count
FROM
[sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN
[sys].[indexes] i ON i.[object_id] = iops.[object_id] AND i.[index_id] = iops.[index_id]
INNER JOIN
[sys].[objects] o ON o.[object_id] = iops.[object_id] -- 테이블 정보를 가져오기 위해 추가
INNER JOIN
[sys].[schemas] s ON s.[schema_id] = o.[schema_id] -- 스키마 정보를 가져오기 위해 추가
WHERE
i.[type_desc] != 'HEAP'
AND s.[name] NOT LIKE 'sys%'
ORDER BY
iops.page_latch_wait_in_ms + iops.page_io_latch_wait_in_ms DESC;
SELECT *
FROM sys.dm_os_latch_stats
ORDER BY wait_time_ms DESC;
이 글은 이비니어스( www.evinious.co.kr )의 소유이며 불펌은 허락하지 않습니다.