MySQL ‐ Basic CRUD in MySQL

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', 'john@example.com', SHA2('pass1', 256), 'John'),
    ('jane_doe', 'jane@example.com', SHA2('pass2', 256), 'Jane'),
    ('john_doe', 'john2@example.com', SHA2('pass3', 256), 'John2');

-- 중복 시 특정 컬럼만 업데이트
INSERT INTO users (username, email, password_hash, display_name, login_count)
VALUES ('john_doe', 'john@example.com', 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', 'john_new@example.com', 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;