Краткий ответ

Индекс может устранить сортировку, если его порядок соответствует условиям фильтрации и требуемому ORDER BY. Тогда СУБД читает подходящий диапазон индекса уже в нужной последовательности и передаёт строки дальше без отдельной операции сортировки.

Исторический контекст

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

Без такого механизма СУБД должна сначала получить строки, а затем построить структуру сортировки в памяти или во временном хранилище. На больших результатах это увеличивает задержку и потребление ресурсов.

Постановка проблемы

Запрос может находить строки быстро, но затем тратить основное время на Sort. Это особенно заметно при частом выполнении запроса, больших наборах результатов или использовании LIMIT, когда пользователю нужны только первые строки в заданном порядке.

Неверное решение — создать индекс с подходящими столбцами, но в неподходящем порядке. Такой индекс может ускорить фильтрацию, однако не избавить от сортировки. Дополнительный индекс также увеличивает стоимость вставок, обновлений и хранения данных.

Подробное решение

Индекс хранит ключи в упорядоченном виде. Если запрос фиксирует начальные столбцы индекса условиями равенства, а следующий столбец используется для сортировки, СУБД может просканировать соответствующий диапазон сразу в требуемом порядке.

Например, индекс по (customer_id, created_at) подходит для получения записей одного клиента по времени. customer_id ограничивает диапазон, а порядок created_at позволяет читать его от старых записей к новым или в обратном направлении.

CREATE INDEX orders_customer_created
    ON orders (customer_id, created_at);

SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

В подходящем плане вместо отдельного Sort может появиться индексное сканирование нужного диапазона в обратном направлении. Благодаря LIMIT СУБД иногда прекращает чтение после первых 20 подходящих строк, не обрабатывая весь диапазон.

Совпадение индекса и сортировки не гарантируется автоматически. На результат влияют порядок столбцов, направления сортировки, фильтры по диапазонам, выражения над столбцами, правила сравнения и особенности конкретной СУБД. При сложном ORDER BY, соединениях или необходимости вернуть большую часть таблицы оптимизатор может предпочесть другой план.

Индекс, устраняющий сортировку, не обязательно покрывает запрос. Если нужных столбцов нет в индексе, СУБД может выполнять дополнительные обращения к таблице; это иногда всё равно выгоднее сортировки, а иногда — нет. Решение принимают по фактическому плану выполнения и стоимости обоих вариантов.

Ситуация из практики

В системе заказов страница показывает последние заказы конкретного клиента. Фильтрация по клиенту выполнялась по индексу, но план дополнительно сортировал все найденные заказы, после чего выбирал первые 20 строк. Для клиентов с историей в сотни тысяч заказов это создавало заметную задержку.

Рассматривались три варианта. Увеличить лимит ресурсов сортировки было быстро, но это не устраняло лишнюю работу и могло ухудшить конкуренцию запросов. Принудительно использовать индекс было рискованно: при изменении объёма данных выбранный план мог стать невыгодным. Создать индекс с клиентом первым, а временем заказа вторым позволяло читать нужный диапазон в порядке выдачи.

Выбрали третий вариант после проверки планов на клиентах с малым и большим объёмом данных. Сортировка исчезла, а при наличии LIMIT чтение прекращалось после получения нужного количества строк. Компромисс состоял в дополнительных затратах на запись и размере нового индекса, поэтому индекс оставили только для действительно частого сценария.

Что кандидаты часто упускают

Достаточно ли того, что столбец из ORDER BY присутствует в индексе?

Нет. Важны позиция столбца и ограничения на предыдущие ключи. Если первый ключ индекса не ограничен подходящим условием, значения следующего ключа могут быть перемешаны между разными значениями первого, поэтому один последовательный проход не всегда выдаёт глобально отсортированный результат.

Почему индекс может убрать сортировку, но всё равно оказаться медленнее полного сканирования?

Индексный проход может привести к множеству случайных обращений к таблице, если нужных столбцов в индексе нет. Когда требуется вернуть большую долю строк, последовательное чтение таблицы с одной сортировкой может быть дешевле, чем индексный проход и многочисленные обращения к данным.

Всегда ли LIMIT делает индекс, соответствующий ORDER BY, особенно выгодным?

Нет. Это выгодно, когда фильтр достаточно быстро находит подходящие строки в начале нужного порядка. Если предикат плохо коррелирует с порядком индекса, СУБД может прочитать большой диапазон и отбросить много строк до достижения лимита. Поэтому нужно оценивать селективность фильтра, распределение данных и фактический план, а не ориентироваться только на наличие LIMIT.