데이터베이스 설계하기

기본키 설계

기본키

각 행을 유일하게 식별할 수 있는 속성(컬럼)을 의미한다.

특징

🧪

예시) 사용자 -> 주민등록번호(123456-1234567)

주민등록번호는 절대 중복될 수 없으며, 반드시 한 명이 한 개의 주민등록번호를 가지고 있어야한다.

ℹ️

자연키 현실 세계의 속성중에서 본래부터 고유하게 식별할 수 있는 값

ℹ️

대체키 인위적으로 생성된, 의미 없는 고유 식별자

  • 대표적으로 숫자형 자동 증가 값(1 -> 2 -> 3 …)

기본키 설계가 중요한 이유

데이터 무결성 보장

인덱싱과 빠른 검색 보장

참조 무결성 유지

효율적인 저장 공간 활용

기본키 선정 기준

추천하지 않는 기본키 설계 사례

분산 환경 기본키

UUID

전 세계에서 유일한 식별자를 생성하기 위한 표준 규격

📝

기본키 선정 기준으로 살펴보기

  • 고유성 O
  • 불변성 O
  • 간결성 ?

UUIDv4 의 단점

랜덤하게 생성되어 인덱스 단편화가 발생한다.

💡

인덱스 단편화란? 데이터베이스에서 데이터가 삽입, 수정, 삭제되면서 인덱스 페이지들이 디스크 상에 연속적이지 않고 흩어져 있는 현상

이로인해, 인덱스 스캔 시 많은 수의 비연속적인 디스크 I/O가 발생하여 쿼리 속도가 느려짐

대안

  1. UUIDv7 사용

    • UUIDv4 의 단점을 보완하기 위해 설계된 시간 기반 UUID 이며, 타임스탬프 + 난수 조합으로 충분한 고유성 확보

  2. Snowflake ID 사용

    • 트위터에서 개발한 분산 시스템용 고유 ID 생성 방식으로, 데이터 센터 ID + 서버 ID + 타임스탬프 + 시퀀스 번호를 조합하여 전 세계 어디서나 중복되지 않는 고유한 ID 생성
    • UUIDv7 보다 짧고 간결한 정수형 ID

정리하기

Q. 어떤 컬럼을 기본키로 지정할 것인가? -> 자연키 보다는 위 세가지 대체키 사용하기

정규화 & 비정규화

정규화

데이터의 중복을 제거하고, 데이터를 구조적으로 분해하여 논리적으로 정돈하는 작업

정규화가 되지 않은 경우

위 소셜 로그인 컬럼에는 한 개의 값이 있어야 하나, 여러 개의 값이 복수로 포함 되어있기 때문에 1 정규형 위반 되었다고 말한다.

1 정규형(1NF) 위반

📌

1 정규형(1NF) 위반시 문제점 데이터 검색이 어려움

  • 위 소셜 로그인 컬럼에서 “Kakao” 를 검색하려면 여러 값이 포함된 문자열을 가공해야한다.

데이터 무결성 저하

  • 데이터를 문자열로 저장할 경우 쉼표 구분자의 오타가 발생할 수 있다.

정렬/집계 기능 제한

제1정규형(1NF) 적용

1 정규형을 적용하더라도 몇 가지 문제가 존재한다.

  1. 사용자id 가 고유한 사용자를 식별할 수 없음
  2. Alice 라는 이름이 중복으로 저장 되어있음
  3. 사용자id 와 사용자 이름은 강한 연관성을 갖지만, 위 테이블에서 약한 연관성을 띄게 됨 -> 제 2 정규형 위반

제1정규형(1NF) 위반

📌

1 정규형(1NF) 위반시 문제점 데이터 중복 증가

  • 위 제 1 정규형 적용 사례의 테이블 처럼 Alice 라는 이름의 동일한 논리 사용자가 중복된다.

이상 현상 발생

  • 데이터의 삽입, 갱신, 삭제 상황에서 이상 현상이 발생한다.
    • 삽입 이상: 소셜 로그인을 하지 않은 다른 사용자를 저장할 경우, 소셜 로그인이 NULL 로 불필요한 값이 삽입 된다.
    • 갱신 이상: 데이터가 중복된 테이블에서 데이터를 수정할 경우, 중복된 데이터가 모두 같이 변경되어야 한다.
    • 삭제 이상: 원하지 않는 정보까지 삭제 되는 경우이다.

정렬/집계 기능 제한

제2정규형(2NF) 적용

연관성이 낮은 컬럼끼리 데이터를 분리 하는 방식이다.

정규화를 하는 것이 반드시 좋은 것은 아닐 수 있다

