[MySQL][프로그래머스] 조건에 부합하는 중고거래 댓글 조회하기

bamboocoding·2025년 10월 15일
mysql프로그래머스

MySQL

목록 보기
1/1

문제 출처 https://school.programmers.co.kr/learn/courses/30/parts/17042

📦 DDL & DML

/* 기존에 같은 이름의 테이블이 있으면 제거 */
DROP TABLE IF EXISTS USED_GOODS_REPLY;
DROP TABLE IF EXISTS USED_GOODS_BOARD;

/* ----------------------------- */
/* 1. 중고거래 게시글 테이블      */
/* ----------------------------- */
CREATE TABLE USED_GOODS_BOARD (
    BOARD_ID      VARCHAR(5)   NOT NULL PRIMARY KEY,
    WRITER_ID     VARCHAR(50)  NOT NULL,
    TITLE         VARCHAR(100) NOT NULL,
    CONTENTS      VARCHAR(1000) NOT NULL,
    PRICE         INT          NOT NULL,
    CREATED_DATE  DATE         NOT NULL,
    STATUS        VARCHAR(10)  NOT NULL,
    VIEWS         INT          NOT NULL
);

/* 문제 예시 데이터 */
INSERT INTO USED_GOODS_BOARD (BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS) VALUES
('B0001','kwag98'   ,'반려견 배변패드 팝니다'        ,'정말 저렴히 판매합니다. 전부 미개봉 새상품입니다.',     12000,'2022-10-01','DONE',250),
('B0002','lee871201','국내산 볶음참깨'              ,'직접 농사지은 참깨입니다.',                               3000 ,'2022-10-02','DONE',121),
('B0003','goung12' ,'배드민턴 라켓 사놓고 방치만 해서 팝니다.' ,'거의 새 제품입니다.',                          9000 ,'2022-10-02','SALE',212),
('B0004','keel1990','디올 귀걸이'                   ,'신세계강남점에서 구입. 정품 아닐시 백퍼센트 환불',        130000,'2022-10-02','SALE',199),
('B0005','haphli01','스팸클래식 팔아요'             ,'유통기한 2025년까지에요',                                10000,'2022-10-02','SALE',121);


/* -------------------------------- */
/* 2. 중고거래 댓글(Reply) 테이블    */
/* -------------------------------- */
CREATE TABLE USED_GOODS_REPLY (
    REPLY_ID      VARCHAR(10)  NOT NULL PRIMARY KEY,
    BOARD_ID      VARCHAR(5)   NOT NULL,
    WRITER_ID     VARCHAR(50)  NOT NULL,
    CONTENTS      VARCHAR(1000),
    CREATED_DATE  DATE         NOT NULL,
    FOREIGN KEY (BOARD_ID) REFERENCES USED_GOODS_BOARD(BOARD_ID)
);
/* 문제 예시 데이터 */
INSERT INTO USED_GOODS_REPLY (REPLY_ID, BOARD_ID, WRITER_ID, CONTENTS, CREATED_DATE) VALUES
('R000000001','B0001','s2s2123','구매하겠습니다. 쪽지 드립니다.','2022-10-02'),
('R000000002','B0002','hoho1112','쪽지 주세요.','2022-10-03'),
('R000000003','B0002','ssong19','가격 좀 깎아주세요.','2022-10-04'),
('R000000004','B0003','harry','라켓 사진 추가해주실 수 있나요?','2022-10-05'),
('R000000005','B0004','zkfks12','교환 가능할까요?','2022-10-06'),
('R000000006','B0001','mths002','배변패드 몇 팩인가요?','2022-10-02');

📋 중고거래 게시판 정보 테이블(USED_GOODS_BOARD)

