Пагинация базы данных: почему LIMIT/OFFSET тормозит на больших таблицах

Пагинация через LIMIT/OFFSET — самый простой способ листать данные, но с ростом таблицы он превращается в узкое место: чем дальше страница, тем медленнее ответ базы. Причина в том, что база физически перебирает и отбрасывает все строки до нужной позиции, и на десятой тысяче страниц это превращается в серьёзную нагрузку.

Что вообще такое LIMIT/OFFSET и почему им все пользуются?

Это два ключевых слова в SQL-запросе, которые позволяют получить не всю таблицу целиком, а нужный кусок: LIMIT говорит, сколько строк вернуть, а OFFSET — сколько строк пропустить перед этим. Например, чтобы показать вторую страницу товаров по 20 штук, пишут OFFSET 20 LIMIT 20. Это интуитивно понятно, легко реализуется на любом языке программирования и одинаково работает почти во всех реляционных базах данных, поэтому конструкция стала стандартом де-факто для листалок, каталогов и лент.

Почему тогда с этим способом вообще есть проблемы?

Проблема в том, как база данных выполняет OFFSET внутри себя. Она не умеет «телепортироваться» сразу к нужной строке — вместо этого она читает все строки по порядку с самого начала выборки и просто отбрасывает те, что попадают в диапазон OFFSET, пока не дойдёт до нужного места. Если вы просите пропустить первые сто строк, это не страшно. Но если пользователь долистал до сотой тысячи строки, база вынуждена прочитать и выбросить сто тысяч записей, прежде чем отдать нужные двадцать.

Из-за этого время ответа растёт линейно с глубиной страницы: первые страницы каталога открываются мгновенно, а переход в конец списка может занимать секунды или даже вызывать таймаут. При этом каждый такой запрос дополнительно нагружает диск и процессор сервера базы данных, а если листать одновременно пытаются много пользователей, эффект накладывается и замедляет всю систему.

Как понять, что именно пагинация — источник тормозов?

Первый признак — жалобы именно на дальние страницы: первая открывается быстро, а условная тридцатая или сотая заметно медленнее. Второй способ — посмотреть план выполнения запроса командой EXPLAIN: если в нём видно, что база сканирует и обрабатывает намного больше строк, чем реально возвращает клиенту, это прямое указание на проблему с OFFSET. Полезно также мониторить время выполнения именно постраничных запросов в логах медленных запросов — они начинают выделяться на фоне остальных по мере роста номера страницы.

Что делать вместо LIMIT/OFFSET?

Самая распространённая альтернатива — так называемая курсорная пагинация, или keyset-пагинация. Вместо того чтобы говорить базе «пропусти столько-то строк», ей передают значение последнего элемента с предыдущей страницы — например, идентификатор или дату последней показанной записи. Следующий запрос звучит не «пропусти N строк», а «дай мне записи, которые идут после этой конкретной». Если на нужном поле есть индекс, база сразу находит стартовую точку и читает только те строки, которые действительно нужно показать, без перебора всего, что было раньше.

Такой подход требует немного изменить логику интерфейса: вместо номеров страниц пользователю показывают кнопки «вперёд» и «назад» либо реализуют бесконечную ленту с подгрузкой. Зато скорость ответа перестаёт зависеть от того, насколько глубоко пользователь пролистал список — сотая и десятитысячная «страница» открываются одинаково быстро.

А если номера страниц нужны обязательно, например для SEO или каталога товаров?

В этом случае полностью отказаться от OFFSET не всегда получится, но нагрузку можно снизить другими способами. Один из вариантов — ограничить глубину пагинации: показывать не бесконечное число страниц, а разумный максимум, дальше предлагая уточнить поиск или фильтры. Другой вариант — заранее просчитать и закэшировать список идентификаторов записей для каждой страницы, чтобы не выполнять тяжёлый запрос с OFFSET каждый раз заново, а просто доставать нужные строки по готовым идентификаторам, что делается намного быстрее.

Также помогает грамотное индексирование поля, по которому идёт сортировка и фильтрация: без подходящего индекса база вынуждена сортировать всю таблицу целиком при каждом запросе, что усугубляет проблему глубокой пагинации. Если индекс уже есть, но страницы всё равно тормозят, стоит проверить, действительно ли база его использует — иногда из-за неудачно составленного запроса оптимизатор выбирает более медленный путь.

Как это связано с общей производительностью сайта под нагрузкой?

Пагинация редко бывает единственным источником проблем, но она хорошо иллюстрирует общий принцип: то, что удобно писать в коде, не всегда удобно выполнять базе данных. Один медленный, но популярный запрос — например, переход в конец большого каталога или архива — способен занять диск и процессор настолько, что начинают тормозить и все остальные, казалось бы, не связанные с ним операции. Именно поэтому на проекты с растущей базой данных стоит закладывать запас по производительности сервера, а не оптимизировать только тогда, когда пользователи уже начали жаловаться.

Интересный факт: сама конструкция OFFSET появилась в SQL задолго до того, как базы данных стали хранить миллионы строк, и изначально задумывалась для небольших выборок вроде отчётов на несколько сотен записей — поэтому её «наивная» реализация до сих пор встречается практически во всех популярных СУБД и одинаково упирается в один и тот же потолок производительности.

Можно ли решить эту проблему без глубокого погружения в SQL самостоятельно?

Да, если не хочется вручную разбираться с индексами, планами запросов и настройками сервера, часть этой работы можно делегировать. Платформа «Клауд АйСи» предлагает готовые облачные серверы и базы данных с возможностью гибко наращивать ресурсы под растущую нагрузку, а также услуги по сопровождению и оптимизации баз данных, где специалисты помогают найти узкие места вроде медленной пагинации и подобрать решение под конкретный проект — от смены схемы запросов до расширения мощности сервера.

С чего начать, если пагинация уже тормозит прямо сейчас?

Логичный первый шаг — посмотреть через EXPLAIN, что именно происходит с самыми медленными постраничными запросами, и убедиться, что для сортировки и фильтрации есть подходящий индекс. Дальше стоит оценить, реально ли пользователям нужны номера страниц в глубину, или можно перейти на курсорную пагинацию с кнопками «вперёд» и «назад». Даже частичный переход на keyset-подход для самых нагруженных разделов — каталога, ленты новостей, списка заказов — обычно даёт заметный прирост скорости без переписывания всего проекта.