Почему не нужно использовать OFFSET и LIMIT для нумерации страниц

Оригинальная статья: Ivo Pereira – Why You Shouldn’t Use OFFSET and LIMIT For Your Pagination

Прошли те времена, когда нам не нужно было беспокоиться об оптимизации производительности базы данных.

С опережением времени и каждого нового предпринимателя, желающего создать очередной Facebook, в сочетании с мышлением, позволяющим собирать все возможные данные для обеспечения более точных прогнозов машинного обучения, мы, как разработчики, должны создавать наше API более оптимизирован, чем когда-либо, чтобы обеспечить надежные и эффективные конечные точки (URL), которые должны иметь возможность без проблем обрабатывать огромные объемы данных.

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

SELECT * FROM table_name LIMIT 10 OFFSET 40

Верно?

Но если вы использовали подобные запросы для отображения страниц, извините, но вы поступали не верно.

Вы не согласны со мной? Обратите на это внимание.

Я не знаю ни одного бэкэнд-разработчика, которому никогда не приходилось иметь дело с OFFSET и LIMIT для нумерации страниц. Просто потому что такой подход прост и понятен.

Но если вы хотите создавать надежные и эффективные системы с нуля, вам лучше рассмотреть недостатки такого подхода.

В этой статье я расскажу о том, каковы проблемы в подобном подходе и как создавать качественные нумерации страниц.

Что не так с OFFSET и LIMIT?

Как мы кратко рассмотрели в предыдущих параграфах, OFFSET и LIMIT отлично работают для проектов с небольшим количеством данных.

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

Для этого база данных должна будет выполнять неэффективное полное сканирование таблицы каждый раз, когда вы запрашиваете нумерацию страниц (между тем могут происходить вставки и удаления, и нам так же нужно это учитывать!).

Что такое полное сканирование таблицы? Полное сканирование таблицы (также известное как последовательное сканирование) – это сканирование, выполненное в базе данных, при котором каждая строка в таблице последовательно читается, а встречающиеся столбцы затем проверяются на соответствие условиям. Этот тип сканирования, как известно, является самым медленным из-за большого количества операций чтения-ввода с диска, состоящих из нескольких запросов, а также дорогостоящих операций передачи данных с диска в память.

Это означает, что если у вас есть 100 000 000 пользователей, и вы запрашиваете OFFSET 50 000 000, ему нужно будет извлечь все эти записи (которые даже не понадобятся!), Поместить их в память и только после этого получить 20 результатов, указанных в LIMIT.

Итак, чтобы показать такую нумерацию страниц на сайте:

от 50 000 до 50 020 из 100 000 000

Сначала нужно получить 50 000 строк. Видите, насколько это неэффективно?

Если вы мне не верите, взгляните на этот fiddle, который я создал. На левой панели у вас есть базовая схема, которая вставит 100 000 строк для нашего теста, а справа – проблемный запрос и наше решение. Просто нажмите Run вверху и сравните время выполнения каждого. # 1 (проблемный запрос) занимает по крайней мере в 30 раз больше времени.

И это становится еще хуже с большим количеством данных. Посмотрите мое доказательство концепции с 10M строк.

TLDR; Чем больше OFFSET, тем больше времени будет занимать запрос.

Что нужно использовать

Вот более правильный запрос:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Такой запрос называется – Cursor based pagination.

Вместо того, чтобы хранить текущие значения OFFSET и LIMIT локально и передавать их при каждом запросе, вы должны хранить последний полученный первичный ключ (обычно ID) и LIMIT.

Почему? Потому что, явно передав последнюю прочитанную строку, вы точно указываете своей БД, с чего начать поиск, основываясь на индексированном ключе, и вам не придется получать какие-либо строки за пределами этого диапазона.

Возьмите в пример следующее сравнение:

mysql> SELECT * FROM table_name LIMIT 10 OFFSET 8000001;
[...]
10 rows in set (12.80 sec)

И сравните с оптимизированной версии:

mysql> SELECT * FROM table_name WHERE id > 8000000 LIMIT 10;
[...]
10 rows in set (0.01 sec)

Были получены точно такие же записи, но первый запрос занял 12,80 с, а второй – 0,01 с. Можете ли вы осознать разницу?

Предостережения

Запустив этот PoC, я понял, что невозможно реализовать эффективную нумерацию страниц для таблицы, в которой не будет первичного ключа, как в ситуации, когда у вас будет таблица отношений «многие ко многим».

В таких случаях я могу просто посоветовать вам использовать традиционный подход OFFSET / LIMIT, однако вы должны понимать что это может привести к более медленным запросам. Я бы посоветовал всегда использовать первичный ключ с автоинкрементом в таблицах, которые вы бы хотели разбить на страницы, даже если это делается только для нумерации страниц.

Заключение

Главное, что нужно сделать, это всегда проверять, как выполняются ваши запросы, будь то 1k строк или 1M. Масштабируемость чрезвычайно важна, и при правильном ее внедрении с самого начала, несомненно, можно избежать многих проблем в будущем.