데이터 타입과 제약 조건

VARCHAR(n) : 최대 n글자까지 저장되는 가변 길이 문자열

-> 저장공간에서 효율이 좋겠지?

CHAR(n) : 항상 n글자 길이를차지하는고정길이문자열

  • 우편번호, 국가코드처럼 항상 길이가 정해져 있는 데이터에 사용하면 VARCHAR 보다 아주 약간의 이점을 가질 수 있다.

TEXT : 매우 긴 텍스트를 저장할 때 사용한다.

VARCHAR에서 데이터의 길이가 자주 변경되면 , 행의 크기가 변해 데이터 단편화나 페이지 분할 등이 발생해 성능 저하가 발생할 수 있지만 이는 매우 미묘한 차이이다.

단편화 설명

: 데이터 베이스는 페이지(Page)라는 블록 단위로 디스크에 저장한다. 데이터를 업데이트 시 원래 있던 자리보다 데이터의 길이가 길다고 하면 다른 페이지의 빈 공간으로 옮겨야 한다. 이때 해당 데이터에 옮겨간 자리의 포인터를 저장한다. 따라서 해당 데이터에 저장하려면 두 번의 디스크 접근이 필요한 것임.

페이지 분할 설명: B+ tree의 경우 key값으로 정렬 되어있음. 최종 데이터는 children 노드에 저장됨

보통 바이너리서치를 통해 3depth~4depth 를 타고 들어가면 데이터를 찾을 수 있음. Read가 빠른 이유

근데 Write의 성능이 No Sql 보다 안 좋은데 이유는 만약 children 노드에서 데이터를 추가하려고 하는데 자리가 없다. 이 경우 disk write 명령이 여러 번 발생할 수 있다.

먼저 노드를 하나 더 추가한다. 그리고 꽉 찬 노드의 반을 옮긴다. 그리고 해당 데이터를 찾기 위해선 어디 children 노드를 봐야 한다는 정보를 Parent 노드에 추가해줌. 그다음 이제 내가 원래 추가하려했던 데이터와 key를 노드에 추가해준다.

기본 키는 작고, 절대변하지 않고, 순차적인 것이 좋다.

순차적이지 않으면 페이지 분할을 계속해서 유발할 것임.

계속 DELETE 되면 분명히 공간 낭비 및 단편화, 성능 저하가 일어남. 이를 위해 테이블 재구성의 방법이 있고, 아예 삭제하지 않고 남겨두는 방안도 있을 것이다.

VARCHAR 선호

  1. 뛰어난 공간 효율성 : 대부분의 문자열 데이터는 가변적이다.

  2. 유연성 : 초기에 예측한 길이보다 긴 데이터가 들어올 가능성에 대비하기에 용이하다.

현대 애플리케이션에선 TIMESTAMP 안 쓰고 DATETIME 사용

DATETIME 5바이트만 쓰고 저장될 수 있는 이유

## 5바이트(40비트)의 실제 구조

MySQL은 5바이트, 즉 40비트의 공간을 아래와 같이 각 구성 요소에 필요한 만큼만 할당하여 사용합니다.

구성 요소
할당 비트 수
저장 가능 범위
설명

년(Year) & 월(Month)

17 bits

0 ~ 131071

(Year * 13) + Month 값을 저장합니다. 이 방식으로 두 값을 묶어 효율적으로 표현합니다.

일(Day)

5 bits

0 ~ 31

한 달은 최대 31일까지 있으므로, 32개 값을 표현하는 5비트(2^5=32)면 충분합니다.

시(Hour)

5 bits

0 ~ 31

시간은 0~23시까지 있으므로, 32개 값을 표현하는 5비트(2^5=32)면 충분합니다.

분(Minute)

6 bits

0 ~ 63

분은 0~59까지 있으므로, 64개 값을 표현하는 6비트(2^6=64)면 충분합니다.

초(Second)

6 bits

0 ~ 63

초는 0~59까지 있으므로, 64개 값을 표현하는 6비트(2^6=64)면 충분합니다.

총합

40 bits

(17 + 5 + 5 + 6 + 6 = 40 bits = 5 bytes)

제약 조건

제약 조건은 테이블에 데이터를 저장할 때, 특정 규칙을 지키도록 강제하는 장치. 이 규칙에 의해 잘못되거나 일관성 없는 데이터가 입력되는 것을 원천적으로 차단할 수 있다. 데이터의 '무결성' 즉 데이터에 결점이 없는 상태를 유지하는 것이 제약 조건의 핵심 목표.

  • 일관성 : 트랜잭션 실행 전후에 데이터베이스가 정의된 규칙(무결성)을 항상 만족해야 한다는 원칙

NOT NULL : 필수 입력 항목 지정

  • 이 제약 조건이 걸린 열에는 NULL 값 허용하지 않음.

UNIQUE : 중복 불가 항목 지정

PRIMARY KEY : 테이블의 대표 식별자

  • 테이블의 모든 행을 유일하게 식별하는 열이므로, NOT NULL 과 UNIQUE의 특징을 모두 가진다. 모든 테이블에는 반드시 PRIMARY KEY가 있어야 한다.

FOREIGN KEY (FK) : 테이블 간의 관계 설정

  • 한 테이블을 다른 테이블과 연결하는 관계의 고리. 참조하는 열의 값은 반드시 참조되는 테이블의 PRIMARY KEY 값 중 하나여야 한다는 '참조 무결성'을 강제한다.

  • 예를 들어 주문 테이블에 customer_id (FK) 값이 99가 입력된다고 가정해보자. customers 테이블에는 99번 고객 번호가 없다.

DEFAULT : 기본값 설정

  • 데이터를 INSERT 할 때 특정 열의 값을 명시하지 않으면, 자동으로 설정된 기본값이 입력된다.

CHECK : 컬럼에 입력되는 값이 특정 조건을 만족하는지 검사

  • 설명 : 조건에 맞지 않는 데이터의 입력을 막는다.

  1. 컬럼 정의 바로 옆에 추가

CREATE TABLE member (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    age INT DEFAULT 19,
    role VARCHAR(20) CHECK (role IN ('ADMIN', 'USER'))
);

  1. 모든 컬럼 정의 후 마지막에 추가 (Table - level)

CREATE TABLE orders {

    order_id BIGINT,
    product_id BIGINT,
    member_id BIGINT,
    order_date DATE,
    
    CONSTRAINT pk_orders PRIMARY KEY(order_id, product_id),
    CONSTRAINT fk_orders_member FOREIGN KEY (member_id) REFERENCES member(id)
);

  1. 이미 만들어진 테이블에 추가

-- member 테이블의 age 컬럼에 0 이상이어야 한다는 CHECK 제약 조건 추가
ALTER TABLE member
ADD CONSTRAINT chk_member_age CHECK (age >= 0);

PreviousMySQLNextDDL

Last updated