ページネーション・ソートのためのインデックス

一覧画面の典型は「絞り込み + 新着順 + LIMIT」のクエリ。これを索引 1 本で支えることができれば、テーブルサイズが増えても速度が変わりません。

SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

正解は (user_id, created_at) の複合索引。等値 → ソートの順序で、索引自体がユーザーごとに新着順に並んでいます。実行は次のようになります:

1. 索引で user_id = 42 のサブツリーに一気に到達
2. そのサブツリーの末尾（DESC なら末尾）から 20 件読む
3. LIMIT 20 で完了。ソート処理は不要（索引のソート順をそのまま使う）

逆に索引がないと user_id = 42 の全行を読んでから一時領域でソートする動きになります。ユーザーあたり数千件規模になった段階で、画面レスポンスに明確な差が出ます。

定番のページング SQL:

-- 1 ページ 20 件、100 ページ目
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20 OFFSET 1980;

OFFSET は「スキップする行」も実際に読む必要があります。100 ページ目を表示するには 2000 行をソート順に読み、最初の 1980 行を捨てて最後の 20 行だけ返すことになります。

この性質の結果:

• ページ番号が進むほど遅くなる — 1 ページ目は 20 行読むだけ、100 ページ目は 2000 行
• 後ろのページは重いのに、検索エンジンのボット等がページを総なめにするケースがある
• 同時に更新されていると表示がブレる（途中で行が挿入されると同じ行が別ページに現れる）

数百ページ程度なら実用上問題ありませんが、「無限スクロール」や「深い検索結果」ではキーセットページングに切り替えます。

キーセットページングは、「前ページの最後の行のソートキー」を条件にして次ページを取る手法です。OFFSET を使わず、索引をそのまま seek して次の 20 件を読みます。

-- 1 ページ目
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 2 ページ目以降: 前ページ末尾の created_at と id を渡す
SELECT * FROM orders
WHERE user_id = 42
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

メリット:

• ページが深くなっても一定コスト — 常に 20 行しか読まない
• 索引を順に seek するだけ — (user_id, created_at, id) の索引で完結
• 同時挿入があっても表示がブレない — 基準行が明確

デメリットは「N ページ目に直接ジャンプできない」こと。UI として「前へ / 次へ」のスタイルには最適ですが、「3 ページ目に飛ぶ」形の UI には合いません。

ポイント: ORDER BY にユニークになる列（典型は PK）を必ず混ぜること。created_at 単独だと同じ時刻の行で tie（引き分け）が起きて、ページ境界で行の重複・漏れが発生します。

1 つの ORDER BY に昇順と降順が混在する場合、索引にも同じ方向を持たせる必要があります。

-- 優先度高い順 + 新着順
SELECT * FROM tasks
ORDER BY priority ASC, created_at DESC
LIMIT 20;

単純な (priority, created_at) の索引（デフォルト ASC）では、priority 昇順までは合うものの、created_at は逆方向でスキャンする必要があり効率が下がります。方向を合わせた索引が必要:

CREATE INDEX idx_tasks_prio_recency
  ON tasks (priority ASC, created_at DESC);

RDBMS の対応状況:

• PostgreSQL: 全列に個別に方向指定可。古くから対応
• MySQL: 8.0+。7.x は方向指定のパースはするが無視するので注意
• SQL Server: 対応
• Oracle: 対応

MySQL 5.7 以前では、ソート方向が揃わない場合は「全件 ORDER BY した結果から LIMIT する」動作になりやすく、一覧画面の遅延要因になります。利用 RDBMS のバージョンは設計段階で確認すること。

ソートキーに同値が出得る列（created_at ミリ秒単位、status、priority 等）だけで ORDER BY を終わらせると、行の順序が非決定的になります。結果:

• ページネーションで重複・漏れが発生
• ページをリロードすると順序が変わる
• E2E テストが不安定 — 同じクエリで異なる行順が返る

解決は簡単: ORDER BY の末尾に必ず PK を足す。

-- ❌ 同値があると順序がぶれる
ORDER BY created_at DESC

-- ✅ PK を tie-breaker として追加
ORDER BY created_at DESC, id DESC

支える索引も (user_id, created_at, id) のように末尾に PK を含める形にします。PK の比較コストは無視できる程度なので、入れておくデメリットはほぼありません。