Пагінація без OFFSET: бази даних для PHP-розробника

01 · ЧОМУ OFFSET ПОВІЛЬНИЙ

Сторінка 45 000 коштує 45 000 сторінок

каже базі пропустити N рядків, але в індексі немає номерів рядків. Щоб знайти девʼятсот тисячний рядок у порядку published_at DESC, база йде індексом від початку й рахує: перший, другий, і так до 900 020, а потім віддає останні двадцять. Перша сторінка коштує 20 прочитаних рядків, остання майже всю таблицю.

СЛОВНИКOFFSET. Кількість рядків, які база пропускає перед тим, як почати віддавати результат. Пропущені рядки все одно прочитані: з індексу, а часто й з таблиці.

, або seek method, ставить питання інакше: не «пропусти 900 000», а «дай 20 рядків, що йдуть після цього». Після кожної сторінки запамʼятовують ключ її останнього рядка, і наступний запит шукає WHERE (published_at, id) < (?, ?). Для бази це звичайний пошук у складеному індексі: спуск деревом до потрібного місця й 20 кроків далі, хоч перша сторінка, хоч остання.

СЛОВНИКKeyset-пагінація. Наступна сторінка задається значеннями ключа останнього показаного рядка, а не номером. Інші назви: seek method, cursor pagination. Номера сторінки й переходу на сторінку 37 немає, лише «далі» і «назад».

Ключ має бути унікальним. Дата публікації сама собою повторюється, і рядки з однаковою датою на межі сторінок загубляться або покажуться двічі. Тому до неї додають , найчастіше id: порядок ORDER BY published_at DESC, id DESC і індекс (published_at, id) в тому самому порядку.

СЛОВНИКТай-брейкер. Остання колонка в ORDER BY, яка робить порядок однозначним, коли решта колонок однакові. Найчастіше первинний ключ. Без неї рядки з однаковою датою база може віддавати в будь-якому порядку.

02 · ПОРІВНЯННЯ РЯДКІВ

Одна умова, три різні плани

Умову «після рядка з датою d і id 100025» можна записати двома способами. Порівнянням рядків: (published_at, id) < (d, 100025). І розгорнуто: published_at < d OR (published_at = d AND id < 100025). Логічно це одне й те саме, і результат однаковий.

Плани протилежні. PostgreSQL перетворює порівняння рядків на межу пошуку в індексі, а з OR не вміє: він іде індексом від найсвіжіших рядків і відкидає фільтром 900 000 зайвих. MySQL навпаки: OR він розкладає на діапазони індексу, а порівняння рядків перевіряє фільтром на кожному рядку. SQLite справляється з обома. Форма, що працює всюди, це OR з додатковою межею published_at <= d.

PostgreSQL 16 MySQL 8.0 SQLite 3.45

1-- порівняння рядків

2SELECT id, title, published_at FROM vacancies

3WHERE (published_at, id) < ('2024-12-13 00:36:50.028399', 100025)

4ORDER BY published_at DESC, id DESC LIMIT 20;

6-- розгорнуто через OR, як будує Laravel

7WHERE published_at < '2024-12-13 00:36:50.028399'

8 OR (published_at = '2024-12-13 00:36:50.028399' AND id < 100025)

10-- OR з додатковою межею

11WHERE published_at <= '2024-12-13 00:36:50.028399'

12 AND (published_at < '2024-12-13 00:36:50.028399'

13 OR (published_at = '2024-12-13 00:36:50.028399' AND id < 100025))

-- порівняння рядків: 0.056 ms
Index Scan Backward using vacancies_published_at_id_index on vacancies  (actual time=0.018..0.024 rows=20 loops=1)
  Index Cond: (ROW(published_at, id) < ROW('2024-12-13 00:36:50.028399'::timestamp without time zone, 100025))
  Buffers: shared hit=7 read=1

