백짱미 개발log

최근에 erd 설계를 (오랜만에) 하게 되면서 createdAt, updatedAt에 쓰이는 type에 관해 고민이 생겼다.

지금껏 datetime으로 해놨었는데, datetime 외에도 timestamp와 같이 시간을 기록할 수 있는 컬럼에 관해 표현할 수 있는 type은 다양하다.

오늘은 시간 관련 type에 대해 알아보고, 이를 적재적소 어떻게 쓰는지 알아볼 예정이다!

TIME

time 타입은 HH:MM:SS로 시간에 대한 정보를 담는 타입이다.

time이 가질 수 있는 값의 범위는 -838:59:59 ~ 838:59:59이다.

여기서 시간은 날짜 중에 day의 값을 표현할 수 있는 범위까지이기에 838시간이라는 큰 시간까지 포함이 가능하다.

TIMESTAMP

timestamp 타입은 날짜, 시간 모두 포함한 타입이다.

범위로는 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 UTC까지 표현할 수 있다.

DATE

date 타입은 날짜는 포함하지만 시간은 포함하지 않을 때 사용하는 타입이다.

YYYY-MM-DD 형식으로 입력이 가능하며, '1000-01-01'부터 '9999-12-31'까지만 입력 가능하다.

DATETIME 타입

datetime 타입은 날짜와 시간 모두 포함할 때 사용하는 타입이다.

YYYY-MM-DD HH:MM:SS 형식으로 '1000-01-01 00:00:00'부터 '9999-12-31 23:59:59'까지 입력 가능하다.

TIMESTAMP vs DATETIME

1. 타입

TIMESTAMP는 숫자형, DATETIME은 문자형이다

2. 용량

TIMESTAMP는 4byte, DATETIME은 8byte이다.

3. 입력

TIMESTAMP는 데이터값을 입력해주지 않고 저장시 자동으로 현재 날짜가 입력된다. (Default Insert (AUTO))

DATETIME은 데이터값을 입력해주어야만 날짜가 입력된다. (명시적 INSERT)

TIMESTAMP의 경우 Timezone을 적용해서 보여준다.

Timezone
타임존은 동일한 로컬 시간을 따르는 지역을 의미하며, 해당 국가에 의해 법적으로 지정된다.

DATE와 DATETIME의 경우 범위가 거의 같지만 TIMESTAMP의 경우 다르기에 사용할 때 목적에 따라 명확히 구분해서 사용해야한다!

요즘 프로젝트하면서 erd 설계를 하고 있는데, 테이블 연관관계와 정규화에 대해 더 알고 싶어 블로그에 기록하게 되었다!

정규화에 대해 알아보자.

정규화

이상현상이 있는 릴레이션을 분해하여 이상현상을 없애는 과정, 이로써 여러 개의 릴레이션을 생성하게 된다.
이를 단계별로 구분하여 정규형이 높아질 수록 이상현상은 줄어든다.

여러 엔티티의 속성을 혼합하면 정보가 중복 저장된다. 정규화의 기본 목표는 테이블 간 중복된 데이터를 허용하지 않는다.

이러한 테이블을 분해하는 정규화 단계가 정의되어 있는데 여기서 테이블을 어떻게 분해하는지에 따라 정규화 단계가 달라진다.

그 전에, 이상현상과 함수 종속성에 대해 짚고 넘어가자.

이상현상

• 삽입 이상 (Insertion Anomaly) : 튜플 삽입 시 특정 속성에 해당하는 값이 없어 NULL을 입력해야 하는 현상
• 삭제 이상 (Deletion Anomaly) : 튜플 삭제 시 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 현상
• 갱신 이상 (Update Anomaly) : 튜플 갱신 시 중복된 데이터의 일부만 갱신되어 일어나는 데이터 불일치 현상

위와 같은 테이블을 예로 들어보자.

1) 삽입 이상

 만약 강의를 수강하지 않은 학생을 추가할 때 과목 번호와 성적에 null 값이 들어가거나 불필요한 데이터를 추가해야 삽입할 수 있는 문제점이 발생한다. 아래와 같은 데이터를 삽입할 수 없다.

학생이 수강신청 할 때 반드시 과목 번호를 알아야 삽입이 가능하다.

2) 삭제 이상

학번이 345인 학생이 수강 신청한 과목을 취소하면 해당 강의에 대한 정보 모두 삭제된다.

