MySQLでリスト表示や一覧ページを実装するとき、LIMITとOFFSETの組み合わせは最も手軽なページング手段です。しかしOFFSETの値が大きくなるほどクエリが遅くなる問題があり、数十万件を超えるテーブルでは実用上の限界に達します。この記事では、LIMIT/OFFSETが遅くなる原因を実行計画で確認してから、IDやタイムスタンプを使った「SEEK方式（キーセットページング）」で大幅に高速化する方法を解説します。
このシナリオで考える
ブログ投稿を管理する posts テーブルを使います。件数は100万件とします。
CREATE TABLE posts (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
title VARCHAR(200) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
INDEX idx_created (created_at, id)
) ENGINE=InnoDB;
一覧ページは1ページあたり20件表示。「次へ」ボタンで順に送るUIを実装するケースで考えます。
LIMIT/OFFSETページングの基本と限界
基本的なページングは次のように書きます。
-- 1ページ目（OFFSET 0 = 1〜20件目）
SELECT id, user_id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;
-- 1000ページ目（OFFSET 19980 = 19981〜20000件目）
SELECT id, user_id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 19980;
小さいページ番号では問題ありません。しかし1000ページ目では、MySQLは内部的に19980行を読み取ってから捨て、次の20行を返します。
EXPLAINで実行計画を確認してみます。
EXPLAIN
SELECT id, user_id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 19980;
| key | rows | Extra |
|---|---|---|
| idx_created | 20000 | Using index |
rows が20000です。実際に返すのは20行なのに、MySQLは約2万行を読み込んでいます。OFFSETが増えるほど rows は線形に拡大します。
| ページ番号 | OFFSET値 | rows（スキャン行数） | 目安の実行時間 |
|---|---|---|---|
| 1ページ | 0 | 20 | 0.001秒 |
| 100ページ | 1,980 | 2,000 | 0.003秒 |
| 1,000ページ | 19,980 | 20,000 | 0.05秒 |
| 10,000ページ | 199,980 | 200,000 | 0.8秒 |
| 50,000ページ | 999,980 | 1,000,000 | 4秒以上 |
深いページへ行くほど遅くなる一方で、その差はページ数に比例して線形に拡大します。多くのユーザーが後半のページにアクセスするサービスでは、この問題は深刻です。
SEEK方式（キーセットページング）で根本的に解決する
SEEK方式は「前のページの最後の行の値を使って次ページを絞り込む」方法です。OFFSETを完全に排除し、常にインデックスを先頭から検索します。
1ページ目はOFFSETなしのLIMITで取得します。
SELECT id, user_id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;
結果の最終行が id = 980, created_at = '2024-03-15 12:00:00' だったとします。
2ページ目は最終行の値をWHERE句に渡します。
SELECT id, user_id, title, created_at
FROM posts
WHERE (created_at, id) < ('2024-03-15 12:00:00', 980)
ORDER BY created_at DESC, id DESC
LIMIT 20;
(created_at, id) < (値1, 値2) はMySQL 5.7以降で使えるタプル比較です。「created_atが小さいか、created_atが等しい場合はidが小さい」という複合条件をまとめて表現します。
EXPLAINで確認すると:
| key | rows | Extra |
|---|---|---|
| idx_created | 20 | Using where; Using index |
rows がちょうど20です。100万件のテーブルで何ページ目であっても、スキャン行数は常に20件で一定になります。IDだけでソートする場合は、条件がさらにシンプルです。
-- IDの降順ページングの例
SELECT id, user_id, title, created_at
FROM posts
WHERE id < 980
ORDER BY id DESC
LIMIT 20;
よくある落とし穴と注意点
ソートキーの一意性を確保する
タプル比較で正確にページを区切るには、ソートキーの組み合わせが行ごとに一意である必要があります。created_at は複数行が同じ値を持つ可能性があるため、必ず id を第2キーに追加します。
-- NG: created_atが重複するとページ境界でデータが欠落する
ORDER BY created_at DESC
-- OK: idを加えて一意にする
ORDER BY created_at DESC, id DESC
ランダムなページジャンプは不可
SEEK方式では「50ページ目に直接移動」はできません。「次へ/前へ」ナビゲーションや無限スクロールには向いていますが、ページ番号を直接指定するUIとは相性が悪いです。
| UIパターン | 推奨方式 |
|---|---|
| 無限スクロール | SEEK方式 |
| 「次へ/前へ」のみ | SEEK方式 |
| ページ番号ジャンプあり | LIMIT/OFFSET |
| 管理画面・小規模（〜5万件） | LIMIT/OFFSET（シンプルさ優先） |
LIMIT/OFFSETのまま改善する場合（遅延JOIN）
OFFSET方式を保ちつつ高速化したい場合は、サブクエリでIDだけを取得してからJOINする「遅延JOIN」が有効です。
SELECT p.*
FROM posts p
JOIN (
SELECT id
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 19980
) AS paged USING (id);
サブクエリはインデックスのみで完結するため、本体カラムの取得コストを削減できます。OFFSETによるスキャン自体は発生しますが、カバリングインデックスが効く分、通常の SELECT * よりも高速になります。
まとめ
LIMIT/OFFSETはページが深くなるほどスキャン行数が線形に増加し、大量データでは深刻な遅延を引き起こします。前ページの最終行の値を使ったSEEK方式に切り替えると、インデックスを最大限に活かして常にO(log N)の速度を維持できます。「次へ」だけ対応すればよいUIであれば、まずSEEK方式の採用を検討するのがよいでしょう。
参考リンク
- MySQL 8.0 Reference Manual: SELECT Syntax
- Qiita: offsetでページネーションは遅い。これからはシーク法だ！
- gihyo.jp: 第200回 LIMIT OFFSETに関するMySQLクエリチューニング
アイキャッチ画像: Photo by Jantine Doornbos on Unsplash
