company_banner

Не стоит пользоваться OFFSET и LIMIT в запросах с разбиением на страницы

Автор оригинала: Ivo Pereira
  • Перевод
Прошли те дни, когда не надо было беспокоиться об оптимизации производительности баз данных. Время не стоит на месте. Каждый новый бизнесмен из сферы высоких технологий хочет создать очередной Facebook, стремясь при этом собирать все данные, до которых может дотянуться. Эти данные нужны бизнесу для более качественного обучения моделей, которые помогают зарабатывать. В таких условиях программистам необходимо создавать такие API, которые позволяют быстро и надёжно работать с огромными объёмами информации.



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

SELECT * FROM table_name LIMIT 10 OFFSET 40

Так оно и есть?

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

Хотите мне возразить? Можете не тратить время. Slack, Shopify и Mixmax уже применяют приёмы, о которых я хочу сегодня рассказать.

Назовите хотя бы одного разработчика бэкендов, который никогда не пользовался OFFSET и LIMIT для выполнения запросов с разбиением на страницы. В MVP (Minimum Viable Product, минимальный жизнеспособный продукт) и в проектах, где используются небольшие объёмы данных, этот подход вполне применим. Он, так сказать, «просто работает».

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

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

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


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

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

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

Что такое «полное сканирование таблицы» (или «последовательный просмотр таблицы», Sequential Scan)? Это — операция, в ходе которой СУБД последовательно считывает каждую строку таблицы, то есть — содержащиеся в ней данные, и проверяет их на соответствие заданному условию. Известно, что этот тип сканирования таблиц является самым медленным. Дело в том, что при его выполнении выполняется много операций ввода/вывода, задействующих дисковую подсистему сервера. Ситуацию ухудшают задержки, сопутствующие работе с данными, хранящимися на дисках, и то, что передача данных с диска в память — это ресурсоёмкая операция.

Например, у вас есть записи о 100000000 пользователях, и вы выполняете запрос с конструкцией OFFSET 50000000. Это значит, что СУБД придётся загрузить все эти записи (а ведь они нам даже не нужны!), поместить их в память, а уже после этого взять, предположим, 20 результатов, о которых сообщено в LIMIT.

Скажем, это может выглядеть так: «выбрать строки от 50000 до 50020 из 100000». То есть, системе для выполнения запроса нужно будет сначала загрузить 50000 строк. Видите, как много ненужной работы ей придётся выполнить?

Если не верите — взгляните на пример, который я создал, пользуясь возможностями db-fiddle.com


Пример на db-fiddle.com

Там, слева, в поле Schema SQL, имеется код, выполняющий вставку в базу данных 100000 строк, а справа, в поле Query SQL, показаны два запроса. Первый, медленный, выглядит так:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

А второй, который представляет собой эффективное решение той же задачи, так:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Для того чтобы выполнить эти запросы, достаточно нажать на кнопку Run в верхней части страницы. Сделав это, сравним сведения о времени выполнения запросов. Оказывается, что на выполнение неэффективного запроса уходит, как минимум, в 30 раз больше времени, чем на выполнение второго (от запуска к запуску это время различается, например, система может сообщить о том, что на выполнение первого запроса ушло 37 мс, а на выполнение второго — 1 мс).

А если данных будет больше, то всё будет выглядеть ещё хуже (для того чтобы в этом убедиться — взгляните на мой пример с 10 миллионами строк).

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

Учитывайте, что чем больше значение OFFSET — тем дольше будет выполняться запрос.

Что стоит использовать вместо комбинации OFFSET и LIMIT?


Вместо комбинации OFFSET и LIMIT стоит использовать конструкцию, построенную по такой схеме:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Это — выполнение запроса с разбиением на страницы, основанное на курсоре (Cursor based pagination).

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

Почему? Дело в том, что в явном виде указывая идентификатор последней прочитанной строки, вы сообщаете своей СУБД о том, где ей нужно начинать поиск нужных данных. Причём, поиск, благодаря использованию ключа, будет осуществляться эффективно, системе не придётся отвлекаться на строки, находящиеся за пределами указанного диапазона.

Давайте взглянем на следующее сравнение производительности различных запросов. Вот неэффективный запрос.


Медленный запрос

А вот — оптимизированная версия этого запроса.


Быстрый запрос

Оба запроса возвращают в точности один и тот же объём данных. Но на выполнение первого уходит 12,80 секунд, а на второй — 0,01 секунда. Чувствуете разницу?

Возможные проблемы


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

Естественно, конструируя запросы, нужно учитывать особенности архитектуры таблиц, и выбирать те механизмы, которые наилучшим образом покажут себя на имеющихся таблицах. Например, если нужно работать в запросах с большими объёмами связанных данных, вам может показаться интересной эта статья.

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

Если вам интересна эта тема — вот, вот и вот — несколько полезных материалов.

Итоги


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

Как вы анализируете и оптимизируете запросы к базам данных?

RUVDS.com
RUVDS – хостинг VDS/VPS серверов

Комментарии 69

    +13
    В случае, когда нужно сортировать данные, курсоры придется дорабатывать
    Ибо пагинация будет не по ID, а по столбцу сортировки(что делать, если сортировка по двум полям?). В этом случае как раз удобно применять limit/offset — все сделает сам.
    Поэтому удобно из сервера передавать курсор, чтобы клиент не задумывался о природе курсора
    в relay pagination есть примеры. Причем там удобно делать пагинацию вперед-назад, с указанием before/after + limit
    • НЛО прилетело и опубликовало эту надпись здесь
        0
        У меня был случай, когда в БД лежат десятки миллионов строк, в таблице отображаются 20 или 50.
        Во-первых да, OFFSET делает полный запрос и отбрасывает заданное число строк, для задачи SELECT… LIMIT 20 OFFSET 9900000 это и правда неоптимально.
        Во-вторых, подход с id (крайне редко переходят с первой страницы на предпоследнюю, и почти никогда – с начала в середине) работает, но требует состояния на клиенте.
      +10
      Это очень, очень неуниверсальное решение. Limit и offset, в отличие от него, работают с любыми сортировками.

      Для запросов в очень большие данные с очень большим сдвигом стоит пользоваться хранилищами, предоставляющими для этого специальные возможности. Например, у Elasticsearch для этого есть даже два разных API.
        0
        Всем свое применение. Но согласен такой вариант подойдет только в малом кол-ве случаев
          0
          Если задача будет работать со скроллом в Elasticsearch, то тем более будет работать со скроллом в SQL. LIMIT/OFFSET в Elasticsearch так же не рекомендуются, у них природная проблема. Добавлять Elasticsearch в стэк из-за скроллинга нет смысла.
          +14
          SELECT *
          FROM `docs`
          LIMIT 10 OFFSET 85000;

          А ну, сервер, выбери ка мне 10 случайных записей, пропустив 85000 случайных. Интересная задачка…

          Конструкция LIMIT относится не ко всем СУБД. Почему бы это не указать хотя бы в тегах?
            +2
            Выборка 10 случайных записей после пропуска 85000 случайных записей полностью эквивалентна выборке первых 10 случайных записей. Тривиальная задачка…
              +1
              При условии, что записей не меньше, чем 85010 :)
                0
                Не обязательно: «пропустив 85000 случайных» имеет, как минимум, две трактовки. :-)
                  0
                  А кто сказал, что пропуск именно 8500 строк, а не любое число строк, но чтобы у последней был номер 8500. Например, удаления строк, изменение ручное айди (и такое встречалось). Это все очень и очень индивидуально
              0
              Конструкция LIMIT относится не ко всем СУБД
              К слову, к каким относится?
              Я сейчас (по требованию работодателя) использую MS SQL Server, там пишется
              select top 10 * from

              Используем (за редким исключением) только при отладке, оценить, что примерно возвращает запрос / что вообще есть в таблице
                0
                К PostgreSQL. Впрочем, в SQL Server есть аналогичная:
                OFFSET m ROWS
                FETCH NEXT n ROWS ONLY
                  0

                  Почему же редко используете? На мой взгляд как раз логично во всех запросах всегда задавать top или limit, равный максимальному разумному числу строк, которые мы ожидаем получить. Чтобы не случилось, что из-за бага в коде фильтрации, мы случайно попросил у базы 100500000 записей, перегрузили и базу и бекенд при этом

                    0
                    Специфика проекта такая, что надо читать редко, но помногу. И не для отображения (как правило), а для вычислений и записи обратно в базу.
                    «Баги в коде фильтрации», собственно, отслеживаются «только при отладке (и при разработке, разумеется), оценить, что примерно возвращает запрос». В продакшене необходимо использовать все 100500 записей, иначе результат будет некорректный.
                    Когда (по мере роста базы) их становится 100500 000 — приходится переделывать слайсинг, но не от балды. Допустим, если есть данные по разным пользователям за разное время —для одного расчёта надо охватить как можно больше пользователей, но можно считать каждый месяц отдельно, для другого — допустимо считать каждого пользователя (в крайнем случае) отдельно, но за максимальный горизонт (год и больше).
                  0
                  Банально — задача выгрузить все данные с апи, которое возвращает максимум по 100 результатов. Shopify, который здесь упомянули, как раз по этой причине и отказался от такой пагинации.
                  +1

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


                  Это как раз сейчас СУБД стали очень умными, а в конце 90-х простой prepare или индекс радикально снижали задумчивость программы

                    +2
                    И с тех пор почти ничего не изменилось. Жёсткие разборы по прежнему делают Oracle задумчивым (CURSOR_SHARING не предлагать). А фуллскан по прежнему выполняется медленно (и подходящий индекс часто помогает). Объёмы данных вот стали больше, это да.
                    +4

                    Так при работе с id потребуется сортировка по этому id, т. к. иначе БД не гарантирует порядок отдачи строк, емнип.
                    А использование сортировки сведет на нет всю оптимизацию. Или я что то не так понял?

                    • НЛО прилетело и опубликовало эту надпись здесь
                        –1

                        Насколько я знаю PgSQL — даже при наличии индекса он не гарантирует порядок при отдаче.

                        • НЛО прилетело и опубликовало эту надпись здесь
                      +3

                      Тема стара как мир — keyset pagination. Оригинал. Перевод.

                        +3

                        Хороший пример решения для частного случая. Если id записей монотонно возрастают (не удаляются), целочисленные (а не guid например). И про сортировку выше уже написали.

                          0
                          Была уже похожая статья в 2016-м, только способов там рассматривается больше, и плюсы с минусами рассматриваются глубже — habr.com/ru/post/301044
                            +3
                            И все это накрывается медным тазом как только нужно выбирать хоть по какому-то условию, например, по категории.
                              +1
                              Фильтры не играют роли. Играет роль сортировка. Если используется по времени, то можно отсекать результаты по autoinc ключу. Конечно, только там, где он есть.
                              +1

                              Сортировка по суррогатному ключу это какой-то очень специфический случай. По крайней мере в моей практике за два десятилетия это встречалось буквально пару раз.


                              Чаще всего юзеры имеют возможность выбирать поля для (мульти-)сортировки и налагать сколько угодно фильтров. Так что да, придётся таки сканировать всё и потом возвращать кусочек.


                              Добавьте тег "MySQL", пожалуйста. Все СУБД устроены по своему, и поэтому полезный совет для одной из них чаще всего окажется вредным для другой.

                                +2
                                Хлеще фигни про оптимизацию выборки я не слышал. Опустим даже кейс с фильтрами и сортировками. Получается, если мы удалили пользователя с ID 60 из 80 000 — нам надо снова на всю таблицу ребилдить уникальный ключ среза? М-да, вот это я понимаю «оптимизация».
                                Для таких вещей есть мемкешы, etc. И не надо городить костыли. Даже для каких-то уникально-специфичных случаев.
                                  +1
                                  Получается, если мы удалили пользователя с ID 60 из 80 000

                                  Это легко решается (хотя смысла в таком “решении" нет, если только DBA не отъявленный перфекционист) — навешивается ON UPDATE CASCADE на ID, а на удаление ставится триггер, который назначает записи с наибольшим ID удаленный ID (в примере — пользователю с ID 80000 назначается новый ID 60, остальные 79998 записей остаются как есть).

                                    +1
                                    а на удаление ставится триггер, который назначает записи с наибольшим ID удаленный ID
                                    Так тогда порядок выдачи записей в следующий раз изменится же, и поле с CURRENT_TIMESTAMP обновится, хотя изменения в БД эту запись вообще не затрагивали.
                                      0
                                      … и ловит database exception с текстом, что в соседней таблице есть foreign key на этот самый ID 80000
                                        0

                                        Для этого и существует ON UPDATE CASCADE. Поэтому ничего ловить не будет.

                                        0
                                        а в чем смысла? пользователь с id был в конце списка (и должен там оставаться), и теперь вдруг стал с id 60, фактически в начале списка
                                      +3
                                      А теперь давайте спустимся на землю.
                                      1. Кто переходит больше чем на 10 страницу? Никто.
                                      2. У кого может быть пагинация на 100000 страниц? Да ни у кого. Это бессмысленно делать в ГУИ. Потому что никому в голову не придет ходить по сотням страниц в поисках чего-то. Могут понадобится 10, ну может 100 страниц, не более. Для того чтобы было не больше 100 страниц предлагается фильтрация по различным критериям и поиск и отображаются первые 10 страниц результатов поиска.
                                      3. Результаты поиска из п.2 часто грузятся полностью в какой-нибудь редис или мемкеш и никакие офсеты не используются.
                                      Из этого я делаю вывод, что автор оригинала просто сказочный теоретик, решающий надуманную проблему.
                                        0
                                        Перебор всех данных может быть нужен и без GUI. Например, банально, заполнение нового поля в таблице. Ветки форумов тоже только так и работают без фильтраций и сортировок. Вовсе не теоретик, скорее, боянист.
                                          0
                                          Про заполнение нового поля в таблице не понял. Киньте ссылку плз или объясните в двух словах.
                                            0
                                            Раньше делали JOIN другой таблицы для данных, теперь добавили поле для данных оттуда и надо его заполнить. Или откуда-то еще подтянуть данные. Либо заполнить каким-нибудь дефолтным значением. Всё разом нельзя из-за лока, надо кусками пробежать и заполнить.
                                          +2
                                          1. Кто переходит больше чем на 10 страницу? Никто.

                                          Если это результаты поиска чего-то в гугле — да, почти никто. А если это список банковских транзакций — то "никтов", желающих посмотреть на все страницы, от первой до последней, даже если их там несколько сотен или даже тысяч, может оказаться много (и я один из них).


                                          Или если это журнал событий за какой-то промежуток времени, и нужно найти что-то "странное" по нечёткому критерию (т.е. поиск-фильтры не применить) — для меня вполне рутинный случай, ещё несколько человек знаю кто этим же занимается периодически.


                                          1. У кого может быть пагинация на 100000 страниц?

                                          Как минимум в двух вышеприведённых случаях — абсолютно нормальное явление. Это кажется бессмыслленным — листать постранично миллион результатов, но если у вас есть кнопки "+100", "-100", "середина", и т.п., результаты отсортированы по какому-то критерию — то поиск нужной информациии становится интересней, в то же время нечёткости других критериев по прежнему не позволяют применить более "узкие" фильтры.


                                          Есть и ещё один, более прозаичный случай — список доменных имён с сопутствующей информацией, которых десятки а то и сотни тысяч. Нет, не абстрактный список, а принадлежащий хостеру — и вот этот самый чувак ну очень хочет видеть список весь, с количеством страниц и возможностью навигации, причём он реально им пользуется иногда до последней страницы (это видно по журналу), хотя всё доступно в json/xml/tsv в полном объеме, но он упорно лезет в GUI.


                                          Так что, если вам лично никогда не приходилось сталкиваться с чем-то, не значит что другие, кому приходилось, являются сказочными теоретиками.

                                            0
                                            Если у вас много страниц и есть необходимость ходить по ним, значит сортировка и фильтрация сделаны неправильно и их возможности не позволяют эффективно работать со списком. Например, те же банковские транзакции и логи эффективно ограничиваются диапазоном дат. Вы понимаете что у человека, который постранично просматривает какие-то данные, проблемы возникнут гораздо быстрее чем у базы данных?
                                            При этом никто не мешает вам и дальше не ограничивать число отображаемых страниц, т.к. если все сделано правильно и такие, кому это все-же надо, находятся, их число очень невелико и проблемой можно пренебречь, она не оказывает существенного влияния.
                                              +1
                                              значит сортировка и фильтрация сделаны неправильно и их возможности не позволяют эффективно работать со списком

                                              Не значит. Невозможно фильтровать по критерию который нечёткий — если (условно) у вас список из тысячи имён, но в нём нужно найти "левые" (типа Подрыв Устоев или Разбор Полётов) — это один из примеров.


                                              Например, те же банковские транзакции и логи эффективно ограничиваются диапазоном дат.

                                              Это хорошо для случая когда транзакций два десятка в неделю, а когда их тысячи в час и больше, ситуация меняется кардинально.


                                              К примеру, вам известно что в интервале от 20:00 до 20:30 была подозрительная транзакция, но вы не знаете критерия подозрительности, при этом есть шанс (наблюдая за всеми параметрами в процессе пролистывания — имя, сумма, адрес, etc) заметить что-то необычное. В этом интервале их было около 2 тыс. Ваши действия?


                                              Вы понимаете что у человека, который постранично просматривает какие-то данные, проблемы возникнут гораздо быстрее чем у базы данных?

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


                                              При этом никто не мешает вам и дальше не ограничивать число отображаемых страниц

                                              Вы же чуть выше сказали что это не нужно в принципе и вообще это сказки и так не бывает?


                                              их число очень невелико и проблемой можно пренебречь, она не оказывает существенного влияния

                                              Так пренебрегайте — в ваших собственных проектах. А у других бывают ситуации когда пренебречь совсем нельзя — к примеру, аналитический отдел финансовой группы, где от "пренебрежения" могут пропасть или появиться денежные суммы равные годовым бюджетам некоторых стран, и нужно вручную (ок, глазами) просматривать десятки тысяч страниц, причём не всегда чётко структурированных данных. И нет, вопреки рекламе и бравым статьям ИИ ещё не достаточно развит и его далеко не везде можно применять.

                                                0
                                                К примеру, вам известно что в интервале от 20:00 до 20:30 была подозрительная транзакция...
                                                Ну вы же сами ответили на свой вопрос — вам не нужна вся таблица, вам нужен только этот диапазон — полчаса. Сколько там записей в этих полчаса? Миллион? 100 Миллионов? Но на самом деле нет, не миллион. Кто умрет первым, человек, который будет переходить по страницам и искать что-то среди миллионово записей или БД, для которой взять оффсет по миллиону записей не проблема? Поэтому когда у вас возникнет такая ситуация «была подозрительная транзакция», вам придется уточнить в какое время она была, чтобы в этот диапазон попали ну хотя бы не больше нескольких тысяч транзакций (да, те самые 2 тысячи). Иначе бобик сдохнет.
                                                Для меня не проблема пролистать (и понять, хотя и не запомнить) несколько сотен страниц
                                                Вот я о том же. 1000 страниц по 100 элементов — это 100 000 элементов. Для БД не проблема работать с такими выборками, в т.ч. с офсетами. Если же программист дал доступ к таблице с десятками и сотнями миллионов записей с возможностью пагинации и перехода на 100000 страницу и дальше и у да!, у него появляются проблемы с пагинацией, то он уже не сказочный теоретик, это уже по-другому называется. Из двух зол я выбрал меньшее и назвал его теоретиком ;)
                                                  0

                                                  Выше вы написали:


                                                  1. Кто переходит больше чем на 10 страницу? Никто.

                                                  Так всё же "никто" или "это зависит"?


                                                  Если же программист дал доступ к таблице с десятками и сотнями миллионов записей с возможностью пагинации...

                                                  Да что тут необычного-то? Я хочу знать медианное значение какого-то параметра, в такой многомиллионной таблице — вот вам и offset на середину, даже без всякой пагинации но с использованием того же offset/limit.


                                                  К примеру, если у меня таблица покупок за последний год, и там миллион транзакций, остортированные по сумме транзакции — я прыгаю (кнопочкой) в середину таблицы и сразу вижу что как минимум 50% покупателей заплатили не меньше чем определённую сумму.


                                                  Или такое использование это тоже "сказочная теория"? Особенно если учесть что никаким другим способом эту задачу вообще не решить — единственный способ узнать медианное значение (как и любой другой перцентиль) — это отсортировать все данные и взять соотвествующий перцентилю элемент, никакие читы тут в принципе невозможны, даже если вы будете иметь индекс по значению — вам всё равно придётся пропустить первые записи (вот где нужен offset) чтобы узнать искомое.

                                                    0
                                                    У нас с вами разное понимание того, как должно работать гуи. Пример перехода на середину таблицы для определения медианы для меня выглядит дикостью. Все такие вещи должны отображаться в какой-нибудь аналитике. Как и пример того, что поиск возлагается на человека, который глазами грепает «десятки тысяч страниц, причём не всегда чётко структурированных данных» в таблице, в которой «могут появиться денежные суммы равные годовым бюджетам некоторых стран».
                                                    Ну что же, кесарю кесарево, каждый остается при своем мнении )

                                                    Так что, если вам лично никогда не приходилось сталкиваться с чем-то, не значит что другие, кому приходилось, являются сказочными теоретиками.
                                                    В конечном итоге я соглашусь с вами. Может быть кто-то на практике так и делает и сталкивается с проблемой офсетов. «Каждый сам кузнец своего несчастья» (с) )
                                              0

                                              Количество, которое вы называете никак не нагрузит базу данных. Список на миллион доменных имён или транзакций занимает пару десятков мегабайт памяти и легко помещается в кэш, с ним limit и offset прекрасно сработают. В статье упоминается Фейсбук и другие гиганты и я бы предполагал размер таблиц изменяющийся хотя бы десятками гигабайт. Такую таблицу реально не имеет никакого смысла отображать в гуе с полной пагинацией. Это как если бы Гугл позволял посмотреть результаты поиска вплоть до 100млн.
                                              Так что я поддерживаю NoRegrets, на таких больших данных работают фильтры + limit без пагинации. Но бывает реально трудно это объяснить пользователям, которые по старинке хотят "а можно всё посмотреть?"

                                            +3
                                            Глупость полнейшая.

                                            1. Сдвиг по значению поля не равен сдвигу по порядковому номеру записи в выборке. Приведённые в примерах запросы делают сдвиг в разные места и поэтому возвращают разные результаты.

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

                                            Честно скажу, поленился пойти и посмотреть, что там написано по ссылкам, на которые автор ссылается. Уверен, что автор либо не понял написанное там, либо что-то пропустил и нам не пересказал.
                                              0

                                              Сортировка по монотонно возрастающему id очень часто нужна, и прямая, и обратная.

                                              –9

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


                                              <?php
                                              $result = mysql_query("SELECT id, name, еще_что_то FROM big_table");
                                              while ($row = mysql_fetch_assoc($result)) {
                                              if (($row['id']>1)and($row['id']<100)) {echo "$row['name'], $row['еще_что_то']<br>";}
                                              }
                                              ?>

                                              Да, при большой таблице каждый подобный запрос будет потреблять память, которая нынче дешевая. Но не будет жрать ни CPU ни IO.


                                              Ну а вообще, на мой взгляд САМЫЙ оптимальный и простой вариант без установки всяких мемкешей — просто написать один скрипт на сервере с запуском по крону, который будет генерить статические HTML-ки для каждой страницы. Это быстро, дешево, и сердито.

                                                +5

                                                Совсем вредные советы начались. Как же вы можете говорить что это не нагружает IO, если это делает не просто full scan, а в некоторых случаях полную выборку (включая прогон данных по сети) из таблицы? Этот код впринципе никогда не отработает на по-настоящему большой таблице если пользователь случайно нажмёт кнопку "последняя страница".

                                                  0

                                                  Если быть очень дотошным — не надо использовать PHP 5.2 в 2020 году.


                                                  Если быть очень дотошным, то ваша "пагинация на стороне PHP" уронит к чертовой матери процесс — memory overflow и так далее.


                                                  А если уж мы говорим о "статических HTML-ках" — инвалидация кэша — нет, не слышали?

                                                    0

                                                    На мой взгляд, самый простой и оптимальный вариант, ЕСЛИ заранее известны варианты фильтров для построения страниц — кэш чанков айдишников. Да, по крону — и, в базе. Smth like this:


                                                    CREATE TABLE `ids_chunks` {
                                                    int id, int pageoffset, int limit, text ids_set
                                                    }
                                                    0

                                                    Метод не будет работать в случае, если ключевое поле (id) не является непрерывным. Допустим, некоторые записи были удалены, как сразу перейти на страницу в середине датасета? Кроме того, в выборке могут присутствовать не все записи (например, только непрочитанные сообщения). Про сортировку уже сказано было.
                                                    Получается, что метод можно использовать только при последовательном чтении страниц, начиная с первой, что является очень частным случаем.

                                                      +1

                                                      Нет. Метод вполне рабочий. Но есть несколько НО:


                                                      1. Сортировка должна быть по полю id. Если будет по другому полю, то нужно менять условие. Как это сделать с несколькими полями… надо подумать. Но лень. С ходу кажется, что без глюков это сделать нереально.
                                                      2. Все записи должны быть уникальными в рамках критериев сортировки, иначе можно потерять часть записей из-за того, что они предыдущим LIMIT отсеклись, а в новый уже не будут входить.
                                                      3. Как вы и сказали, нельзя сделать запрос "дай мне содержимое пятой страницы". Можно только сказать "выдай мне следующую страницу".

                                                      P.S. Ну и в статье забыли про необходимость указывать ORDER BY для получения адекватного результата. Но, возможно, имелась в виду, например, MySQL, а не PostgreSQL. Там это будет работать и без ORDER BY.

                                                      0
                                                      В статье забыли указать о том, что если id генерируется по sequence/autoincrement (а другого варианта с большой постоянно пополняемой таблицей я представить не могу), то описываемый метод «в лоб» не подойдёт, т.к. неизбежно будут пропуски значений.
                                                        +1
                                                        Как хорошо что у нас есть всего одна SQL база данных, которая, к тому же, работает по одному и тому же принципу, что мы можем давать такие убедительные советы, даже не указывая к какой БД они относятся.
                                                          +1

                                                          Чудесно, и ни слова о сортировках. Пользователям так-то почти никогда не нужны выборки по идентификатору. А часто нужны выборки даже по неуникальным полям. Рекомендую почитать эту статью о паджинации, которая не оторвана от реальности: https://habr.com/ru/post/413133/

                                                            –1
                                                            я не знаю в какой СУБД чтобы сделать оффсет пропущенные записи надо продолжать хранить в памяти.
                                                            я не знаю в каких задачах нужно делать паггинацию. по ключу I'd
                                                            автор такое ощущение из прошлого века или о СУБД только в теории знает.
                                                            статья полный бред извините за мой французский
                                                              0
                                                              Проблема pagination + filter + sort не решается «просто» (прикиньте как вы сами это запрограммировали бы в своём движке SQL).

                                                              Лучшее до чего дошёл я — никакого виртуального скроллинга или постраничной навигации для больших таблиц.
                                                              В GUI grid — либо предпросмотр результатов фильтрации с показом первых N записей из курсора и SQL с LIMIT N+1 либо кнопка «скачать все» работающая через открытый streaming cursor на backend. Моим пользователям было достаточно, к счастью.
                                                                0

                                                                Она вообще не решается в общем случае, потому что у пользователей разные требования.

                                                                0
                                                                А если pk не int а uuid? То тут какой кейс?
                                                                  0
                                                                  плохая статья. т.к. не затрагиваются самые интересные варианты
                                                                  1) сортировка, например по полю createdate, как отработает с fetch\limit и where date > x
                                                                  2) что делать тем у кого id это guid?
                                                                    0
                                                                    А как будет работать такая пагинация при пропусках в таблице?
                                                                      0

                                                                      Нормально при пропусках как раз.

                                                                        0
                                                                        К примеру, пропущены id с 1 по 100; записей на страницу — 10. Тогда запросы с условием id>0 и id>10 вернут одни и те же записи. Как разбивать по страницам в таком случае?
                                                                          0

                                                                          Запрос с id > 0 LIMIT 10 вернёт 101-110, следующий запрос будет id > 110 LIMIT 10. Как и с классической OFFSET LIMIT пагинацией рулит страницами клиент.

                                                                            0
                                                                            Вы предлагаете отталкиваться от максимального найденного id — идея хорошая. А как без лишних запросов к БД рассчитать, с какого id будет начинаться отсчет, скажем, через 3 страницы от текущей?
                                                                              0

                                                                              Без лишних не получится, наверное (если не брать какие-нибудь рекурсивніе CTE). Вернее можно сделать, но с тем же OFFSET- где-то будет иметь смысл, где-то нет.

                                                                            +1
                                                                            В этом случае забываем о переходах по номерам страниц и оставляем только кнопки следующая / предыдущая / первая / последняя страницы. И в качестве отсечки берём максимальное (вперёд) или минимальное (назад) значения id текущей загруженной страницы.
                                                                              0

                                                                              Ну, чисто в теории, можно сделать вызов в цикле, как с клиента, а лучше на бэке.

                                                                      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                                      Самое читаемое