가 교수가 강의하는 과목을 취소하는 경우 해당 강의를 듣는 학생에 대한 정보 모두 삭제된다.

3) 갱신 이상

학번이 123인 학생의 지도교수가 라로 변경되면, 이 학생이 수강하는 모든 과목 (행)에서의 지도 교수를 변경시켜줘야 한다.

함수 종속성 (Functional Dependency)

• 어떤 속성 A의 값을 알면 다른 속성 B의 값이 유일하게 정해지는 관계
• A -> B로 표기하며 A를 B의 결정자(Determinant)라고 한다.
• A -> B이면 A는 B를 결정한다(Determine) 한다고 하고, B는 A에 종속한다(Dependent)라고 한다.

함수 종속성은 크게 세 가지로 나뉜다.

1) 완전 함수 종속 : 기본키를 구성하는 모든 속성에 종속되는 경우

2) 부분 함수 종속 : 기본키를 구성하는 속성의 일부에 종속되거나, 기본키가 아닌 다른 속성에 종속되는 경우

3) 이행적 함수 종속 : A, B, C 세 속성이 있고, A -> B, B -> C 종속 관계가 있을 때 A -> C가 성립하는 경우

위와 같은 테이블이 있다고 하자.

여러 학생들이 있고 각 학생별로 여러 과목을 수강한다고 하면 위 테이블의 기본 키는 (학번, 과목번호)이다.

그렇다면 이름과 학년은 학번만 알아도 유일하게 결정된다.

반면에 성적은 학번과 과목 번호를 모두 알아야 유일하게 결정된다.

따라서 이름과 학년은 (학번, 과목번호)에 대해 부분 함수 종속이고, 성적은 완전 함수 종속이다.

이렇게 엔티티를 구성하는 속성간의 함수 종속성을 판단하여 좋은 릴레이션인지 알 수 있다.

제1 정규형 (1NF)

제1 정규형은 다음과 같은 규칙들을 만족해야 한다.

1. 각 컬럼이 하나의 속성만을 가져야 한다.
2. 하나의 컬럼은 같은 종류나 타입(type)의 값을 가져야 한다.
3. 각 컬럼이 유일한(unique) 이름을 가져야 한다.
4. 칼럼의 순서가 상관없어야 한다.

상단의 테이블을 보면 '나'는 여러 개의 취미를 갖고 있으므로 제1 정규형을 만족하지 못한다. 그렇기에 제1 정규화하여 분해할 수 있다.

제2 정규형 (2NF)

제2 정규형은 다음과 같은 규칙을 만족해야 한다.

1. 제1 정규형을 만족해야 한다.
2. 모든 컬럼이 부분적 종속(Partial Dependency)이 없어야 한다. 즉 모든 칼럼이 완전 함수 종속을 만족해야 한다.

부분적 종속이란 기본키 중 특정 컬럼에만 종속되는 것이다. 제1 정규화를 진행한 테이블에 대해 완전 함수 종속을 만족하도록 테이블을 분해하는 것이다.

즉 기본키의 부분집합이 결정자가 되어선 안된다는 것이다.

이 테이블의 기본 키는 (학번, 강좌 이름)로 복합키이다. 그리고 (학번, 강좌 이름)인 기본 키는 성적을 결정하고 있다.

(학번, 강좌이름) -> (성적)

여기서 강의실 컬럼은 기본키의 부분집합인 강좌이름에 의해 결정될 수 있다.

(강좌이름) -> (강의실)

즉, 기본키(학번, 강좌이름)의 부분키인 강좌이름이 결정자이기 때문에 위의 테이블의 경우 다음과 같이 기존의 테이블에서 강의실을 분해하여 별도의 테이블로 관리하여 제2 정규형을 만족시킬 수 있다.

제3 정규화 (3NF)

제3 정규형은 다음과 같은 규칙을 만족해야 한다.

1. 제2 정규형을 만족해야 한다.
2. 기본키를 제외한 속성들 간의 이행 종속성 (Transitive Dependency)이 없어야 한다.

여기서 이행적 종속이라는 것은 A -> B, B -> C가 성립할 때 A -> C가 성립되는 것을 의미한다.
예를 들어 아래와 같은 계절 학기 테이블을 살펴보자. 

기존의 테이블에서 학번은 강좌 이름을 결정하고 있고, 강좌 이름은 수강료를 결정하고 있다.

