MySQL ‐ Basic CRUD in MySQL - thought-corner/Backend-PlayGround GitHub Wiki

MySQL - Basic CRUD in MySQL

Optimizing CREATE Statements for Performance

INSERT INTO posts (user_id, category_id, title, content, status) VALUES
     (1, 1, '제목1', '내용1', 'PUBLISHED'),
     (1, 2, '제목2', '내용2', 'PUBLISHED'),
     (2, 1, '제목3', '내용3', 'DRAFT');

-- Prepared Statement로 반복 INSERT 최적화
PREPARE stmt_insert_comment FROM
    'INSERT INTO comments (post_id, user_id, content) VALUES (?, ?, ?)';

Prepared Statement를 사용하면 실행 계획 재사용이 가능하다.
DB가 SQL을 처리하는 과정은 다음과 같이 크게 4단계로 나뉜다.
- 1단계: 파싱(Parse) - SQL 문자열을 토큰으로 쪼개고 문법 검증을 하는 단계, 문자열을 AST(Abstract Syntax Tree)로 변환하는 과정이다.
- 2단계: 유효성 검증(Validate) - 테이블이 실제로 존재하는지, 컬럼 타입이 맞는지, 권한이 있는지를 카탈로그(메타데이터)를 조회해서 확인한다.
- 3단계: 실행 계획 생성(Optimize/Plan) - 옵티마이저가 이 쿼리를 어떻게 실행할지 결정하는 단계, INSERT의 경우 단순해보이지만 인덱스가 몇 개 걸려있는지, 트리거가 있는지, 파티션 테이블인지에 따라 실행 경로가 다르다. SELECT의 경우 인덱스 선택, 조인 순서, 스캔 방식 등을 결정하는 가장 비용이 큰 단게이다.
- 4단계: 실행(Execute) - 만들어진 실행 계획대로 실제 데이터를 쓰거나 읽는다.
일반 Statement로 같은 INSERT를 1만 번 실행하면 1→2→3→4를 1만 번 반복한다. 쿼리 문자열이 매번 다르기 때문이다.
Prepared Statement는 prepare 시점에 1→2→3을 딱 1번 수행하고, 그 결과(실행 계획)를 DB 내부 캐시에 저장한다. 이후 execute할 때는 파라미터만 바인딩해서 4단계만 반복하기 때문에 1~3단계의 비용이 완전히 사라지게 된다.

-- 중복 시 에러 없이 건너뛰기
INSERT IGNORE INTO users (username, email, password_hash, display_name)
VALUES
    ('john_doe', '[email protected]', SHA2('pass1', 256), 'John'),
    ('jane_doe', '[email protected]', SHA2('pass2', 256), 'Jane'),
    ('john_doe', '[email protected]', SHA2('pass3', 256), 'John2');

-- 중복 시 특정 컬럼만 업데이트
INSERT INTO users (username, email, password_hash, display_name, login_count)
VALUES ('john_doe', '[email protected]', SHA2('pass', 256), 'John Doe', 1)
ON DUPLICATE KEY UPDATE
         display_name = VALUES(display_name),
         login_count = login_count + 1,  -- 기존값 + 1
         updated_at = CURRENT_TIMESTAMP;

-- 복잡한 업데이트 로직
INSERT INTO posts (user_id, category_id, title, content, view_count)
VALUES (1, 1, '제목', '내용', 1)
ON DUPLICATE KEY UPDATE
         content = CONCAT(content, '\n--- 업데이트됨 ---\n', VALUES(content)),
         view_count = view_count + VALUES(view_count),
         updated_at = CURRENT_TIMESTAMP;

-- 중복 시 기존 데이터 삭제 후 새 데이터 삽입
REPLACE INTO users (user_id, username, email, password_hash, display_name)
VALUES (1, 'john_updated', '[email protected]', SHA2('newpass', 256), 'John Updated');

-- EXISTS를 이용한 조건부 삽입
INSERT INTO comments (post_id, user_id, content)
SELECT 1, 2, '댓글 내용'
WHERE EXISTS (
    SELECT 1 FROM posts
    WHERE post_id = 1 AND status = 'PUBLISHED'
)
  AND NOT EXISTS (
    SELECT 1 FROM comments
    WHERE post_id = 1 AND user_id = 2
      AND created_at > DATE_SUB(NOW(), INTERVAL 1 MINUTE)
);