-- OR: 138.697 ms
Index Scan Backward using vacancies_published_at_id_index on vacancies  (actual time=138.675..138.679 rows=20 loops=1)
  Filter: ((published_at < '2024-12-13 00:36:50.028399'::timestamp without time zone) OR ((published_at = '2024-12-13 00:36:50.028399'::timestamp without time zone) AND (id < 100025)))
  Rows Removed by Filter: 900000
  Buffers: shared hit=268293

-- OR з межею: 0.079 ms
Index Scan Backward using vacancies_published_at_id_index on vacancies  (actual time=0.027..0.033 rows=20 loops=1)
  Index Cond: (published_at <= '2024-12-13 00:36:50.028399'::timestamp without time zone)
  Filter: (... OR ...)
  Rows Removed by Filter: 1

Index Cond проти Filter і є вся різниця: у першому плані межа задає, звідки почати читати індекс, у другому індекс дає лише порядок, а умову перевіряють на кожному з 900 000 записів від початку.

1EXPLAIN ANALYZE

2SELECT id, title, published_at FROM vacancies

3WHERE (published_at, id) < ('2024-12-13 00:36:50.028399', 100025)

4ORDER BY published_at DESC, id DESC LIMIT 20;

6-- і те саме з OR

7WHERE published_at < '2024-12-13 00:36:50.028399'

8 OR (published_at = '2024-12-13 00:36:50.028399' AND id < 100025)

-- порівняння рядків: 2.5 s
-> Limit: 20 row(s)  (actual time=2557..2557 rows=20 loops=1)
    -> Filter: ((vacancies.published_at,vacancies.id) < ('2024-12-13 00:36:50.028399',100025))
        -> Index scan on vacancies using vacancies_published_at_id_index (reverse)
           (actual time=0.0858..2476 rows=900020 loops=1)

-- OR: 0.5 ms
-> Limit: 20 row(s)  (cost=105481 rows=20) (actual time=0.524..0.527 rows=20 loops=1)
    -> Index range scan on vacancies using vacancies_published_at_id_index
       over (published_at < '2024-12-13 00:36:50.028399') OR (published_at = '2024-12-13 00:36:50.028399' AND id < 100025) (reverse)
       (actual time=0.523..0.525 rows=20 loops=1)

MySQL 8.0 будує діапазон індексу з OR, а порівняння рядків з < лишає фільтром: 900 020 прочитаних записів. Форма з додатковою межею published_at <= ... на MySQL теж дає Index range scan, 1 ms.

1EXPLAIN QUERY PLAN

2SELECT id, title, published_at FROM vacancies

3WHERE (published_at, id) < ('2024-12-13 00:36:50.028399', 100025)

4ORDER BY published_at DESC, id DESC LIMIT 20;

-- порівняння рядків
SEARCH vacancies USING INDEX vacancies_published_at_id_index (published_at<?)
-- OR
SEARCH vacancies USING INDEX vacancies_published_at_id_index (published_at<?)
-- OR з межею
SEARCH vacancies USING INDEX vacancies_published_at_id_index (published_at<?)

Порівняння рядків SQLite підтримує з версії 3.15. В усіх трьох формах межею пошуку стає published_at, а id перевіряється вже на місці. Усі три повертають ті самі рядки, що й PostgreSQL і MySQL.

03 · OFFSET ПРОТИ KEYSET

Що база робить зі сторінкою 45 000

Мільйон вакансій, індекс (published_at, id), сторінки по 20 рядків, найсвіжіші першими. Той самий запит з OFFSET 0, 100 000 і 900 000, а потім keyset з курсором на тому самому місці, де починається OFFSET 900 000: результат у них збігається рядок у рядок.

OFFSET 0SELECT id, title, published_at FROM vacancies ORDER BY published_at DESC, id DESC LIMIT 20 OFFSET 0;

Limit  (cost=0.42..1.64 rows=20 width=27) (actual time=0.023..0.030 rows=20 loops=1)
  Buffers: shared hit=3 read=3
  ->  Index Scan Backward using vacancies_published_at_id_index on vacancies  (actual time=0.022..0.028 rows=20 loops=1)
Execution Time: 0.043 ms