BOARD_IDWRITER_IDTITLECONTENTSPRICECREATED_DATESTATUSVIEWS
B0001kwag98반려견 배변패드 팝니다정말 저렴히 판매합니다. 전부 미개봉 새상품입니다.120002022-10-01DONE250
B0002lee871201국내산 볶음참깨직접 농사지은 참깨입니다.30002022-10-02DONE121
B0003goung12배드민턴 라켓 사놓고 방치만 해서 팝니다.거의 새 제품입니다.90002022-10-02SALE212
B0004keel1990디올 귀걸이신세계강남점에서 구입. 정품 아닐시 백퍼센트 환불1300002022-10-02SALE199
B0005haphli01스팸클래식 팔아요유통기한 2025년까지에요100002022-10-02SALE121

💬 중고거래 게시판 첨부파일 정보 테이블(USED_GOODS_REPLY)

REPLY_IDBOARD_IDWRITER_IDCONTENTSCREATED_DATE
R000000001B0001s2s2123구매하겠습니다. 쪽지 드립니다.2022-10-02
R000000002B0002hoho1112쪽지 주세요.2022-10-03
R000000003B0002ssong19가격 좀 깎아주세요.2022-10-04
R000000004B0003harry라켓 사진 추가해주실 수 있나요?2022-10-05
R000000005B0004zkfks12교환 가능할까요?2022-10-06
R000000006B0001mths002배변패드 몇 팩인가요?2022-10-02

✅ 정답 쿼리

SELECT  B.TITLE,
        B.BOARD_ID,
        R.REPLY_ID,
        R.WRITER_ID,
        R.CONTENTS,
        DATE_FORMAT(R.CREATED_DATE,'%Y-%m-%d') AS CREATED_DATE
FROM    USED_GOODS_BOARD  AS B
JOIN    USED_GOODS_REPLY  AS R USING (BOARD_ID)
WHERE   YEAR(B.CREATED_DATE)=2022
  AND   MONTH(B.CREATED_DATE)=10
ORDER BY R.CREATED_DATE, B.TITLE;

성능 확인 및 더미 데이터 추가

mysql의 성능 측정을 위해 더미 데이터를 추가해본다.
게시글 데이터는 10만, 댓글 데이터는 100만으로 잡았다.
날짜는 20년부터 24년 사이의 값을 랜덤으로 넣고, BOARD_ID의 길이를 늘렸다.

/* 기존에 같은 이름의 테이블이 있으면 제거 */
DROP TABLE IF EXISTS USED_GOODS_REPLY;
DROP TABLE IF EXISTS USED_GOODS_BOARD;

/* ----------------------------- */
/* 중고거래 게시글 테이블      */
/* ----------------------------- */
CREATE TABLE USED_GOODS_BOARD (
    BOARD_ID      VARCHAR(10)   NOT NULL PRIMARY KEY, -- 수정
    WRITER_ID     VARCHAR(50)  NOT NULL,
    TITLE         VARCHAR(100) NOT NULL,
    CONTENTS      VARCHAR(1000) NOT NULL,
    PRICE         INT          NOT NULL,
    CREATED_DATE  DATE         NOT NULL,
    STATUS        VARCHAR(10)  NOT NULL,
    VIEWS         INT          NOT NULL
);

/* -------------------------------- */
/* 중고거래 댓글(Reply) 테이블    */
/* -------------------------------- */
CREATE TABLE USED_GOODS_REPLY (
    REPLY_ID      VARCHAR(10)  NOT NULL PRIMARY KEY,
    BOARD_ID      VARCHAR(10)   NOT NULL, -- 수정
    WRITER_ID     VARCHAR(50)  NOT NULL,
    CONTENTS      VARCHAR(1000),
    CREATED_DATE  DATE         NOT NULL,
    FOREIGN KEY (BOARD_ID) REFERENCES USED_GOODS_BOARD(BOARD_ID)
);

--  재귀 깊이 설정
SET SESSION cte_max_recursion_depth = 1000000;

