Comments 70
Ибо пагинация будет не по ID, а по столбцу сортировки(что делать, если сортировка по двум полям?). В этом случае как раз удобно применять limit/offset — все сделает сам.
Поэтому удобно из сервера передавать курсор, чтобы клиент не задумывался о природе курсора
в relay pagination есть примеры. Причем там удобно делать пагинацию вперед-назад, с указанием before/after + limit
Во-первых да, OFFSET делает полный запрос и отбрасывает заданное число строк, для задачи SELECT… LIMIT 20 OFFSET 9900000 это и правда неоптимально.
Во-вторых, подход с id (крайне редко переходят с первой страницы на предпоследнюю, и почти никогда – с начала в середине) работает, но требует состояния на клиенте.
Для запросов в очень большие данные с очень большим сдвигом стоит пользоваться хранилищами, предоставляющими для этого специальные возможности. Например, у Elasticsearch для этого есть даже два разных API.
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
А ну, сервер, выбери ка мне 10 случайных записей, пропустив 85000 случайных. Интересная задачка…
Конструкция LIMIT относится не ко всем СУБД. Почему бы это не указать хотя бы в тегах?
Конструкция LIMIT относится не ко всем СУБДК слову, к каким относится?
Я сейчас (по требованию работодателя) использую MS SQL Server, там пишется
select top 10 * fromИспользуем (за редким исключением) только при отладке, оценить, что примерно возвращает запрос / что вообще есть в таблице
OFFSET m ROWS
FETCH NEXT n ROWS ONLY
Почему же редко используете? На мой взгляд как раз логично во всех запросах всегда задавать top или limit, равный максимальному разумному числу строк, которые мы ожидаем получить. Чтобы не случилось, что из-за бага в коде фильтрации, мы случайно попросил у базы 100500000 записей, перегрузили и базу и бекенд при этом
«Баги в коде фильтрации», собственно, отслеживаются «только при отладке (и при разработке, разумеется), оценить, что примерно возвращает запрос». В продакшене необходимо использовать все 100500 записей, иначе результат будет некорректный.
Когда (по мере роста базы) их становится 100500 000 — приходится переделывать слайсинг, но не от балды. Допустим, если есть данные по разным пользователям за разное время —для одного расчёта надо охватить как можно больше пользователей, но можно считать каждый месяц отдельно, для другого — допустимо считать каждого пользователя (в крайнем случае) отдельно, но за максимальный горизонт (год и больше).
Вы заблуждаетесь насчёт
"Прошли те дни, когда не надо было беспокоиться об оптимизации производительности баз данных"
Это как раз сейчас СУБД стали очень умными, а в конце 90-х простой prepare или индекс радикально снижали задумчивость программы
Так при работе с id потребуется сортировка по этому id, т. к. иначе БД не гарантирует порядок отдачи строк, емнип.
А использование сортировки сведет на нет всю оптимизацию. Или я что то не так понял?
Хороший пример решения для частного случая. Если id записей монотонно возрастают (не удаляются), целочисленные (а не guid например). И про сортировку выше уже написали.
Сортировка по суррогатному ключу это какой-то очень специфический случай. По крайней мере в моей практике за два десятилетия это встречалось буквально пару раз.
Чаще всего юзеры имеют возможность выбирать поля для (мульти-)сортировки и налагать сколько угодно фильтров. Так что да, придётся таки сканировать всё и потом возвращать кусочек.
Добавьте тег "MySQL", пожалуйста. Все СУБД устроены по своему, и поэтому полезный совет для одной из них чаще всего окажется вредным для другой.
Для таких вещей есть мемкешы, etc. И не надо городить костыли. Даже для каких-то уникально-специфичных случаев.
Получается, если мы удалили пользователя с ID 60 из 80 000
Это легко решается (хотя смысла в таком “решении" нет, если только DBA не отъявленный перфекционист) — навешивается ON UPDATE CASCADE на ID, а на удаление ставится триггер, который назначает записи с наибольшим ID удаленный ID (в примере — пользователю с ID 80000 назначается новый ID 60, остальные 79998 записей остаются как есть).
а на удаление ставится триггер, который назначает записи с наибольшим ID удаленный IDТак тогда порядок выдачи записей в следующий раз изменится же, и поле с CURRENT_TIMESTAMP обновится, хотя изменения в БД эту запись вообще не затрагивали.
- Кто переходит больше чем на 10 страницу? Никто.
Если это результаты поиска чего-то в гугле — да, почти никто. А если это список банковских транзакций — то "никтов", желающих посмотреть на все страницы, от первой до последней, даже если их там несколько сотен или даже тысяч, может оказаться много (и я один из них).
Или если это журнал событий за какой-то промежуток времени, и нужно найти что-то "странное" по нечёткому критерию (т.е. поиск-фильтры не применить) — для меня вполне рутинный случай, ещё несколько человек знаю кто этим же занимается периодически.
- У кого может быть пагинация на 100000 страниц?
Как минимум в двух вышеприведённых случаях — абсолютно нормальное явление. Это кажется бессмыслленным — листать постранично миллион результатов, но если у вас есть кнопки "+100", "-100", "середина", и т.п., результаты отсортированы по какому-то критерию — то поиск нужной информациии становится интересней, в то же время нечёткости других критериев по прежнему не позволяют применить более "узкие" фильтры.
Есть и ещё один, более прозаичный случай — список доменных имён с сопутствующей информацией, которых десятки а то и сотни тысяч. Нет, не абстрактный список, а принадлежащий хостеру — и вот этот самый чувак ну очень хочет видеть список весь, с количеством страниц и возможностью навигации, причём он реально им пользуется иногда до последней страницы (это видно по журналу), хотя всё доступно в json/xml/tsv в полном объеме, но он упорно лезет в GUI.
Так что, если вам лично никогда не приходилось сталкиваться с чем-то, не значит что другие, кому приходилось, являются сказочными теоретиками.
значит сортировка и фильтрация сделаны неправильно и их возможности не позволяют эффективно работать со списком
Не значит. Невозможно фильтровать по критерию который нечёткий — если (условно) у вас список из тысячи имён, но в нём нужно найти "левые" (типа Подрыв Устоев или Разбор Полётов) — это один из примеров.
Например, те же банковские транзакции и логи эффективно ограничиваются диапазоном дат.
Это хорошо для случая когда транзакций два десятка в неделю, а когда их тысячи в час и больше, ситуация меняется кардинально.
К примеру, вам известно что в интервале от 20:00 до 20:30 была подозрительная транзакция, но вы не знаете критерия подозрительности, при этом есть шанс (наблюдая за всеми параметрами в процессе пролистывания — имя, сумма, адрес, etc) заметить что-то необычное. В этом интервале их было около 2 тыс. Ваши действия?
Вы понимаете что у человека, который постранично просматривает какие-то данные, проблемы возникнут гораздо быстрее чем у базы данных?
Зависит от человека. Для меня не проблема пролистать (и понять, хотя и не запомнить) несколько сотен страниц структурированных и адекватно отформатированных данных, и это совсем не такая уж уникальная способность.
При этом никто не мешает вам и дальше не ограничивать число отображаемых страниц
Вы же чуть выше сказали что это не нужно в принципе и вообще это сказки и так не бывает?
их число очень невелико и проблемой можно пренебречь, она не оказывает существенного влияния
Так пренебрегайте — в ваших собственных проектах. А у других бывают ситуации когда пренебречь совсем нельзя — к примеру, аналитический отдел финансовой группы, где от "пренебрежения" могут пропасть или появиться денежные суммы равные годовым бюджетам некоторых стран, и нужно вручную (ок, глазами) просматривать десятки тысяч страниц, причём не всегда чётко структурированных данных. И нет, вопреки рекламе и бравым статьям ИИ ещё не достаточно развит и его далеко не везде можно применять.
Выше вы написали:
- Кто переходит больше чем на 10 страницу? Никто.
Так всё же "никто" или "это зависит"?
Если же программист дал доступ к таблице с десятками и сотнями миллионов записей с возможностью пагинации...
Да что тут необычного-то? Я хочу знать медианное значение какого-то параметра, в такой многомиллионной таблице — вот вам и offset на середину, даже без всякой пагинации но с использованием того же offset/limit.
К примеру, если у меня таблица покупок за последний год, и там миллион транзакций, остортированные по сумме транзакции — я прыгаю (кнопочкой) в середину таблицы и сразу вижу что как минимум 50% покупателей заплатили не меньше чем определённую сумму.
Или такое использование это тоже "сказочная теория"? Особенно если учесть что никаким другим способом эту задачу вообще не решить — единственный способ узнать медианное значение (как и любой другой перцентиль) — это отсортировать все данные и взять соотвествующий перцентилю элемент, никакие читы тут в принципе невозможны, даже если вы будете иметь индекс по значению — вам всё равно придётся пропустить первые записи (вот где нужен offset) чтобы узнать искомое.
Количество, которое вы называете никак не нагрузит базу данных. Список на миллион доменных имён или транзакций занимает пару десятков мегабайт памяти и легко помещается в кэш, с ним limit и offset прекрасно сработают. В статье упоминается Фейсбук и другие гиганты и я бы предполагал размер таблиц изменяющийся хотя бы десятками гигабайт. Такую таблицу реально не имеет никакого смысла отображать в гуе с полной пагинацией. Это как если бы Гугл позволял посмотреть результаты поиска вплоть до 100млн.
Так что я поддерживаю NoRegrets, на таких больших данных работают фильтры + limit без пагинации. Но бывает реально трудно это объяснить пользователям, которые по старинке хотят "а можно всё посмотреть?"
Совсем вредные советы начались. Как же вы можете говорить что это не нагружает IO, если это делает не просто full scan, а в некоторых случаях полную выборку (включая прогон данных по сети) из таблицы? Этот код впринципе никогда не отработает на по-настоящему большой таблице если пользователь случайно нажмёт кнопку "последняя страница".
Если быть очень дотошным — не надо использовать PHP 5.2 в 2020 году.
Если быть очень дотошным, то ваша "пагинация на стороне PHP" уронит к чертовой матери процесс — memory overflow и так далее.
А если уж мы говорим о "статических HTML-ках" — инвалидация кэша — нет, не слышали?
На мой взгляд, самый простой и оптимальный вариант, ЕСЛИ заранее известны варианты фильтров для построения страниц — кэш чанков айдишников. Да, по крону — и, в базе. Smth like this:
CREATE TABLE `ids_chunks` {
int id, int pageoffset, int limit, text ids_set
}
Метод не будет работать в случае, если ключевое поле (id) не является непрерывным. Допустим, некоторые записи были удалены, как сразу перейти на страницу в середине датасета? Кроме того, в выборке могут присутствовать не все записи (например, только непрочитанные сообщения). Про сортировку уже сказано было.
Получается, что метод можно использовать только при последовательном чтении страниц, начиная с первой, что является очень частным случаем.
Нет. Метод вполне рабочий. Но есть несколько НО:
- Сортировка должна быть по полю id. Если будет по другому полю, то нужно менять условие. Как это сделать с несколькими полями… надо подумать. Но лень. С ходу кажется, что без глюков это сделать нереально.
- Все записи должны быть уникальными в рамках критериев сортировки, иначе можно потерять часть записей из-за того, что они предыдущим LIMIT отсеклись, а в новый уже не будут входить.
- Как вы и сказали, нельзя сделать запрос "дай мне содержимое пятой страницы". Можно только сказать "выдай мне следующую страницу".
P.S. Ну и в статье забыли про необходимость указывать ORDER BY для получения адекватного результата. Но, возможно, имелась в виду, например, MySQL, а не PostgreSQL. Там это будет работать и без ORDER BY.
Чудесно, и ни слова о сортировках. Пользователям так-то почти никогда не нужны выборки по идентификатору. А часто нужны выборки даже по неуникальным полям. Рекомендую почитать эту статью о паджинации, которая не оторвана от реальности: https://habr.com/ru/post/413133/
Лучшее до чего дошёл я — никакого виртуального скроллинга или постраничной навигации для больших таблиц.
В GUI grid — либо предпросмотр результатов фильтрации с показом первых N записей из курсора и SQL с LIMIT N+1 либо кнопка «скачать все» работающая через открытый streaming cursor на backend. Моим пользователям было достаточно, к счастью.
1) сортировка, например по полю createdate, как отработает с fetch\limit и where date > x
2) что делать тем у кого id это guid?
Нормально при пропусках как раз.
Запрос с id > 0 LIMIT 10 вернёт 101-110, следующий запрос будет id > 110 LIMIT 10. Как и с классической OFFSET LIMIT пагинацией рулит страницами клиент.
Не стоит пользоваться OFFSET и LIMIT в запросах с разбиением на страницы