> Какие есть способы оптимизации тяжелых запросов к базе данных (PHP)

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

Основные способы оптимизации тяжелых запросов: анализ через EXPLAIN, добавление и корректировка индексов, рефакторинг запросов (устранение SELECT *, разбиение на подзапросы, использование JOIN вместо вложенных запросов), денормализация данных, кэширование результатов, партиционирование таблиц и ограничение выборки через LIMIT. Также важно проверять покрытие индексов, избегать функций в WHERE и использовать правильные типы данных. Для PHP-приложений дополнительно применяется кэширование через Redis или Memcached.

Подробное объяснение

Оптимизация тяжелых запросов начинается с диагностики. EXPLAIN показывает план выполнения: какие индексы используются, сколько строк сканируется, есть ли временные таблицы или filesort. Основные направления:

1. Индексы - составные индексы под конкретные WHERE и ORDER BY, покрывающие индексы, удаление дублирующих. Индекс на внешних ключах обязателен.

2. Структура запроса - избегать SELECT *, использовать только нужные колонки, заменять OR на UNION, переписывать NOT IN на LEFT JOIN с NULL-проверкой, выносить вычисления из WHERE в константы.

3. Денормализация - добавление вычисляемых полей, кэш-таблиц агрегатов, особенно для частых COUNT и SUM.

4. Партиционирование - разбиение больших таблиц по диапазонам (например, по дате) для ускорения выборок по ключу партиции.

5. Кэширование - на уровне приложения (Redis, Memcached) для часто повторяющихся запросов, а также query cache в MySQL (устарел в 8.0).

6. Ограничение нагрузки - LIMIT с пагинацией, использование ключей для cursor-based пагинации вместо OFFSET.

Для PHP важно: не выполнять запросы в цикле, использовать prepared statements, собирать данные одним запросом с JOIN вместо нескольких.

На практике

В реальном проекте сначала смотрим медленный лог MySQL, находим проблемные запросы. Затем EXPLAIN и проверяем: если type = ALL или rows большое - нужен индекс. Если запрос с JOIN и сортировкой - смотрим на filesort и временные таблицы.

Частая ситуация: запрос с несколькими JOIN и агрегацией. Решение - разбить на два запроса: сначала получить ID нужных записей, затем второй запрос с WHERE IN. Либо создать материализованное представление, обновляемое по расписанию.

Для пагинации на больших таблицах используем WHERE id > last_id вместо OFFSET, так как OFFSET заставляет базу сканировать все предыдущие строки.

В PHP-коде проверяем, что нет N+1 запросов через ORM, используем eager loading (в Doctrine - joins в QueryBuilder).

Пример кода

SQL
-- Плохо: сканирование всей таблицы
SELECT * FROM orders 
WHERE YEAR(created_at) = 2023 
ORDER BY total DESC;

-- Хорошо: индекс на (created_at, total) и диапазон
SELECT id, total FROM orders 
WHERE created_at >= '2023-01-01' 
  AND created_at < '2024-01-01' 
ORDER BY total DESC 
LIMIT 20;

PHP
// Плохо: N+1 запросов
foreach ($users as $user) {
    $orders = $db->query("SELECT * FROM orders WHERE user_id = {$user['id']}");
}

// Хорошо: один запрос с JOIN
$stmt = $db->prepare(
    "SELECT u.id, u.name, o.total 
     FROM users u 
     LEFT JOIN orders o ON o.user_id = u.id 
     WHERE u.id IN (:ids)"
);

Как отвечать на собеседовании

Начните с диагностики: EXPLAIN, медленный лог. Затем перечислите основные категории: индексы, рефакторинг запросов, кэширование, структурные изменения. Приведите конкретный пример из практики: как нашли проблему и что сделали. Упомяните trade-off: индексы ускоряют SELECT, но замедляют INSERT/UPDATE. Покажите понимание, что оптимизация - итеративный процесс, а не разовое действие.

Что проверяет интервьюер

• Понимание принципов работы индексов (B-tree, покрывающие индексы).
• Умение читать план выполнения запроса.
• Знание типичных анти-паттернов: SELECT *, функции в WHERE, N+1.
• Способность выбирать между денормализацией и нормализацией.
• Понимание ограничений кэширования (инвалидация, согласованность).
• Практический опыт: не просто теория, а реальные кейсы.

Типичные ошибки

• Предложение "добавить индекс" без анализа конкретного запроса.
• Игнорирование покрывающих индексов и составных.
• Использование функции в WHERE, что убивает индекс.
• Кэширование без стратегии инвалидации - данные устаревают.
• Попытка оптимизировать все запросы сразу, вместо фокуса на самых тяжелых.
• Забывают про LIMIT и пагинацию на больших выборках.
• Не учитывают, что EXPLAIN показывает план для конкретных данных - после изменения данных план может стать другим.