그렇기 때문에 이를 (학생 번호, 강좌 이름) 테이블과 (강좌 이름, 수강료) 테이블로 분해해야 한다.

BCNF (Boyce-Codd Normal Form)

BCNF는 제 3정규형을 좀 더 강화한 버전으로 다음과 같은 규칙을 만족해야 한다.

1. 3정규형을 만족해야 한다.
2. 모든 결정자가 후보키 집합에 속해야 한다.

예를 들어 다음과 같은 특강수강 테이블이 존재한다고 하자.

특강수강 테이블에서 기본키는 (학번, 특강이름)이다. 그리고 기본키 (학번, 특강이름)는 교수를 결정하고 있다. 또한 여기서 교수는 특강이름을 결정하고 있다.
그런데 문제는 교수가 특강이름을 결정하는 결정자이지만 후보키가 아니다.

이처럼 후보키 집합이 아닌 칼럼이 결정자가 되어버린 상황을 BCNF를 만족하지 않는다고 한다.

(참고로 위 테이블은 제3 정규형까지는 만족하는 테이블이다 )

그렇기 때문에 BCNF 정규화를 만족시키기 위해서 위의 테이블을 (학번, 특강이름), (특강이름, 교수) 테이블로 분해해야 한다.

정규화 장단점

장점

• 데이터베이스 변경 시 각종 이상 현상들이 발생하는 문제점을 해결할 수 있다.
• 데이터베이스 구조 확장 시 정규화된 데이터베이스 구조에서는 새로운 데이터 형의 추가로 인한 확장 시, 그 구조를 변경하지 않아도 되거나 일부만 변경해도 된다. 이는 데이터베이스와 연동된 응용 프로그램에 최소한의 영향만을 미치게 되며 응용프로그램의 생명을 연장시킨다.
• 사용자에게 데이터 모델을 더욱 의미있게 제공
• 정규화된 테이블들과 정규화된 테이블들간의 관계들은 현실 세계에서의 개념들과 그들간의 관계들을 반영

단점

• 릴레이션의 분해로 인해 릴레이션 간의 연산(JOIN 연산) 증가되어 질의에 대한 응답 시간이 느려질 수 있다.

정규화를 수행한다는 것은 데이터를 결정하는 결정자에 의해 함수적 종속을 가지고 있는 일반 속성을 의존자로 하여 입력/수정/삭제 이상을 제거하는 것이다. 데이터의 중복 속성을 제거하고 결정자에 의해 동일한 의미의 일반 속성이 하나의 테이블로 집약되므로 한 테이블의 데이터 용량이 최소화되는 효과가 있다.

따라서 정규화된 테이블은 데이터를 처리할 때 속도가 빨라질 수도 있고 느려질 수도 있는 특성이 있다.

조회를 하는 SQL 문장에서 조인이 많이 발생하여 이로 인한 성능저하가 나타나는 경우에 반정규화 적용이 필요하다.

반정규화(De-normalization, 비정규화)

데이터베이스의 성능 향상을 위하여, 데이터 중복을 허용하고 조인을 줄이는 데이터베이스 성능 향상 방법이다.

반정규화는 조회(select) 속도를 향상시키지만, 데이터 모델의 유연성은 낮아진다.

반정규화의 대상

• 정규화에 충실하여 종속성, 활용성은 향상 되었지만 수행속도가 느려진 경우
• 다량의 범위를 자주 처리해야하는 경우
• 특정 범위의 데이터만 자주 처리하는 경우
• 요약/집계 정보가 자주 요구되는 경우
• 테이블에 지나치게 조인을 많이 사용하게 되어 데이터를 조회하는 것이 기술적으로 어려울 경우

반정규화 기법

계산된 컬럼 추가
배치 프로그램으로 총판매액, 평균잔고, 계좌평가를 미리 계산하고 그 결과를 특정 칼럼에 추가한다.

테이블 수직 분할
하나의 테이블의 두 개 이상의 테이블로 분할한다. 즉, 칼럼을 분할하여 새로운 테이블을 만드는 것이다.

테이블 수평분할
하나의 테이블에 있는 값을 기준으로 테이블을 분할하는 방법이다.

반정규화 주의사항

 반정규화를 과도하게 적용하다 보면 데이터의 무결성이 깨질 수 있다.

 또한 입력, 수정, 삭제의 질의문에 대한 응답 시간이 늦어질 수 있다.

