Comments 29
Что за пагинация такая что в ней по дефолту идёт группировка по первичному ключу???
"select table_name.* from table_name group by ID limit ? offset ?"
Согласен в первом запросе можно обойтись без group by ID, указал его для наглядности, так как во втором запросе использую group by sortID.
Отличное объяснение. Теперь осталось только понять, зачем во втором запросе group by.
Так как sortID не является PK для таблицы
Ну хочется как минимум отметить пару моментов.
Допустим, набор записей может сортироваться по нескольким критериям. В простейшем случае ASC/DESC, а вообще по разным полям (по алфавиту, дате, цене и пр.). Получается, на каждую мыслимую сортировку нужно прикручивать своё поле sortID_N. Расточительно.
Записи иногда добавляются. Причём далеко не всегда добавленные записи оказываются в самом конце списка при требуемой сортировке. Как итог - после каждой подобной вставки требуется пересчёт sortID. А пересчитать и обновить поле для сотни тысяч записей - это не мгновенно.
Так что метод если и применим, то очень редко, в очень узкой области вывода статических, неизменяемых наборов данных. То есть ценность метода, по большому счёту, куда как ниже, чем декларируется. Потому и используется редко. ИМХО.
Да, ещё момент. Не понял, зачем в запросах группировка. Глупо как-то. Если ID - первичный ключ, то бессмысленно. А если не первичный ключ - так запрос и вовсе синтаксически неверен. В MySQL ещё худо-бедно выполнится при отключенном ONLY_FULL_GROUP_BY, а вот PostgreSQL просто обидится..
И последнее. LIMIT без использования ORDER BY - это не более чем лотерея. Угадай, что выведет сервер.. да, в большинстве случаев при тупом SELECT без WHERE сервер возвращает записи в соответствии с сортировкой по выражению первичного индекса - но даже это не догма.
Я так понимаю автор в этой статье хотел показать пагинацию по ключам, но тему эту даже близко не раскрыл и зачем-то прикрутил новую колонку sortID. Такую пагинацию вполне можно выполнять и по pk. Правда в случае сортировки данных по какой-то другой колонке нужно прописывать дополнительную логику (в случае если колонка не уникальна) WHERE some_field > $1 OR (some_field = $1 AND id > $2) ORDER BY some_field, id Ну и конечно без индексов никуда
К сожалению, PK не всегда бывает сортируемым, в часто uuid. Я показал на примере как внедрить сортируемое поле и в дальнейшем его использовать.
Не часто. И вообще редко такое можно встретить. Если есть строковое UUID, то для него делается отдельная таблица, если это выборка. Если это для него и есть таблица, первичный ключ должен быть целочисленным уникальным, а не по строковому полю.
Любое уникальное поле является сортируемым. Просто по определению.
Некоторые виды UUID не сортируются по дате добавления, это верно. Но в вашем случае это нигде и не оговаривается. И сортировать можно в любом порядке, главное чтобы он оставался одним и тем же.
Спасибо за интересный комментарий.<o:p></o:p>
В статье я писал: «Но необходимо понимать, что за данную оптимизацию мы платим тем, что мы испытываем сложности с дополнительной сортировкой (например, по алфавиту)». Чтобы избежать внедрения дополнительных полей (помимо sortID), я использовал фильтрацию: по имени (алфавит), по дате...
Прошу учесть, что в миграции я добавлял поле sortID AUTO_INCREMENT. В свою очередь AUTO_INCREMENT: Значение будет увеличиваться для каждой новой строки; Значение уникально, дубликаты невозможны; Если строка удалена, auto_increment столбец этой строки не будет повторно назначен. То есть перерасчёт sortID не требуется. У новой записи всегда будет sortID больше чем у предыдущих (это можно назвать сортировкой по дате создания).
Согласен в первом запросе можно обойтись без group by ID, указал его для наглядности, так как во втором запросе использую group by sortID.
Так же согласен, что необходимо дополнительно подстраховаться и указать ORDER BY в первом запросе.
в первом запросе можно обойтись без group by ID, указал его для наглядности
??? Какой наглядности?
во втором запросе использую group by sortID
Гм... а зачем? Так, чисто по описанию, sortID - уникальное поле. Но тогда группировка по нему - просто способ заставить сервер проделать никому не нужную и совершенно бессмысленную дополнительную работу.
Я нихчего не понял.
Сначала идет запрос с group by ID. Тут я вижу только два варианта: либо ID неуникален (что уже идет вразрез со всеми соглашениями об именовании полей), либо тут просто перепутано group by c order by. Но кончается всё запросом group by sortID. Куда делась группировка по ID? Она была не нужна? Зачем тогда было делать новое поле sortID, если можно было просто сделать существующее поле ID первичным ключом.
Ну и традиционно, умиляет наличие в тегах бигдаты.
Я бы с удовольствием почитал статью о решении проблемы пагинации, если бы это была более реальная статья, а не очередное унылое откровение "делайте where id > ...". А если надо пагинировать результаты сортировки по нескольким полям? Причём одни asc, а вторые desc? А если в where были дополнительные условия фильтрации и больше нельзя установить соответствие кол-во записей на странице - кол-во ключей?
Ну так стандартно — возвращаться к тому же LIMIT-у, но ограничивать запрашиваемый объём. Если это пагинация для людей, то больше, скажем, тысячи страниц показывать бессмысленно.
Если это пагинация для АПИ, то делать обязательный параметр, ограничивающий выборку, например по дате.
Я встречал один совет (для MySQL, но по идее должен работать и на других базах), но пока за занятостью руки проверить всё никак не дойдут.
Кто-то советовал LIMIT, OFFEST выбирать первичные ключи из целевой таблицы, а вторым запросом уже по ним нужные записи. По идее тут сканирование будет идти по индексу, который и так может быть в памяти закеширован.
Это может помочь, но главная проблема здесь в другом. "Сканирование будет идти по индексу" только если выборка идет по этому индексу. А если выборка идёт с сортировкой, или с дополнительными условиями, то никакой первичный ключ тут не поможет. Значения полей для WHERE все равно придется читать с диска, а сортировка будет файлсортом.
Поэтому самое важное здесь — это сделать кастомный индекс, в котором сначала указаны поля, по которым идёт сортировка, а потом все поля, которые используются в условиях. Вот тогда выборка будет идти по этому индексу и только из памяти. И выборка только первичных ключей здесь как раз будет к месту.
Но, если вернуться к исходному вопросу, выборка с лимитом, даже по индексу, все равно будет относительно медленной, и — главное — скорость запроса для каждой следующей страницы будет падать.
И в этом случае поможет то, чем я говорил выше — не раздувать пагинацию на сотни тысяч страниц, а ограничивать выборку определенным диапазоном.
"мы видим увеличение скорости"
А я вижу уменьшение скорости... как так-то?
Можно просто попросить ChatGPT оптимизировать запрос
Ускорение запросов на разных СУБД. Судя по тегам. Ок. А планы конечно лесом.
В pg можно использовать row_number() OVER() и уже пагинация делать по этому полю, не нужно создавать непонятные столбцы, сортировать можешь как угодно
CTE — это не прекрасная фея, которая порхает над базой данных, и волшебной палочкой выбирает нужные записи. А та же самая выборка, с фильтрами и сортировкой.
То есть использовать-то можно, но при этом базе данных придется сначала выбрать всю таблицу целиком. Ровно то действие, от которого и пытаются избавиться при пагинации.
where (table_name.sortID > ?)
Простите я ненастоящий сварщик ©... а чем вас не устроило такое же автоинкрементное поле ID?
Ускорение sql запросов к большим таблицам. Оптимизация пагинации