LIMIT OFFSET のページングが遅くなるのは、MySQL が OFFSET の分の行を毎回読んでから捨てるからです。深いページほど読む行が増えます。直すには、前のページの最後の行の値を WHERE に渡して続きから読む「SEEK 方式（キーセットページング）」に切り替えます。
100万件のテーブルで実際に測ると、OFFSET 19980 のページは 0.66 秒かかり、SEEK 方式ではどのページも 1 ミリ秒以下でした。ただし、SEEK 方式の条件を (created_at, id) < (…) と行の比較で書くと、深いページで 6 秒以上かかります。この記事では、速い書き方と遅い書き方の違いまで、実行結果つきで説明します。
以下の結果は MySQL 8.0 で実際に実行したものです。時間は実行した環境での値なので、目安として見てください。
このシナリオで考える
ブログ投稿を管理する 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;
-- 100万件を入れる（3件ずつ同じ created_at になる）
SET SESSION cte_max_recursion_depth = 1000000;
INSERT INTO posts (user_id, title, created_at)
WITH RECURSIVE seq (n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000000
)
SELECT n % 1000, CONCAT('post ', n), '2024-01-01' + INTERVAL (n DIV 3) MINUTE
FROM seq;
一覧ページは1ページあたり20件、新しい順に表示します。「次へ」ボタンで順に送る画面を作る場面で考えます。
LIMIT/OFFSETページングの基本と限界
基本的なページングは次のように書きます。
-- 1ページ目（1〜20件目）
SELECT id, user_id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;
-- 1000ページ目（19981〜20000件目）
SELECT id, user_id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 19980;
1000ページ目を EXPLAIN で見ると、インデックスが使われず、全件を読んで並べかえていました。
EXPLAIN
SELECT id, user_id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 19980;
+-------+------+------+--------+----------------+
| table | type | key | rows | Extra |
+-------+------+------+--------+----------------+
| posts | ALL | NULL | 997279 | Using filesort |
+-------+------+------+--------+----------------+
OFFSET が小さいうちは、MySQL はインデックスを後ろから読みます。OFFSET が大きくなると「インデックスをたどって本体を何万回も読むより、全件を読んで並べかえたほうが安い」と判断し、全件スキャンに切り替えます。EXPLAIN ANALYZE で測った時間は次のとおりです。
| ページ | OFFSET | 読み方 | 読んだ行 | 時間 |
|---|---|---|---|---|
| 1ページ目 | 0 | インデックスを後ろから | 20 | 0.16 ミリ秒 |
| 100ページ目 | 1,980 | インデックスを後ろから | 2,000 | 2.3 ミリ秒 |
| 1,000ページ目 | 19,980 | 全件を読んで並べかえ | 1,000,000 | 664 ミリ秒 |
| 50,000ページ目 | 999,980 | 全件を読んで並べかえ | 1,000,000 | 723 ミリ秒 |
浅いページは速くても、あるところから急に遅くなります。後ろのページを開く人やクローラーが多いサービスでは、ここがデータベースの負荷になります。
SEEK方式（キーセットページング）で解決する
SEEK 方式は「前のページの最後の行の値を使って、次のページを絞り込む」方法です。OFFSET を使わないので、何ページ目でもインデックスの途中から20件だけ読みます。
1ページ目は OFFSET なしの LIMIT で取得します。
SELECT id, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;
+---------+---------------------+
| id | created_at |
+---------+---------------------+
| 1000000 | 2024-08-19 11:33:00 |
| 999999 | 2024-08-19 11:33:00 |
| 999998 | 2024-08-19 11:32:00 |
...（中略）
| 999982 | 2024-08-19 11:27:00 |
| 999981 | 2024-08-19 11:27:00 |
+---------+---------------------+
最後の行は id = 999981, created_at = '2024-08-19 11:27:00' です。2ページ目は、この2つの値を WHERE に渡します。
SELECT id, user_id, title, created_at
FROM posts
WHERE created_at < '2024-08-19 11:27:00'
OR (created_at = '2024-08-19 11:27:00' AND id < 999981)
ORDER BY created_at DESC, id DESC
LIMIT 20;
「created_at が前のページより古い行、または created_at が同じで id が小さい行」という条件です。EXPLAIN ANALYZE では、インデックスの範囲を後ろから読み、20行だけで終わっていました。
-> Limit: 20 row(s) (actual time=0.282..0.286 rows=20 loops=1)
-> Index range scan on posts using idx_created over (created_at < '2024-08-19 11:27:00') OR (created_at = '2024-08-19 11:27:00' AND id < 999981) (reverse) ... (actual time=0.281..0.284 rows=20 loops=1)
同じ書き方で、5万ページ目あたり（created_at = '2024-01-01 06:40:00', id = 1200 の続き）を取っても 0.9 ミリ秒でした。OFFSET 999980 の 723 ミリ秒とは大きな差です。
ID だけで並べる場合は、条件がもっと単純になります。
-- IDの降順ページングの例
SELECT id, user_id, title, created_at
FROM posts
WHERE id < 999981
ORDER BY id DESC
LIMIT 20;
よくある落とし穴と注意点
行の比較 (created_at, id) < (…) は深いページで遅い
同じ条件は WHERE (created_at, id) < ('2024-08-19 11:27:00', 999981) と短く書けます。結果は同じですが、MySQL 8.0 ではこの形がインデックスの範囲の検索になりませんでした。インデックスを先頭（いちばん新しい行）から読み、条件に合わない行を1行ずつ捨てます。
| 続きを取る位置 | OR で書いた条件 | (created_at, id) < (…) |
|---|---|---|
| 2ページ目 | 0.29 ミリ秒（20行） | 0.11 ミリ秒（40行） |
| 5万ページ目あたり | 0.9 ミリ秒（20行） | 6,414 ミリ秒（998,821行） |
浅いページでは差が見えないので、開発中は気づきにくい点に注意してください。SEEK 方式の条件は OR の形で書きます。
並べる列の組み合わせを一意にする
ページの境目を正しく決めるには、並べる列の組み合わせが行ごとに一意である必要があります。created_at は複数の行が同じ値を持つので（今回のデータでは3行ずつ）、必ず id を2つ目の列に足します。インデックスも (created_at, id) の順にします。
-- NG: created_atが重複するとページの境目で行が抜ける・重なる
ORDER BY created_at DESC
-- OK: idを加えて一意にする
ORDER BY created_at DESC, id DESC
複合インデックスの列の順番の考え方は「MySQLの複合インデックス設計：左端マッチとカラム順の基本」で説明しています。
ページ番号へのジャンプはできない
SEEK 方式では「50ページ目に直接移動」はできません。「次へ・前へ」や無限スクロールには向いていますが、ページ番号を並べる画面とは相性がよくありません。
| 画面の作り | 向いている方式 |
|---|---|
| 無限スクロール | SEEK方式 |
| 「次へ/前へ」のみ | SEEK方式 |
| ページ番号ジャンプあり | LIMIT/OFFSET |
| 管理画面・小規模（〜5万件） | LIMIT/OFFSET（シンプルさ優先） |
LIMIT/OFFSETのまま速くする場合（遅延JOIN）
OFFSET 方式のまま速くしたい場合は、サブクエリで ID だけを取ってから JOIN する「遅延 JOIN」が使えます。
SELECT p.id, p.user_id, p.title, p.created_at
FROM posts p
JOIN (
SELECT id
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 999980
) AS paged USING (id);
サブクエリはインデックスだけで答えられるので（カバリングインデックス）、並べかえが要らなくなります。OFFSET 999980 で 723 ミリ秒だったものが 304 ミリ秒になりました。ただし 100 万行を読むことは変わらないので、SEEK 方式ほどは速くなりません。
まとめ
LIMIT/OFFSET は、深いページで MySQL が全件を読んで並べかえるようになり、100万件では 0.7 秒ほどかかりました。前のページの最後の値で絞る SEEK 方式なら、どのページも 1 ミリ秒以下です。条件は created_at < ? OR (created_at = ? AND id < ?) の形で書き、行の比較 (created_at, id) < (?, ?) は使わないでください。「次へ」だけの画面なら、まず SEEK 方式を選びます。
参考リンク
- MySQL 8.0 Reference Manual: LIMIT Query Optimization
- MySQL 8.0 Reference Manual: Row Constructor Expression Optimization
- Qiita: offsetでページネーションは遅い。これからはシーク法だ！
- gihyo.jp: 第200回 LIMIT OFFSETに関するMySQLクエリチューニング
アイキャッチ画像: Photo by Jantine Doornbos on Unsplash
