페이징

뒤로 갈수록 느려집니다

2.7 에서 배운 OFFSET … FETCH 로 게시판 목록을 20개씩 냅니다. 몇 쪽을 보느냐에 따라 읽는 양이 달라집니다.

SET STATISTICS IO ON;

-- 자유 게시판 16만 6천 건을 20개씩 봅니다.
SELECT num, title FROM Board.POSTS_BIG WHERE board_num = 2
ORDER BY group_num DESC, sort_no
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;        -- 1쪽

SELECT num, title FROM Board.POSTS_BIG WHERE board_num = 2
ORDER BY group_num DESC, sort_no
OFFSET 100000 ROWS FETCH NEXT 20 ROWS ONLY;   -- 5,000쪽

계획을 보면 까닭이 한 줄로 드러납니다.

SET SHOWPLAN_TEXT ON;
GO
SELECT num, title FROM Board.POSTS_BIG WHERE board_num = 2
ORDER BY group_num DESC, sort_no
OFFSET 100000 ROWS FETCH NEXT 20 ROWS ONLY;
GO

OFFSET 은 건너뛰는 것이 아니라 읽고 버리는 것입니다. 100,000 을 건너뛰려면 100,000행을 실제로 읽어야 합니다. 5.1 에서 본 "아래에서 몇 행이 올라오는가" 가 그대로 드러납니다.

대부분의 서비스에서는 이것이 문제가 되지 않습니다. 사람은 3쪽 넘게 넘기지 않습니다. 문제가 되는 것은 사람이 아니라 기계입니다 — 목록을 처음부터 끝까지 긁어 가는 수집기나, 전체를 내려받는 배치가 마지막 쪽까지 훑습니다.

몇 번째가 아니라 어디서부터

"100,000행을 건너뛴 다음 20건" 이 아니라 "이 값 다음부터 20건" 으로 적으면 건너뛸 것이 없습니다. 이것을 키셋 페이징이라고 합니다.

앞 쪽의 마지막 행의 정렬 키를 받아 그다음부터 읽습니다. 이 목록은 group_num DESC, sort_no 로 정렬하므로 그 둘을 넘깁니다.

-- 앞 쪽 마지막 행이 group_num = 200002, sort_no = 0 이었다고 합니다.
SELECT TOP (20) num, title
FROM Board.POSTS_BIG
WHERE board_num = 2
  AND group_num <= 200002
  AND (group_num < 200002 OR sort_no > 0)
ORDER BY group_num DESC, sort_no;

조건 모양이 중요합니다

같은 뜻인데 인덱스를 사용하지 못하는 모양이 있습니다. 5.4 가 여기서 다시 나옵니다.

-- 흔히 이렇게 적습니다. 뜻은 맞지만 인덱스로 들어가지 못합니다.
WHERE board_num = 2
  AND (group_num < 200002 OR (group_num = 200002 AND sort_no > 0))

-- 이렇게 적어야 SEEK 에 들어갑니다.
WHERE board_num = 2
  AND group_num <= 200002
  AND (group_num < 200002 OR sort_no > 0)

범위 조건을 하나 더 적어 주는 것이 요령입니다. group_num <= 200002 는 뒤의 OR 조건에 이미 들어 있어 결과를 바꾸지 않지만, 엔진에게 어디서부터 읽으면 되는지 알려 줍니다.

키셋 페이징을 적었다면 반드시 계획을 확인하십시오. 조건이 SEEK: 에 들어갔는지만 보면 됩니다. 들어가지 않았다면 고생만 하고 얻은 것이 없습니다.

둘 중 무엇을 씁니까

쪽 번호를 늘어놓는 화면이라면 OFFSET 이 맞습니다. 사람이 5,000쪽으로 바로 가는 일은 없고, 있어도 그 한 번은 916장이면 됩니다.

"더 보기" 나 무한 스크롤이라면 키셋이 맞습니다. 어차피 다음 쪽으로만 가고, 읽는 도중에 새 글이 올라와도 목록이 흔들리지 않습니다.

목록을 내려받는 API 라면 키셋만 두는 편이 낫습니다. ?page=99999 같은 요청을 누가 보낼지 모릅니다. 키셋에는 애초에 그런 매개 변수가 없습니다.

세는 것이 목록보다 비쌉니다

쪽 번호를 내려면 전부 몇 건인지 알아야 합니다. 그런데 세는 일은 목록을 가져오는 일보다 훨씬 비쌉니다.

SET STATISTICS IO ON;

-- 목록 20건
SELECT TOP (20) num, title FROM Board.POSTS_BIG
WHERE board_num = 2 ORDER BY group_num DESC, sort_no;

-- 총 건수
SELECT COUNT(*) FROM Board.POSTS_BIG WHERE board_num = 2;

-- 한 번에 내려는 시도
SELECT TOP (20) num, title, COUNT(*) OVER () AS 총건수
FROM Board.POSTS_BIG WHERE board_num = 2
ORDER BY group_num DESC, sort_no;

COUNT(*) OVER () 는 편해 보이지만 가장 비쌉니다. TOP (20) 이 있어도 총 건수를 알려면 전부 세어야 하고, 그 값을 모든 행에 붙인 뒤에야 20건을 자릅니다.

세지 않는 방법들

-- 20 대신 21건을 가져옵니다. 21건이 오면 다음 쪽이 있습니다.
SELECT TOP (21) num, title
FROM Board.POSTS_BIG
WHERE board_num = 2 AND group_num <= 200002
  AND (group_num < 200002 OR sort_no > 0)
ORDER BY group_num DESC, sort_no;

-- 표 전체의 대략적인 행 수는 세지 않고 읽습니다.
SELECT SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('Board.POSTS_BIG') AND index_id IN (0, 1);

정렬이 유일하지 않으면 흔들립니다

ORDER BY hit_count 로 20개씩 가져온다고 합시다. 이 표에는 같은 조회수가 500건씩 있습니다. 같은 값끼리의 순서는 정해져 있지 않습니다.

그래서 1쪽과 2쪽에 같은 글이 겹쳐 나오거나 어떤 글이 아예 빠질 수 있습니다. 계획이 바뀌면 순서도 바뀌기 때문입니다. 우연히 잘 나오는 동안에는 아무도 눈치채지 못합니다.

-- 위험합니다. hit_count 가 같은 행끼리의 순서가 정해져 있지 않습니다.
ORDER BY hit_count DESC

-- 유일한 열을 마지막에 붙입니다.
ORDER BY hit_count DESC, num DESC

정렬 키의 마지막에는 언제나 유일한 열을 두십시오. 대개 기본 키입니다. 그러면 순서가 하나로 정해지고, 키셋 페이징도 그 열로 이어 갈 수 있습니다.

Board.POSTS 의 목록은 group_num DESC, sort_no 로 정렬합니다(3.9). 이 둘의 조합은 글마다 유일하므로 안전합니다. 계층 정렬을 두면 페이징이 함께 안정됩니다.

직접 해보기

SELECT num, title, user_num, reg_date, COUNT(*) OVER () AS 총건수
FROM Board.POSTS_BIG
WHERE board_num = 2
ORDER BY reg_date DESC
OFFSET @skip ROWS FETCH NEXT 20 ROWS ONLY;

연습 1에서 만든 것을 지웁니다.
DROP PROCEDURE Board.P_POST_PAGE;
Board.POSTS_BIG 은 5.7 · 5.8 에서 계속 사용합니다.