레퍼런스
https://rebro.kr/159
https://mangkyu.tistory.com/110
https://github.com/JaeYeopHan/Interview_Question_for_Beginner/tree/master/Database#%EC%A0%95%EA%B7%9C%ED%99%94%EC%97%90-%EB%8C%80%ED%95%B4%EC%84%9C
https://code-lab1.tistory.com/48

흔히 백엔드 개발자들이 사용하는 MySQL이라 부르는 것은 MySQL 서버라고 부를 수 있다.

MySQL 서버는 두뇌 역할을 하는 MySQL 실행 엔진이 있고, 손 발 역할을 하는 스토리지 엔진으로 구성된다.

이 둘은 핸들러 API를 통해 서로 통신을 주고 받는다.

쿼리의 실행 과정을 통해 아키텍처에 대해 알아보자.

간단한 join문으로 구성된 쿼리문을 실행한다.

select u.userIdx, u.userName, b.buyList from Buy
	left join User as u on u.userIdx = b.userIdx

1. 쿼리 파서 (Parser)

사용자가 SQL 쿼리문을 날리게 되면 쿼리 파서가 요청을 수행한다.

이 쿼리 파서는 SQL 문장을 트리의 형태로 파싱한다.

이 트리 안에 있는 각각의 요소는 MySQL이 인지할 수 있는 최소한의 단위인 Token으로 파싱이 된다.

SQL 문법 오류 여부를 확인하고 예외가 있다면 예외 메세지를 반환한다.

2. 전처리기 (PreProcessor)

다음으로 전처리기에 파서 트리 데이터가 전달된다.

전처리기는 예약어를 제외한 Token을 검사해서 데이터베이스에 실제로 객체가 존재하는지, 실제로 그 객체에 사용자가 접근할 수 있는지 권한을 검증한다.

3. 옵티마이저 (QueryOptimizer)

전처리기에서 수행된 데이터를 바탕으로 옵티마이저에게 전달한다.

옵티마이저는 파서 트리를 실행 계획으로 바꾸는 역할을 한다. 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 것을 목적으로 한다.

1. 데이터 접근 방법 결정
2. join 등 쿼리 재작성 및 실행
3. 테이블 접근/스캔 순서 결정하여 사용할 인덱스 선택

이런 옵티마이저는 크게 두 종류로 구분할 수 있다.

비용 기반 최적화와 규칙 기반 최적화로 나눌 수 있는데 전자는 MySQL에 존재하는 다양한 통계 정보를 활용해 수행 계획을 수립, 후자는 동일한 SQL이면 항상 동일한 SQL 실행 계획을 수립하는 것이다.

MySQL 포함한 대부분의 RDBMS는 비용 기반 최적화로 진행된다.

비용 기반 최적화

SQL문을 처리하는데 필요 비용이 가장 적은 실행 계획을 선택하는 방식이다. 

비용이란 SQL문을 처리하기 위해 예상되는 소유시간 또는 자원 사용량을 의미하며 비용 기반 최적화는 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보 등을 이용한다.

비용기반은 통계정보가 없는 경우 불확실한 실행계획을 생성 할 수 있으므로 정확한 통계정보를 유지하는 것이 중요하다.

# 통계 정보란? (MySQL)
 대략의 레코드 건수와 인덱스의 유니크한 값의 개수. 벤더마다 약간씩 차이가 있다.
 Memory 테이블은 별도 통계 정보가 없으며, MyISAM과 InnoDB의 테이블과 인덱스 통계 정보는 아래와 같이 확인 할 수 있다.

SHOW TABLE STATUS LIKE 'user_ldap_list';
SHOW INDEX FROM user_ldap_list;

# 통계정보 수집시 고려사항

• 시간 : 시스템 부하가 적은 날짜와 시간을 산정해 수집
• 샘플 크기 : 데이터베이스와 세그먼트의 크기에 비례해 일정 부분만 추출
• 정확성 : 오브젝트의 데이터와 통계정보의 데이터가 근접해야 함
• 안정성 : 통계정보 수집으로 인한 데이터베이스 성능 저하를 최소화해야 함

# 실행 계획이란?
SQL에서 요구한 사항을 처리하기 위한 절차와 방법
실행계획에서 표시되는 내용 및 형태는 벤더마다 약간 차이는 있지만 실행계획이 SQL 처리를 위한 절차와 방법을 의미한다는 기본적인 사항은 모두 동일하다.

