풀이 이력이 쌓일수록 느려지는 모의고사 문항 추출 개선

• 분야: DB
• 기술 스택: TypeScript · SQLite · Cloudflare D1
• 관련 기술: 상관 서브쿼리 · MATERIALIZED CTE · 실행 계획 · 사용자별 풀이 빈도

문제와 원인

SKCT 문항 추출은 적게 본 문항을 먼저 고르기 위해 후보 문항마다 해당 사용자의 노출 횟수를 셌다. 후보가 1,700개이고 풀이 이력이 50,000행이면, 정렬 중 각 후보에 대해 응시·문항 이력을 다시 탐색하게 된다. 후보 목록에 인덱스가 있어도 정렬 키를 계산하는 상관 서브쿼리 비용은 남았다.

전체 모의고사는 영역별 ROW_NUMBER()를 계산한다. 수정 전 실행 계획에는 같은 이력 집계가 CORRELATED SCALAR SUBQUERY 두 노드로 나타났다. 필요한 문항 수만 세는 SQL도 정렬·이력 조회가 포함된 추출 쿼리를 감싸 실행했다.

이미지 확대

수정 전 전체 모의고사 추출 실행 계획의 반복 상관 서브쿼리

닫기

화면 너비에 맞춤원본 크기−

100%

+원본 보기 (새 창)

확대한 이미지는 상하좌우로 이동할 수 있습니다. 키보드는 이미지 영역에 초점을 두고 방향키를 사용하세요. Esc로 닫습니다.

수정 전 코드

skctMockSelection()의 문항 정렬

ORDER BY (
  SELECT COUNT(*)
  FROM skct_personal_attempt_items ai
  JOIN skct_personal_attempts a ON a.id = ai.attempt_id
  WHERE a.user_key = ?
    AND ai.source_item_id = p.source_item_id
), RANDOM(), p.source_item_id

사용자 조건과 후보 문항 ID가 서브쿼리 안에 함께 들어가 있어 후보마다 탐색이 반복됐다. 연습 문항 추출도 같은 방식으로 횟수를 계산했다.

수정 후 코드

skctMockSelection() · skctPracticeSelection()

사용자의 이력을 source_item_id별로 먼저 집계하고, 만들어진 결과를 후보 목록에 한 번 조인했다. 이력이 없는 문항은 COALESCE(..., 0)으로 0회로 취급한다.

WITH selection_history AS MATERIALIZED (
  SELECT ai.source_item_id, COUNT(*) AS seen_count
  FROM skct_personal_attempts a
  JOIN skct_personal_attempt_items ai ON ai.attempt_id = a.id
  WHERE a.user_key = ?
  GROUP BY ai.source_item_id
)
SELECT p.source_item_id, p.unit_id,
  ROW_NUMBER() OVER (
    PARTITION BY p.unit_id
    ORDER BY COALESCE(h.seen_count, 0), RANDOM(), p.source_item_id
  ) AS ordinal
FROM skct_personal_public_items p
LEFT JOIN selection_history h ON h.source_item_id = p.source_item_id

모의고사는 기존처럼 연습·미완료 응시를 포함한 사용자별 노출 횟수를 사용한다. 연습은 연습 모드의 이력 조건과 완료 문항 우선순위를 유지한다. 공개 릴리스·영역·제외 문항 필터도 그대로 적용했다.

문항 수 조회는 이력을 정렬할 필요가 없어 후보 수와 영역별 상한만 계산하도록 분리했다.

countSql: `SELECT COALESCE(SUM(MIN(item_count,20)),0) AS count FROM (
  SELECT COUNT(*) AS item_count FROM skct_personal_public_items p
  ${where} GROUP BY p.unit_id)`,
countValues: [...input.releaseValues, SKCT_MOCK_EXCLUDED_ITEM_IDS_JSON],

수정 후 실행 계획은 MATERIALIZE selection_history에서 사용자 이력을 집계하고, source_item_id로 후보를 조인한다. 후보마다 실행하던 상관 서브쿼리 노드는 사라졌다.

이미지 확대

수정 후 selection_history 집계와 후보 LEFT JOIN 실행 계획

닫기

화면 너비에 맞춤원본 크기−

100%

+원본 보기 (새 창)

확대한 이미지는 상하좌우로 이동할 수 있습니다. 키보드는 이미지 영역에 초점을 두고 방향키를 사용하세요. Esc로 닫습니다.

검증 결과

같은 스키마의 로컬 SQLite에 후보 1,700문항, 응시 500건, 문항 이력 50,000행을 넣었다. 양쪽 SQL을 한 번씩 워밍업한 뒤 수정 전·후 순서로 5회씩 실행한 중앙값을 비교했다. 문항 집합 비교에서는 난수 함수를 고정해 선택된 ID까지 대조했다.

이미지 확대

같은 데이터에서 5회 실행한 문항 추출 SQL의 중앙값 집계

닫기

화면 너비에 맞춤원본 크기−

100%

+원본 보기 (새 창)

확대한 이미지는 상하좌우로 이동할 수 있습니다. 키보드는 이미지 영역에 초점을 두고 방향키를 사용하세요. Esc로 닫습니다.

다른 사용자의 이력이 순위에 섞이지 않는지, 릴리스가 바뀐 과거 풀이도 같은 기준으로 계산되는지, 연습의 재출제 조건과 영역별 20문항 상한이 유지되는지도 통과했다.