Выборки со смещением (LIMIT/OFFSET) могут работать очень медленно при больших значениях смещений. Это происходит по причине того, что MySQL перебирает и отбрасывает все строки результата, пока их количество не будет равно значению OFFSET. Например:
SELECT * FROM articles ORDER BY id LIMIT **20000, 20**
Такой запрос будет использоваться для показа 1000й страницы статей (по 20 статей на страницу). В таком случае, MySQL переберет и отбросит 20 тыс. записей до того, как вернет 20 нужных нам.
Естественно, с ростом номера страницы, запрос будет работать все медленнее.
Обычно реализация постраничного вывода подразумевает:
В стандартном случае все реализуется довольно просто:
Количество всех результатов:
SELECT count(*) FROM articles
Ссылки на страницы:
for ( $i = 1; $i <= $count; $i++ ) { echo "<a href="?page={$i}">страница {$i}</a>"; }
Проведем эксперимент. В нашей таблице есть 500 000 записей. Выполним ряд запросов со смещением:
# Запрос с выборкой первой страницы SELECT * FROM articles ORDER BY id DESC LIMIT 0, 20 # Время исполнения: 0.08 секунды
# Запрос с выборкой одной из последних страниц SELECT * FROM articles ORDER BY id DESC LIMIT 480000, 20 # Время исполнения: 0.8 секунды
Заметно, что второй запрос выполнялся в 10 раз дольше, чем первый. Причем мы рассматриваем достаточно простой вариант, с элементарным запросом и структурой таблицы.
Если Вы используете таблицы InnoDB, то count(*) будет работать очень медленно. Это вторая проблема, которая приводит к замедлению работы постраничной выборки.
Лучшее решение проблемы — избежать ее:
Стоит подумать о переносе задачи постраничного вывода на другую технологию. Тогда Вы делаете выборку абсолютно всех строк, кэшируете ее и организовываете постраничный вывод на уровне, например, PHP. Для того, что-бы не хранить в кеше результаты огромной выборки, стоит кешировать только первичные ключи записей и список ID:
<? $list = [1,2,3,4,5]; # список ID статей $page = 1; ... memcache_set('list-page' . $page, $list); # сохраняем список в кеш ... foreach ( $list as $id ) { $article = memcache_get('article' . $id); # каждую статью достаем из кеша ... }
Попробуйте избавиться от LIMIT OFFSET, если это возможно. Например, можно передавать первичный ключ первой записи на странице, и тогда выборка результатов страницы будет выглядеть так:
SELEСT * FROM articles WHERE id > 350 LIMIT 20
Чтобы узнать, нужно ли делать ссылку на след. страницу можно выбрать не 20, а 21 запись. Показывать все равно будем только 20. А отсутствие 21й в результатах будет говорить о том, что ссылка не нужна.
В случае, если таблица меняется редко (несколько раз в сутки), кэшируйте полный результат каждой страницы.
Для замены count(*) можно парсить результаты EXPLAIN. Это даст оценочный результат (с погрешностью около 30%), но сработает намного быстрее.
Стандартная реализация постраничного вывода может быть очень медленной. Откажитесь от списков страниц и показа дальних страниц если это возможно. В другом случае используйте кеширование идентификаторов или замените OFFSET на выборку по ID.
Сегодня мы поговорим о том, как выбрать лучшие курсы Power BI в Украине, особенно для…
В 2023 году во всех крупнейших регионах конкуренция за вакансию выросла на 5–12%. Не исключением…
Unicorn Hunter/Talent Manager Лина Калиш создала бесплатный трекер поиска работы в Notion, систематизирующий все этапы…
Edtech-стартап Mate academy принял решение отправить своих работников в десятидневный отпуск – с 25 декабря…
Служба безопасности Украины задержала в Киеве 46-летнего программиста, который за деньги устанавливал шпионские программы и…
IT-специалист Джордан Катлер создал и выложил на Github подборку разнообразных ресурсов, которые помогут достичь уровня…