LIMIT で取得する行数を制限する
LIMIT は SQL の中でも一番シンプルなつまみで、SQLite に「最大でこの行数だけ返してね」と伝えるためのものです。SELECT の末尾に付けるだけで、指定した行数を上限として結果が返ってきます。テーブルにそれだけの行がなければ、もちろん少なめに返ってくることもあります。
最初の3行が返ってきます。ただし、どの3行かは分かりません。ここが落とし穴で、ORDER BY を付けないと、SQLite は内部的に都合のいい順序で行を返します。今日は挿入順かもしれませんが、UPDATE やインデックスの変更があれば、明日は別の順序になる可能性もあります。「ちょっとサンプルを見たいだけ」なら LIMIT 単体でも十分ですが、順序に意味がある瞬間から、必ず明示的に並び順を指定する必要があります。
OFFSET で先頭から行をスキップする
LIMIT と OFFSET を組み合わせれば、結果セットの途中から一部を切り出せます。OFFSET k で先頭の k 行を読み飛ばし、残りの中から LIMIT n で最大 n 行を返す、という仕組みです。
つまり「先頭の2行を飛ばして、次の2行を返す」という意味で、ソート済みの結果から3行目と4行目が取れるわけです。イメージとしてはこんな感じ：WHEREで絞り込み、ORDER BYで並べ替え、OFFSETでスキップ、LIMITで件数を制限する。この順番で動くし、どれも欠かせません。
ページングには必ずORDER BYを付ける
LIMITとOFFSETの使い方として一番多いのが、SQLiteでのページング処理です。長いリストを1ページ20行ずつといった形で分割するやつですね。1ページ目はLIMIT 20 OFFSET 0、2ページ目はLIMIT 20 OFFSET 20、というふうに続けていきます。
ここで2つ注意点があります。1つ目は、ORDER BY は省略不可ということ。これがないと「2ページ目」という概念そのものが成立せず、ページを再読み込みするたびに行の順序が入れ替わる可能性があります。2つ目は、ソートキーに id をタイブレーカーとして含めている点です。created_at が同じ投稿が2件あった場合、一意な列を添えてやらないと順序が確定せず、行の位置が入れ替わってページ間に漏れ出すことがあります。
覚えておきたい原則は、一意な列で並び替えるか、ソート列＋一意なタイブレーカーの組み合わせで並び替えることです。
省略記法: LIMIT n, m
SQLite には MySQL との後方互換のため、古いカンマ区切りの書き方も用意されています。LIMIT offset, count という形式です。意味は LIMIT count OFFSET offset と同じですが、引数の順序が逆になっているので読み間違えやすいので注意してください。
-- これら2つは同等です:
SELECT * FROM books LIMIT 10 OFFSET 20;
SELECT * FROM books LIMIT 20, 10; -- 最初にオフセット、次に件数
2 つ目の書き方は短くて済みますが、最初の数字が件数だと思い込んでいると痛い目を見ます。やはり LIMIT n OFFSET k の形を使うのが無難です。明示的ですし、左から右へ素直に読めます。
LIMIT なしで OFFSET だけ使いたいときは LIMIT -1
SQLite の文法上、OFFSET を単独で書くことはできません。必ず LIMIT の後ろに続ける必要があります。では「最初の 10 行をスキップして、それ以降を全部取りたい」場合はどうするのか。定番のイディオムが LIMIT -1 です。SQLite はこれを「上限なし」として解釈してくれます。
負の値ならどれを指定しても同じ挙動になりますが、慣習的に -1 が使われます。スクリプトで結果をページングしながら、最後のバッチで「残り全部ちょうだい」というクエリを投げたいときによく見かけるパターンです。
OFFSETのパフォーマンス問題に注意
あまり語られませんが、踏むと痛い落とし穴があります。OFFSET はSQLiteに処理をスキップさせているのではなく、出力 をスキップさせているだけ、ということです。10,001行目から10,020行目を取得する場合でも、エンジンは内部的に最初の1万行をきっちり走査してから結果を返し始めます。オフセットが小さいうちはコストはほぼゼロですが、数万〜数十万のオーダーになると目に見えて遅くなります。
深いページネーションの定石は キーセットページネーション です。「N行スキップ」ではなく、直前の行のソートキーを覚えておいて「この行より後ろの行をください」と問い合わせる方式ですね。
ページごとに、過去のレコードをスキャンするのではなくインデックスを使ったルックアップで取得します。トレードオフとしては、「47ページ目」のように特定のページへジャンプすることはできず、データを前方向にしかたどれません。とはいえ、無限スクロール型のフィードやAPIのカーソルであれば、これがまさに欲しい挙動です。
OFFSET を使ったページングは、管理画面のテーブルや件数の少ない結果セットなら問題ありません。ただ、データが青天井で増えていくケースでは、キーセットページネーションを選びましょう。
実例で見るページング
ここまでの内容を全部組み合わせてみます。フィルタ・ソート・タイブレーク用の決定的なキーまで含めた、ページング付きクエリの例です:
オフィス用品だけに絞り込み、価格の昇順で並べ替え、同じ価格なら名前で順序を決め、先頭の2件を取り出します。2ページ目にするには OFFSET 0 を OFFSET 2 に変えるだけ。短いクエリですが、どの句もしっかり仕事をしています。
次は DISTINCT
LIMIT は返す行数をコントロールするのに対し、DISTINCT は重複そのものを返すかどうかをコントロールします。SELECT で次に押さえておきたい句であり、シンプルに見えて意外と落とし穴が多い ── 詳しくは次のページで見ていきます。
よくある質問
SQLiteのLIMITは何をするもの?
LIMIT n は SELECT が返す行数を最大 n 件に制限します。実行されるのは WHERE・GROUP BY・ORDER BY の後なので、絞られるのは「最終結果」であって、スキャンされる行そのものではありません。たとえば SELECT * FROM users LIMIT 10 なら最大10件が返ります。
LIMITとOFFSETを組み合わせるとどう動く?
OFFSET k は LIMIT がカウントを始める前に、結果の先頭 k 行をスキップします。つまり LIMIT 10 OFFSET 20 は21〜30行目を返すということ。ただしSQLiteは内部的にスキップ対象の行も舐めていくので、OFFSETの値が大きくなるほど遅くなります。
SQLiteでLIMITなしにOFFSETだけ使える?
そのままでは無理で、OFFSET は LIMIT 句の一部としてしか書けません。回避策が LIMIT -1 OFFSET k で、-1 が「上限なし」を意味するため、k 行スキップしてそれ以降を全部返してくれます。覚えておくと地味に便利な書き方です。
ページング処理にORDER BYが必須なのはなぜ?
ORDER BY を付けないと、SQLiteはどんな順番で行を返してもよいことになっていて、その順番はクエリごとに変わる可能性があります。結果としてページングが壊れ、同じ行が1ページ目と3ページ目に出たり、逆にどこにも出てこなかったりします。LIMIT/OFFSET を使うときは、必ず一意で安定した列で ORDER BY を指定してください。