--  10만 개의 게시글 데이터 생성
INSERT INTO USED_GOODS_BOARD (
    BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS
)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100000 
)
SELECT
    CONCAT('B', LPAD(n, 6, '0')) AS BOARD_ID,
    CONCAT('writer_', n) AS WRITER_ID,
    CONCAT('중고상품 제목_', n) AS TITLE,
    CONCAT('이것은 더미 데이터_', n, '번째 게시글 내용입니다.') AS CONTENTS,
    FLOOR(1000 + RAND() * 90000) AS PRICE,
    --  2020-01-01 ~ 2024-12-31 사이의 랜덤 날짜 생성
    DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * DATEDIFF('2024-12-31', '2020-01-01')) DAY) AS CREATED_DATE,
    CASE WHEN n % 2 = 0 THEN 'SALE' ELSE 'DONE' END AS STATUS,
    FLOOR(RAND() * 1000) AS VIEWS
FROM seq;

-- 100만 개 댓글
INSERT INTO USED_GOODS_REPLY (
    REPLY_ID, BOARD_ID, WRITER_ID, CONTENTS, CREATED_DATE
)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 1000000  
)
SELECT
    CONCAT('R', LPAD(n, 9, '0')) AS REPLY_ID,
    CONCAT('B', LPAD(FLOOR(1 + RAND() * 100000), 6, '0')) AS BOARD_ID, -- 1~10000 랜덤 게시글
    CONCAT('user_', FLOOR(1 + RAND() * 100000)) AS WRITER_ID,
    CONCAT('이것은 더미 댓글_', n, '번째 입니다.') AS CONTENTS,
    --  댓글도 2020~2024 사이 랜덤 날짜
    DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * DATEDIFF('2024-12-31', '2020-01-01')) DAY) AS CREATED_DATE
FROM seq;

다시 정답 쿼리를 실행하면 약 485ms가 걸린다.
EXPLAIN을 사용하여 해당 SQL문의 실행 계획을 살펴보자.

🚀 실행 계획 분석

EXPLAIN SELECT  B.TITLE,
        B.BOARD_ID,
        R.REPLY_ID,
        R.WRITER_ID,
        R.CONTENTS,
        DATE_FORMAT(R.CREATED_DATE,'%Y-%m-%d') AS CREATED_DATE
FROM    USED_GOODS_BOARD  AS B
JOIN    USED_GOODS_REPLY  AS R USING (BOARD_ID)
WHERE   YEAR(B.CREATED_DATE)=2022
  AND   MONTH(B.CREATED_DATE)=10
ORDER BY R.CREATED_DATE, B.TITLE;

USED_GOODS_BOARD는 인덱스를 사용하지 않고 풀테이블 스캔을 하였다.
약 99134행을 읽었고 extra를 보면 using where, using temporary(정렬이나 집계 시 임시 테이블 생성) 그리고 using filesort(order by 수행을 위해 별도 정렬 단계를 수행함)을 확인할 수 있다.
USED_GOODS_REPLY는 board_id 인덱스로 참조 조인하고 있다.
rows = 1 인 것을 보아 B.BOARD_ID 하나당 R(USED_GOODS_REPLY)에서 평균 1행 매칭된다고 볼 수 있다.

explain analyze를 이용하여 좀 더 자세히 분석해보자.

🚀 EXPLAIN ANALYZE 결과 분석

EXPLAIN ANALYZE SELECT  B.TITLE,
        B.BOARD_ID,
        R.REPLY_ID,
        R.WRITER_ID,
        R.CONTENTS,
        DATE_FORMAT(R.CREATED_DATE,'%Y-%m-%d') AS CREATED_DATE
FROM    USED_GOODS_BOARD  AS B
JOIN    USED_GOODS_REPLY  AS R USING (BOARD_ID)
WHERE   YEAR(B.CREATED_DATE)=2022
  AND   MONTH(B.CREATED_DATE)=10
