Постраничный вывод в MySQL
Выборки со смещением (LIMIT/OFFSET) могут работать очень медленно при больших значениях смещений. Это происходит по причине того, что MySQL перебирает и отбрасывает все строки результата, пока их количество не будет равно значению OFFSET. Например:
SELECT * FROM articles ORDER BY id LIMIT **20000, 20**
Такой запрос будет использоваться для показа 1000й страницы статей (по 20 статей на страницу). В таком случае, MySQL переберет и отбросит 20 тыс. записей до того, как вернет 20 нужных нам.
Естественно, с ростом номера страницы, запрос будет работать все медленнее.
Требования
Обычно реализация постраничного вывода подразумевает:
- Вывести количество всех результатов (например, всего 234 тыс. статей)
- Вывести ссылки на страницы результатов (1я, 2я, 3я… 2334я и т.п.)
В стандартном случае все реализуется довольно просто:
Количество всех результатов:
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 раз дольше, чем первый. Причем мы рассматриваем достаточно простой вариант, с элементарным запросом и структурой таблицы.
Проблема count(*)
Если Вы используете таблицы InnoDB, то count(*) будет работать очень медленно. Это вторая проблема, которая приводит к замедлению работы постраничной выборки.
Решение первое — избегание
Лучшее решение проблемы — избежать ее:
- Действительно ли Вам необходимо показывать пользователю сколько записей было найдено при выборке?
- Нужно ли пользователю иметь возможность листать на последние страницы списка? Возможно, следует ограничиться несколькими первыми страницами?
- Нужна ли пользователю возможность перейти со страницы 3 на страницу 17? Возможно ему хватит только ссылок “следующая” и “предыдущая”? Это позволит не использовать тяжелый расчет количества доступных страниц.
Решение второе — оптимизация
Стоит подумать о переносе задачи постраничного вывода на другую технологию. Тогда Вы делаете выборку абсолютно всех строк, кэшируете ее и организовываете постраничный вывод на уровне, например, 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.
Сообщить об опечатке
Текст, который будет отправлен нашим редакторам: