출제기준 세세항목: 파티셔닝 · 클러스터링 · 데이터베이스 백업 · 테이블 저장 사이징 · 데이터 지역화(locality)
논리 설계가 '어떤 테이블에 어떤 열을 두고 서로 어떻게 잇는가'를 정했다면, 물리 설계는 '그 테이블을 실제 DBMS의 디스크 위에 어떻게 저장하고, 어떻게 빨리 찾게 할 것인가'를 정합니다. 같은 설계도로 집을 짓더라도 어떤 자재를 쓰고 수납장을 어디에 두느냐에 따라 살기 편한 정도가 달라지는 것과 같습니다.
물리 설계에서 정하는 것은 저장 레코드의 양식(열의 자료형과 길이), 데이터를 찾아가는 접근 경로(인덱스), 관련 데이터를 모아 두는 방법(클러스터링), 큰 테이블을 나누는 방법(파티셔닝), 테이블이 차지할 공간(사이징), 그리고 데이터를 지킬 백업 방식입니다. 판단 기준은 응답 시간, 저장 공간의 효율, 트랜잭션 처리량입니다.
물리 설계는 사용하는 DBMS 제품의 특성을 많이 탑니다. 같은 기능이라도 제품마다 이름과 옵션이 다르므로, 여기서는 개념을 중심으로 정리하고 예시는 널리 쓰이는 방식으로 듭니다.
인덱스는 책 뒤의 찾아보기처럼, 특정 열의 값과 그 값을 가진 행의 위치를 정리해 둔 구조입니다. 인덱스가 없으면 원하는 행을 찾으려고 테이블 전체를 처음부터 끝까지 읽어야 합니다. 가장 널리 쓰이는 구조는 B-트리(균형 트리) 인덱스로, 값이 정렬된 트리를 따라 내려가 빠르게 찾고 범위 검색에도 강합니다.
인덱스는 행을 실제로 어떻게 저장하느냐에 따라 둘로 나뉩니다. 클러스터드 인덱스(Clustered Index)는 테이블의 행 자체가 인덱스 키 순서대로 정렬되어 저장됩니다. 사전이 단어 순서대로 인쇄된 것과 같아서, 키 범위로 찾으면 붙어 있는 영역만 읽으면 되어 빠릅니다. 행을 실제로 정렬해 둘 수 있는 순서는 하나뿐이므로 테이블당 하나만 만들 수 있습니다. 넌클러스터드 인덱스(Non-Clustered Index)는 행은 그대로 두고 키와 행 위치만 따로 정렬해 둔 것으로, 한 테이블에 여러 개를 만들 수 있습니다.
그 밖에 성별처럼 값의 종류가 아주 적은 열에 값마다 비트 지도를 만들어 두는 비트맵 인덱스, UPPER(이름)처럼 열을 가공한 결과로 만드는 함수 기반 인덱스, 해시 함수로 위치를 바로 계산해 '같다' 비교에는 빠르지만 범위 검색에는 쓸 수 없는 해시 인덱스가 있습니다. 비트맵 인덱스는 조회 위주의 분석 시스템에 맞고, 변경이 잦은 업무 시스템에는 잘 맞지 않습니다.
인덱스는 공짜가 아닙니다. 행을 넣고 고치고 지울 때마다 인덱스도 함께 고쳐야 해서 쓰기 작업이 느려지고 저장 공간도 더 듭니다. 그래서 WHERE 조건, 조인 조건, 정렬에 자주 쓰이고 값의 종류가 많아 잘 걸러지는 열을 골라 만듭니다. 인덱스 열을 함수로 가공해 비교하거나 LIKE '%값'처럼 앞을 와일드카드로 시작하면 인덱스를 제대로 쓰지 못한다는 점도 알아 둡니다.
파티셔닝(Partitioning)은 아주 큰 테이블을 논리적으로는 하나의 테이블로 두면서, 물리적으로는 여러 조각(파티션)으로 나눠 저장하는 기법입니다. 애플리케이션은 평소처럼 한 테이블에 SQL을 보내지만, DBMS는 조건에 해당하는 파티션만 골라 읽습니다(파티션 프루닝). 10년 치 주문 중 올해 것만 조회하면 올해 파티션만 읽는 식입니다.
나누는 방식은 네 가지입니다. 범위 분할(Range Partitioning)은 날짜나 번호 같은 분할 키 값의 구간으로 나눕니다. '1~3월은 1번 파티션, 4~6월은 2번 파티션'처럼요. 해시 분할(Hash Partitioning)은 분할 키에 해시 함수를 적용한 결과로 파티션을 정해 데이터가 고르게 퍼지게 하며, 특정 구간에 데이터가 몰리는 것을 막고 싶을 때 씁니다. 리스트 분할(List Partitioning)은 '서울·경기는 1번, 부산·경남은 2번'처럼 값의 목록을 직접 지정해 나눕니다. 조합 분할(Composite Partitioning)은 범위로 먼저 나누고 그 안을 다시 해시로 나누는 식으로 두 방식을 겹쳐 씁니다.
파티셔닝의 이점은 세 가지로 정리됩니다. 필요한 파티션만 읽으니 조회가 빨라지고(성능), 한 파티션에 문제가 생겨도 나머지는 쓸 수 있으며(가용성), 오래된 파티션만 통째로 지우거나 파티션 단위로 백업할 수 있어 관리가 쉬워집니다(관리 편의). 반면 분할 키를 잘못 고르면 모든 파티션을 다 읽어야 하는 조회가 많아져 오히려 느려질 수 있습니다.
행을 기준으로 나누는 것을 수평 분할, 열을 기준으로 나누는 것을 수직 분할이라고도 합니다. 파티셔닝은 주로 수평 분할이고, 자주 쓰는 열과 잘 쓰지 않는 큰 열(긴 설명, 이미지 같은 것)을 다른 테이블로 떼어 두는 것은 수직 분할에 해당합니다.
디스크에서 데이터를 읽는 단위는 행 하나가 아니라 블록(페이지)입니다. 한 번 읽은 블록 안에 다음에 필요한 데이터가 함께 들어 있으면 디스크를 덜 읽어도 됩니다. 이처럼 방금 쓴 데이터는 곧 다시 쓰이고, 가까이 있는 데이터는 함께 쓰인다는 성질을 지역성(Locality)이라고 합니다. 최근에 쓴 것을 곧 다시 쓰는 시간 지역성과, 가까이 있는 것을 이어서 쓰는 공간 지역성이 대표적입니다. 데이터 지역화는 이 성질을 살려 함께 쓰이는 데이터를 물리적으로 가까이 배치하는 것입니다.
클러스터링(Clustering)은 지역화를 저장 구조로 구현한 대표 기법으로, 같은 키 값을 가진 행들을 같은 블록에 모아 저장합니다. 한 테이블 안에서 같은 부서의 사원들을 모아 둘 수도 있고, 자주 조인하는 [부서]와 [사원]에서 부서번호가 같은 행들을 아예 한 블록에 함께 저장할 수도 있습니다. 조인과 범위 조회는 빨라지지만, 행을 넣거나 키 값을 바꿀 때 제자리를 찾아 넣어야 해서 쓰기가 느려지고 테이블 전체를 훑는 작업은 오히려 느려질 수 있습니다. 그래서 조회가 많고 변경이 적은 테이블에 씁니다.
분산 데이터베이스에서는 지역화가 '데이터를 가장 많이 쓰는 곳 가까이에 둔다'는 뜻으로도 쓰입니다. 부산 지점이 주로 쓰는 데이터를 부산 서버에 두면 원격 접속이 줄어 응답이 빨라집니다. 한편 '클러스터링'은 여러 대의 DB 서버를 하나처럼 묶어 한 대가 멈춰도 서비스를 이어 가게 하는 구성을 가리킬 때도 쓰이므로, 문제가 저장 구조를 말하는지 서버 구성을 말하는지 구별해 읽어야 합니다.
반정규화(Denormalization, 역정규화)는 정규화를 마친 모델에서 조회 성능이나 개발 편의를 위해 일부러 중복을 허용하거나 테이블을 합치고 나누는 것입니다. 매번 여러 테이블을 조인해 계산하던 값을 미리 저장해 두면 조회는 빨라지지만, 같은 값이 여러 곳에 있으니 함께 고쳐 주지 않으면 데이터가 어긋날 위험이 생깁니다.
기법은 네 갈래로 정리할 수 있습니다. 테이블 병합은 1:1이나 1:N 관계의 테이블, 또는 슈퍼타입과 서브타입 테이블을 하나로 합쳐 조인을 줄입니다. 테이블 분할은 한 테이블을 행 기준(수평)이나 열 기준(수직)으로 나눕니다. 중복 테이블 추가는 집계 결과만 모은 통계 테이블, 변경 내역을 담는 이력 테이블, 자주 쓰는 일부만 담은 부분 테이블 등을 따로 둡니다. 중복 컬럼 추가는 다른 테이블의 열을 복사해 두거나(중복 컬럼) 계산 결과를 저장해 두는(파생 컬럼) 방식입니다. 이 밖에 조인 경로를 줄이려고 관계를 하나 더 두는 중복 관계 추가도 있습니다.
반정규화는 마지막 수단입니다. 먼저 인덱스를 손보거나, 클러스터링·파티셔닝을 적용하거나, 응용 프로그램에서 결과를 캐시하는 방법으로 해결되지 않는지 검토합니다. 복잡한 조인을 뷰로 감싸면 SQL은 단순해 보이지만, 일반 뷰는 조회할 때마다 정의를 다시 실행하므로 조회 성능 자체를 높이지는 않습니다(결과를 실제로 저장해 두는 구체화 뷰는 예외입니다). 반정규화를 한다면 중복된 값을 함께 맞춰 주는 트리거나 배치 작업을 같이 설계해야 합니다.
테이블 저장 사이징은 테이블과 인덱스가 앞으로 얼마나 큰 공간을 차지할지 미리 계산해 저장 공간을 준비하고 배치를 정하는 일입니다. 공간이 모자라면 운영 중에 저장이 멈추고, 너무 넉넉하게 잡으면 비용이 낭비됩니다.
기본 계산은 '평균 행 길이 × 예상 행 수'입니다. 평균 행 길이는 열마다의 평균 길이를 더하고 행을 관리하는 데 드는 부가 공간을 얹어 구합니다. 예상 행 수는 처음 옮겨 올 데이터에 '기간당 늘어나는 행 수 × 보관 기간'을 더합니다. 평균 200바이트인 행이 하루 1만 건씩 3년 쌓인다면 200 × 10,000 × 365 × 3으로 약 2.2GB입니다. 여기에 블록마다 수정용으로 비워 두는 여유 공간과 인덱스 크기, 예측이 빗나갈 때를 대비한 여유분을 더해 최종 크기를 정합니다.
크기를 구했다면 배치도 정합니다. 크기와 증가 속도가 비슷한 테이블끼리 같은 저장 영역(테이블스페이스)에 묶고, 테이블과 인덱스를 서로 다른 영역에 두어 디스크 입출력을 나눕니다. 빠르게 커지는 테이블에는 자동 확장 설정과 사용량 감시를 함께 걸어 둡니다.
백업은 장애, 실수, 해킹으로 데이터가 망가졌을 때 되살리기 위한 복사본입니다. 백업 계획을 세울 때는 먼저 두 가지 목표를 정합니다. 장애가 났을 때 어느 시점의 데이터까지 되살려야 하는지를 정한 목표 복구 시점(RPO, Recovery Point Objective)과, 얼마 안에 서비스를 되살려야 하는지를 정한 목표 복구 시간(RTO, Recovery Time Objective)입니다.
백업 범위에 따라 세 가지로 나눕니다. 전체 백업(Full Backup)은 데이터 전체를 복사합니다. 차등 백업(Differential Backup)은 마지막 전체 백업 이후 바뀐 내용을 모두 담으므로 날이 갈수록 커지지만, 복구할 때는 전체 백업과 가장 최근 차등 백업 하나만 있으면 됩니다. 증분 백업(Incremental Backup)은 직전 백업(전체든 증분이든) 이후 바뀐 내용만 담아 가장 작고 빠르지만, 복구할 때는 전체 백업 뒤의 증분 백업을 모두 순서대로 적용해야 합니다.
받는 방식도 나뉩니다. 서비스를 멈추지 않고 운영 중에 받는 핫(온라인) 백업과, 서비스를 멈추고 받는 콜드(오프라인) 백업이 있습니다. 데이터 파일 자체를 복사하는 물리 백업과, 테이블 정의와 데이터를 SQL 문이나 내보내기 파일로 받는 논리 백업도 구별합니다. 변경 내역이 기록된 트랜잭션 로그까지 백업해 두면, 마지막 백업 위에 로그를 다시 적용해 장애 직전이나 원하는 시점으로 되돌릴 수 있습니다(시점 복구).
백업은 받는 것만큼 되살려 보는 것이 중요합니다. 정기적으로 실제 복구 연습을 해서 백업 파일이 멀쩡한지, 목표 시간 안에 복구되는지 확인하고, 백업본은 원본과 다른 장소에도 보관해 화재나 랜섬웨어 같은 큰 사고에 대비합니다.
하루 수백만 건씩 쌓이는 접속 로그 테이블을 접속일 기준 월별 범위 분할로 바꿨습니다. 최근 한 달 조회는 해당 파티션만 읽어 빨라졌고, 보관 기간 1년이 지난 달은 파티션을 통째로 지워, 대량 DELETE로 몇 시간씩 걸리던 정리 작업이 금방 끝나게 됐습니다.
주문 목록 화면이 매번 [주문상세]를 합산하느라 느렸습니다. 인덱스를 손봐도 부족해서 [주문]에 '총액' 파생 컬럼을 추가하는 반정규화를 했습니다. 대신 [주문상세]가 바뀔 때마다 총액을 다시 계산하는 트리거를 함께 만들어 두 값이 어긋나지 않게 했습니다.
쇼핑몰 DB를 일요일 새벽에 전체 백업하고, 다른 날은 매일 새벽 증분 백업, 트랜잭션 로그는 15분마다 백업하도록 정했습니다. 목요일 오후에 실수로 테이블을 지웠을 때, 일요일 전체 백업에 월요일부터 목요일 새벽까지의 증분 백업을 순서대로 적용한 뒤 로그로 삭제 직전 시점까지 되살렸습니다.