ИТ Блог. Работа с базой данных (MySQL, CouchDB, MariaDB, MongoDB)
(2 оценок, среднее: 5,00 из 5)
Загрузка...
Запросы с LIMIT и OFFSET распространены в приложениях, которые требуют нумерации страниц, а в некоторых случаях могут работать некоторое время.
Однако во многих случаях они становятся медленными и болезненными, когда значение OFFSET имеет высокое значение.
Ну, в большинстве случаев запросы с низким смещением не медленные. Проблема начинается с высоких значений смещения.
Если ваш запрос использует следующее предельное условие: «LIMIT 50000, 20», он фактически запрашивает у базы данных 5050 строк и отбрасывает первые 50 000. Это действие может иметь высокую стоимость, время отклика на воздействие.
Вы можете спросить себя: «Кто, черт возьми, собирается пропустить страницу 50 000 в моем заявлении?».
Давайте перечислим несколько возможных вариантов использования:
Протестируем следующие значения 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 | Query Duration (ms) |
| 0 | 1 |
| 50 | 1 |
| 1000 | 13 |
| 10000 | 150 |
| 25000 | 500 |
| 50000 | 930 |
| 100000 | 1750 |
Чтобы оптимизировать медленные запросы 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 лучше, но и в том, что он более стабилен, независимо от того, как далеко вы разбиваетесь на таблицы.
SELECT date, id FROM events ORDER BY date, id LIMIT 1 OFFSET 39999 ;Этот запрос должен быть очень быстрым, потому что он использует индекс покрытия.
Мы не рекомендуем использовать функцию OFFSET в MySQL для реализации возможностей подкачки. Когда данные растут, вы, вероятно, начнете замечать проблемы с производительностью. Вместо этого рассмотрите возможность использования метода поиска, описанного выше.