상품, 상품 옵션, 옵션 정보, 옵션 값을 저장할 수 있는 총 4개의 테이블이 존재한다고 가정하자.

이 테이블은 정규화가 적용되어있는 테이블 구조 설계 방식이기 때문에, 사용자에게 선택할 수 있는 옵션 목록을 보여주기 위해서는 아래와 같이 테이블 간 JOIN 쿼리를 통해 데이터를 조회 해야한다.

SELECT  
    p.name AS product_name,  
    o.name AS option_name,  
    ov.value AS option_value  
    FROM product p  
JOIN product_option_value pov ON p.id = pov.product_id  
JOIN option_value ov on ov.id = pov.value_id  
JOIN `option` o on o.id = ov.option_id  
ORDER BY p.id, o.id, ov.id;

하지만, 테이블에 존재하는 데이터가 많은 경우 JOIN 은 성능이 좋지 않기 때문에 이러한 경우는 중복을 감수 하더라도 비정규화를 적용하여 성능의 이점을 선택할 수 있다.

정리하기

📑

제 1 정규형은 하나의 셀은 하나의 값만 존재해야 한다. 제 2 정규형은 기본키가 2개 이상의 복합키로 구성된 테이블에서 기본키 전체가 아닌, 일부에만 연관된 데이터는 분리한다. -> 연관성이 낮은 컬럼은 분리한다. 제 3 정규형은 기본키가 아닌 일반 컬럼끼리 연관된 데이터는 분리한다.

그 외에도 보이스-코드 정규형, 제4정규형, 제5정규형 등이 더 존재한다.

다만, 이런 정규형 규칙들은 모두 데이터 중복을 줄이고 무결성을 유지하기 위한 구조적 분해 과정이다.

  • 큰 테이블을 의미 있는 작은 테이블로 쪼개어 중복과 이상현상을 줄이는 과정
  • 핵심은 하나의 큰 테이블로 관리하지 않고, 테이블을 쪼개어 관리하는 방식이다.
  • 테이블을 어디까지 쪼갤 것인지, 쪼개지 않을 것인지 결정하는게 필요하다.

비정규화

정규화된 데이터베이스에서 성능이나 편의성을 위해 일부러 중복을 허용하거나 구조를 단순화 하는 기술이다.

정규화된 테이블로 인한 성능 저하 사례 -> 비정규화가 효과적인 경우

다수의 테이블 JOIN 이 필요한 경우

실시간 집계가 필요한 경우

언제 정규화하고, 언제 비정규화할까?

  1. 처음에는 데이터 무결성과 일관성을 위해 정규화를 적용한다.
    • 안정적인 기본 설계를 만드는 것이 우선되기 때문
  2. 성능 이슈나 특정 요구사항이 발생하면, 비정규화 적용
    • 쿼리 성능 최적화나 편의를 위해 타협하는 단계

외래키

한 테이블이 다른 테이블의 기본키를 참조하는 키

만약, 사용자 아이디 같이 고유한 컬럼이 아닌 이름과 같은 중복 가능성이 있는 컬럼을 외래키로 참조한다면 어떻게 돌까?

고유한 아이디를 참조하는 경우 사용자 구분이 쉽게 가능하지만, 만약 위 예시의 테이블 정보에서 동명이인인 “Alice” 가 한 명 더 등록 되었지만, 3번 사용자 ID 를 갖고 있기 때문에 다른 사용자라는 것은 우리는 알고있다.

하지만, 외래키를 참조하고 있는 대상 테이블에서는 Alice 라는 이름으로 참조할 경우 어떤 1번 Alice 인지, 새로 등록된 Alice 인지 알 수 없다. 이로인해 참조 무결성이 깨졌다 라고 표현한다.

ℹ️

외래키 제약 외래키를 지정할 때 함께 설정할 수 있는, 참조 무결성을 보장하기 위한 규칙

외래키 제약을 설정하지 않는 경우

실무에서 외래키 제약을 설정하지 않고, 외래키 관계를 사용하는 경우가 있다.

일부 회사에서는 외래키 제약 미사용을 컨벤션으로 권장 하는 곳도 있는데, 성능과 유연성 확보가 목적이라면, 외래키 제약을 없애기 보다 NoSQL 을 고려하는 것이 더 나은 선택일 수 있다.

인덱스

데이터베이스에서 원하는 데이터를 더 빠르게 찾기 위해 사용하는 자료구조

B+트리

균형 잡힌 트리 구조로, 데이터 검색/삽입/삭제가 빠른 특징이 있다. B+트리에서의 “B” 는 Balanced 의 약자이고, + 는 개량되었다 라는 의미를 담고 있는 자료구조이다.

