물리 스키마·자료형·저장 구조와 성능
물리 설계는 논리 모델을 목표 DBMS의 테이블·칼럼·저장 구조로 구현하는 단계다. 자료형·길이·NULL·제약조건과 함께 스토리지, 행 폭, 예상 용량, 데이터 지역화를 검토한다. 물리 E-R 다이어그램과 CRUD 분석으로 업무–데이터 대응과 구조의 품질을 확인한다. 반정규화는 실제 성능 근거와 중복 데이터 관리 방안을 전제로 검토한다.
물리 데이터베이스 설계가 하는 일
물리 데이터베이스 설계(Physical Database Design)는 개념·논리 설계에서 정의한 데이터 구조를 특정 DBMS에서 실제로 생성하고 운영할 수 있는 형태로 구체화하는 단계다. 논리 모델이 “어떤 사실과 관계를 저장할 것인가”를 중심으로 한다면, 물리 설계는 “그 사실을 어떤 제품의 자료형과 저장 구조로 구현하고, 예상 업무 부하에서 어떻게 읽고 쓸 것인가”를 결정한다.
| 설계 수준 | 중심 질문 | 대표 산출물 | 제품 종속성 |
|---|---|---|---|
| 개념 설계 | 어떤 업무 개체와 관계가 있는가 | 개체·관계·업무 규칙 | 낮음 |
| 논리 설계 | 릴레이션과 키를 어떻게 구성할 것인가 | 관계 스키마·정규화 결과 | 비교적 낮음 |
| 물리 설계 | 특정 DBMS에 어떻게 저장하고 접근할 것인가 | DDL·자료형·저장 배치·접근 구조 | 높음 |
물리 스키마에는 일반적으로 다음 결정이 포함된다.
- 실제 테이블·열·제약조건과 객체 이름
- 열별 자료형, 길이, 정밀도, NULL 허용 여부와 기본값
- 기본키·외래키·고유 제약조건의 구체적 구현
- 테이블·인덱스·대용량 데이터의 저장 위치와 저장 옵션
- 예상 데이터량과 증가율에 따른 공간 계획
- 대표 조회·입력·수정·삭제 부하를 위한 접근 구조
- 압축, 행 여유 공간, 제한적 반정규화 등 성능 관련 선택
다만 설계자가 모든 행의 디스크 주소나 페이지 위치를 직접 지정하는 것은 아니다. 설계자는 DBMS가 제공하는 DDL과 저장 옵션으로 정책을 정하고, 실제 페이지 할당·버퍼 관리·파일 쓰기의 상당 부분은 DBMS가 수행한다.
시험 판별 기준: 논리 스키마는 데이터의 논리적 구조와 제약을, 물리 스키마는 특정 DBMS의 자료형·저장·접근 구현을 다룬다. 물리 스키마를 단순히 운영체제 파일 목록과 같은 뜻으로 보면 안 된다.
물리 설계의 입력과 절차
물리 설계는 테이블 수만 보고 결정하지 않는다. 최소한 다음 정보를 함께 수집해야 한다.
| 입력 정보 | 확인 내용 | 설계에 미치는 영향 |
|---|---|---|
| 데이터 규모 | 초기 행 수, 일·월 증가량, 보존 기간 | 파일·공간·파티션 후보와 증설 계획 |
| 값의 분포 | 평균·최대 길이, NULL 비율, 중복도, 편향 | 자료형·행 폭·통계·접근 경로 |
| 업무 부하 | 읽기·쓰기 비율, 대표 조건, 조인, 집계 | 인덱스·열 배치·요약 구조 후보 |
| 동시성과 목표 | 동시 사용자, 처리량, 지연시간 목표 | 경합·I/O·쓰기 비용 검증 |
| 운영 조건 | 백업 창, 장애 복구 목표, 보관·삭제 정책 | 저장 위치·로그·아카이브 용량 |
일반적인 절차는 다음과 같다.
- 논리 모델을 확정한다. 테이블, 후보키, 기본키, 외래키, 무결성 규칙을 확인한다.
- 대상 DBMS와 버전을 정한다. 같은 SQL 이름이라도 자료형 범위와 저장 방식이 다를 수 있다.
- 열별 자료형과 제약조건을 선택한다. 현재 표본이 아니라 업무상 가능한 범위와 연산을 기준으로 한다.
- 평균·최대 행 폭과 데이터 증가량을 추정한다. 테이블 본문, 인덱스, 대용량 값, 로그와 임시 공간을 구분한다.
- 저장 배치와 접근 구조 후보를 정한다. 테이블스페이스·파일그룹·인덱스·파티션 등은 제품 기능과 업무 부하를 함께 본다.
- 대표 데이터와 대표 질의로 기준값을 측정한다. 실행계획, 지연시간, 처리량, 논리·물리 I/O와 쓰기 비용을 확인한다.
- 변경 전후를 비교하고 회귀 검증한다. 한 질의의 개선이 다른 조회나 쓰기 성능을 악화시키지 않았는지 확인한다.
- 근거와 운영 규칙을 문서화한다. 자료형 범위, 시간대, 증설 기준, 반정규화 동기화 방법을 남긴다.
물리 설계의 출발점은 “가장 큰 자료형”이나 “인덱스를 많이 만드는 것”이 아니라 업무 의미·데이터 분포·부하를 수치로 파악하는 것이다.
자료형과 칼럼 속성
자료형은 저장할 값의 범위와 허용 연산을 결정한다. 현재 값만 보고 정하지 말고 업무상 가능한 길이·범위·소수 자릿수와 NULL 허용 여부를 함께 결정한다.
| 분류 | 예 | 선택 기준 |
|---|---|---|
| 정수 | INTEGER, BIGINT | 건수·수량·번호의 범위 |
| 정확 수치 | DECIMAL(p,s), NUMERIC(p,s) | 금액 등 정확한 소수 계산 |
| 근사 수치 | FLOAT, REAL, DOUBLE | 근사 오차가 허용되는 넓은 수치 범위 |
| 고정 길이 문자 | CHAR(n) | 일정 길이의 코드 |
| 가변 길이 문자 | VARCHAR(n) | 실제 길이가 달라지는 이름·주소 |
| 날짜·시간 | DATE, TIME, TIMESTAMP 계열 | 날짜와 시각의 업무 의미 |
| 대용량 문자·이진 | CLOB, BLOB 계열 | 긴 문서·이미지 등의 저장 |
일반적인 DECIMAL(p,s)에서 p는 전체 자릿수, s는 소수부 자릿수이므로 정수부는 최대 p-s자리다. DECIMAL(12,2)는 정수부 10자리·소수부 2자리다. 부동소수점은 모든 10진 소수를 정확히 나타내지 못하므로 금액에 무조건 FLOAT를 선택하지 않는다.
CHAR는 고정 길이, VARCHAR는 가변 길이라는 차이가 핵심이지 CHAR가 항상 빠르다는 뜻은 아니다. 최대 길이가 n이라고 모든 VARCHAR 값이 n바이트를 차지하는 것도 아니다. 한글 등 다중 바이트 문자에서는 문자 수와 바이트 수를 구분한다.
날짜를 임의의 문자열로 저장하면 잘못된 달력값과 정렬 문제가 생길 수 있다. DATE에 시간 성분을 포함하는지 등 구체적인 자료형 의미는 제품에 따라 다르다. 시각을 저장할 때 시간대 기준이 필요한지도 확인한다.
칼럼 설계에는 이름·자료형·길이·정밀도 외에 기본값·NULL 허용·키·CHECK 같은 제약이 포함된다. DEFAULT는 생략된 값의 기본값이며 명시적인 NULL을 자동으로 바꾸는 것과 다르다. 기본키와 외래키의 대응 열은 호환되는 자료형을 사용한다.
스토리지와 저장 단위
스토리지는 데이터가 실제로 저장되는 장치·시스템이다. 데이터베이스용으로 검토할 때에는 용량, I/O 성능, 확장성, 장애 대응과 백업 조건을 조사한다.
| 연결 방식 | 기본 의미 | 접근 단위 |
|---|---|---|
| DAS | 서버에 저장 장치를 직접 연결 | 주로 블록 |
| NAS | 네트워크로 파일 저장 서비스를 제공 | 파일 |
| SAN | 서버와 저장 장치를 위한 저장 네트워크 | 주로 블록 |
여기서는 저장 방식의 개념 차이를 구분한다. 네트워크 프로토콜 설정이나 장비 구축 절차를 데이터베이스 논리 설계와 혼동하지 않는다.
| DBMS 저장 용어 | 의미 |
|---|---|
| 페이지·데이터 블록 | 행을 담고 I/O·버퍼 관리를 하는 기본 단위 |
| 익스텐트 | 여러 페이지·블록을 묶어 할당하는 단위 |
| 세그먼트 | 객체 등에 배정된 공간의 묶음을 부르는 용어 |
| 데이터 파일 | 실제 데이터가 기록되는 파일 |
| 테이블스페이스 | 객체와 데이터 파일의 공간 배치를 관리하는 논리 단위 |
이름과 세부 계층은 DBMS별로 다르다. 테이블스페이스를 하나의 물리 파일과 같은 것으로 보지 않는다. 페이지에는 행 값 외에도 헤더·행 위치 정보·여유 공간 등이 들어가므로 페이지 크기를 단순히 열 선언 길이로 나눈 값이 실제 행 수라고 단정하지 않는다.
행 폭이 I/O와 캐시에 미치는 영향
행 폭(Row Width)이 커지면 한 페이지에 들어가는 행 수가 줄 수 있다. 같은 수의 행을 읽을 때 더 많은 페이지가 필요하면 디스크·스토리지 I/O와 버퍼 캐시 점유가 늘어날 수 있다.
다음은 원리를 보기 위한 단순 계산이다. 실제 DBMS 오버헤드와 압축은 제외하고 페이지에서 행에 사용할 수 있는 공간을 8,000바이트라고 가정한다.
가정 1: 평균 행 길이 200바이트
페이지당 행 수 = floor(8,000 / 200) = 40행
100,000행에 필요한 페이지 = ceil(100,000 / 40) = 2,500페이지
가정 2: 평균 행 길이 400바이트
페이지당 행 수 = floor(8,000 / 400) = 20행
100,000행에 필요한 페이지 = ceil(100,000 / 20) = 5,000페이지
이 단순 모델에서는 행 길이가 두 배가 되면서 필요한 페이지도 두 배가 된다. 실제 환경에서는 페이지 헤더, 행 헤더, 슬롯, 가변 길이 오버헤드, 여유 공간, 압축과 큰 값의 별도 저장 때문에 결과가 달라진다.
행 폭을 줄이는 방법과 비용
| 선택 | 기대 효과 | 비용·주의점 |
|---|---|---|
| 업무 범위에 맞는 자료형 | 불필요한 행 폭과 변환 감소 | 범위를 너무 작게 잡으면 이후 변경 비용 발생 |
| 드물게 쓰는 큰 열 분리 | 자주 읽는 핵심 행의 밀도 향상 가능 | 조인·추가 조회와 일관성 관리 필요 |
| 큰 값을 행 밖에 저장 | 기본 행을 작게 유지 가능 | 추가 I/O와 백업·트랜잭션 정책 필요 |
| 압축 | 저장량과 읽기 I/O 감소 가능 | CPU 비용, 업데이트 특성, 라이선스·기능 차이 |
| 페이지 여유 공간 확보 | 업데이트 시 행 이동·페이지 분할 감소 가능 | 페이지 밀도 저하와 저장량 증가 |
대용량 본문을 별도 테이블로 분리하는 것은 “세로 분할”의 한 형태다. 목록 화면이 작은 열만 반복해서 읽고 본문은 상세 화면에서만 읽는다면 도움이 될 수 있다. 반대로 거의 모든 조회가 본문까지 필요하면 추가 조인이 오히려 비용이 될 수 있다.
VARCHAR(1000)을 선언했다고 모든 행이 1,000바이트를 즉시 차지한다고 단정하면 안 된다. 가변 길이 열은 실제 값 길이에 따라 저장되는 경우가 많지만, 길이 정보·정렬 여백·행 크기 제한·오버플로 처리 방식은 DBMS별로 확인해야 한다.
행 폭과 페이지 수
사용 가능 페이지가 8000바이트일 때 평균 행 200바이트는 페이지당 40행, 400바이트는 20행이다. 100000행은 각각 2500·5000페이지가 필요하다. 헤더·압축·여유 공간을 제외한 단순 계산이며 실제 페이지 규칙은 제품에 따라 다르다.
저장 용량을 추정하는 방법
용량 계획은 테이블의 원시 데이터만 계산해서 끝내지 않는다.
목표 시점 예상 행 수
= 현재 행 수 + 기간 중 유입 행 수 - 기간 중 삭제·보관 전환 행 수
원시 행 데이터량
≈ 예상 행 수 × 측정한 평균 행 길이
테이블 할당량
≈ 원시 행 데이터량 ÷ 목표 페이지 사용률
+ 페이지·행 관리 오버헤드
그 뒤 다음 항목을 별도로 추정한다.
- 기본키·보조 인덱스 공간
- 대용량 값과 별도 저장 영역
- 임시 정렬·해시·배치 작업 공간
- 트랜잭션 로그, WAL, REDO·UNDO 등 제품별 복구 공간
- 통계, 내부 관리 구조와 유지보수 작업 공간
- 복제본, 백업, 아카이브와 장애 대비 여유 공간
단순 용량 계산 예
다음 조건을 가정한다.
예상 행 수 = 5,000,000행
측정 평균 행 길이 = 180바이트
목표 사용률 = 80%
원시 행 데이터량
= 5,000,000 × 180
= 900,000,000바이트
≈ 900MB(10진 기준)
≈ 858MiB(2진 기준)
80% 사용률을 반영한 단순 할당량
= 900,000,000 ÷ 0.8
= 1,125,000,000바이트
≈ 1.125GB
≈ 1.05GiB
이 값은 테이블 행에 대한 단순 추정일 뿐이다. 인덱스·LOB·로그·임시 공간·백업은 포함하지 않았다. 실제 계획에서는 표본 데이터로 평균 행 길이와 압축률을 측정하고, 성장 편차와 운영 여유를 더해야 한다.
오답 경계: 페이지 사용률을 80%로 잡았다고 디스크 전체 사용률이 80%라는 뜻은 아니다. 이는 특정 계산에서 행 데이터가 페이지를 채우는 정도를 단순화한 가정이다.
데이터 지역화와 물리 배치
데이터 지역화(locality)는 함께 또는 반복 접근하는 데이터를 가까이 두거나 메모리에 유지해 접근 비용을 줄이는 관점이다. 최근 접근한 데이터에 다시 접근하는 것은 시간적 지역성, 인접 데이터에 이어 접근하는 것은 공간적 지역성이다.
주문과 주문 항목을 자주 함께 읽는다면 관련 행을 가까이 배치하는 것이 I/O를 줄이는 데 도움이 될 수 있다. 반면 접근 빈도와 관계없는 데이터를 무조건 모으거나 큰 행에 모두 합치면 불필요한 읽기가 늘 수 있다. 분산 환경에서는 이용 지역과 가까운 사이트에 두는 데이터 배치도 지역화 관점으로 검토한다.
인덱스·클러스터링·파티션은 접근 비용을 줄이는 물리 설계 수단이며 저장 공간과 입력·수정 비용도 갖는다. 압축은 저장량을 줄일 수 있지만 압축·해제 비용이 생긴다. 테이블스페이스 이름을 나눈 것만으로 물리 I/O가 자동 분산되는 것도 아니다.
물리 모델 품질 검토와 CRUD 분석
물리 E-R 다이어그램에는 실제 테이블·열, 기본키·외래키, 자료형·길이·NULL 여부 등이 논리 모델과 맞게 나타나야 한다. 키·무결성 누락, 잘못된 자료형, 저장 용량 부족, 주요 조회의 성능 위험을 검토한다.
CRUD 분석은 업무 기능과 데이터의 생성(Create)·조회(Read)·수정(Update)·삭제(Delete) 관계를 표로 나타내는 것이다. 행과 열의 배치 방향은 달라도 의미는 같다.
| 업무 기능 | 고객 | 주문 | 주문 항목 |
|---|---|---|---|
| 고객 등록 | C | — | — |
| 주문 입력 | R | C | C |
| 주문 조회 | R | R | R |
| 주문 내용 수정 | R | U | U |
| 주문 삭제 | — | D | D |
읽기만 하는 테이블에 생성 기능이 전혀 없으면 누가 데이터를 최초로 넣는지 확인한다. 생성만 하고 사용하지 않는 데이터는 필요성과 누락된 기능을 점검한다. 다만 기준 데이터의 외부 반입이나 이력 보존 때문에 일부 CRUD가 없는 것이 정당할 수 있으므로 반드시 네 기능이 모두 있어야 한다고 단정하지 않는다.
CRUD는 SQL문 실행 계획 자체가 아니라 업무–데이터 관계를 검토하는 도구다. 조회·갱신 빈도와 규모를 함께 보면 인덱스, 배치, 반정규화 검토의 근거가 된다.
반정규화와 성능 확인
정규화된 구조에서 성능 요구를 만족하기 어려울 때 반정규화를 검토한다. 대표 방법은 테이블 병합·분할, 중복 속성, 집계 테이블 추가다. 테이블 수를 무조건 줄이는 작업만 뜻하지 않는다.
예를 들어 주문 항목 합계를 주문 테이블에 저장하면 조회 시 집계를 줄일 수 있지만 항목이 바뀔 때 합계도 함께 갱신해야 한다. 기준 원본, 갱신 책임, 불일치 검사 방법을 정하지 않으면 이상 현상이 다시 생길 수 있다.
변경 전후에는 같은 데이터와 업무 조건으로 처리 시간·처리량·I/O·저장 공간을 비교한다. 한 조회가 빨라져도 입력·수정이나 다른 조회가 느려질 수 있으므로 대표 업무 전체를 확인한다.
파일 편성과 페이지 수를 구분하기
힙 파일은 검색 키 순서로 행을 정렬해 유지하지 않는 방식이다. 순차 파일은 검색 키 순서로 레코드를 유지하는 방식이며 순차·범위 접근에 적합하지만 삽입·재배치 비용을 고려해야 한다. 해시 파일은 키에 해시 함수를 적용해 버킷 위치를 정한다. 해시값이 같아도 원래 키가 같은 것은 아니므로 충돌 처리가 필요하다.
레코드가 페이지에 걸쳐 저장되지 않는다는 조건에서 페이지당 레코드 수는 floor((페이지 크기−고정 오버헤드)÷레코드 길이)로 계산한다. 페이지 4096바이트, 오버헤드 96바이트, 레코드 150바이트라면 floor(4000÷150)=26개다. 26.6개처럼 소수 행을 저장한다고 답하지 않는다.
원시 데이터량이 200MB이고 목표 사용률이 80%라면 단순 필요 할당량은 200÷0.8=250MB다. 단위가 MB인지 MiB인지, 인덱스·로그·백업 등을 포함하는지 확인한다.
NAS는 네트워크 파일 단위 접근을, SAN은 전용 저장 네트워크를 통한 블록 단위 접근을 제공하는 구성이 대표적이다. 물리 디스크·네트워크·프로토콜의 구체적 제품 설정까지 동일하다는 뜻은 아니다.
CRUD 표는 업무와 데이터의 생성·조회·수정·삭제 관계를 연결한다. 신규 데이터가 외부 입력 없이 시스템 안에서 생성돼야 하는데 모든 업무에 R·U만 있고 C가 없다면 생성 경로 누락을 검토해야 한다. 사용 빈도가 낮은 큰 열을 분리하면 자주 읽는 행의 폭을 줄일 수 있지만, 그 열이 필요할 때 조인이 추가되는 비용이 생긴다.