[TIL] 물리적 모델링

물리적 모델링 정의 

• 논리 모델을 실제 DBMS에 맞게 구현
• DB 성능고려한 테이블, 인덱스, 파티션, 스키마 정의 등을 진행한다.
• 산출물 : 실제 물리 DB

 

물리적 모델링 - 기본

테이블 설계

• 논리 모델을 바탕으로 실제 데이터베이스에서 동작할 수 있는 구체적인 테이블 구조를 설계한다.
• PostgreSQL, MySQL과 같은 DBMS의 특성을 고려해서 테이블 명명, 데이터 타입, 제약조건등을 설계하는 것이 핵심
• 테이블 설계 흐름
  1. 테이블 명명 규칙에 따른 테이블 생성(DBMS 권장 사항 체크, 팀 컨벤션체크)
  2. 컬럼 데이터 타입 선정
  3. 기본값과 제약조건 설정 (기본키 구현전략, 외래키 제약조건 구현 전략, 고유키 제약조건 구현 전략)
  4. 참조 무결성 규칙 설정 : (DB의 두 테이블 간의 관계가 항상 일관성을 유지하도록 보장하는 제약조건)
     • CASCADE : 부모 삭제 시 자식도 함께 삭제
     • RESTRICT : 자식이 참조 중이면 부모 삭제 금지
     • SET NULL : 부모 삭제 시 자식의 FK를 NULL로
     • NO ACTION : 기본값, RESTRICT와 유사하나 트랜잭션 종료 시점 체크
     • SET DEFAULT : 부모 삭제 시 자식의 FK를 지정된 기본값으로 변경 (실무에서는 잘 안 쓰이지만 이론상 옵션에 포함됨)

 

물리적 모델링 -  성능

인덱스 설계

• 데이터베이스는 데이터를 저장하는 목적도 있지만 데이터를 읽기 위해서도 사용이 된다. 이때 테이블의 데이터를 빠르게 검색하기 위해 사용되는 색인 구조를 인덱스라고한다.
• DBMS마다 인덱스를 작동하는 구조는 조금씩 다를 수 있으며 나는 PostgreSQL을 기반으로 작성할 예정이다.

인덱스 설계 전략

전략 항목	설계 방향
사용 빈도 중심	WHERE, JOIN, ORDER BY 절에 자주 등장하는 컬럼 중심 인덱스 설계
선택도 고려	값의 종류가 다양하고 중복이 적은 컬럼(선택도 높음)에 우선 적용
조합 조건 대비	복합 인덱스는 컬럼 순서를 WHERE 조건의 사용 패턴에 맞춰 설계
유지관리 비용 고려	INSERT/UPDATE/DELETE 와 같은 DML이 빈번한 테이블은 과도한 인덱스를 피함
실행계획 분석 병행	EXPLAIN, EXPLAIN ANALYZE로 인덱스 효과 검증

 

 

인덱스를 만들 수 있는 내부 구조 

• B-Tree와 / B+Tree를 사용한다.
• 공통점
  
  • 하나의 노드가 여러 개의 키(값)를 가질 수 있다 (다진 트리)
  • 노드 하나가 여러 자식으로 분기(뻗어나감)될 수 있다
• 차이점

구분	B-Tree	B+Tree
리프 노드끼리 연결	X	O (연결 리스트로 순차 연결)
중간 노드에 실제 데이터	O (중간 노드도 데이터 보유)	X (중간 노드는 인덱스 역할만, 실제 데이터는 리프에만)

 

B+Tree 리프 연결은 이론상 단방향이 기본이지만, 실무 DBMS(MySQL InnoDB, PostgreSQL 등)는 역순 스캔(ORDER BY DESC)도 지원하기 위해 양방향으로 구현하는 경우가 많다고 하여 아래와 같은 이미지가 완성되었다

 

[MySQL] B-Tree로 인덱스(Index)에 대해 쉽고 완벽하게 이해하기

위에 블로그 정리 진짜 잘해놓으셨음... 꼭 보세요 두번보세여 천천히 보세여

 

 

클러스터형 인덱스 (Clustered Index)

• 이미 테이블 데이터가 인덱스 기준으로 정리된 상태
• 보통은 PK 기준으로 클러스터형 인덱스가 만들어진다.
• 테이블당 1개만 가능 (데이터 자체의 물리적 정렬 순서이기 때문)

비클러스터형 인덱스 (Non-clustered Index)

• 별도의 인덱스가 데이터 위치를 가리킴
• 인덱스 자체와 실제 데이터가 분리되어있음
• 테이블당 여러 개 생성 가능

 

 

좋은 인덱스란?

• 검색할 때 불필요한 데이터를 최대한 많이 걸러내는 인덱스 →즉, 좁혀지는 컬럼을 선택하는 게 핵심(중복이 최소인 컬럼)
• Cardinality(카디널리티)
  • 정의 : 해당 컬럼이 갖는 고유한 값의 개수
  • 카디널리티가 높다 = 거의 다 유니크하다 (예: 주민번호)
  • 카디널리티가 낮다 = 값이 다 비슷비슷 함 (예 : 성별)