OFFSET 100 000SELECT id, title, published_at FROM vacancies ORDER BY published_at DESC, id DESC LIMIT 20 OFFSET 100000;

Limit  (cost=6054.22..6055.43 rows=20 width=27) (actual time=32.794..32.801 rows=20 loops=1)
  Buffers: shared hit=29790 read=63
  ->  Index Scan Backward using vacancies_published_at_id_index on vacancies  (actual time=0.888..28.654 rows=100020 loops=1)
Execution Time: 32.846 ms

OFFSET 900 000SELECT id, title, published_at FROM vacancies ORDER BY published_at DESC, id DESC LIMIT 20 OFFSET 900000;

Limit  (cost=54473.15..54474.36 rows=20 width=27) (actual time=213.946..213.953 rows=20 loops=1)
  Buffers: shared hit=268258 read=35
  ->  Index Scan Backward using vacancies_published_at_id_index on vacancies  (actual time=0.014..177.436 rows=900020 loops=1)
Execution Time: 213.977 ms

keyset

SELECT id, title, published_at FROM vacancies WHERE (published_at, id) < ('2024-12-13 00:36:50.028399', 100025) ORDER BY published_at DESC, id DESC LIMIT 20;

Limit  (cost=0.42..6.27 rows=20 width=27) (actual time=0.018..0.027 rows=20 loops=1)
  Buffers: shared hit=7 read=1
  ->  Index Scan Backward using vacancies_published_at_id_index on vacancies  (actual time=0.018..0.024 rows=20 loops=1)
        Index Cond: (ROW(published_at, id) < ROW('2024-12-13 00:36:50.028399'::timestamp without time zone, 100025))
Execution Time: 0.056 ms

PostgreSQL 16, таблиця на 1 000 000 рядків, прогрітий кеш. Рядки Planning прибрано.

OFFSET 0SELECT id, title, published_at FROM vacancies ORDER BY published_at DESC, id DESC LIMIT 20 OFFSET 0;

-> Limit: 20 row(s)  (cost=0.0493 rows=20) (actual time=0.287..0.304 rows=20 loops=1)
    -> Index scan on vacancies using vacancies_published_at_id_index (reverse)  (cost=0.0493 rows=20) (actual time=0.285..0.301 rows=20 loops=1)

OFFSET 100 000SELECT id, title, published_at FROM vacancies ORDER BY published_at DESC, id DESC LIMIT 20 OFFSET 100000;

-> Limit/Offset: 20/100000 row(s)  (cost=101045 rows=20) (actual time=992..992 rows=20 loops=1)
    -> Sort: vacancies.published_at DESC, vacancies.id DESC, limit input to 100020 row(s) per chunk  (actual time=982..988 rows=100020 loops=1)
        -> Table scan on vacancies  (cost=101045 rows=995631) (actual time=0.065..228 rows=1e+6 loops=1)

OFFSET 900 000SELECT id, title, published_at FROM vacancies ORDER BY published_at DESC, id DESC LIMIT 20 OFFSET 900000;

-> Limit/Offset: 20/900000 row(s)  (cost=102014 rows=20) (actual time=981..981 rows=20 loops=1)
    -> Sort: vacancies.published_at DESC, vacancies.id DESC, limit input to 900020 row(s) per chunk  (actual time=894..946 rows=900020 loops=1)
        -> Table scan on vacancies  (cost=102014 rows=995631) (actual time=0.199..249 rows=1e+6 loops=1)

keyset

SELECT id, title, published_at FROM vacancies WHERE published_at < '2024-12-13 00:36:50.028399' OR (published_at = '2024-12-13 00:36:50.028399' AND id < 100025) ORDER BY published_at DESC, id DESC LIMIT 20;

-> Limit: 20 row(s)  (cost=105481 rows=20) (actual time=0.524..0.527 rows=20 loops=1)
    -> Index range scan on vacancies using vacancies_published_at_id_index over (published_at < '2024-12-13 00:36:50.028399') OR (published_at = '2024-12-13 00:36:50.028399' AND id < 100025) (reverse)  (actual time=0.523..0.525 rows=20 loops=1)