ORDER BY R.CREATED_DATE, B.TITLE;
-> Sort: r.CREATED_DATE, b.TITLE  (actual time=491..493 rows=17045 loops=1)
    -> Stream results  (cost=57777 rows=99134) (actual time=1.06..471 rows=17045 loops=1)
        -> Nested loop inner join  (cost=57777 rows=99134) (actual time=1.04..458 rows=17045 loops=1)
            -> Filter: ((year(b.CREATED_DATE) = 2022) and (month(b.CREATED_DATE) = 10))  (cost=10231 rows=99134) (actual time=0.628..53.8 rows=1717 loops=1)
                -> Table scan on B  (cost=10231 rows=99134) (actual time=0.552..46.8 rows=100000 loops=1)
            -> Index lookup on R using BOARD_ID (BOARD_ID=b.BOARD_ID)  (cost=0.38 rows=1) (actual time=0.199..0.235 rows=9.93 loops=1717)

실행 순서대로 하나씩 살펴보자.

Table scan on B  (cost=10231 rows=99134) (actual time=0.552..46.8 rows=100000 loops=1)

드라이빙 테이블을 USED_GOODS_BOARD(B)로 잡았고 드라이븐 테이블을 USED_GOODS_REPLY로 잡았다.
USED_GOODS_BOARD(B) 전체 행(100000)을 풀스캔했다.
46.8ms가 걸렸다.

Filter: ((year(b.CREATED_DATE) = 2022) and (month(b.CREATED_DATE) = 10))  (cost=10231 rows=99134) (actual time=0.628..53.8 rows=1717 loops=1)

방금 스캔한 10만 행에 함수 (YEAR(B.CREATED_DATE)=2022
AND MONTH(B.CREATED_DATE)=10)을 걸어서 조건에 해당하는 1717건만 가져왔다. 순수하게 값을 필터링하는데 드는 시간은 53.8 - 46.8 = 7ms다.

Index lookup on R using BOARD_ID (BOARD_ID=b.BOARD_ID)  (cost=0.38 rows=1) (actual time=0.199..0.235 rows=9.93 loops=1717)

위에서 걸러진 1717개 게시글마다, USED_GOODS_REPLY(R)에서 BOARD_ID 인덱스로 탐색해서 약 9.93개의 댓글을 찾았다. 9.93 * 1717 약 17050개의 댓글을 찾았다.
루프당 0.235ms가 걸리고 1717번을 반복했으니 약 403ms가 소요된다.

Nested loop inner join  (cost=57777 rows=99134) (actual time=1.04..458 rows=17045 loops=1)

최종 결과 행수는 17045행이며 걸린시간은 458ms이다.

Stream results  (cost=57777 rows=99134) (actual time=1.06..471 rows=17045 loops=1)

지금까지 산출한 결과를 상위 연산으로 전달한다.
완료까지 471ms가 걸렸다.

Sort: r.CREATED_DATE, b.TITLE  (actual time=491..493 rows=17045 loops=1)

최종적으로 정렬해서 출력한다. 정렬에 약 2ms가 걸렸다.

🚀 튜닝

인덱스를 사용하지 않다 보니 게시글 테이블을 풀 테이블 스캔하게 되었고, 결과적으로 불필요한 행도 가져와서 시간이 오래 걸렸다.
또한 WHERE YEAR(b.CREATED_DATE)=2022 AND MONTH(b.CREATED_DATE)=10 처럼 컬럼에 함수가 걸리면 범위 조건으로 변환할 수 없어 인덱스 범위 스캔이 불가능하다.

먼저 USED_GOODS_BOARD의 CREATED_DATE에 인덱스를 걸어봤다.

CREATE INDEX idx_board_created ON USED_GOODS_BOARD (CREATED_DATE);

SELECT  B.TITLE,
        B.BOARD_ID,
        R.REPLY_ID,
        R.WRITER_ID,
        R.CONTENTS,
        DATE_FORMAT(R.CREATED_DATE,'%Y-%m-%d') AS CREATED_DATE
