MySQL 디자인 패턴 완벽 가이드

MySQL 데이터베이스 설계와 쿼리 최적화를 위한 필수 디자인 패턴을 학습합니다. 실무에서 자주 사용되는 패턴들을 실제 코드 예제와 함께 제공합니다.

---

---

들어가며

이 글에서는 MySQL 디자인 패턴 완벽 가이드에 대해 상세히 알아보겠습니다. 총 12가지 주요 개념을 다루며, 각각의 개념에 대한 설명과 실제 코드 예제를 함께 제공합니다.

목차

1. 단일_테이블_상속_패턴
2. 연관_테이블_패턴
3. 인덱스_최적화_패턴
4. 소프트_삭제_패턴
5. 낙관적_잠금_패턴
6. JSON_컬럼_활용_패턴
7. 파티셔닝_패턴
8. 감사_로그_패턴
9. 계층_데이터_패턴
10. 읽기_복제본_패턴
11. 배치_삽입_패턴
12. 캐시_테이블_패턴

---

1. 단일 테이블 상속 패턴

개요

여러 타입의 엔티티를 하나의 테이블에 저장하고 type 컬럼으로 구분하는 패턴입니다. 간단한 상속 구조에 적합합니다.

코드 예제

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  type ENUM('customer', 'admin', 'guest'),
  admin_level INT,
  customer_tier VARCHAR(20)
);

설명

type 컬럼으로 사용자 유형을 구분하고, 각 타입별 전용 컬럼(admin_level, customer_tier)을 함께 저장합니다. 쿼리가 단순하지만 NULL 값이 많아질 수 있습니다.

---

2. 연관 테이블 패턴

개요

다대다 관계를 표현하기 위해 중간 테이블을 사용하는 패턴입니다. 학생-강좌, 상품-카테고리 등에 활용됩니다.

코드 예제

CREATE TABLE students_courses (
  student_id INT,
  course_id INT,
  enrolled_at DATETIME,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

설명

두 엔티티의 관계를 별도 테이블로 관리하여 다대다 관계를 구현합니다. 복합 기본키로 중복을 방지하고 추가 정보(enrolled_at)도 저장 가능합니다.

---

3. 인덱스 최적화 패턴

개요

자주 조회되는 컬럼에 인덱스를 생성하여 쿼리 성능을 향상시키는 패턴입니다. WHERE, JOIN, ORDER BY 절에 사용되는 컬럼이 대상입니다.

코드 예제

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created_at ON orders(created_at);
CREATE INDEX idx_status_date
  ON orders(status, created_at);

SELECT * FROM users WHERE email = 'test@example.com';

설명

단일 컬럼 인덱스와 복합 인덱스를 생성하여 검색 속도를 크게 향상시킵니다. 하지만 과도한 인덱스는 쓰기 성능을 저하시킬 수 있습니다.

---

4. 소프트 삭제 패턴

개요

실제 데이터를 삭제하지 않고 deleted_at 컬럼으로 삭제 여부를 표시하는 패턴입니다. 데이터 복구와 감사 추적에 유용합니다.

코드 예제

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  deleted_at DATETIME NULL
);

UPDATE products SET deleted_at = NOW() WHERE id = 1;
SELECT * FROM products WHERE deleted_at IS NULL;

설명

DELETE 대신 UPDATE로 deleted_at을 설정하여 논리적 삭제를 수행합니다. 실제 데이터는 남아있어 복구가 가능하고 이력 관리가 용이합니다.

---

5. 낙관적 잠금 패턴

개요

version 컬럼으로 동시성 충돌을 감지하는 패턴입니다. 여러 사용자가 동시에 수정할 때 데이터 무결성을 보장합니다.

코드 예제

CREATE TABLE inventory (
  id INT PRIMARY KEY,
  product_name VARCHAR(100),
  quantity INT,
  version INT DEFAULT 0
);

UPDATE inventory SET quantity = 95, version = version + 1
WHERE id = 1 AND version = 0;

설명

UPDATE 시 현재 version을 조건에 포함하여 다른 트랜잭션의 수정 여부를 확인합니다. 영향받은 행이 0이면 충돌이 발생한 것으로 판단합니다.

---

6. JSON 컬럼 활용 패턴

개요

유동적인 속성이나 메타데이터를 JSON 타입으로 저장하는 패턴입니다. 스키마 변경 없이 유연하게 데이터를 저장할 수 있습니다.

코드 예제

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  attributes JSON
);

