메인 화면의 「요즘 많이 본 상품」 칸 하나가 응답 시간의 절반을 차지한다

기초SELECT이커머스585 / 700

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

상황

이커머스 메인 화면에는 최근 30일 동안 많이 본 상품 10개가 뜬다. API 서버는 최근 30일의 조회 기록을 모두 받아 와 상품별로 세고, 상위 10개를 골라 상품명을 하나씩 읽는다. 메인 화면은 초당 20번쯤 열린다. 메인 화면 응답 0.21초 가운데 이 칸이 0.11초다. 개발자는 "조회 기록이 1,600건 남짓이라 자바에서 세는 게 DB를 덜 괴롭힌다"라고 한다.

테이블과 인덱스

product_views: 100,000행 · 367블록 · view_id · product_id · customer_id · view_date · 인덱스는 PK뿐 · 최근 30일 조회 약 1,640건
products: 280행 · 8블록 · PK (product_id) · 상품명은 name
API 서버와 DB는 같은 IDC에 있고 왕복은 0.5ms다. JDBC fetch size는 기본값 10이다.

원본 SQL

sql

// PopularApi.java : 30일 치 조회 기록을 다 받아 와 자바에서 센다
List<Long> views = jdbc.queryForList(
    "SELECT product_id FROM product_views WHERE view_date >= SYSDATE - 30", Long.class);
Map<Long, Integer> cnt = new HashMap<>();
for (Long p : views) cnt.merge(p, 1, Integer::sum);
List<Long> top10 = cnt.entrySet().stream()
    .sorted(Map.Entry.<Long, Integer>comparingByValue().reversed())
    .limit(10).map(Map.Entry::getKey).collect(toList());
for (Long p : top10) {
    names.add(jdbc.queryForObject("SELECT name FROM products WHERE product_id = ?", String.class, p));
}

실행계획 학습용 예시

xplan

[메인 화면 한 번 · 세션 통계 증가분]
  execute count                           11
  SQL*Net roundtrips to/from client      176
  session logical reads                  553
  bytes sent via SQL*Net to client      19 KB
  SQL*Net message from client           176번 · 0.09 초

[V$SQL · 조회 기록 문장]  EXECUTIONS 1 · FETCHES 165 · ROWS_PROCESSED 1,642 · BUFFER_GETS 532
---------------------------------------------------------------------------------------
| Id  | Operation         | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |      1 |        |   1642 |00:00:00.01 |     532 |
|*  1 |  TABLE ACCESS FULL| PRODUCT_VIEWS |      1 |   1644 |   1642 |00:00:00.01 |     532 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("VIEW_DATE">=SYSDATE@!-30)

[상품명 문장]  SELECT name FROM products WHERE product_id = :1 · 화면 한 번에 10번 · 한 번 2블록

문제

화면에 쓰는 것은 10행인데 이 칸이 0.11초 걸리는 이유를 위 숫자로 설명하시오. 개발자의 말이 맞는지도 쓰시오.
같은 10개를 같은 순서로 돌려주도록 SQL 한 문장으로 고치고, 고친 뒤의 왕복 수를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 문장 : DB가 세고, 고르고, 이름을 붙여 10행만 보낸다
SELECT p.product_id, p.name, v.view_cnt
  FROM (SELECT product_id, COUNT(*) AS view_cnt
          FROM product_views
         WHERE view_date >= SYSDATE - 30
         GROUP BY product_id
         ORDER BY view_cnt DESC, product_id
         FETCH FIRST 10 ROWS ONLY) v
  JOIN products p ON p.product_id = v.product_id
 ORDER BY v.view_cnt DESC, p.product_id;