-- 다른 테이블에서 데이터 복사
INSERT INTO posts (user_id, category_id, title, content, status)
SELECT
    user_id,
    1 as category_id,
    CONCAT('임시 제목 - ', user_id) as title,
    '자동 생성된 내용' as content,
    'DRAFT' as status
FROM users
WHERE status = 'ACTIVE'
  AND login_count > 10;

-- @Transactional(Spring에서 제공하는 AOP 기반의 선언적 트랜잭션)
-- 다중 테이블 INSERT
START TRANSACTION;

-- 1. 게시글 작성
INSERT INTO posts (user_id, category_id, title, content, status)
VALUES (1, 1, '새 게시글', '내용', 'PUBLISHED');

SET @new_post_id = LAST_INSERT_ID();

-- 2. 첫 댓글 자동 생성
INSERT INTO comments (post_id, user_id, content, status)
VALUES (@new_post_id, 1, '게시글을 작성했습니다.', 'APPROVED');

COMMIT;

SELECT Query Optimization

-- 전문 검색 인덱스 활용 (가장 효율적)
SELECT post_id, title, content
FROM posts
WHERE MATCH(title, content) AGAINST('MySQL 최적화' IN NATURAL LANGUAGE MODE)
  AND status = 'PUBLISHED';

전체 텍스트 검색은 긴 문자의 텍스트 데이터를 빠르게 검색하기 위한 MySQL의 부가 기능이다.
전체 텍스트도 결국 인덱스 종류의 하나인데 일반 인덱스와의 차이라면 긴 문장 전체를 대상으로 하냐의 여부이다. InnoDB와 MyISAM 테이블만 지원하며, char, varchar, text 타입의 문자만 인덱싱이 가능하다.
또한 여러 개의 열에 풀 텍스트 인덱스 지정이 가능하다.
특별히 옵션을 지정하지 않거나 뒤에 IN NATURAL LAGNAUGE MODE를 붙이면 자연어 검색을 한다. 이 자연어 검색은 단어가 정확한 것을 검색해준다.
그러나 정확한 단어만 검색이 지원되기 때문에 능동적인 검색은 불가능하다.

-- 접두사 검색 (인덱스 활용 가능)
SELECT user_id, username
FROM users
WHERE username LIKE 'john%' AND status = 'ACTIVE';

-- 복합 검색 조건
SELECT post_id, title
FROM posts
WHERE status = 'PUBLISHED'
  AND (
    title LIKE '%데이터베이스%'
        OR content LIKE '%성능%'
    )
ORDER BY created_at DESC;

Advanced SELECT Techniques for Complex Service Data

SELECT post_id, title, view_count, created_at
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

SELECT post_id, title, view_count, created_at
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;

SELECT post_id, title, created_at
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC
LIMIT 10 OFFSET 10000;

페이지가 깊어질수록 선형으로 느려지는 구조이다.
데이터의 규모가 적은 경우 문제가 되지 않지만 데이터의 규모가 많아질 경우에는 버려지는 양이 점점 많아지게 되어 성능 저하로 이어지게 된다.

-- 커서 기반의 페이징 첫번쨰 페이지
SELECT post_id, title, created_at
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC, post_id DESC
LIMIT 10;

-- 커서 기반의 페이징 두번쨰 페이지
SELECT post_id, title, created_at
FROM posts
WHERE status = 'PUBLISHED' AND post_id < 12345 -- 이와 같이 {postId}를 추가(단, postId가 순차적으로 증가하는 값에서 의미가 있다)
ORDER BY created_at DESC, post_id DESC
LIMIT 10;

SELECT
    post_id,
    title,
    view_count,

    ROW_NUMBER() OVER (ORDER BY view_count DESC) as 전체순위,  -- 순차적인 순위 (1,2,3,4...)
    RANK() OVER (ORDER BY view_count DESC) as 랭킹            -- 동점자 고려한 순위 (1,2,2,4...)

FROM posts
WHERE status = 'PUBLISHED'
ORDER BY view_count DESC;

SELECT
    user_id,
    post_id,
    title,
    view_count,

    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY view_count DESC) as 사용자내순위 -- 각 사용자 안에서의 순위

