SELECT * FROM articles ORDER BY id LIMIT 20000, 20

Что приходит Вам на ум, когда Вы видите такой код? Да, это реализация постраничного вывода на уровне SQL. А еще это выбор результатов для отображения 1000-й страницы.

И Вы сталкиваетесь с тем, что этот запрос работает очень быстро для выборки первых страниц, и невероятно медленно для последних. Почему и как это исправить? Рассмотрим пример для MySQL, хотя описанные принципы применимы для любой СУБД.

Типичная задача постраничного вывода

Обычно задача включает в себя следующее:

• Вывести количество найденых результатов
• Выввести ссылки на страницы результатов
• Вывести ссылки на следующую/предыдущую страницу результатов

Эти функциональные особенности являются просто реализуемыми стандартными средставми СУБД. В MySQL мы для этого используем LIMIT OFFSET и функцию count(). Но не учитывая особенностей подобного подхода, мы упираемся в проблемы медленных запросов при выборке последних страниц. Почему так происходит?

Как работает LIMIT, OFFSET в MySQL

Вернемся к нашему запросу:

SELECT * FROM articles ORDER BY id LIMIT 20000, 20

Сначала MySQL забывает о том, что есть LIMIT и делает выборку и сортировку всех радяков, исходя из запроса. Далее, на полученный результат накладывается ограничение по смещению (т.е. работает правило LIMIT). Это значит, что в нашем случае MySQL выбирает 20020 записей, потом отбрасывает 20000 и выдает только 20.

Проведем эксперимент. Пусть в нашей таблице 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(*)

Выборка количества рядков, соответствующих запросу работает аналогичным образом, только MySQL отдает количество найденых рядков, а не их набор. Понятно, что для тяжелых запросов подсчет количества будет происходить весьма медленно.

Решение проблемы - первый подход

И так, у нас есть тяжелые запросы. Несколько советов по решению этой проблемы:

• Делегация. Для избавления от проблемы вывода тяжелой выборки, можно перенести задачу постраничного вывода на другую технологию. Тогда Вы делаете выборку абсолютно всех рядков - кешируете ее и организовываете постраничный вывод на уровне, например, PHP. Для того, что-бы не хранить в кеше результаты огромной выборки, стоит кешировать только первичные ключи записей.
• Избавляйтесь от LIMIT OFFSET, если это возможно. Например, Вы можете передавать первичный ключ первой записи на странице, и тогда выборка будет выглядить следующим образом: “… WHERE id > 350 … LIMIT 15″. В этом случае, Вам необходимо всегда выбирать рядков на 1 больше, чем Вы показываете, что-бы сгенерировать правильную ссылку на следующую страницу результатов (В нашем примере, мы покажем только 14 записей на странице).
• Тяжелое кеширование. Кешируйте результаты всех страниц, тогда Вы сможете гарантировать, что запросы выполняться только один раз. Этот подход стоит внимательно обдумать на предмет того, как часто Вам придется чистить кеш, и насколько он будет эффективен.
• Для определения приблизительного количества записей в таблице пользуйтесь EXPLAIN - это оценочный результат (чего во многих случаях достаточно), но работает гораздо эффективнее, чем count()

Решение проблемы - второй подход

Второй подход имеет концептуальный характер. Обдумайте Ваши требования, варианты использования, потребности пользователей. Возможно, шаблонное решение Вам не нужно. Проанализируйте следующее:

1. Действительно ли Вам необходимо показывать пользователю сколько записей было найдено при выборке, что это ему даст ? Не используйте подсчет количества результатов, если он действительно не нужен. Наоборот, используйте оценочный показатель (например, можно парсить вывод EXPLAIN)
2. Нужно ли пользователю иметь возможность листать на последние страницы списка? Google не дает возможности просмотреть миллионную страницу, и никто не нервничает, ведь люди смотрят обычно только первые несколько страниц. Лимитируйте вывод до разумного предела.
3. Нужна ли пользователю возможность перейти со страницы 3 на страницу 17? Возможно ему хватит только ссылок “следующая” и “предыдущая”? Это поможет Вам экономить ресурсы на подсчете количества страниц результатов.

Все эти рекоммендации можно использовать для различных СУБД, т.к. в большинстве своем они носят методический характер.

Статьи по теме

• Как выбрать колонки для индексирования в MySQL
• Оптимальная настройка Mysql сервера
• Оптимизация клиентской части
• check-unused-keys - проверка неиспользуемых индексов в MySQL
• Настройка и оптимизация параметров postgresql