[오늘 한 일]
1. SQLD 자격증 대비반 4주차 정리 & 5주차 수강
2. 아티클 스터디
3. SQL 카타 문제 풀기
4. JD찾아보기
[오늘 한 내용 정리]
1. SQLD 자격증 대비반 4주차 정리 & 5주차 수강
-----------------------------------[4주차 내용정리]-----------------------------------
오늘 알게 된 용어 : 트랜잭션
[데이터 모델 성능]
1) 성능 데이터 모델링 정의
데이터베이스 성능 향상을 목적으로 하는 작업을 말한다.
ex. 비효율적인 상황
case1) 테이블 설계 자체가 잘못되어 쿼리문을 원하는 방식으로 작성하지 못하는 경우
case2) 테이블 내부에 원하는 정보가 없어서 불가피하게 다른 테이블에서 정보를 가져와야 하는 경우
2) 수행시점
사전에 철저하게 설계된 상태로 도입할 수록 비용이 적게 든다. 분석 및 설계 단계에서 철저하게 준비하는 것이 좋음.
어떠한 트랜잭션이 비즈니스 로직의 핵심인지 파악하고 사용자 업무 처리에 중요성을 보이는지 분석 해야함.
*트랜잭션 : 데이터베이스의 상태를 변화시키기 위해 수행하는 작업단위
3) 성능 데이터 모델링 고려사항
a. 정규화를 정확하게 수행
b. 데이터베이스 용량 산정 수행
c. 데이터베이스에서 발생되는 트랜잭션 유형 파악
d. 데이터베이스의 용량과 트랜잭션 유형에 따라 반정규화 수행
e. 이력 모델, PK/FK, 슈퍼 타입/서브 타입 조정
f. 성능 관점에서 데이터 모델 검증
2. 정규화를 통한 성능 향상 전략
정규화를 수행한다는 것은 데이터의 일관성을 유지하고 데이터의 중복성을 방지하며 데이터의 유연성을 유지하기 위해 데이터를 분해하는 과정이다.
*주의) 정규화를 했다고 무조건 성능이 향상되는 것은 아니다. -> 그럴땐 반정규화
3. 정규화 용어 및 이점
1) 용어정리
a. 정규화(Normalization)
b. 정규형(NF : Normal Form)
c. 함수적 종속성(FD : Functional Dependency)
d. 결정자(Determinant)
e. 다치 종속(MVD : MultiValued Dependency)
2) 정규화 이점
a. 데이터 유연성 - 종속성이 강한 데이터를 분리하여 독립된 개념으로 정의하기 때문에 높은 응집도와 낮은 결합도의 원칙에 충실해짐
b. 데이터의 재활용성 : 데이터의 개념이 조금 더 세분화됨에 따라 재활용하기에 용이
c. 데이터의 중복 최소화
2. 정규화 이론
1) 제1 정규화
한 속성에 여러 개의 속성이 포함되어 있거나 같은 유형의 속성이 여러개로 나눠져있는 경우 해당 속성을 분리
2) 제 2 정규화
제1정규화를 만족시키고 PK가 아닌 모든 칼럼은 PK전체에 종속되어야 한다.
3) 제3정규화
제 2정규화를 만족시키고 일반 속성 간에도 함수 종속 관계가 존재하지 않아야 한다.
(이행적 함수 종속 제거)
4) 정규화 순서
제1정규화 - 제2정규화 - 제3정규화
- 코드 정규화(BCNF) : 결정자 안에 함수 종속을 가진 주식별자 속성을 분리
- 제4정규화 : 다인 종속성(Multi-Valued Dependency) 제거
- 제5정규화 : 조인 속성(join Dependency) 제거
3. 반정규화 개념과 설명
1) 반정규화를 통한 성능 향상 전략
정규화를 수행하지 않은 모델을 지칭할 때 사용. 정규화와 다르게 중복을 통해 목적을 달성하고자 한다. 다음과 같은 상황에서 반정규화 적용
ex.)
상황1. 데이터를 조회할 때 디스트 I/O량이 많아서 성능이 저하되는 경우
상황2. 테이블 간 경로가 너무 멀어 조인으로 인한 성능 저하가 예상되는 경우
상황3. 칼럼을 계산하여 읽을 때 성능이 저해할 것이라고 예상하는 경우
2) 반정규화는 '조회' 성능 향상에 초점
3) 절차 (반정규화는 반드시 적용해야 하는 요소가 아니라 수행 과정에서 필요시 적용)
-> 대상 조사 - 다른 방법 유도 & 검토 - 반정규화 적용
2. 반정규화 기법
1) 테이블 반정규화
a) 테이블 병합
b) 테이블 분할(수직 or 수평 분할)
c) 테이블 추가(중복 or 통계 or 이력 or 부분 테이블)
2) 칼럼 반정규화
a. 중복 칼럼 추가
b. 파생 칼럼 추가
c. 이력 테이블 칼럼 추가
d. PK에 의한 칼럼 추가
e. 응용 시스템의 오작동을 위한 칼럼 추가
3) 관계 반정규화 - 관계를 추가하여 조회 성능을 향상시키는 것
-----------------------------------[5주차 내용정리]-----------------------------------
[대량 데이터에 따른 성능]
1. 개요
1) 개요
아무리 설계가 잘되어 있더라도 대량의 데이터가 하나의 테이블에 집약되는 경우 성능 저하는 불가피
-> 테이블 단위의 분할(수직/수평 분할)을 통해 문제해결이 가능
*참고) 데이터베이스 입출력(I/O)
- 입력/출력 뿐만 아니라 데이터를 조작하는 모든 행위를 의미한다.
- 테이블 내의 모든 행은 블록(Block) 단위로 디스크에 저장
- 칼럼이 많아지면 하나의 행을 저장할 때 물리적인 디스크에 여러 블록에 걸쳐 데이터가 저장될 가능성이 높아진다. 이런 경우 하나의 행을 읽더라도 여러 개의 블록을 읽는 것이 좋다.
2) 성능저하 현상
a. 로우 체이닝(Row Chaining)
- 행 데이터가 너무 길어 데이터 블록 하나에 데이터가 모두 저장되지 않고 2개 이상의 블록에 걸쳐서 하나의 행이 저장된 형태
b. 로우 마이그레이션(Row Migration)
- 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈 공간을 찾아서 저장하는 방식으로 처리된 형태다.
> 두 현상 모두 데이터베이스의 블록 많은 데이터가 저장되고 데이터 조회 과정에서 입/출력 횟수가 증가하게 된다.
*참고) 데이터베이스 파티셔닝(Partitioning)
- 규모가 커짐에 따라 저장 및 관리할 데이터가 많아지면서 이를 해결하고자 테이블을 작은 단위로 쪼개 관리하는 파티셔닝 기법이 발생.
- 논리적인 데이터를 다수의 Entity로 쪼개는 행위로 큰 테이블이나 인덱스를 관리하기 쉬운 Partition이라는 작은 단위로 물리적인 분할을 하는 것을 의미한다.
- 테이블을 물리적으로 분할하지만 데이터베이스에 접근할 때는 분할된 테이블로 인식하지 못한다는 특징이 있다.
2. 테이블 수직 불할]
- 칼럼이 엄청 많을 때, 비슷한 속성을 묶어서 수직분할
3. 테이블 수평 분할]
1) 범위 분할(range partition) ex. 1월 / 2월 / 3월 ---
2) 목록 분할(list partition) ex. 지역 기준) 서울 / 부산 / 제주 ---
3) 해시 분할(Hash partition) : 해싱 알고리즘을 적용하여 테이블 분리(데이터 관리보다는 성능 향상에 목적)
- 특정 파티션에 데이터가 집중될 가능성이 있는 범위 분할의 단점 보완
- 데이터 처리가 많아지는 경우에 경합을 막을 수 있음.
4) 합성 분할(Composition Partitioning)
- 위에서 살펴본 3개의 분할 방식을 섞는 방법을 의미하는데, ex. 범위 분할로 분할 이후 다시 해시 함수를 적용하여 분할하는 방식이 있다.
4. 수직/수평분할 절차
step1 모델링 진행
step2 용량 산정
step3 데이터 처리 과정에서 트랜잭션 처리 패턴 분석 (ex. 결재 트랜젝션, 주문 트랜잭션)
step4 데이터 처리 과정이 칼럼과 로우 중 어디에 집중되는지 분석하고 집중된 부분의 테이블을 파티셔닝한다.
[데이터베이스의 구조와 성능]
1. 슈퍼타입 / 서브타입 모델의 성능 고려방법
1) 슈퍼타입/ 서브타입 기본
- 이 데이터 모델은 업무를 구성하는 데이터의 특징을 공통점과 차이점으로 나누어 표현 가능하기 때문에 직관적인 모델링이 가능하다.
- 슈퍼타입은 공통적인 속성, 서브타입은 자신만의 속성을 의미한다. 공통적인 부분을 슈퍼 엔터티로 만들고 그것으로부터 상속받아 다른 엔터티와 차이가 있는 속성에 대해서는별 도의 서브 타입 엔터티로 구분하는 방식이다.
2) 중요성
a. 트랜잭션은 항상 일괄적으로 처리된다.
b. 트랜잭션은 항상 서브타입을 개별로 처리된다.
c. 트랜잭션은 항상 슈퍼타입과 서브타입을 공통으로 처리된다.
> 트랜잭션이 얼마만큼 빈번하게 처리되는지에 따라 테이블을 설계해야 성능 저하에 대한 부분을 최소화시킬 있다.
> 슈퍼타입과 서브타입 성능을 고려한 물리적인 데이터 모델로 변환하는 기준은 데이터가 얼마나 많은지와 테이블에서 발생하는 트랜잭션의 유형에 따라 결정된다.
2.인덱스 특성을 고려한 PK/FK 데이터베이스 성능 향상
1) PK순서와 성능 사이의 관계
PK가 여러개 있을 때(like 복합키). 복합 pk 안 pk 순서에 따라 성능이 다를 수 있다. 만약 특정 PK를 많이 조회하는 경우엔 그 특정 pk 순서를 우선하는 것이 좋음. 혹은 조회할 때 PK B가 PK A보다 범위가 적은 경우 B를 앞 순서에 두는 것이 다.
2) FK와 테이블 인덱스 구성 관계
물리적인 외래키(FK)의 생성 여부와 상관없이 논리/물리 FK 제약 조건은 외래키 칼럼에 대해 인덱스를 생성하는 것이 성능 상의 우위를 가질 확률이 높다.
[분산 데이터베이스와 성능]
1. 개요
1) 정의
분산 데이터베이스는 여러 곳에 분산되어 있는 데이터베이스 시스템을 하나의 가상시스템으로 사용할 수 있도록 한 데이터베이스다.
논리적으로 동일한 시스템이지만 네트워크를 통해 물리적으로 분산되어 있는 데이터의 모임을 의미.
2) 분산 데이터베이스가되 위해 서는 6가지투명성을만족해야한다.
a. 분할 투명성
b. 위치 투명성
c. 지역 사상 투명성
d. 중복 투명성
e. 장애 투명성
f. 병행 투명성
3) 적용 방법
업무 구성에 따른 아키텍쳐가 어떤 형태로 구성되어 있는지 등 환경에 따라 충분하게 고려한 뒤 적용해야 한다.
2. 분산 뎅터베이스 적용 기법
1) 테이블 위치 분산
설계된 테이블의 위치를 다르게 위치, ex. 자재 품목은 본사에서 구입하고 각 지사별로 자재 품목을 이용하여 제품을 구분
2) 테이블 분할 분산
단순히 위치만 다른 곳에 두는 것이 아니라 각 테이블을 쪼개서 분산하는 방법.
3) 테이블 복제 분산
동일 테이블을 다른 지역이나 서버에서 동시에 생성하고 관리하는 유형
a. 부분 복제
마스터 데이터베이스를 가지고 있으면서 각 지사별로는 지사에 해당하는 로우를 가지고 있는 형태
b. 광역 복제
각 지사별로 마스터 데이터베이스와 동일한 데이터를 가지고 있는 형태
4) 테이블 요약 분산
a. 분석요약(Rollup Summarization)
동일한 테이블 구조를 가지고 있으면서 분산되어 있는 동일한 내용의 데이터를 이용한 통합된 데이터를 산출하는 방식
b. 통합요약(Consolidation Summerization)
분산되어 있는 다른 내용의 데이터를 이용하여 통합된 데이터를 산풀하는 방식
ex. 분석 : A라는 상품에 대한 모든 판매 실적을 모든 분산 데이터베이스가 가지고 있지만 디테일한 실적은 모두 다르다.
통합 : 각 분산 데이터베이스 마다 다른 상품에 대한 데이터를 가지고 있고 이를 통합 데이터베이스가 모아서 전체를 산출.
3. 방법에는 절대적인 것이 없기에 다양한 방법론 사례들을 공부하여 활용하면 좋다!
2. SQL 카타 문제 풀기 - 8문제
-> SQL 써본 코드
SELECT ANIMAL_ID
, COUNT(DISTINCT NAME) as count
FROM ANIMAL_INS
#DISTINCT를 쓰면 기본적으로 NULL값은 포함하지 않기 때문에 WHERE ~ IS NOT NULL 생략가능
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY __ desc / asc # 뒤에 LIMIT 1하면 1개의 결과값만 보임
# Rank 함수 사용법
RANK() OVER (ORDER BY 'column' DESC/ESC)