실전 프로젝트로 완성하는 MySQL 학습

작은 커뮤니티 서비스를 만들어 봅니다

지금까지는 MySQL의 개념을 하나씩 나누어 배웠습니다. 테이블을 만들고, 데이터를 조회하고, 조인하고, 인덱스를 붙이고, 트랜잭션과 권한, 백업까지 살펴보았습니다.

마지막 장에서는 이 내용을 하나의 작은 프로젝트로 묶어 보겠습니다. 목표는 거창한 서비스를 완성하는 것이 아닙니다. 회원, 게시글, 댓글, 좋아요 기능을 가진 커뮤니티 데이터베이스를 직접 설계하고 구현하는 것입니다.

실제 프로젝트를 해 보면 다음 질문에 답해야 합니다.

• 어떤 데이터를 별도 테이블로 나눌 것인가?

• 기본키와 외래키는 어떻게 연결할 것인가?

• 자주 조회하는 화면에는 어떤 인덱스가 필요한가?

• 삭제는 실제 삭제로 처리할 것인가, 상태값으로 숨길 것인가?

• 운영 중 문제가 생기면 어떻게 백업하고 확인할 것인가?

이 질문에 답하는 과정이 MySQL 실력을 만듭니다.

요구사항 정리하기

먼저 기능을 문장으로 정리합니다. 테이블 설계는 SQL 문법에서 시작하지 않습니다. 서비스가 다루는 규칙에서 시작합니다.

이번 프로젝트의 요구사항은 다음과 같습니다.

1. 회원은 이메일, 닉네임, 비밀번호 해시값을 가집니다.

2. 회원은 게시글을 작성할 수 있습니다.

3. 게시글은 제목, 본문, 조회수, 상태값을 가집니다.

4. 회원은 게시글에 댓글을 작성할 수 있습니다.

5. 회원은 게시글에 좋아요를 한 번만 누를 수 있습니다.

6. 게시글 목록에서는 작성자, 제목, 작성일, 댓글 수, 좋아요 수를 함께 보여 줍니다.

7. 게시글 상세 화면에서는 게시글 본문과 댓글 목록을 보여 줍니다.

8. 운영을 위해 백업, 권한 분리, 느린 쿼리 확인이 가능해야 합니다.

요구사항에서 중요한 명사가 보입니다.

• 회원

• 게시글

• 댓글

• 좋아요

이 네 가지가 이번 프로젝트의 핵심 테이블 후보입니다.

관계를 먼저 그리기

ERD를 그림 도구로 그려도 좋지만, 처음에는 글로 표현해도 충분합니다.

members 1 ─── N posts
members 1 ─── N comments
posts   1 ─── N comments
members N ─── N posts  ← post_likes 테이블로 연결

관계는 다음처럼 읽으면 됩니다.

• 회원 한 명은 여러 게시글을 작성할 수 있습니다.

• 게시글 하나는 한 명의 회원이 작성합니다.

• 회원 한 명은 여러 댓글을 작성할 수 있습니다.

• 게시글 하나에는 여러 댓글이 달릴 수 있습니다.

• 회원은 여러 게시글에 좋아요를 누를 수 있습니다.

• 게시글 하나는 여러 회원에게 좋아요를 받을 수 있습니다.

좋아요는 회원과 게시글 사이의 다대다 관계입니다. 다대다 관계는 직접 연결하지 않고 중간 테이블을 둡니다. 여기서는 post_likes가 그 역할을 합니다.

데이터베이스 만들기

실습용 데이터베이스를 새로 만듭니다.

CREATE DATABASE community_app
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

USE community_app;

utf8mb4는 이모지와 다양한 문자를 저장할 수 있는 문자셋입니다. 실무에서는 특별한 이유가 없다면 utf8mb4를 기본으로 선택하는 편이 안전합니다.

회원 테이블 만들기

회원 테이블은 로그인과 작성자 표시의 기준이 됩니다.