MySQL 8.0.46, InnoDB, таблиця на 1 000 000 рядків.

rows=900020: Скільки рядків віддав вузол сканування. Limit узяв з них лише останні 20, решту база прочитала й викинула.
Buffers: 268 258 сторінок для OFFSET 900 000 проти 8 для keyset на тому самому місці: індекс читається підряд, і за кожним записом база ще йде в таблицю.
Index Cond: Є лише в keyset. Межа в Index Cond означає, що база починає читати індекс просто з потрібного місця.

Table scan і Sort: Для глибокого OFFSET MySQL вирішив, що прочитати таблицю й відсортувати дешевше, ніж іти індексом і за кожним записом у кластерний індекс. З FORCE INDEX вийшло ще гірше: 1.6 s і ті самі 900 020 рядків.
Limit/Offset: Вузол, який викидає перші 900 000 рядків. Від номера сторінки залежить лише він, і саме він робить кожну наступну сторінку дорожчою.
Index range scan: Keyset: MySQL розклав OR на діапазон індексу й почав читати з потрібного місця. Номер сторінки на ціну більше не впливає.

04 · ПАГІНАЦІЯ В LARAVEL

Три пагінатори і що вони питають у бази

Усі три методи стоять на запиті з ORDER BY, і SQL нижче знято з журналу запитів Laravel 13 на PostgreSQL для третьої сторінки по 20 рядків.

php VacancyController.php paginate()

1$page = Vacancy::query()

2 ->orderByDesc('published_at')->orderByDesc('id')

3 ->paginate(20);

5// select count(*) as "aggregate" from "vacancies"

6// select * from "vacancies"

7// order by "published_at" desc, "id" desc limit 20 offset 40

ЩО ПЕРЕВІРЯЄ: Два запити: count(*) для кількості сторінок і сам OFFSET. На мільйоні рядків count(*) займає десятки мілісекунд на кожному відкритті сторінки, і глибокі сторінки дорожчають.
КОЛИ БРАТИ: Адмінки, невеликі таблиці, списки, де потрібні номери сторінок і загальна кількість.

php VacancyController.php simplePaginate()

1$page = Vacancy::query()

2 ->orderByDesc('published_at')->orderByDesc('id')

3 ->simplePaginate(20);

5// select * from "vacancies"

6// order by "published_at" desc, "id" desc limit 21 offset 40

ЩО ПЕРЕВІРЯЄ: Без count(*): бере на рядок більше, щоб дізнатися, чи є наступна сторінка. OFFSET лишається, тож глибокі сторінки так само повільні.
КОЛИ БРАТИ: Коли загальна кількість не потрібна, а сторінки рідко гортають далеко.

php VacancyController.php cursorPaginate()

1$page = Vacancy::query()

2 ->orderByDesc('published_at')->orderByDesc('id')

3 ->cursorPaginate(20);

4// курсор у URL: ?cursor=eyJwdWJsaXNoZWRfYXQiOi...

6// select * from "vacancies" where ("published_at" < '2026-10-01 00:06:50'

7// or ("published_at" = '2026-10-01 00:06:50' and ("id" < 999973)))

8// order by "published_at" desc, "id" desc limit 21

ЩО ПЕРЕВІРЯЄ: Keyset: курсор це base64 від JSON зі значеннями колонок ORDER BY останнього рядка. Умову Laravel будує через OR, і на PostgreSQL 16 глибокий курсор так читає індекс від початку: 200 ms на нашій таблиці. Додайте межу ->where('published_at', '<=', $cursor->parameter('published_at')), і та сама сторінка займає 1 ms.
КОЛИ БРАТИ: Стрічки, нескінченний скрол, API, великі таблиці. Колонки ORDER BY мають бути в SELECT і без NULL, а остання з них унікальною.

php VacancyController.php lazyById()

