[실습] 유저 이름으로 특정 기간에 작성된 글 검색하는 SQL문 튜닝하기

JSCODE 박재성

2025-12-02

✅ 유저 이름으로 특정 기간에 작성된 글 검색하기

1. 기본 테이블 셋팅

DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

2. 더미 데이터 추가

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000; 

-- users 테이블에 더미 데이터 삽입
INSERT INTO users (name, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

-- posts 테이블에 더미 데이터 삽입
INSERT INTO posts (title, created_at, user_id)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    CONCAT('Post', LPAD(n, 7, '0')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at, -- 최근 10년 내의 임의의 날짜와 시간 생성
    FLOOR(1 + RAND() * 50000) AS user_id -- 1부터 50000 사이의 난수로 급여 생성
FROM cte;

3. 기존 SQL문 성능 측정하기
2022년 1월 1일부터 2024년 3월 7일까지 User0000046이 작성한 게시글을 조회하는 SQL문이다.

SELECT p.id, p.title, p.created_at
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.name = 'User0000046'
AND p.created_at BETWEEN '2022-01-01' AND '2024-03-07';



약 150ms 정도의 시간이 소요된다.

4. 실행 계획 조회해보기

EXPLAIN SELECT p.id, p.title, p.created_at
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.name = 'User0000046'
AND p.created_at BETWEEN '2022-01-01' AND '2024-03-07';



실행 계획을 보니 풀 테이블 스캔을 하고 있다.

5. 성능 개선해보기
풀 테이블 스캔을 개선하기 위해 인덱스를 추가하려고 한다. 인덱스를 추가할 수 있는 컬럼이 users.name과 posts.created_at이 있다. 일단 둘 다 인덱스로 추가해보자.

CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_created_at ON posts (created_at);

 
그런 뒤에 실행 계획을 조회해봤다.



옵티마이저는 posts.created_at 인덱스가 존재하는 걸 알지만 굳이 사용하지 않는 게 효율적이라고 판단했다. 이 결과에 따라 사용하지 않는 인덱스는 삭제해주자.
 

ALTER TABLE posts DROP INDEX idx_created_at;

 
성능을 다시 한 번 측정해보자.



150ms에서 20ms로 성능이 많이 개선되었다.