ℹ️

특징

  • 데이터가 정렬된 상태로 저장
  • 검색, 삽입, 삭제 연산이 모두 O(log n) 시간에 가능

만약, 인덱스 테이블 없이 데이터를 조회한다면 O(n) 시간 복잡도를 나타낸다.

B+트리 구조

트리 구조를 사용하기 때문에 각 개별 요소가 노드로 되어있고, 모든 연산은 루트 노드부터 시작한다.

내부 노드는 데이터를 직접적으로 저장하고 있지 않고, 검색용 키 값만 가지고 있으며 이정표 역할을 하여 값이 저장되어 있는 노드로 안내한다.

리프 노드는 실제 데이터를 포함하고 있고, 탐색 과정에 반드시 리프 노드까지 탐색을 마쳐야만 데이터를 얻을 수 있다.

리프 노드의 특징 중 하나는 서로 포인터로 연결되어 있다.

B+트리 탐색 과정

찾고자 하는 값인 ‘42’ 가 루트 노드보다 크기 때문에 우측 노드로 탐색을 시작한다.

내부 노드에서 ‘42’ 라는 숫자가 38 과 49의 사이에 있기 때문에 가운데에 위치한 리프 노드로 탐색을 이어간다.

마지막 리프 노드에 도달하여, 값의 탐색을 이어가는데 38 보다 크기 때문에 다음 노드를 탐색하면 되고 이 때 ‘42’ 값을 탐색하게 된다.

B+트리 존재하지 않는 값을 탐색 하려면?

존재하지 않는 값인 ‘15’ 를 탐색하는 과정이다.

탐색 과정은 위에서 살펴보았듯 루트 노드의 값을 기준으로 탐색 해야 할 내부 노드를 선택하는데, 이번에는 28 보다 작으니 좌측 노드로 탐색을 시작한다.

내부 노드에 도착 한 뒤 리프 노드로 향하는 키를 조회하고, 가운데 리프 노드로 탐색을 이어간다.

리프 노드에 도달하여, 9를 먼저 탐색하게 되고 다음 13, 포인터가 연결 되어있기 때문에 다음은 17을 탐색하는데 찾고자 하는 값 보다 크기 때문에 값이 존재하지 않는다는 것을 알 수 있다.

B+트리의 순차 포인터의 활용

범위 조회(range query) WHERE age BETWEEN 30 AND 50 쿼리 실행

정렬된 결과 조회 ORDER BY age

ORDER BY age

B+트리 인덱스 구조

리프 노드에 원본 데이터를 조회할 수 있는 특정 값이 저장 되어 있고, 이와 기본키가 연결되어 있어 사용자 테이블에서 기본키를 통해 값을 조회할 수 있게 된다.

B-트리와 B+트리의 차이점

결정적 차이점은 순차 포인터의 유무이다.

B-트리는 데이터를 탐색하며 리프노드에 도달 했을 때, 찾고자 하는 값이 없다면 사용자 테이블에 값이 없다고 판단한다. 또한, 범위 조회 시 리프 노드에 도달하여 값을 찾지 못했다면 부모 노드로 순회하여 다시 리프노드 까지 탐색을 반복해야한다.

B+트리는 B-트리의 개선된 버전으로, 리프 노드가 다시 부모 노드로 순회하는 경우 없이 순차 포인터를 활용하여 다른 노드의 값들을 탐색할 수 있다.

인덱스 탐색

기본키를 지정하면 데이터베이스는 자동으로 인덱스를 생성된다. 이러한 기본키 외에 사용자가 직접 지정하는 보조 인덱스(CREATE INDEX) 생성이 가능하다.

위 예시는 age 컬럼을 보조 인덱스로 사용하면, 트리 구조에서 탐색을 하게 되고 최종적으로 도달한 노드에서 기본키를 통해 데이터를 가져올 수 있다. 이 과정이 O(log n) 만큼의 시간이 소요된다.

만약, 인덱스가 없었다면?

사용자 테이블에 존재하는 모든 레코드를 확인하고 난 뒤 데이터를 반환 해야한다. 이를 Full Scan 이라고 부르며 일반적으로 검색 성능이 좋지 않다.

인덱스 선택

쿼리 실행 과정

  1. 사용자가 쿼리 실행을 요청
  2. 옵티마이저가 실행 계획 후보를 만들고 평가
  3. 옵티마이저가 가장 효율적인 실행 계획을 선택
  4. 선택된 실행 계획에 따라 실행 엔진이 실제로 데이터를 조회
  5. 결과를 사용자에게 반환
ℹ️