1foreach (Vacancy::query()->where('status', 'active')->lazyById(500) as $vacancy) {

2 // ...

4// select * from "vacancies" where "status" = 'active' and "id" is not null

5// order by "id" asc limit 500

6// select * from "vacancies" where "status" = 'active' and "id" > 2081

7// order by "id" asc limit 500

ЩО ПЕРЕВІРЯЄ: Той самий keyset за первинним ключем, але для обходу всієї таблиці в коді, а не для сторінок у браузері. chunkById() робить те саме колбеками.
КОЛИ БРАТИ: Команди, черги, експорти, sitemap: усе, що проходить велику таблицю шматками. chunk() з OFFSET на великій таблиці повільнішає з кожним шматком і пропускає рядки, якщо їх змінюють під час обходу.

05 · ЯК У PHP UKRAINE

Каталог на OFFSET, sitemap на keyset

Каталог вакансій гортається за номерами сторінок: forPage() це LIMIT і OFFSET, плюс окремий count(*) для загальної кількості. Порядок published_at DESC, id DESC уже має унікальний тай-брейкер, тож вакансії з однаковою датою не перескакують між сторінками. Людям тут потрібні номери сторінок і лічильник, тож OFFSET виправданий, доки видимих вакансій не сотні тисяч. Sitemap і масові обходи вакансій натомість ідуть через lazyById(), тобто keyset за id: WHERE id > ? ORDER BY id LIMIT 500.

php EloquentSitemapQuery.php фрагмент

1public function jobPages(): iterable

3 $query = JobModel::query()->visible()->orderBy('id')->select(['id', 'slug', 'updated_at']);

5 foreach ($query->lazyById(500) as $job) {← where id > ? order by id limit 500

6 yield [

7 'path' => '/jobs/'.$job->id.'-'.$job->slug,

8 'lastmod' => DateTimeImmutable::createFromInterface($job->updated_at ?? $job->published_at),

9 ];

10 }

11}

src/Hiring/Infrastructure/Catalog/EloquentJobCatalog.phppage(): orderByDesc('published_at'), orderByDesc('id'), потім (clone $query)->count() і forPage($page, $perPage). Третя сторінка це limit 20 offset 40.

src/Hiring/Infrastructure/Persistence/EloquentJobRepository.phpvisibleFromSourceNotSeenSince() і visible() обходять вакансії шматками через lazyById(200): синхронізація шукає вакансії джерела, яких давно не бачила, без OFFSET.

src/Hiring/Infrastructure/Catalog/EloquentCompanyDirectory.phpКаталог компаній так само на forPage() з номерами сторінок, а порядок за кількістю вакансій доповнено orderBy('name').

06 · ТИПОВІ ПОМИЛКИ

Де keyset ламається

✕ 1

Порядок без тай-брейкера

ORDER BY published_at DESC і курсор лише за датою. Двадцять вакансій, опублікованих в одну секунду, на межі сторінок: частина зникне, бо умова published_at < ? відріже всі рядки з цією датою, включно з непоказаними.

Натомість: Додайте унікальну колонку останньою в ORDER BY і в індекс: (published_at, id).

✕ 2

NULL у колонці сортування

Порівняння з NULL дає не true і не false, тож рядки з NULL у колонці курсора не потрапляють на жодну сторінку. Сортування за salary_usd_to, де в половини вакансій зарплати немає, з keyset тихо губить цю половину.

Натомість: Сортуйте за колонкою NOT NULL або за COALESCE у згенерованій колонці з індексом. Для сортування з NULLS LAST залишайтеся на OFFSET.

✕ 3

Різні напрямки сортування

ORDER BY published_at DESC, id ASC не можна записати одним порівнянням рядків (published_at, id) < (?, ?), а індекс (published_at, id) не дає такого порядку без сортування.

Натомість: Однаковий напрямок для всіх колонок курсора. Якщо потрібен змішаний, умова лише через OR, а індекс з тим самим змішаним напрямком.

✕ 4

Перевірити лише першу сторінку

На першій сторінці і OFFSET, і будь-яка форма keyset швидкі. Різниця видна лише глибоко: OR на PostgreSQL чи порівняння рядків на MySQL на першій сторінці читають 20 записів, а на останній мільйон.

