Оптимизировать часто выполняемый запрос к таблице orders

70. Оптимизировать часто выполняемый запрос к таблице orders

Условие задачи:
📌 Есть запрос, который часто выполняется в системе. При его выполнении наблюдается высокая нагрузка на диск.

Нужно объяснить, что можно сделать для оптимизации такого запроса и снижения disk I/O.

Код:

SELECT status, created_at
FROM orders
WHERE user_id = :userId
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;

Спойлеры к решению

Подсказки

💡 Сначала нужно понять, есть ли подходящий индекс под WHERE + ORDER BY.
💡 Для такого запроса обычно нужен составной индекс по user_id и created_at.
💡 Если СУБД поддерживает covering index / include columns, можно добавить status, чтобы уменьшить чтение таблицы.
💡 EXPLAIN ANALYZE поможет проверить, идёт ли full scan, bitmap scan или index scan.
💡 Если таблица очень большая, можно подумать о партиционировании по дате.

Решение

Основное, что здесь нужно сделать — дать СУБД индекс, который подходит под фильтрацию и сортировку ✅

Для такого запроса хороший базовый вариант:

CREATE INDEX idx_orders_user_created_at
ON orders (user_id, created_at DESC);

Почему именно так:

user_id = :userId — точное условие
created_at >= ... — диапазон
ORDER BY created_at DESC — можно поддержать тем же индексом
LIMIT 20 — СУБД сможет быстро взять только верхние записи, а не читать много лишнего

Если СУБД поддерживает покрывающий индекс, можно ещё улучшить запрос и уменьшить чтение таблицы:

CREATE INDEX idx_orders_user_created_at_cover
ON orders (user_id, created_at DESC)
INCLUDE (status);

Тогда возможен index only scan, и нагрузка на диск станет меньше, потому что status не придётся дочитывать из основной таблицы.

Что ещё стоит сделать:

проверить план через EXPLAIN ANALYZE
убедиться, что запрос реально использует индекс
если таблица очень большая и запросы часто идут по последним 30 дням — рассмотреть партиционирование по created_at
следить за тем, чтобы статистика таблицы была актуальной (ANALYZE)
если данных очень много, можно подумать о кешировании на уровне приложения

Что обычно отвечать на собеседовании 🎯

Для такого запроса в первую очередь нужен составной индекс по (user_id, created_at DESC), потому что он покрывает фильтрацию, сортировку и LIMIT.
Если хочется ещё сильнее снизить disk I/O, можно сделать покрывающий индекс с status, чтобы получить index only scan.
Дальше я бы проверил план через EXPLAIN ANALYZE и при очень больших объёмах рассмотрел партиционирование по дате.