Исправление запроса
SELECT user_id, $COUNT(\ast )$ FROM orders
WHERE status = 'done'
GROUP BY user_id;
Что не так и почему
- Отсутствует `GROUP BY user_id` — в стандартном SQL агрегатные функции (здесь $COUNT$) требуют группировки по неагрегированным столбцам.
- Запрос может быть очень дорогим на больших таблицах, если приходится сканировать всю таблицу или большие её части.
Как оптимизировать для больших таблиц
1) Индексы
- MySQL (и прочие, без поддержи частичных индексов): создайте составной индекс с фильтром по статусу первым:
CREATE INDEX idx_orders_status_user ON orders (status, user_id);
Такой индекс позволяет быстро отфильтровать строки по `status='done'` и затем группировать по `user_id` (в идеале — выполнить index-only scan).
- PostgreSQL: лучше частичный (filtered) индекс:
CREATE INDEX idx_orders_user_done ON orders (user_id) WHERE status = 'done';
Он покрывает именно этот запрос и хранит только строки со статусом `done`.
- COUNT(*) vs COUNT(1): в плане производительности эквивалентны; можно писать $COUNT(\ast )$ или $COUNT(1)$.
2) Покрывающий индекс / index-only scan
- Если в выборке используются только поля, содержащиеся в индексе (здесь `user_id` и `status`), СУБД может не обращаться к таблице, а читать данные прямо из индекса — значительно быстрее.
3) Партиционирование
- Партиционирование по дате (range) полезно, если запросы ограничены по времени (тогда выполняется pruning).
- Партиционирование по списку по `status` имеет смысл, если статусов немного и часто фильтруют по ним; позволяет сканировать только одну партицию.
- Учтите: при очень низкой кардинальности `status` выгода может быть невелика, а сложность — увеличится.
4) Агрегация / ресурсы
- Позвольте СУБД делать хэш-агрегацию в памяти: увеличьте параметры памяти для сортировки/агрегации (например, `work_mem` в PostgreSQL).
- Для очень больших наборов используйте внешнюю (disk-based) агрегацию или параллельные запросы (если СУБД поддерживает).
5) Материализованные данные / денормализация
- Если счётчики по пользователям часто запрашиваются и обновления происходят отдельно, заведите агрегированную таблицу/материализованное представление:
CREATE MATERIALIZED VIEW orders_done_per_user AS
SELECT user_id, $COUNT(\ast )$ AS cnt
FROM orders WHERE status = 'done' GROUP BY user_id;
Обновляйте периодически или через триггеры/CDC для реального времени.
- Это даёт мгновенный ответ за счёт дополнительных затрат на поддержание.
6) Прочие оптимизации
- Обновляйте статистику (ANALYZE в Postgres, ANALYZE TABLE в MySQL), чтобы планировщик выбирал хорошие планы.
- Для выборки топ-N добавьте ORDER BY и LIMIT, и обеспечьте соответствующий индекс:
SELECT user_id, $COUNT(\ast )$ AS cnt FROM orders WHERE status='done' GROUP BY user_id ORDER BY cnt DESC LIMIT $N$;
Для этого часто удобнее поддерживать предагрегированные счётчики.
Рекомендации по приоритету
1. Исправить запрос (добавить GROUP BY).
2. Добавить покрывающий/частичный индекс (Postgres: partial index; MySQL: composite `(status, user_id)`).
3. Рассмотреть материализованные таблицы/счётчики для горячих метрик.
4. Партиционирование — если данные большие и запросы хорошо локализуются (по дате или статусу).
5. Тюнинг параметров памяти/параллелизма и регулярный анализ статистики.
Если нужно, могу привести конкретные команды для вашей СУБД (Postgres/MySQL) и оценки ожидаемого эффекта.