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

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

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

Верно?

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

Вы не согласны со мной? Ты не нужно к. Slack, Shopify и Mixmax разбивают свои API на страницы с той же концепцией, о которой мы будем говорить сегодня.

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

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

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

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

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

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

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

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

Это означает, что если у вас 100.000.000 пользователей и вы запрашиваете СМЕЩЕНИЕ в 50.000.000, ему нужно будет получить все эти записи (которые даже не понадобятся!), Поместить их в память и только после этого получить 20 результатов указаны в LIMIT.

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

50.000 to 50.020 of 100.000

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

Если вы мне не верите, посмотрите на эту скрипку, которую я создал. На левой панели у вас есть базовая схема, которая вставит 100000 строк для нашего теста, а справа - проблемный запрос и наше решение. Просто нажмите Выполнить вверху и сравните время выполнения каждого из них. Выполнение # 1 (проблемного запроса) занимает как минимум 30x секунды.

А с большим объемом данных ситуация становится еще хуже. Посмотрите мой Proof Of Concept с 10 миллионами строк.

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

TL; DR; Чем выше значение OFFSET, тем дольше будет выполняться запрос.

Что вы должны использовать вместо

Вот что вам следует использовать:

Это пагинация на основе курсора.

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

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

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

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

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

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

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

Как всегда, я бы посоветовал всегда думать о плюсах и минусах каждой архитектуры таблиц и о том, какие запросы вам нужно будет выполнять в каждой из них. Если вам нужно иметь дело с большим количеством связанных данных в ваших запросах, Статья о списках Рика Джеймса может дать вам более подробное руководство.

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

Заключение

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

Ой. И, пожалуйста, не забудьте узнать об индексах. И объяснять запросы.

Если вы ищете, как реализовать разбиение курсора на страницы в ElasticSearch, не стесняйтесь ознакомиться со статьей ElasticSearch - Вот как вы должны разбивать свои результаты на страницы.

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

Этот пост изначально был размещен на ivopereira.net.