INSERT INTO products VALUES
(1, 'Laptop', '{"color": "black", "ram": "16GB"}');
SELECT * FROM products WHERE attributes->>'$.color' = 'black';

설명

JSON 컬럼을 사용하여 제품별로 다른 속성들을 자유롭게 저장합니다. JSON 경로 표현식으로 특정 속성을 검색할 수 있습니다.

---

7. 파티셔닝 패턴

개요

대용량 테이블을 날짜나 범위 기준으로 분할하여 관리하는 패턼입니다. 쿼리 성능과 유지보수성을 향상시킵니다.

코드 예제

CREATE TABLE orders (
  id INT,
  order_date DATE,
  amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025)
);

설명

주문 데이터를 연도별로 파티션을 나누어 저장합니다. 특정 연도 조회 시 해당 파티션만 스캔하므로 성능이 크게 향상됩니다.

---

8. 감사 로그 패턴

개요

데이터 변경 이력을 별도 테이블에 기록하는 패턴입니다. 누가, 언제, 무엇을 변경했는지 추적할 수 있습니다.

코드 예제

CREATE TABLE audit_log (
  id INT PRIMARY KEY AUTO_INCREMENT,
  table_name VARCHAR(50),
  record_id INT,
  action ENUM('INSERT', 'UPDATE', 'DELETE'),
  changed_by INT,
  changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

설명

모든 데이터 변경 사항을 audit_log 테이블에 기록하여 완벽한 변경 이력을 유지합니다. 규정 준수와 보안 감사에 필수적입니다.

---

9. 계층 데이터 패턴

개요

부서, 카테고리 등 계층 구조를 표현하기 위해 parent_id를 사용하는 패턴입니다. 조직도나 메뉴 구조에 활용됩니다.

코드 예제

CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

SELECT * FROM categories WHERE parent_id = 1;

설명

자기 참조 외래키로 계층 구조를 표현합니다. parent_id가 NULL이면 최상위 항목이며, 재귀 쿼리로 전체 트리를 조회할 수 있습니다.

---

10. 읽기 복제본 패턴

개요

마스터 DB는 쓰기용, 복제본 DB는 읽기용으로 분리하는 패턴입니다. 대량의 읽기 트래픽을 처리할 때 효과적입니다.

코드 예제

-- 애플리케이션 레벨에서 구현
const writeDB = mysql.createConnection({host: 'master'});
const readDB = mysql.createConnection({host: 'replica'});

writeDB.query('INSERT INTO users VALUES (...)');
readDB.query('SELECT * FROM users WHERE id = 1');

설명

쓰기는 마스터 DB로, 읽기는 복제본 DB로 분산하여 부하를 줄입니다. 애플리케이션에서 용도에 따라 적절한 연결을 선택합니다.

---

11. 배치 삽입 패턴

개요

여러 행을 한 번에 삽입하여 성능을 향상시키는 패턴입니다. 대량 데이터 처리 시 필수적입니다.

코드 예제

INSERT INTO users (name, email) VALUES
  ('User1', 'user1@example.com'),
  ('User2', 'user2@example.com'),
  ('User3', 'user3@example.com'),
  ('User4', 'user4@example.com');

설명

단일 INSERT 문으로 여러 행을 삽입하면 네트워크 왕복과 트랜잭션 오버헤드가 크게 감소합니다. 수천 건 처리 시 수십 배 빠릅니다.

---

12. 캐시 테이블 패턴

개요

복잡한 집계 결과를 별도 테이블에 미리 계산하여 저장하는 패턴입니다. 대시보드나 리포트 성능을 크게 향상시킵니다.

코드 예제

CREATE TABLE daily_sales_summary (
  date DATE PRIMARY KEY,
  total_orders INT,
  total_revenue DECIMAL(12,2),
  updated_at DATETIME
);

INSERT INTO daily_sales_summary
SELECT DATE(created_at), COUNT(*), SUM(amount), NOW()
FROM orders WHERE DATE(created_at) = CURDATE();

설명

일일 판매 집계를 미리 계산하여 저장합니다. 복잡한 JOIN과 집계를 반복 수행하지 않고 캐시 테이블을 조회하여 빠른 응답을 제공합니다.

---

마치며

이번 글에서는 MySQL 디자인 패턴 완벽 가이드에 대해 알아보았습니다. 총 12가지 개념을 다루었으며, 각각의 사용법과 예제를 살펴보았습니다.

관련 태그

#MySQL #Database #Design Patterns #Query Optimization #Schema Design