[ SQLD ] 데이터 모델과 성능 240806

( 테이블을 분할하는 반정규화 ) 테이블 분할 절차 ( 순서 )

- 데이터 모델링 수행

: 데이터 객체, 속성 그리고 관계를 다이어그램으로 ( ERD )

→ ERD를 기반으로 테이블 ( 릴레이션 ) 을 설계

-

데이터베이스 용량 산정

:  액세스 ( 접근 ) 하는 횟수가 적은 테이블은 분할을 고민할 필요가 없다.

해당 테이블이 미치는 성능이 미미하기 때문이다.

대량의 데이터가 처리되는 테이블이 있다면 → 성능 개선할 여지가 있는지 확인

무엇을 확인하냐면 ↓

 

- 대량 데이터가 처리되는 테이블에 대해 트랜잭션 처리 패턴 분석

: 주로 어떤 작업 ( 조회, 삽입, 수정, 삭제 등 ) 이 발생하는지 분석한다.

작업 패턴이 중구난방이라면 반정규화할 작업이 없다. 일정 패턴이 있는 경우↓

 

- 트랜잭션이 칼럼 단위로 집중되는 경우 테이블 을 수직 분할하고,

로우 단위로 집중되는 경우 테이블을 수평으로 분할한다.

 

정규화를 한다는 것은 정규형 수준이 높아진다는 것

반정규화를 한다는 것은 정규형 수준이 낮아진다는 것

 

반정규화_테이블 수평 분할

- 테이블이 많은 양의 데이터를 가질 것으로 예상되는 경우 → Partioning ( 파티셔닝 )

데이터베이스 행단위로 접근하는 트랜잭션 패턴이 많이 발생하는 경우 테이블을 수평으로 분할하고, 

수평 분할은 데이터베이스의 테이블 ( 릴레이션 ) 을 행 ( Row ) 단위로 나누는 것을 의미한다.

- Range Partition: 범위로 분할

- List Partition: 값으로 분할

- Hash Partition: 해쉬 함수 ( Hash Function ) 로 분할

→ 어떤 데이터를 다른 데이터로 바꿀 때: 임의의 길이의 데이터를 짧은 길이의 데이터로 매핑하는 함수

 

---------------------------------------------------------------------------------------------------------------------------

 

식별자, 비식별자 관계 표시

식별자 관계는 실선으로 표시하고, 비식별자 관계는 점선으로 표시한다.

 

---------------------------------------------------------------------------------------------------------------------------

 

Peter chen 방식에서 관계는 마름모로 나타낸다.

IE 방식에서 관계는 선으로 표시한다.

 

관계의 반정규화

테이블 개수는 변함이 없지만 관계가 늘어나는 경우 관계 추가다.

중복관계 추가: 조인을 통해 정보 조회가 가능하지만, 조인 경로 단축을 위해 중복관계를 추가함

 

반정규화 하기전 다른 방법을 검토할 수 있다.

인덱스 조정을 통해 성능 향상을 유도할 수 있다.

인덱스 ( Index )

 

Select 학번, 이름, 학년 → 테이블에 대해서 세로로 ( 컬럼을 기준으로 ) 필터링From 학생WHERE 학번 > 3000 and 학번 < 5000 → 테이블에 대해서 가로로 ( Row 를 기준으로 ) 필터링

 

기본적으로 관계형 데이터베이스 테이블에서는 레코드가 저장되는 순서가 무작위다 → 정렬이 안되어 있다.그래서 어떤 값을 얻기 위해서는 테이블을 전체를 살펴봐야 하는데 이를 Full Scan 이라고 하고,Full Scan 시 데이터를 전부 살펴봐야 하기 때문에 시간이 많이 걸리고, 여기서 성능을 향상시키고자 만든 장치가 인덱스다.

→ Full Scan 의 경우 인덱스를 사용하면 성능을 향상시킬 수 있다.

 

학번을 가지고 검색 ( 조회 ) 하는 작업이 빈번하게 발생하는데

학번이 정렬이 안되어 있기 때문에 찾는데 시간이 많이 소요된다면

학번으로 인덱스를 하나 만든다 → 인덱스 테이블에 학번을 정렬해서 넣는다.

정리하자면 인덱스는 검색 속도의 향상을 위한 기술이다 → 실제 테이블을 Full Scan ( 전부 살펴보기 ) 하지 않고, 인덱스 테이블을 검색하는데 지나치게 많은 인덱스 생성시 시간 및 공간을 낭비하게 된다 → 인덱스된 필드의 업데이트시 시간이 증가하게 된다.

컬럼에 인덱스가 자동 생성되는 경우는 PK 또는 Unique 조건을 주는 경우고,

Unique 하지 않고 PK도 아닌 컬럼에 Create Index 구문 사용으로 수동으로 인덱스를 생성할 수 있다.

 

인덱스의 스캔 구조 이해

PK의 속성 순서대로 인덱스가 정렬이 되어있다.

조회의 조건으로 더 많이 사용될 것 같은 속성을 PK의 앞으로 정렬을 한다 → 인덱스의 스캔 속도가 빨라진다.

→ PK 에는 인덱스가 ( 자동으로 ) 설정되어 있는데, PK 의 인덱스가 복합 식별자일 때 속성의 순서를 잘 바꿈으로써 성능을 향상시킬 수 있다. 속성의 순서를 잘 바꾼다는 것은 상기 언급한 조회의 조건으로 더 많이 사용될 것 같은 속성을 PK의 앞으로 정렬한다는 것이다.

 

FK는 인덱스가 자동으로 설정되지 않지만 수동으로 설정할 수 있고, ( FK 로 조회를 할 경우가 많을 경우 ) FK 에 인덱스를 설정함으로써 성능을 향상시킬 수 있다.

 

정규화는 반드시 필요한데, 정규화를 조금 포기하더라고 성능을 향상시킬 수 있는 여지가 있지 않을까를 고려한 것이 반정규화고,

반정규화외에 성능을 향상시킬 수 있는 방법이 여러 개 있는데 그 중 하나가 인덱스다.

 

참고 강의

[ 유튜브 ] 데이터베이스실무(SQLD대비) | 김남규 교수