실행 계획이란? 데이터베이스는 쿼리를 실행하기 전에, 쿼리 옵티마이저가 최적의 데이터 읽기 방식을 먼저 선택하여 실행 계획을 결정한다.

목표: 가장 효율적인 방법으로 데이터를 조회하기

쿼리 옵티마이저가 실행 계획을 결정하는 방법

  1. 매우 다양한 요소를 고려해 비용(cost) 이라는 추정치를 계산한다

    • 테이블 및 인덱스 통계, 인덱스 유무 및 종류, 조인 방식 등

  2. 그 중에서 비용이 가장 낮은 계획을 선택한다.

쿼리 옵티마이저의 비용 요소

  1. 인덱스 선택
    • 어떤 인덱스를 사용할 것인가?
  2. 스캔 방식 선택
    • 인덱스 스캔, 풀 스캔
    • 풀 스캔이 더 효율적인 경우도 존재한다. -> 데이터가 매우 적거나, 인덱스가 존재하지만 효율적인 인덱스가 없는 경우

인덱스가 사용되는 쿼리 종류

데이터 필터링 (WHERE) WHERE 절에 자주 사용되는 컬럼을 인덱스로 만들면 효율적이다.

정렬 (ORDER BY) B+ 트리 인덱스 구조는 정렬 되어 있기 때문에, 정렬이 필요한 경우 인덱스를 만들면 효율적이다.

테이블 조인 (JOIN) 조인 조건에 맞는 대상 행을 빠르게 찾아낼 때 인덱스가 활용되기 때문에 인덱스를 만들면 효율적이다.

그룹화 (GROUP BY)

보조 인덱스를 생성할 때 어떤 컬럼을 선택해야 할까?

자주 사용되는 쿼리 패턴을 분석하고, 그에 맞는 컬럼을 선택하는 것이 인덱스 효율이 좋다. 그 중에서 선택성이 높은(선택도가 낮은) 컬럼 위주로 인덱스 생성하는 것이 좋다.

ℹ️

선택도란? 특정 값이 얼마나 고유한지를 나타내는 지표

  • 선택도 계산 하는 공식: 특정 값에 해당하는 행 수 / 전체 행 수
  • 예시: 전체 10,000개 행이 있고, 그 중 Gender = ‘M’ 인 행이 5,000개라면 선택도는 5,000 / 10.000 = 0.5 가 된다.
  • 선택도 값은 전체 레코드의 갯수에 비례하여 중복이 많은지를 볼 수 있다.

선택도 값이 낮으면, 조건에 맞는 행이 적다 -> 인덱스 효율이 증가한다. 선택도 값이 높으면, 조건에 맞는 행이 많다 -> 인덱스 효율이 떨어진다.

인덱스는 항상 좋을까?

인덱스는 읽기 성능을 높이지만, 과도한 인덱스는 쓰기 성능과 별도의 인덱스 테이블로 인한 저장 공간을 낭비할 수 있다.

유니크 제약과 인덱스

테이블 컬럼에 중복된 값이 들어가는 것을 방지하는 제약 조건인 유니크 제약은 중복여부를 빠르게 조회 하기 위해 인덱스가 자동으로 생성된다.

CREATE TABLE user (
	id INT PRIMARY KEY,
	email VARCHAR(100) UNIQUE
)

이러한 테이블 구성이 있다면, 별도로 CREATE_INDEX(email) 이 불필요하다.

복합 유니크 제약

여러 컬럼을 묶어서, 그 조합이 테이블 내에서 유일하도록 강제하는 제약이다. 이는 유니크 제약에서 인덱스를 자동으로 생성 했던 방식과 동일하게, 이 때도 마찬가지로 복합 인덱스가 자동으로 생성된다.

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    classroom VARCHAR(10),
    seat_number INT,
    name VARCHAR(100),
    UNIQUE(classroom, seat_number)
);

이 테이블 구성에서 A교실, 5번 좌석 이라는 데이터가 저장되어 고유함을 검사한다.

복합 인덱스

두 개 이상의 컬럼을 조합하여 만든 인덱스이며, 실무에서는 단일 컬럼의 인덱스 보다 두 개 이상의 컬럼을 조합 하여 만든 인덱스가 더 자주 쓰인다.

📝

특징

  • 인덱스 키가 여러 컬럼의 값을 순서대로 포함한다.
  • 첫 번째 컬럼 값 기준으로 정렬하고, 같은 값일 때 두 번째 커럼 기준으로 정렬한다.

다중 컬럼 인덱스 설계

다중 컬럼 인덱스 설계시, 컬럼 순서가 매우 중요하다. 아래 처럼 두 개의 인덱스는 순서가 변함에 따라 인덱스의 효율이 차이가 난다.