FROM    USED_GOODS_BOARD  AS B
JOIN    USED_GOODS_REPLY  AS R USING (BOARD_ID)
WHERE B.CREATED_DATE >= DATE('2022-10-01') -- 변경
  AND B.CREATED_DATE <  DATE('2022-11-01') -- 변경
ORDER BY R.CREATED_DATE, B.TITLE;
-> Sort: r.CREATED_DATE, b.TITLE  (actual time=663..665 rows=17045 loops=1)
    -> Stream results  (cost=9317 rows=16998) (actual time=0.351..639 rows=17045 loops=1)
        -> Nested loop inner join  (cost=9317 rows=16998) (actual time=0.343..620 rows=17045 loops=1)
            -> Index range scan on B using idx_board_created over ('2022-10-01' <= CREATED_DATE < '2022-11-01'), with index condition: ((b.CREATED_DATE >= <cache>(cast('2022-10-01' as date))) and (b.CREATED_DATE < <cache>(cast('2022-11-01' as date))))  (cost=862 rows=1717) (actual time=0.301..29.2 rows=1717 loops=1)
            -> Index lookup on R using idx_reply_board (BOARD_ID=b.BOARD_ID)  (cost=3.93 rows=9.9) (actual time=0.148..0.343 rows=9.93 loops=1717)

풀 테이블 스캔에서 인덱스 범위 스캔으로 바뀌었다. 풀 테이블 스캔에 필터링까지 46.8ms 걸렸던 게 29.2ms로 줄었다.
하지만 R 테이블에서 0.343 * 1717 ≈ 589ms가 걸린다. 현재 R 테이블은 단일키 인덱스(BOARD_ID)만 사용하다 보니 조인 후 결과가 정렬 기준(R.CREATED_DATE)과 다르다.

  • 수정
    현재 Index range sacn에서 Covering index range scan으로 바꾸기 위해
    B 테이블 인덱스에 SELECT 문에서 사용하는 컬럼들을 추가한다.
    R 테이블에서 BOARD_ID로 먼저 조인하고 뒤에서 CREATED_DATE로 정렬하므로
    이를 복합 인덱스로 만든다.
-- B 인덱스
CREATE INDEX idx_board_created_cover ON USED_GOODS_BOARD (CREATED_DATE, BOARD_ID, TITLE);

-- R 인덱스
CREATE INDEX idx_reply_board_created
ON USED_GOODS_REPLY (BOARD_ID, CREATED_DATE);

✅ 결과

-> Sort: r.CREATED_DATE, b.TITLE  (actual time=154..156 rows=17045 loops=1)
    -> Stream results  (cost=7286 rows=16998) (actual time=0.0719..133 rows=17045 loops=1)
        -> Nested loop inner join  (cost=7286 rows=16998) (actual time=0.0669..121 rows=17045 loops=1)
            -> Filter: ((b.CREATED_DATE >= <cache>(cast('2022-10-01' as date))) and (b.CREATED_DATE < <cache>(cast('2022-11-01' as date))))  (cost=419 rows=1717) (actual time=0.0336..1.32 rows=1717 loops=1)
                -> Covering index range scan on B using idx_board_created_cover over ('2022-10-01' <= CREATED_DATE < '2022-11-01')  (cost=419 rows=1717) (actual time=0.0308..0.889 rows=1717 loops=1)
            -> Index lookup on R using idx_reply_board (BOARD_ID=b.BOARD_ID)  (cost=3.01 rows=9.9) (actual time=0.0244..0.0691 rows=9.93 loops=1717)

예상대로 Covering index range scan으로 바뀐 것을 볼 수 있고
Index lookup on R using idx_reply_board를 보면 실행 시간도 확연히 줄어든 것을 볼 수 있다.
최종적으로 실행 시간이 156ms로 줄었다.

profile
bamboocoding
배운 것을 기록하는 습관을 길들이자.

0개의 댓글