Быстрая нумерация страниц в Mysql — почему order с Limit и смещением является медленным?

Запросы с LIMIT и OFFSET распространены в приложениях, которые требуют нумерации страниц, а в некоторых случаях могут работать некоторое время.

Однако во многих случаях они становятся медленными и болезненными, когда значение OFFSET имеет высокое значение.

Почему OFFSET такой медленный?

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

Если ваш запрос использует следующее предельное условие: «LIMIT 50000, 20», он фактически запрашивает у базы данных 5050 строк и отбрасывает первые 50 000. Это действие может иметь высокую стоимость, время отклика на воздействие.

Вы можете спросить себя: «Кто, черт возьми, собирается пропустить страницу 50 000 в моем заявлении?».
Давайте перечислим несколько возможных вариантов использования:

• Ваша любимая поисковая система (Yandex / Google / Bing / Yahoo / DuckDuckGo / что угодно) собирается проиндексировать ваш сайт электронной коммерции. У вас есть около 100 000 страниц на этом сайте. Как отреагирует ваше приложение, когда поисковый бот попытается получить эти последние 50000 страниц для их индексации? Как часто это будет происходить?
• В большинстве веб-приложений мы позволяем пользователю переходить к последней странице, а не только к следующей. Что произойдет, когда пользователь попытается перейти на страницу 50 000 после посещения страницы 2?
• Что произойдет, если пользователь попал на страницу 20 000 из результатов поиска Google, ему что-то понравилось и разместил это на Facebook, чтобы его могли прочитать еще 1000 друзей?

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

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

SELECT
*
FROM
events
WHERE
date > '2019-04-12T00:00:00-00:00'
AND event = 'editstart'
ORDER BY date
LIMIT 50;

Как оптимизировать медленные OFFSET запросы?

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

Хорошей альтернативой использованию OFFSET будет метод поиска.

Проще говоря, метод поиска — это поиск уникального столбца или набора столбцов, который идентифицирует каждую строку. Затем, вместо использования предложения OFFSET, мы можем просто использовать это уникальное значение в качестве закладки, которая представляет позицию последней выбранной строки, и запросить следующий набор строк, начиная с этой позиции в предложении WHERE.

Например, если посмотреть на запросы, которые мы выполняли ранее, предполагая, что последний идентификатор события со смещением 999 999 равнялся ‘111866’, запрос будет:

SELECT    *
FROM
events
WHERE
(date,id) > ('2019-04-12T10:29:47-07:00',111866)
AND event = 'editstart'
ORDER BY date, id
LIMIT 10

Другой способ написать запрос:

SELECT    *
FROM
events
WHERE
date>='2019-04-12T10:29:47-07:00' and not (date='2019-04-12T10:29:47-07:00' and id < 111866)
AND event = 'editstart'
ORDER BY date, id
LIMIT 10

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

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

Возможные подводные камни/проблемы

• • Вам нужно будет изменить код в вашем приложении, чтобы этот метод работал, сохранив последнюю извлеченную строку (вместо того, чтобы корректировать соответствующее значение смещения).
  • Должен быть указатель на уникальный столбец поиска/набор столбцов.
  • Каждый столбец в уникальном наборе столбцов должен иметь ограничение NOT NULL, в противном случае вы можете получить непредвиденное поведение.
  • Когда пользователь пропускает страницы, вам сначала нужно выбрать соответствующую позицию этой страницы. Скажем, мы хотим перейти к странице 40000:

    SELECT date, id
    FROM events
    ORDER BY date, id
    LIMIT 1 OFFSET 39999 ;Этот запрос должен быть очень быстрым, потому что он использует индекс покрытия.

Заключение

Мы не рекомендуем использовать функцию OFFSET в MySQL для реализации возможностей подкачки. Когда данные растут, вы, вероятно, начнете замечать проблемы с производительностью. Вместо этого рассмотрите возможность использования метода поиска, описанного выше.