WHERE 절의 시작 컬럼을 먼저 조회한다.

그 다음 컬럼에 사용되는 데이터 필터링 작업을 거쳐 리프 노드에서 탐색이 완료된 후 종료한다.

이렇게 구성된 복합 인덱스 구조에서 name 이 먼저 저장되어 있을 때, age 를 조회하게 되면 Alice가 30살인 데이터를 찾기 위한 리프 노드 전체 탐색과 Chris 가 30살인 데이터를 찾는 리프 노드 전체 탐색 과정이 포함 되기 때문에, Full Scan 과 다를게 없다.

복합 인덱스 최적화 전략

선두 컬럼 법칙

즉, 복합 인덱스를 설계할 때 쿼리 실행 계획을 분석했을 때 가장 많이 사용되는 컬럼을 선두 컬럼으로 설계 하는것이 효율적이다.

컬럼 순서와 WHERE, GROUP BY, ORDER BY 조건이 일치

범위 조건 뒤 컬럼 활용 제한

📑

복합 인덱스 설계시, 컬럼 순서가 매우 중요하다.

  • 선행 컬럼이 인덱스 활용을 결정
    • 쿼리 조건이 반드시 선두 컬럼부터 순서대로 포함 되어야한다.
  • 선행 컬럼 기반 인덱스 설계
    • (a, b) 처럼 앞 컬럼이 포함된 인덱스느 a만 쓰는 쿼리도 커버할 수 있어서, 하나의 인덱스로 여러 쿼리를 포괄할 수 있다.

커버링 인덱스

쿼리가 필요한 모든 컬럼이 인덱스에 포함되어 있어, 테이블 데이터를 따로 읽지 않고도 원하는 결과를 반환할 수 있는 인덱스이다.

커버링 인덱스를 활용하려면 SELECT 절에 필요한 컬럼만 최소한으로 명시 해야한다.

다양한 관계 표현

모든 테이블 간의 관계는 세 가지 기본 유형 중 하나로 표현한다.

일대일 관계

테이블 간의 각 레코드가 정확히 하나씩 대응대는 관계

📌

참고 테이블 설계 시 운영 목적과 환경을 고려하여, 정규화 여부를 고려할 수 있다. 무조건 위 예시 처럼 정규화를 적용하여 테이블을 분리할 필요는 없기 때문에 한 테이블에 모든 정보를 포함할 수도 있다.

구현 방법

두 테이블 중 어느 테이블에 외래키를 추가하는 것이 좋을까?

테이블간의 의존성을 고려하여 외래키를 추가해야 하는데, 사용자와 프로필 관계의 예시를 살펴보자.

사용자, 상세 프로필 관계는 “사용자가 있어야 프로필이 존재” 할 수 있기 때문에 프로필 테이블에 사용자 외래키가 포함되는 것이 올바르다.

예시

CREATE TABLE user (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(255) NOT NULL
);

CREATE TABLE profile (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED UNIQUE,
  image VARCHAR(255),
  bio TEXT,
  FOREIGN KEY (user_id) REFERENCES User(id)
);

일대다 관계

한 테이블의 레코드가 다른 테이블의 여러 레코드와 대응되고, 반대편은 하나씩만 대응대는 관계이다.

구현 방법

가장 흔한 유형으로, 다수(N) 쪽 테이블에 1쪽 테이블의 기본키를 외래키로 지정한다.

CREATE TABLE user (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(255) NOT NULL
);

CREATE TABLE Post (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES User(id)
);

다대다 관계

양쪽 테이블의 레코드가 서로 여러 개씩 대응되는 관계이다.

구현 방법

하나의 외래키를 통해 다대다 관계를 구현할 수 없기 때문에 중간 테이블을 통해 1:N + N:1 관계로 구현한다.

❓

왜 하나의 외래키로 구현할 수 없을까? 외래키는 단일 값만 저장 가능한데, 다대다는 여러 값을 참조해야 하기 때문이다.

양쪽 테이블이 서로 외래키로 참조하게 되는 경우, 순환 참조가 발생한다.

  • 이는 단순히 서로 참조하는 것일 뿐 다대다 관계라고 볼 수 없다.
CREATE TABLE Student (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE Subject (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE Enrollment (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id INT UNSIGNED NOT NULL,
  subject_id INT UNSIGNED NOT NULL,  
  UNIQUE (student_id, subject_id),
  FOREIGN KEY (student_id) REFERENCES Student(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES Subject(id) ON DELETE CASCADE
);

자기 참조 관계

한 엔티티가 자기 자신과 관계를 맺는 구조를 의미한다.

구현 방법

외래키를 추가할 때, 외래키 참조 대상이 자기 자신으로 설정한다.

CREATE TABLE Employee (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  manager_id INT UNSIGNED,
  FOREIGN KEY (manager_id) REFERENCES Employee(id)
);
SELECT
  e.id AS employee_id, -- 직원 PK
  e.name AS employee_name,  -- 직원 이름
  m.name AS manager_name -- 관리자 PK
FROM
  Employee e
LEFT JOIN
  Employee m ON e.manager_id = m.id;

이 때, 조회 시 자기 자신과 JOIN 하는 SEL-JOIN 을 이용하고 위계 관계의 최상위 데이터도 출력을 하기 위해 LEFT JOIN 을 사용하여 NULL 로 표기한다.

주의 사항

순환 참조 발생 트리 구조를 탐색할 때, 부모 -> 자식 -> 자식 과정에서 2번과 3번이 서로를 계속 조회하기 때문에 순환 참조가 발생한다.

참조 깊이가 깊은 경우

해결 방안

테이블 설계시 깊이를 의미하는 컬럼과 제약을 추가한다. 

CREATE TABLE Employee (
  …
  CONSTRAINT depth CHECK (depth < 3)
);

순환 참조의 경우 자신의 깊이보다 작거나 같은 깊이를 가진 레코드는 참조를 불가능하게 막는다.

SELECT  
    e.id AS employee_id,  
    e.name AS employee_name,  
    e.depth AS employee_depth,  
    m.name AS manager_name,  
    m.depth AS manager_depth  
FROM  
    employee e  
        LEFT JOIN  
    employee m ON e.manager_id = m.id  
WHERE  
    m.depth IS NULL OR e.depth > m.depth;
📑

정리하기 자기 참조 관계는 자기 자신과 관계를 맺는 구조이다.

  • 같은 유형의 객체들 간에 위계나 연결 관계가 필요할 때 사용
  • 자기 자신에 대한 외래키 참조를 통해 구현
  • 깊이 컬럼과 제약을 통해 순환 참조와 무한 루프 방지

관계 엔티티 설계

단순한 M:N 관계를 넘어서, 현실 세계의 복잡한 비즈니스 규칙과 데이터를 더 정확하게 표현할 때 사용한다.

M:N 테이블 설계 방식에서 중간 테이블의 역할을 하던 “수강” 테이블이 다른 테이블과 관계를 맺게 되면서 더이상 중간 테이블의 역할이 아닌, 독립적인 의미를 갖는 관계 엔티티로 승격되는 것을 의미한다.

기본 관계 모델(1:1, 1:N, M:N) 과 관계 엔티티 만으로도 대부분의 현실 세계 관계를 구조적으로 표현이 가능하다.

대표적인 예시

사용자가 상품을 주문하는 비즈니스 로직에 대응하는 데이터베이스 구조를 설계 한다고 가정해보자. 아래와 같은 테이블 구조가 생성이 된다.

이런 중개 테이블에 상품의 본래 가격과 주문 당시의 가격이 할인이나 쿠폰과 같은 정책에 의해 다를 수 있기 때문에 새로운 컬럼인 “가격” 을 추가한다.

가격이 추가 되면서, 주문과 상품의 관계를 이어주는 중개 테이블의 역할에서 주문의 상세 정보를 볼 수 있는 “주문 상세” 테이블로 독립적인 관계 엔티티로 승격 되었다.

슈퍼타입/서브타입 관계

하나의 상위 개념(슈퍼 타입)과 그를 확장하는 하위 개념들(서브 타입) 사이의 관계를 모델링 하는 설계 방식이다.

슈퍼타입: 공통 속성과 식별자를 가진 상위 엔티티 서브타입: 슈퍼타입을 상속받아 특정한 고유 속성을 가지는 하위 엔티티

이러한 관계는 직원과 관리자 같은 직원도 데이터베이스를 구성할 때 활용할 수 있다.

예시

CREATE TABLE employee (  
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  
    name       VARCHAR(50) NOT NULL,  
    department VARCHAR(100)  
);  
  
CREATE TABLE manager (  
    employee_id BIGINT UNSIGNED PRIMARY KEY,  
    position    VARCHAR(50) NOT NULL,  
    FOREIGN KEY (employee_id) REFERENCES employee (id)  
);

예시를 살펴보면, 슈퍼타입인 employee 테이블로 등록하고 manager 테이블을 하위 타입 테이블로 등록하였다.

하위 타입 테이블의 설계 방식을 보면, 상위 타입의 PK 값을 외래키로 참조하면서 동시에 기본키로 사용한다.

다양한 설계 전략

블랙리스트 기반 재가입 차단

📌

문제 상황

  • 운영정책을 위반한 사용자를 차단한 뒤 탈퇴 처리 하였다.
  • 회원 탈퇴 시, 해당 사용자의 데이터는 “즉시 삭제”된다.
  • 차단한 사용자가 “재가입”을 시도한다.

목표

차단된 사용자의 데이터를 저장하지 않으면서도, 그 사용자가 재가입을 시도할 경우 이를 식별하고 차단해야한다.

개인정보 삭제 정책을 준수하기 위해, 탈퇴한 사용자의 개인정보를 삭제 해야 한다. 악성 사용자 차단을 위해, 해당 사용자가 악성 사용자 라는 것을 저장 해야 하지만 개인정보가 아닌 형태인 데이터로 보관해야한다.

✅ 방법

해시 함수를 이용해 개인정보를 암호환 값을 저장하고, 재가입 시 입력값을 같은 방식으로 해시 처리 하여 비교한 후 탈퇴한 악성 사용자의 재가입을 차단한다.

ℹ️

해싱이란?

  • 임의의 길이를 가진 데이터를 고정된 길이의 값으로 변환하는 알고리즘이다.
  • 입력값이 조금만 달라져도 완전히 다른 해시값이 생성된다.
  • 해시값으로부터 원본 데이터를 복원하는 것은 불가능하다.

상태 관리 전략

복잡한 데이터 상태를 효과적으로 관리하고, 상태 변경 시 데이터의 일관성과 무결성을 유지하는 방법이다.

Enum Over Boolean

불리언 대신 열거형을 사용하는 것이 낫다

✅ 복잡한 상태를 안정적으로 관리하려면 열거형 사용이 권장된다.

💡

주의사항

  • 유효하지 않은 상태 값이 저장되지 않도록 검증 필요
  • 상태 전이 제한
    • A -> B 상태 전이가 허용되는 흐름인지 검증하는 로직 필요
    • 완료 -> 취소가 내부적으로 불가능한 규약이라면, 전이의 흐름이 발생할 때 검증이 필요하다.
    • 상태 전이 다이어그램 작성 또는 문서화를 통해 명확하게 관리할 것은 권장

열거형 기반 상태 관리 시 동시성 문제

여러 작업에서 같은 데이터에 동시에 접근하거나 변경할 때 발생하는 문제이다.

💡

조건부 업데이트 동시성 제어를 위해 사용되는 원자적 연산 기법

  • 원자적 연산: 한 번에 완전히 실행되는 연산
  • ✅ 현재 값이 예상한 값과 같으면 새로운 값으로 바꾸고, 그렇지 않으면 변경하지 않는 방식

예시

UPDATE task SET status = 'in_progress' 
WHERE id = 1 AND status = 'todo';

멀티 테넌시 설계 전략

여러 고객의 데이터를 효과적으로 처리하고, 격리할 수 있도록 데이터베이스를 설계하는 방식이다.

공유 테이블 기반 멀티 테넌시 전략

모든 테넌트의 데이터를 하나의 테이블에 저장하고, tenant id 컬럼을 통해 각 테넌트의 데이터를 구분하는 방법이다.

⚠️

단점

  • 데이터 격리 어려움 및 보안 위험
    • 모든 테넌트 데이터가 한 테이블에 저장되기 때문에, 쿼리에서 tenant_id 조건을 빠뜨리면 다른 테넌트 데이터 노출 위험
  • 성능 저하 가능성
    • 테이블이 매우 커질 수 있고, 다수 테넌트 데이터가 함께 저장되어 조회 성능이 떨어질 수 있음
    • 이로인해 tenant_id 를 갖는 컬럼의 인덱스가 반드시 필요하다.

Table-per-Tenant 전략

테넌트별로 별도의 테이블을 생성하여 데이터를 분리하는 방법이다.

Schema-per-Tenant 전략

한 데이터베이스 내에 테넌트별로 독립된 스키마를 분리하는 방식이다.

멀티 테넌시 설계 전략 선택 가이드

이력 관리 전략(Slowly Changing Dimension)

시간이 지나면서 테이블에 저장된 데이터 값이 변경될 때, 변경 내용을 관리하는 방법

Type 1

데이터의 가장 마지막 상태만 기록하는 간단한 형태의 이력 관리 전략으로, 데이터가 언제 어떻게 바뀌었는지에 대한 정보가 없다.

Type 2

새로운 레코드를 추가하여 변경 이력을 보존하는 방식이다.

Type 4

기존 할 일 테이블은 현재 상태만을 관리하고, 할일 이력 테이블은 변경 내력을 관리하는 별도의 테이블을 구성한다.

SCD 유형 선택

Type 1(덮어쓰기)

Type 2(행 이력 관리)

Type4(히스토리 테이블)

동시성 제어 전략

많은 트래픽을 처리하는 분산 환경에서 데이터 무결성과 일관성을 보장하기 위해 사용하는 방법

ℹ️

동시성 제어 전략의 핵심은 경쟁 상태로 발생하는 데이터 불일치 문제를 해결하는 전략이다.

비관적 락(Pessimistic Lock)

“동시에 접근하면 충돌이 일어날 것”이라고 비관적으로 가정하는 락 기법으로, 데이터를 읽을 때 부터 잠금(lock)을 걸어 다른 트랜잭션이 건드리지 못하게 하는 방식이이다.

우선 접근한 자원이 배타적 독점권을 확보하여, 데이터에 대한 읽기와 쓰기 권한을 점유하게 된다. 결론적으로, 사용자 A 가 락을 획득했다면 사용자 B 는 대기해야한다.

낙관적 락(Optimistic Lock)

“동시에 접근해도 충돌은 없을 것” 이라고 낙관적으로 가정하는 락 기법으로, 읽을 때는 잠금을 걸지 않고 쓰기 시점에 충돌 여부를 확인한다.

📝

낙관적 락의 버전 컬럼 데이터가 어떤 시점의 상태인지를 나타내는 표시이다.

  • 일반적으로 0 부터 시작해서 1 씩 값이 증가하는 방식이다.

낙관적 락을 사용할 때 조건부 업데이트를 활용하게 되는데, 이 때 사용되는 컬럼은 버전이다. 특정 버전의 데이터를 읽고, 먼저 처리하는 트랜잭션에서는 버전을 + 1 하기 때문에 다른 트랜잭션이 이를 같은 값을 읽었더라도 버전 정보가 달라졌기 때문에 업데이트가 하나만 처리되게 하는 방식이다.

동시성 제어 전략 락 기법 비교

비관적 락이 적합한 경우

데이터에 대한 경쟁이 매우 심해서, 이를 엄격하게 관리 해야 하는 환경이나 데이터 갱신이 매우 잦은 환경에서 엄격한 관리가 필요한 환경

낙관적 락이 적합한 경우

읽기가 많고 쓰기가 드문 환경에서 사용하기 좋은 동시성 제어 전략이다.

테이블 모델링 방법

테이블 설계

  1. 엔티티/속성 정의
  2. 관계 정의
  3. 테이블 유형 결정
  4. 기본키, 외래키, 제약 설정
  5. 인덱스 설계

엔티티/속성 정의

📝

무엇을 다뤄야 하는가?

  • 현실의 개념(상품, 주문, 사용자 등)을 하나의 엔터티로 식별하여 데이터베이스의 테이블로 식별하는 단계이다.
  • 각 엔터티가 가진 속성(컬럼)을 나열한면서 요건을 구체화한다.
    • 예시) User(email, username, password, created_at)

관계 정의

📝

각 엔터티가 어떻게 연결되는가?

  • 각 엔터티 간 연결 관계를 파악하고, 적절한 관계 유형 결정
    • 1:1, 1:N, N:M, 자깇마조, 관계 엔터티 등
    • 예시) User <- 1:N -> Order

테이블 유형 결정

📝

데이터를 덮어쓸 것인가, 과거까지 저장할 것인가?

  • 최종 상태 테이블인지, 이력 관리가 필요한 테이블인지 구분한다.
  • 이력 관리 요구 조건에 맞게 SCD Type 1/2/4 등 상황에 맞는 유형 결정
    • 예시) Task.status: 변경 이력 필요 -> Type2/4

기본키, 외래키, 제약 설정

📝

정합성과 성능을 어떻게 보장할 것인가?

  • 기본키(PK): 대체키 유형(ID, UUID, Snowflake ID 등) 결정
  • 외래키(FK): 관계 연결 및 삭제 옵션(ON DELETE) 결정
  • 제약: UNIQUE, NOT NULL, DEFAULT, CHECK 등 결정
    • 예시) Order - id(PK) / user_id(FK) / price(CHECK >= 0)

인덱스 설계

📝

조회/검색 성능을 어떻게 최적화 할 것인가?

  • 자주 쓰는 조회 쿼리 중심으로 설계
  • 자주 조건에 쓰이는 컬럼을 앞쪽에 배치(선행 컬럼 선정)
  • 읽기와 쓰기 트레이드오프 항상 고려