FROM posts
WHERE status = 'PUBLISHED'
ORDER BY user_id, 사용자내순위;

SELECT
    post_id,
    title,
    created_at,
    view_count,

    LAG(view_count, 1) OVER (ORDER BY created_at) as 이전글조회수, -- 이전 게시글의 조회수
    LEAD(view_count, 1) OVER (ORDER BY created_at) as 다음글조회수 -- 다음 게시글의 조회수

FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at;

WITH post_summary AS (
    -- 1단계: 사용자별 게시글 통계 계산
    SELECT
        user_id,
        COUNT(*) as 게시글수,
        SUM(view_count) as 총조회수,
        AVG(view_count) as 평균조회수
    FROM posts
    WHERE status = 'PUBLISHED'
    GROUP BY user_id
)
-- 2단계: 사용자 정보와 결합해서 최종 결과
SELECT
    u.username,
    ps.게시글수,
    ps.총조회수,
    ps.평균조회수
FROM post_summary ps
         INNER JOIN users u ON ps.user_id = u.user_id
ORDER BY ps.총조회수 DESC;

SELECT
    u.username,
    COUNT(*) as 게시글수,
    SUM(p.view_count) as 총조회수,
    AVG(p.view_count) as 평균조회수
FROM posts p
    INNER JOIN users u ON p.user_id = u.user_id
WHERE p.status = 'PUBLISHED'
GROUP BY u.user_id, u.username
ORDER BY SUM(p.view_count) DESC;

WITH 활성사용자 AS (
    -- 1단계: 활성 사용자만 추출
    SELECT user_id, username
    FROM users
    WHERE status = 'ACTIVE'
),
     최근게시글 AS (
         -- 2단계: 최근 30일 게시글 통계
         SELECT user_id, COUNT(*) as 최근글수
         FROM posts
         WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
           AND status = 'PUBLISHED'
         GROUP BY user_id
     )
-- 3단계: 두 결과를 조합
SELECT
    a.username,
    COALESCE(r.최근글수, 0) as 최근30일글수
FROM 활성사용자 a
         LEFT JOIN 최근게시글 r ON a.user_id = r.user_id
ORDER BY 최근30일글수 DESC;

SELECT
    u.username,
    COALESCE(r.최근글수, 0) as 최근30일글수
FROM users u
         LEFT JOIN (
    SELECT user_id, COUNT(*) as 최근글수
    FROM posts
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
      AND status = 'PUBLISHED'
    GROUP BY user_id
) r ON u.user_id = r.user_id
WHERE u.status = 'ACTIVE'
ORDER BY 최근30일글수 DESC;

SELECT
    user_id,
    username
FROM users u
WHERE status = 'ACTIVE'
  AND EXISTS (
    SELECT 1  -- 실제 값은 중요하지 않음, 존재만 확인
    FROM posts p
    WHERE p.user_id = u.user_id
      AND p.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
);

SELECT username
FROM users u
WHERE u.user_id IN (. -- IN절
    SELECT user_id FROM posts
);

UPDATE & DELETE Guide

개인적인 견해지만 나는 DB Level에서 CASE WHEN 구문을 쓰는 것을 별로 선호하지 않는다. 왜냐하면 DB 측에서 비즈니스 로직이 결정된다는 생각이 들기도 했고 유지보수 관점에서도 어렵기 때문이다.
경우에 따라 다르겠지만 CASE WHEN을 다음과 같이 사용할 수도 있다.

UPDATE posts
SET status = CASE
         WHEN view_count < 10 THEN 'DRAFT'        -- 조회수 낮으면 임시저장
         WHEN view_count >= 1000 THEN 'FEATURED'  -- 조회수 높으면 추천글
         ELSE 'PUBLISHED'                         -- 나머지는 일반 발행
    END,
    updated_at = CURRENT_TIMESTAMP
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 대량의 데이터를 나눠서 업데이트하는 쿼리
UPDATE users
SET status = 'INACTIVE'
    WHERE last_login_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
    AND status = 'ACTIVE'
LIMIT 1000;

DELETE FROM comments
WHERE comment_id = 456 -- DELETE절을 사용하는 경우라면 반드시 WHERE절을 사용해야 한다.(테이블 데이터 전체 유실을 막기 위해)
  AND user_id = 123;