• Selectivity (선택도)
  • 정의 : 실제로 얼마나 세밀하게 원하는 데이터를 걸러낼 수 있는가
  • 공식 : Selectivity = Cardinality / 전체 row 수
  • 1에 가까울수록 유니크하다는 뜻으로 인덱스로터 효율이 좋다는 뜻!
• 복합 인덱스 (Composite Index)
  • 실제 테이블에서는 컬럼 하나가 아니라 여러개를 묶어서 인덱스 거는 경우 많음 이때, 컬럼 순서 매우 중요

CREATE INDEX idx_users
ON users (Location, Name, ID)

 

• 인덱스는 명시한 순서대로 정렬/구성되기 때문에 앞쪽 컬럼 기준으로 조회하는 쿼리에서는 효율적이지만 뒤쪽 컬럼만 단독으로 검색 조건에 쓰면 인덱스를 제대로 탈 수 없음 왜냐? Location → Name → ID 순서대로 정렬됐으니! ID를 먼저 조건 하면 인덱스가 없는 거지 뭐

 

인덱스가 많을수록 성능이 좋아지는가? → NO!!

• 인덱스도 결국 별도의 자료구조(트리)를 하나 더 관리하는 것과 같음
  • 공간 비용 증가 : 인덱스도 데이터를 저장하는 구조이므로 저장공간 차지
  • 쓰기(갱신) 비용 증가 : 원본 데이터가 INSERT/UPDATE/DELETE 될 때마다, 인덱스로 걸린 컬럼의 값도 바뀌면 인덱스 트리 내용도 함께 갱신해야 한다.
• 결론 : 읽기 성능(조회)은 좋아지지만, 쓰기 성능(갱신)은 나빠진다 → 인덱스는 무조건 많이 만드는 게 좋은 게 아니고 조회 패턴을 분석해서 필요한 곳에만 딱! 만드는 것이 중요

 

인덱스는 항상 빠른가? → NO!!

• 기본 전제 : 인덱스 스캔 vs 풀 테이블 스캔의 비용 차이
• 인덱스를 통해 레코드 1건을 읽는 건 테이블을 순차적으로 직접 읽는 것보다 4 ~ 5배 정도 더 비싸다고 예측이 된다고 함
  • 왜? 인덱스 스캔은 인덱스 트리 탐색 → PK 확인 → 그 PK로 실제 데이터 위치를 다시 찾아가는 방식이기 때문에 디스크에 랜덤 액세스(무작위 접근)가 발생하기 때문
• 그럼 인덱스를 왜 쓰는가?
  • 인덱스가 없다면 DBMS는 원하는 레코드가 어디 있는지 몰라서 테이블 전체를 뒤지는 풀 테이블 스캔을 해야 함 → 디스크 읽기 량이 매우 커서 느려짐
  • 인덱스를 쓰면? PK로 위치를 먼저 찾고 그 PK로 레코드가 저장된 위치에 바로 접근 가능 불필요한 디스크 읽기를 크게 줄이는 게 가능 
• 그럼 인덱스 쓰는 게 조회할 때는 항상 유리한 거 아닌가? 
  • 인덱스 스캔은 레코드 1건당 비용이 4~5배 더 비싸기 때문에 읽어야 할 레코드 수가 전체 테이블의 20 ~ 25%를 넘어서면 오히려 인덱스를 하지 않는 게 더 효과적이라고 한다. 이 경우에 DBMS는 인덱스 무시 테이블 풀 스캔으로 처리한다.
• 결과적으론 인덱스는 적은 비율의 데이터를 정확히 걸러낼 때 유리하고 조회범위가 넓어질수록 비효율적이게 된다. 이게 앞에서 다룬 Selectivity가 인데스 효율이 직접 영향을 준다는 근거이기도 하다!

인덱스 이해는 되는데 직접 하라고 한다고 하면? 흠.. 주말 이용해서 코드잇에서 제공해 주는 인덱싱 실습을 진행해 보고 진짜 느껴보자

 

설계 검증과 최적화 그리고 역정규화

• 역정규화는 정규화된 데이터 모델이 가지는 성능적 한계를 극복하기 위해 의도적으로 데이터 중복, 컬럼 통합, 집계 저장 등을 수행하는 설계 기법으로 주로 대량 조인 제거, 집계 연산 제거 등 조회 성능 개선을 목적으로 진행된다.
• 하지만 이러한 대가로 데이터 정합성 유지 로직 추가, 갱신 시점의 동기화 문제 모델 복잡도 등등 단점이 발생하기 때문에 정말 심각히 고려해봐야 할 사항이다.
• 실무에서는 많이 사용하지않는다고 한다.