실행 계획은 여러 단계로 이뤄져 있는데 (스텝) 각각 스텝에서 어떤 명령이 수행되었고 몇 건의 데이터가 처리되었으며 얼마만큼의 비용과 시간이 소요되었는지를 표시한다.

규칙 기반 최적화

우선순위가 높은 규칙이 적은 일량으로 해당 작업을 수행하는 방법이다.

규칙 기반 최적화 옵티마이저가 실행계획을 생성할 때 참조하는 정보에는 SQL문을 실행하기 위해서 이용 가능한 인덱스 유무와 (유일, 비유일, 단일, 복합 인덱스) 종류, SQL문 에서 사용하는 연산자(=, <, <>, LIKE, BETWEEN 등)의 종류 그리고 SQL문에서 참조하는 객체(힙, 테이블, 클러스터 테이블 등)의 종류 등이 있다.

규칙 기반은 인덱스를 이용한 액세스 방식이 전체 테이블 액세스 방식보다 우선순위가 높으므로 이용 가능한 인덱스가 존재하다면 항상 인덱스를 사용하는 실행계획을 생성하게된다. join 순서를 결정할 때는 join 컬럼 인덱스의 존재 유무가 중요한 판단기준이다.

규칙 기반은 정해진 규칙에 따라 만들어졌을때 BETWEEN 의 현실적인 데이터의 건수 등을 측정을 예측하지못한다.

4. 실행 엔진 (Query Executioin Engine)

다음으로 실행 엔진이다.

옵티마이저에서의 실행계획은 실행 엔진으로 전달되어 스토리지 엔진과 통신을 하여 데이터를 읽어오는 작업을 수행한다.

스토리지 엔진은 실행 엔진의 요청을 처리하기 위한 디스크로부터 데이터를 읽고 쓰는 작업을 한다. 실행 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.

MySQL엔진과 다르게 여러개를 동시에 사용할 수 있다.

5. 핸들러(Storage Engine)

핸들러는 MySql 서버의 가장 밑단에서 MySql 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당한다. 스토리지 엔진을 의미한다.

MySQL 엔진은 SQL interface, Parser, Preprocessor, Optimizer, Cache와 Buffer로 구성된다.

InnoDB 스토리지 엔진은 Transaction, Buffer pool, Clustering index, MVCC, Foreign key, Deadlock 감지 등이 있다.

MySQL 8.0 이후로부터 InnoDB 스토리지 엔진을 기본 엔진으로 사용하고 트랜잭션을 제공한다.

* InnoDB 스토리지 엔진

InnoDB 엔진(버퍼 풀, Undo log)과 디스크로 구성된다.

Set GLOBAL TRANSACTION ISOLATION
LEVEL REPEATABLE READ;

상단과 같이 트랜잭션 레벨을 repeatable read로 설정하고 insert문을 활용하여 데이터를 삽입하면 버퍼풀과 디스크에 해당 데이터가 같이 들어간 것을 확인할 수 있다. 이는 버퍼풀이 디스크의 데이터값을 캐싱했다는 것으로 확인할 수 있다.

다음으로 삽입한 데이터를 수정하고 커밋하면, 업데이트한 데이터가 버퍼풀에 반영이 되었고 이전의 데이터는 Undo Log에 추가된다.

디스크에 있는 데이터는 버퍼풀에서 쓰기 작업이 지연되고 있는 상태라면 이전의 데이터로 기록이 되고, 쓰기 작업이 일괄적으로 처리된 상태라면 업데이트된 데이터가 기록된다.

Undo Log에 있는 트랜잭션 Id가 있는데, 이는 실행한 트랜잭션의 id를 의미한다. (순차적으로 증가한다)

버퍼풀

버퍼풀은 데이터 캐싱하고 버퍼링시켜서 데이터를 읽고 쓰는 작업을 빠르게 한다.

MVCC (트랜잭션 동시성을 제어하는 방법)

• 하나의 레코드에 대해 여러 개의 버전을 관리 (트랜잭션 id)
• 잠금 없는 일관된 읽기를 제공한다.

-> MVCC, 정렬 처리방식, 복제는 다음 포스팅에서 더 다뤄보기!!

참고
- https://velog.io/@zerodin/%EC%98%B5%ED%8B%B0%EB%A7%88%EC%9D%B4%EC%A0%80%EC%99%80-%EC%8B%A4%ED%96%89%EA%B3%84%ED%9A%8D
- https://youtu.be/8PRkLItDwXQ