개선 후 실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |      1 |        |     10 |00:00:00.01 |     387 |          |
|   1 |  SORT ORDER BY                 |               |      1 |     10 |     10 |00:00:00.01 |     387 |  2048 (0)|
|   2 |   NESTED LOOPS                 |               |      1 |     10 |     10 |00:00:00.01 |     387 |          |
|   3 |    NESTED LOOPS                |               |      1 |     10 |     10 |00:00:00.01 |     377 |          |
|*  4 |     VIEW                       |               |      1 |     10 |     10 |00:00:00.01 |     367 |          |
|*  5 |      WINDOW SORT PUSHED RANK   |               |      1 |    277 |     10 |00:00:00.01 |     367 |  2048 (0)|
|   6 |       HASH GROUP BY            |               |      1 |    277 |    276 |00:00:00.01 |     367 |  1.1M (0)|
|*  7 |        TABLE ACCESS FULL       | PRODUCT_VIEWS |      1 |   1644 |   1642 |00:00:00.01 |     367 |          |
|*  8 |     INDEX UNIQUE SCAN          | PK_PRODUCTS   |     10 |      1 |     10 |00:00:00.01 |      10 |          |
|   9 |    TABLE ACCESS BY INDEX ROWID | PRODUCTS      |     10 |      1 |     10 |00:00:00.01 |      10 |          |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=10)
   5 - filter(ROW_NUMBER() OVER ( ORDER BY COUNT(*) DESC ,"PRODUCT_ID")<=10)
   7 - filter("VIEW_DATE">=SYSDATE@!-30)
   8 - access("P"."PRODUCT_ID"="V"."PRODUCT_ID")

[메인 화면 한 번 · 고친 뒤 (학습용 예시)]
  execute count 1 · SQL*Net roundtrips 2 · session logical reads 388 · bytes sent 1 KB · 이 칸 0.11 → 0.008 초

모범 답안 서술

조회 기록 문장은 1,642행을 돌려주는데, fetch size가 기본값 10이라 165번에 나눠 받는다(ROWS_PROCESSED 1,642 ÷ FETCHES 165). 페치 한 번이 왕복 한 번이고, 상위 10개의 상품명을 하나씩 묻느라 10번을 더 오가서 화면 한 번에 왕복이 176번이다. 0.5ms씩 0.09초로, 이 칸 0.11초의 대부분이다. 화면에 쓰는 것은 10행인데 1,642행을 네트워크로 옮기고 자바에서 다시 센다. 개발자의 말은 반만 맞다. DB가 테이블을 읽는 양은 어느 쪽이든 367블록 언저리다. 오히려 지금은 정렬이 없는 계획을 10행씩 받느라 페치마다 블록을 다시 잡아 532블록을 읽는다. DB를 덜 괴롭힌 것이 아니라 더 오가게 했다. 세는 일(GROUP BY product_id)과 고르는 일(FETCH FIRST 10 ROWS ONLY)을 SQL에 맡기고 상품명도 같은 문장에서 조인으로 붙인다. DB는 367블록을 한 번 읽어 276개 상품으로 묶고 상위 10개만 남긴 뒤 상품명을 붙여 10행을 보낸다. 실행은 11 → 1번, 왕복은 176 → 2번, 보내는 양은 19KB → 1KB, 이 칸은 0.11 → 0.008초가 된다. 같은 조회수인 상품은 product_id로 순서를 정해 두었다. 자바의 HashMap은 순서를 보장하지 않아서 원래 판은 동률 상품의 순서가 요청마다 바뀔 수 있었다.

해설

"자바에서 세면 DB가 편하다"는 말은 DB 안의 일만 보고 한 판단이다. 행을 옮기고 오가는 일까지 세면, 화면이 쓰는 만큼만 보내는 쪽이 DB에도 가볍다.
더 줄이려면 (view_date, product_id) 인덱스로 30일 범위만 읽거나(ACC 단원), 이 결과를 1분쯤 캐시한다. 초당 20번 열리는 칸이 같은 답을 매번 다시 계산할 까닭은 없다(교재 15b화).
정렬이 없는 계획을 작은 fetch size로 받으면 페치마다 블록을 다시 잡는다(532 = 367 + 165). 교재 09화의 ARRAYSIZE 실측과 같은 모양이다.
화면 하나가 실행하는 문장 수와 왕복 수를 APM에 함께 남겨 두면 이런 칸을 빨리 찾는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황