CREATE TABLE members (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    nickname VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_members_email (email),
    UNIQUE KEY uk_members_nickname (nickname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

여기서 핵심은 세 가지입니다.

첫째, id는 내부 식별자입니다. 이메일이나 닉네임은 바뀔 수 있으므로 기본키로 쓰지 않습니다.

둘째, email과 nickname에는 유니크 제약을 겁니다. 같은 이메일이나 닉네임으로 중복 가입되는 일을 데이터베이스 단계에서 막습니다.

셋째, status를 둡니다. 회원을 바로 삭제하면 그 회원이 작성한 게시글과 댓글 처리도 함께 고민해야 합니다. 실무에서는 삭제 대신 ACTIVE, LOCKED, DELETED 같은 상태값으로 관리하는 경우가 많습니다.

게시글 테이블 만들기

게시글은 회원이 작성합니다. 따라서 member_id가 필요합니다.

CREATE TABLE posts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    member_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    view_count INT UNSIGNED NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'PUBLISHED',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_posts_member_created (member_id, created_at, id),
    KEY idx_posts_status_created_id (status, created_at, id),
    CONSTRAINT fk_posts_member
        FOREIGN KEY (member_id) REFERENCES members (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

member_id는 members.id를 참조합니다. 이 외래키 덕분에 존재하지 않는 회원이 게시글을 작성하는 데이터를 막을 수 있습니다.

status는 게시글 상태를 나타냅니다.

• PUBLISHED: 공개

• HIDDEN: 숨김

• DELETED: 삭제 처리

게시글도 실제 삭제보다 상태값 변경이 유리한 경우가 많습니다. 운영 중에는 사용자가 삭제한 글을 일정 기간 보관해야 하거나, 신고 처리 이력을 남겨야 할 수 있기 때문입니다.

댓글 테이블 만들기

댓글은 게시글과 회원을 모두 참조합니다.

CREATE TABLE comments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    post_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NOT NULL,
    content VARCHAR(1000) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PUBLISHED',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_comments_post_created (post_id, created_at, id),
    KEY idx_comments_member_created (member_id, created_at, id),
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id) REFERENCES posts (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_comments_member
        FOREIGN KEY (member_id) REFERENCES members (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ON DELETE CASCADE는 부모 데이터가 삭제될 때 자식 데이터도 함께 삭제하는 옵션입니다. 여기서는 게시글이 실제로 삭제되면 댓글도 함께 삭제되도록 했습니다.

다만 실무에서는 게시글을 실제 삭제하지 않고 status = 'DELETED'로 바꾸는 경우가 많습니다. 그러면 댓글도 함께 숨길지, 댓글은 남길지 정책을 정해야 합니다. 데이터베이스 설계는 기술만의 문제가 아니라 서비스 정책과 연결됩니다.

좋아요 테이블 만들기

좋아요는 회원과 게시글을 연결합니다. 한 회원이 같은 게시글에 좋아요를 여러 번 누르면 안 됩니다.

CREATE TABLE post_likes (
    post_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, member_id),
    KEY idx_post_likes_member_created (member_id, created_at),
    CONSTRAINT fk_post_likes_post
        FOREIGN KEY (post_id) REFERENCES posts (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_post_likes_member
        FOREIGN KEY (member_id) REFERENCES members (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

이 테이블의 기본키는 (post_id, member_id)입니다. 이 조합이 기본키이므로 같은 회원이 같은 게시글에 좋아요를 중복으로 누를 수 없습니다.

좋아요를 누를 때는 다음처럼 입력합니다.

INSERT INTO post_likes (post_id, member_id)
VALUES (1, 2);

이미 좋아요가 있다면 중복 키 오류가 발생합니다. 오류를 피하면서 한 번만 반영하고 싶다면 다음처럼 쓸 수 있습니다.

INSERT IGNORE INTO post_likes (post_id, member_id)
VALUES (1, 2);

INSERT IGNORE는 중복 같은 일부 오류를 무시하고 진행합니다. 편리하지만 의도하지 않은 오류까지 조용히 지나갈 수 있으므로, 애플리케이션에서는 중복 좋아요처럼 예상 가능한 상황에만 신중하게 사용해야 합니다.

샘플 데이터 입력하기

이제 테스트할 데이터를 넣습니다.

INSERT INTO members (email, nickname, password_hash) VALUES
('[email protected]', '민수', 'hash_1'),
('[email protected]', '지은', 'hash_2'),
('[email protected]', '서준', 'hash_3'),
('[email protected]', '하린', 'hash_4');

INSERT INTO posts (member_id, title, content, view_count, created_at) VALUES
(1, 'MySQL을 처음 배울 때 중요한 것', '테이블과 관계를 먼저 이해하면 SQL이 쉬워집니다.', 15, '2026-05-01 10:00:00'),
(2, '인덱스는 언제 만들어야 할까', '자주 조회하고 정렬하는 조건을 기준으로 인덱스를 검토합니다.', 31, '2026-05-02 11:00:00'),
(1, '트랜잭션을 사용하는 이유', '여러 변경 작업을 하나의 단위로 묶어 데이터 일관성을 지킵니다.', 22, '2026-05-03 09:30:00'),
(3, '백업은 개발 단계에서도 필요하다', '복구해 보지 않은 백업은 안전하다고 말하기 어렵습니다.', 8, '2026-05-04 14:20:00');

INSERT INTO comments (post_id, member_id, content, created_at) VALUES
(1, 2, '기본키와 외래키를 같이 보니 이해가 잘 됩니다.', '2026-05-01 10:10:00'),
(1, 3, '예제 데이터가 있으면 더 좋을 것 같아요.', '2026-05-01 10:20:00'),
(2, 1, 'EXPLAIN을 같이 보면 도움이 됩니다.', '2026-05-02 11:30:00'),
(3, 4, '결제 같은 기능에서는 특히 중요하겠네요.', '2026-05-03 10:00:00'),
(4, 1, '복구 연습이 중요하다는 말에 공감합니다.', '2026-05-04 15:00:00');

INSERT INTO post_likes (post_id, member_id) VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 3),
(3, 2),
(4, 1);

샘플 데이터는 많을 필요가 없습니다. 중요한 것은 관계가 드러나야 한다는 점입니다. 게시글마다 작성자가 있고, 댓글과 좋아요가 여러 개 붙어 있어야 조회 쿼리를 제대로 연습할 수 있습니다.

게시글 목록 조회하기

커뮤니티의 첫 화면은 보통 게시글 목록입니다. 목록에는 게시글 제목, 작성자, 작성일, 조회수, 댓글 수, 좋아요 수가 필요합니다.

SELECT
    p.id,
    p.title,
    m.nickname AS writer,
    p.view_count,
    COALESCE(c.comment_count, 0) AS comment_count,
    COALESCE(l.like_count, 0) AS like_count,
    p.created_at
FROM posts p
JOIN members m ON m.id = p.member_id
LEFT JOIN (
    SELECT post_id, COUNT(*) AS comment_count
    FROM comments
    WHERE status = 'PUBLISHED'
    GROUP BY post_id
) c ON c.post_id = p.id
LEFT JOIN (
    SELECT post_id, COUNT(*) AS like_count
    FROM post_likes
    GROUP BY post_id
) l ON l.post_id = p.id
WHERE p.status = 'PUBLISHED'
ORDER BY p.created_at DESC, p.id DESC
LIMIT 20;

여기서 LEFT JOIN을 사용한 이유는 댓글이나 좋아요가 없는 게시글도 목록에 보여야 하기 때문입니다.

COALESCE는 값이 NULL일 때 대신 사용할 값을 지정합니다. 댓글이 없는 게시글은 댓글 수 집계 결과가 없으므로 NULL이 될 수 있습니다. 이때 COALESCE(c.comment_count, 0)으로 0을 보여 줍니다.

주의할 점도 있습니다. 댓글 테이블과 좋아요 테이블을 원본 그대로 동시에 조인하면 행이 불필요하게 늘어날 수 있습니다. 예를 들어 댓글 3개, 좋아요 4개인 게시글을 단순 조인하면 12행이 만들어질 수 있습니다. 그래서 위 예제처럼 먼저 게시글별로 집계한 뒤 조인하는 방식이 안전합니다.

커서 기반 페이지네이션 적용하기

게시글이 많아지면 LIMIT 20 OFFSET 10000 같은 방식은 느려질 수 있습니다. 앞의 많은 행을 건너뛰어야 하기 때문입니다.

목록에서는 커서 기반 페이지네이션을 사용할 수 있습니다. 첫 페이지는 다음처럼 조회합니다.

SELECT
    p.id,
    p.title,
    m.nickname AS writer,
    p.view_count,
    p.created_at
FROM posts p
JOIN members m ON m.id = p.member_id
WHERE p.status = 'PUBLISHED'
ORDER BY p.created_at DESC, p.id DESC
LIMIT 20;

다음 페이지를 조회할 때는 마지막으로 본 게시글의 created_at, id를 조건으로 넘깁니다.

SELECT
    p.id,
    p.title,
    m.nickname AS writer,
    p.view_count,
    p.created_at
FROM posts p
JOIN members m ON m.id = p.member_id
WHERE p.status = 'PUBLISHED'
  AND (
      p.created_at < '2026-05-02 11:00:00'
      OR (p.created_at = '2026-05-02 11:00:00' AND p.id < 2)
  )
ORDER BY p.created_at DESC, p.id DESC
LIMIT 20;

이 방식은 페이지 번호를 직접 이동하는 기능에는 불편할 수 있습니다. 하지만 최신순 목록, 무한 스크롤, 모바일 피드에는 잘 맞습니다.

이 쿼리를 위해서는 다음 인덱스가 중요합니다.

KEY idx_posts_status_created_id (status, created_at, id)

조건에 status가 있고, 정렬에 created_at, id가 있기 때문입니다. 인덱스는 단순히 WHERE 절만 보고 만드는 것이 아닙니다. 조건, 정렬, 조인, 조회 빈도를 함께 보고 결정합니다.

게시글 검색 만들기

가장 단순한 검색은 LIKE를 사용하는 것입니다.

SELECT
    p.id,
    p.title,
    m.nickname AS writer,
    p.created_at
FROM posts p
JOIN members m ON m.id = p.member_id
WHERE p.status = 'PUBLISHED'
  AND p.title LIKE 'MySQL%'
ORDER BY p.created_at DESC, p.id DESC
LIMIT 20;

'MySQL%'처럼 앞부분이 고정된 검색은 일반 인덱스를 활용할 가능성이 있습니다. 이를 위해 제목 인덱스를 추가할 수 있습니다.

CREATE INDEX idx_posts_title ON posts (title);

하지만 다음 검색은 다릅니다.

WHERE p.title LIKE '%MySQL%'

앞에 %가 붙으면 일반적인 B-Tree 인덱스를 효율적으로 사용하기 어렵습니다. 데이터가 적을 때는 괜찮지만, 게시글이 많아지면 느려질 수 있습니다.

본문 검색까지 필요하다면 MySQL의 전문 검색 인덱스나 별도 검색 엔진을 검토합니다. 여기서는 원리를 이해하는 것이 중요합니다. 검색 기능은 단순히 LIKE 하나로 끝나지 않습니다. 데이터 양, 언어, 정렬 기준, 검색 정확도에 따라 설계가 달라집니다.

게시글 상세 조회하기

상세 화면에서는 게시글 본문, 작성자, 댓글 수, 좋아요 수를 보여 줍니다.

조회수를 올리는 작업과 상세 조회를 분리해 보겠습니다.

UPDATE posts
SET view_count = view_count + 1
WHERE id = 1
  AND status = 'PUBLISHED';

그다음 게시글을 조회합니다.

SELECT
    p.id,
    p.title,
    p.content,
    p.view_count,
    p.created_at,
    p.updated_at,
    m.nickname AS writer,
    (SELECT COUNT(*)
     FROM comments c
     WHERE c.post_id = p.id
       AND c.status = 'PUBLISHED') AS comment_count,
    (SELECT COUNT(*)
     FROM post_likes l
     WHERE l.post_id = p.id) AS like_count
FROM posts p
JOIN members m ON m.id = p.member_id
WHERE p.id = 1
  AND p.status = 'PUBLISHED';

상세 조회는 보통 게시글 id로 한 건을 찾습니다. 기본키 조회이므로 빠릅니다. 댓글 수와 좋아요 수는 각각 post_id 기준 인덱스가 있으면 효율적으로 계산할 수 있습니다.

댓글 목록은 별도로 조회합니다.

SELECT
    c.id,
    c.content,
    c.created_at,
    m.nickname AS writer
FROM comments c
JOIN members m ON m.id = c.member_id
WHERE c.post_id = 1
  AND c.status = 'PUBLISHED'
ORDER BY c.created_at ASC, c.id ASC
LIMIT 100;

이 쿼리에는 다음 인덱스가 맞습니다.

KEY idx_comments_post_created (post_id, created_at, id)

post_id로 댓글을 찾고, created_at, id 순서로 정렬하기 때문입니다.

댓글 작성과 트랜잭션

댓글 작성은 단순한 입력처럼 보이지만, 실제로는 검증이 필요합니다. 게시글이 존재하는지, 공개 상태인지 확인한 뒤 댓글을 넣는 편이 안전합니다.

START TRANSACTION;

SELECT id
FROM posts
WHERE id = 1
  AND status = 'PUBLISHED'
FOR UPDATE;

INSERT INTO comments (post_id, member_id, content)
VALUES (1, 2, '좋은 글입니다.');

COMMIT;

FOR UPDATE는 조회한 행에 잠금을 겁니다. 다른 트랜잭션이 동시에 같은 게시글 상태를 바꾸는 상황을 조심해야 할 때 사용할 수 있습니다.

모든 상황에 FOR UPDATE가 필요한 것은 아닙니다. 잠금은 데이터 일관성을 지켜 주지만, 너무 넓게 사용하면 동시 처리 성능을 떨어뜨립니다. 트랜잭션은 짧게 유지하고, 사용자 입력을 기다리는 작업은 트랜잭션 안에 넣지 않습니다.

좋아요 토글 구현하기

좋아요는 누르면 추가되고, 다시 누르면 취소되는 방식으로 만들 수 있습니다.

좋아요 추가:

INSERT INTO post_likes (post_id, member_id)
VALUES (1, 2);

좋아요 취소:

DELETE FROM post_likes
WHERE post_id = 1
  AND member_id = 2;

중복 좋아요를 막는 핵심은 애플리케이션 코드가 아니라 데이터베이스 제약입니다.

PRIMARY KEY (post_id, member_id)

애플리케이션에서 아무리 중복 체크를 해도 동시에 요청이 들어오면 틈이 생길 수 있습니다. 최종 방어선은 유니크 제약 또는 기본키 제약이어야 합니다.

통계 쿼리 작성하기

운영자는 서비스가 어떻게 사용되는지 보고 싶어 합니다. 예를 들어 날짜별 게시글 수를 조회할 수 있습니다.

SELECT
    DATE(created_at) AS write_date,
    COUNT(*) AS post_count
FROM posts
WHERE created_at >= '2026-05-01'
  AND created_at < '2026-06-01'
GROUP BY DATE(created_at)
ORDER BY write_date;

많이 좋아요를 받은 게시글도 확인할 수 있습니다.

SELECT
    p.id,
    p.title,
    m.nickname AS writer,
    COUNT(l.member_id) AS like_count
FROM posts p
JOIN members m ON m.id = p.member_id
LEFT JOIN post_likes l ON l.post_id = p.id
WHERE p.status = 'PUBLISHED'
GROUP BY p.id, p.title, m.nickname
ORDER BY like_count DESC, p.id DESC
LIMIT 10;

회원별 활동량도 볼 수 있습니다.

SELECT
    m.id,
    m.nickname,
    COUNT(DISTINCT p.id) AS post_count,
    COUNT(DISTINCT c.id) AS comment_count
FROM members m
LEFT JOIN posts p ON p.member_id = m.id
LEFT JOIN comments c ON c.member_id = m.id
WHERE m.status = 'ACTIVE'
GROUP BY m.id, m.nickname
ORDER BY post_count DESC, comment_count DESC;

통계 쿼리는 서비스 화면 쿼리보다 무거운 경우가 많습니다. 운영 데이터가 커지면 매번 실시간으로 집계하지 않고, 별도 통계 테이블을 만들어 주기적으로 저장하는 방식도 고려합니다.

EXPLAIN으로 실행 계획 확인하기

쿼리를 작성한 뒤에는 감으로만 판단하지 않습니다. EXPLAIN으로 MySQL이 어떤 방식으로 실행할지 확인합니다.

EXPLAIN
SELECT
    p.id,
    p.title,
    m.nickname AS writer,
    p.created_at
FROM posts p
JOIN members m ON m.id = p.member_id
WHERE p.status = 'PUBLISHED'
ORDER BY p.created_at DESC, p.id DESC
LIMIT 20;

확인할 핵심은 다음과 같습니다.

• 너무 많은 행을 읽고 있지 않은가?

• 적절한 인덱스를 사용하고 있는가?

• 정렬 때문에 임시 테이블이나 파일 정렬이 과도하게 발생하지 않는가?

• 조인 순서가 이상하지 않은가?

인덱스는 많을수록 좋은 것이 아닙니다. 인덱스가 많으면 조회는 빨라질 수 있지만, 입력·수정·삭제는 느려질 수 있습니다. 또한 저장 공간도 더 사용합니다.

인덱스를 추가할 때는 다음 순서로 판단합니다.

1. 실제로 자주 실행되는 쿼리인가?

2. WHERE 조건에 자주 쓰이는 열은 무엇인가?

3. JOIN 조건에 쓰이는 열은 무엇인가?

4. ORDER BY에 쓰이는 열은 무엇인가?

5. 추가한 뒤 EXPLAIN 결과가 좋아졌는가?

6. 쓰기 성능에 부담을 주지 않는가?

게시글 목록 성능 개선 예시

처음에는 다음처럼 단순한 쿼리로 목록을 만들 수 있습니다.

SELECT *
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC
LIMIT 20;

하지만 실무에서는 SELECT *를 피하는 편이 좋습니다. 필요하지 않은 본문 content까지 읽으면 I/O가 늘어납니다.

개선한 쿼리는 필요한 열만 가져옵니다.

SELECT
    id,
    member_id,
    title,
    view_count,
    created_at
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC, id DESC
LIMIT 20;

그리고 조건과 정렬에 맞는 인덱스를 준비합니다.

CREATE INDEX idx_posts_status_created_id
ON posts (status, created_at, id);

이미 같은 이름의 인덱스를 테이블 생성 시 만들었다면 다시 만들 필요는 없습니다. 여기서는 어떤 인덱스가 필요한지 강조하기 위해 다시 보여 준 것입니다.

성능 개선은 복잡한 기술에서 시작하지 않습니다. 먼저 필요한 열만 조회하고, 자주 쓰는 조건과 정렬에 맞는 인덱스를 설계하는 것에서 시작합니다.

삭제 정책 정하기

프로젝트를 만들 때 자주 빠뜨리는 부분이 삭제 정책입니다.

게시글 삭제를 실제 DELETE로 처리하면 관련 댓글과 좋아요도 함께 삭제할 수 있습니다.

DELETE FROM posts
WHERE id = 1
  AND member_id = 1;

comments와 post_likes에는 ON DELETE CASCADE가 있으므로 게시글이 삭제되면 연결 데이터도 함께 삭제됩니다.

하지만 운영 서비스에서는 다음처럼 상태값을 바꾸는 방식을 더 자주 사용합니다.

UPDATE posts
SET status = 'DELETED'
WHERE id = 1
  AND member_id = 1;

상태값 삭제의 장점은 이력 보존입니다. 사용자가 삭제한 글을 복구하거나, 신고 처리 과정에서 내용을 확인해야 할 수 있습니다.

대신 모든 조회 쿼리에 다음 조건을 빠뜨리지 않아야 합니다.

WHERE status = 'PUBLISHED'

삭제 정책은 처음에 정해야 합니다. 중간에 바꾸면 쿼리와 운영 도구를 함께 수정해야 합니다.

백업과 복구 절차 만들기

프로젝트가 작아도 백업은 필요합니다. 백업의 목적은 파일을 만드는 것이 아니라 복구할 수 있는 상태를 만드는 것입니다.

가장 기본적인 논리 백업은 mysqldump로 만들 수 있습니다.

mysqldump -u backup_user -p \
  --single-transaction \
  --routines \
  --triggers \
  community_app > community_app_backup.sql

--single-transaction은 InnoDB 테이블을 백업할 때 일관된 시점의 데이터를 얻는 데 도움이 됩니다. 백업 중에도 서비스의 읽기와 쓰기를 완전히 막지 않아도 되는 장점이 있습니다.

복구는 별도 데이터베이스에서 먼저 연습합니다.

mysql -u root -p -e "CREATE DATABASE community_app_restore DEFAULT CHARACTER SET utf8mb4"

mysql -u root -p community_app_restore < community_app_backup.sql

복구 후에는 간단히 검증합니다.

USE community_app_restore;

SELECT COUNT(*) FROM members;
SELECT COUNT(*) FROM posts;
SELECT COUNT(*) FROM comments;
SELECT COUNT(*) FROM post_likes;

백업 파일만 쌓아 두고 복구해 본 적이 없다면 안전하다고 말할 수 없습니다. 운영 체크리스트에는 반드시 복구 테스트를 포함해야 합니다.

권한 분리하기

애플리케이션이 root 계정으로 데이터베이스에 접속하면 위험합니다. 계정은 역할에 따라 나눕니다.

예를 들어 애플리케이션 쓰기 계정을 만듭니다.

CREATE USER 'community_app_rw'@'%' IDENTIFIED BY 'change-this-strong-password';

GRANT SELECT, INSERT, UPDATE, DELETE
ON community_app.*
TO 'community_app_rw'@'%';

조회 전용 계정도 만듭니다.

CREATE USER 'community_app_ro'@'%' IDENTIFIED BY 'change-this-strong-password';

GRANT SELECT
ON community_app.*
TO 'community_app_ro'@'%';

백업 전용 계정은 백업에 필요한 권한만 줍니다.

CREATE USER 'community_backup'@'%' IDENTIFIED BY 'change-this-strong-password';

GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES
ON community_app.*
TO 'community_backup'@'%';

예제에서는 접속 위치를 '%'로 보여 주었지만, 운영 환경에서는 가능한 한 애플리케이션 서버나 백업 서버의 IP로 좁혀야 합니다. 비밀번호도 예시 문자열을 그대로 사용하지 말고 충분히 길고 예측하기 어려운 값으로 바꿔야 합니다.

권한은 확인해야 합니다.

SHOW GRANTS FOR 'community_app_rw'@'%';
SHOW GRANTS FOR 'community_app_ro'@'%';
SHOW GRANTS FOR 'community_backup'@'%';

필요 없어진 권한은 회수합니다.

REVOKE DELETE
ON community_app.*
FROM 'community_app_rw'@'%';

권한 관리의 원칙은 단순합니다. 필요한 계정에 필요한 권한만 줍니다. 이것이 최소 권한 원칙입니다.

느린 쿼리 확인하기

운영 중에는 어떤 쿼리가 느린지 알아야 합니다. MySQL은 느린 쿼리 로그를 남길 수 있습니다.

현재 설정을 확인합니다.

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

실습 환경에서 임시로 켤 수 있습니다.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

long_query_time = 1은 1초 이상 걸린 쿼리를 느린 쿼리로 기록하겠다는 뜻입니다.

운영 환경에서는 설정 파일이나 관리형 DB의 파라미터 설정에 반영해야 재시작 후에도 유지됩니다. 느린 쿼리가 발견되면 바로 인덱스부터 만들지 말고 다음 순서로 봅니다.

1. 정말 필요한 쿼리인가?

2. 너무 많은 행을 조회하지 않는가?

3. SELECT *를 사용하고 있지 않은가?

4. WHERE 조건이 인덱스를 사용할 수 있는 형태인가?

5. 조인 조건이 올바른가?

6. 정렬과 그룹화가 과도하지 않은가?

7. EXPLAIN 결과는 어떤가?

느린 쿼리 분석은 범인을 찾는 일이 아닙니다. 서비스가 실제로 데이터를 어떻게 사용하는지 이해하는 과정입니다.

운영 체크리스트 완성하기

마지막으로 이 프로젝트를 운영한다고 가정하고 체크리스트를 만듭니다.

설계 체크리스트

• 기본키가 모든 테이블에 있는가?

• 외래키로 관계가 명확히 표현되어 있는가?

• 중복되면 안 되는 값에 유니크 제약이 있는가?

• 삭제 정책이 정해져 있는가?

• 상태값의 의미가 명확한가?

쿼리 체크리스트

• 목록 조회에서 필요한 열만 가져오는가?

• 페이지네이션 방식이 데이터 증가에 견딜 수 있는가?

• 댓글 수, 좋아요 수 집계에서 중복 카운트가 발생하지 않는가?

• 검색 쿼리가 인덱스를 사용할 수 있는 형태인가?

• 상세 조회와 댓글 조회가 분리되어 있는가?

인덱스 체크리스트

• 자주 쓰는 WHERE 조건에 맞는 인덱스가 있는가?

• JOIN에 쓰는 외래키 열에 인덱스가 있는가?

• ORDER BY와 LIMIT을 고려했는가?

• 사용하지 않는 인덱스가 너무 많지 않은가?

• EXPLAIN으로 실제 실행 계획을 확인했는가?

보안 체크리스트

• 애플리케이션이 root 계정으로 접속하지 않는가?

• 읽기 계정과 쓰기 계정이 분리되어 있는가?

• 백업 계정 권한이 과도하지 않은가?

• 비밀번호와 접속 정보가 코드에 직접 들어 있지 않은가?

• 외부 입력은 Prepared Statement로 처리하는가?

백업 체크리스트

• 정기 백업이 설정되어 있는가?

• 백업 파일이 안전한 위치에 보관되는가?

• 복구 테스트를 해 보았는가?

• 복구에 걸리는 시간을 알고 있는가?

• 장애 상황에서 누가 어떤 순서로 대응할지 정해져 있는가?

성능 체크리스트

• 느린 쿼리 로그를 확인할 수 있는가?

• 주요 화면 쿼리의 실행 계획을 알고 있는가?

• 데이터가 늘어났을 때 느려질 쿼리를 예상했는가?

• 통계 쿼리와 서비스 쿼리를 구분했는가?

• 모니터링해야 할 지표를 정했는가?

프로젝트를 확장하는 방향

이번 장의 커뮤니티 데이터베이스는 작지만, 확장할 수 있는 지점이 많습니다.

예를 들어 다음 기능을 추가할 수 있습니다.

• 게시판 카테고리

• 대댓글

• 게시글 신고

• 첨부 파일

• 알림

• 회원 팔로우

• 관리자 제재 이력

• 인기 게시글 집계 테이블

기능을 추가할 때마다 바로 테이블부터 만들지 말고 요구사항을 먼저 씁니다. 그다음 관계를 정리하고, 제약 조건과 인덱스를 결정합니다.

MySQL 설계의 핵심은 문법을 많이 아는 것이 아닙니다. 데이터가 어떤 규칙으로 움직이는지 이해하고, 그 규칙을 테이블과 쿼리로 안정적으로 표현하는 것입니다.

마무리: 판단력을 기르는 학습

MySQL을 배운다는 것은 SELECT, INSERT, UPDATE, DELETE 문법을 외우는 데서 끝나지 않습니다.

실제 프로젝트에서는 늘 선택해야 합니다.

• 이 데이터는 한 테이블에 둘 것인가, 나눌 것인가?

• 이 관계는 외래키로 강제할 것인가?

• 이 쿼리는 지금은 빠르지만 데이터가 늘어도 괜찮은가?

• 이 인덱스는 조회를 빠르게 하지만 쓰기 부담을 감당할 수 있는가?

• 이 계정에 이 권한을 줘도 안전한가?

• 백업 파일로 정말 복구할 수 있는가?

이 질문에 답하는 힘이 MySQL의 실무 능력입니다.

작은 커뮤니티 데이터베이스라도 처음부터 끝까지 직접 만들어 보면 많은 것이 연결됩니다. 테이블 설계, 조인, 서브쿼리, 인덱스, 트랜잭션, 권한, 백업, 성능 점검은 따로 떨어진 지식이 아닙니다. 하나의 서비스를 안정적으로 움직이게 하는 부품들입니다.

이제 새로운 기능을 만날 때마다 같은 방식으로 접근하면 됩니다.

요구사항을 정리합니다. 관계를 그립니다. 테이블을 만듭니다. 샘플 데이터를 넣습니다. 쿼리를 작성합니다. 실행 계획을 확인합니다. 인덱스를 조정합니다. 권한과 백업을 점검합니다.

이 흐름을 반복하면 MySQL은 낯선 도구가 아니라, 서비스를 이해하고 지탱하는 강력한 기반이 됩니다.