件数を制限・ページング - LIMIT / OFFSET

件数制限とページング。OFFSET の性能落とし穴と keyset pagination への移行を解説

概念図

構文

sql

SELECT ... ORDER BY 列 LIMIT 件数 [OFFSET スキップ数]

サンプル

公開日の新しい順で 3 ページ目（1 ページ 20 件）を取得

sql

SELECT id, title
FROM articles
ORDER BY published_at DESC
LIMIT 20 OFFSET 40;

LIMIT と OFFSET

LIMIT n は返す行数の上限、OFFSET m は先頭から m 行を読み飛ばす指定です。ページングの定番ですが、必ず ORDER BY と併用してください。順序が決まっていないと「2 ページ目」の中身は不定になります。

RDBMS ごとの方言

SQL Server の OFFSET ... FETCH NEXT、Oracle の OFFSET ... FETCH FIRST、古い Oracle の ROWNUM 代用など、件数制限の書き方は RDBMS で差があります。詳しくは RDBMS 方言早見表を参照してください。

落とし穴: OFFSET が大きいほど遅くなる

OFFSET 100000 LIMIT 20 のようなクエリは、DB が 先頭から 100,020 行を読んで 100,000 行を捨てる動作になります。深いページほど線形に遅くなるのが基本です。

さらに、ページ送りの途中でデータが挿入/削除されると、行が重複表示されたり飛ばされたりします。

Keyset pagination（シーク法）

深いページでも高速なのが keyset pagination です。OFFSET の代わりに 前ページの最後の値を条件に使い、インデックスで直接その位置にシークします。

ソート列にインデックスがあれば常に O(log N) + LIMIT 件
ランダムアクセス（いきなり 500 ページ目へ）はできない — 次へ/前へのナビゲーションに向く

sql

-- 前ページの最後の published_at = :last_at, id = :last_id を引き継ぐ
SELECT id, title, published_at
FROM articles
WHERE (published_at, id) < (:last_at, :last_id)
ORDER BY published_at DESC, id DESC
LIMIT 20;