Постраничный вывод в 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(*) будет работать очень медленно. Это вторая проблема, которая приводит к замедлению работы постраничной выборки.

Решение первое — избегание

Лучшее решение проблемы — избежать ее:

1. Действительно ли Вам необходимо показывать пользователю сколько записей было найдено при выборке?
2. Нужно ли пользователю иметь возможность листать на последние страницы списка? Возможно, следует ограничиться несколькими первыми страницами?
3. Нужна ли пользователю возможность перейти со страницы 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.