Использование MySQL LIMIT
В Mysql для ограничения количества результатов используется инструкция LIMIT:
SELECT * FROM users ORDER BY id DESC LIMIT 10 последние 10 записей из таблицы users
Смещение
Чтобы вернуть результаты с 6 по 15, нужно использовать такой синтаксис:
SELECT * FROM users ORDER BY id DESC LIMIT 5, 10 последние 10 записей из таблицы users, но без первых 5ти
Или то же самое:
SELECT * FROM users ORDER BY id DESC LIMIT 10 OFFSET 5
Скорость
С точки зрения производительности, LIMIT работает очень быстро. Однако использование больших величин смещения может привести к деградации производительности. Например, запрос:
SELECT * FROM users ORDER BY id DESC LIMIT 10
будет работать значительно быстрее, чем запрос:
SELECT * FROM users ORDER BY id DESC LIMIT 9999, 10
Во втором случае MySQL пропустит 10 тыс. строк, чтобы показать всего 10.
Читайте как оптимизировать большие смещения в 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.
Last updated