[실습] 좋아요 많은 순으로 게시글 조회하는 SQL문 튜닝하기

JSCODE 박재성

2025-12-02

✅ 좋아요 많은 순으로 게시글 조회하기

1. 기본 테이블 셋팅

DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS posts;


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

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

CREATE TABLE likes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

2. 더미 데이터 추가

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

-- posts 테이블에 더미 데이터 삽입
INSERT INTO posts (title, created_at)
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년 내의 임의의 날짜와 시간 생성
FROM cte;

-- 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;

-- likes 테이블에 더미 데이터 삽입
INSERT INTO likes (post_id, user_id, created_at)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    FLOOR(1 + RAND() * 1000000) AS post_id,    -- 1부터 1000000 사이의 난수로 급여 생성
    FLOOR(1 + RAND() * 1000000) AS user_id,    -- 1부터 1000000 사이의 난수로 급여 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

3. 성능 개선해야 할 SQL문

SELECT
    p.id,
    p.title,
    p.created_at,
    COUNT(l.id) AS like_count
FROM
    posts p
INNER JOIN
    likes l ON p.id = l.post_id
GROUP BY
    p.id, p.title, p.created_at
ORDER BY
    like_count DESC
LIMIT 30;



대략 2600ms 정도의 시간이 소요되고 있다. 분명 개선해야 할 SQL문이다.

4. 실행 계획 조회해보기
성능 개선을 위해 어떤 식으로 SQL문이 작동하고 있는 지 살펴보고 어떻게 개선할 지 고민해보자.
[실행 계획 조회]

EXPLAIN SELECT
    p.id,
    p.title,
    p.created_at,
    COUNT(l.id) AS like_count
FROM
    posts p
INNER JOIN
    likes l ON p.id = l.post_id
GROUP BY
    p.id, p.title, p.created_at
ORDER BY
    like_count DESC
LIMIT 30;

 

EXPLAIN ANALYZE SELECT
    p.id,
    p.title,
    p.created_at,
    COUNT(l.id) AS like_count
FROM
    posts p
INNER JOIN
    likes l ON p.id = l.post_id
GROUP BY
    p.id, p.title, p.created_at
ORDER BY
    like_count DESC
LIMIT 30;

-> Limit: 30 row(s)  (actual time=2775..2775 rows=30 loops=1)
    -> Sort: like_count DESC, limit input to 30 row(s) per chunk  (actual time=2775..2775 rows=30 loops=1)
        -> Table scan on <temporary>  (actual time=2675..2745 rows=575582 loops=1)
            -> Aggregate using temporary table  (actual time=2675..2675 rows=575582 loops=1)
                -> Nested loop inner join  (cost=449599 rows=997632) (actual time=0.126..920 rows=1e+6 loops=1)
                    -> Table scan on p  (cost=100428 rows=997632) (actual time=0.0937..115 rows=1e+6 loops=1)
                    -> Covering index lookup on l using post_id (post_id=p.id)  (cost=0.25 rows=1) (actual time=602e-6..703e-6 rows=1 loops=1e+6)

5. 성능 개선
어떻게 성능 개선을 할 지 고민할 때는 성능 저하가 일어난 부분의 원인을 어떻게 개선할 수 있을 지를 고민하다보면 쉽게 떠올릴 수 있다.

SELECT p.*, l.like_count
FROM posts p
INNER JOIN
	(SELECT post_id, count(post_id) AS like_count FROM likes l
	GROUP BY l.post_id
	ORDER BY like_count DESC
	LIMIT 30) l
ON p.id = l.post_id;

1. 이전 SQL문은 INNER JOIN을 한 뒤에 GROUP BY를 했었다. 하지만 위 SQL문은 likes 테이블만 가지고 먼저 GROUP BY를 한 뒤 좋아요 수가 많은 post_id를 찾아낸다. 이 때, GROUP BY 과정에서 post_id만 사용하기 때문에 풀 테이블 스캔을 하지 않고 인덱스만 활용해서 데이터를 조회할 수 있어서 빠르다.(커버링 인덱스)
2. 그러고 기존 posts 테이블과 걸러진 30개의 행을 가진 테이블과 INNER JOIN을 함으로써 데이터 액세스 수를 확 줄였다.
 
성능도 2500ms에서 170ms로 아주 많이 개선되었다.

6. 성능 개선 후 실행 계획
[실행 계획 조회]

EXPLAIN SELECT p.*, l.like_count
FROM posts p
INNER JOIN
	(SELECT post_id, count(post_id) AS like_count FROM likes l
	GROUP BY l.post_id
	ORDER BY like_count DESC
	LIMIT 30) l
ON p.id = l.post_id;



이제 점점 실행 계획이 읽기 어려워진다. 하나 체크하고 가면 좋은 건 풀 테이블 스캔으로 액세스한 데이터의 수가 30으로 줄었다. 그리고 l이라는 테이블에서 인덱스 풀 스캔을 했음을 알 수 있다. 즉, 대부분의 데이터를 원래 풀 테이블 스캔을 하던 걸 풀 인덱스 스캔으로 고친 것이다.
 
[실행 계획 세부 내용 조회]

EXPLAIN ANALYZE SELECT p.*, l.like_count
FROM posts p
INNER JOIN
	(SELECT post_id, count(post_id) AS like_count FROM likes l
	GROUP BY l.post_id
	ORDER BY like_count DESC
	LIMIT 30) l
ON p.id = l.post_id;

-> Nested loop inner join  (cost=20.5 rows=30) (actual time=227..227 rows=30 loops=1)
    -> Filter: (l.post_id is not null)  (cost=0.196..5.88 rows=30) (actual time=227..227 rows=30 loops=1)
        -> Table scan on l  (cost=2.5..2.5 rows=0) (actual time=227..227 rows=30 loops=1)
            -> Materialize  (cost=0..0 rows=0) (actual time=227..227 rows=30 loops=1)
                -> Limit: 30 row(s)  (actual time=227..227 rows=30 loops=1)
                    -> Sort: like_count DESC, limit input to 30 row(s) per chunk  (actual time=227..227 rows=30 loops=1)
                        -> Stream results  (cost=200702 rows=573484) (actual time=0.0883..199 rows=575582 loops=1)
                            -> Group aggregate: count(l.post_id)  (cost=200702 rows=573484) (actual time=0.0837..163 rows=575582 loops=1)
                                -> Covering index scan on l using idx_post_id  (cost=100912 rows=997899) (actual time=0.074..101 rows=1e+6 loops=1)
    -> Single-row index lookup on p using PRIMARY (id=l.post_id)  (cost=0.392 rows=1) (actual time=0.0019..0.00192 rows=1 loops=30)

실제 커버링 인덱스를 활용했음을 알 수 있다. 그리고 풀 테이블 스캔의 데이터보다 훨씬 크기가 작은 커버링 인덱스만을 활용해서 GROUP BY를 실행하니 훨씬 속도가 빠른 걸 알 수 있다.