상품 상세의 「최근 리뷰 10건」이 늦게 뜬다, 인기 상품일수록 심하다는데

기초SELECT이커머스307 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

상품팀: "상품 상세를 열 때마다 아래쪽 리뷰 칸이 늦게 뜬다. 인기 상품일수록 심한 것 같다." 상품 상세는 하루 약 8만 번 열린다. 화면은 상품 하나의 리뷰를 최신순으로 10건 보여 준다(리뷰 번호, 평점, 작성일, 작성 고객). 아래는 상품 100을 열었을 때의 실행계획이다.

테이블과 인덱스

reviews: 15,000행 · 775블록(전체 스캔 757). content CLOB 때문에 행이 커서 블록당 약 19행이 들어간다. 인덱스는 PK(review_id) 하나뿐이다.
상품당 리뷰는 34 ~ 76건(평균 53.6)이고, 상품 100은 57건이다. 한 상품의 리뷰는 테이블 곳곳의 블록에 흩어져 있다.
created_at은 DATE(시분초 있음)다. 같은 상품 안에서 created_at이 같은 리뷰는 지금 0쌍이지만, 그렇다는 제약은 없다.
FK는 꺼져 있고 product_id에 인덱스가 없다(18화 졸업 과제 때의 상태 그대로다). 통계는 최신이다.

원본 SQL

sql

SELECT r.review_id, r.rating, r.created_at, r.customer_id
  FROM reviews r
 WHERE r.product_id = :prod
 ORDER BY r.created_at DESC
 FETCH FIRST 10 ROWS ONLY

실행계획 학습용 예시 · 교재 18화 E4 규모

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Starts | E-Rows | A-Rows |    A-Time   | Buffers |  Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |      1 |        |     10 | 00:00:00.01 |     757 |           |
|*  1 |  VIEW                     |         |      1 |     10 |     10 | 00:00:00.01 |     757 |           |
|*  2 |   WINDOW SORT PUSHED RANK |         |      1 |     54 |     10 | 00:00:00.01 |     757 | 2048  (0) |
|*  3 |    TABLE ACCESS FULL      | REVIEWS |      1 |     54 |     57 | 00:00:00.01 |     757 |           |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("R"."CREATED_AT") DESC )<=10)
   3 - filter("R"."PRODUCT_ID"=:PROD)

문제

이 화면이 한 번에 몇 블록을 읽는지 쓰고, '인기 상품일수록 느리다'는 말이 이 실행계획에서 맞는지 설명하시오.
어느 상품이든 10건만 읽고 멈추도록 인덱스와 SQL을 고치시오. 작성 시각이 같은 리뷰가 생기면 어떻게 되는지도 설명하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 상품 하나 · 최신순과 같은 순서의 인덱스 (동점 깨기 review_id 까지)
CREATE INDEX IX_REV_PROD_DT ON reviews (product_id, created_at, review_id);

SELECT r.review_id, r.rating, r.created_at, r.customer_id
  FROM reviews r
 WHERE r.product_id = :prod
 ORDER BY r.created_at DESC, r.review_id DESC
 FETCH FIRST 10 ROWS ONLY;

개선 후 실행계획 학습용 예시 · 교재 18화 E4 규모

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |      1 |        |     10 | 00:00:00.01 |      12 |
|*  1 |  VIEW                           |                |      1 |     10 |     10 | 00:00:00.01 |      12 |
|*  2 |   WINDOW NOSORT STOPKEY         |                |      1 |     10 |     10 | 00:00:00.01 |      12 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | REVIEWS        |      1 |     54 |     10 | 00:00:00.01 |      12 |
|*  4 |     INDEX RANGE SCAN DESCENDING | IX_REV_PROD_DT |      1 |     54 |     10 | 00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("R"."CREATED_AT") DESC ,
              INTERNAL_FUNCTION("R"."REVIEW_ID") DESC )<=10)
   4 - access("R"."PRODUCT_ID"=:PROD)

모범 답안 서술

한 번에 757블록을 읽는다. 3번 줄이 reviews 15,000행을 전부 읽으며(TABLE ACCESS FULL) product_id = :prod로 걸러 57건을 찾고, 2번 줄이 그 57건을 정렬해 10건을 남긴다. 상품이 무엇이든 테이블 전체를 읽으므로 일량은 어느 상품이나 757블록이다. 따라서 '인기 상품일수록'이라는 말은 이 실행계획으로는 근거가 없다. 하루 8만 번이면 약 6,000만 블록을 읽는다. (product_id, created_at, review_id) 인덱스를 만들면 상품 하나의 리뷰가 이미 최신순으로 줄 서 있다. 인덱스를 거꾸로(RANGE SCAN DESCENDING) 읽다가 10건에서 멈추므로 정렬 줄이 없어진다(WINDOW NOSORT STOPKEY). Buffers는 757 → 12로 준다(인덱스 2, 테이블 10). 정렬 키 created_at만으로는 같은 시각에 쓴 리뷰 둘의 순서가 정해지지 않는다. 지금은 동점이 0쌍이지만 제약으로 막혀 있지는 않다. 동점이 10번째 자리에 걸리면 어느 리뷰가 보일지 실행할 때마다 달라질 수 있다. 그래서 review_id DESC를 붙여 정렬 키를 유일하게 하고, 그 컬럼을 인덱스 끝에도 넣어 STOPKEY가 유지되게 한다.

해설

함정: 동점 깨기를 정렬에만 붙이는 경우. 인덱스가 (product_id, created_at)뿐인데 ORDER BY created_at DESC, review_id DESC를 주면 인덱스는 review_id 순서를 모른다. 그래서 그 상품의 리뷰를 다 읽고 다시 정렬한다. 교재 18화 E4 rev01 실측에서는 (product_id, created_at, rating) 인덱스로 59블록을 읽었다(그 상품의 리뷰를 다 읽고 정렬한 값). 757 → 59도 크게 좋아진 것이지만 STOPKEY는 사라졌다.
ROWNUM <= 10(인라인 뷰 안에 ORDER BY)으로 써도 같은 일을 한다. 실행계획의 연산 이름이 COUNT STOPKEY로 다를 뿐이다.
인덱스에 rating과 customer_id까지 넣으면 테이블을 읽지 않고 2 ~ 3블록으로 끝난다. 대신 인덱스가 커지고, 리뷰가 쌓일 때마다 고쳐야 할 인덱스 항목이 는다(교재 14화와 18화의 적재 비용).
'인기 상품일수록' 같은 현업의 가설은 실행계획으로 확인한다. 이 실행계획에서는 어느 상품이나 일량이 같으므로, 체감이 다르다면 화면의 다른 부분(리뷰 본문 CLOB을 읽는 다른 호출 등)을 재 본다.
리뷰 '더 보기'가 생기면 다음 10건은 마지막 (created_at, review_id) 뒤에서부터 읽는다. 이것이 키 기반 페이징이다(교재 09화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 18화 E4 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 18화 E4 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황