ページネーション — OFFSET の罠と keyset pagination
大量データで OFFSET ページングが遅いとき。keyset 方式で一定速度に。
問題 — OFFSET はページが進むほど遅くなる
一覧画面で LIMIT 20 OFFSET 0 / OFFSET 20 / OFFSET 40… とページ送りする実装は一般的だが、実はOFFSET が大きくなるほど遅くなる落とし穴があります。
理由: RDBMS は内部的に 先頭から OFFSET 件を読み飛ばしてから LIMIT 件を返すため。OFFSET 100000 は「10万行スキャンして捨てる」のと同等で、ページ 5000 に行くほど重くなる。
解法: keyset pagination (seek method)
OFFSET をやめて、前のページの最後の ID（または ORDER BY 値）を条件に使う方式。 WHERE id > :last_id ORDER BY id LIMIT 20 という形。
id にインデックスがあれば、RDBMS は木を直接ジャンプして続き 20 件を取れるので、何ページ目でも一定時間。深いページでもサクサクです。
-- 最初のページ
SELECT id, title, created_at
FROM posts
ORDER BY id
LIMIT 20;
-- 次のページ (前ページの最後の id を渡す)
SELECT id, title, created_at
FROM posts
WHERE id > :last_id
ORDER BY id
LIMIT 20;複合キーでソートする場合
ソートキーが (created_at, id) のように複数列になる場合、単純比較ではなくタプル比較が必要。PostgreSQL / MySQL 8 では (created_at, id) > (:last_at, :last_id) と書けます。
SQL Server / SQLite は行値のタプル比較が使えない場合があり、その場合は OR 展開:(created_at > :last_at) OR (created_at = :last_at AND id > :last_id)
-- 作成日降順、同値なら id 降順
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < (:last_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;keyset pagination の制約
便利だが万能ではない:
- 「ページ番号で飛ぶ」ことができない（次/前ボタンのみ）。100ページ目に直接ジャンプ、とかは不可
- ソート順のキーは必ずユニークになるよう設計（タイブレークに id を含める）
- 総件数を出すには別途
COUNT(*)が必要だが、これ自体も大テーブルでは重い
UI 的に次/前で十分な無限スクロール・タイムラインは keyset が理想。ページ番号ジャンプが必須のレポート系は OFFSET を許容することも。
注意点
OFFSET は同時に追加された行で結果がズレるという別の問題もあります。ページ1を見たあとページ2に進む間に新規投稿が1件挿入されると、ページ1の最後の1件がページ2の先頭にも出る（重複）。keyset pagination は ID ベースなので、この問題も自然に解消されます。
OFFSET を残す場合でも、深いページには制限をかける (OFFSET 10000 以上は拒否するなど) のが現実的。検索エンジンが深いページをクロールして重いクエリを走らせるのは、よくあるパフォーマンス障害の原因です。