Натомість: Дивіться EXPLAIN ANALYZE з курсором з кінця таблиці. У плані має бути Index Cond у PostgreSQL чи Index range scan у MySQL, а не Filter з тисячами Rows Removed.

✕ 5

Курсор як секрет

Курсор Laravel це base64 від JSON, його легко прочитати й змінити руками. Він не підписаний і не прихований.

Натомість: Не кладіть у колонки сортування того, що не можна показувати, і не покладайтеся на курсор як на обмеження доступу: фільтри за користувачем мають бути в самому запиті.

07 · СЛОВНИК

OFFSET: Кількість рядків, які база пропускає перед тим, як почати віддавати результат. Пропущені рядки все одно прочитані: з індексу, а часто й з таблиці.
Keyset-пагінація: Наступна сторінка задається значеннями ключа останнього показаного рядка, а не номером. Інші назви: seek method, cursor pagination. Номера сторінки й переходу на сторінку 37 немає, лише «далі» і «назад».
Тай-брейкер: Остання колонка в ORDER BY, яка робить порядок однозначним, коли решта колонок однакові. Найчастіше первинний ключ. Без неї рядки з однаковою датою база може віддавати в будь-якому порядку.

08 · ПЕРЕВІР СЕБЕ

Питання зі співбесід

Усі питання з SQL →

База не вміє стрибнути одразу на стотисячний рядок: вона читає 100 020 рядків у потрібному порядку і віддає останні 20. Що далі сторінка, то більше роботи. На нашій таблиці в PostgreSQL це 0.04 ms для першої сторінки і 214 ms для OFFSET 900 000.

Замість номера сторінки клієнт передає значення ключа останнього показаного рядка, наприклад published_at і id. Запит шукає WHERE (published_at, id) < (?, ?) ORDER BY published_at DESC, id DESC LIMIT 20, і з індексом (published_at, id) база одразу стає на потрібне місце дерева. Кожна сторінка коштує однаково. Ціна: немає номерів сторінок і переходу на довільну сторінку, а порядок має бути однозначним.

paginate() робить count(*) і LIMIT з OFFSET, тож дає номери сторінок і загальну кількість. simplePaginate() не рахує кількість, але OFFSET лишається. cursorPaginate() це keyset: замість OFFSET умова на колонки ORDER BY з курсора, без count(*) і без номерів сторінок, тільки наступна й попередня.

Laravel розгортає курсор у published_at < ? OR (published_at = ? AND id < ?). PostgreSQL 16 не робить з такого OR межу пошуку в індексі: індекс дає лише порядок, а умову він перевіряє фільтром, починаючи з найсвіжіших рядків. Що далі курсор, то більше Rows Removed by Filter. Варіанти: додати ->where('published_at', '<=', $cursor->parameter('published_at')), щоб зʼявилася межа в Index Cond; написати умову порівнянням рядків через whereRaw('(published_at, id) < (?, ?)') для PostgreSQL; або сортувати лише за id, якщо порядок за id збігається з потрібним.

← ПОПЕРЕДНЯN+1 НАСТУПНА →ACID

ТЕМАСкладені індекси ТЕМАEXPLAIN ТЕМАІндекси ПИТАННЯ · SQLЧому OFFSET-пагінація повільна на великих таблицях і що таке keyset? ПИТАННЯ · SQLЩо таке складений індекс і чому важливий порядок колонок? ПИТАННЯ · SQLЯк NULL поводиться в порівняннях, агрегатах і унікальних індексах? ПИТАННЯ · SQLЯк читати EXPLAIN і що в ньому найважливіше?

Пагінація без OFFSETKeyset pagination

Сторінка 45 000 коштує 45 000 сторінок

Одна умова, три різні плани

Що база робить зі сторінкою 45 000

Три пагінатори і що вони питають у бази

Каталог на OFFSET, sitemap на keyset

Де keyset ламається

Порядок без тай-брейкера

NULL у колонці сортування

Різні напрямки сортування

Перевірити лише першу сторінку

Курсор як секрет

Питання зі співбесід